- NAME
- SYNOPSIS
- DESCRIPTION
- Connecting to Sybase
- Handling Multiple Result Sets
- $sth->execute() failure mode behavior
- Sybase Specific Attributes
- Controlling DATETIME output formats
- Retrieving OUTPUT parameters from stored procedures
- Multiple active statements on one $dbh
- Working with IMAGE and TEXT columns
- AutoCommit, Transactions and Transact-SQL
- Behavior of $dbh->last_insert_id
- Using ? Placeholders & bind parameters to $sth->execute
- Calling Stored Procedures
- Other Private Methods
- Experimental Bulk-Load Functionality
- Using DBD::Sybase with MS-SQL
- nsql
- Multi-Threading
- BUGS
- SEE ALSO
- AUTHOR
- COPYRIGHT
- ACKNOWLEDGEMENTS
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';
}
my $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.
host =item port
-
If you built DBD::Sybase with OpenClient 12.5.1 or later, then you can use the host and port values to define the server you want to connect to. This will by-pass the server name lookup in the interfaces file. This is useful in the case where the server hasn't been entered in the interfaces file.
$dbh = DBI->connect("dbi:Sybase:host=db1.domain.com;port=4100",
$user, $passwd);
maxConnect
-
By default DBD::Sybase (and the underlying OpenClient libraries) is limited to openening 25 simultaneous connections to one or more database servers. If you need more than 25 connections at the same time, you can use the maxConnect option to increase this number.
$dbh = DBI->connect("dbi:Sybase:maxConnect=100",
$user, $passwd);
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);Note that the language has to have been installed on the server (via langinstall or sp_addlanguage) for this to work. If the language is not installed the session will default to the default language of the server.
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.
$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 CHAINED non-AutoCommit mode, in particular.
encryptPassword
-
Specify the use of the client password encryption supported by CT-Lib. Specify a value of 1 to use encrypted passwords.
$dbh=DBI->connect("dbi:Sybase:encryptPassword=1", $user, $password);
kerberos
-
Note: Requires OpenClient 11.1.1 or later.
Sybase and OpenClient can use Kerberos to perform network-based login. If you use Kerberos for authentication you can use this feature and pass a kerberos serverprincipal using the
kerberos=value
parameter:$dbh = DBI->connect("dbi:Sybase:kerberos=$serverprincipal", '', '');
In addition, if you have a system for retrieving Kerberos serverprincipals at run-time you can tell DBD::Sybase to call a perl subroutine to get the serverprincipal from connect():
sub sybGetPrinc {
my $srv = shift;
return the serverprincipal...
}
$dbh = DBI->connect('dbi:Sybase:server=troll', '', '', { syb_kerberos_serverprincipal => /&sybGetPrinc });The subroutine will be called with one argument (the server that we will connect to, using the normal Sybase behavior of checking the DSQUERY environment variable if no server is specified in the connect()) and is expected to return a string (the Kerberos serverprincipal) to the caller.
sslCAFile
-
Specify the location of an alternate trusted.txt file for SSL connection negotiation:
$dbh->DBI->connect("dbi:Sybase:sslCAFile=/usr/local/sybase/trusted.txt.ENGINEERING", $user, $password);
bulkLogin
-
Set this to 1 if the connection is going to be used for a bul