NAME

Sybase::Simple - Utility module for Sybase::CTlib

SYNOPSIS

use Sybase::Simple;

$dbh = new Sybase::Simple $user, $pwd, $server;

$date = $dbh->Scalar("select getdate()");

DESCRIPTION

Sybase::Simple is a module built on top of Sybase::CTlib, and which provides some simplified access methods to get at the database's data.

The following methods are defined:

$dbh = new Sybase::Simple $user [, $pwd [, $server [, $appname [, \%attr]]]]

Open a new connection to the Sybase server $server, using $user and $pwd for authentication. Optionally set the application name (as shown in sysprocesses) to $appname. The optional %attr hash can be used to add attributes to the $dbh hash.

See the sybperl(3) man page for details.

$dbh->config(key => value [, key => value ...])

The behavior of Sybase::Simple can be modified by setting configuration values. Currently two config values are supported:

AbortOnError

If true, ExecSql() will abort and return 0 on the first failed command in the batch that it executes.

Default: false

DeadlockRetry

If true, ExecSql() will retry the entire batch if a deadlock error (error number 1205) is detected.

Default: false

$data = $dbh->Scalar($sql)

Execute the SQL in $sql, and take the first column of the first row and return it as a scalar value. Typical use might be

$val = $dbh->Scalar("select max(foo) from bar");

$data = $dbh->HashRow($sql)

Execute the SQL in $sql, and return the first row, in hash format:

$data = $dbh->HashRow("select * from sysusers where uid = 0");
if($data->{name} ne 'public') {
     print "Your sysusers table is strange!\n";
}

$data = $dbh->ArrayOfHash($sql)

Execute the SQL in $sql, and return an array of all the rows, each row begin stored in hash format. Similar to the Sybase::CTlib ct_sql() subroutine.

$data = $dbh->ArrayOfArray($sql)

Execute the SQL in $sql, and return an array of all the rows, each row begin stored in array format. Similar to the Sybase::CTlib ct_sql() subroutine.

$data = $dbh->HashOfScalar($sql, $key, $val)

Execute $sql, and return a hash where the key is based on the column $key in the result set, and the value is the $val column:

$data = $dbh->HashOfScalar("select uid, name from sysusers", 'uid', 'name');
if($data->{0} ne 'public') {
     print "Your sysusers table is strange!\n";
}

Rows where the $key column is NULL are ignored. No checking is made on the uniqueness of the $key column - if multiple rows have the same value in the $key column then the last row retrieved will be stored.

$data = $dbh->HashOfHash($sql, $key)

Same as HashOfScalar(), except that the entire row is stored as a hash.

$data = $dbh->HashOfHashOfHash($sql, $key1, $key2)

Same as HashOfHash(), except that it expects a two column primary key. So if you have a table with for example 'authorId' and 'bookId' as the primary key, you could do this:

my $data = $dbh->HashOfHashOfHash("select * from books", 'authorId', 'bookId')
my $book = $data->{1234}->{567};

Now $book is the row where authorId == 1234 and bookId == 567.

$iter = $dbh->HashIter($sql);

Executes $sql, and returns a Sybase::Simple::HashIter object. This can then be used to retrieve one row at a time. This is really useful for queries where the number of rows returned can be large.

$iter = $dbh->HashIter($sql);
while($data = $iter->next) {
    # do something with $data
}

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

Executes $sql and ignores any rows that the statement may return. This routine is useful for executing insert/update/delete statements, or stored procedures that perform those types of operation.

If $abortOnError is non-0 then ExecSql will abort on the first failed statement.

If verbose warnings are turned on (ie if the -w switch is passed to perl) then a warning is issued if rows are returned when executing $sql. In any case those rows are ignored.

The status return code of the executed stored procedure, if any, is available in $dbh->{SIMPLE}->{STATUS}.

The number of rows of the last statement executed by ExecSql is available in $dbh->{SIMPLE}->{ROW_COUNT}.

Returns 0 for any failure, non-0 otherwise.

Error Handling

This module adds a some error handling above what is normally found in Sybase::CTlib.

In particular you can check $dbh->lastErr and $dbh->lastErrText to see the last error associated with this database connection. There is also some optional deadlock retry logic in the ExecSql() call. This logic can certainly be extended.

AUTHOR

Michael Peppler, mpeppler@peppler.org

COPYRIGHT

Copyright (c) 1998-2001 Michael Peppler

You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

SEE ALSO

perl(1), sybperl(3), Sybase::CTlib(3)