-
Notifications
You must be signed in to change notification settings - Fork 3
/
Sybase.pm
2355 lines (1727 loc) · 77.4 KB
/
Sybase.pm
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
# -*-Perl-*-
# Copyright (c) 1996-2023 Michael Peppler
#
# You may distribute under the terms of either the GNU General Public
# License or the Artistic License, as specified in the Perl README file.
#
# Based on DBD::Oracle Copyright (c) 1994,1995,1996,1997 Tim Bunce
{
package DBD::Sybase;
use DBI ();
use DynaLoader ();
use Exporter ();
use Sys::Hostname ();
@ISA = qw(DynaLoader Exporter);
@EXPORT = qw(CS_ROW_RESULT CS_CURSOR_RESULT CS_PARAM_RESULT
CS_STATUS_RESULT CS_MSG_RESULT CS_COMPUTE_RESULT);
$hostname = Sys::Hostname::hostname();
$init_done = 0;
$VERSION = '1.24';
require_version DBI 1.30;
# dl_open() calls need to use the RTLD_GLOBAL flag if
# you are going to use the Kerberos libraries.
# There are systems / OSes where this does not work (AIX 5.x, for example)
# set to 1 to get RTLD_GLOBAL turned on.
sub dl_load_flags { 0x00 }
bootstrap DBD::Sybase $VERSION;
$drh = undef; # holds driver handle once initialised
sub driver {
return $drh if $drh;
my ( $class, $attr ) = @_;
$class .= "::dr";
($drh) = DBI::_new_drh(
$class,
{
'Name' => 'Sybase',
'Version' => $VERSION,
'Attribution' => 'Sybase DBD by Michael Peppler',
}
);
if ( $DBI::VERSION >= 1.37 && !$DBD::Sybase::init_done ) {
DBD::Sybase::db->install_method('syb_nsql');
DBD::Sybase::db->install_method('syb_date_fmt');
DBD::Sybase::db->install_method('syb_isdead');
DBD::Sybase::st->install_method('syb_ct_get_data');
DBD::Sybase::st->install_method('syb_ct_data_info');
DBD::Sybase::st->install_method('syb_ct_send_data');
DBD::Sybase::st->install_method('syb_ct_prepare_send');
DBD::Sybase::st->install_method('syb_ct_finish_send');
DBD::Sybase::st->install_method('syb_output_params');
DBD::Sybase::st->install_method('syb_describe');
++$DBD::Sybase::init_done;
}
$drh;
}
sub CLONE {
undef $drh;
}
1;
}
{
package DBD::Sybase::dr; # ====== DRIVER ======
use strict;
sub connect {
my ( $drh, $dbase, $user, $auth, $attr ) = @_;
my $server = $dbase || $ENV{DSQUERY} || 'SYBASE';
my ($this) = DBI::_new_dbh(
$drh,
{
'Name' => $server,
'Username' => $user,
'CURRENT_USER' => $user,
}
);
DBD::Sybase::db::_login( $this, $server, $user, $auth, $attr )
or return undef;
return $this;
}
sub data_sources {
my @s;
if ( $^O eq 'MSWin32' ) {
open( INTERFACES, "$ENV{SYBASE}/ini/sql.ini" ) or return;
@s = map { /\[(\S+)\]/i; "dbi:Sybase:server=$1" } grep /\[/i,
<INTERFACES>;
close(INTERFACES);
} else {
open( INTERFACES, "$ENV{SYBASE}/interfaces" ) or return;
@s = map { /^(\S+)/i; "dbi:Sybase:server=$1" } grep /^[^\s\#]/i,
<INTERFACES>;
close(INTERFACES);
}
return @s;
}
}
{
package DBD::Sybase::db; # ====== DATABASE ======
use strict;
use DBI qw(:sql_types);
use Carp;
sub prepare {
my ( $dbh, $statement, $attribs ) = @_;
# create a 'blank' sth
my $sth = DBI::_new_sth( $dbh, { 'Statement' => $statement, } );
DBD::Sybase::st::_prepare( $sth, $statement, $attribs )
or return undef;
$sth;
}
# prepare_cached doesn't really work correctly with Sybase, given that you can't easily have
# more than one active statement handle for a given database handle.
# You only get the advantage of not having to re-parse/compile
# the statement *if* you have placeholders in the statement.
# In other cases the driver will attempt to open a new connection if more than one statement handle is needed
# which will cause things like transactions to behave incorrectly.
sub prepare_cached {
my ( $dbh, $statement, $attribs, $if_active ) = @_;
# We ignore the $if_active attribute...
# always prepare a new statement
return prepare($dbh, $statement, $attribs);
}
sub tables {
my $dbh = shift;
my $catalog = shift;
my $schema = shift || '%';
my $table = shift || '%';
my $type = shift || '%';
$type =~ s/[\'\"\s]//g; # strip quotes and spaces
if ( $type =~ /,/ ) { # multiple types
$type =
'[' . join( '', map { substr( $_, 0, 1 ) } split /,/, $type ) . ']';
} else {
$type = substr( $type, 0, 1 );
}
$type =~ s/T/U/;
my $sth;
if ( $catalog and $catalog ne '%' ) {
$sth =
$dbh->prepare(
"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'"
);
} else {
$sth =
$dbh->prepare(
"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'"
);
}
$sth->execute;
my @names;
my $dat;
while ( $dat = $sth->fetch ) {
push( @names, $dat->[0] );
}
@names;
}
# NOTE - RaiseError & PrintError is turned off while we are inside this
# function, so we must check for any error, and return immediately if
# any error is found.
# XXX add optional deadlock detection?
sub do {
my ( $dbh, $statement, $attr, @params ) = @_;
my $sth = $dbh->prepare( $statement, $attr ) or return undef;
$sth->execute(@params) or return undef;
return undef if $sth->err;
if ( defined( $sth->{syb_more_results} ) ) {
{
while ( my $dat = $sth->fetch ) {
return undef if $sth->err;
# XXX do something intelligent here...
}
redo if $sth->{syb_more_results};
}
}
my $rows = $sth->rows;
( $rows == 0 ) ? "0E0" : $rows;
}
# This will only work if the statement handle used to do the insert
# has been properly freed. Otherwise this will try to fetch @@identity
# from a different (new!) connection - which is obviously wrong.
sub last_insert_id {
my ( $dbh, $catalog, $schema, $table, $field, $attr ) = @_;
# parameters are ignored.
my $sth = $dbh->prepare('select @@identity');
if ( !$sth->execute ) {
return undef;
}
my $value;
($value) = $sth->fetchrow_array;
$sth->finish;
return $value;
}
sub table_info {
my $dbh = shift;
my $catalog = $dbh->quote(shift);
my $schema = $dbh->quote(shift);
my $table = $dbh->quote(shift);
my $type = $dbh->quote(shift);
# https://github.com/mpeppler/DBD-Sybase/issues/53
# sp_tables is broken in ASE 15 and later...
#my $sth = $dbh->prepare("sp_tables $table, $schema, $catalog, $type");
my $sth = $dbh->prepare( q{
select TABLE_QUALIFIER = db_name()
, TABLE_OWNER = u.name
, TABLE_NAME = o.name
, TABLE_TYPE =
case o.type
when 'U' then 'TABLE'
when 'V' then 'VIEW'
when 'S' then 'SYSTEM TABLE'
end
, REMARKS = NULL
from sysobjects o
join sysusers u
on u.uid = o.uid
where o.type in ('U', 'V', 'S')
and o.id > 99
});
$sth->execute;
$sth;
}
{
my $names = [
qw(TABLE_CAT TABLE_SCHEM TABLE_NAME COLUMN_NAME DATA_TYPE
TYPE_NAME COLUMN_SIZE BUFFER_LENGTH DECIMAL_DIGITS
NUM_PREC_RADIX NULLABLE REMARKS COLUMN_DEF SQL_DATA_TYPE
SQL_DATETIME_SUB CHAR_OCTET_LENGTH ORDINAL_POSITION
IS_NULLABLE
)
];
# Technique of using DBD::Sponge borrowed from DBD::mysql...
sub column_info {
my $dbh = shift;
my $catalog = $dbh->quote(shift);
my $schema = $dbh->quote(shift);
my $table = $dbh->quote(shift);
my $column = $dbh->quote(shift);
my $sth = $dbh->prepare("sp_columns $table, $schema, $catalog, $column");
return undef unless $sth;
if ( !$sth->execute() ) {
return DBI::set_err( $dbh, $sth->err(), $sth->errstr() );
}
my @cols;
while ( my $d = $sth->fetchrow_arrayref() ) {
push( @cols, [ @$d[ 0 .. 11 ], @$d[ 14 .. 19 ] ] );
}
my $dbh2;
if ( !( $dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'} ) ) {
$dbh2 = $dbh->{'~dbd_driver~_sponge_dbh'} = DBI->connect("DBI:Sponge:");
if ( !$dbh2 ) {
DBI::set_err( $dbh, 1, $DBI::errstr );
return undef;
}
}
my $sth2 = $dbh2->prepare(
"SHOW COLUMNS",
{
'rows' => \@cols,
'NAME' => $names,
'NUM_OF_FIELDS' => scalar(@$names)
}
);
if ( !$sth2 ) {
DBI::set_err( $sth2, $dbh2->err(), $dbh2->errstr() );
}
$sth2->execute;
$sth2;
}
}
sub primary_key_info {
my $dbh = shift;
my $catalog = $dbh->quote(shift); # == database in Sybase terms
my $schema = $dbh->quote(shift); # == owner in Sybase terms
my $table = $dbh->quote(shift);
my $sth = $dbh->prepare("sp_pkeys $table, $schema, $catalog");
$sth->execute;
$sth;
}
sub foreign_key_info {
my $dbh = shift;
my $pk_catalog = $dbh->quote(shift); # == database in Sybase terms
my $pk_schema = $dbh->quote(shift); # == owner in Sybase terms
my $pk_table = $dbh->quote(shift);
my $fk_catalog = $dbh->quote(shift); # == database in Sybase terms
my $fk_schema = $dbh->quote(shift); # == owner in Sybase terms
my $fk_table = $dbh->quote(shift);
my $sth =
$dbh->prepare(
"sp_fkeys $pk_table, $pk_schema, $pk_catalog, $fk_table, $fk_schema, $fk_catalog"
);
$sth->execute;
$sth;
}
sub statistics_info {
my $dbh = shift;
my $catalog = $dbh->quote(shift); # == database in Sybase terms
my $schema = $dbh->quote(shift); # == owner in Sybase terms
my $table = $dbh->quote(shift);
my $is_unique = shift;
my $quick = shift;
my $sth =
$dbh->prepare(
"sp_indexes \@\@servername, $table, $schema, $catalog, NULL, $is_unique");
$sth->execute;
$sth;
}
sub ping_pl { # old code - now implemented by syb_ping() in dbdimp.c
my $dbh = shift;
return 0 if DBD::Sybase::db::_isdead($dbh);
# Use "select 1" suggested by Henri Asseily.
my $sth = $dbh->prepare("select 1");
return 0 if !$sth;
my $rc = $sth->execute;
# Changed && to || for 1.07.
return 0 if ( !defined($rc) || DBD::Sybase::db::_isdead($dbh) );
$sth->finish;
return 1;
}
# Allows us to cache this data as it is static.
my @type_info;
sub type_info_all {
my ($dbh) = @_;
if(scalar(@type_info) > 0) {
return \@type_info;
}
# Calling sp_datatype_info returns the appropriate data for the server that
# we are currently connected to.
# In general the data is static, so it's not really necessary, but ASE 12.5
# introduces some changes, in particular char/varchar max lenghts that depend
# on the server's page size. 12.5.1 introduces the DATE and TIME datatypes.
my $sth = $dbh->prepare("sp_datatype_info");
my $data;
if ( $sth->execute ) {
$data = $sth->fetchall_arrayref;
}
my $ti = [
{
TYPE_NAME => 0,
DATA_TYPE => 1,
PRECISION => 2,
LITERAL_PREFIX => 3,
LITERAL_SUFFIX => 4,
CREATE_PARAMS => 5,
NULLABLE => 6,
CASE_SENSITIVE => 7,
SEARCHABLE => 8,
UNSIGNED_ATTRIBUTE => 9,
MONEY => 10,
AUTO_INCREMENT => 11,
LOCAL_TYPE_NAME => 12,
MINIMUM_SCALE => 13,
MAXIMUM_SCALE => 14,
sql_data_type => 15,
sql_datetime_sub => 16,
num_prec_radix => 17,
interval_precision => 18,
USERTYPE => 19
},
];
# ASE 11.x only returns 13 columns, MS-SQL return 20...
my $columnCount = @{ $data->[0] };
foreach my $columnName ( keys( %{ $ti->[0] } ) ) {
if ( $ti->[0]->{$columnName} >= $columnCount ) {
delete( $ti->[0]->{$columnName} );
}
}
push( @$ti, @$data );
foreach (@$ti) {
push(@type_info, $_);
}
return \@type_info;
}
# First straight port of DBlib::nsql.
# mpeppler, 2/19/01
# Updated by Merijn Broeren 4/17/2007
# This version *can* handle ? placeholders
sub nsql {
my ( $dbh, $sql, $type, $callback, $option ) = @_;
my ( @res, %resbytype );
my $retrycount = $dbh->FETCH('syb_deadlock_retry');
my $retrysleep = $dbh->FETCH('syb_deadlock_sleep') || 60;
my $retryverbose = $dbh->FETCH('syb_deadlock_verbose');
my $nostatus = $dbh->FETCH('syb_nsql_nostatus');
$option = $callback if ref($callback) eq 'HASH' and ref($option) ne 'HASH';
my $bytype = $option->{bytype} || 0;
my $merge = $bytype eq 'merge';
my @default_types = (
DBD::Sybase::CS_ROW_RESULT(), DBD::Sybase::CS_CURSOR_RESULT(),
DBD::Sybase::CS_PARAM_RESULT(), DBD::Sybase::CS_MSG_RESULT(),
DBD::Sybase::CS_COMPUTE_RESULT()
);
my $oktypes = $option->{oktypes}
|| (
$nostatus
? [@default_types]
: [ @default_types, DBD::Sybase::CS_STATUS_RESULT() ]
);
my %oktypes = map { ( $_ => 1 ) } @$oktypes;
my @params = $option->{arglist} ? @{ $option->{arglist} } : ();
if ( ref $type ) {
$type = ref $type;
} elsif ( not defined $type ) {
$type = "";
}
my $sth = $dbh->prepare($sql);
return unless $sth;
my $raiserror = $dbh->FETCH('RaiseError');
my $errstr;
my $err;
# Rats - RaiseError doesn't seem to work inside of this routine.
# So we fake it with lots of die() statements.
# $sth->{RaiseError} = 1;
DEADLOCK:
{
# Initialize $err before each iteration through this loop.
# Otherwise, we inherit the value from the previous failure.
$err = undef;
# ditto for @res, %resbytype
@res = ();
%resbytype = ();
# Use RaiseError technique to throw a fatal error if anything goes
# wrong in the execute or fetch phase.
eval {
$sth->execute(@params) || die $sth->errstr;
{
my $result_type = $sth->{syb_result_type};
my ( @set, $data );
if ( not exists $oktypes{$result_type} ) {
while ( $data = $sth->fetchrow_arrayref ) {
; # do not include return status rows..
}
} elsif ( $type eq "HASH" ) {
while ( $data = $sth->fetchrow_hashref ) {
die $sth->errstr if ( $sth->err );
if ( ref $callback eq "CODE" ) {
unless ( $callback->(%$data) ) {
return;
}
} else {
push( @set, {%$data} );
}
}
} elsif ( $type eq "ARRAY" ) {
while ( $data = $sth->fetchrow_arrayref ) {
die $sth->errstr if ( $sth->err );
if ( ref $callback eq "CODE" ) {
unless ( $callback->(@$data) ) {
return;
}
} else {
push( @set, ( @$data == 1 ? $$data[0] : [@$data] ) );
}
}
} else {
# If you ask for nothing, you get nothing. But suck out
# the data just in case.
while ( $data = $sth->fetch ) { 1; }
# NB this is actually *counting* the result sets which are not ignored above
$res[0]++; # Return non-null (true)
}
die $sth->errstr if ( $sth->err );
if (@set) {
if ($merge) {
$resbytype{$result_type} ||= [];
push @{ $resbytype{$result_type} }, @set;
} elsif ($bytype) {
push @res, { $result_type => [@set] };
} else {
push @res, @set;
}
}
redo if $sth->{syb_more_results};
}
};
# If $@ is set then something failed in the eval{} call above.
if ($@) {
$errstr = $@;
$err = $sth->err || $dbh->err;
if ( $retrycount && $err == 1205 ) {
if ( $retrycount < 0 || $retrycount-- ) {
carp "SQL deadlock encountered. Retrying...\n"
if $retryverbose;
sleep($retrysleep);
redo DEADLOCK;
} else {
carp "SQL deadlock retry failed ",
$dbh->FETCH('syb_deadlock_retry'), " times. Aborting.\n"
if $retryverbose;
last DEADLOCK;
}
}
last DEADLOCK;
}
}
#
# If we picked any sort of error, then don't feed the data back.
#
if ($err) {
if ($raiserror) {
croak($errstr);
}
return;
} elsif ( ref $callback eq "CODE" ) {
return 1;
} else {
if ($merge) {
return %resbytype;
} else {
return @res;
}
}
}
if ( $DBI::VERSION >= 1.37 ) {
*syb_nsql = *nsql;
}
}
{
package DBD::Sybase::st; # ====== STATEMENT ======
use strict;
sub syb_output_params {
my ($sth) = @_;
my @results;
my $status;
{
while ( my $d = $sth->fetch ) {
# The tie() doesn't work here, so call the FETCH method
# directly....
if ( $sth->FETCH('syb_result_type') == 4042 ) {
push( @results, @$d );
} elsif ( $sth->FETCH('syb_result_type') == 4043 ) {
$status = $d->[0];
}
}
redo if $sth->FETCH('syb_more_results');
}
# XXX What to do if $status != 0???
@results;
}
sub exec_proc {
my ($sth) = @_;
my @results;
my $status;
$sth->execute || return undef;
{
while ( my $d = $sth->fetch ) {
# The tie() doesn't work here, so call the FETCH method
# directly....
if ( $sth->FETCH('syb_result_type') == 4043 ) {
$status = $d->[0];
}
}
redo if $sth->FETCH('syb_more_results');
}
# XXX What to do if $status != 0???
$status;
}
}
1;
__END__
=head1 NAME
DBD::Sybase - Sybase database driver for the DBI module
=head1 SYNOPSIS
use DBI;
$dbh = DBI->connect("dbi:Sybase:", $user, $passwd);
# See the DBI module documentation for full details
=head1 DESCRIPTION
DBD::Sybase is a Perl module which works with the DBI module to provide
access to Sybase databases.
=head1 Connecting to Sybase
=head2 The interfaces file
The DBD::Sybase module is built on top of the Sybase I<Open Client Client
Library> API. This library makes use of the Sybase I<interfaces> file
(I<sql.ini> on Win32 machines) to make a link between a logical
server name (e.g. SYBASE) and the physical machine / port number that
the server is running on. The OpenClient library uses the environment
variable B<SYBASE> to find the location of the I<interfaces> file,
as well as other files that it needs (such as locale files). The B<SYBASE>
environment is the path to the Sybase installation (eg '/usr/local/sybase').
If you need to set it in your scripts, then you I<must> set it in a
C<BEGIN{}> block:
BEGIN {
$ENV{SYBASE} = '/opt/sybase/11.0.2';
}
my $dbh = DBI->connect('dbi:Sybase:', $user, $passwd);
=head2 Specifying the server name
The server that DBD::Sybase connects to defaults to I<SYBASE>, but
can be specified in two ways.
You can set the I<DSQUERY> environement variable:
$ENV{DSQUERY} = "ENGINEERING";
$dbh = DBI->connect('dbi:Sybase:', $user, $passwd);
Or you can pass the server name in the first argument to connect():
$dbh = DBI->connect("dbi:Sybase:server=ENGINEERING", $user, $passwd);
=head2 Specifying other connection specific parameters
It is sometimes necessary (or beneficial) to specify other connection
properties. Currently the following are supported:
=over 4
=item server
Specify the server that we should connect to.
$dbh = DBI->connect("dbi:Sybase:server=BILLING",
$user, $passwd);
The default server is I<SYBASE>, or the value of the I<$DSQUERY> environment
variable, if it is set.
=item host
=item port
If you built DBD::Sybase with OpenClient 12.5.1 or later, then you can
use the I<host> and I<port> values to define the server you want to
connect to. This will by-pass the server name lookup in the interfaces file.
This is useful in the case where the server hasn't been entered in the
interfaces file.
$dbh = DBI->connect("dbi:Sybase:host=db1.domain.com;port=4100",
$user, $passwd);
=item maxConnect
By default DBD::Sybase (and the underlying OpenClient libraries) is limited
to openening 25 simultaneous connections to one or more database servers.
If you need more than 25 connections at the same time, you can use the
I<maxConnect> option to increase this number.
$dbh = DBI->connect("dbi:Sybase:maxConnect=100",
$user, $passwd);
=item database
Specify the database that should be made the default database.
$dbh = DBI->connect("dbi:Sybase:database=sybsystemprocs",
$user, $passwd);
This is equivalent to
$dbh = DBI->connect('dbi:Sybase:', $user, $passwd);
$dbh->do("use sybsystemprocs");
=item charset
Specify the character set that the client uses.
$dbh = DBI->connect("dbi:Sybase:charset=iso_1",
$user, $passwd);
The default charset used depends on the locale that the application runs
in. If you wish to interact with unicode varaiables (see syb_enable_utf8, below) then
you should set charset=utf8. Note however that this means that Sybase will expect all
data sent to it for char/varchar columns to be encoded in utf8 (e.g. sending iso8859-1 characters
like e-grave, etc).
=item language
Specify the language that the client uses.
$dbh = DBI->connect("dbi:Sybase:language=us_english",
$user, $passwd);
Note that the language has to have been installed on the server (via
langinstall or sp_addlanguage) for this to work. If the language is not
installed the session will default to the default language of the
server.
=item packetSize
Specify the network packet size that the connection should use. Using a
larger packet size can increase performance for certain types of queries.
See the Sybase documentation on how to enable this feature on the server.
$dbh = DBI->connect("dbi:Sybase:packetSize=8192",
$user, $passwd);
=item interfaces
Specify the location of an alternate I<interfaces> file:
$dbh = DBI->connect("dbi:Sybase:interfaces=/usr/local/sybase/interfaces",
$user, $passwd);
=item loginTimeout
Specify the number of seconds that DBI->connect() will wait for a
response from the Sybase server. If the server fails to respond before the
specified number of seconds the DBI->connect() call fails with a timeout
error. The default value is 60 seconds, which is usually enough, but on a busy
server it is sometimes necessary to increase this value:
$dbh = DBI->connect("dbi:Sybase:loginTimeout=240", # wait up to 4 minutes
$user, $passwd);
=item timeout
Specify the number of seconds after which any Open Client calls will timeout
the connection and mark it as dead. Once a timeout error has been received
on a connection it should be closed and re-opened for further processing.
Setting this value to 0 or a negative number will result in an unlimited
timeout value. See also the Open Client documentation on CS_TIMEOUT.
$dbh = DBI->connect("dbi:Sybase:timeout=240", # wait up to 4 minutes
$user, $passwd);
=item scriptName
Specify the name for this connection that will be displayed in sp_who
(ie in the sysprocesses table in the I<program_name> column).
$dbh=DBI->connect("dbi:Sybase:scriptName=myScript", $user, $password);
=item hostname
Specify the hostname that will be displayed by sp_who (and will be stored
in the hostname column of sysprocesses)..
$dbh=DBI->connect("dbi:Sybase:hostname=kiruna", $user, $password);
=item tdsLevel
Specify the TDS protocol level to use when connecting to the server.
Valid values are CS_TDS_40, CS_TDS_42, CS_TDS_46, CS_TDS_495 and CS_TDS_50.
In general this is automatically negotiated between the client and the
server, but in certain cases this may need to be forced to a lower level
by the client.
$dbh=DBI->connect("dbi:Sybase:tdsLevel=CS_TDS_42", $user, $password);
B<NOTE>: Setting the tdsLevel below CS_TDS_495 will disable a number of
features, ?-style placeholders and CHAINED non-AutoCommit mode, in particular.
=item encryptPassword
Specify the use of the client password encryption supported by CT-Lib.
Specify a value of 1 to use encrypted passwords. Set to a value > 1 to also
enable asymetric password encryption.
$dbh=DBI->connect("dbi:Sybase:encryptPassword=1", $user, $password);
=item kerberos
Note: Requires OpenClient 11.1.1 or later.
Sybase and OpenClient can use Kerberos to perform network-based login.
If you use Kerberos for authentication you can use this feature and pass
a kerberos serverprincipal using the C<kerberos=value> parameter:
$dbh = DBI->connect("dbi:Sybase:kerberos=$serverprincipal", '', '');
In addition, if you have a system for retrieving Kerberos serverprincipals at
run-time you can tell DBD::Sybase to call a perl subroutine to get
the serverprincipal from connect():
sub sybGetPrinc {
my $srv = shift;
return the serverprincipal...
}
$dbh = DBI->connect('dbi:Sybase:server=troll', '', '', { syb_kerberos_serverprincipal => \&sybGetPrinc });
The subroutine will be called with one argument (the server that we will
connect to, using the normal Sybase behavior of checking the DSQUERY
environment variable if no server is specified in the connect()) and is
expected to return a string (the Kerberos serverprincipal) to the caller.
=item sslCAFile
Specify the location of an alternate I<trusted.txt> file for SSL
connection negotiation:
$dbh->DBI->connect("dbi:Sybase:sslCAFile=/usr/local/sybase/trusted.txt.ENGINEERING", $user, $password);
=item bulkLogin
Set this to 1 if the connection is going to be used for a bulk-load
operation (see I<Experimental Bulk-Load functionality> elsewhere in this
document.)
$dbh->DBI->connect("dbi:Sybase:bulkLogin=1", $user, $password);
=item serverType
Tell DBD::Sybase what the server type is. Defaults to ASE. Setting it to
something else will prevent certain actions (such as setting options,
fetching the ASE version via @@version, etc.) and avoid spurious errors.
=item tds_keepalive
Set this to 1 to tell OpenClient to enable the KEEP_ALIVE attribute on the
connection. Default 1.
=back
These different parameters (as well as the server name) can be strung
together by separating each entry with a semi-colon:
$dbh = DBI->connect("dbi:Sybase:server=ENGINEERING;packetSize=8192;language=us_english;charset=iso_1",
$user, $pwd);
=head1 Handling Multiple Result Sets
Sybase's Transact SQL has the ability to return multiple result sets
from a single SQL statement. For example the query:
select b.title, b.author, s.amount
from books b, sales s
where s.authorID = b.authorID
order by b.author, b.title
compute sum(s.amount) by b.author
which lists sales by author and title and also computes the total sales
by author returns two types of rows. The DBI spec doesn't really
handle this situation, nor the more hairy
exec my_proc @p1='this', @p2='that', @p3 out
where C<my_proc> could return any number of result sets (ie it could
perform an unknown number of C<select> statements.
I've decided to handle this by returning an empty row at the end
of each result set, and by setting a special Sybase attribute in $sth
which you can check to see if there is more data to be fetched. The
attribute is B<syb_more_results> which you should check to see if you
need to re-start the C<fetch()> loop.
To make sure all results are fetched, the basic C<fetch> loop can be
written like this:
{
while($d = $sth->fetch) {
... do something with the data
}
redo if $sth->{syb_more_results};
}
You can get the type of the current result set with
$sth->{syb_result_type}. This returns a numerical value, as defined in
$SYBASE/$SYBASE_OCS/include/cspublic.h:
#define CS_ROW_RESULT (CS_INT)4040
#define CS_CURSOR_RESULT (CS_INT)4041
#define CS_PARAM_RESULT (CS_INT)4042
#define CS_STATUS_RESULT (CS_INT)4043
#define CS_MSG_RESULT (CS_INT)4044
#define CS_COMPUTE_RESULT (CS_INT)4045
In particular, the return status of a stored procedure is returned
as CS_STATUS_RESULT (4043), and is normally the last result set that is
returned in a stored proc execution, but see the B<syb_do_proc_status>
attribute for an alternative way of handling this result type. See B<Executing
Stored Procedures> elsewhere in this document for more information.
If you add a
use DBD::Sybase;
to your script then you can use the symbolic values (CS_xxx_RESULT)
instead of the numeric values in your programs, which should make them
easier to read.