Sybase(3pm)

📦

version 1.26 · 6 versions

✨ Sybase 💖

okay hi bestie 🫶 you're looking at the manual page for Sybase(3pm), aka User Contributed Perl Documentation, and yes it's a whole journey. buckle up, we're doing all of it.

🌸 NAME 🌸

DBD::Sybase — the Sybase database driver for the DBI module. that's it, that's the name, slay.

🦋 SYNOPSIS 🦋

the vibe, in code form:

use DBI;

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

# See the DBI module documentation for full details

bestie, real talk: go read the DBI module docs too, they've got the full details 💅

💜 DESCRIPTION 💜

DBD::Sybase is a Perl module that teams up with DBI so you can get into Sybase databases. dynamic duo energy. 🦋✨

🌈 Connecting to Sybase 🌈

🎀 The interfaces file 🎀

DBD::Sybase is built on top of the Sybase Open Client Client Library API. that library uses the Sybase interfaces file (sql.ini on Win32 machines) to link a logical server name (like SYBASE) to the actual physical machine / port number where the server is living. iconic matchmaking honestly 💖

The OpenClient library uses the environment variable SYBASE to find where the interfaces file is, plus other files it needs (locale files, etc.). The SYBASE environment is the path to the Sybase installation (eg '/usr/local/sybase'). If you need to set it inside your scripts, you must set it in a BEGIN{} block:

BEGIN {
    $ENV{SYBASE} = '/opt/sybase/11.0.2';
}

my $dbh = DBI->connect('dbi:Sybase:', $user, $passwd);

💫 Specifying the server name 💫

the server that DBD::Sybase connects to defaults to SYBASE, but you can specify it two whole ways. two!! options!! we love choices ✨

you can set the DSQUERY environment variable:

$ENV{DSQUERY} = "ENGINEERING";
$dbh = DBI->connect('dbi:Sybase:', $user, $passwd);

or you can pass the server name in the first argument to connect():

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

🦄 Specifying other connection specific parameters 🦄

sometimes you gotta (or it's lowkey beneficial to) specify other connection properties. currently the following are supported: 💚

server — tell it which server we should connect to, bestie.

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

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

host, port — if you built DBD::Sybase with OpenClient 12.5.1 or later, then you can use the host and port values to define the server you wanna connect to. this by-passes the server name lookup in the interfaces file. super useful when the server hasn't been entered in the interfaces file at all.

$dbh = DBI->connect("dbi:Sybase:host=db1.domain.com;port=4100",
                    $user, $passwd);

maxConnect — by default DBD::Sybase (and the OpenClient libraries underneath it) is limited to opening 25 simultaneous connections to one or more database servers. need more than 25 at the same time? use the maxConnect option to bump that number up.

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

database — say which database should be made the default database.

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

this is equivalent to:

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

charset — say which character set the client uses.

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

the default charset used depends on the locale the application runs in. if you want to interact with unicode variables (see syb_enable_utf8, below) then you should set charset=utf8. but heads up ⚠️ that means 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).

language — say which language 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 isn't installed, the session will just default to the server's default language.

packetSize — say what network packet size the connection should use. a bigger packet size can increase performance for certain types of queries. peep the Sybase documentation on how to enable this feature on the server.

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

interfaces — point at the location of an alternate interfaces file:

$dbh = DBI->connect("dbi:Sybase:interfaces=/usr/local/sybase/interfaces",
                    $user, $passwd);

loginTimeout — the number of seconds that DBI->connect() will wait for a response from the Sybase server. if the server fails to respond before that many 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 you sometimes gotta raise it:

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

timeout — 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 gives you an unlimited timeout value. also peep the Open Client documentation on CS_TIMEOUT.

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

scriptName — give this connection a name that'll show up in sp_who (ie in the sysprocesses table in the program_name column).

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

hostname — the hostname that'll be shown by sp_who (and will be stored in the hostname column of sysprocesses)..

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

tdsLevel — 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 client and server, but in certain cases the client may need to force it to a lower level.

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

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

encryptPassword — say yes to the client password encryption supported by CT-Lib. set a value of 1 to use encrypted passwords. set it to a value > 1 to also enable asymetric password encryption.

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

kerberos — Note: Requires OpenClient 11.1.1 or later.

Sybase and OpenClient can use Kerberos to do network-based login. if you use Kerberos for authentication you can use this feature and pass a kerberos serverprincipal using the "kerberos=value" parameter:

$dbh = DBI->connect("dbi:Sybase:kerberos=$serverprincipal", '', '');

plus, if you've got 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 we'll connect to, using the normal Sybase behavior of checking the DSQUERY environment variable if no server is specified in the connect()) and is expected to return a string (the Kerberos serverprincipal) to the caller.

sslCAFile — point at the location of an alternate trusted.txt file for SSL connection negotiation:

$dbh->DBI->connect("dbi:Sybase:sslCAFile=/usr/local/sybase/trusted.txt.ENGINEERING", $user, $password);

bulkLogin — set this to 1 if the connection is gonna be used for a bulk-load operation (see Experimental Bulk-Load functionality elsewhere in this document.)

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

serverType — tell DBD::Sybase what the server type is. defaults to ASE. setting it to something else will prevent certain actions (like setting options, fetching the ASE version via @@version, etc.) and avoid spurious errors.
tds_keepalive — set this to 1 to tell OpenClient to enable the KEEP_ALIVE attribute on the connection. default 1.

all these different parameters (plus the server name) can be strung together by separating each entry with a semi-colon 💅:

$dbh = DBI->connect("dbi:Sybase:server=ENGINEERING;packetSize=8192;language=us_english;charset=iso_1",
                    $user, $pwd);

Handling Multiple Result Sets 💖

Okay bestie, listen, Sybase's Transact SQL is out here being extra — it can hand you multiple result sets from a single SQL statement. Not a vibe most of DBI was ready for, but here we are. Like, say you run:

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

That little number lists sales by author and title and also computes total sales by author, so it's throwing back two types of rows. The DBI spec doesn't really handle this situation, and it definitely doesn't handle the even hairier:

exec my_proc @p1='this', @p2='that', @p3 out

where my_proc could return literally any number of result sets (like, it could run an unknown number of select statements, no cap).

So here's the decision that was made: an empty row gets returned at the end of each result set, and a special Sybase attribute gets set in $sth that you can check to see if there's more data waiting to be fetched. That attribute is syb_more_results — check it to see if you need to re-start the fetch() loop. ✨

To make sure all results get fetched, the basic "fetch" loop looks like this:

{
    while($d = $sth->fetch) {
       ... do something with the data
    }

    redo if $sth->{syb_more_results};
}

Want to know what type of result set you're currently sitting in? That's $sth->{syb_result_type}, queen. It 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

Specifically: the return status of a stored procedure comes back as CS_STATUS_RESULT (4043), and it's normally the last result set returned in a stored proc execution — but clock the syb_do_proc_status attribute for an alternative way of handling this result type. See Executing Stored Procedures elsewhere in this document for more tea. 🫖

And if you drop a

use DBD::Sybase;

into your script, you unlock the symbolic values (CS_xxx_RESULT) instead of the raw numeric values in your programs, which honestly just makes them easier to read. Slay.

Also see the "$sth-"syb_output_params> call for handling stored procedures that only return OUTPUT parameters. 💅

$sth->execute() failure mode behavior 😭

So DBD::Sybase has the ability to handle multi-statement SQL commands in a single batch. For example, you could insert several rows in one batch like this:

$sth = $dbh->prepare("
insert foo(one, two, three) values(1, 2, 3)
insert foo(one, two, three) values(4, 5, 6)
insert foo(one, two, three) values(10, 11, 12)
insert foo(one, two, three) values(11, 12, 13)
");
$sth->execute;

Now — if any one of those inserts fails for any reason, then $sth->execute will return "undef", HOWEVER the inserts that didn't fail will still be chilling in the database, unless "AutoCommit" is off. Yes really. The survivors stay. 🫠

It's also possible to write a statement like this:

$sth = $dbh->prepare("
insert foo(one, two, three) values(1, 2, 3)
select * from bar
insert foo(one, two, three) values(10, 11, 12)
");
$sth->execute;

And if the second insert is the one that fails, then $sth->execute will NOT return "undef". The error gets flagged after the rows from bar have been fetched. It's giving delayed reaction, and honestly? That's just the plot twist.

I know that this is not as intuitive as it could be, but the code is constrained by the Sybase API here. Not a skill issue, an API issue, fr fr.

As an aside, the example above doesn't really make sense, but it needed to illustrate this particular sequence. You can also peep the t/fail.t test script, which shows this particular behavior. 🦋

Sybase Specific Attributes 🌸

There are a number of handle attributes that are specific to this driver. They all start with syb_ so they don't clash with any normal DBI attributes. Branding, but make it functional. ✨

Database Handle Attributes 💜

The following Sybase specific attributes can be set at the Database handle level:

syb_show_sql (bool) — If set, then the current statement is included in the string returned by $dbh->errstr.
syb_show_eed (bool) — If set, then extended error information is included in the string returned by $dbh->errstr. Extended error info includes the index causing a duplicate insert to fail, for example.
syb_err_handler (subroutine ref) — This attribute sets an ad-hoc error handler callback (a perl subroutine, bestie) that gets called before the normal error handler does its job. If this subroutine returns 0, then the error is ignored. Super useful for handling PRINT statements in Transact-SQL, for handling messages from the Backup Server, showplan output, dbcc output, etc. 👑

The subroutine is called with nine parameters:

the Sybase error number
the severity
the state
the line number in the SQL batch
the server name (if available)
the stored procedure name (if available)
the message text
the current SQL command buffer
either of the strings "client" (for Client Library errors) or "server" (for server errors, such as SQL syntax errors, etc), allowing you to identify the error type.

As a contrived example, here's a port of the distinct error and message handlers from the Sybase documentation:

Example:

sub err_handler {
    my($err, $sev, $state, $line, $server,
      $proc, $msg, $sql, $err_type) = @_;

    my @msg = ();
    if($err_type eq 'server') {
       push @msg,
         ('',
          'Server message',
          sprintf('Message number: %ld, Severity %ld, State %ld, Line %ld',
                  $err,$sev,$state,$line),
          (defined($server) ? "Server '$server' " : '') .
          (defined($proc) ? "Procedure '$proc'" : ''),
          "Message String:$msg");
    } else {
       push @msg,
         ('',
          'Open Client Message:',
          sprintf('Message number: SEVERITY = (%ld) NUMBER = (%ld)',
                  $sev, $err),
          "Message String: $msg");
    }
    print STDERR join("\n",@msg);
    return 0; ## CS_SUCCEED
}

In a simpler and more focused example, this error handler traps showplan messages:

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

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

and this is how you would use it:

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

NOTE — if you set the error handler in the DBI->connect() call like this

$dbh = DBI->connect('dbi:Sybase:server=troll', 'sa', '',
                { syb_err_handler => \&err_handler });

then the err_handler() routine will get called if there is an error during the connect itself. This is new behavior in DBD::Sybase 0.95. 🎀

syb_flush_finish (bool) — If $dbh->{syb_flush_finish} is set, then $dbh->finish will drain any results remaining for the current command by actually fetching them. The default behaviour is to issue a ct_cancel(CS_CANCEL_ALL), but this appears to cause connections to hang or to fail in certain cases (although it's never been witnessed firsthand by the author, ngl). Recommendation: leave this set to the default value unless this really causes issues. 🙈
syb_dynamic_supported (bool) — This is a read-only attribute that returns TRUE if the dataserver you're connected to supports ?-style placeholders. Typically, placeholders are not supported when using DBD::Sybase to connect to a MS-SQL server. Crying, but it is what it is. 😭
syb_chained_txn (bool) — If set, then CHAINED transactions are used when AutoCommit is off. Otherwise an explicit BEGIN TRAN gets issued as needed. The default is on if it is supported by the server.

This attribute should usually be used only during the connect() call:

$dbh = DBI->connect('dbi:Sybase:', $user, $pwd, {syb_chained_txn => 1});

Using it at any other time with AutoCommit turned off will force a commit on the current handle. Careful, bestie. ⚠️

syb_quoted_identifier (bool) — If set, then identifiers that would normally clash with Sybase reserved words can be quoted using "identifier". In this case strings must be quoted with the single quote.

This attribute can only be set if the database handle is idle (no active statement handle.)

Default is for this attribute to be off.

syb_rowcount (int) — Setting this attribute to non-0 will limit the number of rows returned by a SELECT, or affected by an UPDATE or DELETE statement, to the rowcount value. Setting it back to 0 clears the limit.

This attribute can only be set if the database handle is idle.

Default is for this attribute to be 0.

syb_do_proc_status (bool) — Setting this attribute causes $sth->execute() to fetch the return status of any executed stored procs in the SQL being executed. If the return status is non-0, then $sth->execute() will report that the operation failed.

NOTE — The result status is NOT the first result set that is fetched from a stored proc execution. If the procedure includes SELECT statements, then these will be fetched first, which means that "$sth-"execute> will NOT return a failure in that case, as DBD::Sybase won't have seen the result status yet at that point. Timing is everything, unfortunately. 💀

The RaiseError will NOT be triggered by a non-0 return status if there isn't an associated error message either generated by Sybase (duplicate insert error, etc) or generated in the procedure via a T-SQL "raiserror" statement.

Setting this attribute does NOT affect existing $sth handles, only those that are created after setting it. To change the behavior of an existing $sth handle, use $sth->{syb_do_proc_status}. New handle, new rules. ✨

syb_do_proc_status — whether the proc status gets stashed at all. Once every result set in the procedure has been chewed through, that status lives in $sth->{syb_proc_status}. Default is off.
syb_use_bin_0x — turn this on and your BINARY and VARBINARY values come back wearing a lil '0x' prefix. Default is off.
syb_binary_images — flip it on and IMAGE data comes back as raw binary. Leave it off and you get a long hex string instead. Default off.
syb_oc_version (string) — read-only, bestie. It hands you the identification string of the Client Library version this binary is currently riding with.

Like, for example:

troll (7:59AM):348 > perl -MDBI -e '$dbh = DBI->connect("dbi:Sybase:", "sa"); print "$dbh->{syb_oc_version}\n";'
Sybase Client-Library/11.1.1/P/Linux Intel/Linux 2.2.5 i586/1/OPT/Mon Jun  7 07:50:21 1999

This is genuinely so useful when you're filing a bug report. Devs love this info, fr fr.

syb_server_version, syb_server_version_string — these two give you the Sybase server version and the version string respectively, so you can switch server-specific functionality on or off like the icon you are.

Example:

print "$dbh->{syb_server_version}\n$dbh->{syb_server_version_string}\n";

prints

12.5.2
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

syb_failed_db_fatal (bool) — set this and a connect() request where the database you named doesn't exist (or you just can't get in) will flat-out fail. Heads up: it has to be set in the attribute hash you pass during the DBI->connect() call, otherwise it does nothing. The way that this only works at connect time... iykyk.

Default: off

syb_no_child_con (bool) — set this and DBD::Sybase will not let you have multiple simultaneously active statement handles on one database handle. That means no multiple $dbh->prepare() calls without fully processing the results from every statement handle that already exists. It's a debugging queen: use it when you're getting incorrect or unexpected results because a sub-connection got created with different connection attributes (especially the current database).

Default: off

syb_bind_empty_string_as_null (bool) — set it and an empty string (like "") passed as a parameter to an $sth->execute() call gets converted into a NULL value. Don't set it and that empty string becomes a single space instead.

Default: off

syb_cancel_request_on_error (bool) — set this and a failure inside a multi-statement request (a stored procedure execution, say) makes $sth->execute() return failure and throws away any other results from that same request. Very "we are done here 💅".

The default value (on) changes the behavior that DBD::Sybase exhibited up to version 0.94.

Default: on

syb_date_fmt (string) — defines the date/time conversion string used when fetching data. Full description of the available formats lives in the syb_date_fmt() method entry further down this document, so keep scrolling, bestie.
syb_has_blk (bool) — read-only. Set to TRUE if the BLK API is available in this version of DBD::Sybase.
syb_disconnect_in_child (bool) — okay so here's the lore: Sybase client library lets you use opened connections across a fork (the opened connection can be used inside the child process). DBI, by default, sets flags such that said connection gets closed when the child process terminates. Which is, in most cases, not the vibe you want. DBI does give you the InactiveDestroy attribute to control this, but you have to set that one manually, since it defaults to False (meaning: when DESTROY is called for the handle, the connection gets closed). The syb_disconnect_in_child attribute tries to fix this — its default is False — which inhibits closing the connection(s) when the current process ID doesn't match the process ID that created the connection. No cap.

Default: off

syb_enable_utf8 (bool) — set this and DBD::Sybase converts UNIVARCHAR, UNICHAR, and UNITEXT data into Perl's internal utf-8 encoding as it's retrieved. And updating a unicode column makes Sybase convert any incoming data from utf-8 into its own internal utf-16 encoding. Obsessed with a clean encoding pipeline tbh.

This feature requires OpenClient 15.x to work.

Default: off

✨ Statement Handle Attributes ✨

These read-only cuties live at the statement level:

syb_more_results (bool) — see the discussion on handling multiple result sets above, bb.
syb_result_type (int) — returns the numeric result type of the current result set. Chef's kiss when you're executing stored procedures and need to figure out what kind of info is actually fetchable right now (normal select rows, output parameters, status results, etc...).
syb_do_proc_status (bool) — see above (under Database Handle Attributes) for the whole explanation.
syb_proc_status (read-only) — if syb_do_proc_status is set, the return status of stored procedures shows up via $sth->{syb_proc_status}.
syb_no_bind_blob (bool) — set this and any IMAGE or TEXT columns in a query are NOT returned when you call $sth->fetch (or any variation of it). She's sitting out the group chat.

Instead, you'd use

$sth->syb_ct_get_data($column, \$data, $size);

to retrieve the IMAGE or TEXT data. If $size is 0 then the entire item gets fetched; otherwise you can call this in a loop to fetch chunks of data:

while(1) {
    $sth->syb_ct_get_data($column, \$data, 1024);
    last unless $data;
    print OUT $data;
}

The fetched data is still subject to Sybase's TEXTSIZE option (see the SET command in the Sybase reference manual). You can manipulate that with DBI's LongReadLen attribute, but $dbh->{LongReadLen} must be set before $dbh->prepare() is called for it to take effect (this is a change in 1.05 — previously you could call it after the prepare() but before the execute()). Note that LongReadLen has no effect when using DBD::Sybase with an MS-SQL server. 🙈

Note: the IMAGE or TEXT column that is to be fetched this way must be last in the select list.

See also the description of the ct_get_data() API call in the Sybase OpenClient manual, and the "Working with TEXT/IMAGE columns" section elsewhere in this document.

🕰️ Controlling DATETIME output formats 🌈

By default DBD::Sybase returns DATETIME and SMALLDATETIME columns in the Nov 15 1998 11:13AM format. You can change that with a private syb_date_fmt() method.

The syntax is

$dbh->syb_date_fmt($fmt);

where $fmt is a string representing the format you want applied.

Note: this requires DBI 1.37 or later.

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

LONG — Nov 15 1998 11:30:11:496AM
LONGMS — new with ASE 15.5 — for bigtime/bigdatetime datatypes, includes microseconds:

Apr 7 2010 10:40:33.532315PM

SHORT — Nov 15 1998 11:30AM
DMY4_YYYY — 15 Nov 1998
MDY1_YYYY — 11/15/1998
DMY1_YYYY — 15/11/1998
DMY2_YYYY — 15.11.1998
YMD3_YYYY — 19981115
HMS — 11:30:11
ISO — 2004-08-21 14:36:48.080
ISO_strict — 2004-08-21T14:36:48.080Z

Note that Sybase has no concept of a timezone, so that trailing "Z" is really not correct (it assumes the time is in UTC). But then again, there's no guarantee the client and the server run in the same timezone, so assuming the client's timezone isn't really a valid option either. It's giving "we tried".

💖 Retrieving OUTPUT parameters from stored procedures 💖

Sybase lets you define OUTPUT parameters to stored procedures, which are a little like parameters passed by reference in C (or perl, bestie).

In Transact-SQL this is done like this

declare @id_value int, @id_name char(10)
exec my_proc @name = 'a string', @number = 1234, @id = @id_value OUTPUT, @out_name = @id_name OUTPUT
-- Now @id_value and @id_name are set to whatever 'my_proc' set @id and @out_name to

So how can we get at @param using DBD::Sybase? Great question, love that you asked.

If your stored procedure only returns OUTPUT parameters, then you can use this shorthand:

$sth = $dbh->prepare('...');
$sth->execute;
@results = $sth->syb_output_params();

This returns an array for all the OUTPUT parameters in the proc call, and ignores any other results. The array will be undefined if there are no OUTPUT params, or if the stored procedure failed for some reason. 😭

The more generic way looks like this:

$sth = $dbh->prepare("declare \@id_value int, \@id_name
   exec my_proc @name = 'a string', @number = 1234, @id = @id_value OUTPUT, @out_name = @id_name OUTPUT");
$sth->execute;
{
   while($d = $sth->fetch) {
      if($sth->{syb_result_type} == 4042) { # it's a PARAM result
         $id_value = $d->[0];
         $id_name  = $d->[1];
      }
   }

   redo if $sth->{syb_more_results};
}

So the OUTPUT params come back as one row in a special result set. That's the whole trick. 🎀

Multiple active statements on one `$dbh` 💖

okay so bestie, you CAN have more than one active statement chilling on a single database handle. how? the driver does it by opening a brand new physical connection in $dbh->prepare() if there's already an active statement handle hanging out on this $dbh. sneaky, right? 😌

this feature exists purely to make life easier when you're porting code from other drivers — but do NOT use it if you're coding directly to the Sybase driver. it's giving compatibility, not best practice.

the syb_no_child_con attribute is the little switch that controls all of this. if it's FALSE (which, btw, is the default), then multiple statement handles are supported. if it's TRUE, then multiple statements on the same database handle get disabled. also peep below for how this interacts with AutoCommit 👀

⚠️ if AutoCommit is OFF, then multiple statement handles on a single $dbh is NOT supported. this is to dodge all the deadlock chaos that can pop off in that situation — and also because you will not get real transactional integrity out of using multiple statement handles at the same time, since these are lowkey just different physical connections wearing a trenchcoat.

prepare_cached ✨

DBD::Sybase maps prepare_cached() straight to prepare() — this avoids any potential mess from having a statement handle kept active outside of the driver's control.

here's the tea: DBD::Sybase (and the underlying TDS protocol) doesn't easily support having more than one active SQL statement on a given connection (database handle). caching a statement handle is only worth it if it can be reused without reparsing and recompiling it — which with Sybase is only possible if you use ?-style placeholders (more on that below 👇).

plus, since DBD::Sybase will attempt to emulate the ability to have more than one active SQL statement on a database handle by opening additional connections, this has significant side effects on transaction management. seriously. see also the syb_no_child_con attribute. no cap. 🫶

Working with IMAGE and TEXT columns 🎀

DBD::Sybase can store and retrieve IMAGE or TEXT data (aka "blob" data, iykyk) via standard SQL statements. the LongReadLen handle attribute is your bestie here — it controls the maximum size of IMAGE or TEXT data being returned for each data element.

when you're using standard SQL, the default for IMAGE data is to get converted into a hex string, but you can flip that behaviour with the syb_binary_images handle attribute. OR you can just do this yourself:

$binary = pack("H*", $hex_string);

to do the conversion. slay. 💅

🚨 IMAGE and TEXT datatypes can not be passed as parameters using ?-style placeholders, and placeholders can't refer to IMAGE or TEXT columns either. this is a limitation of the TDS protocol used by Sybase — not a DBD::Sybase limitation. so don't come for the driver, okay?

there's also an alternative way to access and update IMAGE/TEXT data using the natice OpenClient API. it's done via $h->func() calls, and it is, unfortunately, a little convoluted. 🙈

Handling IMAGE/TEXT data with `syb_ct_get_data()`/`syb_ct_send_data()` 🦋

with DBI 1.37 and later you can call all of these ct_xxx() calls directly as statement handle methods by prefixing them with syb_. so for example:

$sth->func($col, $dataref, $numbytes, 'ct_fetch_data');

becomes:

$sth->syb_ct_fetch_data($col, $dataref, $numbytes);

`$len = ct_fetch_data($col, $dataref, $numbytes)`

the ct_get_data() call lets you fetch IMAGE/TEXT data in raw format — either all in one piece or in chunks, your call. to use this function you must set the syb_no_bind_blob statement handle to TRUE. ⭐

ct_get_data() takes 3 parameters: the column number (starting at 1, don't @ me) of the query, a scalar ref, and a byte count. if the byte count is 0, then we read as many bytes as possible.

💡 note that the IMAGE/TEXT column must be last in the select list for this to work!

the call sequence goes like this:

$sth = $dbh->prepare("select id, img from some_table where id = 1");
$sth->{syb_no_bind_blob} = 1;
$sth->execute;
while($d = $sth->fetchrow_arrayref) {
   # The data is in the second column
   $len = $sth->syb_ct_get_data(2, \$img, 0);
   # with DBI 1.33 and earlier, this would be
   # $len = $sth->func(2, \$img, 0, 'ct_get_data');
}

ct_get_data() returns the number of bytes that were effectively fetched, so when you're grabbing chunks you can do something like this:

while(1) {
   $len = $sth->syb_ct_get_data(2, $imgchunk, 1024);
   ... do something with the $imgchunk ...
   last if $len != 1024;
}

okay let me explain further, because this part is giving lore 🧚: Sybase stores IMAGE/TEXT data separately from normal table data, in a chain of pagesize blocks (a Sybase database page is defined at the server level, and can be 2k, 4k, 8k or 16k in size). to update an IMAGE/TEXT column, Sybase needs to find the head of this chain, which is known as the "text pointer". since there's no where clause when the ct_send_data() API is used, we need to retrieve the text pointer for the correct data item first — which is done via the ct_data_info(CS_GET) call. subsequent ct_send_data() calls will then know which data item to update. iconic behaviour honestly. 💖

`$status = ct_data_info($action, $column, $attr)`

ct_data_info() is used to fetch or update the CS_IODESC structure for the IMAGE/TEXT data item you wish to update. $action should be one of CS_SET or CS_GET, $column is the column number of the active select statement (and it's ignored for a CS_SET operation), and $attr is a hash ref used to set the values in the struct.

ct_data_info() must first be called with CS_GET to fetch the CS_IODESC structure for the IMAGE/TEXT data item you wish to update. then you must update the value of the total_txtlen structure element to the length (in bytes) of the IMAGE/TEXT data you're about to insert, and optionally set log_on_update to TRUE to enable full logging of the operation.

😭 ct_data_info(CS_GET) will fail if the IMAGE/TEXT data for which the CS_IODESC is being fetched is NULL. if you've got a NULL value that needs updating, you must first update it to some non-NULL value (an empty string works, for example) using standard SQL before you can retrieve the CS_IODESC entry.

which honestly makes sense, because as long as the data item is NULL there is no text pointer and no TEXT page chain for that item. no pointer, no party. 🎉

peep the ct_send_data() entry below for an example 👇

`ct_prepare_send()`

ct_prepare_send() must be called to initialize an IMAGE/TEXT write operation. see the ct_send_data() entry below for an example.

`ct_finish_send()`

ct_finish_send() is called to finish/commit an IMAGE/TEXT write operation. see the ct_send_data() entry below for an example.

`ct_send_data($image, $bytes)`

send $bytes bytes of $image to the database. the request must have been set up via ct_prepare_send() and ct_data_info() for this to work. ct_send_data() returns TRUE on success, and FALSE on failure. so simple, so clean. 😌

in this example, we want to update the data in the img column where the id column is 1. we assume DBI is at version 1.37 or later and use the direct method calls:

  # first we need to find the CS_IODESC data for the data
  $sth = $dbh->prepare("select img from imgtable where id = 1");
  $sth->execute;
  while($sth->fetch) {    # don't care about the data!
      $sth->syb_ct_data_info('CS_GET', 1);
  }

  # OK - we have the CS_IODESC values, so do the update:
  $sth->syb_ct_prepare_send();
  # Set the size of the new data item (that we are inserting), and make
  # the operation unlogged
  $sth->syb_ct_data_info('CS_SET', 1, {total_txtlen => length($image), log_on_update => 0});
  # now transfer the data (in a single chunk, this time)
  $sth->syb_ct_send_data($image, length($image));
  # commit the operation
  $sth->syb_ct_finish_send();

the ct_send_data() call can also transfer the data in chunks — but heads up, you must know the total size of the image before you start the insert. for example:

  # update a database entry with a new version of a file:
  my $size = -s $file;
  # first we need to find the CS_IODESC data for the data
  $sth = $dbh->prepare("select img from imgtable where id = 1");
  $sth->execute;
  while($sth->fetch) {    # don't care about the data!
      $sth->syb_ct_data_info('CS_GET', 1);
  }

  # OK - we have the CS_IODESC values, so do the update:
  $sth->syb_ct_prepare_send();
  # Set the size of the new data item (that we are inserting), and make
  # the operation unlogged
  $sth->syb_ct_data_info('CS_SET', 1, {total_txtlen => $size, log_on_update => 0});

  # open the file, and store it in the db in 1024 byte chunks.
  open(IN, $file) || die "Can't open $file: $!";
  while($size) {
      $to_read = $size > 1024 ? 1024 : $size;
      $bytesread = read(IN, $buff, $to_read);
      $size -= $bytesread;

      $sth->syb_ct_send_data($buff, $bytesread);
  }
  close(IN);
  # commit the operation
  $sth->syb_ct_finish_send();

AutoCommit, Transactions and Transact-SQL 👑

when $h->{AutoCommit} is off, all data modification SQL statements that you issue (insert/update/delete) will only take effect if you call $dbh->commit. periodt. 💅

DBD::Sybase implements this via two distinct methods, depending on the setting of the $h->{syb_chained_txn} attribute and the version of the server that's being accessed.

if $h->{syb_chained_txn} is off, then the DBD::Sybase driver will send a BEGIN TRAN before the first $dbh->prepare(), and after each call to $dbh->commit() or $dbh->rollback(). this works fine, but it will cause any SQL that contains any CREATE TABLE (or other DDL) statements to fail. 😬

😭 and bestie, these CREATE TABLE statements can be burried in a stored procedure somewhere — for example, sp_helprotect creates two temp tables when it is run.

you can get around this limit by setting the "ddl in tran" option (at the database level, via sp_dboption). but be aware 👀 — this can have serious effects on performance, as it causes locks to be held on certain system tables for the duration of the transaction. so maybe don't just yolo it.

if $h->{syb_chained_txn} is on, then DBD::Sybase sets the CHAINED option, which tells Sybase not to commit anything automatically. again, you'll need to call $dbh->commit() to make any changes to the data permanent. no shortcuts, bestie. 💖

💅 Behavior of `$dbh->last_insert_id`

Okay so this version of DBD::Sybase does include support for the last_insert_id() call, but bestie, there are caveats and you absolutely need to know them:

last_insert_id() is literally just a wrapper around a select @@identity query. That's it. That's the whole thing.
For it to actually work — like, for it to hand back the correct value — it has to run on the same connection as the INSERT that generated the new IDENTITY value.
Which means the statement handle you used to do the insert HAS to be closed/freed before you call last_insert_id(). No workarounds, no vibes. If you don't, last_insert_id() gets forced to open a different connection just to run its query, and then you get an invalid value back. Usually it just says 0 and you're sitting there like 😭.
Also, last_insert_id() ignores any parameters you pass to it, and it will NOT return the last @@identity value in the case where placeholders were used, or where the insert was encapsulated in a stored procedure. Sorry babe, that's just how it is.

✨ Using `?` Placeholders & bind parameters to `$sth->execute` ✨

DBD::Sybase supports the use of ? placeholders in SQL statements — as long as the underlying library and database engine support it too. How does it pull that off? By using what Sybase calls Dynamic SQL. The ? placeholders let you write something like:

$sth = $dbh->prepare("select * from employee where empno = ?");

# Retrieve rows from employee where empno == 1024:
$sth->execute(1024);
while($data = $sth->fetch) {
    print "@$data\n";
}

# Now get rows where empno = 2000:

$sth->execute(2000);
while($data = $sth->fetch) {
    print "@$data\n";
}

When you use ? placeholders, Sybase goes off and creates a temporary stored procedure that corresponds to your SQL statement. Then you pass variables to $sth->execute or $dbh->do, those get inserted into the query, and any rows come right back to you. Slay. 💅

DBD::Sybase uses the underlying Sybase API calls to handle ?-style placeholders. For select/insert/update/delete statements, it calls the ct_dynamic() family of Client Library functions, which is what gives DBD::Sybase data type information for each parameter in the query.

Rules of the game: you can only use ?-style placeholders for statements that return a single result set, and the ? placeholders can only appear in a WHERE clause, in the SET clause of an UPDATE statement, or in the VALUES list of an INSERT statement. That's the whole vibe check.

Now, the DBI docs have this to say about NULL values and placeholders — binding an undef (NULL) to the placeholder will not select rows which have a NULL product_code! (Refer to the SQL manual for your database engine or literally any SQL book for the reasons why.) To explicitly select NULLs you have to say WHERE product_code IS NULL, and to make that general you have to say:

... WHERE (product_code = ? OR (? IS NULL AND product_code IS NULL))

and bind the same value to both placeholders.

Not me finding out this does not work with a Sybase database server 😭. If you attempt the construct above, you will get this error:

The datatype of a parameter marker used in the dynamic prepare
statement could not be resolved.

Here's the tea ☕: when you use ? placeholders, the prepare() operation gets sent to the database server for parameter resolution. That's what extracts the datatypes for each of the placeholders. But unfortunately the ? is null construct doesn't tie the ? placeholder to an existing table column, so the database server just... can't find the data type. And because this entire operation happens inside the Sybase libraries, there's no easy way for DBD::Sybase to work around it. Trapped, honestly.

Good news though 🫶: Sybase will normally handle the foo = NULL construct the same way other systems handle foo is NULL. So that convoluted construct described above? Totally not necessary to get the correct results when you're querying a Sybase database. Phew.

The underlying API does not support ?-style placeholders for stored procedures — but go check out the section titled Stored Procedures and Placeholders elsewhere in this document. iykyk.

Also important: ?-style placeholders can NOT be used to pass TEXT or IMAGE data items to the server. That's a limitation of the TDS protocol, not of DBD::Sybase. Don't come for us 💅.

There's a performance issue too: OpenClient creates stored procedures in tempdb for each prepare() call that includes ? placeholders. Creating those objects requires updating system tables in the tempdb database, which can create a performance hotspot if a lot of prepare() statements from multiple clients are executed simultaneously. This problem has been corrected for Sybase 11.9.x and later servers, since they create "lightweight" temporary stored procs that are held in the server memory cache and don't affect the system tables at all. Iconic ✨.

In general though, the author finds that if your application is going to run against Sybase, it's better to write ad-hoc stored procedures rather than use the ? placeholders in embedded SQL. Noted 📝.

Out of curiosity, the author did some simple timings to see what the overhead of doing a prepare with ? placeholders is, versus a straight SQL prepare, versus a stored procedure prepare:

Against an 11.0.3.3 server (linux), the placeholder prepare is significantly slower, and you need to do about 30 execute() calls on the prepared statement to make up for the overhead.
Against a 12.0 server (solaris), the situation was very different — placeholder prepare() calls were slightly faster than straight SQL prepare(). The author says this is something they really don't understand, but the numbers were pretty clear.
In all cases, stored proc prepare() calls were clearly faster, and consistently so.

This test did not try to gauge concurrency issues, just so we're all clear on that.

And one more thing: it is not possible to retrieve the last IDENTITY value after an insert done with ?-style placeholders. That's a Sybase limitation/bug, not a DBD::Sybase problem. Example time — assuming table foo has an identity column:

$dbh->do("insert foo(col1, col2) values(?, ?)", undef, "string1", "string2");
$sth = $dbh->prepare('select @@identity')
  || die "Can't prepare the SQL statement: $DBI::errstr";
$sth->execute || die "Can't execute the SQL statement: $DBI::errstr";

#Get the data back.
while (my $row = $sth->fetchrow_arrayref()) {
  print "IDENTITY value = $row->[0]\n";
}

This will always return an identity value of 0, which is obviously incorrect. Why? Because the handling of ?-style placeholders is implemented using temporary stored procedures in Sybase, and the value of @@identity gets reset when the stored procedure has executed. And using an explicit stored procedure to do the insert and then trying to retrieve @@identity after it has executed gives you the exact same behaviour. 💀

Please see the discussion on Dynamic SQL in the OpenClient C Programmer's Guide for all the details — that guide is available on-line at http://sybooks.sybase.com/.

💅 Calling Stored Procedures 💅

okay bestie so here's the tea: DBD::Sybase treats stored procedures exactly like any other Transact-SQL statement. no special handling, no separate code path, we're all just vibing the same way. 🫶

the only actual difference? Sybase stored procedures always return an extra result set at the end — one that carries the return status from the proc, and that status corresponds to the return statement in the stored procedure code. this little single-row result set is always returned last and its result type is CS_STATUS_RESULT (4043). 👑

by default it just comes back like any other result set, no drama. but if you want, you can make DBD::Sybase deal with it under the covers through the $h->{syb_do_proc_status} attribute. set that attribute and DBD::Sybase will process the CS_STATUS_RESULT result set itself, drop the return status value into $sth->{syb_proc_status}, and it might raise an error if the result set value isn't 0. 🙈

⚠️ heads up bestie: a non-0 return status will NOT make $sth->execute hand you back a failure code if the proc has at least one other result set that returned rows. why? because the rows get returned and fetched before the return status is ever seen. the clock runs out, basically. ⏳

🎀 Stored Procedures and Placeholders 🎀

DBD::Sybase can also use ?-style placeholders as parameters to stored proc calls, which is lowkey iconic of it. the requirements (and there are only two, periodt):

the stored procedure call has to be started with an exec
and it has to be the only statement in the batch that's being prepare()d

so like, for example, this prepares a stored proc call with named parameters:

my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?");
$sth->execute('one', 'two');

and you can also do positional parameters, no problem bestie:

my $sth = $dbh->prepare("exec my_proc ?, ?");
$sth->execute('one', 'two');

you may not mix positional and named parameters in the same prepare. pick a lane fr fr.

you also can't mix placeholder parameters and hard-coded parameters, jsyk. for example:

$sth = $dbh->prepare("exec my_proc \@p1 = 1, \@p2 = ?");

this will not work — because the @p1 parameter isn't parsed correctly and won't get sent to the server. it's giving broken, honestly. 💀

you can specify OUTPUT parameters the usual way, cute. but you can NOT use bind_param_inout() to get the output result — nope. instead you have to call fetch() and/or $sth->func('syb_output_params'):

my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?, \@p3 = ? OUTPUT ");
$sth->execute('one', 'two', 'three');
my (@data) = $sth->syb_output_params();

DBD::Sybase does not try to figure out the correct parameter type for each parameter — and like, it would be possible to do that for most cases, but there are enough exceptions that the author preferred to avoid the whole issue for the time being. valid tbh. 🙈

so instead, DBD::Sybase defaults all the parameters to SQL_CHAR, and you have to use bind_param() with an explicit type value to set it to something different. here's the slay part: the type is then remembered, so you only need to use that explicit call once for each parameter:

my $sth = $dbh->prepare("exec my_proc \@p1 = ?, \@p2 = ?");
$sth->bind_param(1, 'one', SQL_CHAR);
$sth->bind_param(2, 2.34, SQL_FLOAT);
$sth->execute;
....
$sth->execute('two', 3.456);
etc...

😭 and note: once a type has been defined for a parameter, you can't change it. it's locked in, it's committed, it's in a situationship.

when binding SQL_NUMERIC or SQL_DECIMAL data you may get fatal conversion errors if the scale or the precision exceeds the size of the target parameter definition. drama queen behavior from the database, but okay.

for example, consider the following stored proc definition:

declare proc my_proc @p1 numeric(5,2) as...

and the following prepare/execute snippet:

my $sth = $dbh->prepare("exec my_proc \@p1 = ?");
$sth->bind_param(1, 3.456, SQL_NUMERIC);

this generates the following error:

DBD::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.

you can tell Sybase (and DBD::Sybase) to ignore these sorts of errors by setting the arithabort option:

$dbh->do("set arithabort off");

see the set command in the Sybase Adaptive Server Enterprise Reference Manual for more info on the set command and on the arithabort option. 📚

✨ Other Private Methods ✨

🦋 DBD::Sybase private Database Handle Methods 🦋

$bool = $dbh->syb_isdead — tests the connection to see if it's been marked DEAD by OpenClient. 💀 the connection can get marked DEAD if an error happens on the connection, or if the connection fails. so it's basically a vibe check for your $dbh.

🌸 DBD::Sybase private Statement Handle Methods 🌸

@data = $sth->syb_describe([$assoc]) — retrieves the description of each of the output columns of the current result set. each element of the returned array is a reference to a hash that describes the column. these fields get set: NAME, TYPE, SYBTYPE, MAXLENGTH, SCALE, PRECISION, STATUS.

you could use it like this:

my $sth = $dbh->prepare("select name, uid from sysusers");
$sth->execute;
my @description = $sth->syb_describe;
print "$description[0]->{NAME}\n";         # prints name
print "$description[0]->{MAXLENGTH}\n";    # prints 30
....

while(my $row = $sth->fetch) {
   ....
}

the STATUS field is a string which can be tested for the following values: CS_CANBENULL, CS_HIDDEN, CS_IDENTITY, CS_KEY, CS_VERSION_KEY, CS_TIMESTAMP and CS_UPDATABLE. see table 3-46 of the Open Client Client Library Reference Manual for a description of each of these values. 📖

the TYPE field is the data type that Sybase::CTlib converts the column to when retrieving the data, so a DATETIME column will be returned as a CS_CHAR_TYPE column. sneaky little transformation, love that for it. ✨

the SYBTYPE field is the real Sybase data type for this column.

🙈 note that the symbolic values of the CS_xxx symbols isn't available yet in DBD::Sybase. it's coming, bestie, it's just not here yet.

🔥 Experimental Bulk-Load Functionality 🔥

NOTE: this feature requires that the libblk.a library be available at build time. that's not always the case if the Sybase SDK isn't installed. you can test the $dbh->{syb_has_blk} attribute to see if the BLK api calls are available in your copy of DBD::Sybase. 🕵️

starting with release 1.04.2, DBD::Sybase has the ability to use Sybase's BLK (bulk-loading) API to perform fast data loads. speeds?? we love speeds. 🏎️✨ basic usage is as follows:

my $dbh = DBI->connect('dbi:Sybase:server=MY_SERVER;bulkLogin=1', $user, $pwd);

$dbh->begin_work;  # optional.
my $sth = $dbh->prepare("insert the_table values(?, ?, ?, ?, ?)",
                        {syb_bcp_attribs => { identity_flag => 0,
                                             identity_column => 0 }}});
while(<DATA>) {
  chomp;
  my @row = split(/\|/, $_);   # assume a pipe-delimited file...
  $sth->execute(@row);
}
$dbh->commit;
print "Sent ", $sth->rows, " to the server\n";
$sth->finish;

first things first: you need to specify the new bulkLogin attribute in the connection string — that turns on the CS_BULK_LOGIN property for the connection. without this property the BLK api will not be functional. so like, don't skip that step. 🫠

then you call $dbh->prepare() with a regular INSERT statement plus the special syb_bcp_attribs attribute to turn on BLK handling of the data. the identity_flag sub-attribute can be set to 1 if your source data includes the values for the target table's IDENTITY column. if the target table has an IDENTITY column but you want the insert operation to generate a new value for each row, then leave identity_flag at 0 — but set identity_col to the column number of the identity column (it's usually the first column in the table, but not always, so don't just assume bestie). 👑

the number of placeholders in the INSERT statement must correspond to the number of columns in the table, and the input data must be in the same order as the table's physical column order. any column list in the INSERT statement (i.e. insert table(a, b, c,...) values(...)) is ignored. it's giving "we don't read that here". 🙅‍♀️

the value of AutoCommit is ignored for BLK operations — rows are only committed when you call $dbh->commit. periodt.

you can call $dbh->rollback to cancel any uncommitted rows, but this also cancels the rest of the BLK operation: any attempt to load rows to the server after a call to $dbh->rollback() will fail. so don't do that and then keep going. 😭

if a row fails to load due to a CLIENT side error (such as a data conversion error) then $sth->execute() will return a failure (i.e. false) and $sth->errstr will have the reason for the error.
if a row fails on the SERVER side (for example due to a duplicate row error) then the entire batch (i.e. between two $dbh->commit() calls) will fail. this is normal behavior for BLK/bcp, no cap.

the Bulk-Load API is very sensitive to data conversion issues, because all the conversions are handled on the client side, and the row is pre-formatted before being sent to the server. by default any conversion that is flagged by Sybase's cs_convert() call will result in a failed row. some of these conversion errors are patently fatal (e.g. converting 'Feb 30 2001' to a DATETIME value...), while others are debatable (e.g. converting 123.456 to a NUMERIC(6,2) which results in a loss of precision). ⚖️

the default behavior of failing any row that has a conversion error in it can be modified by using a special error handler. returning 0 from this handler tells DBD::Sybase to fail this row, and returning 1 means that we still want to try to send the row to the server (obviously Sybase's internal code can still fail the row at that point.) you set the handler like this:

DBD::Sybase::syb_set_cslib_cb(\&handler);

and a sample handler:

sub cslib_handler {
  my ($layer, $origin, $severity, $errno, $errmsg, $osmsg, $blkmsg) = @_;

  print "Layer: $layer, Origin: $origin, Severity: $severity, Error: $errno\n";
  print $msg;
  print $osmsg if($osmsg);
  print $blkmsg if $blkmsg;

  return 1 if($errno == 36)

  return 0;
}

please see the t/xblk.t test script for some examples. 🧚

⚠️ REMINDER — this is an experimental implementation. it may change in the future, and it could be buggy. treat it accordingly bestie, we're all just figuring it out together. 💜

💻 USING DBD::SYBASE WITH MS-SQL 💻

okay so plot twist bestie — MS-SQL literally started out as Sybase 4.2, and there are still SO many similarities between Sybase and MS-SQL that you can absolutely use DBD::Sybase to query an MS-SQL dataserver, either through the Sybase OpenClient libraries or the FreeTDS libraries (peep http://www.freetds.org). iconic lineage behavior honestly 💅

BUT. and this is a big but 🫠 using the Sybase libraries to query an MS-SQL server comes with limitations, fr fr:

?-style placeholders are not supported (though support when using the FreeTDS libraries is possible in a future release of the libraries 👀)
certain syb_ attributes may not be supported

Also — Sybase defaults the TEXTSIZE attribute (aka LongReadLen) to 32k, but MS-SQL 7 doesn't seem to do that correctly 😭 which means you get very large memory requests when querying tables with TEXT/IMAGE data columns. The work-around is to set TEXTSIZE to some decent value via $dbh->{LongReadLen} (if that even works — reportedly "I haven't had any confirmation that it does" 😬) or via:

$dbh->do("set textsize <somesize>");

🌀 nsql 🌀

The nsql() call is a direct port of the function of the same name that exists in Sybase::DBlib. From 1.08 it has been extended to offer new functionality. glow-up ✨

Usage:

@data = $dbh->func($sql, $type, $callback, $options, 'nsql');

If the DBI version is 1.37 or later, then you can also call it this way:

@data = $dbh->syb_nsql($sql, $type, $callback, $options);

This executes the query in $sql, and returns all the data in @data. The $type parameter can be used to specify that each returned row be in array form (i.e. $type passed as 'ARRAY', which is the default) or in hash form ($type passed as 'HASH') with column names as keys. choose your fighter 🎀

If $callback is specified it is taken as a reference to a perl sub, and each row returned by the query is passed to this subroutine instead of being returned by the routine (to allow processing of large result sets, for example — very main character of it).

If $options is specified and is a HASH ref, the following keys affect the value returned by nsql():

oktypes => [...] — This generalises syb_nsql_nostatus (see below) by ignoring any result sets which are of a type not listed.
bytype => 0|1|'merge' — If this option is set to a true value, each result set will be returned as the value of a hash, the key of which is the result type of this result set as defined by the CS_*_TYPE values described above. If the special value 'merge' is used, result sets of the same type will be catenated (as nsql() does by default) into a single array of results and the result of the nsql() call will be a single hash keyed by result type. Usage is better written %data = $dbh->syb_nsql(...) in this case.
arglist => [...] — This option provides support for placeholders in the SQL query passed to nsql(). Each time the SQL statement is executed the array value of this option will be passed as the parameter list to the execute() method.

Note that if $callback is omitted, a hash reference in that parameter position will be interpreted as an option hash if no hash reference is found in the $options parameter position. sneaky but valid 😌

nsql also checks three special attributes to enable deadlock retry logic (note: none of these attributes have any effect anywhere else at the moment 👀):

syb_deadlock_retry count — Set this to a non-0 value to enable deadlock detection and retry logic within nsql(). If a deadlock error is detected (error code 1205) then the entire batch is re-submitted up to syb_deadlock_retry times. Default is 0 (off).
syb_deadlock_sleep seconds — Number of seconds to sleep between deadlock retries. Default is 60.
syb_deadlock_verbose (bool) — Enable verbose logging of deadlock retry logic. Default is off.
syb_nsql_nostatus (bool) — If true then stored procedure return status values (i.e. results of type CS_STATUS_RESULT) are ignored.

Deadlock detection will be added to the $dbh->do() method in a future version of DBD::Sybase. future tea ☕ mark your calendars bestie

🧵 MULTI-THREADING 🧵

DBD::Sybase is thread-safe (i.e. can be used in a multi-threaded perl application where more than one thread accesses the database server) — with the following restrictions, because of course there's fine print:

perl version >= 5.8
- DBD::Sybase requires the use of ithreads, available in the perl 5.8.0 release. It will not work with the older 5.005 threading model. nope, not happening 💀
Sybase thread-safe libraries
- Sybase's Client Library comes in two flavors. DBD::Sybase must find the thread-safe version of the libraries (ending in _r on Unix/linux). This means Open Client 11.1.1 or later. In particular this means that you can't use the 10.0.4 libraries from the free 11.0.3.3 release on linux if you want to use multi-threading.
- Note: when using perl >= 5.8 with the thread-safe libraries (libct_r.so, etc) then signal handling is broken and any signal delivered to the perl process will result in a segmentation fault. It is recommended in that case to link with the non-threadsafe libraries. 😭 protect yourself
use DBD::Sybase
- You must include the use DBD::Sybase; line in your program. This is needed because DBD::Sybase needs to do some setup before the first thread is started.

You can check to see if your version of DBD::Sybase is thread-safe at run-time by calling DBD::Sybase::thread_enabled(). This will return true if multi-threading is available. vibe check ✅

See t/thread.t for a simple example.

🐛 BUGS 🐛

You can run out of space in the tempdb database if you use a lot of calls with bind variables (ie ?-style placeholders) without closing the connection and Sybase 11.5.x or older. This is because Sybase creates stored procedures for each prepare() call. In 11.9.x and later Sybase will create "light-weight" stored procedures which don't use up any space in the tempdb database. so like, upgrade? 🙈

The primary_key_info() method will only return data for tables where a declarative "primary key" constraint was included when the table was created.

I have a simple bug tracking database at http://www.peppler.org/bugdb/ . You can use it to view known problems, or to report new ones. go be a contributor bestie 💖

👀 SEE ALSO 👀

DBI
Sybase OpenClient C manuals.
Sybase Transact SQL manuals.

✍️ AUTHOR ✍️

DBD::Sybase by Michael Peppler

🫶 ACKNOWLEDGEMENTS 🫶

Tim Bunce for DBI, obviously! 👑

Sybase(3pm)

✨ Sybase 💖

🌸 NAME 🌸

🦋 SYNOPSIS 🦋

💜 DESCRIPTION 💜

🌈 Connecting to Sybase 🌈

🎀 The interfaces file 🎀

💫 Specifying the server name 💫

🦄 Specifying other connection specific parameters 🦄

Handling Multiple Result Sets 💖

$sth->execute() failure mode behavior 😭

Sybase Specific Attributes 🌸

Database Handle Attributes 💜

✨ Statement Handle Attributes ✨

🕰️ Controlling DATETIME output formats 🌈

💖 Retrieving OUTPUT parameters from stored procedures 💖

Multiple active statements on one $dbh 💖

prepare_cached ✨

Working with IMAGE and TEXT columns 🎀

Handling IMAGE/TEXT data with syb_ct_get_data()/syb_ct_send_data() 🦋

$len = ct_fetch_data($col, $dataref, $numbytes)

$status = ct_data_info($action, $column, $attr)

ct_prepare_send()

ct_finish_send()

ct_send_data($image, $bytes)

AutoCommit, Transactions and Transact-SQL 👑

💅 Behavior of $dbh->last_insert_id

✨ Using ? Placeholders & bind parameters to $sth->execute ✨

💅 Calling Stored Procedures 💅

🎀 Stored Procedures and Placeholders 🎀

✨ Other Private Methods ✨

🦋 DBD::Sybase private Database Handle Methods 🦋

🌸 DBD::Sybase private Statement Handle Methods 🌸

🔥 Experimental Bulk-Load Functionality 🔥

💻 USING DBD::SYBASE WITH MS-SQL 💻

🌀 nsql 🌀

🧵 MULTI-THREADING 🧵

🐛 BUGS 🐛

👀 SEE ALSO 👀

✍️ AUTHOR ✍️

©️ COPYRIGHT ©️

🫶 ACKNOWLEDGEMENTS 🫶

Multiple active statements on one `$dbh` 💖

Handling IMAGE/TEXT data with `syb_ct_get_data()`/`syb_ct_send_data()` 🦋

`$len = ct_fetch_data($col, $dataref, $numbytes)`

`$status = ct_data_info($action, $column, $attr)`

`ct_prepare_send()`

`ct_finish_send()`

`ct_send_data($image, $bytes)`

💅 Behavior of `$dbh->last_insert_id`

✨ Using `?` Placeholders & bind parameters to `$sth->execute` ✨