/ 
DBD-Sybase-0.15
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:

server

Specify the server that we should connect to

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

The default server is SYBASE, or the value of the $DSQUERY environment variable, if it is set.

database

Specify the database that should be made the default database.

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

This is equivalent to

$dbh = DBI->connect('dbi:Sybase', $user, $passwd);
$dbh->do("use sybsystemprocs");
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);
timeout

Specify the number of seconds after which any Open Client calls will timeout the connection and mark it as dead. Once a timeout error has been received on a connection it should be closed and re-opened for further processing.

Setting this value to 0 or a negative number will result in an unlimited timeout value. See also the Open Client documentation on CS_TIMEOUT.

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

Specify the name for this connection that will be displayed in sp_who (ie in the sysprocesses table in the program_name column).

$dbh->DBI->connect("dbi:Sybase:scriptName=myScript", $user, $password);
hostname

Specify the hostname that will be displayed by sp_who (and will be stored in the hostname column of sysprocesses)..

$dbh->DBI->connect("dbi:Sybase:hostname=kiruna", $user, $password);
tdsLevel

Specify the TDS protocol level to use when connecting to the server. Valid values are CS_TDS_40, CS_TDS_42, CS_TDS_46, CS_TDS_495 and CS_TDS_50. In general this is automatically negotiated between the client and the server, but in certain cases this may need to be forced to a lower level by the client.

I have heard rumors that setting this to CS_TDS_42 enables DBD::Sybase to connect to an MS-SQL 7.0 server, although I can't confirm that firsthand.

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

NOTE: Setting the tdsLevel below CS_TDS_495 will disable a number of features, ?-style placeholders and non-AutoCommit mode, in particular.

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.

syb_err_handler

NEW Note that the syntax for the error handler is experimental and may change in future versions.

This attribute is used to set an ad-hoc error handler callback (ie a perl subroutine) that gets called before the normal error handler does it's job. If this subroutine returns 0 then the error is ignored. This is useful for handling PRINT statements in Transact-SQL, for handling messages from the Backup Server, showplan output, dbcc output, etc.

The subroutine is called with 7 parameters: the Sybase error number, the severity, the state, the line number in the SQL batch, the server name (if available), the stored procedure name (if available), and the message text.

Example:

%showplan_msgs = map { $_ => 1}  (3612 .. 3615, 6201 .. 6225);
sub err_handler {
    my($err, $sev, $state, $line, $server, $proc, $msg) = @_;

    if($showplan_msgs{$err}) { # it's a showplan message
        print SHOWPLAN "$err - $msg\n";
        return 0;    # This is not an error
    }

    return 1;
}

$dbh = DBI->connect('dbi:Sybase:server=troll', 'sa', '');
$dbh->{syb_err_handler} = \&err_handler;
$dbh->do("set showplan on");
open(SHOWPLAN, ">>/var/tmp/showplan.log") || die "Can't open showplan log: $!";
$dbh->do("exec someproc");    # get the showplan trace for this proc.
$dbh->disconnect;

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...).

Controlling DATETIME output formats

By default DBD::Sybase will return DATETIME and SMALLDATETIME columns in the Nov 15 1998 11:13AM format. This can be changed via a special _date_fmt() function that is accessed via the $dbh->func() method.

The syntax is

$dbh->func($fmt, '_date_fmt');

where $fmt is a string representing the format that you want to apply.

The formats are based on Sybase's standard conversion routines. The following subset of available formats has been implemented:

LONG

Nov 15 1998 11:30:11:496AM

SHORT

Nov 15 1998 11:30AM

DMY4_YYYY

15 Nov 1998

MDY1_YYYY

11/15/1998

DMY1_YYYY

15/11/1998

HMS

11:30:11

Multiple active statements on one $dbh

It is now possible to open multiple active statements on a single database handle. This is done by openeing a new physical connection in $dbh->prepare() if there is already an active statement handle for this $dbh.

This feature has been implemented to improve compatibility with other drivers, but should not be used if you are coding directly to the Sybase driver.

WARNING: This feature should be used with care. In particular, because the SQL statements executed on the second (and subsequent) statement handles are sent over a different physical connection DBD::Sybase cannot guarantee a complete rollback if you have AutoCommit set to OFF.

A future version may make a better attempt at getting this particular problem right.

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 temp 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.

There is also a performance issue: OpenClient creates stored procedures in tempdb for each prepare() call that includes ? placeholders. Creating these objects requires updating system tables in the tempdb database, and can therefore create a performance hotspot if a lot of prepare() statements from multiple clients are executed simultaneously (I have heard that Sybase 11.9.x corrects this hotspot problem.) In general it is better if your application is going to run against Sybase to write ad-hoc stored procedures rather than use the ? placeholders in embedded SQL.

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

You can run out of space in the tempdb database if you use a lot of calls with bind variables (ie ? style placeholders) without closing the connection. On my system, with an 8 MB tempdb database I run out of space after 760 prepare() statements with ? parameters. This is because Sybase creates stored procedures for each prepare() call. So my suggestion is to only use ? style placeholders if you really need them (i.e. if you are going to execute the same prepared statement multiple times).

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-1999 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.