NAME

SYNOPSIS

DESCRIPTION

The general usage format for both Sybase::DBlib and Sybase::CTlib is this:

The DBPROCESS or CS_CONNECTION that is opened with the call to new() is automatically closed when the $dbh goes out of scope:

Attributes

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:

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:

DateTime, Money and Numeric data behavior

Please see the discussion on Special handling of DATETIME, MONEY & NUMERIC/DECIMAL values below for details.

Compatibility with Sybase Open Client documentation.

You should if possible have the Sybase Open Client documentation available when writing Sybperl programs.

Sybase::DBlib

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:

$dbh = new Sybase::DBlib [$user [, $server [, $appname [, {additional attributes}]]]]

$dbh = Sybase::DBlib->dblogin([$user [, $pwd [, $server [, $appname, [{additional attributes}] ]]]])

Initiates a connection to a Sybase dataserver, using the supplied user, password, server and application name information. Uses the default values (see 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 .

$dbh = Sybase::DBlib->dbopen([$server [, $appname, [{attributes}] ]])

Open an additional connection, using the current LOGINREC information.

$status = $dbh ->dbuse($database)

Executes ``use database $database'' for the connection $dbh .

$status = $dbh ->dbcmd($sql_cmd)

Appends the string $sql_cmd to the current command buffer of this connection.

$status = $dbh ->dbsqlexec

Sends the content of the current command buffer to the dataserver for execution. See the DB-library documentation for a discussion of return values.

$status = $dbh ->dbresults

Retrieves result information from the dataserver after having executed dbsqlexec().

$status = $dbh ->dbcancel

Cancels the current command batch.

$status = $dbh ->dbcanquery

Cancels the current query within the currently executing command batch.

$dbh ->dbfreebuf

Free the command buffer (required only in special cases - if you don't know what this is you probably don't need it :-)

$dbh ->dbclose

Force the closing of a connection. Note that connections are automatically closed when the $dbh goes out of scope.

$dbh ->DBDEAD

Returns TRUE if the DBPROCESS has been marked DEAD by DBlibrary.

$status = $dbh ->DBCURCMD

Returns the number of the currently executing command in the command batch. The first command is number 1.

$status = $dbh ->DBMORECMDS

Returns TRUE if there are additional commands to be executed in the current command batch.

$status = $dbh ->DBCMDROW

Returns SUCCEED if the current command can return rows.

$status = $dbh ->DBROWS

Returns SUCCEED if the current command did return rows

$status = $dbh ->DBCOUNT

Returns the number of rows that the current command affected.

$row_num = $dbh ->DBCURROW

Returns the number (counting from 1) of the currently retrieved row in the current result set.

$status = $dbh ->dbhasretstat

Did the last executed stored procedure return a status value? dbhasretstats must only be called after dbresults returns NO_MORE_RESULTS, ie after all the selet, insert, update operations of he sored procedure have been processed.

$status = $dbh ->dbretstatus

Retrieve the return status of a stored procedure. As with dbhasretstat, call this function after all the result sets of the stored procedure have been processed.

$status = $dbh ->dbnumcols

How many columns are in the current result set.

$status = $dbh ->dbcoltype($colid)

What is the column type of column $colid in the current result set.

$status = $dbh ->dbcollen($colid)

What is the length (in bytes) of column $colid in the current result set.

$string = $dbh ->dbcolname($colid)

What is the name of column $colid in the current result set.

@dat = $dbh ->dbnextrow([$doAssoc])

Retrieve one row. 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(). 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"; }

@dat = $dbh ->dbretdata[$doAssoc])

Retrieve the value of the parameters marked as 'OUTPUT' in a stored procedure. If $doAssoc is non-0, then retrieve the data as an associative array with parameter name/value pairs.

$string = $dbh ->dbstrcpy

Retrieve the contents of the command buffer.

$ret = $dbh ->dbsetopt($opt [, $c_val [, $i_val]])

Sets option $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.

$ret = $dbh ->dbclropt($opt [, $c_val])

Clears the option $opt, previously set using dbsetopt().

$ret = $dbh ->dbisopt($opt [, $c_val])

Returns TRUE if the option $opt is set.

$string = $dbh ->dbsafestr( $string [,$quote_char])

Convert $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.

$status = $dbh ->dbwritetext($colname, $dbh_2, $colnum, $text [, $log])

Insert or update data in a TEXT or IMAGE column. The usage is a bit different from that of the C version:

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);

$packet_size = $dbh ->dbgetpacket

Returns the TDS packet size currently in use for this $dbh .

BCP_SETL( $state )

This is an exported routine (ie it can be called without a $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.

$state = bcp_getl

Retrieve the current BCP flag status.

$status = $dbh ->bcp_init($table, $hfile, $errfile, $direction)

Initialize BCP library. $direction can be DB_OUT or DB_IN

$status = $dbh ->bcp_meminit($numcols)

This is a utility function that does not exist in the normal BCP API. It's use is to initialize some internal variables before starting a BCP operation from program variables into a table. This call avoids setting up translation information for each of the columns of the table being updated, obviating the use of the bcp_colfmt call.

See EXAMPLES, below.

$status = $dbh ->bcp_sendrow(LIST)

$status = $dbh ->bcp_sendrow(ARRAY_REF)

Sends the data in LIST to the server. The LIST is assumed to contain one element for each column being updated. To send a NULL value set the appropriate element to the Perl undef value.

In the second form you pass an array reference instead of passing the LIST, which makes processing a little bit faster on wide tables.

$rows = $dbh ->bcp_batch

Commit rows to the database. You usually use it like this:

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; } }

$status = $dbh ->bcp_done

$status = $dbh ->bcp_control($field, $value)

$status = $dbh ->bcp_columns($colcount)

$status = $dbh ->bcp_colfmt($host_col, $host_type, $host_prefixlen, $host_collen, $host_term, $host_termlen, $table_col [, $precision, $scale])

If you have DBlibrary for System 10 or higher, then you can pass the additional $precision and $scale parameters, and have sybperl call bcp_colfmt_ps() instead of bcp_colfmt().

$status = $dbh ->bcp_collen($varlen, $table_column)

$status = $dbh ->bcp_exec

$status = $dbh ->bcp_readfmt($filename)

$status = $dbh ->bcp_writefmt($filename)

Please see the DB-library documentation for these calls.

( $status , $sum) = $dbh ->dbmny4add($m1, $m2)

$status = $dbh ->dbmny4cmp($m1, $m2)

( $status , $quotient) = $dbh ->dbmny4divide($m1, $m2)

( $status , $dest) = $dbh ->dbmny4minus($source)

( $status , $product) = $dbh ->dbmny4mul($m1, $m2)

( $status , $difference) = $dbh ->dbmny4sub($m1, $m2)

( $status , $ret ) = $dbh ->dbmny4zero

( $status , $sum) = $dbh ->dbmnyadd($m1, $m2)

$status = $dbh ->dbmnycmp($m1, $m2)

( $status , $ret ) = $dbh ->dbmnydec($m1)

( $status , $quotient) = $dbh ->dbmnydivide($m1, $m2)

( $status , $ret , $remainder) = $dbh ->dbmnydown($m1, $divisor)

( $status , $ret ) = $dbh ->dbmnyinc($m1)

( $status , $ret , $remain) = $dbh ->dbmnyinit($m1, $trim)

( $status , $ret ) = $dbh ->dbmnymaxneg

( $status , $ret ) = $dbh ->dbmnymaxpos

( $status , $dest) = $dbh ->dbmnyminus($source)

( $status , $product) = $dbh ->dbmnymul($m1, $m2)

( $status , $m1, $digits, $remain) = $dbh ->dbmnyndigit($m1)

( $status , $ret ) = $dbh ->dbmnyscale($m1, $multiplier, $addend)

( $status , $difference) = $dbh ->dbmnysub($m1, $m2)

( $status , $ret ) = $dbh ->dbmnyzero

All of these routines correspond to their DB-library counterpart, with the following exception:

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)

$dbh ->dbrpcinit($rpcname, $option)

Initialize an RPC call to the remote procedure $rpcname. See the DB-library manual for valid values for $option.

$dbh ->dbrpcparam($parname, $status , $type, $maxlen, $datalen, $value)

Add a parameter to an RPC call initiated with 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.

$dbh ->dbrpcsend

Execute an RPC initiated with dbrpcinit().

NOTE: This call executes both dbrpcsend() and dbsqlok(). You can call $dbh ->dbresults direcly after calling $dbh ->dbrpcsend.

dbrpwset($srvname, $pwd)

Set the password for connecting to a remote server.

dbrpwclr

Clear all remote server passwords.

$dbh = Sybase::DBlib->open_commit($user, $pwd, $server, $appname)

$id = $dbh ->start_xact($app_name, $xact_name, $site_count)

$status = $dbh ->stat_xact( $id )

$status = $dbh ->scan_xact( $id )

$status = $dbh ->commit_xact( $id )

$status = $dbh ->abort_xact( $id )

$dbh ->close_commit

$string = Sybase::DBlib::build_xact_string($xact_name, $service_name, $id )

$status = $dbh ->remove_xact( $id , $site_count)

Please see the Sybase documentation for this.

NOTE: These routines have not been thouroughly tested!

$old_handler = dberrhandle($err_handle)

$old_handler = dbmsghandle($msg_handle)

Register an error (or message) handler for DB-library to use. Handler examples can be found in sybutil.pl in the Sybperl distribution. Returns a reference to the previously defined handler (or undef if none were defined). Passing undef as the argument clears the handler.

dbsetifile($filename)

Set the name of the 'interfaces' file. This file is normally found by DB-library in the directory pointed to by the $SYBASE environment variable.

dbrecftos($filename)

Start recording all SQL sent to the server in file $filename.

dbversion

Returns a string identifying the version of DBlibrary that this copy of Sybperl was built with.

DBSETLCHARSET($charset)

DBSETLNATLANG($language)

DBSETLPACKET( $packet_size )

$time = DBGETTIME

$time = dbsettime($seconds)

$time = dbsetlogintime($seconds)

These utility routines are probably very seldom used. See the DB-library manual for an explanation of their use.

dbexit

Tell DB-library that we're done. Once this call has been made, no further activity requiring DB-library can be performed in the current program.

$ret |@ret = $dbh ->sql($cmd [, \&rowcallback [, $flag]])

Runs the sql command and returns the result as a reference to an array of the rows. In a LIST context, return the array itself (instead of a reference to the array). Each row is a reference to an array of scalars.

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.

$Sybase::DBlib::Version

The Sybperl version. Can be interpreted as a string or as a number.

DBLIBVS

The version of DBlibrary that sybperl was built against.

Attributes:

and to set a connection specific attribute you would code

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:

dbNullIsUndef

If set, NULL results are returned as the Perl 'undef' value, otherwise as the string ``NULL''. Default: set.

dbKeepNumeric

If set, numeric results are not converted to strings before returning the data to Perl. Default: set.

dbBin0x

If set, BINARY results are preceeded by '0x' in the result. Default: unset.

useDateTime

Turn the special handling of DATETIME values on. Default: unset. See the section on special datatype handling below.

useMoney

Turn the special handling of MONEY values on. Default: unset. See the section on special datatype handling below.

Status Variables

DBstatus

The return status of the last call to dbnextrow.

ComputeID

The compute id of the current returned row. Is 0 if no compute by clause is currently being processed.

Examples

BCP from program variables

See also Sybase::BCP for a symplified bulk copy API.

&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;

Using the 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

Getting SHOWPLAN and STATISTICS information within a script

You can get SHOWPLAN and STATISTICS information when you run a sybperl script. To do so, you must first turn on the respective options, using 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!

---

BUGS

The 2PC calls have not been well tested.

---

Sybase::Sybperl

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.

---

Sybase::CTlib

The CT-library module has been written in colaboration with Sybase. It is still in Beta.

---

DESCRIPTION

$dbh = new Sybase::CTlib $user [, $passwd [, $server [, $appname[, {attributes}]

$dbh = Sybase::CTlib->ct_connect($user [, $passwd [, $server [,$appname, [{attributes}]]]])

Establishes a connection to the database engine. Initializes and allocates resources for the connection, and registers the user name, password, target server and application name.

Additional attributes can be set by passing a hash reference.

$status = $dbh ->ct_execute($sql)

Send the SQL commands $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().

$status = $dbh ->ct_command(type, buffer, len, option)

Append a command to the current SQL command buffer. Please check the OpenClient documentation for exact usage.

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.

$status = $dbh ->ct_send

Send the current command buffer to the server for execution.

NOTE: You only need to call ct_send() directly if you've used ct_command() to set up your SQL query.

$status = $dbh ->ct_results($res_type)

This routine returns a results type to indicate the status of returned data. ``Command Done:'' result type is returned if one result set has been processed. ``Row result'' token is returned if regular rows are returned. This output is stored in $res_type.

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.

@names = $dbh ->ct_col_names

Retrieve the column names of the current query. If the current query is not a select statement, then an empty array is returned.

@types = $dbh ->ct_col_types([$doAssoc])

Retrieve the column types of the currently executing query. If $doAssoc is non-0, then a hash (aka associative array) is returned with column names/column type pairs.

@data = $dbh ->ct_describe([$doAssoc])

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. The following fields are set: NAME , TYPE, MAXLENGTH, SCALE, PRECISION, STATUS.

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.

@data = $dbh ->ct_fetch([$doAssoc])

Retrieve one row of data. If $doAssoc is non-0, a hash is returned with column name/value pairs.

An empty array is returned if there is no data to fetch.

$dbh ->ct_cancel($type)

Issue an attention signal to the server about the current transaction. If $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.

$old_cb = ct_callback($type, $cb_func)

Install a callback routine. Valid callback types are CS_CLIENTMSG_CB and CS_SERVERMSG_CB. Returns a reference to the previously installed callback of the specified type, or undef if no callback of that type exists. Passing undef as $cb_func unsets the callback for that type.

$res_info = $dbh ->ct_res_info($info_type)

Retrieves information on the current result set. The type of information returned depends on $info_type. Currently supported values are: CS_NUM_COMPUTES, CS_NUMDATA, CS_NUMORDERCOLS, CS_ROW_COUNT.

( $status , $param) = $dbh ->ct_option($action, $option, $param, $type)

This routine will set, retrieve or clear the values of server query-processing options.

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

$ret = $dbh ->ct_cursor($type, $name, $text, $option)

Initiate a cursor command. Usage is similar to the CTlibrary 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.

$ret = $dbh ->ct_param(\%datafmt)

Define a command parameter. The %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 = $dbh ->ct_cmd_alloc

Allocate a new CS_COMMAND structure. The new $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 attributes values are copied from $dbh to $dbh2 .

$rc = $dbh ->ct_cmd_realloc

Drops the current CS_COMMAND structure, and reallocs a new one. Returns CS_SUCCEED on successfull completion.

$ret = ct_config($action, $property, $value)

Calls ct_config() to change some basic parameter, like the $action can be <B>CS_SET</B> or <B>CS_GET</B>.

$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 <B>CS_GET</B>, and the output value if $action is <B>CS_GET</B>.

$ret is the return status of the ct_config() call.

Example:

$ret = ct_config(CS_SET, CS_IFILE, "/home/mpeppler/foo"); print "$ret\n"; $ret = ct_config(CS_GET, CS_IFILE, $out); print "$ret - $out\n"; #prints 1 - /home/mpeppler/foo

$ret |@ret = $dbh ->ct_sql($cmd [, \&rowcallback [, $doAssoc]])

Runs the sql command and returns the result as a reference to an array of the rows. Each row is a reference to an array of scalars. In a LIST context, ct_sql returns an array of references to each row.

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().

$ret = $dbh ->ct_fetchable($restype)

Returns TRUE if the current result set has fetchable rows. Use like this:

$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"; } }

.

---

EXAMPLES

#!/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) = @_; 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($cmd, $number, $severity, $state, $line, $server, $proc, $msg) = @_; 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; }

---

ATTRIBUTES

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:

UseDateTime

If TRUE, then keep DATETIME data retrieved via ct_fetch() in native format instead of converting the data to a character string. Default: FALSE.

UseMoney

If TRUE, keep MONEY data retrieved via ct_fetch() in native format instead of converting the data to double precision floating point. Default: FALSE.

UseNumeric

If TRUE, keep NUMERIC or DECIMAL data retrieved via ct_fetch() in native format, instead of converting to double precision floating point. Default: FALSE.

MaxRows

If non-0, limit the number of data rows that can be retrieve via ct_sql(). Default: 0.

.

---

Common Sybase::DBlib and Sybase::CTlib routines

$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:

* TRACE_CREATE

Trace all CTlib and/or DBlib object creations.

* TRACE_DESTROY

Trace all calls to DESTROY.

* TRACE_SQL

Traces all SQL language commands - (ie calls to dbcmd(), ct_execute() or ct_command().)

* TRACE_RESULTS

Traces calls to dbresults()/ct_results().

* TRACE_FETCH

Traces calls to dbnextrow()/ct_fetch(), and traces the values that are pushed on the stack.

* TRACE_CUSROR

Trace calls to ct_cursor() (not available in Sybase::DBlib).

* TRACE_PARAMS

Trace calls to ct_param() (not implemented in Sybase::DBlib).

* TRACE_OVERLOAD

Trace all overloaded operations involving DateTime, Money or Numeric datatypes.

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.

---

Special handling of DATETIME, MONEY & NUMERIC/DECIMAL values

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:

$date->str

Convert to string (calls cs_convert()/dbconvert()).

@arr = $date->crack

'Crack' the date into its components.

$date->cmp( $date2 )

Compare $date with $date2 .

$date2 = $date->calc($days, $msecs)

Add or substract $days and $msecs from $date, and returns the new date.

($days, $msecs) = $date->diff( $date2 )

Compute the difference, in $days and $msecs between $date and $date2 .

$val = $date->info($datepart)

Calls cs_dt_info to return the string representation for a datepart. Valid dateparts are CS_MONTH, CS_SHORTMONTH and CS_DAYNAME.

NOTE: Not implemented in DBlib.

$time = $date->mktime

$time = $date->timelocal

$time = $date->timegm

Converts a Sybase DATETIME value to a Unix time_t value. The mktime and timelocal methods assumes the date is stored in local time, timegm assumes GMT. The mktime method uses the POSIX module (note that unavailability of the POSIX module is not a fatal error.)

Both the str and the 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

$mny->str

Convert to string (calls cs_convert()/dbconvert()).

$mny->num

Convert to a floating point number (calls cs_convert()/dbconvert()).

$mny->cmp($mny2)

Compare two Money or Numeric values.

$mny->set($number)

Set the value of $mny to $number.

$mny->calc($mny2, $op)

Perform the calculation specified by $op on $mny and $mny2. $op is one of '+', '-', '*' or '/'.

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.

---

ACKNOWLEDGEMENTS

Larry Wall - for Perl :-)

Tim Bunce & Andreas Koenig - for all the work on MakeMaker

---

AUTHORS

Michael Peppler <mpeppler@itf.ch>

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.