/ 
DBD-Sybase-0.08
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);

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;

This should be compatible with other DBI drivers.

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.

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.