/ 
DBD-Sybase-0.09
River stage one • 4 direct dependents • 4 total dependents
/ DBD::Sybase

NAME

DBD::Sybase - Sybase database driver for the DBI module

SYNOPSIS

use DBI;

$dbh = DBI->connect("dbi:Sybase:", $user, $passwd);

# See the DBI module documentation for full details

DESCRIPTION

DBD::Sybase is a Perl module which works with the DBI module to provide access to Sybase databases.

Connecting to Sybase

The interfaces file

The DBD::Sybase module is built on top of the Sybase Open Client Client Library API. This library makes use of the Sybase interfaces file (sql.ini on Win32 machines) to make a link between a logical server name (e.g. SYBASE) and the physical machine / port number that the server is running on. The OpenClient library uses the environment variable SYBASE to find the location of the interfaces file, as well as other files that it needs (such as locale files). The SYBASE environment is the path to the Sybase installation (eg '/usr/local/sybase'). If you need to set it in your scripts, then you must set it in a BEGIN{} block:

BEGIN {
    $ENV{SYBASE} = '/opt/sybase/11.0.2';
}

$dbh = DBI->connect('dbi:Sybase', $user, $passwd);

Specifying the server name

The server that DBD::Sybase connects to defaults to SYBASE, but can be specified in two ways.

You can set the DSQUERY environement variable:

$ENV{DSQUERY} = "ENGINEERING";
$dbh = DBI->connect('dbi:Sybase:', $user, $passwd);

Or you can pass the server name in the first argument to connect():

$dbh = DBI->connect("dbi:Sybase:server=ENGINEERING", $user, $passwd);

Specifying other connection specific parameters

It is sometimes necessary (or beneficial) to specify other connection properties. Currently the following are supported:

charset

Specify the character set that the client uses.

     $dbh = DBI->connect("dbi:Sybase:charset=iso_1",
			 $user, $passwd);
language

Specify the language that the client uses.

     $dbh = DBI->connect("dbi:Sybase:language=us_english",
			 $user, $passwd);
packetSize

Specify the network packet size that the connection should use. Using a larger packet size can increase performance for certain types of queries. See the Sybase documentation on how to enable this feature on the server.

     $dbh = DBI->connect("dbi:Sybase:packetSize=8192",
			 $user, $passwd);
interfaces

Specify the location of an alternate interfaces file:

     $dbh = DBI->connect("dbi:Sybase:interfaces=/usr/local/sybase/interfaces",
			 $user, $passwd);
loginTimeout

Specify the number of seconds that DBI->connect() will wait for a response from the Sybase server. If the server fails to respond before the specified number of seconds the DBI->connect() call fails with a timeout error. The default value is 60 seconds, which is usually enough, but on a busy server it is sometimes necessary to increase this value:

     $dbh = DBI->connect("dbi:Sybase:loginTimeout=240", # wait up to 4 minutes
			 $user, $passwd);

These different parameters (as well as the server name) can be strung together by separating each entry with a semi-colon:

    $dbh = DBI->connect("dbi:Sybase:server=ENGINEERING;packetSize=8192;language=us_english;charset=iso_1",
			$user, $pwd);

Handling Multiple Result Sets

Sybase's Transact SQL has the ability to return multiple result sets from a single SQL statement. For example the query:

select b.title, b.author, s.amount
  from books b, sales s
 where s.authorID = b.authorID
 order by b.author, b.title
compute sum(s.amount) by b.author

which lists sales by author and title and also computes the total sales by author returns two types of rows. The DBI spec doesn't really handle this situation, nor the more hairy

exec my_proc @p1='this', @p2='that', @p3 out

where my_proc could return any number of result sets (ie it could perform an unknown number of select statements.

I've decided to handle this by returning an empty row at the end of each result set, and by setting a special Sybase attribute in $sth which you can check to see if there is more data to be fetched. The attribute is syb_more_results which you should check to see if you need to re-start the fetch() loop.

To make sure all results are fetched, the basic fetch loop can be written like this:

do {
    while($d = $sth->fetch) {
       ... do something with the data
    }
} while($sth->{syb_more_results});
$sth->finish;

You can get the type of the current result set with $sth->{syb_result_type}. This returns a numerical value, as defined in $SYBASE/include/cspublic.h:

#define CS_ROW_RESULT		(CS_INT)4040
#define CS_CURSOR_RESULT	(CS_INT)4041
#define CS_PARAM_RESULT		(CS_INT)4042
#define CS_STATUS_RESULT	(CS_INT)4043
#define CS_MSG_RESULT		(CS_INT)4044
#define CS_COMPUTE_RESULT	(CS_INT)4045

In particular, the return status of a stored procedure is returned as CS_STATUS_RESULT (4043), and is normally the last result set that is returned in a stored proc execution.

This should be compatible with other DBI drivers.

Sybase Specific Attributes

There are a number of handle attributes that are specific to this driver. These attributes all start with syb_ so as to not clash with any normal DBI attributes.

Database Handle Attributes

The following Sybase specific attributes can be set at the Database handle level:

syb_show_sql

If set then the current statement is included in the string returned by $dbh->errstr.

syb_show_eed

If set, then extended error information is included in the string returned by $dbh->errstr. Extended error information include the index causing a duplicate insert to fail, for example.

Statement Handle Attributes

The following read-only attributes are available at the statement level:

syb_more_results

See the discussion on handling multiple result sets above.

syb_result_type

Returns the numeric result type of the current result set. Useful when executing stored procedurs to determine what type of information is currently fetchable (normal select rows, output parameters, status results,\ etc...).

IMAGE and TEXT datatypes

DBD::Sybase uses the standard OpenClient conversion routines to convert data retrieved from the server into either string or numeric format.

The conversion routines convert IMAGE datatypes to a hexadecimal string. If you need the binary representation you can use something like

$binary = pack("H*", $hex_string);

to do the conversion. Note that TEXT columns are not treated this way and will be returned exactly as they were stored. Internally Sybase makes no distinction between TEXT and IMAGE columns - both can be used to store either text or binary data.

Transactions and Transact-SQL

When $h->{AutoCommit} is off (ie 0) the DBD::Sybase driver will send a BEGIN TRAN before the first $dbh->prepare(), and after each call to $dbh->commit() or $dbh->rollback(). This works fine, but will cause any SQL that contains any CREATE TABLE statements to fail. These CREATE TABLE statements can be burried in a stored procedure somewhere (for example, sp_helprotect creates two templ tables when it is run).

If you absolutely want to have manual commits (ie have AutoCommit set to 0) and be able to run any arbitrary SQL, then you can use sp_dboption to set the ddl in tran option to TRUE. However, the Sybase documentation warns that this can cause the system to seriouslys slow down as this causes locks to be set on certain system tables, and these locks will be held for the duration of the transaction.

Using ? Placeholders & bind parameters to $sth->execute

This version supports the use of ? placeholders in SQL statements. It does this by using what Sybase calls Dynamic SQL. The ? placeholders allow you to write something like:

	$sth = $dbh->prepare("select * from employee where empno = ?");

        # Retrieve rows from employee where empno == 1024:
	$sth->execute(1024);
	while($data = $sth->fetch) {
	    print "@$data\n";
	}

       # Now get rows where empno = 2000:
	
	$sth->execute(2000);
	while($data = $sth->fetch) {
	    print "@$data\n";
	}

When you use ? placeholders Sybase goes and creates a temporary stored procedure that corresponds to your SQL statement. You then pass variables to $sth->execute or $dbh->do, which get inserted in the query, and any rows are returned.

For those of you who are used to Transact-SQL there are some limitations to using this feature: In particular you can only pass a simple exec proc call, or a simple select statement (ie a statement that only returns a single result set). In addition, the ? placeholders can only appear in a WHERE clause, in the SET clause of an UPDATE statement, or in the VALUES list of an INSERT statement. In particular you can't pass ? as a parameter to a stored procedure.

Please see the discussion on Dynamic SQL in the OpenClient C Programmer's Guide for details. The guide is available on-line at http://sybooks.sybase.com/dynaweb.

BUGS

Setting $dbh->{LongReadLen} has no effect. Use $dbh->do("set textsize xxxx") instead.

You can't set a particular database via the connect() call. Use $dbh->do("use $database") instead.

SEE ALSO

DBI Sybase OpenClient C manuals. Sybase Transact SQL manuals.

AUTHOR

DBD::Sybase by Michael Peppler

COPYRIGHT

The DBD::Sybase module is Copyright (c) 1997, 1998 Michael Peppler. The DBD::Sybase module is free software; you can redistribute it and/or modify it under the same terms as Perl itself with the exception that it cannot be placed on a CD-ROM or similar media for commercial distribution without the prior approval of the author.

ACKNOWLEDGEMENTS

Tim Bunce for DBI, obviously!

See also "ACKNOWLEDGEMENTS" in DBI.