Sybperl 2.0: Using the Sybase::CTlib module

Sybperl is the generic name for the Open Client extensions to Perl. Perl stands for Practical Extraction and Report Language. It is a shell-like scripting language, that borrows it's syntax from a large number of sources, including C, the Bourne shell, the C shell, sed and awk. Because of it's powerfull syntax and unified features, perl has become the language of choice for Unix system administrators, and it has also proved very popular for writing CGI scripts for WWW applications. Sybperl extends perl by adding a fairly large subset of the Open Client API, making it possible to write very powerfull scripts in a very short time. Sybperl was first released in 1991, and has gained a fairly wide acceptance among Sybase DBAs and programers.

Both perl and sybperl are available in source form, and are distributed under the GNU General Public License, or under the Perl Artistic License.

In early 1995, version 5.0 of perl was released, with many new features and enhacements. In particular, Perl 5.0 adds a new 'Object Oriented' paradigm to perl programing, with the ability of creating object hierchies via perl 'modules'. Sybperl version 2.0, released last November, takes advantage of these new features, and extends the range of Open Client API calls that are supported to include a subset of the Client Library (aka CTlib) API (Sybase's new web.sql product uses a version of this API.) This article will introduce Perl's interpretation of the CTlib API. I will assume that the reader has some familiarity both with Perl and with the Open Client products. Please see the end of the article for references on how to obtain perl and sybperl and for Perl documentation.

The prototypical Sybase::CTlib program looks like this:

#!/usr/local/bin/perl

# Load the Sybase::CTlib module:
use Sybase::CTlib;

# Allocate a new Database 'handle':
$dbh = new Sybase::CTlib 'user', 'password', 'SERVER';

# Send the query to th server:
$dbh->ct_execute("select uid, name from sysusers");

# Retrieve the result sets 
while($dbh->ct_results($restype) == CS_SUCCEED) {

    # Skip non-fetchable results:
    next unless $dbh->ct_fetchable($restype);

    # Retrieve actual data rows:
    while(($uid, $name) = $dbh->ct_fetch) {
        print "$uid - $name\n";
    }
}
# Output:
0 public
1 dbo
3 mpeppler

You will notice that with Perl 5.0, a special 'sybperl' binary is no longer necessary on most Unix platforms: Perl is capable of dynamically loading the libraries that are necessary at run time. Compared to a 'C' program doing the same thing, this Perl script is an order of magnitude simpler, while providing the same level of control and flexibility (including error handler callbacks). The object oriented features of Perl provide a lot of additional help, including automatic 'destruction' of 'objects' when they go out of scope. Thus it is usually not necessary to explicitly close a connection to the database.

Some Explanations:

The ct_execute() method takes a SQL statement and sends it to the server using the $dbh connection. The SQL statement can be a simple SELECT, or any combination of statements (ct_execute() is a combination of ct_command() and ct_send().) The ct_results() call retrieves each of the result sets that are generated by this query, and any data rows are fetched with ct_fetch(). Notice that ct_fetch() directly returns an array of values, one for each column in the result row - variable binding and column description is done automatically.

ct_fetch() can also return an associative array keyed on the column name. This feature is enabled by passing a TRUE flag to ct_fetch:

	$dbh->ct_execute("select uid, name from...");
	...
	%dat = $dbh->ct_fetch(CS_TRUE);
	if($dat{'uid'} == 10) {
	     ...
	}

This technique makes the code less sensitive to changes in the SQL submitted to the server (you don't have to worry if the uid column is column 1 or column 2 in the result set), and makes it easier to read, too.

One step queries:

	@rows = $dbh->ct_sql("select uid, name from...");

	foreach $row_ref (@rows) {
		($uid, $name) = ($$row_ref[0], $$row_ref[1]);
		...
	}

This one step call is very usefull for writing simple, clear code, but has one major drawback: the entire query result set is loaded into memory, which makes it unusable for processing large result sets.

Dates and Money:

($date) = $dbh->ct_fetch;

($year, $month, $mon_day, $year_day, $week_day,
$hour, $min, $sec, $msec, $zone) = $date->crack; # call cs_dt_crack()

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

$time_t = $date->mktime; # Convert the date to a Unix time_t value.

$money_result = $money1 / $money2;

Of course, nothing is free, and this native handling of dates and fixed precision types has a certain performance cost, which can in extreme cases add up to 75% processing time. This feature is therefore optional and can be selectively turned on for one or more of the data types, on a per-connection basis.

Advanced features:

DB handle Attributes:

$value = $dbh->{AttributeName};

Stored procedures, parameters and cursors:

    %param = (name => '@acc',
	      datatype => CS_CHAR_TYPE,
	      status => CS_RETURN,		# This is an OUTPUT param
	      value => 'CIS 98941',
	      indicator => CS_UNUSED);
    $dbh->ct_param(\%param);	# Pass a reference to the hash...

$dbh->ct_command(CS_RPC_CMD, "t_proc", CS_NULLTERM, CS_NO_RECOMPILE);

%param = (name => '@acc',
	  datatype => CS_CHAR_TYPE,
	  status => CS_INPUTVALUE,
	  value => 'CIS 98941' ,
	  indicator => CS_UNUSED);

$dbh->ct_param(\%param);

# Alternate technique: pass an anonymous hash...
$dbh->ct_param({name => '@date',
	        datatype => CS_DATETIME_TYPE,
	        status => CS_RETURN,
	        value => '950529' ,
	        indicator => CS_UNUSED});
$dbh->ct_send();
while($dbh->ct_results($restype) == CS_SUCCEED)
{
    next unless $dbh->ct_fetchable($restype);

    while(%dat = $dbh->ct_fetch(CS_TRUE))
    {
	foreach (keys(%dat)) {
	    print "$_: $dat{$_}\n";
	}
    }
}

If the above code seems a bit complicated, you can often get away with writing something like this:

	@rows = $dbh->ct_sql(<<'SQL_EOF');
	   declare @date datetime
	   select @date = '950529'
	   exec t_proc 'CIS 98941', @date out
	SQL_EOF

The @rows variable will contain:

• Any rows returned by SELECT statements within the stored procedure.
• The return status of the stored procedure.
• The OUTPUT parameter values.

As you can see, very few lines of code can produce amazing results!

Cursors are programmatically complex, and their use in Perl remains a bit arcane. The steps that are required to setup a cursor operation are the same as in C:

    $dbh->ct_cursor(CS_CURSOR_DECLARE, 'cursor_eg',
	            'select * from master.dbo.sysprocesses',
	            CS_READ_ONLY);
    $dbh->ct_cursor(CS_CURSOR_ROWS, undef, undef, 5);
    $dbh->ct_cursor(CS_CURSOR_OPEN, undef, undef, CS_UNUSED);
    $dbh->ct_send();
    while($d->ct_results($restype) == CS_SUCCEED) {
        ...
    }

• ct_cmd_alloc duplicates the current DB handle, and allocates a new CS_COMMAND buffer. This can be usefull when interleaved fetches from multiple cursors are required.
• ct_cmd_realloc drops and reallocates the current CS_COMMAND buffer. This is necessary to run multiple cursor commands sequentially.

Using Perl references:

sub load_table {
    my($dbh, $table, $key) = @_;
    my(%out, %dat, $restype);

    $dbh->ct_execute("select * from $table");
    while($dbh->ct_results($restype) == CS_SUCCEED) {
        next unless $dbh->ct_fetchable($restype);
        while(%dat = $dbh->ct_fetch(CS_TRUE)) {
             $out{$dat{$key}} = {%dat};
        }
    }
    return %out;
}

%sysusers = &load_table($dbh, 'sysusers', 'suid');

$name = $sysusers{3}->{'name'};
print "$name\n"; # Prints 'mpeppler'

    foreach $suid (keys(%sysusers)) {
        foreach $col (keys(%{$sysusers{$suid}})) {
	    print "$suid - $col: $sysusers{$suid}->{$col}\n";
        }
    }

prints:
1 - name: dbo
1 - environ: 
1 - uid: 1
1 - gid: 0
1 - suid: 1
3 - name: mpeppler
3 - environ: 
3 - uid: 3
3 - gid: 16384
3 - suid: 3
...

Why should I use CTlib...

Although I am not advocating converting all existing sybperl code to use the new CTlib module, I think that using it should be considered for new projects. Why? For one thing, Sybase has stated that although DBlibrary will be supported in the future, it will no longer be enhanced to support new functionality. Secondly, the Client Library programming model is very clean, and Sybperl's implementation of the API makes it really easy to use.

The Client Library syntax has been unified and simplyfied compared to DBlibrary, and the number of API calls has been greatly reduced: the Sybase::DBlib module implements 93 calls, the Sybase::CTlib module fewer than 20, with similar functionality. In addition, native handling of Dates and other special data types can make things a lot easier when writing scripts.

Missing features in the current version of the Sybase::CTlib module includes access to the bulk copy library (it is available in the Sybase::DBlib version), asynchronous programming and dynamic SQL. The bulk copy library will certainly be added in the near future.

Sybperl in general, and the Sybase::CTlib module in particular have to a certain extent been blessed by Sybase, and are becoming supported products - as previously noted, web.sql uses a version of the Sybase::CTlib module as it's underlying engine. Sybperl may not be for everybody as it requires understanding a number of sometimes complex programming issues, but it beats using shell scripts or writing custom C code hands down.

Where do I get sybperl?

Perl is available from the same source, in directory "CPAN"/src/5.0 The best Perl documentation are the 'Camel' and 'Llama' books (Programing Perl, Wall, Schwartz & Christiansen, O'Reilly, and Learning Perl, Schwartz, O'Reilly). There is a sybperl homepage at http://www.mbay.net/~mpeppler