nsql())ct_get_data() and ct_send_data() to do raw TEXT processing
sybperl - Sybase extensions to Perl
use Sybase::DBlib; use Sybase::CTlib; use Sybase::Sybperl;
Sybperl implements three Sybase extension modules to perl (version 5.002 or higher). Sybase::DBlib adds a subset of the Sybase DB-Library API. Sybase::CTlib adds a subset of the Sybase CT-Library (aka the Client Library) API. Sybase::Sybperl is a backwards compatibility module (implemented on top of Sybase::DBlib) to enable scripts written for sybperl 1.0xx to run with Perl 5. Using both the Sybase::Sybperl and Sybase::DBlib modules explicitly in a single script is not garanteed to work correctly.
The general usage format for both Sybase::DBlib and Sybase::CTlib is this:
use Sybase::DBlib;
# Allocate a new connection, usually refered to as a database handle
$dbh = new Sybase::DBlib username, password;
# Set an attribute for this dbh:
$dbh->{UseDateTime} = TRUE;
# Call a method with this dbh:
$dbh->dbcmd(sql code);
The DBPROCESS or CS_CONNECTION that is opened with the call to
new() is automatically closed when the $dbh goes out of scope:
sub run_a_query {
my $dbh = new Sybase::CTlib $user, $passwd;
my @dat = $dbh->ct_sql("select * from sysusers");
return @dat;
}
# The $dbh is automatically closed when we exit the subroutine.
It should be noted that an important difference between CTlib and DBlib is in how the SYBASE environment variable is handled. DBlib only checks for the SYBASE variable when it requires access to the interfaces file. This allows for definition of the SYBASE variable in your script. CTlib requires that the SYBASE variable be defined BEFORE initialization. If the variable is not defined then CTlib will not initialize properly and your script will not run.
The Sybase::DBlib and Sybase::CTlib modules make a use of attributes that are either package global or associated with a specific $dbh. These attributes control certain behavior aspects, and are also used to store status information.
Package global attributes can be set using the %Att hash table in either modules. The %Att variable is not exported, so it must be fully qualified:
$Sybase::DBlib::Att{UseDateTime} = TRUE;
NOTE: setting an attribute via the %Att variable does NOT change the status of currently allocated database handles.
In this version, the available attributes for a $dbh are set when the $dbh is created. You can't add arbitrary attributes during the life of the $dbh. This has been done to implement a stricter behavior and to catch attribute errors.
It is possible to add your own attributes to a $dbh at creation
time. The Sybase::BCP module adds two attributes to the normal
Sybase::DBlib attribute set by passing an additional attribute
variable to the Sybase::DBlib new() call:
$d = new Sybase::DBlib $user,$passwd,
$server,$appname, {Global => {}, Cols => {}};
As of version 2.04, the Sybase DATETIME and MONEY datatypes can be kept in their native formats in both the Sybase::DBlib and Sybase::CTlib modules. In addition, NUMERIC or DECIMAL values can also be kept in their native formats when using the Sybase::CTlib module. This behavior is normally turned off by default, because there is a performance penalty associated with it. It is turned on by using package or database handle specific attributes.
Please see the discussion on Special handling of DATETIME, MONEY & NUMERIC/DECIMAL values below for details.
In general, I have tried to make the calls in this package behave the same way as their C language equivalents. In certain cases the parameters are different, and certain calls (dblogin() for example) don't do the same thing in C as in Perl. This has been done to make the life of the Perl programmer easier.
You should if possible have the Sybase Open Client documentation available when writing Sybperl programs.
A generic perl script using Sybase::DBlib would look like this:
use Sybase::DBlib;
$dbh = new Sybase::DBlib 'sa', $pwd, $server, 'test_app';
$dbh->dbcmd("select * from sysprocesses\n");
$dbh->dbsqlexec;
$dbh->dbresults;
while(@data = $dbh->dbnextrow)
{
.... do something with @data ....
}
The API calls that have been implemented use the same calling sequence as their C equivalents, with a couple of exceptions, detailed below.
Please see also Common Sybase::DBlib and Sybase::CTlib routines below.
List of API calls
Standard Routines:
Both forms of the call are identical.
This call can be used multiple times if connecting to multiple servers with different username/password combinations is required, for example.
The additional attributes parameter allows you to define application specific attributes that you wish to associate with the $dbh.
dbuse($database)dbcmd($sql_cmd)dbpoll() and dbsqlok(). See the
Sybase docs for further details.
dbmoretext()
in Sybase::DBlib. See also the Sybase documentation for details.
dbpoll($millisecs)dbpoll($millisecs)dbpoll() syntax has been changed since sybperl 2.09_05.
Poll the server to see if any connection has results pending. Used in
conjunction with dbsqlsend() and dbsqlok() to perform asynchronous queries.
dbpoll() will wait up to $millisecs milliseconds and poll any open DBPROCESS
for results (if called as Sybase::DBlib->dbpoll()) or poll the specified
DBPROCESS (if called as $dbh->dbpoll()). If it finds a DBPROCESS that is
ready it returns it, along with the reason why it's ready. If dbpoll()
times out, or if an interupt occurs $dbproc will be undefined, and $reason
will be either DBTIMEOUT or DBINTERUPT. If $millisecs is 0 then dbpoll()
returns immediately. If $millisecs is -1 then it will not return until
either results are pending or a system interupt has occured. Please see
the Sybase documentation for further details.
Here is an example of using dbsqlsend(), dbpoll() and dbsqlok():
$dbh->dbcmd("exec big_hairy_query_proc");
$dbh->dbsqlsend;
# here you can go do something else...
# now - find out if some results are waiting
($dbh2, $reason) = $dbh->dbpoll(100);
if($dbh2 && $reason == DBRESULT) { # yes! - there's data on the pipe
$dbh2->dbsqlok;
while($dbh2->dbresults != NO_MORE_RESULTS) {
while(@dat = $dbh2->dbnextrow) {
....
}
}
}
dbcoltype($colid)dbprtype($colid)dbcollen($colid)dbcolname($colid)dbnextrow() returns an array of scalars, one for each
column value. If $doAssoc is non-0, then dbnextrow() returns a hash (aka
associative array) with column name/value pairs. This relieves the
programmer from having to call dbbind() or dbdata().
If $wantRef is non-0, then dbnextrow() returns a reference to
a hash or an array. This reference points to a static array (or hash)
so if you wish to store the returned rows in an array, you must
copy the array/hash:
while($d = $dbh->dbnextrow(0, 1)) {
push(@rows, [@$d]);
}
The return value of the C version of dbnextrow() can be accessed via the
Perl DBPROCESS attribute field, as in:
@arr = $dbh->dbnextrow; # read results
if($dbh->{DBstatus} != REG_ROW) {
take some appropriate action...
}
When the results row is a COMPUTE row, the ComputeID field of the DBPROCESS is set:
@arr = $dbh->dbnextrow; # read results
if($dbh->{ComputeID} != 0) { # it's a 'compute by' row
take some appropriate action...
}
dbnextrow() can also return a hash keyed on the column name:
$dbh->dbcmd("select Name=name, Id = id from test_table");
$dbh->dbsqlexec; $dbh->dbresults;
while(%arr = $dbh->dbnextrow(1)) {
print "$arr{Name} : $arr{Id}\n";
}
dbbylist($computeID)dbcolname() to get the column
names.
$dbh->dbcmd("select * from sysusers order by uid compute count(uid) by uid");
$dbh->dbsqlexec;
$dbh->dbresults;
my @dat;
while(@dat = $dbh->dbnextrow) {
if($dbh->{ComputeID} != 0) {
my $bylist = $dbh->dbbylist($dbh->{ComputeID});
print "bylist = @$bylist\n";
}
print "@dat\n";
}
$dbh->dbsetopt(DBSHOWPLAN);
See also dbclropt() and dbisopt() below.
The second argument to dbsafestr() (normally DBSINGLE, DBDOUBLE or
DBBOTH) has been replaced with a literal ' or `` (meaning DBSINGLE or
DBDOUBLE, respectively). Omitting this argument means DBBOTH.
The calling sequence is a little different from the C version, and logging is off by default:
$dbh_2 and $colnum are the DBPROCESS and column number of a currently active query. Example:
$dbh_2->dbcmd('select the_text, t_index from text_table where t_index = 5');
$dbh_2->dbsqlexec; $dbh_2->dbresults;
@data = $dbh_2->dbnextrow;
$d->dbwritetext ("text_table.the_text", $dbh_2, 1,
"This is text which was added with Sybperl", TRUE);
The calling sequence is a little different from the C version, and logging is off by default:
$dbh_2 and $colnum are the DBPROCESS and column number of a currently active query. Example:
$dbh_2->dbcmd('select the_text, t_index from text_table where t_index = 5');
$dbh_2->dbsqlexec; $dbh_2->dbresults;
@data = $dbh_2->dbnextrow;
$size = length($data1) + length($data2);
$d->dbpreptext ("text_table.the_text", $dbh_2, 1, $size, TRUE);
$dbh->dbsqlok;
$dbh->dbresults;
$dbh->dbmoretext(length($data1), $data1);
$dbh->dbmoretext(length($data2), $data2);
$dbh->dbsqlok; $dbh->dbresults;
Example:
$dbh->dbcmd("select data from text_test where id=1");
$dbh->dbsqlexec;
while($dbh->dbresults != NO_MORE_RESULTS) {
my $bytes;
my $buf = '';
while(($bytes = $dbh->dbreadtext($buf, 512)) != NO_MORE_ROWS) {
if($bytes == -1) {
die "Error!";
} elsif ($bytes == 0) {
print "End of row\n";
} else {
print "$buf";
}
}
}
See also the Sybase::BCP module.
BCP_SETL($state)It is necessary to call BCP_SETL(TRUE) before opening the
connection with which one wants to run a BCP IN operation.
bcp_meminit($numcols)See EXAMPLES, below.
bcp_sendrow(LIST)bcp_sendrow(ARRAY_REF)In the second form you pass an array reference instead of passing the LIST, which makes processing a little bit faster on wide tables.
while(<IN>) {
chop;
@data = split(/\|/);
$d->bcp_sendrow(\@data); # Pass the array reference
# Commit data every 100 rows.
if((++$count % 100) == 0) {
$d->bcp_batch;
}
}
bcp_columns($colcount)bcp_colfmt_ps() instead of bcp_colfmt().
bcp_readfmt($filename)bcp_writefmt($filename)
NOTE: In this version it is possible to avoid calling the routines below and still get DBMONEY calculations done with the correct precision. See the Sybase::DBlib::Money discussion below.
dbmny4minus($source)dbmnydec($m1)dbmnyinc($m1)dbmnyminus($source)dbmnyndigit($m1)The routines which in the C version take pointers to arguments (in order to return values) return these values in an array instead:
status = dbmnyadd(dbproc, m1, m2, &result) becomes ($status, $result) = $dbproc->dbmnyadd($m1, $m2)
NOTE: Check out eg/rpc-example.pl for an example on how to use these calls.
NOTE: All floating point types (MONEY, FLOAT, REAL, DECIMAL, etc.) are converted to FLOAT before being sent to the RPC.
dbrpcsend([$no_ok])By default this routine calls the C library dbrpcsend() and dbsqlok(), so
that you can directly call $dbh->dbresults directly after a call to
$dbh->dbrpcsend. If you need more control you can pass a non-0 value for
the $no_ok parameter, and it will then be your responsibility to call
$dbh->dbsqlok(). Please read the Sybase OpenClient DB-Library manual
pages on dbrpcsend() and dbsqlok() for further details.
dbreginit($proc_name)dbregexec($opt)
stat_xact($id)scan_xact($id)commit_xact($id)abort_xact($id)NOTE: These routines have not been thouroughly tested!
dberrhandle($err_handle)dbmsghandle($msg_handle)dbsetifile($filename)dbrecftos($filename)DBSETLCHARSET($charset)DBSETLNATLANG($language)DBSETLPACKET($packet_size)dbsettime($seconds)dbsetlogintime($seconds)
nsql())These routines are not part of the DB-library API, but have been added because they can make our life as programers easier, and exploit certain strenghts of Perl.
If you provide a second parameter it is taken as a procedure to call for each row. The callback is called with the values of the row as parameters.
If you provide a third parameter, this is used in the call to
dbnextrow() to retrieve associative arrays rather than 'normal' arrays
for each row, and store them in the returned array. To pass the third
parameter without passing the &rowcallback value you should pass the
special value undef as second parameter:
@rows = $dbh->sql("select * from sysusers", undef, TRUE);
foreach $row_ref (@rows) {
if($$row_ref{'uid'} == 10) {
....
}
}
See also eg/sql.pl for an example.
Contributed by Gisle Aas.
NOTE: This routine loads all the data into memory. It should not be run with a query that returns a large number of rows. To avoid the risk of overflowing memory, you can limit the number of rows that the query returns by setting the 'MaxRows' field of the $dbh attribute field:
$dbh->{'MaxRows'} = 100;
This value is not set by default.
nsql() provides better error checking (using its companion error and
message handlers), optional deadlock retry logic, and several options
for the format of the return values. In addition, the data can either
be returned to the caller in bulk, or processes line by line via a
callback subroutine passed as an argument (this functionality is
similar to the r_sql() method).
The arguments are an SQL command to be executed, the $type of the data to be returned, and the callback subroutine.
if a callback subroutine is not given, then the data from the query is returned as an array. The array returned by nsql is one of the following:
Array of Hash References (if type eq HASH)
Array of Array References (if type eq ARRAY)
Simple Array (if type eq ARRAY, and a single column is queried
Boolean True/False value (if type ne ARRAY or HASH)
Optionally, instead of the words ``HASH'' or ``ARRAY'' a reference of the same type can be passed as well. This is, both of the following are equivalent:
$dbh->nsql("select col1,col2 from table","HASH");
$dbh->nsql("select col1,col2 from table",{});
For example, the following code will return an array of hash references:
@ret = $dbh->nsql("select col1,col2 from table","HASH");
foreach $ret ( @ret ) {
print "col1 = ", $ret->{'col1'}, ", col2 = ", $ret->{'col2'}, "\n";
}
The following code will return an array of array references:
@ret = $dbh->nsql("select col1,col2 from table","ARRAY");
foreach $ret ( @ret ) {
print "col1 = ", $ret->[0], ", col2 = ", $ret->[1], "\n";
}
The following code will return a simple array, since the select statement queries for only one column in the table:
@ret = $dbh->nsql("select col1 from table","ARRAY");
foreach $ret ( @ret ) {
print "col1 = $ret\n";
}
Success of failure of an nsql() call cannot necessarily be judged
based on the value of the return code, as an empty array may be a
perfectly valid result for certain sql code.
The nsql() routine will maintain the success or failure state in a variable $DB_ERROR, accessed by the method of the same name, and a pair of Sybase message/error handler routines are also provided which will use $DB_ERROR for the Sybase messages and errors as well. However, these must be installed by the client application:
dbmsghandle("Sybase::DBlib::nsql_message_handler");
dberrhandle("Sybase::DBlib::nsql_error_handler");
Success of failure of an nsql() call cannot necessarily be judged
based on the value of the return code, as an emtpy array may be a
perfectly valid result for certain sql code.
The following code is the proper method for handling errors with use of nsql.
@ret = $dbh->nsql("select stuff from table where stuff = 'nothing'","ARRAY");
if ( $DB_ERROR ) {
# error handling code goes here, perhaps:
die "Unable to get stuff from table: $DB_ERROR\n";
}
The behavior of nsql() can be customized in several ways. If the variable:
$Sybase::DBlib::nsql_strip_whitespace
is true, then nsql() will strip the trailing white spaces from all of
the scalar values in the results.
When using a callback subroutine, the subroutine is passed to nsql()
as a CODE reference. For example:
sub parse_hash {
my %data = @_;
# Do something with %data
}
$dbh->nsql("select * from really_huge_table","HASH",\&parse_hash);
if ( $DB_ERROR ) {
# error handling code goes here, perhaps:
die "Unable to get stuff from really_huge_table: $DB_ERROR\n";
}
In this case, the data is passed to the callback (&parse_hash) as a HASH, since that was the format specified as the second argument. If the second argument specifies an ARRAY, then the data is passed as an array. For example:
sub parse_array {
my @data = @_;
# Do something with @data
}
$dbh->nsql("select * from really_huge_table","HASH",\&parse_array);
if ( $DB_ERROR ) {
# error handling code goes here, perhaps:
die "Unable to get stuff from really_huge_table: $DB_ERROR\n";
}
The primary advantage of using the callback is that the rows are processed one at a time, rather than the data returned in a huge array. For very large tables, this can result in very significant memory consumption, and on resource constrained machines, some large queries may simply fail. Processing rows individually will be much more efficient with respect to memory consumption.
IMPORTANT NOTE: The callback subroutine must return a true value if it
has successfully handled the data. If a false value is returned, then
the query is canceled via dbcancel(), and nsql() will abort further
processing.
WARNING: Using the following deadlock retry logic together with a callback routine is dangerous. If a deadlock is encountered after some rows have already been processed by the callback, then the data will be processed a second time (or more, if the deadlock is retried multiple times).
The nsql() method also supports automated retries of deadlock errors
(1205). This is disabled by default, and enabled only if the
variable:
$Sybase::DBlib::nsql_deadlock_retrycount
is non-zero. This variable is the number of times to resubmit a given SQL query, and the variable
$Sybase::DBlib::nsql_deadlock_retrysleep
is the delay, in seconds, between retries (default is 60). Normally,
the retries happen silently, but if you want nsql() to carp() about
it, then set:
$Sybase::DBlib::nsql_deadlock_verbose
to a true value, and nsql() will whine about the failure. If all of
the retries fail, then nsql() will return an error, like it normally
does. If you want the code to try forever, then set the retry count
to -1.
Most of the #defines from sybdb.h can be accessed as Sybase::DBlib::NAME (eg Sybase::DBlib::STDEXIT) Additional constants are:
The behaviour of certain aspects of the Sybase::CTlib module can be controled via global or connection specific attributes. The global attributes are stored in the %Sybase::DBlib::Att variable, and the connection specific attributes are stored in the $dbh. To set a global attribute, you would code
$Sybase::CTlib::Att{'AttributeName'} = value;
and to set a connection specific attribute you would code
$dbh->{"AttributeName'} = value;
NOTE!!! Global attribute setting changes do not affect existing
connections, and changing an attribute inside a ct_fetch() does not
change the behaviour of the data retrieval during that ct_fetch()
loop.
The following attributes are currently defined:
These status variables are set by Sybase::DBlib internal routines, and can be accessed using the $dbh->{'variable'} syntax.
&BCP_SETL(TRUE);
$dbh = new Sybase::DBlib $User, $Password;
$dbh->bcp_init("test.dbo.t2", '', '', DB_IN);
$dbh->bcp_meminit(3); # we wish to copy three columns into
# the 't2' table
while(<>)
{
chop;
@dat = split(' ', $_);
$dbh->bcp_sendrow(@dat);
}
$ret = $dbh->bcp_done;
sql() routine
$dbh = new Sybase::DBlib;
$ret = $dbh->sql("select * from sysprocesses");
foreach (@$ret) # Loop through each row
{
@row = @$_;
# do something with the data row...
}
$ret = $dbh->sql("select * from sysusers", sub { print "@_"; });
# This will select all the info from sysusers, and print it
The following message handler differentiates the SHOWPLAN or STATICSTICS messages from other messages:
# Message number 3612-3615 are statistics time / statistics io
# message. Showplan messages are numbered 6201-6225.
# (I hope I haven't forgotten any...)
@sh_msgs = (3612 .. 3615, 6201 .. 6225);
@showplan_msg{@sh_msgs} = (1) x scalar(@sh_msgs);
sub showplan_handler {
my ($db, $message, $state, $severity, $text,
$server, $procedure, $line) = @_;
# Don't display 'informational' messages:
if ($severity > 10) {
print STDERR ("Sybase message ", $message, ",
Severity ", $severity, ", state ", $state);
print STDERR ("\nServer `", $server, "'") if defined ($server);
print STDERR ("\nProcedure `", $procedure, "'")
if defined ($procedure);
print STDERR ("\nLine ", $line) if defined ($line);
print STDERR ("\n ", $text, "\n\n");
}
elsif($showplan_msg{$message}) {
# This is a HOWPLAN or STATISTICS message, so print it out:
print STDERR ($text, "\n");
}
elsif ($message == 0) {
print STDERR ($text, "\n");
}
0;
}
This could then be used like this:
use Sybase::DBlib;
dbmsghandle(\&showplan_handler);
$dbh = new Sybase::DBlib 'mpeppler', $password, 'TROLL';
$dbh->dbsetopt(DBSHOWPLAN);
$dbh->dbsetopt(DBSTAT, "IO");
$dbh->dbsetopt(DBSTAT, "TIME");
$dbh->dbcmd("select * from xrate where date = '951001'");
$dbh->dbsqlexec;
while($dbh->dbresults != NO_MORE_RESULTS) {
while(@dat = $dbh->dbnextrow) {
print "@dat\n";
}
}
Et voila!
The Sybase::Sybperl package is designed for backwards compatibility with sybperl 1.0xx (for Perl 4.x). It's main purpose is to allow sybperl 1.0xx scripts to work unchanged with Perl 5 & sybperl 2. Using this API for new scripts is not recomended, unless portability with older versions of sybperl is essential.
The sybperl 1.0xx man page is included in this package in pod/sybperl-1.0xx.man
Sybase::Sybperl is layered on top of the Sybase::DBlib package, and could therefore suffer a small performance penalty.
The CT-library module has been written in colaboration with Sybase.
The attributes hash reference can be used to add private attributes to the connection handle that you can later use, and can also be used to set certain connection properties.
To set the connection properties you pass a special hash in the attributes parameter:
$dbh = new Sybase::CTlib 'user', 'pwd', 'SYBASE', undef,
{ CON_PROPS => { CS_HOSTNAME => 'kiruna',
CS_PACKETSIZE => 1024,
CS_SEC_CHALLENGE => CS_TRUE }
};
The following connection properties are currently recognized:
See the Sybase documentation on how and when to use these connection properties.
In addition, you can set the CS_SYB_LANG and CS_SYB_CHARSET properties in the same manner. However - you should be aware that these settings affect all open connections, not just the one that you are openeing with this call to ct_connect(). This behaviour will likely change in the future.
ct_execute($sql)ct_results() until it returns
CS_END_RESULTS or CS_FAIL, or call ct_cancel() before submitting a new
set of SQL commands to the server.
Return values: CS_SUCCEED, CS_FAIL or CS_CANCELED (the operation was canceled).
NOTE: ct_execute() is equivalent to calling ct_command() followed by
ct_send().
NOTE: You should only need to call ct_command()/ct_send() directly
if you want to do RPCs or cursor operations. For straight queries you
should use ct_execute() or ct_sql() instead.
NOTE: You only need to call ct_send() directly if you've used
ct_command() to set up your SQL query.
If the optional $textBind parameter is FALSE then TEXT or IMAGE columns are not bound, and will not be subsequewntly returned by ct_fetch(). Using this feature is a little tricky - please see the discussion on raw TEXT and IMAGE handling elsewhere in this document.
The commonly used values for $res_type are CS_ROW_RESULT, CS_CMD_DONE, CS_CMD_SUCCEED, CS_COMPUTE_RESULT, CS_CMD_FAIL. The full list of values is on page 3-203 OpenClient reference manual.
See also the description of ct_fetchable() below.
The $status value takes the following values: CS_SUCCEED, CS_END_RESULTS, CS_FAIL, CS_CANCELED.
ct_col_types([$doAssoc])ct_describe([$doAssoc])You could use it like this:
$dbh->ct_execute("select name, uid from sysusers");
while(($rc = $dbh->ct_results($restype)) == CS_SUCCEED) {
next unless $dbh->ct_fetchable($restype);
@desc = $dbh->ct_describe;
print "$desc[0]->{NAME}\n"; # prints 'name'
print "$desc[0]->{MAXLENGTH}\n"; # prints 30
....
}
The STATUS field is a bitmask which can be tested for the following values: CS_CANBENULL, CS_HIDDEN, CS_IDENTITY, CS_KEY, CS_VERSION_KEY, CS_TIMESTAMP and CS_UPDATEABLE. 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, unless the UseDateTime attribute described elsewhere in this document is turned on.
The SYBTYPE field is the real Sybase data type for this column.
If $wantRef is non-0, then a reference to an array (or hash) is returned. This reference points to a static array (or hash), so to store the returned rows in an array you must copy the array (or hash):
while($d = $dbh->ct_fetch(1, 1)) {
push(@rows, {%$d});
}
An empty array is returned if there is no data to fetch.
ct_cancel($type)ct_con_props(CS_CON_STATUS) on the connection and returns TRUE if
the connection status CS_CONSTAT_DEAD bit is set. If this call returns
TRUE (ie non-0) the connection has been marked DEAD and you need to
reconnect to the server.
ct_res_info($info_type)Values for $action: CS_SET, CS_GET, CS_CLEAR
Values for $option: see p.3-170 of the OpenClient reference manual
Values for $param: When setting an option, $param can be a integer or a string. When retrieving an option, $param is set and returned. When clearing an option, $param is ignored.
Value for $type: CS_INT_TYPE if $param is of integer type, CS_CHAR_TYPE if $param is a string
ct_cursor() call, except that when in C you would pass NULL as the
value for $name or $text you pass the special Perl value undef
instead.
See eg/ct_cursor.pl for an example.
ct_param(\%datafmt)The hash should be used like this:
%param = (name => '@acc', datatype => CS_CHAR_TYPE,
status => CS_INPUTVALUE, value => 'CIS 98941',
indicator => CS_UNUSED);
$dbh->ct_param(\%param);
Note that ct_param() converts all parameter types to either
CS_CHAR_TYPE, CS_FLOAT_TYPE, CS_DATETIME_TYPE, CS_MONEY_TYPE or
CS_INT_TYPE.
See eg/ct_param.pl for an example.
ct_cursor() above, and
the section on cursors in Chapter 2 of the
Open Client Client-Library/C Reference manual.
The two handles also share attributes, so setting $dbh->{UseDataTime} (for example) will also set $dbh2->{UseDateTime}.
ct_config() to change some basic parameter, like the
interfaces file location.
$action can be CS_SET or CS_GET.
$property is one of the properties that is settable via ct_config()
(see your OpenClient man page on ct_config() for a complete list).
$value is the input value if $action is CS_SET, and the output value if $action is CS_GET.
$type is the data type of the property that is being set or retrieved. It defaults to CS_CHAR_TYPE, but should be set to CS_INT_TYPE if an integer value (such CS_NETIO is being set or retrieved).
$ret is the return status of the ct_config() call.
Example:
$ret = ct_config(CS_SET, CS_IFILE, "/home/mpeppler/foo", CS_CHAR_TYPE);
print "$ret\n";
$ret = ct_config(CS_GET, CS_IFILE, $out, CS_CHAR_TYPE);
print "$ret - $out\n"; #prints 1 - /home/mpeppler/foo
cs_dt_info() allows you to set the default conversion modes for
DATETIME values, and lets you query the locale database for
names for dateparts.
To set the default conversion you call cs_dt_info() with
a $type parameter of CS_DT_CONVFMT, and pass the conversion style
you want as the last parameter:
cs_dt_info(CS_SET, CS_DT_CONVFMT, CS_UNUSED, CS_DATES_LONG);
See Table 2-26 in the Open Client and Open Server Common Libraries Reference Manual for details of other formats that are available.
You can query a datepart name by doing something like:
cs_dt_info(CS_GET, CS_MONTH, 3, $buf);
print "$buf\n"; # Prints 'April' in the default locale
Again see the entry for cs_dt_info() in Chapter 2 of the Open Client and
Open Server Common Libraries Reference Manual for details.
ct_get_data() should be called in a loop until $ret == CS_END_DATA
(all the data for this column has been retrieved, and this is the
last column in the row) or $ret == CS_END_ITEM (all the data for this
column has been retrieved, but there are more columns to be processed).
The TEXT columns must appear after all the normal data columns
in the select list for this to work, and ct_results() must be called
with $textBind set to 0.
See the discussion on raw TEXT/IMAGE processing elsewhere in this document.
If the data item is large it can be stored in chunks by calling
ct_send_data() multiple times.
Please see the discussion on raw TEXT/IMAGE handling elsewhere in this document for details.
ct_data_info() retrieves a CS_IODESC
struct for column $colnum. The CS_IODESC struct is stored internally
in the $dbh, and stores the text pointer, the total length and whether
logging should be turned on for updates.
If $dbh_2 is passed and $action is CS_SET then the CS_IODESC struct from $dbh_2 is copied to $dbh. This is usefull if you need to update TEXT columns in multiple rows by selecting the rows in one connection and doing the update in a different connection.
The CS_IODESC struct is referenced when calling ct_send_data() to
tell OpenClient where to store the data that is being sent to the server,
as well as what the total size of the column is supposed to be.
The CS_IODESC is typically set during a select query to retrieve a valid text pointer to the column that you wish to update. Sybase::CTlib is limited to a single CS_IODESC struct per connection, so you can only update a single TEXT or IMAGE column at a time.
For details on CS_IODESC please see the Sybase OpenClient Client Library Reference Manual.
For examples on the usage of ct_data_info() please see the discussion on
raw TEXT processing elsewhere in this document.
If the $doAssoc parameter is CS_TRUE, then each row is a reference to an associative array (keyed on the column names) rather than a normal array (see ct_fetch(), above).
If you provide a second parameter it is taken as a procedure to call for each row. The callback is called with the values of the row as parameters.
This routine is very usefull to send SQL commands to the server that do not return rows, such as:
$dbh->ct_sql("use BugTrack");
Examples can be found in eg/ct_sql.pl.
NOTE: This routine loads all the data into memory. Memory consumption can therefore become quite important for a query that returns a large number of rows, unless the MaxRows attribute has been set.
Two additional attributes are set after calling ct_sql(): ROW_COUNT holds the number of rows affected by the command, and RC holds the return code of the last call to ct_execute().
ct_fetchable($restype)
$dbh->ct_execute("select * from sysprocesses");
while($dbh->ct_results($restype) == CS_SUCCEED) {
next if(!$dbh->ct_fetchable($restype));
while(@dat = $dbh->ct_fetch) {
print "@dat\n";
}
}
#!/usr/local/bin/perl
use Sybase::CTlib;
ct_callback(CS_CLIENTMSG_CB, \&msg_cb);
ct_callback(CS_SERVERMSG_CB, "srv_cb");
$uid = 'mpeppler'; $pwd = 'my-secret-password'; $srv = 'TROLL';
$X = Sybase::CTlib->ct_connect($uid, $pwd, $srv);
$X->ct_execute("select * from sysusers");
while(($rc = $X->ct_results($restype)) == CS_SUCCEED) {
next if($restype == CS_CMD_DONE || $restype == CS_CMD_FAIL ||
$restype == CS_CMD_SUCCEED);
if(@names = $X->ct_col_names()) {
print "@names\n";
}
if(@types = $X->ct_col_types()) {
print "@types\n";
}
while(@dat = $X->ct_fetch) {
print "@dat\n";
}
}
print "End of Results Sets\n" if($rc == CS_END_RESULTS);
print "Error!\n" if($rc == CS_FAIL);
sub msg_cb {
my($layer, $origin, $severity, $number, $msg, $osmsg, $dbh) = @_;
printf STDERR "\nOpen Client Message: (In msg_cb)\n";
printf STDERR "Message number: LAYER = (%ld) ORIGIN = (%ld) ",
$layer, $origin;
printf STDERR "SEVERITY = (%ld) NUMBER = (%ld)\n",
$severity, $number;
printf STDERR "Message String: %s\n", $msg;
if (defined($osmsg)) {
printf STDERR "Operating System Error: %s\n", $osmsg;
}
CS_SUCCEED;
}
sub srv_cb {
my($dbh, $number, $severity, $state, $line, $server,
$proc, $msg) = @_;
# If $dbh is defined, then you can set or check attributes
# in the callback, which can be tested in the main body
# of the code.
printf STDERR "\nServer message: (In srv_cb)\n";
printf STDERR "Message number: %ld, Severity %ld, ",
$number, $severity;
printf STDERR "State %ld, Line %ld\n", $state, $line;
if (defined($server)) {
printf STDERR "Server '%s'\n", $server;
}
if (defined($proc)) {
printf STDERR " Procedure '%s'\n", $proc;
}
printf STDERR "Message String: %s\n", $msg; CS_SUCCEED;
}
The behaviour of certain aspects of the Sybase::CTlib module can be controled via global or connection specific attributes. The global attributes are stored in the %Sybase::CTlib::Att variable, and the connection specific attributes are stored in the $dbh. To set a global attribute, you would code
$Sybase::CTlib::Att{'AttributeName'} = value;
and to set a connection specific attribute you would code
$dbh->{"AttributeName'} = value;
NOTE!!! Global attribute setting changes do not affect existing
connections, and changing an attribute inside a ct_fetch() does not
change the behaviour of the data retrieval during that ct_fetch()
loop.
The following attributes are currently defined:
ct_fetch() in native
format instead of converting the data to a character string. Default:
FALSE.
ct_fetch() in native format
instead of converting the data to double precision floating
point. Default: FALSE.
ct_fetch()
in native format, instead of converting to double precision floating
point. Default: FALSE.
ct_fetch() to character strings. This ensures that there is no
precision loss associated with converting fixed decimal values to
floating point representation, and is faster than using the UseNumeric
and/or UseMoney attributes. UseChar overrides the setting for
UseNumeric and/or UseMoney.
ct_get_data() and ct_send_data() to do raw TEXT processingAs of release 2.09_06 of sybperl Sybase::CTlib includes the ability
to process TEXT and IMAGE datatypes using perl versions of
ct_get_data() and ct_send_data(). Using these functions is a little
tricky, however.
NOTE: This discussion applies equally to TEXT and IMAGE datatypes, even if only one or the other is mentioned in the text.
ct_get_data()First lets see how ct_get_data() is implemented to retrieve TEXT
or IMAGE data types in raw format, and (possibly) in retrieve large
data items in smaller, more manageable pieces.
First, it is essential that the TEXT columns appear last in the select statement (there can be several TEXT columns in the statement, but they must appear after any regular columns.
For example:
select userID, userName, msgText
from messageTable
(where msgText is a TEXT column) would work fine.
You issue the query in the normal way:
$dbh->ct_execute("select userID, userName, msgText
from messageTable where userID = 5");
You call ct_results() in the normal way, with the exception that you
pass the $textBind param as FALSE to prevent ct_fetch() from
returning the TEXT column.
If there are fetchable results, you call ct_fetch() to retrieve the
normal data columns, and for each row you then call ct_get_data()
to retrieve the TEXT column(s).
For example:
$dbh->ct_execute("select userID, userName, msgText
from messageTable where userID = 5");
while($dbh->ct_results($restype, 0) == CS_SUCCEED) {
next unless $dbh->ct_fetchable($restype);
while(@row = $dbh->ct_fetch) {
($ret, $msg) = $dbh->ct_get_data(3);
}
ct_send_data()This operation is a little more complicated. Essentially, you must
first select the column that you wish to update to obtain a valid
text pointer (via a call to ct_data_info(CS_GET), then you
initiate a CS_SEND_DATA_CMD command using ct_command(), you
set the new total length of the column via ct_data_info(CS_SET),
send the data to the server via ct_send_data(), commit the operation
with ct_send(), and then process the results in the normal way
with ct_results() and ct_fetch().
For example, assuming the following table:
create table blobtext(id numeric(5,0) identity,
data image)
We would update the data column of a particular row like this:
$dbh->ct_execute("select id, data from testdb..blobtest where id = 5");
my $restype;
while($dbh->ct_results($restype) == CS_SUCCEED) {
next unless($dbh->ct_fetchable($restype));
my @dat;
while(@dat = $dbh->ct_fetch) {
$dbh->ct_data_info(CS_GET, 2);
}
}
my $data = "This is a the new content that we want to place in
the 'data' column for the row";
my @dat;
$dbh->ct_command(CS_SEND_DATA_CMD, '', CS_UNUSED, CS_COLUMN_DATA);
$dbh->ct_data_info(CS_SET, 2, {total_txtlen => length($data)});
$dbh->ct_send_data($data, length($data));
$dbh->ct_send;
while($dbh->ct_results($restype) == CS_SUCCEED) {
next unless $dbh->ct_fetchable($restype);
while(@dat = $dbh->ct_fetch) {
print "@dat\n";
}
}
The last ct_fetch() will return one column - the new text pointer. At the
moment there is no way to make use of this text pointer directly.
You can also update TEXT fields on a set of rows by using a second
connection and performing the ct_send_data() in a nested loop:
$dbh->ct_execute("select id, data from testdb..blobtest");
my $restype;
while($dbh->ct_results($restype) == CS_SUCCEED) {
next unless($dbh->ct_fetchable($restype));
my @dat;
while(@dat = $dbh->ct_fetch) {
$dbh->ct_data_info(CS_GET, 2);
# get the data to be updated, based on the 'id' column
# presumably the get_data() function knows what to do :-)
my $data = get_data($dat[0]);
$dbh2->ct_command(CS_SEND_DATA_CMD, '', CS_UNUSED, CS_COLUMN_DATA);
# copy the CS_IODESC struct from $dbh to $dbh2, and
# set 'total_txtlen' to the correct value.
$dbh2->ct_data_info(CS_SET, 2, {total_txtlen => length($data)}, $dbh);
$dbh2->ct_send_data($data, length($data));
$dbh2->ct_send;
while($dbh2->ct_results($restype) == CS_SUCCEED) {
next unless $dbh2->ct_fetchable($restype);
while(@dat = $dbh2->ct_fetch) {
print "@dat\n";
}
}
}
}
=item $module_name::debug($bitmask)
Turns the debugging trace on or off. The $module_name should be one of Sybase::DBlib or Sybase::CTlib. The value of $bitmask determines which features are going to be traced. The following trace bits are currently recognized:
ct_execute() or
ct_command().)
ct_cursor() (not available in Sybase::DBlib).
ct_param() (not implemented in Sybase::DBlib).
Two special trace flags are TRACE_NONE, which turns off debug tracing, and TRACE_ALL which (you guessed it!) turns everything on.
The traces are pretty obscure, but they can be useful when trying to find out what is really going on inside the program.
For the TRACE_* flags to be available in your scripts, you must load the Sybase::??lib module with the following syntax:
use Sybase::CTlib qw(:DEFAULT /TRACE/);
This tells the autoloading mechanism to import all the default symbols, plus all the trace symbols.
NOTE: This feature is turned off by default for performance reasons. You can turn it on per datatype and dbh, or via the module attribute hash (%Sybase::DBlib::Att and %Sybase::CTlib::Att).
The Sybase::CTlib and Sybase::DBlib modules include special features
to handle DATETIME, MONEY, and NUMERIC/DECIMAL (CTlib
only) values in their native formats correctly. What this means is
that when you retrieve a date using ct_fetch() or dbnextrow() it is
not converted to a string, but kept in the internal format used by the
Sybase libraries. You can then manipulate this date as you see fit,
and in particular 'crack' the date into it's components.
The same is true for MONEY (and for CTlib NUMERIC values),
which otherwise are converted to floating point values, and hence are
subject to loss of precision in certain situations. Here they are
stored as MONEY values, and by using operator overloading we can
give you intuitive access to the cs_calc()/dbmnyxxx() routines.
This feature has been implemented by creating new classes in both Sybase::DBlib and Sybase::CTlib: Sybase::DBlib::DateTime, Sybase::DBlib::Money, Sybase::CTlib::DateTime, Sybase::CTlib::Money and Sybase::CTlib::Numeric (hereafter referred to as DateTime, Money and Numeric). All the examples below use the CTlib module. The syntax is identical for the DBlib module, except that the Numeric class does not exist.
To create data items of these types you call:
$dbh = new Sybase::CTlib user, password;
... # code deleted
# Create a new DateTime object, and initialize to Jan 1, 1995:
$date = $dbh->newdate('Jan 1 1995');
# Create a new Money object $mny = $dbh->newmoney; # Default value is 0
# Create a new Numeric object $num = $dbh->newnumeric(11.111);
The DateTime class defines the following methods:
cmp($date2)diff($date2)info($datepart)NOTE: Not implemented in DBlib.
Both the B<str> and the B<cmp> methods will be called transparently when they are needed, so that
print "$date"
will print the date string correctly, and
$date1 cmp $date2
will do a comparison of the two dates, not the two strings.
crack executes cs_dt_crack()/dbdatecrack() on the date value, and
returns the following list:
($year, $month, $month_day, $year_day, $week_day, $hour,
$minute, $second, $millisecond, $time_zone) = $date->crack;
Compare this with the value returned by the standard Perl function localtime():
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) =
localtime(time);
In addition, the values returned for the week_day can change depending on the locale that has been set.
Please see the discussion on cs_dt_crack() or dbdatecrack() in the
Open Client / Open Server Common Libraries Reference Manual, chap. 2.
The Money and Numeric classes define these methods
cmp($mny2)set($number)As with the DateTime class, the str and cmp methods will be called automatically for you when required. In addition, you can perform normal arithmetic on Money or Numeric datatypes without calling the calc method explicitly.
CAVEAT! You must call the set method to assign a value to a Money/Numeric data item. If you use
$mny = 4.05
then $mny will loose its special Money or Numeric behavior and become a normal Perl data item.
When a new Numeric data item is created, the SCALE and
PRECISION values are determined by the initialization. If the data
item is created as part of a SELECT statement, then the SCALE
and PRECISION values will be those of the retrieved item. If the
item is created via the newnumeric method (either explicitly or
implicitly) the SCALE and PRECISION are deduced from the
initializing value. For example, $num = $dbh->newnumeric(11.111) will
produce an item with a SCALE of 3 and a PRECISION of 5. This is
totally transparent to the user.
The Sybase::DBlib 2PC calls have not been well tested.
There is a (approximately) 300 byte memory leak in the newdbh() function
in Sybase/DBlib.xs and Sybase/CTlib.xs. This function is called when a
new connection is created, or, in the case of CTlib, when extended
error information is processed in the server callback.
I have not been able to locate the real cause of the leak so far. Patches
that appear to solve the problem are welcome!
I have a simple bug tracking system at http://gw.peppler.org/cgi-bin/bug.cgi. It's on the end of a K56 line, so it may be slow (depending on what I happen to be doing!) You can use it to check for known bugs, or to submit new ones.
Larry Wall - for Perl :-)
Tim Bunce & Andreas Koenig - for all the work on MakeMaker
Michael Peppler <mpeppler@peppler.org>
Dave Bowen & Amy Lin for help with Sybase::CTlib.
Jeffrey Wong for the Sybase::DBlib DBMONEY routines.
W. Phillip Moore <wpm@ms.com> for the nsql() method.
Numerous folks have contributed ideas and bug fixes for which they have my undying thanks :-)
The sybperl mailing list <sybperl-l@listproc.net> is the best place to ask questions.