1=head1 DBD::Sybase - Driver and Database Characteristics 2 3=begin docbook 4<sect1></sect1> 5 6=end docbook 7 8 9=head2 Driver Name, Version, Author and Contact Details 10 11This driver summary is for DBD::Sybase version 0.90. 12 13The driver author is Michael Peppler and he can be contacted via the 14dbi-users mailing list, or at mpeppler@peppler.org 15 16 17=head2 Supported Database Versions and Options 18 19The DBD::Sybase module supports Sybase 10.x, 11.x and 12.x, and offers 20limited support for accessing Microsoft MS-SQL 6.x and 7.x (as SP2) 21server. Assuming that OpenClient 10.x or 11.x is available DBD::Sybase 22can be used to connect to Sybase 4.x servers. 23 24In addition DBD::Sybase can be used in combination with the 25FreeTDS reimplementation of OpenClient to connect to MS-SQL or 26Sybase servers from platforms where Sybase OpenClient is not available. 27See http://www.freetds.org for details. 28 29=head2 Connect Syntax 30 31The DSN for DBD::Sybase is of the general form 32"dbi:Sybase:attr=value;attr=value". The following attributes are supported: 33 34=over 8 35 36=item server 37 38Specify the Sybase server to connect to. 39 40=item database 41 42Specify the database within the server that should be made the default 43database (via "use $database"). 44 45=item charset 46 47Specify the client character set to use. Useful if the client's default 48character set is different from the server. Using this will enable 49automatic character conversion from one character set to the other. 50 51=item packetSize 52 53Set the network packetSize. Setting a larger packet size can increase the 54network throughput. See the Sybase documentation on how to use this as 55it may require changing the server configuration values. 56 57=item hostname 58 59Set the hostname that will be stored in the sysprocesses table for this 60process. 61 62=item loginTimeout 63 64Specify the number of seconds that DBI->connect() will wait for a 65response from the Sybase server. The default is 60 seconds. (This 66was added in the 0.14 release.) 67 68=item timeout 69 70Specify the number of seconds that DBD::Sybase will wait for a server 71response. If no response is received within that timeframe the command 72fails with a timeout error and the connection is marked dead. The 73default is to not timeout. Setting a timeout of 0 is the same as no 74timeout. (This was added in the 0.14 release.) 75 76=item interfaces 77 78Specify the location of an alternate I<interfaces> file: 79 80=item scriptName 81 82Specify the name for this connection that will be displayed in sp_who 83(ie in the sysprocesses table in the I<program_name> column). 84 85=item hostname 86 87Specify the hostname that will be displayed by sp_who (and will be stored 88in the hostname column of sysprocesses).. 89 90=item tdsLevel 91 92Specify the TDS protocol level to use when connecting to the server. 93Valid values are CS_TDS_40, CS_TDS_42, CS_TDS_46, CS_TDS_495 and CS_TDS_50. 94In general this is automatically negotiated between the client and the 95server, but in certain cases this may need to be forced to a lower level 96by the client. 97 98 $dbh->DBI->connect("dbi:Sybase:tdsLevel=CS_TDS_42", $user, $password); 99 100B<NOTE>: Setting the tdsLevel below CS_TDS_495 will disable a number of 101features, ?-style placeholders and CHAINED non-AutoCommit mode, in particular. 102 103=item encryptPassword 104 105Specify the use of the client password encryption supported by CT-Lib. 106Specify a value of 1 to use encrypted passwords. 107 108 $dbh->DBI->connect("dbi:Sybase:encryptPassword=1", $user, $password); 109 110=back 111 112 113=head2 Numeric Data Handling 114 115The driver supports INTEGER, SMALLINT, TINYINT, MONEY, SMALLMONEY, 116FLOAT, REAL, DOUBLE, NUMERIC(p,s) and DECIMAL(p,s). 117 118All but the NUMERIC/DECIMAL datatypes are hardware specific, but INTEGER 119is always a 32bit int, SMALLINT is 16bit, TINYINT is 8bit. 120 121Precision for numeric/decimal is from 1 to 38, and scale is from 0 to 38. 122 123Numeric/decimal values are returned as perl strings by default, even if the 124scale is 0 and the precision is small enough to fit in an integer value. 125All other numbers are returned in native format. 126 127 128=head2 String Data Handling 129 130DBD::Sybase supports CHAR/VARCHAR/BINARY/VARBINARY, limited to 255 131characters in length up to version 12.0x. As of 12.5 these datatypes can 132be up to 16K in size - but supporting the larger sizes requires that 133Open Client 12.5 or later be used. Note that the CHAR type is fixed 134length (blank padded). 135 136Sybase automatically converts CHAR and VARCHAR data between the 137character set of the server (see the syscharset system table) and the 138character set of the client, defined by the locale setting of the 139client. The BINARY and VARBINARY types are not converted. 140UTF-8 is supported. 141 142See the OpenClient International Developer's Guide in the Sybase 143OpenClient manuals for more on character set issues. 144 145Strings can be concatenated using the C<+> SQL operator. 146 147 148=head2 Date Data Handling 149 150Sybase supports the DATETIME and SMALLDATETIME values. A DATETIME can 151have a value from Jan 1 1753 to Dec 31, 9999 with a 300th of a second 152resolution. A SMALLDATETIME has a range of Jan 1 1900 to Jun 6 2079 153with a 1 minute resolution. 154 155The current date on the server is obtained with the GETDATE() SQL 156function. 157 158The Sybase date format depends on the locale settings for the client. 159The default date format is based on the 'C' locale: 160 161 Feb 16 1999 12:07PM 162 163In this same locale Sybase understands several input formats in 164addition to the one above: 165 166 2/16/1998 12:07PM 167 1998/02/16 12:07 168 1998-02-16 12:07 169 19980216 12:07 170 171If the time portion is omitted it is set to 00:00. If the date portion 172is omitted it is set to Jan 1 1900. If the century is omitted it is 173assumed to be 1900 if the year is <50 and 2000 if the year >= 50. 174 175You can use the special _date_fmt() private method (accessed via 176$dbh->func()) to change the date input and output format. 177The formats are based on Sybase's standard conversion routines. The 178following subset of available formats has been implemented: 179 180 LONG - Nov 15 1998 11:30:11:496AM 181 SHORT - Nov 15 1998 11:30AM 182 DMY4_YYYY - 15 Nov 1998 183 MDY1_YYYY - 11/15/1998 184 DMY1_YYYY - 15/11/1998 185 HMS - 11:30:11 186 187Use the CONVERT() SQL function to convert date and time values from 188other formats. For example: 189 190 UPDATE a_table 191 SET date_field = CONVERT(datetime_field, '1999-02-21', 105) 192 193CONVERT() is a generic conversion function that can convert to/from 194most datatypes. See the CONVERT() function in Chapter 2 of the Sybase 195Reference Manual. 196 197Arithmetic on date time types is done on dates via the DATEADD(), 198DATEPART(), DATEDIFF() Transact SQL functions. For example: 199 200 SELECT DATEDIFF(ss, date1, date2) 201 202returns the difference in seconds between date1 and date2. 203 204Sybase does not understand time zones at all, except that the GETDATE() 205SQL function returns the date in the time zone that the server is running 206in (via localtime). 207 208The following SQL expression can be used to convert an integer "seconds 209since 1-jan-1970" value ('unix time') to the corresponding database 210date time: 211 212 DATEADD(ss, unixtime_field, 'Jan 1 1970') 213 214Note however that the server does not understand time zones, and will 215therefore give the 'server I<local> unixtime' and not the correct value 216for the GMT time zone. 217 218If you know that the server runs in the same timezone as the client then 219you can use 220 221 use Time::Local; 222 $time_to_database = timegm(localtime($unixtime)); 223 224to convert the unixtime value before sending it to Sybase. 225 226To do the reverse, converting from a database date time value to 'unix 227time', you can use: 228 229 DATEDIFF(ss, 'Jan 1 1970', datetime_field) 230 231The same GMT vs localtime caveat applies in this case. If you know that the 232server runs in the same timezone as the client you can convert the returned 233value to the correct GMT based value with this perl expression: 234 235 use Time::Local; 236 $time = timelocal(gmtime($time_from_database)); 237 238 239 240=head2 LONG/BLOB Data Handling 241 242Sybase supports an IMAGE and a TEXT type for LONG/BLOB data. Each type 243can hold up to 2GB of binary data, including nul characters. The main 244difference between an IMAGE and a TEXT column lies in how the client 245libraries treat the data on input and output. TEXT data is entered and 246returned "as is". IMAGE data is returned as a long hex string, and 247should be entered in the same way. 248 249The default size limit for TEXT/IMAGE data is 32Kb, but this can be 250changed by setting the LongReadLen attribute. 251 252Bind parameters can I<not> be used to insert TEXT or IMAGE data to 253Sybase. 254 255 256=head2 Other Data Handling issues 257 258Sybase does not differentiate between CHAR and VARCHAR or BINARY and 259VARBINARY on returned data, so you will never get a TYPE value of 260SQL_VARCHAR or SQL_VARBINARY when querying the $h->{TYPE} attribute 261for a result set. 262 263Sybase does not automatically convert numbers to strings or strings to 264numbers. You need to explicitly call the C<CONVERT> SQL function. 265However, placeholders don't need special handling because DBD::Sybase 266knows what type each placeholder needs to be. 267 268 269=head2 Transactions, Isolation and Locking 270 271DBD::Sybase supports transactions. 272The default transaction isolation level is 'Read Commited'. 273 274Sybase supports READ COMMITED, READ UNCOMMITED and SERIALIZABLE 275isolation levels. The level be changed per-connection or 276per-statement by executing a "SET TRANSACTION_ISOLATION LEVEL x", 277where x is 0 for READ UNCOMMITED, 1 for READ COMMITED, and 3 for 278SERIALIZABLE. 279 280By default a READ query will aquire a shared lock on each page that it 281reads. This will allow any other process to read from the table, 282but will block any process trying to obtain an exclusive lock (for 283update). The shared lock is only maintained for the time the server needs 284to actually read the page, not for the entire length of the SELECT 285operation. Sybase 11.9.2 and later include optional row-level locking 286("datarows" locking) which can be set on a table by table basis. See the 287Sybase manuals for details. 288 289There is an explicit LOCK TABLE statement (from 11.9.2 onwards) but 290you should not normally need to use it. 291Appending "WITH HOLDLOCK" to a SELECT statement can be used to force an 292exclusive lock to be aquired on a table. It is usually called within a 293transaction. In general this call is not needed. 294 295The correct way to do a multi-table update with Sybase is to wrap the entire 296operation in a transaction. This will ensure that locks will be aquired in 297the correct order, and that no intervening action from another process 298will modify any rows that your operation is currently modifying. 299 300 301=head2 No-Table Expression Select Syntax 302 303To select a constant expression (one that doesn't involve data from a 304database table or view) you can select it without naming a table: 305 306 SELECT getdate() 307 308 309=head2 Table Join Syntax 310 311Outer joins are supported using the =* (right outer join) and *= (left 312outer join) operators: 313 314 SELECT customer_name, order_date 315 FROM customers, orders 316 WHERE customers.cust_id =* orders.cust_id 317 318For all rows in the customers table that have no matching rows in the 319orders table, Sybase returns NULL for any select list expressions 320containing columns from the orders table. 321 322ASE 12.0 and later supports the ANSI syntax for left/right outer 323joins. 324 325=head2 Table and Column Names 326 327The names of Sybase identifiers, such as tables and columns, cannot 328exceed 30 characters in length. 329 330The first character must be an alphabetic character (as defined by the 331current server character set) or _ (underscore). Subsequent characters 332can be alpha, and may include currency symbols, @, # and _. Identifiers 333can't include embedded spaces or the %, !, ^, * or . symbols. In 334addition, identifiers must not be on the "reserved word" list (see the 335Sybase documentation for a complete list). 336 337Table names or column names I<may> be quoted if the B<set quoted_identifier> 338option is turned on. This allows the user to get around the reserved word 339limitation. When this option is set, character strings enclosed in double 340quotes are treated as identifiers, and strings enclosed in single quotes 341are treated as literal strings. 342 343By default identifiers are case-sensitive. This can be turned off by 344changing the default sort order for the B<server>. 345 346National characters can be used in identifier names without quoting. 347 348 349=head2 Case sensitivity of like operator 350 351The Sybase LIKE operator is case sensitive. 352 353The UPPER function can be used to force a case insensitive match, e.g., 354UPPER(name) LIKE 'TOM%' (although that does prevent Sybase from making 355use of any index on the name column to speed up the query). 356 357 358=head2 Row ID 359 360Sybase does not support a pseudo 'row id' column. 361 362 363=head2 Automatic Key or Sequence Generation 364 365Sybase supports an IDENTITY feature for automatic key generation. 366Declaring a table with an IDENTITY column will generate a new value for 367each insert. The values are monotnonically increasing, but are not 368guaranteed to be sequential. 369 370To fetch the value generated and used by the last insert, you can 371 372 SELECT @@IDENTITY 373 374Sybase does not support sequence generators, although ad-hoc stored 375procedures to generate sequence numbers are quite easy to write. See 376http://techinfo.sybase.com/css/techinfo.nsf/DocID/ID=860 for a 377complete explanation of the various possibilities. 378 379 380=head2 Automatic Row Numbering and Row Count Limiting 381 382Sybase does not offer a pseudocolumn that sequentially numbers the rows 383fetched by a select statement. 384 385However, using 386 387 SET ROWCOUNT xxx 388 389will limit the number of rows returned in a SELECT statement I<or> 390the number of rows affected by a DELETE, INSERT or UPDATE statement. 391 392 393=head2 Parameter binding 394 395Parameter binding is directly suported by Sybase. However, there are 396two downsides that one should be aware of: 397 398Firstly, Sybase creates an internal stored procedure for each prepare() 399call that includes ? style parameters. These stored procedures live in 400the tempdb database, and are only destroyed when the connection is 401closed. It is quite possible to run out of tempdb space if a lot of 402prepare() calls with placeholders are being made in a script. 403 404Secondly, because all the temporary stored procedures are created in 405tempdb this causes a potential hot-spot due to the locking of system 406tables in tempdb. This hot-spot is a problem in Sybase 11.5.1 and 407earlier, but has been lifted in 11.9.2 and later releases. 408 409The :1 placeholder style is not supported and the TYPE attribute to 410bind_param is currently ignored, so unsupported values don't generate a 411warning. However, trying to bind a TEXT or IMAGE datatype will fail. 412 413 414=head2 Stored procedures 415 416Sybase stored procedures are written in Transact-SQL, Sybase's 417procedural extension to SQL. 418 419Stored procedures are called exactly the same way as regular SQL, and 420can return the same types of results (ie a SELECT in the stored 421procedure can be retrieved with $sth->fetch). 422 423If the stored procedure returns data via OUTPUT parameters, then these 424must be declared first: 425 426 $sth = $dbh->prepare(qq[ 427 declare \@name varchar(50) 428 exec getName 1234, \@name output 429 ]); 430 431Stored procedures can't be called with bind (?) parameters - so this 432would be illegal: 433 434 $sth = $dbh->prepare("exec my_proc ?"); 435 $sth->execute('foo'); 436 437so use 438 439 $sth = $dbh->prepare("exec my_proc 'foo'"); 440 $sth->execute; 441 442instead. 443 444Because Sybase stored procedures almost always return more than one 445result set you should always make sure to use a loop until the 446B<syb_more_results> is 0: 447 448 do { 449 while($data = $sth->fetch) { 450 ... 451 } 452 } while($sth->{syb_more_results}); 453 454 455=head2 Table Metadata 456 457DBD::Sybase supports the table_info method. 458 459The syscolumns table has one row per column per table. See the 460definitions of the Sybase system tables for details. However, the 461easiest method is to use the sp_help stored procedure. 462 463The easiest way to get detailed information about the indexes of a 464table is to use the sp_helpindex (or sp_helpkey) stored procedure. 465 466 467=head2 Driver-specific attributes and methods 468 469DBD::Sybase has the following driver specific database handle attributes: 470 471=over 8 472 473=item syb_show_sql 474 475If set then the current statement is included in the string returned by 476$dbh->errstr. 477 478=item syb_show_eed 479 480If set, then extended error information is included in the string returned 481by $dbh->errstr. Extended error information include the index causing a 482duplicate insert to fail, for example. 483 484=item syb_err_handler 485 486This attribute is used to set an ad-hoc error handler callback (ie a perl 487subroutine) that gets called before the normal error handler does it's job. 488If this subroutine returns 0 then the error is ignored. This is useful 489for handling PRINT statements in Transact-SQL, for handling messages 490from the Backup Server, showplan output, dbcc output, etc. 491 492The subroutine is called with 7 parameters: the Sybase error number, 493the severity, the state, the line number in the SQL batch, the server name 494(if available), the stored procedure name (if available), and the message 495text. 496 497Example: 498 499 %showplan_msgs = map { $_ => 1} (3612 .. 3615, 6201 .. 6225); 500 sub err_handler { 501 my($err, $sev, $state, $line, $server, $proc, $msg) = @_; 502 503 if($showplan_msgs{$err}) { # it's a showplan message 504 print SHOWPLAN "$err - $msg\n"; 505 return 0; # This is not an error 506 } 507 508 return 1; 509 } 510 511 $dbh = DBI->connect('dbi:Sybase:server=troll', 'sa', ''); 512 $dbh->{syb_err_handler} = \&err_handler; 513 $dbh->do("set showplan on"); 514 open(SHOWPLAN, ">>/var/tmp/showplan.log") || die "Can't open showplan log: $!"; 515 $dbh->do("exec someproc"); # get the showplan trace for this proc. 516 $dbh->disconnect; 517 518=item syb_flush_finish 519 520If $dbh->{syb_flush_finish} is set then $dbh->finish will drain 521any results remaining for the current command by actually fetching them. 522The default behaviour is to issue a ct_cancel(CS_CANCEL_ALL), but this 523I<appears> to cause connections to hang or to fail in certain cases (although 524I've never witnessed this myself.) 525 526=item syb_dynamic_supported 527 528This is a read-only attribute that returns TRUE if the dataserver 529you are connected to supports ?-style placeholders. Typically placeholders are 530not supported when using DBD::Sybase to connect to a MS-SQL server. 531 532=item syb_chained_txn 533 534If set then we use CHAINED transactions when AutoCommit is off. 535Otherwise we issue an explicit BEGIN TRAN as needed. The default is off. 536 537This attribute should usually be used only during the connect() call: 538 539 $dbh = DBI->connect('dbi:Sybase:', $user, $pwd, {syb_chained_txn => 1}); 540 541Using it at any other time with B<AutoCommit> turned B<off> will 542B<force a commit> on the current handle. 543 544=item syb_use_bin_0x 545 546If set, BINARY and VARBINARY values are prefixed with '0x' in the 547result. Default is off. 548 549=item syb_binary_images 550 551If set, IMAGE data is returned in raw binary format. Otherwise the data is 552converted to a long hex string. The default is off. 553 554=item syb_quoted_identifier (bool) 555 556If set, then identifiers that would normally clash with Sybase reserved 557words can be quoted using C<"identifier">. In this case strings must 558be quoted with the single quote. 559 560Default is for this attribute to be B<off>. 561 562=item syb_rowcount (int) 563 564Setting this attribute to non-0 will limit the number of rows returned by 565a I<SELECT>, or affected by an I<UPDATE> or I<DELETE> statement to the 566I<rowcount> value. Setting it back to 0 clears the limit. 567 568Default is for this attribute to be B<0>. 569 570=item syb_do_proc_status (bool) 571 572Setting this attribute causes $sth->execute() to fetch the return status 573of any executed stored procs in the SQL being executed. If the return 574status is non-0 then $sth->execute() will report that the operation 575failed (ie it will return C<undef>) 576 577Setting this attribute does B<NOT> affect existing $sth handles, only 578those that are created after setting it. To change the behavior of 579an existing $sth handle use $sth->{syb_do_proc_status}. 580 581The default is for this attribute to be B<off>. 582 583=item syb_oc_version (string) 584 585Returns the identification string of the version of Client Library that 586this binary is currently using. This is a read-only attribute. 587 588For example: 589 590 troll (7:59AM):348 > perl -MDBI -e '$dbh = DBI->connect("dbi:Sybase:", "sa"); print "$dbh->{syb_oc_version}\n";' 591 Sybase Client-Library/11.1.1/P/Linux Intel/Linux 2.2.5 i586/1/OPT/Mon Jun 7 07:50:21 1999 592 593This is very useful information to have when reporting a problem. 594 595 596=back 597 598And the following driver specific statement handle attributes: 599 600=over 8 601 602=item syb_more_results 603 604See the discussion on handling multiple result sets above. 605 606=item syb_result_type 607 608Returns the numeric result type of the current result set. Useful when 609executing stored procedurs to determine what type of information is 610currently fetchable (normal select rows, output parameters, status results, 611etc...). 612 613=back 614 615 616One private method is provided: 617 618=over 8 619 620=item _date_fmt 621 622Set the default date conversion and display formats. See the 623description elsewhere in this document. 624 625=back 626 627 628=head2 Positioned updates and deletes 629 630Sybase does not support positioned updates or deletes. 631 632 633=head2 Differences from the DBI specification 634 635Note that DBD::Sybase does not fully parse the statement until 636it's executed. Thus attributes like $sth->{NUM_OF_FIELDS} are not 637available until after $sth->execute has been called. This is valid 638behaviour but is important to note when porting applications 639originally written for other drivers. 640 641 642=head2 URLs to more database/driver specific information 643 644 http://www.sybase.com 645 http://techinfo.sybase.com 646 http://sybooks.sybase.com 647 648 649=head2 Concurrent use of multiple handles 650 651DBD::Sybase supports up to 25 concurrent database 652connections to one or more databases. 653 654It is not normally possible for Sybase clients to prepare/execute 655a new statement handle while still fetching data from another 656statment handle associated with the same database handle. However, 657DBD::Sybase emulates this by opening a new connection that will 658automatically be closed when the new statement handle is destroyed. 659You should be aware that there are some subtle but significant 660transaction issues with this approach. 661 662=head2 Other Significant Database or Driver Features 663 664Sybase and DBD::Sybase allow multiple statements to be prepared with 665one call and then executed with one call. The results are fed back to 666the client as a stream of tabular data. Stored procedures can also 667return a stream of multiple data sets. Each distinct set of results 668is treated as a normal single result set so C<fetch> returns undef at 669the end of each set. To see if there are more data sets to follow the 670C<syb_more_results> attribute can be checked. A typical loop making use 671of this Sybase specific feature looks like: 672 673 do { 674 while($d = $sth->fetch) { 675 ... do something with the data 676 } 677 } while($sth->{syb_more_results}); 678 679Sybase also has rich and powerful stored procedure and trigger 680functionality and encourages you to use them. 681 682=cut 683 684# This driver summary for DBD::Sybase is Copyright (c) 1999 Tim Bunce 685# and Michael Peppler. 686# $Id: dbd-sybase.pod,v 1.8 2003/03/31 23:55:11 mpeppler Exp $ 687 688