use Sybase::DBlib; use Sybase::CTlib; use Sybase::Sybperl;
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.
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 => {}};
Please see the discussion on Special handling of DATETIME, MONEY & NUMERIC/DECIMAL values below for details.
You should if possible have the Sybase Open Client documentation available when writing Sybperl programs.
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:
DBSETLUSER(), DBSETLPWD(), etc. in the
Sybase DB-library documentation) if the parameters are ommitted.
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.
$sql_cmd to the current command buffer of
this connection.
dbsqlexec().
dbpoll()
and dbsqlok(). See the Sybase docs for further details.
dbmoretext() in Sybase::DBlib. See also the Sybase
documentation for details.
dbsqlsend() and dbsqlok() to
perform asynchronous queries. dbpoll() will wait up to
$millisecs milliseconds and poll any open DBPROCESS for
results. 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) = Sybase::DBlib::dbpoll(100);
if($dbh2) { # yes! - there's data on the pipe
$dbh2->dbsqlok;
while($dbh2->dbresults != NO_MORE_RESULTS) {
while(@dat = $dbh2->dbnextrow) {
....
}
}
}
$dbh goes out of scope.
$colid in the current result
set.
$colid in the current
result set.
$colid in the current result set.
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";
}
$doAssoc is non-0, then retrieve the data as an
associative array with parameter name/value pairs.
$opt with optional character parameter
$c_val and optional integer parameter $i_val.
$opt is one of the option values defined in the Sybase
DBlibrary manual (f.eg. DBSHOWPLAN, DBTEXTSIZE). For example, to set
SHOWPLAN on, you would use
$dbh->dbsetopt(DBSHOWPLAN);
See also dbclropt() and dbisopt() below.
dbsetopt().
$opt is set.
$string to a 'safer' version by inserting single or
double quotes where appropriate, so that it can be passed to the dataserver
without syntax errors.
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);
dbmoretext().
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;
$size chunks.
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";
}
}
}
$dbh handle) which sets the BCP IN flag to TRUE/FALSE.
It is necessary to call BCP_SETL(TRUE) before opening the
connection with which one wants to run a BCP IN operation.
$direction can be DB_OUT or DB_IN
See EXAMPLES, below.
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;
}
}
$precision and $scale parameters, and
have sybperl call bcp_colfmt_ps() instead of
bcp_colfmt().
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.
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.
dbrpcinit().
Please see the DB-library manual page for details & values for the
parameters.
NOTE: All floating point types (MONEY, FLOAT, REAL, DECIMAL, etc.) are converted to FLOAT before being sent to the RPC.
dbrpcinit().
NOTE: This call executes both dbrpcsend() and
dbsqlok(). You can call $dbh->dbresults direcly after
calling $dbh->dbrpcsend.
NOTE: These routines have not been thouroughly tested!
$SYBASE
environment variable.
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.
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 ( $dbh->DB_ERROR() ) {
# error handling code goes here, perhaps:
die "Unable to get stuff from table:" . $dbh->DB_ERROR() . "\n";
}
For compatibility with older release, the error variable $DB_ERROR is
still exported, however, direct use of this variable makes it
difficult to pass the Sybase::DBlib object around and use the nsql()
method for queries, since the subroutine using the object will not
necessarily have $DB_ERROR in its namespace. The method will always
be available.
NOTE: This routine was contributed by W. Phillip Moore <wpm@ms.com>.
Most of the #defines from sybdb.h can be accessed as Sybase::DBlib::NAME (eg Sybase::DBlib::STDEXIT) Additional constants are:
$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:
&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;
$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
dbsetopt(), and then you need a special message handler that
will filter the SHOWPLAN and/or STATISTICS messages sent from the server.
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 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 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:
$sql to the server. Multiple commands
are allowed. However, you must call 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.
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.
$doAssoc is non-0, then a hash (aka associative array) is
returned with column names/column type pairs.
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.
$doAssoc is non-0, a hash is
returned with column name/value pairs.
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.
$type == CS_CANCEL_ALL, then cancels the current command
immediately. If $type == CS_CANCEL_ATTN, then discard all
results when next time the application reads from the server.
$cb_func
unsets the callback for that 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.
%datafmt hash is used to pass
the appropriate parameters to the call. The following fields are defined:
name (parameter name), datatype, status, indicator and value). These fields
correspond to the equivalent fields in the CS_DATAFMT structure which is
used in the CTlibrary ct_param call, and includes the two additional
parameters 'value' and 'indicator'.
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.
$dbh2 shares the
CS_CONNECTION with the original $dbh, so this is really only usefull for interleaving
cursor operations (see 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_GET, 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 a string:
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.
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().
$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;
}
$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_sql(). Default: 0.
$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:
dbcmd(),
ct_execute() or ct_command().)
dbresults()/ct_results().
dbnextrow()/ct_fetch(), and traces the values
that are pushed on the stack.
ct_cursor() (not available in Sybase::DBlib).
ct_param() (not implemented in Sybase::DBlib).
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.
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:
cs_convert()/dbconvert()).
$date with $date2.
$days and $msecs from $date, and
returns the new date.
$days and $msecs
between $date and $date2.
NOTE: Not implemented in DBlib.
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
cs_convert()/dbconvert()).
cs_convert()/dbconvert()).
$mny to $number.
$op on $mny
and $mny2. $op is one of '+', '-', '*' or '/'.
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.
Tim Bunce & Andreas Koenig - for all the work on MakeMaker
Dave Bowen & Amy Lin for help with Sybase::CTlib.
Jeffrey Wong for the Sybase::DBlib DBMONEY routines.
Numerous folks have contributed ideas and bug fixes for which they have my undying thanks :-)
The sybperl mailing list <sybperl-l@trln.lib.unc.edu> is the best place to ask questions.