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


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


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 the mssql-sqllib manpage.

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 the mssql-sqllib manpage. For a full discussion, see SQL Server Books Online.


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");
    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.



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


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

Make $database the current database.


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

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



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


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


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


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


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


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


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


   $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");
   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.


   $status = $dbh->dbcancel

Cancels the current command batch, discarding all results sets.


   $status = $dbh->dbcanquery

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



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.


   $status = $dbh->DBCURCMD

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


   $status = $dbh->DBMORECMDS

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


    $status = $dbh->DBCMDROW

Returns SUCCEED if the current command can return rows.


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


    $isreal = $dbh->dbiscount

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


   $row_num = $dbh->DBCURROW

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


   $count = $dbh->dbnumcols

How many columns there are in the current result set.


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


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

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


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

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


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


   $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");
    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\ for an example of using dbsetopt.

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


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


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

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


  $size = $dbh->dbgetpacket()

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


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


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


    $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\ for an example of using a message handler that catches SHOW STATISTICS output.


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


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


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


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


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


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


    $status = DBSETLPWD($pwd)

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


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


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



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


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


    $ret = dbsetmaxprocs($maxprocs)

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


    $maxprocs = dbgetmaxprocs()

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


    $time = DBGETTIME()

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


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


    $status = dbsetlogintime($seconds)

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



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.


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


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


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


   $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\ for a simple example.


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


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


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


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


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


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

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.


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


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


    $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/ for an example on how to use these calls.


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


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


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


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


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


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


   $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\ 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");
   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.


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


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


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

See also eg\ for a complete script.


   $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');
   $stat = $d->dbresults;
   $stat = $d->dbnextrow2($data);
   $d2->dbpreptext ("##text_table.the_text", $d, 1, length($text1 . $text2))
   $d2->dbmoretext(length($text1), $text1)
   $d2->dbmoretext(undef, $text2)
   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.


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

Adds a chunk of data to a text or image column. You must first have called the dbpreptext manpage 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.)


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


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


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


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


   $dest->dbcmd("SELECT t_index, the_text FROM dest_table WHERE t_index = 36");
   $src->dbcmd("SELECT txt FROM src_tbl WHERE id = 2");
   $d->dbcopytext('dest_table.the_text', 'src_tbl.txt', $dest, 2, $src, 1)

Two-Phase Commit Routines

   $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)
   $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:

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.

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.

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.

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.

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:

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

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.


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

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


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::BCP          -- Constants for BCP
   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::Options      -- Options with dbsetopt/dbclropt/dbgetopt.
   MSSQL::DBlib::Const::RPC          -- Constants for RPC.
   MSSQL::DBlib::Const::Severity     -- Severity levels of DB-Library errors.
   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):


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/';
          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:

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:



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.


Larry Wall invented Perl.

Michael Peppler wrote the original Sybperl.

Christian Mallwitz of Intershop GmbH ported Sybperl 2.03 to NT.

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