1# -*-Perl-*- 2# $Id: Sybase.pm,v 1.119 2017/09/10 14:31:45 mpeppler Exp $ 3 4# Copyright (c) 1996-2011 Michael Peppler 5# 6# You may distribute under the terms of either the GNU General Public 7# License or the Artistic License, as specified in the Perl README file. 8# 9# Based on DBD::Oracle Copyright (c) 1994,1995,1996,1997 Tim Bunce 10 11{ 12 13 package DBD::Sybase; 14 15 use DBI (); 16 use DynaLoader (); 17 use Exporter (); 18 19 use Sys::Hostname (); 20 21 @ISA = qw(DynaLoader Exporter); 22 23 @EXPORT = qw(CS_ROW_RESULT CS_CURSOR_RESULT CS_PARAM_RESULT 24 CS_STATUS_RESULT CS_MSG_RESULT CS_COMPUTE_RESULT); 25 26 $hostname = Sys::Hostname::hostname(); 27 $init_done = 0; 28 $VERSION = '1.16'; 29 my $Revision = substr( q$Revision: 1.119 $, 10 ); 30 31 require_version DBI 1.30; 32 33 # dl_open() calls need to use the RTLD_GLOBAL flag if 34 # you are going to use the Kerberos libraries. 35 # There are systems / OSes where this does not work (AIX 5.x, for example) 36 # set to 1 to get RTLD_GLOBAL turned on. 37 sub dl_load_flags { 0x00 } 38 39 bootstrap DBD::Sybase $VERSION; 40 41 $drh = undef; # holds driver handle once initialised 42 43 sub driver { 44 return $drh if $drh; 45 my ( $class, $attr ) = @_; 46 $class .= "::dr"; 47 ($drh) = DBI::_new_drh( 48 $class, 49 { 50 'Name' => 'Sybase', 51 'Version' => $VERSION, 52 'Attribution' => 'Sybase DBD by Michael Peppler', 53 } 54 ); 55 56 if ( $DBI::VERSION >= 1.37 && !$DBD::Sybase::init_done ) { 57 DBD::Sybase::db->install_method('syb_nsql'); 58 DBD::Sybase::db->install_method('syb_date_fmt'); 59 DBD::Sybase::db->install_method('syb_isdead'); 60 DBD::Sybase::st->install_method('syb_ct_get_data'); 61 DBD::Sybase::st->install_method('syb_ct_data_info'); 62 DBD::Sybase::st->install_method('syb_ct_send_data'); 63 DBD::Sybase::st->install_method('syb_ct_prepare_send'); 64 DBD::Sybase::st->install_method('syb_ct_finish_send'); 65 DBD::Sybase::st->install_method('syb_output_params'); 66 DBD::Sybase::st->install_method('syb_describe'); 67 ++$DBD::Sybase::init_done; 68 } 69 70 $drh; 71 } 72 73 sub CLONE { 74 undef $drh; 75 } 76 77 1; 78} 79 80{ 81 82 package DBD::Sybase::dr; # ====== DRIVER ====== 83 use strict; 84 85 sub connect { 86 my ( $drh, $dbase, $user, $auth, $attr ) = @_; 87 my $server = $dbase || $ENV{DSQUERY} || 'SYBASE'; 88 89 my ($this) = DBI::_new_dbh( 90 $drh, 91 { 92 'Name' => $server, 93 'Username' => $user, 94 'CURRENT_USER' => $user, 95 } 96 ); 97 98 DBD::Sybase::db::_login( $this, $server, $user, $auth, $attr ) 99 or return undef; 100 101 return $this; 102 } 103 104 sub data_sources { 105 my @s; 106 if ( $^O eq 'MSWin32' ) { 107 open( INTERFACES, "$ENV{SYBASE}/ini/sql.ini" ) or return; 108 @s = map { /\[(\S+)\]/i; "dbi:Sybase:server=$1" } grep /\[/i, 109 <INTERFACES>; 110 close(INTERFACES); 111 } 112 else { 113 open( INTERFACES, "$ENV{SYBASE}/interfaces" ) or return; 114 @s = map { /^(\S+)/i; "dbi:Sybase:server=$1" } grep /^[^\s\#]/i, 115 <INTERFACES>; 116 close(INTERFACES); 117 } 118 119 return @s; 120 } 121} 122 123{ 124 125 package DBD::Sybase::db; # ====== DATABASE ====== 126 use strict; 127 128 use DBI qw(:sql_types); 129 use Carp; 130 131 sub prepare { 132 my ( $dbh, $statement, @attribs ) = @_; 133 134 # create a 'blank' sth 135 136 my $sth = DBI::_new_sth( $dbh, { 'Statement' => $statement, } ); 137 138 DBD::Sybase::st::_prepare( $sth, $statement, @attribs ) 139 or return undef; 140 141 $sth; 142 } 143 144 sub tables { 145 my $dbh = shift; 146 my $catalog = shift; 147 my $schema = shift || '%'; 148 my $table = shift || '%'; 149 my $type = shift || '%'; 150 $type =~ s/[\'\"\s]//g; # strip quotes and spaces 151 if ( $type =~ /,/ ) { # multiple types 152 $type = '[' 153 . join( '', map { substr( $_, 0, 1 ) } split /,/, $type ) . ']'; 154 } 155 else { 156 $type = substr( $type, 0, 1 ); 157 } 158 $type =~ s/T/U/; 159 160 my $sth; 161 if ( $catalog and $catalog ne '%' ) { 162 $sth = 163 $dbh->prepare( 164"select o.name from $catalog..sysobjects o, $catalog..sysusers u where o.type like '$type' and o.name like '$table' and o.uid = u.uid and u.name like '$schema'" 165 ); 166 } 167 else { 168 $sth = 169 $dbh->prepare( 170"select o.name from sysobjects o, sysusers u where o.type like '$type' and o.name like '$table' and o.uid = u.uid and u.name like '$schema'" 171 ); 172 } 173 174 $sth->execute; 175 my @names; 176 my $dat; 177 while ( $dat = $sth->fetch ) { 178 push( @names, $dat->[0] ); 179 } 180 @names; 181 } 182 183 # NOTE - RaiseError & PrintError is turned off while we are inside this 184 # function, so we must check for any error, and return immediately if 185 # any error is found. 186 # XXX add optional deadlock detection? 187 sub do { 188 my ( $dbh, $statement, $attr, @params ) = @_; 189 190 my $sth = $dbh->prepare( $statement, $attr ) or return undef; 191 $sth->execute(@params) or return undef; 192 return undef if $sth->err; 193 if ( defined( $sth->{syb_more_results} ) ) { 194 { 195 while ( my $dat = $sth->fetch ) { 196 return undef if $sth->err; 197 198 # XXX do something intelligent here... 199 } 200 redo if $sth->{syb_more_results}; 201 } 202 } 203 my $rows = $sth->rows; 204 205 ( $rows == 0 ) ? "0E0" : $rows; 206 } 207 208 # This will only work if the statement handle used to do the insert 209 # has been properly freed. Otherwise this will try to fetch @@identity 210 # from a different (new!) connection - which is obviously wrong. 211 sub last_insert_id { 212 my ( $dbh, $catalog, $schema, $table, $field, $attr ) = @_; 213 214 # parameters are ignored. 215 216 my $sth = $dbh->prepare('select @@identity'); 217 if ( !$sth->execute ) { 218 return undef; 219 } 220 my $value; 221 ($value) = $sth->fetchrow_array; 222 $sth->finish; 223 224 return $value; 225 } 226 227 sub table_info { 228 my $dbh = shift; 229 my $catalog = $dbh->quote(shift); 230 my $schema = $dbh->quote(shift); 231 my $table = $dbh->quote(shift); 232 my $type = $dbh->quote(shift); 233 234 my $sth = $dbh->prepare("sp_tables $table, $schema, $catalog, $type"); 235 236 # Another possibility would be: 237 # select TABLE_QUALIFIER = NULL 238 # , TABLE_OWNER = u.name 239 # , TABLE_NAME = o.name 240 # , TABLE_TYPE = o.type -- XXX 241 # , REMARKS = NULL 242 # from sysobjects o 243 # , sysusers u 244 # where o.type in ('U', 'V', 'S') 245 # and o.uid = u.uid 246 247 $sth->execute; 248 $sth; 249 } 250 251 { 252 253 my $names = [ 254 qw(TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE 255 TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS 256 NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE 257 SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION 258 IS_NULLABLE 259 ) 260 ]; 261 262 # Technique of using DBD::Sponge borrowed from DBD::mysql... 263 sub column_info { 264 my $dbh = shift; 265 my $catalog = $dbh->quote(shift); 266 my $schema = $dbh->quote(shift); 267 my $table = $dbh->quote(shift); 268 my $column = $dbh->quote(shift); 269 270 my $sth = 271 $dbh->prepare("sp_columns $table, $schema, $catalog, $column"); 272 return undef unless $sth; 273 274 if ( !$sth->execute() ) { 275 return DBI::set_err( $dbh, $sth->err(), $sth->errstr() ); 276 } 277 my @cols; 278 while ( my $d = $sth->fetchrow_arrayref() ) { 279 push( @cols, [ @$d[ 0 .. 11 ], @$d[ 14 .. 19 ] ] ); 280 } 281 my $dbh2; 282 if ( !( $dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'} ) ) { 283 $dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'} = 284 DBI->connect("DBI:Sponge:"); 285 if ( !$dbh2 ) { 286 DBI::set_err( $dbh, 1, $DBI::errstr ); 287 return undef; 288 } 289 } 290 my $sth2 = $dbh2->prepare( 291 "SHOW COLUMNS", 292 { 293 'rows' => \@cols, 294 'NAME' => $names, 295 'NUM_OF_FIELDS' => scalar(@$names) 296 } 297 ); 298 if ( !$sth2 ) { 299 DBI::set_err( $sth2, $dbh2->err(), $dbh2->errstr() ); 300 } 301 $sth2->execute; 302 $sth2; 303 } 304 } 305 306 sub primary_key_info { 307 my $dbh = shift; 308 my $catalog = $dbh->quote(shift); # == database in Sybase terms 309 my $schema = $dbh->quote(shift); # == owner in Sybase terms 310 my $table = $dbh->quote(shift); 311 312 my $sth = $dbh->prepare("sp_pkeys $table, $schema, $catalog"); 313 314 $sth->execute; 315 $sth; 316 } 317 318 sub foreign_key_info { 319 my $dbh = shift; 320 my $pk_catalog = $dbh->quote(shift); # == database in Sybase terms 321 my $pk_schema = $dbh->quote(shift); # == owner in Sybase terms 322 my $pk_table = $dbh->quote(shift); 323 my $fk_catalog = $dbh->quote(shift); # == database in Sybase terms 324 my $fk_schema = $dbh->quote(shift); # == owner in Sybase terms 325 my $fk_table = $dbh->quote(shift); 326 327 my $sth = 328 $dbh->prepare( 329"sp_fkeys $pk_table, $pk_catalog, $pk_schema, $fk_table, $fk_catalog, $fk_schema" 330 ); 331 332 $sth->execute; 333 $sth; 334 } 335 336 sub statistics_info { 337 my $dbh = shift; 338 my $catalog = $dbh->quote(shift); # == database in Sybase terms 339 my $schema = $dbh->quote(shift); # == owner in Sybase terms 340 my $table = $dbh->quote(shift); 341 my $is_unique = shift; 342 my $quick = shift; 343 344 my $sth = 345 $dbh->prepare( 346"sp_indexes \@\@servername, $table, $catalog, $schema, NULL, $is_unique" 347 ); 348 349 $sth->execute; 350 $sth; 351 } 352 353 sub ping_pl { # old code - now implemented by syb_ping() in dbdimp.c 354 my $dbh = shift; 355 return 0 if DBD::Sybase::db::_isdead($dbh); 356 357 # Use "select 1" suggested by Henri Asseily. 358 my $sth = $dbh->prepare("select 1"); 359 360 return 0 if !$sth; 361 362 my $rc = $sth->execute; 363 364 # Changed && to || for 1.07. 365 return 0 if ( !defined($rc) || DBD::Sybase::db::_isdead($dbh) ); 366 367 $sth->finish; 368 return 1; 369 } 370 371 sub type_info_all { 372 my ($dbh) = @_; 373 374 # Calling sp_datatype_info returns the appropriate data for the server that 375 # we are currently connected to. 376 # In general the data is static, so it's not really necessary, but ASE 12.5 377 # introduces some changes, in particular char/varchar max lenghts that depend 378 # on the server's page size. 12.5.1 introduces the DATE and TIME datatypes. 379 my $sth = $dbh->prepare("sp_datatype_info"); 380 my $data; 381 if ( $sth->execute ) { 382 $data = $sth->fetchall_arrayref; 383 } 384 my $ti = [ 385 { 386 TYPE_NAME => 0, 387 DATA_TYPE => 1, 388 PRECISION => 2, 389 LITERAL_PREFIX => 3, 390 LITERAL_SUFFIX => 4, 391 CREATE_PARAMS => 5, 392 NULLABLE => 6, 393 CASE_SENSITIVE => 7, 394 SEARCHABLE => 8, 395 UNSIGNED_ATTRIBUTE => 9, 396 MONEY => 10, 397 AUTO_INCREMENT => 11, 398 LOCAL_TYPE_NAME => 12, 399 MINIMUM_SCALE => 13, 400 MAXIMUM_SCALE => 14, 401 sql_data_type => 15, 402 sql_datetime_sub => 16, 403 num_prec_radix => 17, 404 interval_precision => 18, 405 }, 406 ]; 407 408 # ASE 11.x only returns 13 columns: 409 my $c; 410 if ( ( $c = scalar( @{ $data->[0] } ) ) < 19 ) { 411 foreach ( keys( %{ $ti->[0] } ) ) { 412 if ( $ti->[0]->{$_} >= $c ) { 413 delete( $ti->[0]->{$_} ); 414 } 415 } 416 } 417 push( @$ti, @$data ); 418 419 return $ti; 420 } 421 422 # First straight port of DBlib::nsql. 423 # mpeppler, 2/19/01 424 # Updated by Merijn Broeren 4/17/2007 425 # This version *can* handle ? placeholders 426 sub nsql { 427 my ( $dbh, $sql, $type, $callback, $option ) = @_; 428 my ( @res, %resbytype ); 429 my $retrycount = $dbh->FETCH('syb_deadlock_retry'); 430 my $retrysleep = $dbh->FETCH('syb_deadlock_sleep') || 60; 431 my $retryverbose = $dbh->FETCH('syb_deadlock_verbose'); 432 my $nostatus = $dbh->FETCH('syb_nsql_nostatus'); 433 434 $option = $callback 435 if ref($callback) eq 'HASH' 436 and ref($option) ne 'HASH'; 437 my $bytype = $option->{bytype} || 0; 438 my $merge = $bytype eq 'merge'; 439 440 my @default_types = ( 441 DBD::Sybase::CS_ROW_RESULT(), DBD::Sybase::CS_CURSOR_RESULT(), 442 DBD::Sybase::CS_PARAM_RESULT(), DBD::Sybase::CS_MSG_RESULT(), 443 DBD::Sybase::CS_COMPUTE_RESULT() 444 ); 445 my $oktypes = $option->{oktypes} 446 || ( $nostatus 447 ? [@default_types] 448 : [ @default_types, DBD::Sybase::CS_STATUS_RESULT() ] ); 449 my %oktypes = map { ( $_ => 1 ) } @$oktypes; 450 451 my @params = $option->{arglist} ? @{ $option->{arglist} } : (); 452 453 if ( ref $type ) { 454 $type = ref $type; 455 } 456 elsif ( not defined $type ) { 457 $type = ""; 458 } 459 460 my $sth = $dbh->prepare($sql); 461 return unless $sth; 462 463 my $raiserror = $dbh->FETCH('RaiseError'); 464 465 my $errstr; 466 my $err; 467 468 # Rats - RaiseError doesn't seem to work inside of this routine. 469 # So we fake it with lots of die() statements. 470 # $sth->{RaiseError} = 1; 471 472 DEADLOCK: 473 { 474 475 # Initialize $err before each iteration through this loop. 476 # Otherwise, we inherit the value from the previous failure. 477 478 $err = undef; 479 480 # ditto for @res, %resbytype 481 @res = (); 482 %resbytype = (); 483 484 # Use RaiseError technique to throw a fatal error if anything goes 485 # wrong in the execute or fetch phase. 486 eval { 487 $sth->execute(@params) || die $sth->errstr; 488 { 489 my $result_type = $sth->{syb_result_type}; 490 my ( @set, $data ); 491 if ( not exists $oktypes{$result_type} ) { 492 while ( $data = $sth->fetchrow_arrayref ) { 493 ; # do not include return status rows.. 494 } 495 } 496 elsif ( $type eq "HASH" ) { 497 while ( $data = $sth->fetchrow_hashref ) { 498 die $sth->errstr if ( $sth->err ); 499 if ( ref $callback eq "CODE" ) { 500 unless ( $callback->(%$data) ) { 501 return; 502 } 503 } 504 else { 505 push( @set, {%$data} ); 506 } 507 } 508 } 509 elsif ( $type eq "ARRAY" ) { 510 while ( $data = $sth->fetchrow_arrayref ) { 511 die $sth->errstr if ( $sth->err ); 512 if ( ref $callback eq "CODE" ) { 513 unless ( $callback->(@$data) ) { 514 return; 515 } 516 } 517 else { 518 push( @set, 519 ( @$data == 1 ? $$data[0] : [@$data] ) ); 520 } 521 } 522 } 523 else { 524 525 # If you ask for nothing, you get nothing. But suck out 526 # the data just in case. 527 while ( $data = $sth->fetch ) { 1; } 528 529 # NB this is actually *counting* the result sets which are not ignored above 530 $res[0]++; # Return non-null (true) 531 } 532 533 die $sth->errstr if ( $sth->err ); 534 535 if (@set) { 536 if ($merge) { 537 $resbytype{$result_type} ||= []; 538 push @{ $resbytype{$result_type} }, @set; 539 } 540 elsif ($bytype) { 541 push @res, { $result_type => [@set] }; 542 } 543 else { 544 push @res, @set; 545 } 546 } 547 548 redo if $sth->{syb_more_results}; 549 } 550 }; 551 552 # If $@ is set then something failed in the eval{} call above. 553 if ($@) { 554 $errstr = $@; 555 $err = $sth->err || $dbh->err; 556 if ( $retrycount && $err == 1205 ) { 557 if ( $retrycount < 0 || $retrycount-- ) { 558 carp "SQL deadlock encountered. Retrying...\n" 559 if $retryverbose; 560 sleep($retrysleep); 561 redo DEADLOCK; 562 } 563 else { 564 carp "SQL deadlock retry failed ", 565 $dbh->FETCH('syb_deadlock_retry'), 566 " times. Aborting.\n" 567 if $retryverbose; 568 last DEADLOCK; 569 } 570 } 571 572 last DEADLOCK; 573 } 574 } 575 576 # 577 # If we picked any sort of error, then don't feed the data back. 578 # 579 if ($err) { 580 if ($raiserror) { 581 croak($errstr); 582 } 583 return; 584 } 585 elsif ( ref $callback eq "CODE" ) { 586 return 1; 587 } 588 else { 589 if ($merge) { 590 return %resbytype; 591 } 592 else { 593 return @res; 594 } 595 } 596 } 597 598 if ( $DBI::VERSION >= 1.37 ) { 599 *syb_nsql = *nsql; 600 } 601} 602 603{ 604 605 package DBD::Sybase::st; # ====== STATEMENT ====== 606 use strict; 607 608 sub syb_output_params { 609 my ($sth) = @_; 610 611 my @results; 612 my $status; 613 614 { 615 while ( my $d = $sth->fetch ) { 616 617 # The tie() doesn't work here, so call the FETCH method 618 # directly.... 619 if ( $sth->FETCH('syb_result_type') == 4042 ) { 620 push( @results, @$d ); 621 } 622 elsif ( $sth->FETCH('syb_result_type') == 4043 ) { 623 $status = $d->[0]; 624 } 625 } 626 redo if $sth->FETCH('syb_more_results'); 627 } 628 629 # XXX What to do if $status != 0??? 630 631 @results; 632 } 633 634 sub exec_proc { 635 my ($sth) = @_; 636 637 my @results; 638 my $status; 639 640 $sth->execute || return undef; 641 642 { 643 while ( my $d = $sth->fetch ) { 644 645 # The tie() doesn't work here, so call the FETCH method 646 # directly.... 647 if ( $sth->FETCH('syb_result_type') == 4043 ) { 648 $status = $d->[0]; 649 } 650 } 651 redo if $sth->FETCH('syb_more_results'); 652 } 653 654 # XXX What to do if $status != 0??? 655 656 $status; 657 } 658 659} 660 6611; 662 663__END__ 664 665=head1 NAME 666 667DBD::Sybase - Sybase database driver for the DBI module 668 669=head1 SYNOPSIS 670 671 use DBI; 672 673 $dbh = DBI->connect("dbi:Sybase:", $user, $passwd); 674 675 # See the DBI module documentation for full details 676 677=head1 DESCRIPTION 678 679DBD::Sybase is a Perl module which works with the DBI module to provide 680access to Sybase databases. 681 682=head1 Connecting to Sybase 683 684=head2 The interfaces file 685 686The DBD::Sybase module is built on top of the Sybase I<Open Client Client 687Library> API. This library makes use of the Sybase I<interfaces> file 688(I<sql.ini> on Win32 machines) to make a link between a logical 689server name (e.g. SYBASE) and the physical machine / port number that 690the server is running on. The OpenClient library uses the environment 691variable B<SYBASE> to find the location of the I<interfaces> file, 692as well as other files that it needs (such as locale files). The B<SYBASE> 693environment is the path to the Sybase installation (eg '/usr/local/sybase'). 694If you need to set it in your scripts, then you I<must> set it in a 695C<BEGIN{}> block: 696 697 BEGIN { 698 $ENV{SYBASE} = '/opt/sybase/11.0.2'; 699 } 700 701 my $dbh = DBI->connect('dbi:Sybase:', $user, $passwd); 702 703 704=head2 Specifying the server name 705 706The server that DBD::Sybase connects to defaults to I<SYBASE>, but 707can be specified in two ways. 708 709You can set the I<DSQUERY> environement variable: 710 711 $ENV{DSQUERY} = "ENGINEERING"; 712 $dbh = DBI->connect('dbi:Sybase:', $user, $passwd); 713 714Or you can pass the server name in the first argument to connect(): 715 716 $dbh = DBI->connect("dbi:Sybase:server=ENGINEERING", $user, $passwd); 717 718=head2 Specifying other connection specific parameters 719 720It is sometimes necessary (or beneficial) to specify other connection 721properties. Currently the following are supported: 722 723=over 4 724 725=item server 726 727Specify the server that we should connect to. 728 729 $dbh = DBI->connect("dbi:Sybase:server=BILLING", 730 $user, $passwd); 731 732The default server is I<SYBASE>, or the value of the I<$DSQUERY> environment 733variable, if it is set. 734 735=item host 736 737=item port 738 739If you built DBD::Sybase with OpenClient 12.5.1 or later, then you can 740use the I<host> and I<port> values to define the server you want to 741connect to. This will by-pass the server name lookup in the interfaces file. 742This is useful in the case where the server hasn't been entered in the 743interfaces file. 744 745 $dbh = DBI->connect("dbi:Sybase:host=db1.domain.com;port=4100", 746 $user, $passwd); 747 748=item maxConnect 749 750By default DBD::Sybase (and the underlying OpenClient libraries) is limited 751to openening 25 simultaneous connections to one or more database servers. 752If you need more than 25 connections at the same time, you can use the 753I<maxConnect> option to increase this number. 754 755 $dbh = DBI->connect("dbi:Sybase:maxConnect=100", 756 $user, $passwd); 757 758 759=item database 760 761Specify the database that should be made the default database. 762 763 $dbh = DBI->connect("dbi:Sybase:database=sybsystemprocs", 764 $user, $passwd); 765 766This is equivalent to 767 768 $dbh = DBI->connect('dbi:Sybase:', $user, $passwd); 769 $dbh->do("use sybsystemprocs"); 770 771 772=item charset 773 774Specify the character set that the client uses. 775 776 $dbh = DBI->connect("dbi:Sybase:charset=iso_1", 777 $user, $passwd); 778 779The default charset used depends on the locale that the application runs 780in. If you wish to interact with unicode varaiables (see syb_enable_utf8, below) then 781you should set charset=utf8. Note however that this means that Sybase will expect all 782data sent to it for char/varchar columns to be encoded in utf8 (e.g. sending iso8859-1 characters 783like e-grave, etc). 784 785=item language 786 787Specify the language that the client uses. 788 789 $dbh = DBI->connect("dbi:Sybase:language=us_english", 790 $user, $passwd); 791 792Note that the language has to have been installed on the server (via 793langinstall or sp_addlanguage) for this to work. If the language is not 794installed the session will default to the default language of the 795server. 796 797=item packetSize 798 799Specify the network packet size that the connection should use. Using a 800larger packet size can increase performance for certain types of queries. 801See the Sybase documentation on how to enable this feature on the server. 802 803 $dbh = DBI->connect("dbi:Sybase:packetSize=8192", 804 $user, $passwd); 805 806=item interfaces 807 808Specify the location of an alternate I<interfaces> file: 809 810 $dbh = DBI->connect("dbi:Sybase:interfaces=/usr/local/sybase/interfaces", 811 $user, $passwd); 812 813=item loginTimeout 814 815Specify the number of seconds that DBI->connect() will wait for a 816response from the Sybase server. If the server fails to respond before the 817specified number of seconds the DBI->connect() call fails with a timeout 818error. The default value is 60 seconds, which is usually enough, but on a busy 819server it is sometimes necessary to increase this value: 820 821 $dbh = DBI->connect("dbi:Sybase:loginTimeout=240", # wait up to 4 minutes 822 $user, $passwd); 823 824 825=item timeout 826 827Specify the number of seconds after which any Open Client calls will timeout 828the connection and mark it as dead. Once a timeout error has been received 829on a connection it should be closed and re-opened for further processing. 830 831Setting this value to 0 or a negative number will result in an unlimited 832timeout value. See also the Open Client documentation on CS_TIMEOUT. 833 834 $dbh = DBI->connect("dbi:Sybase:timeout=240", # wait up to 4 minutes 835 $user, $passwd); 836 837=item scriptName 838 839Specify the name for this connection that will be displayed in sp_who 840(ie in the sysprocesses table in the I<program_name> column). 841 842 $dbh=DBI->connect("dbi:Sybase:scriptName=myScript", $user, $password); 843 844=item hostname 845 846Specify the hostname that will be displayed by sp_who (and will be stored 847in the hostname column of sysprocesses).. 848 849 $dbh=DBI->connect("dbi:Sybase:hostname=kiruna", $user, $password); 850 851=item tdsLevel 852 853Specify the TDS protocol level to use when connecting to the server. 854Valid values are CS_TDS_40, CS_TDS_42, CS_TDS_46, CS_TDS_495 and CS_TDS_50. 855In general this is automatically negotiated between the client and the 856server, but in certain cases this may need to be forced to a lower level 857by the client. 858 859 $dbh=DBI->connect("dbi:Sybase:tdsLevel=CS_TDS_42", $user, $password); 860 861B<NOTE>: Setting the tdsLevel below CS_TDS_495 will disable a number of 862features, ?-style placeholders and CHAINED non-AutoCommit mode, in particular. 863 864=item encryptPassword 865 866Specify the use of the client password encryption supported by CT-Lib. 867Specify a value of 1 to use encrypted passwords. 868 869 $dbh=DBI->connect("dbi:Sybase:encryptPassword=1", $user, $password); 870 871=item kerberos 872 873Note: Requires OpenClient 11.1.1 or later. 874 875Sybase and OpenClient can use Kerberos to perform network-based login. 876If you use Kerberos for authentication you can use this feature and pass 877a kerberos serverprincipal using the C<kerberos=value> parameter: 878 879 $dbh = DBI->connect("dbi:Sybase:kerberos=$serverprincipal", '', ''); 880 881In addition, if you have a system for retrieving Kerberos serverprincipals at 882run-time you can tell DBD::Sybase to call a perl subroutine to get 883the serverprincipal from connect(): 884 885 sub sybGetPrinc { 886 my $srv = shift; 887 return the serverprincipal... 888 } 889 $dbh = DBI->connect('dbi:Sybase:server=troll', '', '', { syb_kerberos_serverprincipal => \&sybGetPrinc }); 890 891The subroutine will be called with one argument (the server that we will 892connect to, using the normal Sybase behavior of checking the DSQUERY 893environment variable if no server is specified in the connect()) and is 894expected to return a string (the Kerberos serverprincipal) to the caller. 895 896=item sslCAFile 897 898Specify the location of an alternate I<trusted.txt> file for SSL 899connection negotiation: 900 901 $dbh->DBI->connect("dbi:Sybase:sslCAFile=/usr/local/sybase/trusted.txt.ENGINEERING", $user, $password); 902 903=item bulkLogin 904 905Set this to 1 if the connection is going to be used for a bulk-load 906operation (see I<Experimental Bulk-Load functionality> elsewhere in this 907document.) 908 909 $dbh->DBI->connect("dbi:Sybase:bulkLogin=1", $user, $password); 910 911=item serverType 912 913Tell DBD::Sybase what the server type is. Defaults to ASE. Setting it to 914something else will prevent certain actions (such as setting options, 915fetching the ASE version via @@version, etc.) and avoid spurious errors. 916 917=item tds_keepalive 918 919Set this to 1 to tell OpenClient to enable the KEEP_ALIVE attribute on the 920connection. Default 1. 921 922=back 923 924These different parameters (as well as the server name) can be strung 925together by separating each entry with a semi-colon: 926 927 $dbh = DBI->connect("dbi:Sybase:server=ENGINEERING;packetSize=8192;language=us_english;charset=iso_1", 928 $user, $pwd); 929 930=head1 Handling Multiple Result Sets 931 932Sybase's Transact SQL has the ability to return multiple result sets 933from a single SQL statement. For example the query: 934 935 select b.title, b.author, s.amount 936 from books b, sales s 937 where s.authorID = b.authorID 938 order by b.author, b.title 939 compute sum(s.amount) by b.author 940 941which lists sales by author and title and also computes the total sales 942by author returns two types of rows. The DBI spec doesn't really 943handle this situation, nor the more hairy 944 945 exec my_proc @p1='this', @p2='that', @p3 out 946 947where C<my_proc> could return any number of result sets (ie it could 948perform an unknown number of C<select> statements. 949 950I've decided to handle this by returning an empty row at the end 951of each result set, and by setting a special Sybase attribute in $sth 952which you can check to see if there is more data to be fetched. The 953attribute is B<syb_more_results> which you should check to see if you 954need to re-start the C<fetch()> loop. 955 956To make sure all results are fetched, the basic C<fetch> loop can be 957written like this: 958 959 { 960 while($d = $sth->fetch) { 961 ... do something with the data 962 } 963 964 redo if $sth->{syb_more_results}; 965 } 966 967You can get the type of the current result set with 968$sth->{syb_result_type}. This returns a numerical value, as defined in 969$SYBASE/$SYBASE_OCS/include/cspublic.h: 970 971 #define CS_ROW_RESULT (CS_INT)4040 972 #define CS_CURSOR_RESULT (CS_INT)4041 973 #define CS_PARAM_RESULT (CS_INT)4042 974 #define CS_STATUS_RESULT (CS_INT)4043 975 #define CS_MSG_RESULT (CS_INT)4044 976 #define CS_COMPUTE_RESULT (CS_INT)4045 977 978In particular, the return status of a stored procedure is returned 979as CS_STATUS_RESULT (4043), and is normally the last result set that is 980returned in a stored proc execution, but see the B<syb_do_proc_status> 981attribute for an alternative way of handling this result type. See B<Executing 982Stored Procedures> elsewhere in this document for more information. 983 984If you add a 985 986 use DBD::Sybase; 987 988to your script then you can use the symbolic values (CS_xxx_RESULT) 989instead of the numeric values in your programs, which should make them 990easier to read. 991 992See also the C<$sth->syb_output_params> call to handle stored procedures 993that B<only> return B<OUTPUT> parameters. 994 995=head1 $sth->execute() failure mode behavior 996 997DBD::Sybase has the ability to handle multi-statement SQL commands 998in a single batch. For example, you could insert several rows in 999a single batch like this: 1000 1001 $sth = $dbh->prepare(" 1002 insert foo(one, two, three) values(1, 2, 3) 1003 insert foo(one, two, three) values(4, 5, 6) 1004 insert foo(one, two, three) values(10, 11, 12) 1005 insert foo(one, two, three) values(11, 12, 13) 1006 "); 1007 $sth->execute; 1008 1009If any one of the above inserts fails for any reason then $sth->execute 1010will return C<undef>, B<HOWEVER> the inserts that didn't fail will still 1011be in the database, unless C<AutoCommit> is off. 1012 1013It's also possible to write a statement like this: 1014 1015 $sth = $dbh->prepare(" 1016 insert foo(one, two, three) values(1, 2, 3) 1017 select * from bar 1018 insert foo(one, two, three) values(10, 11, 12) 1019 "); 1020 $sth->execute; 1021 1022If the second C<insert> is the one that fails, then $sth->execute will 1023B<NOT> return C<undef>. The error will get flagged after the rows 1024from C<bar> have been fetched. 1025 1026I know that this is not as intuitive as it could be, but I am 1027constrained by the Sybase API here. 1028 1029As an aside, I know that the example above doesn't really make sense, 1030but I need to illustrate this particular sequence... You can also see the 1031t/fail.t test script which shows this particular behavior. 1032 1033=head1 Sybase Specific Attributes 1034 1035There are a number of handle attributes that are specific to this driver. 1036These attributes all start with B<syb_> so as to not clash with any 1037normal DBI attributes. 1038 1039=head2 Database Handle Attributes 1040 1041The following Sybase specific attributes can be set at the Database handle 1042level: 1043 1044=over 4 1045 1046=item syb_show_sql (bool) 1047 1048If set then the current statement is included in the string returned by 1049$dbh->errstr. 1050 1051=item syb_show_eed (bool) 1052 1053If set, then extended error information is included in the string returned 1054by $dbh->errstr. Extended error information include the index causing a 1055duplicate insert to fail, for example. 1056 1057=item syb_err_handler (subroutine ref) 1058 1059This attribute is used to set an ad-hoc error handler callback (ie a 1060perl subroutine) that gets called before the normal error handler does 1061it's job. If this subroutine returns 0 then the error is 1062ignored. This is useful for handling PRINT statements in Transact-SQL, 1063for handling messages from the Backup Server, showplan output, dbcc 1064output, etc. 1065 1066The subroutine is called with nine parameters: 1067 1068 o the Sybase error number 1069 o the severity 1070 o the state 1071 o the line number in the SQL batch 1072 o the server name (if available) 1073 o the stored procedure name (if available) 1074 o the message text 1075 o the current SQL command buffer 1076 o either of the strings "client" (for Client Library errors) or 1077 "server" (for server errors, such as SQL syntax errors, etc), 1078 allowing you to identify the error type. 1079 1080As a contrived example, here is a port of the distinct error and 1081message handlers from the Sybase documentation: 1082 1083 Example: 1084 1085 sub err_handler { 1086 my($err, $sev, $state, $line, $server, 1087 $proc, $msg, $sql, $err_type) = @_; 1088 1089 my @msg = (); 1090 if($err_type eq 'server') { 1091 push @msg, 1092 ('', 1093 'Server message', 1094 sprintf('Message number: %ld, Severity %ld, State %ld, Line %ld', 1095 $err,$sev,$state,$line), 1096 (defined($server) ? "Server '$server' " : '') . 1097 (defined($proc) ? "Procedure '$proc'" : ''), 1098 "Message String:$msg"); 1099 } else { 1100 push @msg, 1101 ('', 1102 'Open Client Message:', 1103 sprintf('Message number: SEVERITY = (%ld) NUMBER = (%ld)', 1104 $sev, $err), 1105 "Message String: $msg"); 1106 } 1107 print STDERR join("\n",@msg); 1108 return 0; ## CS_SUCCEED 1109 } 1110 1111In a simpler and more focused example, this error handler traps 1112showplan messages: 1113 1114 %showplan_msgs = map { $_ => 1} (3612 .. 3615, 6201 .. 6299, 10201 .. 10299); 1115 sub err_handler { 1116 my($err, $sev, $state, $line, $server, 1117 $proc, $msg, $sql, $err_type) = @_; 1118 1119 if($showplan_msgs{$err}) { # it's a showplan message 1120 print SHOWPLAN "$err - $msg\n"; 1121 return 0; # This is not an error 1122 } 1123 return 1; 1124 } 1125 1126and this is how you would use it: 1127 1128 $dbh = DBI->connect('dbi:Sybase:server=troll', 'sa', ''); 1129 $dbh->{syb_err_handler} = \&err_handler; 1130 $dbh->do("set showplan on"); 1131 open(SHOWPLAN, ">>/var/tmp/showplan.log") || die "Can't open showplan log: $!"; 1132 $dbh->do("exec someproc"); # get the showplan trace for this proc. 1133 $dbh->disconnect; 1134 1135B<NOTE> - if you set the error handler in the DBI->connect() call like this 1136 1137 $dbh = DBI->connect('dbi:Sybase:server=troll', 'sa', '', 1138 { syb_err_handler => \&err_handler }); 1139 1140then the err_handler() routine will get called if there is an error during 1141 the connect itself. This is B<new> behavior in DBD::Sybase 0.95. 1142 1143 1144=item syb_flush_finish (bool) 1145 1146If $dbh->{syb_flush_finish} is set then $dbh->finish will drain any 1147results remaining for the current command by actually fetching them. 1148The default behaviour is to issue a ct_cancel(CS_CANCEL_ALL), but this 1149I<appears> to cause connections to hang or to fail in certain cases 1150(although I've never witnessed this myself.) 1151 1152=item syb_dynamic_supported (bool) 1153 1154This is a read-only attribute that returns TRUE if the dataserver 1155you are connected to supports ?-style placeholders. Typically placeholders are 1156not supported when using DBD::Sybase to connect to a MS-SQL server. 1157 1158=item syb_chained_txn (bool) 1159 1160If set then we use CHAINED transactions when AutoCommit is off. 1161Otherwise we issue an explicit BEGIN TRAN as needed. The default is on 1162if it is supported by the server. 1163 1164This attribute should usually be used only during the connect() call: 1165 1166 $dbh = DBI->connect('dbi:Sybase:', $user, $pwd, {syb_chained_txn => 1}); 1167 1168Using it at any other time with B<AutoCommit> turned B<off> will 1169B<force a commit> on the current handle. 1170 1171=item syb_quoted_identifier (bool) 1172 1173If set, then identifiers that would normally clash with Sybase reserved 1174words can be quoted using C<"identifier">. In this case strings must 1175be quoted with the single quote. 1176 1177This attribute can only be set if the database handle is idle (no 1178active statement handle.) 1179 1180Default is for this attribute to be B<off>. 1181 1182=item syb_rowcount (int) 1183 1184Setting this attribute to non-0 will limit the number of rows returned by 1185a I<SELECT>, or affected by an I<UPDATE> or I<DELETE> statement to the 1186I<rowcount> value. Setting it back to 0 clears the limit. 1187 1188This attribute can only be set if the database handle is idle. 1189 1190Default is for this attribute to be B<0>. 1191 1192=item syb_do_proc_status (bool) 1193 1194Setting this attribute causes $sth->execute() to fetch the return status 1195of any executed stored procs in the SQL being executed. If the return 1196status is non-0 then $sth->execute() will report that the operation 1197failed. 1198 1199B<NOTE> The result status is NOT the first result set that 1200is fetched from a stored proc execution. If the procedure includes 1201SELECT statements then these will be fetched first, which means that 1202C<$sth->execute> will NOT return a failure in that case as DBD::Sybase 1203won't have seen the result status yet at that point. 1204 1205The RaiseError will NOT be triggered by a non-0 return status if 1206there isn't an associated error message either generated by Sybase 1207(duplicate insert error, etc) or generated in the procedure via a T-SQL 1208C<raiserror> statement. 1209 1210Setting this attribute does B<NOT> affect existing $sth handles, only 1211those that are created after setting it. To change the behavior of 1212an existing $sth handle use $sth->{syb_do_proc_status}. 1213 1214The proc status is available in $sth->{syb_proc_status} after all the 1215result sets in the procedure have been processed. 1216 1217The default is for this attribute to be B<off>. 1218 1219=item syb_use_bin_0x 1220 1221If set, BINARY and VARBINARY values are prefixed with '0x' 1222in the result. The default is off. 1223 1224=item syb_binary_images 1225 1226If set, IMAGE data is returned in raw binary format. Otherwise the data is 1227converted to a long hex string. The default is off. 1228 1229=item syb_oc_version (string) 1230 1231Returns the identification string of the version of Client Library that 1232this binary is currently using. This is a read-only attribute. 1233 1234For example: 1235 1236 troll (7:59AM):348 > perl -MDBI -e '$dbh = DBI->connect("dbi:Sybase:", "sa"); print "$dbh->{syb_oc_version}\n";' 1237 Sybase Client-Library/11.1.1/P/Linux Intel/Linux 2.2.5 i586/1/OPT/Mon Jun 7 07:50:21 1999 1238 1239This is very useful information to have when reporting a problem. 1240 1241=item syb_server_version 1242 1243=item syb_server_version_string 1244 1245These two attributes return the Sybase server version, respectively 1246version string, and can be used to turn server-specific functionality 1247on or off. 1248 1249Example: 1250 1251 print "$dbh->{syb_server_version}\n$dbh->{syb_server_version_string}\n"; 1252 1253prints 1254 1255 12.5.2 1256 Adaptive Server Enterprise/12.5.2/EBF 12061 ESD#2/P/Linux Intel/Enterprise Linux/ase1252/1844/32-bit/OPT/Wed Aug 11 21:36:26 2004 1257 1258=item syb_failed_db_fatal (bool) 1259 1260If this is set, then a connect() request where the I<database> 1261specified doesn't exist or is not accessible will fail. This needs 1262to be set in the attribute hash passed during the DBI->connect() call 1263to be effective. 1264 1265Default: off 1266 1267=item syb_no_child_con (bool) 1268 1269If this attribute is set then DBD::Sybase will B<not> allow multiple 1270simultaneously active statement handles on one database handle (i.e. 1271multiple $dbh->prepare() calls without completely processing the 1272results from any existing statement handle). This can be used 1273to debug situations where incorrect or unexpected results are 1274found due to the creation of a sub-connection where the connection 1275attributes (in particular the current database) are different. 1276 1277Default: off 1278 1279=item syb_bind_empty_string_as_null (bool) 1280 1281If this attribute is set then an empty string (i.e. "") passed as 1282a parameter to an $sth->execute() call will be converted to a NULL 1283value. If the attribute is not set then an empty string is converted to 1284a single space. 1285 1286Default: off 1287 1288=item syb_cancel_request_on_error (bool) 1289 1290If this attribute is set then a failure in a multi-statement request 1291(for example, a stored procedure execution) will cause $sth->execute() 1292to return failure, and will cause any other results from this request to 1293be discarded. 1294 1295The default value (B<on>) changes the behavior that DBD::Sybase exhibited 1296up to version 0.94. 1297 1298Default: on 1299 1300=item syb_date_fmt (string) 1301 1302Defines the date/time conversion string when fetching data. See the 1303entry for the C<syb_date_fmt()> method elsewhere in this document for a 1304description of the available formats. 1305 1306=item syb_has_blk (bool) 1307 1308This read-only attribute is set to TRUE if the BLK API is available in 1309this version of DBD::Sybase. 1310 1311=item syb_disconnect_in_child (bool) 1312 1313Sybase client library allows using opened connections across a fork (i.e. the opened connection 1314can be used in the child process). DBI by default will set flags such that this connection will 1315be closed when the child process terminates. This is in most cases not what you want. DBI provides 1316the InactiveDestroy attribute to control this, but you have to set this attribute manually as it 1317defaults to False (i.e. when DESTROY is called for the handle the connection is closed). 1318The syb_disconnect_in_child attribute attempts to correct this - the default is for this 1319attribute to be False - thereby inhibitting the closing of the connection(s) when 1320the current process ID doesn't match the process ID that created the connection. 1321 1322Default: off 1323 1324=item syb_enable_utf8 (bool) 1325 1326If this attribute is set then DBD::Sybase will convert UNIVARCHAR, UNICHAR, 1327and UNITEXT data to Perl's internal utf-8 encoding when they are 1328retrieved. Updating a unicode column will cause Sybase to convert any incoming 1329data from utf-8 to its internal utf-16 encoding. 1330 1331This feature requires OpenClient 15.x to work. 1332 1333Default: off 1334 1335=back 1336 1337=head2 Statement Handle Attributes 1338 1339The following read-only attributes are available at the statement level: 1340 1341=over 4 1342 1343=item syb_more_results (bool) 1344 1345See the discussion on handling multiple result sets above. 1346 1347=item syb_result_type (int) 1348 1349Returns the numeric result type of the current result set. Useful when 1350executing stored procedurs to determine what type of information is 1351currently fetchable (normal select rows, output parameters, status results, 1352etc...). 1353 1354=item syb_do_proc_status (bool) 1355 1356See above (under Database Handle Attributes) for an explanation. 1357 1358=item syb_proc_status (read-only) 1359 1360If syb_do_proc_status is set, then the return status of stored procedures will 1361be available via $sth->{syb_proc_status}. 1362 1363=item syb_no_bind_blob (bool) 1364 1365If set then any IMAGE or TEXT columns in a query are B<NOT> returned 1366when calling $sth->fetch (or any variation). 1367 1368Instead, you would use 1369 1370 $sth->syb_ct_get_data($column, \$data, $size); 1371 1372to retrieve the IMAGE or TEXT data. If $size is 0 then the entire item is 1373fetched, otherwis you can call this in a loop to fetch chunks of data: 1374 1375 while(1) { 1376 $sth->syb_ct_get_data($column, \$data, 1024); 1377 last unless $data; 1378 print OUT $data; 1379 } 1380 1381The fetched data is still subject to Sybase's TEXTSIZE option (see the 1382SET command in the Sybase reference manual). This can be manipulated with 1383DBI's B<LongReadLen> attribute, but C<$dbh->{LongReadLen}> I<must> be 1384set before $dbh->prepare() is called to take effect (this is a change 1385in 1.05 - previously you could call it after the prepare() but 1386before the execute()). Note that LongReadLen 1387has no effect when using DBD::Sybase with an MS-SQL server. 1388 1389B<Note>: The IMAGE or TEXT column that is to be fetched this way I<must> 1390be I<last> in the select list. 1391 1392See also the description of the ct_get_data() API call in the Sybase 1393OpenClient manual, and the "Working with TEXT/IMAGE columns" section 1394elsewhere in this document. 1395 1396=back 1397 1398=head1 Controlling DATETIME output formats 1399 1400By default DBD::Sybase will return I<DATETIME> and I<SMALLDATETIME> 1401columns in the I<Nov 15 1998 11:13AM> format. This can be changed 1402via a private B<syb_date_fmt()> method. 1403 1404The syntax is 1405 1406 $dbh->syb_date_fmt($fmt); 1407 1408where $fmt is a string representing the format that you want to apply. 1409 1410Note that this requires DBI 1.37 or later. 1411 1412The formats are based on Sybase's standard conversion routines. The following 1413subset of available formats has been implemented: 1414 1415=over 4 1416 1417=item LONG 1418 1419Nov 15 1998 11:30:11:496AM 1420 1421=item LONGMS 1422 1423New with ASE 15.5 - for bigtime/bigdatetime datatypes, includes microseconds: 1424 1425Apr 7 2010 10:40:33.532315PM 1426 1427=item SHORT 1428 1429Nov 15 1998 11:30AM 1430 1431=item DMY4_YYYY 1432 143315 Nov 1998 1434 1435=item MDY1_YYYY 1436 143711/15/1998 1438 1439=item DMY1_YYYY 1440 144115/11/1998 1442 1443=item DMY2_YYYY 1444 144515.11.1998 1446 1447=item YMD3_YYYY 1448 144919981115 1450 1451=item HMS 1452 145311:30:11 1454 1455=item ISO 1456 14572004-08-21 14:36:48.080 1458 1459=item ISO_strict 1460 14612004-08-21T14:36:48.080Z 1462 1463Note that Sybase has no concept of a timezone, so the trailing "Z" is 1464really not correct (assumes that the time is in UTC). However, there 1465is no guarantee that the client and the server run in the same timezone, 1466so assuming the timezone of the client isn't really a valid option 1467either. 1468 1469=back 1470 1471=head1 Retrieving OUTPUT parameters from stored procedures 1472 1473Sybase lets you pass define B<OUTPUT> parameters to stored procedures, 1474which are a little like parameters passed by reference in C (or perl.) 1475 1476In Transact-SQL this is done like this 1477 1478 declare @id_value int, @id_name char(10) 1479 exec my_proc @name = 'a string', @number = 1234, @id = @id_value OUTPUT, @out_name = @id_name OUTPUT 1480 -- Now @id_value and @id_name are set to whatever 'my_proc' set @id and @out_name to 1481 1482 1483So how can we get at @param using DBD::Sybase? 1484 1485If your stored procedure B<only> returns B<OUTPUT> parameters, then you 1486can use this shorthand: 1487 1488 $sth = $dbh->prepare('...'); 1489 $sth->execute; 1490 @results = $sth->syb_output_params(); 1491 1492This will return an array for all the OUTPUT parameters in the proc call, 1493and will ignore any other results. The array will be undefined if there are 1494no OUTPUT params, or if the stored procedure failed for some reason. 1495 1496The more generic way looks like this: 1497 1498 $sth = $dbh->prepare("declare \@id_value int, \@id_name 1499 exec my_proc @name = 'a string', @number = 1234, @id = @id_value OUTPUT, @out_name = @id_name OUTPUT"); 1500 $sth->execute; 1501 { 1502 while($d = $sth->fetch) { 1503 if($sth->{syb_result_type} == 4042) { # it's a PARAM result 1504 $id_value = $d->[0]; 1505 $id_name = $d->[1]; 1506 } 1507 } 1508 1509 redo if $sth->{syb_more_results}; 1510 } 1511 1512So the OUTPUT params are returned as one row in a special result set. 1513 1514 1515=head1 Multiple active statements on one $dbh 1516 1517It is possible to open multiple active statements on a single database 1518handle. This is done by opening a new physical connection in $dbh->prepare() 1519if there is already an active statement handle for this $dbh. 1520 1521This feature has been implemented to improve compatibility with other 1522drivers, but should not be used if you are coding directly to the 1523Sybase driver. 1524 1525The C<syb_no_child_con> attribute controls whether this feature is 1526turned on. If it is FALSE (the default), then multiple statement handles are 1527supported. If it is TRUE then multiple statements on the same database 1528handle are disabled. Also see below for interaction with AutoCommit. 1529 1530If AutoCommit is B<OFF> then multiple statement handles on a single $dbh 1531is B<NOT> supported. This is to avoid various deadlock problems that 1532can crop up in this situation, and because you will not get real transactional 1533integrity using multiple statement handles simultaneously as these in 1534reality refer to different physical connections. 1535 1536 1537=head1 Working with IMAGE and TEXT columns 1538 1539DBD::Sybase can store and retrieve IMAGE or TEXT data (aka "blob" data) 1540via standard SQL statements. The B<LongReadLen> handle attribute controls 1541the maximum size of IMAGE or TEXT data being returned for each data 1542element. 1543 1544When using standard SQL the default for IMAGE data is to be converted 1545to a hex string, but you can use the I<syb_binary_images> handle attribute 1546to change this behaviour. Alternatively you can use something like 1547 1548 $binary = pack("H*", $hex_string); 1549 1550to do the conversion. 1551 1552IMAGE and TEXT datatypes can B<not> be passed as parameters using 1553?-style placeholders, and placeholders can't refer to IMAGE or TEXT 1554columns (this is a limitation of the TDS protocol used by Sybase, not 1555a DBD::Sybase limitation.) 1556 1557There is an alternative way to access and update IMAGE/TEXT data 1558using the natice OpenClient API. This is done via $h->func() calls, 1559and is, unfortunately, a little convoluted. 1560 1561=head2 Handling IMAGE/TEXT data with syb_ct_get_data()/syb_ct_send_data() 1562 1563With DBI 1.37 and later you can call all of these ct_xxx() calls directly 1564as statement handle methods by prefixing them with syb_, so for example 1565 1566 $sth->func($col, $dataref, $numbytes, 'ct_fetch_data'); 1567 1568becomes 1569 1570 $sth->syb_ct_fetch_data($col, $dataref, $numbytes); 1571 1572=over 4 1573 1574=item $len = ct_fetch_data($col, $dataref, $numbytes) 1575 1576The ct_get_data() call allows you to fetch IMAGE/TEXT data in 1577raw format, either in one piece or in chunks. To use this function 1578you must set the I<syb_no_bind_blob> statement handle to I<TRUE>. 1579 1580ct_get_data() takes 3 parameters: The column number (starting at 1) 1581of the query, a scalar ref and a byte count. If the byte count is 0 1582then we read as many bytes as possible. 1583 1584Note that the IMAGE/TEXT column B<must> be B<last> in the select list 1585for this to work. 1586 1587The call sequence is: 1588 1589 $sth = $dbh->prepare("select id, img from some_table where id = 1"); 1590 $sth->{syb_no_bind_blob} = 1; 1591 $sth->execute; 1592 while($d = $sth->fetchrow_arrayref) { 1593 # The data is in the second column 1594 $len = $sth->syb_ct_get_data(2, \$img, 0); 1595 # with DBI 1.33 and earlier, this would be 1596 # $len = $sth->func(2, \$img, 0, 'ct_get_data'); 1597 } 1598 1599ct_get_data() returns the number of bytes that were effectively fetched, 1600so that when fetching chunks you can do something like this: 1601 1602 while(1) { 1603 $len = $sth->syb_ct_get_data(2, $imgchunk, 1024); 1604 ... do something with the $imgchunk ... 1605 last if $len != 1024; 1606 } 1607 1608To explain further: Sybase stores IMAGE/TEXT data separately from 1609normal table data, in a chain of pagesize blocks (a Sybase database page 1610is defined at the server level, and can be 2k, 4k, 8k or 16k in size.) To update an IMAGE/TEXT 1611column Sybase needs to find the head of this chain, which is known as 1612the "text pointer". As there is no I<where> clause when the ct_send_data() 1613API is used we need to retrieve the I<text pointer> for the correct 1614data item first, which is done via the ct_data_info(CS_GET) call. Subsequent 1615ct_send_data() calls will then know which data item to update. 1616 1617=item $status = ct_data_info($action, $column, $attr) 1618 1619ct_data_info() is used to fetch or update the CS_IODESC structure 1620for the IMAGE/TEXT data item that you wish to update. $action should be 1621one of "CS_SET" or "CS_GET", $column is the column number of the 1622active select statement (ignored for a CS_SET operation) and $attr is 1623a hash ref used to set the values in the struct. 1624 1625ct_data_info() must be first called with CS_GET to fetch the CS_IODESC 1626structure for the IMAGE/TEXT data item that you wish to update. Then 1627you must update the value of the I<total_txtlen> structure element 1628to the length (in bytes) of the IMAGE/TEXT data that you are going to 1629insert, and optionally set the I<log_on_update> to B<TRUE> to enable full 1630logging of the operation. 1631 1632ct_data_info(CS_GET) will I<fail> if the IMAGE/TEXT data for which the 1633CS_IODESC is being fetched is NULL. If you have a NULL value that needs 1634updating you must first update it to some non-NULL value (for example 1635an empty string) using standard SQL before you can retrieve the CS_IODESC 1636entry. This actually makes sense because as long as the data item is NULL 1637there is B<no> I<text pointer> and no TEXT page chain for that item. 1638 1639See the ct_send_data() entry below for an example. 1640 1641=item ct_prepare_send() 1642 1643ct_prepare_send() must be called to initialize a IMAGE/TEXT write operation. 1644See the ct_send_data() entry below for an example. 1645 1646=item ct_finish_send() 1647 1648ct_finish_send() is called to finish/commit an IMAGE/TEXT write operation. 1649See the ct_send_data() entry below for an example. 1650 1651=item ct_send_data($image, $bytes) 1652 1653Send $bytes bytes of $image to the database. The request must have been set 1654up via ct_prepare_send() and ct_data_info() for this to work. ct_send_data() 1655returns B<TRUE> on success, and B<FALSE> on failure. 1656 1657In this example, we wish to update the data in the I<img> column 1658where the I<id> column is 1. We assume that DBI is at version 1.37 or 1659later and use the direct method calls: 1660 1661 # first we need to find the CS_IODESC data for the data 1662 $sth = $dbh->prepare("select img from imgtable where id = 1"); 1663 $sth->execute; 1664 while($sth->fetch) { # don't care about the data! 1665 $sth->syb_ct_data_info('CS_GET', 1); 1666 } 1667 1668 # OK - we have the CS_IODESC values, so do the update: 1669 $sth->syb_ct_prepare_send(); 1670 # Set the size of the new data item (that we are inserting), and make 1671 # the operation unlogged 1672 $sth->syb_ct_data_info('CS_SET', 1, {total_txtlen => length($image), log_on_update => 0}); 1673 # now transfer the data (in a single chunk, this time) 1674 $sth->syb_ct_send_data($image, length($image)); 1675 # commit the operation 1676 $sth->syb_ct_finish_send(); 1677 1678The ct_send_data() call can also transfer the data in chunks, however you 1679must know the total size of the image before you start the insert. For example: 1680 1681 # update a database entry with a new version of a file: 1682 my $size = -s $file; 1683 # first we need to find the CS_IODESC data for the data 1684 $sth = $dbh->prepare("select img from imgtable where id = 1"); 1685 $sth->execute; 1686 while($sth->fetch) { # don't care about the data! 1687 $sth->syb_ct_data_info('CS_GET', 1); 1688 } 1689 1690 # OK - we have the CS_IODESC values, so do the update: 1691 $sth->syb_ct_prepare_send(); 1692 # Set the size of the new data item (that we are inserting), and make 1693 # the operation unlogged 1694 $sth->syb_ct_data_info('CS_SET', 1, {total_txtlen => $size, log_on_update => 0}); 1695 1696 # open the file, and store it in the db in 1024 byte chunks. 1697 open(IN, $file) || die "Can't open $file: $!"; 1698 while($size) { 1699 $to_read = $size > 1024 ? 1024 : $size; 1700 $bytesread = read(IN, $buff, $to_read); 1701 $size -= $bytesread; 1702 1703 $sth->syb_ct_send_data($buff, $bytesread); 1704 } 1705 close(IN); 1706 # commit the operation 1707 $sth->syb_ct_finish_send(); 1708 1709 1710=back 1711 1712 1713=head1 AutoCommit, Transactions and Transact-SQL 1714 1715When $h->{AutoCommit} is I<off> all data modification SQL statements 1716that you issue (insert/update/delete) will only take effect if you 1717call $dbh->commit. 1718 1719DBD::Sybase implements this via two distinct methods, depending on 1720the setting of the $h->{syb_chained_txn} attribute and the version of the 1721server that is being accessed. 1722 1723If $h->{syb_chained_txn} is I<off>, then the DBD::Sybase driver 1724will send a B<BEGIN TRAN> before the first $dbh->prepare(), and 1725after each call to $dbh->commit() or $dbh->rollback(). This works 1726fine, but will cause any SQL that contains any I<CREATE TABLE> 1727(or other DDL) statements to fail. These I<CREATE TABLE> statements can be 1728burried in a stored procedure somewhere (for example, 1729C<sp_helprotect> creates two temp tables when it is run). 1730You I<can> get around this limit by setting the C<ddl in tran> option 1731(at the database level, via C<sp_dboption>.) You should be aware that 1732this can have serious effects on performance as this causes locks to 1733be held on certain system tables for the duration of the transaction. 1734 1735If $h->{syb_chained_txn} is I<on>, then DBD::Sybase sets the 1736I<CHAINED> option, which tells Sybase not to commit anything automatically. 1737Again, you will need to call $dbh->commit() to make any changes to the data 1738permanent. 1739 1740=head1 Behavior of $dbh->last_insert_id 1741 1742This version of DBD::Sybase includes support for the last_insert_id() call, 1743with the following caveats: 1744 1745The last_insert_id() call is simply a wrapper around a "select @@identity" 1746query. To be successful (i.e. to return the correct value) this must 1747be executed on the same connection as the INSERT that generated the 1748new IDENTITY value. Therefore the statement handle that was used to 1749perform the insert B<must> have been closed/freed before last_insert_id() 1750can be called. Otherwise last_insert_id() will be forced to open a different 1751connection to perform the query, and will return an invalid value (usually 1752in this case it will return 0). 1753 1754last_insert_id() ignores any parameters passed to it, and will NOT return 1755the last @@identity value generated in the case where placeholders were used, 1756or where the insert was encapsulated in a stored procedure. 1757 1758=head1 Using ? Placeholders & bind parameters to $sth->execute 1759 1760DBD::Sybase supports the use of ? placeholders in SQL statements as long 1761as the underlying library and database engine supports it. It does 1762this by using what Sybase calls I<Dynamic SQL>. The ? placeholders allow 1763you to write something like: 1764 1765 $sth = $dbh->prepare("select * from employee where empno = ?"); 1766 1767 # Retrieve rows from employee where empno == 1024: 1768 $sth->execute(1024); 1769 while($data = $sth->fetch) { 1770 print "@$data\n"; 1771 } 1772 1773 # Now get rows where empno = 2000: 1774 1775 $sth->execute(2000); 1776 while($data = $sth->fetch) { 1777 print "@$data\n"; 1778 } 1779 1780When you use ? placeholders Sybase goes and creates a temporary stored 1781procedure that corresponds to your SQL statement. You then pass variables 1782to $sth->execute or $dbh->do, which get inserted in the query, and any rows 1783are returned. 1784 1785DBD::Sybase uses the underlying Sybase API calls to handle ?-style 1786placeholders. For select/insert/update/delete statements DBD::Sybase 1787calls the ct_dynamic() family of Client Library functions, which gives 1788DBD::Sybase data type information for each parameter to the query. 1789 1790You can only use ?-style placeholders for statements that return a single 1791result set, and the ? placeholders can only appear in a 1792B<WHERE> clause, in the B<SET> clause of an B<UPDATE> statement, or in the 1793B<VALUES> list of an B<INSERT> statement. 1794 1795The DBI docs mention the following regarding NULL values and placeholders: 1796 1797=over 4 1798 1799 Binding an `undef' (NULL) to the placeholder will not 1800 select rows which have a NULL `product_code'! Refer to the 1801 SQL manual for your database engine or any SQL book for 1802 the reasons for this. To explicitly select NULLs you have 1803 to say "`WHERE product_code IS NULL'" and to make that 1804 general you have to say: 1805 1806 ... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL)) 1807 1808 and bind the same value to both placeholders. 1809 1810=back 1811 1812This will I<not> work with a Sybase database server. If you attempt the 1813above construct you will get the following error: 1814 1815=over 4 1816 1817The datatype of a parameter marker used in the dynamic prepare statement could not be resolved. 1818 1819=back 1820 1821The specific problem here is that when using ? placeholders the prepare() 1822operation is sent to the database server for parameter resoltion. This extracts 1823the datatypes for each of the placeholders. Unfortunately the C<? is null> 1824construct doesn't tie the ? placeholder with an existing table column, so 1825the database server can't find the data type. As this entire operation happens 1826inside the Sybase libraries there is no easy way for DBD::Sybase to work around 1827it. 1828 1829Note that Sybase will normally handle the C<foo = NULL> construct the same way 1830that other systems handle C<foo is NULL>, so the convoluted construct that 1831is described above is not necessary to obtain the correct results when 1832querying a Sybase database. 1833 1834 1835The underlying API does not support ?-style placeholders for stored 1836procedures, but see the section on titled B<Stored Procedures and Placeholders> 1837elsewhere in this document. 1838 1839?-style placeholders can B<NOT> be used to pass TEXT or IMAGE data 1840items to the server. This is a limitation of the TDS protocol, not of 1841DBD::Sybase. 1842 1843There is also a performance issue: OpenClient creates stored procedures in 1844tempdb for each prepare() call that includes ? placeholders. Creating 1845these objects requires updating system tables in the tempdb database, and 1846can therefore create a performance hotspot if a lot of prepare() statements 1847from multiple clients are executed simultaneously. This problem 1848has been corrected for Sybase 11.9.x and later servers, as they create 1849"lightweight" temporary stored procs which are held in the server memory 1850cache and don't affect the system tables at all. 1851 1852In general however I find that if your application is going to run 1853against Sybase it is better to write ad-hoc 1854stored procedures rather than use the ? placeholders in embedded SQL. 1855 1856Out of curiosity I did some simple timings to see what the overhead 1857of doing a prepare with ? placehoders is vs. a straight SQL prepare and 1858vs. a stored procedure prepare. Against an 11.0.3.3 server (linux) the 1859placeholder prepare is significantly slower, and you need to do ~30 1860execute() calls on the prepared statement to make up for the overhead. 1861Against a 12.0 server (solaris) however the situation was very different, 1862with placeholder prepare() calls I<slightly> faster than straight SQL 1863prepare(). This is something that I I<really> don't understand, but 1864the numbers were pretty clear. 1865 1866In all cases stored proc prepare() calls were I<clearly> faster, and 1867consistently so. 1868 1869This test did not try to gauge concurrency issues, however. 1870 1871It is not possible to retrieve the last I<IDENTITY> value 1872after an insert done with ?-style placeholders. This is a Sybase 1873limitation/bug, not a DBD::Sybase problem. For example, assuming table 1874I<foo> has an identity column: 1875 1876 $dbh->do("insert foo(col1, col2) values(?, ?)", undef, "string1", "string2"); 1877 $sth = $dbh->prepare('select @@identity') 1878 || die "Can't prepare the SQL statement: $DBI::errstr"; 1879 $sth->execute || die "Can't execute the SQL statement: $DBI::errstr"; 1880 1881 #Get the data back. 1882 while (my $row = $sth->fetchrow_arrayref()) { 1883 print "IDENTITY value = $row->[0]\n"; 1884 } 1885 1886will always return an identity value of 0, which is obviously incorrect. 1887This behaviour is due to the fact that the handling of ?-style placeholders 1888is implemented using temporary stored procedures in Sybase, and the value 1889of C<@@identity> is reset when the stored procedure has executed. Using an 1890explicit stored procedure to do the insert and trying to retrieve 1891C<@@identity> after it has executed results in the same behaviour. 1892 1893 1894Please see the discussion on Dynamic SQL in the 1895OpenClient C Programmer's Guide for details. The guide is available on-line 1896at http://sybooks.sybase.com/ 1897 1898=head1 Calling Stored Procedures 1899 1900DBD::Sybase handles stored procedures in the same way as any other 1901Transact-SQL statement. The only real difference is that Sybase stored 1902procedures always return an extra result set with the I<return status> 1903from the proc which corresponds to the I<return> statement in the stored 1904procedure code. This result set with a single row is always returned last 1905and has a result type of CS_STATUS_RESULT (4043). 1906 1907By default this result set is returned like any other, but you can ask 1908DBD::Sybase to process it under the covers via the $h->{syb_do_proc_status} 1909attribute. If this attribute is set then DBD::Sybase will process the 1910CS_STATUS_RESULT result set itself, place the return status value in 1911$sth->{syb_proc_status}, and possibly raise an error if the result set 1912is different from 0. Note that a non-0 return status will B<NOT> cause 1913$sth->execute to return a failure code if the proc has at least one other 1914result set that returned rows (reason: the rows are returned and fetched 1915before the return status is seen). 1916 1917 1918=head2 Stored Procedures and Placeholders 1919 1920DBD::Sybase has the ability to use ?-style 1921placeholders as parameters to stored proc calls. The requirements are 1922that the stored procedure call be initiated with an "exec" and that it be 1923the only statement in the batch that is being prepared(): 1924 1925For example, this prepares a stored proc call with named parameters: 1926 1927 my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?"); 1928 $sth->execute('one', 'two'); 1929 1930You can also use positional parameters: 1931 1932 my $sth = $dbh->prepare("exec my_proc ?, ?"); 1933 $sth->execute('one', 'two'); 1934 1935You may I<not> mix positional and named parameter in the same prepare. 1936 1937You I<can't> mix placeholder parameters and hard coded parameters. For example 1938 1939 $sth = $dbh->prepare("exec my_proc \@p1 = 1, \@p2 = ?"); 1940 1941will I<not> work - because the @p1 parameter isn't parsed correctly 1942and won't be sent to the server. 1943 1944You can specify I<OUTPUT> parameters in the usual way, but you can B<NOT> 1945use bind_param_inout() to get the output result - instead you have to call 1946fetch() and/or $sth->func('syb_output_params'): 1947 1948 my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?, \@p3 = ? OUTPUT "); 1949 $sth->execute('one', 'two', 'three'); 1950 my (@data) = $sth->syb_output_params(); 1951 1952DBD::Sybase does not attempt to figure out the correct parameter type 1953for each parameter (it would be possible to do this for most cases, but 1954there are enough exceptions that I preferred to avoid the issue for the 1955time being). DBD::Sybase defaults all the parameters to SQL_CHAR, and 1956you have to use bind_param() with an explicit type value to set this to 1957something different. The type is then remembered, so you only need to 1958use the explicit call once for each parameter: 1959 1960 my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?"); 1961 $sth->bind_param(1, 'one', SQL_CHAR); 1962 $sth->bind_param(2, 2.34, SQL_FLOAT); 1963 $sth->execute; 1964 .... 1965 $sth->execute('two', 3.456); 1966 etc... 1967 1968Note that once a type has been defined for a parameter you can't change 1969it. 1970 1971When binding SQL_NUMERIC or SQL_DECIMAL data you may get fatal conversion 1972errors if the scale or the precision exceeds the size of the target 1973parameter definition. 1974 1975For example, consider the following stored proc definition: 1976 1977 declare proc my_proc @p1 numeric(5,2) as... 1978 1979and the following prepare/execute snippet: 1980 1981 my $sth = $dbh->prepare("exec my_proc \@p1 = ?"); 1982 $sth->bind_param(1, 3.456, SQL_NUMERIC); 1983 1984This generates the following error: 1985 1986DBD::Sybase::st execute failed: Server message number=241 severity=16 state=2 line=0 procedure=dbitest text=Scale error during implicit conversion of NUMERIC value '3.456' to a NUMERIC field. 1987 1988You can tell Sybase (and DBD::Sybase) to ignore these sorts of errors by 1989setting the I<arithabort> option: 1990 1991 $dbh->do("set arithabort off"); 1992 1993See the I<set> command in the Sybase Adaptive Server Enterprise Reference 1994Manual for more information on the set command and on the arithabort option. 1995 1996=head1 Other Private Methods 1997 1998=head2 DBD::Sybase private Database Handle Methods 1999 2000=over 4 2001 2002=item $bool = $dbh->syb_isdead 2003 2004Tests the connection to see if the connection has been marked DEAD by OpenClient. 2005The connection can get marked DEAD if an error occurs on the connection, or the connection fails. 2006 2007=back 2008 2009=head2 DBD::Sybase private Statement Handle Methods 2010 2011=over 4 2012 2013=item @data = $sth->syb_describe([$assoc]) 2014 2015Retrieves the description of each of the output columns of the current 2016result set. Each element of the returned array is a reference 2017to a hash that describes the column. The following fields are set: 2018NAME, TYPE, SYBTYPE, MAXLENGTH, SCALE, PRECISION, STATUS. 2019 2020You could use it like this: 2021 2022 my $sth = $dbh->prepare("select name, uid from sysusers"); 2023 $sth->execute; 2024 my @description = $sth->syb_describe; 2025 print "$description[0]->{NAME}\n"; # prints name 2026 print "$description[0]->{MAXLENGTH}\n"; # prints 30 2027 .... 2028 2029 while(my $row = $sth->fetch) { 2030 .... 2031 } 2032 2033The STATUS field is a string which can be tested for the following 2034values: CS_CANBENULL, CS_HIDDEN, CS_IDENTITY, CS_KEY, CS_VERSION_KEY, 2035CS_TIMESTAMP and CS_UPDATABLE. See table 3-46 of the Open Client Client 2036Library Reference Manual for a description of each of these values. 2037 2038The TYPE field is the data type that Sybase::CTlib converts the 2039column to when retrieving the data, so a DATETIME column will be 2040returned as a CS_CHAR_TYPE column. 2041 2042The SYBTYPE field is the real Sybase data type for this column. 2043 2044I<Note that the symbolic values of the CS_xxx symbols isn't available 2045yet in DBD::Sybase.> 2046 2047 2048=back 2049 2050=head1 Experimental Bulk-Load Functionality 2051 2052B<NOTE>: This feature requires that the I<libblk.a> library be available 2053at build time. This is not always the case if the Sybase SDK isn't 2054installed. You can test the $dbh->{syb_has_blk} attribute to 2055see if the BLK api calls are available in your copy of DBD::Sybase. 2056 2057Starting with release 1.04.2 DBD::Sybase has the ability to use Sybase's 2058BLK (bulk-loading) API to perform fast data loads. Basic usage is as follows: 2059 2060 my $dbh = DBI->connect('dbi:Sybase:server=MY_SERVER;bulkLogin=1', $user, $pwd); 2061 2062 $dbh->begin_work; # optional. 2063 my $sth = $dbh->prepare("insert the_table values(?, ?, ?, ?, ?)", 2064 {syb_bcp_attribs => { identity_flag => 0, 2065 identity_column => 0 }}}); 2066 while(<DATA>) { 2067 chomp; 2068 my @row = split(/\|/, $_); # assume a pipe-delimited file... 2069 $sth->execute(@row); 2070 } 2071 $dbh->commit; 2072 print "Sent ", $sth->rows, " to the server\n"; 2073 $sth->finish; 2074 2075First, you need to specify the new I<bulkLogin> attribute in the connection 2076string, which turns on the CS_BULK_LOGIN property for the connection. Without 2077this property the BLK api will not be functional. 2078 2079You call $dbh->prepare() with a regular INSERT statement and the 2080special I<syb_bcp_attribs> attribute to turn on BLK handling of the data. 2081The I<identity_flag> sub-attribute can be set to 1 if your source data 2082includes the values for the target table's IDENTITY column. If the 2083target table has an IDENTITY column but you want the insert operation to 2084generate a new value for each row then leave I<identity_flag> at 0, but set 2085I<identity_col> to the column number of the identity column (it's usually 2086the first column in the table, but not always.) 2087 2088The number of placeholders in the INSERT statement I<must> correspond to 2089the number of columns in the table, and the input data I<must> be in the 2090same order as the table's physical column order. Any column list in the 2091INSERT statement (i.e. I<insert table(a, b, c,...) values(...)> is ignored. 2092 2093The value of AutoCommit is ignored for BLK operations - rows are only 2094commited when you call $dbh->commit. 2095 2096You can call $dbh->rollback to cancel any uncommited rows, but this I<also> 2097cancels the rest of the BLK operation: any attempt to load rows to the 2098server after a call to $dbh->rollback() will fail. 2099 2100If a row fails to load due to a CLIENT side error (such as a data conversion 2101error) then $sth->execute() will return a failure (i.e. false) and 2102$sth->errstr will have the reason for the error. 2103 2104If a row fails on the SERVER side (for example due to a duplicate row 2105error) then the entire batch (i.e. between two $dbh->commit() calls) 2106will fail. This is normal behavior for BLK/bcp. 2107 2108The Bulk-Load API is very sensitive to data conversion issues, as all the 2109conversions are handled on the client side, and the row is pre-formatted 2110before being sent to the server. By default any conversion that is flagged 2111by Sybase's cs_convert() call will result in a failed row. Some of these 2112conversion errors are patently fatal (e.g. converting 'Feb 30 2001' to a 2113DATETIME value...), while others are debatable (e.g. converting 123.456 to 2114a NUMERIC(6,2) which results in a loss of precision). The default behavior 2115of failing any row that has a conversion error in it can be modified by 2116using a special error handler. Returning 0 from this handler 2117tells DBD::Sybase to fail this row, and returning 1 means that we still 2118want to try to send the row to the server (obviously Sybase's internal 2119code can still fail the row at that point.) 2120You set the handler like this: 2121 2122 DBD::Sybase::syb_set_cslib_cb(\&handler); 2123 2124and a sample handler: 2125 2126 sub cslib_handler { 2127 my ($layer, $origin, $severity, $errno, $errmsg, $osmsg, $blkmsg) = @_; 2128 2129 print "Layer: $layer, Origin: $origin, Severity: $severity, Error: $errno\n"; 2130 print $msg; 2131 print $osmsg if($osmsg); 2132 print $blkmsg if $blkmsg; 2133 2134 return 1 if($errno == 36) 2135 2136 return 0; 2137 } 2138 2139Please see the t/xblk.t test script for some examples. 2140 2141Reminder - this is an I<experimental> implementation. It may change 2142in the future, and it could be buggy. 2143 2144=head1 Using DBD::Sybase with MS-SQL 2145 2146MS-SQL started out as Sybase 4.2, and there are still a lot of similarities 2147between Sybase and MS-SQL which makes it possible to use DBD::Sybase 2148to query a MS-SQL dataserver using either the Sybase OpenClient libraries 2149or the FreeTDS libraries (see http://www.freetds.org). 2150 2151However, using the Sybase libraries to query an MS-SQL server has 2152certain limitations. In particular ?-style placeholders are not 2153supported (although support when using the FreeTDS libraries is 2154possible in a future release of the libraries), and certain B<syb_> 2155attributes may not be supported. 2156 2157Sybase defaults the TEXTSIZE attribute (aka B<LongReadLen>) to 215832k, but MS-SQL 7 doesn't seem to do that correctly, resulting in 2159very large memory requests when querying tables with TEXT/IMAGE 2160data columns. The work-around is to set TEXTSIZE to some decent value 2161via $dbh->{LongReadLen} (if that works - I haven't had any confirmation 2162that it does) or via $dbh->do("set textsize <somesize>"); 2163 2164=head1 nsql 2165 2166The nsql() call is a direct port of the function of the same name that 2167exists in Sybase::DBlib. From 1.08 it has been extended to offer new 2168functionality. 2169 2170Usage: 2171 2172 @data = $dbh->func($sql, $type, $callback, $options, 'nsql'); 2173 2174If the DBI version is 1.37 or later, then you can also call it this way: 2175 2176 @data = $dbh->syb_nsql($sql, $type, $callback, $options); 2177 2178This executes the query in $sql, and returns all the data in @data. The 2179$type parameter can be used to specify that each returned row be in array 2180form (i.e. $type passed as 'ARRAY', which is the default) or in hash form 2181($type passed as 'HASH') with column names as keys. 2182 2183If $callback is specified it is taken as a reference to a perl sub, and 2184each row returned by the query is passed to this subroutine I<instead> of 2185being returned by the routine (to allow processing of large result sets, 2186for example). 2187 2188If $options is specified and is a HASH ref, the following keys affect the 2189value returned by nsql(): 2190 2191=over 4 2192 2193=item oktypes => [...] 2194 2195This generalises I<syb_nsql_nostatus> (see below) by ignoring any result sets 2196which are of a type not listed. 2197 2198=item bytype => 0|1|'merge' 2199 2200If this option is set to a true value, each result set will be returned as the 2201value of a hash, the key of which is the result type of this result set as defined 2202by the CS_*_TYPE values described above. If the special value 'merge' is used, 2203result sets of the same type will be catenated (as nsql() does by default) into 2204a single array of results and the result of the nsql() call will be a single hash 2205keyed by result type. Usage is better written %data = $dbh->syb_nsql(...) in this 2206case. 2207 2208=item arglist => [...] 2209 2210This option provides support for placeholders in the SQL query passed to nsql(). 2211Each time the SQL statement is executed the array value of this option will be 2212passed as the parameter list to the execute() method. 2213 2214=back 2215 2216Note that if $callback is omitted, a hash reference in that parameter position 2217will be interpreted as an option hash if no hash reference is found in the 2218$options parameter position. 2219 2220C<nsql> also checks three special attributes to enable deadlock retry logic 2221(I<Note> none of these attributes have any effect anywhere else at the moment): 2222 2223=over 4 2224 2225=item syb_deadlock_retry I<count> 2226 2227Set this to a non-0 value to enable deadlock detection and retry logic within 2228nsql(). If a deadlock error is detected (error code 1205) then the entire 2229batch is re-submitted up to I<syb_deadlock_retry> times. Default is 0 (off). 2230 2231=item syb_deadlock_sleep I<seconds> 2232 2233Number of seconds to sleep between deadlock retries. Default is 60. 2234 2235=item syb_deadlock_verbose (bool) 2236 2237Enable verbose logging of deadlock retry logic. Default is off. 2238 2239=item syb_nsql_nostatus (bool) 2240 2241If true then stored procedure return status values (i.e. results of type 2242CS_STATUS_RESULT) are ignored. 2243 2244=back 2245 2246Deadlock detection will be added to the $dbh->do() method in a future 2247version of DBD::Sybase. 2248 2249=head1 Multi-Threading 2250 2251DBD::Sybase is thread-safe (i.e. can be used in a multi-threaded 2252perl application where more than one thread accesses the database 2253server) with the following restrictions: 2254 2255=over 4 2256 2257=item * perl version >= 5.8 2258 2259DBD::Sybase requires the use of I<ithreads>, available in the perl 5.8.0 2260release. It will not work with the older 5.005 threading model. 2261 2262=item * Sybase thread-safe libraries 2263 2264Sybase's Client Library comes in two flavors. DBD::Sybase must find the 2265thread-safe version of the libraries (ending in _r on Unix/linux). This 2266means Open Client 11.1.1 or later. In particular this means that you can't 2267use the 10.0.4 libraries from the free 11.0.3.3 release on linux if you 2268want to use multi-threading. 2269 2270Note: when using perl >= 5.8 with the thread-safe libraries (libct_r.so, etc) 2271then signal handling is broken and any signal delivered to the perl process 2272will result in a segmentation fault. It is recommended in that case to 2273link with the non-threadsafe libraries. 2274 2275=item * use DBD::Sybase 2276 2277You I<must> include the C<use DBD::Sybase;> line in your program. This 2278is needed because DBD::Sybase needs to do some setup I<before> the first 2279thread is started. 2280 2281=back 2282 2283You can check to see if your version of DBD::Sybase is thread-safe at 2284run-time by calling DBD::Sybase::thread_enabled(). This will return 2285I<true> if multi-threading is available. 2286 2287See t/thread.t for a simple example. 2288 2289=head1 BUGS 2290 2291You can run out of space in the tempdb database if you use a lot of 2292calls with bind variables (ie ?-style placeholders) without closing the 2293connection and Sybase 11.5.x or older. This is because 2294Sybase creates stored procedures for each prepare() call. 2295In 11.9.x and later Sybase will create "light-weight" stored procedures 2296which don't use up any space in the tempdb database. 2297 2298The primary_key_info() method will only return data for tables 2299where a declarative "primary key" constraint was included when the table 2300was created. 2301 2302I have a simple bug tracking database at http://www.peppler.org/bugdb/ . 2303You can use it to view known problems, or to report new ones. 2304 2305 2306=head1 SEE ALSO 2307 2308L<DBI> 2309 2310Sybase OpenClient C manuals. 2311 2312Sybase Transact SQL manuals. 2313 2314=head1 AUTHOR 2315 2316DBD::Sybase by Michael Peppler 2317 2318=head1 COPYRIGHT 2319 2320The DBD::Sybase module is Copyright (c) 1996-2007 Michael Peppler. 2321The DBD::Sybase module is free software; you can redistribute it and/or 2322modify it under the same terms as Perl itself. 2323 2324=head1 ACKNOWLEDGEMENTS 2325 2326Tim Bunce for DBI, obviously! 2327 2328See also L<DBI/ACKNOWLEDGEMENTS>. 2329 2330=cut 2331