NAME

MSSQL::DBlib - Call DB-Library for Microsoft SQL Server from Perl

SYNOPSIS

use MSSQL::DBlib;
use MSSQL::DBlib::Const;
or
use MSSQL::DBlib;
use MSSQL::DBlib::Const::General;
use MSSQL::DBlib::Const::Datatypes;
etc

DESCRIPTION

MSSQL::DBlib is an extension module to Perl (5.003 or higher) for calling Microsoft SQL Server through DB-Library. It is derived from Michael Peppler's Sybperl. See the section "Compatibility with Sybperl" if you are planning to use Sybperl scripts with MSSQL::DBlib.

Several DB-Library calls use constants, for parameters or return values. You import these from the MSSQL::DBlib::Const::* modules. Either you use the modules which hold the constants you need, or you use MSSQL::DBlib::Const which gives you all of them. See the section "MSSQL::DBlib::Const" below.

MSSQL::DBlib has an heir module, MSSQL::Sqllib which reduces sending queries to SQL Server to one call, including error handling. See further mssql-sqllib.

CAVEAT Use of DB-Library is deprecated by Microsoft, and you do not get full support for all datatypes and other features in SQL 7 and SQL 2000 with DB-Library, and thus not with MSSQL::DBlib. Thus you should probably not use MSSQL::DBlib for new development.

As far as the restrictions apply directly to MSSQL::DBlib, I discuss them below. There is also a summary on mssql-sqllib. For a full discussion, see SQL Server Books Online.

MSSQL::DBlib

A simple Perl script using MSSQL::DBlib could look like this:

use MSSQL::DBlib;
use MSSQL::DBlib::Const::General;

$dbh = MSSQL::DBlib->dblogin('sa', $pwd, $server, 'test_app');
$dbh->dbcmd("SELECT * FROM sysprocesses");
$dbh->dbsqlexec;
while ($dbh->dbresults != NO_MORE_RESULTS) {
   while ($dbh->dbnextrow2($dataref) != NO_MORE_ROWS) {
      ... do something with @$dataref ...
   }
}

Most of the routines in MSSQL::DBlib is accessed through a reference, and are therefore not exported. Only the routines in the subsection "Exported Routines" are exported.

Many functions in MSSQL::DBlib return a status value. For most such routines the only possible status values are SUCCEED and FAIL. When this is the case, I typically do not discuss on return-status values, but only in the cases where there are more values available. Constants for the return values are declared in MSSQL::DBlib::Const::General.

This document describes the routines as they appear from Perl. In many cases, they are simply passed on to DB-Library and the description on such routines are often brief in this document. Please refer to the DB-Library for C in Books Online or in the printed documentation for complete information on each routine.

Standard Routines

dblogin dbopen

$dbh = MSSQL::DBlib->dblogin([$user [, $pwd [, $server [, $appname ]]]])
$dbh = MSSQL::DBlib->dbopen([$server [, $appname ]])

Initiates a connection to SQL Server, using the supplied user, password, server and application name and returns a blessed reference to a hash, that includes a DBPROCESS as well as other attributes. In this document I mainly refer to this reference as a handle. The user-accessible attributes are described in the section "Handle Attributes".

You can also set user, password and application name in advance with the "DBSETLUSER", "DBSETLPWD" and "DBSETLAPP" calls and leave these out when calling "dblogin" or "dbopen". You cannot specify a host name with "dblogin", however you can set it with "DBSETLHOST" prior to calling "dblogin". By default, MSSQL::DBlib sets the hostname to value of the environment variable COMPUTERNAME.

If you don't provide a server name, DB-Library will apply its defaults. $server may refer to a named instance, for instance MYMACHINE\SECOND. Note: Named instances were introduced in SQL2000, and according to Books Online, named instances are not supported with DB-Library. However, it appears that Microsoft did actually change DB-Library to support named instances. If you get problems, you can always use the Client Network Utility to set up an alias for the named instance.

To use Windows NT authentication (known as "Integrated Security" in SQL 6.5), call "DBSETLSECURE" before calling "dblogin" or "dbopen". In this case SQL Server will ignore $user and $pwd.

"dblogin" is somewhat confusing if you are acquainted with DB-Library for C. To wit, the real call to the DB-Library namesake is in fact performed when MSSQL::DBlib is bootstrapped, and "dblogin" is really a call to DB-Library's dbopen and the sole difference between MSSQL::DBlib's "dblogin" and "dbopen", is that the latter takes fewer parameters.

dbclose

$dbh->dbclose

Close a connection while leaving the handle defined. Connections are automatically closed when a handle goes out of scope, so there is no urgent need to call this routine.

dbdead DBDEAD

$is_dead = $dbh->dbdead
$is_dead = $dbh->DBDEAD

Returns TRUE if the DBPROCESS has been marked dead by DB-Library. (That is, your connection to the server is lost.)

dbuse

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

Make $database the current database.

dbcmd

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

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

dbfreebuf

$dbh->dbfreebuf

Free the command buffer. Only needed if you have turned on the DBNOAUTOFREE option.

dbsqlexec

$status = $dbh->dbsqlexec

Sends the content of the current command buffer to SQL Server for execution. "dbsqlexec" waits until it has received a response from the server, or a timeout elapses. If this is not feasable, use "dbsqlsend" followed by "dbsqlok" instead, and check whether a response is available with "dbdataready".

dbsqlsend

$status = $dbh->dbsqlsend

Sends the content of the current command buffer to SQL Server, but in difference to "dbsqlexec", "dbsqlsend" does not wait for response from the server. You must call "dbsqlok" to verify that the execution was successful, before you can start to process the result sets. To check whether there is any server response available, use "dbdataready".

dbsqlok

$status = $dbh->sqlok

Retrieves the result of a batch previously sent with "dbsqlsend" or "dbrpcsend"(0). If no server response is available, "dbsqlok" waits until the server responds, or a timeout elapses.

dbdataready

$boolean = $dbh->dataready

Checks whether a response to a previous call to "dbsqlsend" or "dbrpcsend"(0) is available, and returns a true value if so, else a false value is returned.

The DB-Library documentation is somewhat inconsistent, but it appears that it can be meaningful calling "dbdataready" before calls to "dbresults" and "dbnextrow2" as well, as these function also waits until the server has responded.

Note that if there is a lock condition in SQL Server, "dbdataready" will return a false value until the lock condition is resolved. If you use this function, you should probably have some timeout mechanism.

dbresults

$status = $dbh->dbresults

Makes the next result set of the query the current result set, from which "dbnextrow2" will fetch rows. Call "dbresults" after you have called "dbsqlexec", "dbsqlsend"/"dbsqlok" or "dbrpcsend".

"dbresults" can return any of these four values:

SUCCEED: result set was fetched successfully.

FAIL: something went wrong. Note that you must continue to call dbresults until it returns NO_MORE_RESULTS, or cancel the batch with "dbcancel".

NO_MORE_RESULTS: there are no further results sets to process. When processing a query batch, you must call "dbresults" until it returns NO_MORE_RESULTS (or you cancel the batch with "dbcancel").

NO_MORE_RPC_RESULTS: there are no more results of this stored procedure, but there were more stored procedures in the batch, of which the result sets have not been processed. Applies only when you call stored procedures with "dbrpcparam"/"dbrpcsend".

dbnextrow

@data    = $dbh->dbnextrow([0])
%data    = $dbh->dbnextrow(1)
$dataref = $dbh->dbnextrow($use_hash, 1)

Get the next row from the current result set. If the second parameter $wantref has a false value, the return value is a list of scalar values. If the first parameter $use_hash has a true value, the names of the columns are included in the list, so that you can receive the data into a hash.

When $wantref has a true value, a reference to an array or a hash is returned, depending on $use_hash.

The return status from the DB-Library function dbnextrow is available in $dbh->{DBstatus}.

This function is deprecated and provided for compatibility only. It is implemented on top of "dbnextrow2", which is the preferred routine. See this routine for further details.

dbnextrow2

$status = $dbh->dbnextrow2($dataref[, $use_hash])

Get the next row from the current result set and return the status from the DB-Library function dbnextrow. On a successful fetch, the data in the row is available in $dataref. When a fetch fails (see possible status values below), $dataref will be undef.

$dataref is a reference to an array (when $use_hash has a false value or is omitted) or a hash (when $use_hash has a true value). When returning an array reference, the columns appear in the array, in the same order as they appear in the query.

When returning hash reference, the keys of the hash are the names of the columns in the result set. If there are unnamed columns, "dbnextrow2" will name them "Col n", where n is the column number in the result set, starting on 1. Beware! If two columns in the result set have the same name, obviously there is only room for one of them in the hash. If warnings are activated, "dbnextrow2" will emit a warning if a name collision is detected.

"dbnextrow2" can return the following return values:

REG_ROW: A regular result row was returned.

Any positive value: The row was a COMPUTE row, and the return value is the compute id for that row. It appears that then when there are more than one COMPUTE BY clauses, that the innermost COMPUTE BY clause has number 1. The compute id is also available in the handle attribute ComputeID.

FAIL: Something went wrong. Note that you must continue to call "dbnextrow2" until it returns NO_MORE_ROWS, unless you cancel the query with "dbcanquery".

NO_MORE_ROWS: There were no more rows to fetch. You must call "dbnextrow2" until you have fetched all row in the result (or cancel the query with "dbcanquery").

BUF_FULL: The row buffer is full, and you must clear the buffer with "dbclrbuf". This return value can only occur if you have set the DB-Library option DBBUFFER with "dbsetopt". See further the DB-Library documentation.

An example with "dbnextrow2" returning a hash:

$dbh->dbcmd("SELECT Name = name, Id = id FROM test_table");
$dbh->dbsqlexec;
$dbh->dbresults;

while ($dbh->dbnextrow($dataref, 1) != NO_MORE_ROWS) {
   print "$$dataref{Name} : $$dataref{Id}\n";
}

This is how "dbnextrow2" return the different datatypes:

NULL: undef or the string "NULL", depending on the handle attribute dbNullIsUndef. The default is to return NULL as undef.

int, smallint, tinyint, bit: As integers (default) or strings depending on the handle attribute dbKeepNumeric. Bit columns with the value NULL, are returned as 0. (Nullable bit columns were an addition with SQL Server 7 and are not supported by DB-Library.)

datetime, smalldatetime: As strings, formatted either by DB-Library, or according to the handle attributes dateFormat and msecFormat. See these attributes for details.

bigint, float, real, numeric, decimal, money, smallmoney: As float values (default) or strings, depending on the handle attribute dbKeepNumeric.

char, varchar, nchar, nvarchar, sql_variant: As strings. Values longer than 255 chars (a novelty in SQL 7) are truncated to 255 characters due to limitations in DB-Library. Varchar and nvarchar values of the empty string, will be received as a string of one space by DB-Library. In nchar and nvarchar strings, characters with codes beyond the range of 0-255 are converted to some fallback character by SQL Server. sql_variant (introduced in SQL 2000) are always returned as char values, no matter the actual base type.

text: As strings. Truncation occurs if the length exceed what has been specified with the SQL command SET TEXTSIZE or any of the DB-Library options DBTEXTSIZE and DBTEXTLIMIT.

ntext: This datatype is not supported at all with DB-Library, an attempt to fetch such a column, will result in an error message from SQL Server.

binary, varbinary, timestamp: As a hex strings, with or without a leading 0x, depending on the handle attribute dbBin0x.

uniqueidentifier: Returned as a hex string, with or wihout a leading 0x. The regular formatting for a GUID is missing, and several bytes are tossed around. You can use the utility function "reformat_uniqueid" to convert it to a proper GUID string.

image: As a binary value, thus not a hex string. Same truncation rules as for the text datatype.

dbcancel

$status = $dbh->dbcancel

Cancels the current command batch, discarding all results sets.

dbcanquery

$status = $dbh->dbcanquery

Cancels the current query within the currently executing command batch. There may still be further result sets to retrieve.

dbclrbuf

$dbh->dbclrbuf($n)

Clears $n rows from the row buffer. Row buffering only applies if you have set DB-Library option DBBBUFFER with "dbsetopt". Note that this function does not return any value.

DBCURCMD

$status = $dbh->DBCURCMD

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

DBMORECMDS

$status = $dbh->DBMORECMDS

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

DBCMDROW

$status = $dbh->DBCMDROW

Returns SUCCEED if the current command can return rows.

DBROWS

$status = $dbh->DBROWS

Returns SUCCEED if the current command did return rows.

DBCOUNT, dbcount

$count = $dbh->DBCOUNT
$count = $dbh->dbcount

Returns the number of rows that the current command affected. Notice that for a SELECT statement you will not get a correct answer from "DBCOUNT" until you have retrieved all rows with "dbnextrow2".

You can check with "dbiscount" whether the value you get is "real". (Yeah this is what the DB-Library documentation says.)

dbiscount

$isreal = $dbh->dbiscount

Returns whether the number by "DBCOUNT, dbcount" is "real" or not.

DBCURROW

$row_num = $dbh->DBCURROW

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

dbnumcols

$count = $dbh->dbnumcols

How many columns there are in the current result set.

dbcoltype

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

Returns the column type of column $colid in the current result set. Constants for the datatypes are declared in MSSQL::DBlib::Const::Datatypes.

dbcollen

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

Returns the maximum length (in bytes) of column $colid in the current result set.

dbcolname

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

Returns the name of column $colid in the current result set.

dbstrcpy

$string = $dbh->dbstrcpy [($start [, $numbytes])]

Returns the contents of the command buffer. $start is the position to start copying from. Default is 0, the first character in the buffer. When negative, $numbytes asks for the entire buffer to be returned. When >= 0, at most $numbytes characters are returned. Default is -1, return everything.

dbsetopt

$status = $dbh->dbsetopt($opt [, $char_val])

Sets option $opt with optional character parameter $char_val. $opt is one of the option values defined in the DB-Library manual (e.g. DBSTAT, DBTEXTSIZE). To get IO statistics from the server on, you would say:

$dbh->dbsetopt(DBSTAT, "io");
$dbh->dbsqlexec;
while ($dbh->dbresults != NO_MORE_RESULTS) {}

There are two kinds of options: SQL Server options (that have corresponding SET commands) and DB-Library options (that have no corresponding T-SQL commands at all.)

For the SQL Server options "dbsetopt" does not itself send the SET command to SQL Server, you must use "dbsqlexec" and "dbresults" just like after sending a command before the SET command to take effect, as shown in the example above.

But for DB-Library options, you must not use the sequence with "dbsqlexec" and "dbresults", because since no command buffer has been initiated, "dbresults" will never return NO_MORE_RESULTS, but appears to return SUCCEED and then FAIL.

The following options are DB-Library options: DBANSItoOEM, DBOEMtoANSI, DBBUFFER, DBNOAUTOFREE, and DBTEXTLIMIT. (Books Online fails to define the first two as DB-Library options.)

See also "dbclropt" and "dbisopt". See eq\statistics.pl for an example of using "dbsetopt".

Constants for options are declared in MSSQL::DBlib::Const::Options.

dbclropt

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

Unsets the option $opt, which might be on by default, or previously have been set with "dbsetopt".

See "dbsetopt" for when and when not calling "dbsqlexec" and "dbresults" after calling "dbsetopt".

dbisopt

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

Returns a true value if the option $opt is set.

dbgetpacket

$size = $dbh->dbgetpacket()

Returns the current packet size of the connection. You can set the packet size prior to opening a connection with "dbsetlpacket".

dbgetoff

$offset = $dbh->dbgetoff($offtype, $startfrom)

"dbgetoff" returns offsets to T-SQL statements in the command buffer. $offtype is a constant telling what you want offsets to. These constants are defined in MSSQL::DBlib::Const::Offset. $startfrom is where to start searching in the command buffer. Typically you would set $startfrom to the last returned $offset + 1 for the next search.

To get offset information, you need to set the option DBOFFSET, please refer to Books Online for this.

Note: I've played with this function both in SQL 6.5 and SQL 7 to get offsets to T-SQL statements and the results I got were neither useful nor consistent. Use at your own risk.

Exported Routines

These are functions that has no connection with any DBPROCESS handle, but are exported from MSSQL::DBlib. All but reformat_uniqueid are exported by default.

dberrhandle

$old_handler = dberrhandle($err_handler)

Installs an error handler and returns the previously installed handler. This handler is called when an error occurs in DB-Library itself. To handle errors from SQL Server, see "dbmsghandle".

$err_handler should be a reference to a Perl sub, or a string containing the name of a Perl sub. In the latter case, specify the name with the package component, for instance main::my_error_handler. If you specify an unqualified name, and warnings are activated, "dberrhandle" will print a warning. Unqualified name are deceivable, and does for instance not work when you are using the debugger.

Pass undef to only uninstall the current handler.

NOTE: MSSQL::DBlib provides a built-in default error handler which applies if you have not called "dberrhandle" or uninstalled your own handler by passing undef.

The error handler you specify should take six parameters:

$dbproc: This is a reference to a hash. The reference may or may not be blessed. When $dbproc is blessed, it is the handle you used for calling the routine that caused the error. When unblessed it is just a temporary handle, containing nothing but a DBPROCESS reference. Thus, if you want to retrieve any of the handle attributes, you first need to examine ref $dbproc. Don't attempt to call any DB-Library routines from the error handler, as they may lead to futher errors with new calls to the error handler and...

$severity: the severity level of the error. Constants for DB-Library severities are declared in MSSQL::DBlib::Const::Severity.

$dberr: a number identifying the error. Constants for DB-Library errors are declared in MSSQL::DBlib::Const::Errors.

$oserr: a number identifying any error from the OS or network level that may have occurred. When there is no such error, $oserr is DBNOERR, which is declared in MSSQL::DBlib::Const::General.

$dberrstr: a string describing the DB-Library error.

$oserr: a string describing any OS or network error.

The error handler should return any of the values INT_EXIT (which causes DB-Library to exit and return an error status to the OS), INT_CANCEL (which causes the current DB-Library call to return FAIL) or INT_CONTINUE (which is only meaningful for timeout errors). These constants are declared in MSSQL::DBlib::Const::General.

dbmsghandle

$old_handler = dbmsghandle($msg_handler)

Installs a message handler to take care of messages from SQL Server and returns the old handler. These messages can arise from server errors, RAISERROR or PRINT statements or be output caused by SET SHOWPLAN or SET STATISTICS.

$msg_handler should be a reference to a Perl sub, or a string containing the name of a Perl sub. In the latter case, specify the name with the package component, for instance main::my_message_handler. If you specify an unqualified name, and warnings are activated, "dberrhandle" will print a warning. Unqualified name are deceivable, and does for instance not work when you are running the debugger.

Pass undef to uninstall the current handler.

NOTE: MSSQL::DBlib provides a built-in default message handler which applies if you have not called "dbmsghandle" or uninstalled your own handler by passing undef.

The message handler you specify should take eight parameters:

$dbproc: This is a reference to a hash. The reference may or may not be blessed. When $dbproc is blessed, it is the handle you used to call the routine that caused the error. When unblessed it is just a temporary handle, containing nothing but a DBPROCESS reference. Thus, if you want to retrieve any of the handle attributes, you first need to examine ref $dbproc. Only when $dbproc is blessed you can call other MSSQL::DBlib routines, however you should be very restrictive with this, as stated in the DB-Library documentation.

$msgno: the error number from SQL Server. 0 when the message comes from a PRINT statement in Transact-SQL.

$msgstate: the state associated with the SQL Server message.

$severity: the severity level of the message. See the SQL Server documentation for details.

$msgtext: the text of the message from SQL Server.

$server: the name of the server that sent the message.

$procname: the name of the stored procedure that generated the message. undef if there was no procedure involved.

$line: the line number in the stored procedure or batch where the message was generated.

The message handler should return 0.

See eg\statistics.pl for an example of using a message handler that catches SHOW STATISTICS output.

dbprtype

$str = dbprtype($typeid)

Returns the textual representation of an SQL Server datatype such as SQLCHAR. These are declared in MSSQL::DBlib::Const::Datatypes. You can retrieve the datatype of a column with "dbcoltype".

DBSETLAPP

$status = DBSETLAPP($appname)

Sets the application name for connections created with subsequent calls to "dblogin" or "dbopen". This name will appear with for instance sp_who. The name provided with "DBSETLAPP", can be overridden by an explicit parameter to "dblogin" or "dbopen".

DBSETLHOST

$status = DBSETLHOST($hostname)

Sets the host name for connections created with subsequent calls to "dblogin" or "dbopen". This name will appear with for instance sp_who.

DBSETLFALLBACK

$status = DBSETLFALLBACK($onoff)

Turns fallback support on or off for subsequent calls to "dblogin" or "dbopen". $onoff should be any of the strings "ON" or "OFF". See Books Online for further details on fallback.

DBSETLNATLANG

$status = DBSETLNATLANG($language)

Sets the national language for connections created with subsequent calls to "dblogin" or "dbopen". Preferrably, the language should be installed on the SQL Server you are connecting to.

DBSETLPACKET

$status = DBSETLPACKET($packet_size)

Sets the packet size for the TDS protocol for connections created with subsequent calls to "dblogin" or "dbopen".

If you do not set the packet size, the default is determined by the server-configuration parameter "network packet size", which is 4096 in its default configuration. However, on SQL7 SP3 and SQL 2000 RTM and SP1 there is a bug (see KB article Q308016), which causes this parameter to be non-functional, giving a default of 512, which usually gives performance less than optimal.

DBSETLPWD

$status = DBSETLPWD($pwd)

Sets the password for subsequent calls to "dblogin" or "dbopen". This parameter can be overridden by an explicit parameter to "dblogin".

DBSETLSECURE

$status = DBSETLSECURE()

Sets the secure connection flag for subsequent calls to "dblogin" or "dbopen". This means that SQL Server will use integrated security for authentication, and that any provided username and password will be ignored.

DBSETLTIME

$status = DBSETLTIME($seconds)

Sets the time in seconds for how long MSSQL::DBlib is to wait when connecting to the server with "dblogin" or "dbopen". $seconds can also be any of the constants described in the DB-Library documentation. These are declared in MSSQL::DBlib::Const::Timeout.

DBSETLUSER

DBSETLUSER($user)

Sets the username for subsequent calls to "dblogin" or "dbopen". This user name can be overridden by an explicit parameter to "dblogin".

DBSETLVERSION

$status = DBSETLVERSION($version)

Sets the DB-Library client behaviour to version 4.2 or 6.0 for connections created with subsequent calls to "dblogin" or "dbopen". Use the constants DBVER60 or DBVER42. These are declared in MSSQL::DBlib::Const::DBSETLNAME.

The choise of version applies to issues of which none concerns users of MSSQL::DBlib: 1) the function dbcolinfo which is not available in MSSQL::DBlib. 2) whether the datatypes decimal and numeric are converted to float or not when returned to the client. MSSQL::DBlib converts numeric values to float itself, so DB-Library's behaviour does not matter.

dbsetmaxprocs

$ret = dbsetmaxprocs($maxprocs)

Sets the maximum number of connections you can have open simultaneously in your script. The default is 25.

dbgetmaxprocs

$maxprocs = dbgetmaxprocs()

Returns the maximum number of connections you can have open simultaneously in your script. You can set this value with "dbsetmaxprocs".

DBGETTIME

$time = DBGETTIME()

Returns the time MSSQL::DBlib waits for response from SQL Server. The timeout may be altered with "DBSETLTIME" or "dbsetlogintime".

dbsettime

$status = dbsettime($seconds)

Sets the time MSSQL::DBlib is to wait for response from SQL Server when calling "dbsqlexec", "dbnextrow", "dbresults", "dbnextrow2" or "dbrpcsend". To set the timeout for "dblogin", use "dbsetlogintime" or "DBSETLTIME".

dbsetlogintime

$status = dbsetlogintime($seconds)

With MSSQL::DBlib this routine is equvivalent to "DBSETLTIME", see this routine.

dbexit

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.

NOTE: do not call this routine, unless you have previously called "dbclose" for all your handles that still are in scope. Else you will get an error message from DB-Library when the handles eventually goes out of scope, for instance when Perl exits.

NOTE: in the consequence with the above note, do not call dbexit at all from code that is being called by ActiveState's PerlScript or something else which keeps the DLL for MSSQL::DBlib loaded.

reformat_uniqueid

$GUID = MSSQL::DBlib::reformat_uniqueid($hexstring)

Converts $hexstring which supposedly have been returned by "dbnextrow2" as the value for a column of the type uniqueidentifier to the regular presentation form for a GUID. If $hexstring is already on this format, $hexstring is returned unchanged. If $hexstring is not a 32 character long hexstring (or 34 with a leading 0x), "reformat_uniqueid" returns undef.

This routine is not exported by default, so you either have to mention it explicitly in your use statment, or give a full qualification as shown above.

BCP Routines

Constants for BCP are in MSSQL::DBlib::Const::BCP.

There are two ways you can bulk copy data into the database: from variables and from file. To copy data out of the database there is only one way: to file.

When you copy from variables you use the routines "bcp_init", "bcp_meminit", "bcp_sendrow", "bcp_batch" and "bcp_done". MSSQL::DBlib has here simplified the sequence you would use in a C program.

When you copy from/to a file, you use the routines "bcp_init", "bcp_columns", "bcp_colfmt" and "bcp_exec". Optionally you can change parameters with "bcp_control". This calling sequence is equivavelent to the one in C.

Note: Assume that you are restricted to use 6.5 datatypes. If this is not sufficient use the BCP that comes with SQL7 and SQL2000, the BULK INSERT command or the BCP API of ODBC or OLE DB.

BCP_SETL

$status = BCP_SETL($state)

If you wish to use the BCP routines for a connection, you must call BCP_SETL with $state = 1 prior to creating that connection with "dblogin" or "dbopen".

This is an exported routine, thus you don't need any handle or package reference to call it.

bcp_init

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

Initializes a bulk-copy operation. $table is the table you are bulking into or out from. $datafile is a file that the data is read from or written to. Specify undef if you are sending data from variables with "bcp_sendrow". $errfile is a file for BCP to write error messages to. $direction must be one of DB_OUT or DB_IN.

bcp_meminit

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

This is a routine which is particular to MSSQL::DBlib and not available in DB-Library itself. It defines the number of columns to use for a bulk copy from program variables to SQL Server. It must be called prior to calling "bcp_sendrow".

See eg\bcp.pl for a simple example.

bcp_sendrow

$status = $dbh->bcp_sendrow(@list)

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

Note that while data is sent to the server it is not actually committed until you call "bcp_batch" or "bcp_done".

You must call "bcp_meminit" prior to calling "bcp_sendrow".

bcp_batch

$rows_copied = $dbh->bcp_batch

Commits what has been sent to the server in the previous calls to "bcp_sendrow". The return value is the number of rows copied.

If you never call "bcp_batch", all rows will be committed when you call "bcp_done".

bcp_done

$rows_copied = $dbh->bcp_done

Ends a bulk copy from Perl variables into SQL Server with "bcp_sendrow" and returns the number of rows copied in the last batch.

bcp_control

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

Changes the default settings for control parameters for a bulk copy between a file and SQL Server. The available values for $field are listed below. Note that for all parameters, specifying 0 for $value will give you the default value.

BCPMAXERRS The number of errors allowed before giving up. The default is 10. The maximum limit is 65.535.

BCPFIRST The first row to copy. The default is 1.

BCPLAST The last row to copy. By default all rows are copied.

BCPBATCH The number of rows per batch. The default is 0, that is all is sent in one batch.

BCPKEEPNULLS When set, empty data values in the file will be converted to NULL values in the SQL Server table. When unset, empty values will be converted to the default value for the column. By default the option is not set.

bcp_columns

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

Sets the total number of columns found in the user file for use with a bulk copy into/out of SQL Server.

bcp_colfmt

$status = $dbh->bcp_colfmt($file_colno, $file_datatype, $file_prefixlen,
                           $file_collen, $col_terminator, $termlen,
                           $table_col)

Specifies the format of a user file for bulk copy into/out of SQL Server. What follows is my attempt to clarify the meaning of these parameters. For a complete reference please see the, ehum somewhat obscure, documentation for DB-Library.

$file_colno is the number of the column in your data file. The first column is number 1.

$file_datatype is the datatype for the column in the data file. That is, if you have written a integer as a string to the file, you should say SQLCHAR here. Say SQLINT when you've written it as a binary value, e.g. with pack. If you specify 0, DB-Library will use the type of the column in the table. You can only use datatypes for which there exists a conversion between the datatype of the file column and the datatype of the database column.

You use $file_prefixlen when you want the boundaries of the column in the data file to be determined by a length prefix. This length prefix can be 1, 2 or 4 bytes long. Specify 0 if you don't use a length prefix. Specify -1 to let DB-Library decide. (I would not recommend this; you will scratch your hair, trying to understand what's going.) Notice that a datatype such as int has a fixed length of 4 with one exception: for NULL the length is 0.

$file_collen specifies the maximum length of the column in the data file. -1 instructs DB-Library to ignore this parameter. 0 means that the column is always NULL; the column in the database will receive NULL for all rows. The value does not inlucde any length prefixes or terminators.

$col_terminator specifies a terminator string for the colunm in the data file. This string can be one or more characters long. Obviously the terminator must not appear in the data of the column. If you write a data file from Perl, and you want to inlucde newline in your terminator, you must specify "\r\n" and not only "\n", unless you applied binmode on the file handle. Specify an empty string if you don't use any terminator.

$term_len is length of $col_terminator. Set to 0 when you don't use a terminator.

$table_col is the number of the column in the table, starting on 1.

As you see there are several ways you can specify the boundaries of a column: fixed-length datatype, length prefix, maximum length or a terminator. If you use more than one, DB-Library will use the one that results in the smallest amount of data being copied. I wouldn't enourage this usage, though.

bcp_exec

$rows_copied = $dbh->bcp_exec
($status, $rows_copied) = $dbh->bcp_exec

Executes a complete bulk copy of data between a database and a user file. As shown, you can retrieve the return value in two ways: With a scalar as a return value, you will get the number of rows successfully copied. With a two-element list, the first element will be status of the operation, SUCCEED or FAIL, and the second column will be the number of rows copied.

bcp_readfmt

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

Reads a datafile format definition from a user file for a bulk copy between a file and SQL Server and from the contents makes the corresponding calls to "bcp_columns" and "bcp_colfmt".

bcp_writefmt

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

Writes a datafile format definition to a user file for a bulk copy between a file and SQL Server. The contents of the file is derived from previous calls to "bcp_columns" and "bcp_colfmt".

RPC Routines

Constants for RPC are declared in MSSQL::DBlib::Const::RPC. See eg/rpctest.pl for an example on how to use these calls.

dbrpcinit

$status = $dbh->dbrpcinit($SP, $option)

Initializes an RPC call to the stored procedure $SP. Available options are DBRPCRECOMPILE, which forces a recompile (i.e. a re-optimization) of the called stored procedure, and DBRPCRESET which cancels any previous batch of stored procedures. It's probably a good habit to always use DBRPCRESET unless you are really having more than one procedure in the same batch.

dbrpcparam

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

Adds a parameter to an RPC call initiated with "dbrpcinit".

$parname is the name of the variable, with @ included. Send undef to send an unamed parameter; this will be interpreted as the first, second etc paramter of the procedure. Once you have provided one named parameter, the remaining parameters must also be named.

$output is either zero or DBRPCRETURN to specify that the parameter is an output parameter.

$type is the datatype of the parameter. Constants for datatypes are declared in MSSQL::DBlib::Const::Datatypes. All floating-point and decimal types (money, decimal, real etc) are converted to float before being sent to the server. This also apply to bigint values. (Bigint were added in SQL2000.) You can specify SQLUNIQUEIDENTIFIER for the type uniqueidentifier; "dbrpcparam" will actually send this as SQLVARCHAR (as DB-Library does not support uniqueidentifier).

For datetime and smalldatetime parameters you can specify either a character datatype or a datetime datatype for $type. In the former case, conversion from string value to (small)datetime is performed by SQL Server, in the latter case by DB-Library (by dbconvert). This can yield different results, as DB-Library may look on regional settings (as determined in the SQL Client Configuration Utility), but SQL Server does not. Also, the two may not support exactly the same set of date formats.

You cannot specify the new datatypes nchar, nvarchar and ntext. Pass these as SQLCHAR, SQLVARCHAR and SQLTEXT respectively.

$maxlen specifies the maximum length of an OUTPUT value. My empirical tests indicate that you can set $maxlen to -1 in all cases, except when specifying NULL for a text or image parameter, when it must be 0.

$datalen is the length of the data you send in. This should be -1 for a non-NULL fixed-length datatypes. Specify 0 for $datalen to send NULL for all datatypes. If you want to send an empty string, you need to actually send one blank. $datalen cannot exceed 255.

$value is the value for the parameter. When $datalen is 0, $value is ignored. When sending in a binary type, $value may, but does not need to, include a leading 0x. Note that with DB-Library you cannot pass an empty string for a varchar parameter, although SQL Server 7 supports this.

dbrpcsend

$status = $dbh->dbrpcsend([$wait])

Executes a call to a stored procedure initiated with "dbrpcinit". The parameter $wait determines if you want to wait for SQL Server to return a reply. The default is 1, to wait.

When you specify a false value for $wait, you must later call "dbsqlok" before you can fetch data with "dbresults".

Seasoned DB-Library programmers should note that this function is slighlty different from its DB-Library namesake, which never waits for reply from SQL Server.

dbretdata

@data = $dbh->dbretdata([0])
%data = $dbh->dbretdata(1)
$dataref = $dbh->dbretdata($use_hash, 1)

Retrieves the values of the parameters marked as OUTPUT in a stored procedure executed with "dbrpcsend" in the current batch.

If the second parameter $wantref has a false value, the return value is a list of scalar values. If the first parameter $use_hash has a true value, the names of the parameters are included in the list, so that you can receive the data into a hash.

When $wantref has a true value, a reference to an array or a hash is returned, depending on $use_hash.

This function is deprecated and provided for compatibility. The preferred routine is "dbretdata2" on top which "dbretdata" is implemented. See "dbretdata2" for further details.

dbretdata2

$dataref = $dbh->dbretdata2([$use_hash])

Retrieves the values of the parameters marked as OUTPUT in a stored procedure executed with "dbrpcsend" in the current batch.

"dbretdata2" returns a reference, which is a reference to an array if $use_hash has a false value or is omitted. Else it is a reference to a hash with the parameter names (including leading @) as keys. If you did not provide any parameter names with "dbrpcparam", the keys will be "Par n", where n is the number of that output parameter, counting only the output parameters of the SP.

You must not call "dbretdata2" until you have processed the results of the SP. That is, "dbresults" should have returned NO_MORE_RESULTS (for the last procedure in a batch) or NO_MORE_RPC_RESULTS (for the all but the last procedure in a batch).

For information on how "dbretdata2" return different datatypes, see "dbnextrow2" which has an identical behaviour in this area, except in one case: parameters of the type uniqueidentifier are returned as properly formatted GUID strings.

dbretstatus

$status = $dbh->dbretstatus

Retrieve the return status of the most recently executed stored procedure in the batch. You must not call "dbretstatus" until you have processed the results of the SP. That is, "dbresults" should have returned NO_MORE_RESULTS (for the last procedure in a batch) or NO_MORE_RPC_RESULTS (for the all but the last procedure in a batch).

dbhasretstat

$status = $dbh->dbhasretstat

Returns a true value if the most recently executed stored procedure returned a status value. As with "dbretstatus" you must first process the results sets of the stored procedure.

Text/image functions

The functions for inserting and updating text/image columns in MSSQL::DBlib have a different interface from dbwritetext and dbupdatetext in DB-Library.

There is a simple example of using "dbwritetext" in eg\wtext.pl. For example of all functions, please see the test script dblib\t\3_text.t.

Notice that by default, DB-Library and SQL Server have a limit on the maximum size of a text/image values you can recieve of mere 4096 bytes. You can use these calls to remove all limits:

$d->dbsetopt(DBTEXTSIZE, "2147483647");
$d->dbsetopt(DBTEXTLIMIT, "0");
$d->dbsqlexec;
while ($d->dbresults != NO_MORE_RESULTS) {}

You cannot use these functions if you have enabled the table option text in row (available in SQL2000 an on).

General caveat: about the only time I play with text/image columns is when I work with the MSSQL::DBlib.

dbreadtext

$bytes = dbh->dbreadtext($buf, $len)

You can always use "dbnextrow2" to retrieve a text/image value in its entirety - provided that the value is smaller than the current settings of the options DBTEXTSIZE and DBTEXTLIMIT, that is. But if the values are very large, you may prefer to receive the value in chunks, and to that end you use "dbreadtext" in place of "dbnextrow2".

To use to "dbreadtext" you must submit a query with the one single column, the text/image column you want to retrieve.

$buf will receive with chunk of data. $len is how much data you want back at a maximum.

The return value $bytes can have any of these values:

>0            Number of bytes read.
0             End of row reached.
FAIL          An error occurred.
NO_NORE_ROWS  All rows read.

Note that the limits set by DBTEXTSIZE and DBTEXTLIMIT apply to "dbreadtext" as well.

dbwritetext

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

Inserts or updates data in a TEXT or IMAGE column. All existing data in the column is over-written by the new value.

$colname is the name of the text column, including table name.

$dbh_2 is the handle of another server connection, and which should have an active query against the table of the text column. $colname should be part of that result set.

$colnum is the number of the text column in that query (thus, not in the table).

$text is the text to be inserted.

$log only has importance if the recovery model of the database is BULK_LOGGED (in SQL7 and earlier this corresponds to the database option select into/bulk copy set to true). In this mode $log controls whether the operation is actually logged or not. The default is 0, not to log. In other recovery models, "dbwritetext" is always fully logged in the transaction log.

Example:

$dbh_2->dbcmd('SELECT txt, t_index FROM text_table WHERE t_index = 5');
$dbh_2->dbsqlexec; $dbh_2->dbresults;
$dbh_2->dbnextrow2($dataref);
$dbh->dbwritetext ("text_table.txt", $dbh_2, 1,
                   "This is text added with MSSQL::DBlib");

See also eg\wtext.pl for a complete script.

dbpreptext

$status = $dbh->dbpreptext($colname, $dbh_2, $colnum, $size, [, $log])

Prepares the insertion/update of text for later calls with "dbmoretext". $colname, $dbh_2, $colnum and $log works in the same way as with "dbwritetext". $size is the size in bytes of the data you intend to insert with "dbmoretext".

Example of usage:

$d->dbcmd('SELECT the_text, t_index FROM ##text_table WHERE t_index = 6');
$d->dbsqlexec;
$stat = $d->dbresults;
$stat = $d->dbnextrow2($data);

$d2->dbpreptext ("##text_table.the_text", $d, 1, length($text1 . $text2))
$d2->dbsqlok;
$d2->dbresults;
$d2->dbmoretext(length($text1), $text1)
$d2->dbmoretext(undef, $text2)
$d2->dbsqlok;
while ($d2->dbresults != NO_MORE_RESULTS) {}

Note that you twice must call "dbsqlok" followed by "dbresults" in this sequence. First once after calling "dbpreptext" and then once you have finished calling "dbmoretext".

dbmoretext

$status = dbh->dbmoretext($len | undef, $buf);

Adds a chunk of data to a text or image column. You must first have called dbpreptext to initiate the operation. $buf is the data you send in, $len is the length of the data you write. You can pass undef instead of $len, in which case "dbmoretext" will use take the length from $buf. (The interface may appear illogical, but I've preserved the parameter order from Sybperl.)

dbupdatetext

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

Updates a portion of a text/image column. For the parameters $colname, $dbh_2, $colnum, $text and $log please see the description for "dbwritetext."

$insert_offset specifies where in the column $text is to be inserted and is an offset in bytes from beginning of the text/image value. An offset of 0 means the beginning of the string. -1, which is the default, means that $text will be appended to the existing value.

$delete_length specifies how many bytes that are to be deleted from the existing value in the column. The deletion starts at $insert_offset. -1 menas that all bytes from $insert_offset to the end are deleted. The default is 0, which means that no bytes are deleted.

dbprepupdatetext

$status = $dbh->dbprepupdatetext($colname, $dbh_2, $colnum, $size,
                                [$insert_offset, [$delete_length, [, $log]]])

"dbprepupdatetext" corresponds to "dbupdatetext" as "dbpreptext" corresponds to "dbwritetext". That is, you are supposed to specify how much data you want to insert with $size and then call "dbmoretext" to insert the data.

However... I have not been able to use this function successfully. As I see it there are three possibilities: a) calling the DB-Library function dbupdatetext with the flag UT_MORETEXT does not work at all. b) MSSQL::DBlib plays tricks in "dbnextrow2" that breaks the scheme. c) I have simply not understood how the calling sequence should be.

In the vain hope that the right answer is c), I have included and documented "dbprepupdatetext", but you are warned.

dbdeletetext

$status = $dbh->dbdeletetext($colname, $dbh_2, $colnum,
                             $insert_offset, $delete_length, [, $log])

Deletes $delete_length bytes from the text/image column, starting at $insert_offset which must be >= 0. No new bytes are inserted to the column.

For $colname, $dbh_2, $colnum and $log, see "dbwritetext".

dbcopytext

$status = $dbh->dbcopytext($colnamedest, $colnamesrc,
                           $dbh_dest, $colnumdest,
                           $dbh_src,  $colnumsrc
                           [$insert_offset, [$delete_length, [, $log]]])

"dbcopytext" copies text from one text column to another.

$colnamedest is the name of the table and column to copy to. $colnamesrc is the table and column to copy from.

$dbh_dest is the handle of another server connection, and which should have an active query against the destination column. $colnamedest should be part of that result set.

$colnumdest is the number of $colnamedest in that query (thus, not in the table).

$dbh_src and $colnumsrc are the equivalent for the source column.

$insert_offset, $delete_length and $log works as for "dbupdatetext".

Example:

$dest->dbcmd("SELECT t_index, the_text FROM dest_table WHERE t_index = 36");
$dest->dbsqlexec;
$dest->dbresults;
$dest->dbnextrow2($data);

$src->dbcmd("SELECT txt FROM src_tbl WHERE id = 2");
$src->dbsqlexec
$src->dbresults;
$src->dbnextrow2($data);

$d->dbcopytext('dest_table.the_text', 'src_tbl.txt', $dest, 2, $src, 1)

$dest->dbcancel;
$src->dbcancel;
$d->dbcancel;

Two-Phase Commit Routines

open_commit

start_xact

stat_xact

commit_xact

abort_xact

close_commit

build_xact_string

remove_xact

$dbh = MSSQL::DBlib->open_commit($user, $pwd, $server, $appname)
$id = $dbh->start_xact($app_name, $xact_name, $site_count)
$status = $dbh->stat_xact($id)
$status = $dbh->commit_xact($id)
$status = $dbh->abort_xact($id)
$dbh->close_commit
$string = MSSQL::DBlib::build_xact_string($xact_name, $service_name, $id)
$status = $dbh->remove_xact($id, $site_count)

These functions are provided for compatibility with Sybperl only. Descriptions of them are available in the DB-Library documentation that comes with SQL Server 6.0 and 6.5. However, in Books Online for SQL Server 7, they are absent. I have never tested them, never will I. Use of them is strongly deprecated.

Handle Attributes

The handle you get with "dblogin" or "dbopen" has a number of attributes that you can use to alter the behaviour of MSSQL::DBlib. You access these with the Perl hash table syntax:

$dbh->{'Attribute Name'}

The following attributes are currently defined:

dbNullIsUndef

Controls how NULL values are to be returned by "dbnextrow2" and "dbretdata2". When unset, NULLs are returned as the string "NULL". When set, NULLs are returned as undef, which is the default.

dbKeepNumeric

Controls the data type of numeric values returned by "dbnextrow2" and "dbretdata2". If unset, numeric varaibles are returned to Perl as string variables. If set, numeric values appear as numeric in Perl, which is the default.

dbBin0x

Controls how binary values are returned by "dbnextrow2" and "dbretdata2". Binary values (except for the datatype image) are returned as string of hex digits. With dbBin0x set, the string is preceded with 0x, else not. By default this attribute is not set.

dateFormat

msecFormat

These control how datetime and smalldatetime values are converted to strings by "dbnextrow2" and "dbretdata2". dateFormat controls how everything but the milliseconds are formatted, while msecFormat takes care of that part, including the millisecond delimiter. (Obviously, msecFormat does not apply to smalldatetime values.)

When defined the dateFormat and msecFormat are passed to the C run-time functions strftime and sprintf respectively. (And this is why there are two of them; strftime does not deal with milliseconds.) If dateFormat is undefined, msecFormat is not looked at, and the DB-Library function dbconvert is used for the conversion.

A selection of format codes for dateFormat follows. For a full reference see the documentation for strftime in, for instance, the Visual C++ reference.

%b, %B   Abbreviated/full month name.
%c       Date and time representation appropriate for locale.
%d       Day of month as decimal number (01 – 31)
%H, %I   Hour in 24/12-hour format (00 – 23)/(01 - 12)
%m       Month as decimal number (01 – 12)
%M       Minute as decimal number (00 – 59)
%p       Current locale's A.M./P.M. indicator for 12-hour clock.
%S       Second as decimal number (00 – 59)
%y, %Y   Year without/with century, as decimal number.
%%       Percent sign

Note: Looking at a Solaris manpage for strftime, there appears to be differences to what Visual C++ provides. The codes above seem to coincide, though.

For the format of msecFormat, see the entry for sprintf in the Camel book, or look up in sprintf in any appropriate C reference.

The defaults are undef for dateFormat and ".%3.3d" for msecFormat.

While you can pick any format you like, I would suggest that you use a format that SQL Server understands. If you plan to use the RPC routines, you should know that DB-Library fails with some formats that SQL Server grasps. So the format code "%Y-%m-%d %H:%M:%S" (e.g. "1998-01-01 12:12:12") works with "dbcmd"/"dbsqlexec", but not if you pass it to "dbrpcparam" as a datetime datatype. The format "19980101 12:12:12" works with both, though.

cloneFlag

If you for some reason make a temporary copy of the hash the handle is pointing to, you need to set the attribute cloneFlag for that copy, or else Perl will close the SQL Server connection associated with that handle, when the copy goes out of scope.

The following read-only attribues are available:

DBstatus

The return status of the last call to dbnextrow. Undefined after all other calls.

ComputeID

The compute id of the most recently returned row by "dbnextrow2". See further this routine.

If you examine the handle in the Perl debugger, you may find a few more attributes. These are strictly internal.

Constants

$MSSQL::DBlib::VERSION

The version number of MSSQL::DBlib. Can be interpreted as a string or as a number.

$MSSQL::DBlib::Version

A longer blurb of version information that includes the copyright stuff.

MSSQL::DBlib::Const

The constants that are defined in SQLFRONT.H are available either from MSSQL::DBlib::Const that contains all constants, or MSSQL::DBlib::Const::* where you find the constants split up over several modules, so that you can use only those you need.

The constants TRUE and FALSE are not provided, though.

This is the complete list of modules in MSSQL::DBlib::Const::*

MSSQL::DBlib::Const::Aggregates
MSSQL::DBlib::Const::BCP          -- Constants for BCP
MSSQL::DBlib::Const::Cursor
MSSQL::DBlib::Const::Datatypes    -- SQL Server datatypes.
MSSQL::DBlib::Const::DBSETLNAME   -- Useful only for DBSETLVERSION.
MSSQL::DBlib::Const::Errors       -- DB-Library error codes.
MSSQL::DBlib::Const::General      -- See below.
MSSQL::DBlib::Const::MaxValues    -- Various max and default values.
MSSQL::DBlib::Const::NetlibErrors
MSSQL::DBlib::Const::Offset
MSSQL::DBlib::Const::Options      -- Options with dbsetopt/dbclropt/dbgetopt.
MSSQL::DBlib::Const::Print
MSSQL::DBlib::Const::RPC          -- Constants for RPC.
MSSQL::DBlib::Const::ServerInfo
MSSQL::DBlib::Const::Severity     -- Severity levels of DB-Library errors.
MSSQL::DBlib::Const::Streamtokens
MSSQL::DBlib::Const::Text
MSSQL::DBlib::Const::Timeout      -- Constants for DBSETLTIME.

Those I have left uncommented are probably not useful with MSSQL::DBlib as the DB-Library routines that use these with are not included in MSSQL::DBlib.

The module MSSQL::DBlib::Const::General includes the most commonly used DB-Library constants (and a few more which didn't seem to fit anywhere else):

CI_REGULAR CI_ALTERNATE CI_CURSOR DBNOERR SUCCEED FAIL SUCCEED_ABORT
DBUNKNOWN MORE_ROWS NO_MORE_ROWS REG_ROW BUF_FULL NO_MORE_RESULTS
NO_MORE_RPC_RESULTS INT_EXIT INT_CONTINUE INT_CANCEL STDEXIT ERREXIT
SQLESMSG DBANSItoOEM DBOEMtoANSI

Notice that SQLESMG is also declared in MSSQL::DBlib::Const::Errors and that DBANSItoOEM and DBOEMtoANSI also appear in MSSQL::DBlib::Const::Options. If you are running with the -w switch, you will get warnings about redefined subroutines, if you include both General and any of the other two modules. While these warnings are ignorable, they are not pretty. You can avoid them by replacing

use MSSQL::DBlib::Const::General;

with the following kludge:

BEGIN {
       local($^W = 0);
       require 'MSSQL/DBlib/Const/General.pm';
       import MSSQL::DBlib::Const::General;
      }

Note that you must include MSSQL::DBlib::Const::General after the other constant modules for the kludge to work.

Notes on threading and PerlScript

MSSQL::DBlib is not thread-safe with scripts that uses use threads.

However, you can use it with ActiveState's PerlScripts in things like DTS or ISAPI. Here the DLL for MSSQL::DBlib remains loaded between invocations, and each new invocation is a new thread. Obviously, you can have parallel invocations. Here are some things to keep in mind:

• A routine in DB-Library concerns either a DBPROCESS, a LOGINREC, or DB-Library as a whole. The routines in the first two category concerns the current thread only. In MSSQL::DBlib, you never see the LOGINREC, but it is there under the covers. The routines that concerns DB-Library on global level will affect all threads. They are: dbsettime, dbsetlogintime, dbsetmaxprocs, DBGETTIME, dbgetmaxprocs and dbexit. Thus some care is needed when calling these functions.

• Never call dbexit in the context of PerlScript. You are closing down DB-Library, and you could wreck another thread's handle.

• When you set up an error or message handler with dberrhandle or dbmghandle, the handler will be local to the thread. (This is different from how you work with DB-Library in C.)

• If you are expecting many parallel executions, you may need to change the limit of current DBPROCESSes with dbsetmaxprocs. The default value is 25.

Finally a caveat: I have actually only tested with DTS. If you run into problems with ISAPI, there is a workaround. Set this registry key to 0:

HKEY_LOCAL_MACHINE/SYSTEM/CurrentControlSet/Services/W3SVC/Parameters/CacheExtensions

BUGS

If you call "dbexit" without calling "dbclose" on your handles, you will get an error message from DB-Library when the Perl script exits.

If you run a script from the Perl debugger and the exit the debugger while in a message or error handler, Perl will hang until you kill it with CTRL/C. Obviously DB-Library doesn't like its dbclose being called from a error or message handler. (dbclose is called implicitly when an object is destroyed.)

Compatibility with Sybperl

This section only concerns the module Sybase::DBlib. There is no support for Sybase::Sybperl or Sybase::BCP, and even less for Sybase::CTlib, as this library is not available with Microsoft SQL Server.

• You must change use Sybase::DBlib to say use MSSQL::DBlib.

  You probably also need to add use for the MSSQL::DBlib::Const::* modules you need, see the section "MSSQL::DBlib::Const" above. Also note that some constant names are different with MS SQL Server. For instance, the for the error codes the names start with SQL, not SYB.

• The following routines available in Sybperl 2.03 are missing:

  DBSETLCHARSET, bcp_getl, the dbmny* routines, dbrecftos, dbsafestr, dbsetifile, dbversion, dbrpwset, dbrpwset, scan_xact.

  All of these are not available in Microsoft's DB-Library. (Except for scan_xact which is documented, but doesn't link...)

• The routines "dbsetopt" and "bcp_colfmt" have a shorter parameter list with MSSQL::DBlib than in Sybperl, since Microsoft does not provide these parameters.

• The constant DBLIBVS is not avaiable. Also, constants that were added by Sybase after the split between Sybase and Microsoft are likely to be missing.

• Features that Michael Peppler added to Sybperl in version 2.04 and later are not present, as MSSQL::DBlib is based on a port of Sybperl 2.03 to NT. This include the debug/trace feautre, the Sybase::Money and Sybase::DateTime classes, and the BCP module. There is likely to be more.

• "dbprtype" has a different behaviour from Sybperl. (Then again, this routine is undocumented in Sybperl).

• The routine Sybase::DBlib::sql is not available in MSSQL::DBlib, but has a much more powerful namesake in the MSSQL::Sqllib module. That also means that the handle attribute MaxRows have no meaning in MSSQL::DBlib.

AUTHOR

Larry Wall invented Perl.

Michael Peppler wrote the original Sybperl.

Christian Mallwitz of Intershop GmbH ported Sybperl 2.03 to NT.

Erland Sommarskog <sommar@algonet.se> turned Michael's and Christian's efforts into MSSQL::DBlib and is the maintainer of this module.

cpanm MSSQL::DBlib

perl -MCPAN -e shell
install MSSQL::DBlib