Sybase::CTlib - Sybase Client Library API.

    use Sybase::CTlib;

    $dbh = Sybase::CTlib->new('user', 'pwd', 'server');
    $dbh->ct_execute("select * from master..sysprocesses");
    while($dbh->ct_results($restype) == CS_SUCCEED) {
        if($restype == CS_CMD_FAIL) {
            warn "Command failed!";
            next;
        }
	next unless $dbh->ct_fetchable($restype);
	while(@data = $dbh->ct_fetch) {
	    print "@data\n";
	}
    }

Sybase::CTlib implements a subset of the Sybase Open Client Client Library API. For the most part the syntax is the same or very similar to the C language version, though in some cases the syntax varies a little to make the life of the perl programmer a little easier.

It is a good idea to have the Sybase Client Library reference manual available when writing Sybase::CTlib programs. The Sybase manuals are available on-line at http://sybooks.sybase.com/. This manual is not a replacement for the Sybase manuals of Client Library - it is mostly aimed at illustrating the differences between the Perl and C versions of the API and to give a basic understanding of the APIs.

The basic philosphy of Client Library (CTlib) is to send a command to the server, and then process any results and fetch data as needed. Commands can be sent as plain SQL with one or more statements, or they can be sent as Remote Procedure Calls (RPCs).

CTlib can connect and interact with any type of server that understands Sybase's Tabular Data Stream (TDS) protocol. This means that you can use CTlib to connect to a Sybase database server, a replication server, or any other type of server that was built with the Open Server API.

A typical database request starts with a call to ct_execute() with the SQL command to be executed. This sends the request to the server. You the call ct_results($restype) in a loop until it stops returning CS_SUCCEED. ct_results() sets the $restype (the result type) for each result set. Some of the result types do not include any fetchable rows, hence the ct_fetchable() routine that returns TRUE if a $restype value is one that includes fetchable data:

    $dbh->ct_execute("select * from master..sysprocesses");
    while($dbh->ct_results($restype) == CS_SUCCEED) {
        if($restype == CS_CMD_FAIL) {
            warn "Command failed!";
            next;
        }
	next unless $dbh->ct_fetchable($restype);
	while(@data = $dbh->ct_fetch) {
	    print "@data\n";
	}
    }

ct_execute() will return CS_FAIL if there is an error on the client side. Errors that occur on the server will be reported via the server message callback handler (see ct_callback()), and will in most cases result in a $restype value of CS_CMD_FAIL.

In the case of an error occuring inside a stored procedure or trigger the error is NOT reported via a CS_CMD_FAIL $restype. Instead the return status of the stored procedure ($restype of CS_STATUS_RESULT) is set to -4.

It is a good idea to check for error conditions in-line (i.e. check the return value of all API calls), to check the value of $restype returned from ct_results() for a possible CS_CMD_FAIL status, to check any stored procedure status value (CS_STATUS_RESULT result set) for a negative value (which generally indicates that an error occured in the stored procedure), and install server and client error handlers via ct_callback() to flag any errors (server or client messages where the $severity value is greater than 10).

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

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:

CS_HOSTNAME

CS_ANSI_BINDS

CS_PACKETSIZE

CS_SEC_APPDEFINED

CS_SEC_CHALLENGE

CS_SEC_ENCRYPTION

CS_SEC_NEGOTIATE

CS_HAFAILOVER

See the Sybase documentation on how and when to use these connection properties.

Please see the Sybase manuals for CS_HAFAILOVER usage.

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 opening with this call to ct_connect(). This behavior will likely change in the future.

$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 [, $textBind])

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.

If the optional $textBind parameter is FALSE then TEXT or IMAGE columns are not bound, and will not be subsequently 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 of the 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, SYBTYPE, 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.

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.

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

Retrieve one row of data. If $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.

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

$status = $dbh->DBDEAD

Calls 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 (i.e. non-0) the connection has been marked DEAD and you need to reconnect to 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_options($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 CT-Library 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 CT-Library 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 useful 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}.

$rc = $dbh->ct_cmd_realloc

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

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

Calls 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 as 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

$ret = $dbh->ct_dyn_prepare($sql)

Prepare a Dynamic SQL statement. A Dynamic SQL statement is a normal insert/update/delete SQL statement that includes '?' placeholders. The placeholders are replaced by the values passed to ct_dyn_execute(). The prepared statement can be called multiple times with different variables, making it quite efficient.

Sybase creates a temporary stored procedure for the dynamic SQL statement, and then executes this procedure each time ct_dyn_execute() is called. Only one active prepared statement can exist per connection.

Returns CS_SUCCEED on success, CS_FAIL in case of errors.

Example:

   $ret = $dbh->ct_dyn_prepare("update my_table set string=? where key=?");
   $dbh->ct_dyn_execute(["testing", 123]);
   while($dbh->ct_results($restype) == CS_SUCCEED) {
       next unless $dbh->ct_fetchable($restype);
       # shoudn't have any fetchable results in this example!
   }
   $dbh->ct_dyn_execute(["another test", 456]);
   while($dbh->ct_results($restype) == CS_SUCCEED) {
       next unless $dbh->ct_fetchable($restype);
       # shoudn't have any fetchable results in this example!
   }
   $dbh->ct_dyn_dealloc();    # free the temporary proc.

$ret = $dbh->ct_dyn_execute($arrayref);

Execute a prepared statement with the specified parameters. $arrayref is a reference to an array with one value for each '?' that appears in the SQL statement passed to ct_dyn_prepare().

$ret = $dbh->ct_dyn_dealloc();

Free the resources associated with the prepared statement created by ct_dyn_prepare(). This must be called before you can prepare a new statement.

$ret = cs_dt_info($action, $type, $item, $buf)

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.

($ret, $data) = $dbh->ct_get_data($colnum [, $maxsize])

Retrieve the raw TEXT or IMAGE data from column $colnum in the current result set. The optional $maxsize parameter can be used to limit the size of the retrieved buffer. If $maxsize is used then 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.

$ret = $dbh->ct_send_data($data, $size)

Send $size bytes of data in $data to the server, based on information previously determined via a call to ct_data_info().

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.

$ret = $dbh->ct_data_info($action, $colnum [, \%attr [, $dbh_2]])

When $action is CS_GET 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.

The %attr hash can be used to set the total_txtlen and log_on_update fields of the CS_IODESC by setting the corresponding fields in the %attr param:

	%attr = ( total_txtlen => 1024, log_on_update => 1 );
        $dbh->ct_data_info(CS_SET, $colnum, \%attr);

If $dbh_2 is passed and $action is CS_SET then the CS_IODESC struct from $dbh_2 is copied to $dbh. This is useful 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.

$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 useful 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->nsql($sql [, "ARRAY" | "HASH" ] [, \&subroutine ] );

An enhanced version of the ct_sql routine, nsql, is also available. nsql() provides better error checking (using its companion server error callback), 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.

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. That 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 or failure of an nsql() call cannot necessarily be judged from 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 Sybase server errror handler routine is also provided which will use $DB_ERROR for the Sybase messages and errors as well. However, these must be installed by the client application:

    ct_callback(CS_SERVERMSV_CB, \&Sybase::CTlib::nsql_srv_cb);

Success of failure of an nsql() call cannot necessarily be judged from the value of the return code, as an empty 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::CTlib::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 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 use much less memory.

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 ct_cancel(), 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::CTlib::nsql_deadlock_retrycount

is non-zero. This variable is the number of times to resubmit a given SQL query, and the variable

    $Sybase::CTlib::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::CTlib::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, as it normally does. If you want the code to try forever, then set the retry count to -1.

$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 unless $dbh->ct_fetchable($restype);

	while(@dat = $dbh->ct_fetch) {
	    print "@dat\n";
	}
    }

The following routines allow you to use a subset of the blk_*() routines to bulk-copy data from Perl variables to Sybase tables. The blk_*() routines are faster than normal inserts as they run with minimal logging.

You enable BLK operations on a Sybase::CTlib handle by passing the CS_BULK_LOGIN property to the new() or ct_connect() call:

   my $dbh = new Sybase::CTlib $user, $pwd, $server, $appname, 
                  {CON_PROPS => {CS_BULK_LOGIN => CS_TRUE }};

You then initialise the bulk-copy operation by calling blk_init(), send rows with blk_rowxfer(), commit rows with blk_done(), and end with a call to blk_drop() to clean up resources held during the bulk-copy operation.

Routines:

$ret = $dbh->blk_init($table, $num_cols, $has_identity, $id_column)

This initializes a bulk-copy operation for the table $table. $num_cols should be set to the number of columns of the target table. $has_identity should be set if the target table has an identity column and you provide the identity values as part of your blk operation (i.e. if $has_identity is set, then the identity insert option is set on the table). If you want to let the server set the identity values during the insert then leave $has_identity off, set $id_column to the column number of the identity column (remember that the first column is column 1, the second is 2, etc), and pass undef as the values for that column.

See the t/2_ct_xblk.t test script for an example of both letting the server set the identity values and specifying them directly.

Returns CS_SUCCEED or CS_FAIL.

$ret = $dbh->blk_rowxfer($data)

Send one row to the server. $data is an array reference, where each element is the data for a column in the row.

By default, Sybase::CTlib attempts to convert incoming data to the target format. These conversions can in some cases generate warnings, in particular for NUMERIC data where the precision of the source data exceeds the target column (for example trying to stored 123.456 to a numeric(6,2) column).

If such a conversion fails it generates a warning which is sent to the client callback (see ct_callback()). You can convert the warning to an error by returning CS_FAIL from the callback.

Returns CS_SUCCEED or CS_FAIL.

$ret = $dbh->blk_done($type, $rows)

Commit a batch of rows, or all rows sent with blk_rowxfer(). $type should be one of CS_BLK_ALL, CS_BLK_BATCH or CS_BLK_CANCEL. As a side effect sets $rows to the number of rows affected.

Returns CS_SUCCEED or CS_FAIL.

$dbh->blk_drop()

Free internal resources after a Bulk-copy operation has been performed.

The BLK API is much more picky about the data that it accepts and so it is more likely to get data conversion errors when the row is sent to the server via blk_rowxfer().

By default ALL data conversion errors (other than string truncation for char/varchar data) are flagged as failures, and the row will not get uploaded, with an error message written to STDERR.

This behavior can be modified by registering a CS_MESSAGE_CB callback, which will be called in the event of a data conversion error.

A typical CS_MESSAGE_CB subroutine might look like this:

  sub msg_cb {
    my ($layer, $origin, $severity, $number, $msg, $osmsg, $usermsg) = @_;

    print "$layer $origin $severity $number: $msg ($usermsg)\n";

    if($number == 36) {
      return CS_SUCCEED;
    }

    return CS_FAIL;
  }

and you would install it with a call to ct_callback():

  ct_callback(CS_MESSAGE_CB, \&msg_cb);

If the CS_MESSAGE_CB handler returns CS_SUCCEED then the conversion error is ignored by the Sybase::CTlib code, and the row is sent to the BLK API for processing. If the handler returns CS_FAIL then the row is skipped and blk_rowxfer() will return CS_FAIL as well. Obviously you can't force the BLK API to accept really bad data (such as "Feb 30 2000" for a date), so even if your CS_MESSAGE_CB handler returns CS_SUCCEED the row can still fail.

In my example above I decided to accept error number 36, which flags truncation/scale errors (e.g. trying to load a value of 123.456 to a numeric(6,2) column, which results in 123.45 being loaded.), and to fail all other conversion errors.

It should be noted that when numeric values are truncated due to overflow the values are truncated, not rounded (so 123.456 is stored as 123.45.)

The following routines allow you to make asynchronous calls to the server. The implementation is experimental, and might change in the future, so please use with care.

Before attempting to use this feature you should read the "Asynchronous Programming" chapter in the Sybase OpenClient - Client Library manuals to understand the concepts. Sybase::CTlib only provides a thin layer over the Sybase API.

The Sybase API knows about synchronous (the default), deferred IO and async IO modes. In the synchronous mode database requests block until a response is available. In deferred IO mode database requests return immediately (with a return code of CS_PENDING), and you check for completion with the ct_poll() call. In async IO mode the API uses a completion callback to notify the application of pending data for async operations.

A connection can be set to async or deferred IO modes when it is idle, via the ct_con_props() call:

	$dbh->ct_con_props(CS_SET, CS_NETIO, CS_DEFER_IO, CS_INT_TYPE);

Operations will now run in deferred mode, and the perl code will need to be adapted somewhat to handle the async behaviour. Specifically, ct_results() won't set $restype correctly - this value will only get set when ct_results() completes, which means that it has to be retrieved with an alternative method. The same is true for ct_fetch(), which can't be used in async or deferred modes.

Here is a simple (well, as simple as I can make it!) example of using the deferred IO mode:

   my $ret = $dbh->ct_con_props(CS_SET, CS_NETIO, CS_DEFER_IO, CS_INT_TYPE);

   $ret = $dbh->ct_execute("select * from large_table where val = 100");
   my $compid;
   my $compstatus;
   my $foo;

   $ret = $dbh->ct_poll(CS_NO_LIMIT, $foo, $compid, $compstatus);
   my $restype;
   while(($ret = $dbh->ct_results($restype)) == CS_SUCCEED || $ret == CS_PENDING) {
      if($ret == CS_PENDING) {
	 $ret = $dbh->ct_poll(CS_NO_LIMIT, $foo, $compid, $compstatus);
	 last if $compstatus == CS_END_RESULTS;
	 $restype = $dbh->{LastRestype};
      }
      next unless $dbh->ct_fetchable($restype);
      $ret = $dbh->as_describe($restype);
      while(($ret = $dbh->as_fetch) == CS_SUCCEED || $ret == CS_PENDING) {
	if($ret == CS_PENDING) {
	    $ret = $dbh->ct_poll(CS_NO_LIMIT, $foo, $compid, $compstatus);
	}
	my $d = $dbh->as_fetchrow;
	print "data: @$d\n";

	last if $compstatus == CS_END_DATA;
      }
   }

As you can see this snippet is quite a bit more complicated than the corresponding synchronous code would be. In this case we've used the CS_NO_LIMIT parameter to ct_poll(), meaning that ct_poll() will block until data is available (which of course defeats the purpose of running an async query, but serves to illustrate the API).

Note the use of $dbh->{LastRestype} to fetch the $restype value set by ct_results() after that call completes.

Also, note the use of $dbh->as_describe($restype). This is a call that is normally done internally in ct_results(), but which needs to be called separately in this case, and which allocates appropriate data structures internally for the columns being returned.

The ct_fetch() call is replaced by as_fetch(), moving the actual data fetch to as_fetchrow().

If you define a completion callback then this will also be called - with the $dbh, $func (set to a symbolic value for the operation that completed, and $status (the return code for the operation).

For example, when ct_results() completes $func will be set to CT_RESULTS and $status will be set to CS_SUCCEED, or CS_END_RESULTS if there are no more results to be processed.

Most Sybase::CTlib calls can be called from within the completion callback, if necessary.

To enable fully async mode, you need to have a completion callback registered, and you need to also enable async notifications, as well as setting up a timeout for requests, via ct_config(CS_TIMEOUT).

There are two very simple scripts in the eg/ directory (ctpoll.pl and ctasync.pl) which should illustrate the API somewhat.

Completion Callbacks

A completion callback is a perl subroutine that takes three arguments (a database handle, and two integers ($func, $status). The callback is enabled via the ct_callback() call:

    ct_callback(CS_COMPLETION_CB, \&my_completion_cb);

It is either invoked when ct_poll() is notified of a request completion, or by the OpenClient API directly in full async mode.

It should return CS_SUCCEED, or CS_PENDING if you've initiated a new async request from within the callback

$ret = $dbh->ct_con_props($action, $property, $value, $type)

You can set or retrieve connection properties with this call. In particular you set async mode like this:

	$dbh->ct_con_props(CS_SET, CS_NETIO, CS_ASYNC_IO, CS_INT_TYPE);

$ret = $dbh->ct_poll($milliseconds, $conn, $compid, $compstatus)

Check for completion of an operation. $milliseconds determines how long ct_poll should wait before timing out, where 0 means return immediately, and CS_NO_LIMIT means don't return until a pending operation has completed.

If the call is invoked as Sybase::CTlib->ct_poll() it will return the first connection that has a completed operation in the $conn variable. The $compid value is a symbolic value identifying the type of operation that has completed, and $compstatus is the return value of that operation.

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

This needs to be called before fetching rows on an async or deferred IO connection. This allocates the appropriate internal buffers for the data columns returned in the query.

$ret = $dbh->as_fetch

Performs an async fetch - does not actually return any row data.

$ret = $dbh->as_fetchrow([$doAssoc])

Returns a row of data after as_fetch() is marked as completed. This call returns a static array (or a static hash, if $doAssoc is set). So each call to this routine overwrites the data that you just fetched. If you need to save the data you must make a copy of the array.

    #!/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 Result Set\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 behavior of certain aspects of the Sybase::CTlib module can be controlled 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 behavior 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 character string. Default: FALSE.

UseNumeric

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

UseChar

This is a no-op. As of sybperl 2.16 NUMERIC, DECIMAL and MONEY data retrieved via ct_fetch() is stored as character strings by default to ensure no loss of precision.

UseBinary

If set then BINARY and IMAGE data items are returned in native form. The default is to have BINARY and IMAGE data returned as hex strings.

UseBin0x

If set, and if UseBinary is FALSE, then BINARY data fetched from the server will be converted to a hex string and prepended with 0x.

MaxRows

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

SkipEED

If set, then Extended Error Data will not be fetched in error handlers. The default is to fetch extended error data, which includes things like the index name that caused a duplicate insert error, for example.

As 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.

First let's 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);
	    }

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

Turns the debugging trace on or off. 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 - (i.e. 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.

* TRACE_CONVERT

Verbose tracing of calls to cs_convert().

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::CTlib::Att).

The Sybase::CTlib module includes 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 its 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.

NOTE: The minimal interval that Sybase understands is 1/300th of second, so amounts of less than 3 $msecs will NOT be visible.

($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 lose 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.

There is a (approximately) 300 byte memory leak in the newdbh() function in Sybase/CTlib.xs. This function is called when a new connection is created. 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://www.peppler.org/cgi-bin/bug.cgi . You can use it to check for known bugs, or to submit new ones.

You can also look for new versions/patches for sybperl in http://www.peppler.org/downloads.

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.

W. Phillip Moore <Phil.Moore@msdw.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@peppler.org> is the best place to ask questions.