$dbproc = &dblogin([$user[, $pwd[, $server]]])
$dbproc1 = &dbopen([$server])
&dbclose($dbproc)
$ret = &dbcmd($dbproc, $sql_cmd)
$ret = &dbsqlexec($dbproc)
$ret = &dbresults($dbproc)
@data = &dbnextrow($dbproc [, $doAssoc])
@data = &dbretdata($dbproc [, $doAssoc])
$ret = &dbuse($dbproc, $database)
$ret = &dbcancel($dbproc)
$ret = &dbcanquery($dbproc)
$ret = &dbexit($dbproc)
$string = &dbstrcpy($dbproc)
$string = &dbsafestr($dbproc,$instring[,$quote_char])
$status = &dbwritetext($dbproc_1, $col_name, $dbproc_2, $select_col, $text)
$count = &dbnumcol($dbproc)
$type = &dbcoltype($dbproc, $colid)
$name = &dbcolname($dbproc, $colid)
$len = &dbcollen($dbproc, $colid)
$status = &dbhasretstat($dbproc)
$status = &dbretstatus($dbproc)
$ret = &dberrhandle($handler)
$ret = &dbmsghandle($handler)
$ret = &DBCURCMD($dbproc)
$status = &DBMORECMD($dbproc)
$status = &DBCMDROW($dbproc)
$status = $DBROWS($dbproc)
$ret = $DBCOUNT($dbproc)
($status, $sum) = &dbmny4add($dbproc,$m1,$m2)
$status = &dbmny4cmp($dbproc,$m1,$m2)
($status, $quotient) = &dbmny4divide($dbproc,$m1,$m2)
($status, $dest) = &dbmny4minus($dbproc,$source)
($status, $product) = &dbmny4mul($dbproc,$m1,$m2)
($status, $difference) = &dbmny4sub($dbproc,$m1,$m2)
($status, $ret) = &dbmny4zero($dbproc)
($status, $sum) = &dbmnyadd($dbproc,$m1,$m2)
$status = &dbmnycmp($dbproc,$m1,$m2)
($status, $ret) = &dbmnydec($dbproc, $m1)
($status, $quotient) = &dbmnydivide($dbproc,$m1,$m2)
($status, $ret, $remainder) = &dbmnydown($dbproc,$m1, $divisor)
($status, $ret) = &dbmnyinc($dbproc, $m1)
($status, $ret, $remain) = &dbmnyinit($dbproc, $m1,$trim)
($status, $ret) = &dbmnymaxneg($dbproc)
($status, $ret) = &dbmnymaxpos($dbproc)
($status, $dest) = &dbmnyminus($dbproc,$source)
($status, $product) = &dbmnymul($dbproc,$m1,$m2)
($status, $m1, $digits, $remain) = &dbmnyndigit($dbproc,$m1)
($status, $ret) = &dbmnyscale($dbproc,$m1,$multiplier,
$addend)
($status, $difference) = &dbmnysub($dbproc,$m1,$m2)
($status, $ret) = &dbmnyzero($dbproc)
$status = &BCP_SETL($state)
$status = &bcp_getl;
$status = &bcp_init($dbproc, $tblname, $hostfile, $errfile, $dir)
$status = &bcp_meminit($dbproc, $numcols)
$status = &bcp_sendrow($dbproc, $col1, $col2, ...)
$status = &bcp_batch($dbproc)
$status = &bcp_done($dbproc)
$status = &bcp_control($dbproc, $field, $value)
$status = &bcp_columns($dbproc, $host_columns)
$status = &bcp_colfmt($dbproc, $host_column, $host_type,
$host_prefixlen, $host_collen, $host_term,
$host_termlen, $table_colnum)
($status, $rows_copied) = &bcp_exec($dbproc)
$status = &bcp_readfmt($dbproc, $filename)
$status = &bcp_writefmt($dbproc, $filename)
$SUCCEED $MORE_ROWS $EXCEPTION $EXPROGRAM
$FAIL $REG_ROW $EXSIGNAL $EXSERVER
$NO_MORE_ROWS $BUF_FULL $EXINFO $EXCOMM
$NO_MORE_RESULTS $NO_MORE_PARAMS $EXDBLIB $EXTIME
$ComputeId $DBSAVE $EXFORMS $EXFATAL
$DBstatus $DBNOSAVE $EXUSER
$SybperlVer $DBNOERR $EXLOOKUP
$STDEXIT $DB_PASSTHRU_MORE $EXSCREENIO
$ERREXIT $DB_PASSTHRU_EOM $EXCLIPBOARD
$INT_EXIT $DBNOPROC $EXNONFATAL
$INT_CONTINUE $EXCONVERSION
$INT_CANCEL $EXRESOURCE
$INT_TIMEOUT $EXCONSISTENCY
$DB_IN $DB_OUT
$BCPMAXERRS $BCPFIRST $BCPLAST $BCPBATCH
$DBTRUE $DBFALSE
$SybPackageBug
$dbNullIsUndef $dbKeepNumeric $dbBin0x
Sybperl maps a subset of the Sybase DB-Library API to Perl. The usage of these functions is the same as in DB-Library, unless specifically noted.
Differences with DB-Library:
&dblogin takes 3 optional arguements (the userid, the password and the server to connect to). These default to the Unix userid, the null password and the default server (from the DSQUERY environment variable).
&dblogin returns a DBPROCESS, not a LOGINREC. This simplifies the call to open a connection to a Sybase dataserver somewhat. If the login fails for any reason &dblogin returns -1. &dblogin can be called multiple times to login to different servers, or to login as several users simultaneously.
Further DBPROCESSes can be opened using &dbopen([$server]), using the login information from the last call to &dblogin(). The number of simultaneous DBPROCESSes is limited to 25 (This can be changed by altering a #define in sybperl.c).
The $dbproc parameter used by most subroutines is optional, and defaults to the DBPROCESS returned by the first call to &dblogin (exceptions: &dbsafestr(), &dbwritetext() and &bcp_sendrow() require explicit $dbproc parameters.)
&dbnextrow returns an array of formatted data, based on the datatype of the corresponding columns. &dbnextrow sets the variable $ComputeId when the result row is a computed row (the result of a compute by clause). If the optional $doAssoc parameter is non-zero &dbnextrow returns an associative array keyed on the column name of each returned field. If the column name is null (as for example in the case of an aggregate), then &dbnextrow assigns a column name based on the column number.
&dbretdata returns an array of the parameters declared as OUTput in an EXEC stored procedure statement. If the ooptional $doAssoc parameter is non-zero, then an associative array keyed on the name of the parameters is returned (again, if the parameters are unnamed, the key is based on the paramter number). A single call will return all the parameters for the last EXEC statement.
&dbsafestr takes a string literal ' or " as the third [optional] argument and means DBSINGLE or DBDOUBLE, respectively. Omission of the third argument means DBBOTH.
In order to simplify its use somewhat, the calling sequence of &dbwritetext has been changed. $select_proc and $select_col are the dbproc and column number of a currently active query. Logging is always off.
Note that all DBMONEY routines which in the C version take pointers to arguments (in order to return values) return these values in an array instead (eg: status = dbmnyadd(dbproc, m1, m2, result) becomes ($status, $result) = &dbmnyadd($dbproc, $m1, $m2))
Copying data from program variables into a Sybase table using BCP has been implemented in a slightly different manner. Instead of using bcp_bind(), you need to call &bcp_meminit() to determine the number of columns that will be sent to the server, and the call &bcp_sendrow() with the data for each row (see EXAMPLES, below). Passing undef as one of the data values will result in a NULL value being sent to the server for that column.
Variables:
Sybperl defines a number of Read-Only variables, and three Read-Write variables. Most of the variables correspond to #define's in the OpenClient include files (see the Sybase documentation for more information).
The Sybperl specific variables are:
$ComputeId - Set by &dbnextrow when it processes a
compute row as opposed to a normal results row.
$DBstatus - The status returned by the last call to
dbnextrow().
$SybperlVer - The Sybperl release version.
$SybPackageBug - Set to TRUE if Sybperl was compiled with
the option to circumvent a bug in Perl's implementation of
packages. This variable is undefined otherwise.
$dbNullIsUndef - This variable controls whether NULL values
returned from a query will be returned as the string 'NULL' (the
default) or as the Perl undef value.
$dbKeepNumeric - This variable controls whether numeric
datatypes returned by queries are converted to strings (the default)
or left in native format.
$dbBin0x - This variable controls whether variables of type
SYBBINARY are returned with a leading 0x or not (the
default).
These last three variables are all boolean.
&dbfcmd is not implemented, but can be emulated by using sprintf as in &dbcmd($dbproc, sprintf("%d", $num_val));
Using &dbretdata():
&dbcmd($dbproc, "declare @data int);
&dbcmd($dbproc, "exec my_stored_proc @data out);
&dbsqlexec($dbproc);
&dbresults($dbproc);
while(&dbnextrow($dbproc))
{
; # empty loop...
}
($ret) = &dbretdata($dbproc);
Doing a Bulk Copy from program variables into a Sybase table:
&BCP_SETL($DBTRUE);
$dbproc = &dbopen;
&bcp_init($dbproc, "test.dbo.t2", '', 'bcp.err', $DB_IN);
&bcp_meminit($dbproc, 3); # we wish to copy three columns into
# the 't2' table
while(<>)
{
chop;
@dat = split(' ', $_);
&bcp_sendrow($dbproc, @dat);
}
$ret = &bcp_done($dbproc);
See the Perl(1) manual page.
Memory usage can become very large in certain conditions when using a version of Perl prior to 4.035. This can be circumvented - see the BUGS file in the Sybperl distribution.
If &dbnextrow encounters a datatype that it does not know about, it tries to convert it to SYBCHAR, and to store it in a 256 byte buffer - without checking for overflow.
The handling of multiple logins isn't really clean. A call to &dblogin sets the values for the User name and Password. These values are remembered - and used in calls to &dbopen - until they are changed in a new call to &dblogin(). It is possible to avoid the use of &dbopen alltogether, and simply call &dblogin each time a new DBPROCESS is required.
It is not possible to call &BCP_SETL for the first DBPROCESS. You have to call &dblogin, then &BCP_SETL, then &dbopen to get a DBPROCESS with BCP_IN enabled.
$PERLLIB/sybperl.pl should be called in all sybperl scripts to set the correct environment variables used by DB-Library. A sample $PERLLIB/sybdb.ph is provided with sybperl. You may want to use h2ph to add definitions to this file.
Perl(1L), Sybase Open Client DB Library Reference Manual, h2ph(1L).
Michael Peppler, ITF Management SA - mpeppler@itf.chBrent Milnor (brent@oceania.com) contributed &dbwritetext(). Eric Fifer (egf@sbi.com) contributed corrections to the &dblogin()/&dbopen() sequence. Mark Lawrence (mark@drd.com) contributed &dbsafestr(). Michael Bloom (mb@tti.com) contributed code to handle SYBIMAGE data. Don Preuss (donp@niaid.nih.gov) contributed the &dbcolXXX() calls. Jeffrey Wong (jtw@comdyn.cdsyd.oz.au) contributed the OpenClient R4.6.1 DBMONEY routines