/ 
mssql-1.009
River stage zero No dependents
/ MSSQL::Sqllib

NAME

MSSQL::Sqllib - SQL Library for Microsoft SQL Server from Perl

SYNOPSIS

use MSSQL::Sqllib;

use MSSQL::Sqllib qw(:DEFAULT :consts);

use MSSQL::Sqllib qw(:DEFAULT [:resultstyles] [:rowstyles] [:directions]
                              [:returns] [$SQLSEP]);

OVERVIEW

MSSQL::Sqllib is a Perl module that inherits from "MSSQL::DBlib" in mssql-dblib and extends it with a high-level interface, providing the possibility to send an SQL command and retrieve the result in one line of code and forget about error-checking. By default MSSQL::Sqllib will abort in case of a error, be it in DB-Library or SQL Server. The result set(s) from the SQL query can be received by callback or in a return value which can be structured in several ways, ranging from one single scalar up to a three-dimensional array, or in a hash with keyed with key values in the data.

There are also special calls for single-row queries, for generating INSERT statements from a hash, and generating code for calling stored procedures and retrieve output parameters.

MSSQL::Sqllib has a configurable message handler for SQL errors, permitting you to choose which errors you tolerate and which you do not. You can opt to get the errors messages sent back to you and/or have them printed on STDERR. A similar arrangement exists for handling DB-Library errors.

MSSQL::Sqllib also provides the possibility to chose your own character conversion much more freely than Microsoft's ANSI/OEM concept permits.

For special tasks, you have access to all calls in MSSQL::DBlib.

A simple example of using this module:

use MSSQL::Sqllib;

# Log into the server.
my $X = sql_init("", "sa", "", "master");

# Run a query.
@x = $X->sql("SELECT dbid, name, crdate FROM sysdatabases");

# Just print the results, it's a list of hashes.
foreach $x (@x) {
  foreach $kol (keys %$x) {
     print "$kol: $$x{$kol}   ";
  }
  print "\n";
}

Since MSSQL::Sqllib is based on DB-Library, and Microsoft is no longer developing DB-Library, this means that with MSSQL::Sqllib you do not have access to all features in SQL7 and SQL2000. See the section "Restrictions with new datatypes" at the end of this document. Thus, you should not use MSSQL::Sqllib, unless you are certain that the restrictions will not be an issue for you.

If you want to use MSSQL::Sqllib in a threaded environment, the short story is that you cannot use it with use threads in a Perl script, but you can use it in an environment for a thing like ActiveState's PerlScript. Please see further "Notes on threading and PerlScript" in mssql-dblib.

EXPORT TAGS

By default MSSQL::Sqllib exports a couple of routines. They are described in the section "FUNCTION DESCRIPTIONS".

MSSQL::Sqllib also exports a number of constants that are used in the interface of the routines. To avoid clashes with other modules, they are not exported by default. You can request these being imported by mentioning them in your use statement, either by mentioning them explicitly, or using any the following export tags below. Don't forget that export tags must be preceded by a colon, see the "SYNOPSIS".

DEFAULT

This tag imports all routines in MSSQL::Sqllib, but no constants. You need to use this tag when you add other items to the import list, since as soon as you have an explicit list, nothing is imported by default.

consts

Imports all constants, as well as $SQLSEP, the only control variable in MSSQL::Sqllib. (In fact it is quite much of a constant, save the fact that you can change it. :-)

resultstyles

Imports these five constants: NORESULT, SINGLEROW, SINGLESET, KEYED and MULTISET. You use them to choose how the results from an SQL batch are to be structured on Perl level.

rowstyles

Imports these three constants: SCALAR, LIST and HASH. You use them to choose how the rows in a result set is to be represented.

directions

Imports these three constants: TO_SERVER_ONLY, TO_CLIENT_ONLY and TO_SERVER_CLIENT. You use them with the configuration routines "sql_set_conversion" and "sql_unset_conversion".

returns

Imports these five constants: RETURN_NEXTROW, RETURN_NEXTQUERY, RETURN_CANCEL, RETURN_ERROR and RETURN_ABORT. You use them for return values from callback routines.

FUNCTION DESCRIPTIONS

Summary

"sql_init" sets up message and error handlers, logs into the server, sets up defaults and returns a blessed reference.

"sql" runs a batch of one or more SQL queries and returns the result which can be structured in the way of your choice. Alternatively, "sql" can send the results row by row to a user-provided callback routine. By default, if an error occurs, "sql" will abort the Perl script.

"sql_one" runs an SQL query that is supposed to return exactly one row, and "sql_one" will abort if the query does not.

"sql_sp" calls a stored procedure with named or unnamed parameters and retrieves the result sets, the return value and any output parameters of the SP.

"sql_insert" generates an INSERT statement from a hash and executes the statement.

"sql_begin_trans", "sql_commit", "sql_rollback" begin and end transactions.

"sql_string" formats a string to use in an SQL statement.

"sql_set_conversion" sets up a conversion from one character-set to another. The conversion may be bi-directional, or only from client to server or vice versa.

"sql_unset_conversion" removes a conversion in force.

"sql_message_handler" is a configurable handler for SQL Server messages.

"sql_error_handler" is a configurable handler for DB-Library errors.

sql_init

$X = sql_init([$server [, $user [, $password [, $database]]]]);
$X = new MSSQL::Sqllib [$server, [$user [, $password [, $database]]]];

"sql_init" logs into SQL Server, and performs a number of initializations listed below. new simply calls "sql_init".

The following defaults apply for the parameters:

$server: the server on the local machine.

$user: sa.

$password: NULL.

$database: the user's default database as defined in SQL Server, with one exception: for sa, the database will be tempdb to avoid a disaster, were you to accidently omit $database.

To use Windows NT authentication (known as "integrated security" MS SQL 6.5), call the MSSQL::DBlib routine "DBSETLSECURE" in mssql-dblib before you call "sql_init".

The following initializations are performed:

  • The first time "sql_init" is called, it will establish "sql_message_handler" and "sql_error_handler" as message and error handlers. On further calls, the then-current handlers will be retained.

  • Sets the attributes dateFormat and msecFormat to %Y%m%d %H:%M:%S and .%3.3d respectively, giving a default representation for datetime values in the style of "19980106 13:29:19.230".

  • Turns of any ANSI/OEM conversion that may be in effect. See "sql_set_conversion" if you wish to use character-set conversion.

If any error occurs while setting up the connection, "sql_init" will abort execution. This includes errors that are not considered fatal by SQL Server, such as the user's default database in SQL Server not being accessible. (If you need to continue after a failed login, remember that you can always run your "sql_init" from an eval.)

$server may refer to a named instance, for instance MYMACHINE\SECOND. Officially, Microsoft does not support named instances in DB-Library, but it appears they changed DB-Library to handle it nevertheless. If you run into to problems, use the Client Network Utility and set up an alias for the named instance.

The return value from "sql_init" is a blessed reference to a hash, in this document usually called a handle. The handle that is created the first time "sql_init" is called becomes the default handle for calls to other routines in MSSQL::Sqllib. This is conventient in throw-away scripts, but for serious development, I recomment that you always use a handle when you access the other routines. You often need to use the handle anyway, to change the attributes, particularly "errInfo". In the syntax descriptions below, the handles are shown as mandatory.

NOTE: If you use MSSQL::Sqllib from a thing like ActiveState's PerlScript you must always use the handle.

sql

$resultref = $X->sql($sql [, $rowstyle] [, $resultstyle]]);
@result    = $X->sql($sql [, $rowstyle] [, $resultstyle]]);
%result    = $X->sql($sql, HASH, SINGLEROW);

$hashref   = $X->sql($sql [, $rowstyle], KEYED, \@keys);
%hash      = $X->sql($sql [, $rowstyle], KEYED, \@keys);

$retstat   = $X->sql($sql [, $rowstyle], \&callback);

$X is the handle that you got when you called "sql_init".

$sql is a string containing one or more SQL statements. "sql" executes them as one batch (thus you cannot separate batches with go), and in case of success returns the result set.

By default, if SQL Server or DB-Library signals an error, execution is aborted. You can alter this behaviour with the various elemetns of the handle attribute "errInfo". See this attribute for details.

For details on how the various data types in SQL Server are returned, see the MSSQL::DBlib routine "dbnextrow2" in mssql-dblib.

The data from SQL Server can be structured in several different ways, as determined by the parameters $rowstyle and $resultstyle, as detailed below. Below I say that the return value for a certain combination is a scalar, array or a hash. In every case when I say array or hash, you can always opt for receiving the return value in a scalar to get a reference to that array/hash.

In the call syntax above, $rowstyle is shown as coming before $resultstyle. However, "sql" actually permits you to put $rowstyle after $resultstyle. This is also true when $resultstyle is KEYED or \&callback. However, $rowstyle must come before \@keys.

Row style

$rowstyle controls how each row is to appear at the lowest level of the result. There are three possible values:

HASH

Each row is returned as a hash with the column names as keys. Nameless columns are called Col 1, Col 2 etc. This is the default row style.

If more than one column in a result set has the same name, only one of them will be present in the result set. If warnings are active, you will receive a warning about this condition.

LIST

Each row is returned as an array with the columns appearing in the order they appeared in the query with the first column at index 0.

SCALAR

Each row is returned as a scalar value. SCALAR is mainly intended for one-columns queries, but if the query has more than one column, they are separated by the module attribute $MSSQL::Sqllib::SQLSEP. Initially $SQLSEP has the value "\022", a control character. You can set it to a string of any length that fit your needs. Note, however, that the value of $SQLSEP must not appear in the actual data.

Result styles

$resultstyle controls how the rows are "packaged". There are five possible values:

SINGLESET

The rows are returned in one single array, which thus is an array of hashes, arrays or scalars depending on $rowstyle. The order of the array is the same as the order returned from SQL Server. If the batch returns two or more result sets, these will come after each other in the result set, and you will have to separate them yourself.

SINGLESET is the default resultstyle.

MULTISET

You get an array of result sets in the order they come from SQL Server. Each result set is in its turn an array of hashes, arrays or scalars dependning on $rowstyle.

SINGLEROW

The return value is a hash, an array or a scalar with the single row that the batch is supposed to return. When you specify SCALAR and SINGLEROW, and provide a scalar return value, you will get the actual row, not a reference to a scalar.

Would the batch return more than one row, or more than one result set, you get what you get, and it may or may not make sense. See also "sql_one".

KEYED

The return value is a hash keyed with the data in the highest-level key column in the result set. There can be more than one key level, in which case you will get one more hash dimension for each key level. In the bottom of the structure there is a hash, array or scalar, depending on $rowstyle, with the non-key columns of the result set.

Which column(s) that supply the key values, is determined by the parameter @keys, which is mandatory for KEYED. For row style HASH, @keys should hold the name of the key column(s) of the result set. For row style LIST and SCALAR, @keys should hold the column number of the key(s). Column numbers start on 1.

A simple example, retrieve all types and their ids:

%types = $X->sql("SELECT name, type FROM systypes", SCALAR, KEYED, [1]);
foreach $type (keys %types) {
   print "$type:  $types{$type}\n";
}

A more complicated case. The table Tbl has the columns key1, key2, key3, val1, val2, ... Among other data, we want to retrive the column val4 for the key ABC/17/X.

$result = $X->sql("SELECT * FROM Tbl", HASH, KEYED,
                  ['key1', 'key2', 'key3']);
$val4 = $$result{'ABC'}{'17'}{'X'}{'val4'};

The keys you provide in @keys are supposed to be the unique keys of your result set, and appear in all rows of the result set(s). If a key listed in @keys does not appear in one of the rows, this is an error, and "sql" will unconditionally abort. If a key value is a duplicate, "sql" will emit a warning about this, if warnings are activated. It is undefined which values that end up in the result set.

If one or more of your key columns can hold NULL values, you will get warnings about uninitialized value from Perl if you run with -w. One way to avoid this warning, is to set the handle attribute dbNullIsUndef to 0, in which case NULL values will be received as the string "NULL" instead of undef.

NORESULT

The batch is not expected to return any data, or you simply do not care about it, and wish to save network traffic. The return value is undef or an empty array.

The script sqllib\t\1_resultsets.t can give you an idea of the structure you get with the various row and result styles.

When there are no result sets in the query at all - which may happen if the batch does not compile, and you have changed "errInfo" so that "sql" does not abort on errors - you will get undef in return if you specify a scalar return value. Else you will get an empty list/hash.

Callbacks

Instead of a result style, you can provide a callback. "sql" calls your callback in this way, depending on $rowstyle:

$retstat = &$callback(\%row, $resultset_no);   # HASH
$retstat = &$callback(\@row, $resultset_no);   # LIST
$retstat = &$callback($row,  $resultset_no);   # SCALAR

$resultset_no tells you which result set the row comes from. The result sets are numbered from 1 and up.

The callback should return any of the integer numbers detailed below. The return value from the last call to the callback is the return value from "sql".

RETURN_NEXTROW

Tells "sql" to give you the next row (if there is one, that is). This is the "normal" return value. I usually don't leak constant values, but if you think RETURN_NEXTROW is too noisy, I can reveal that 1 will do the same task.

RETURN_NEXTQUERY

Tells "sql" to cancel the current result set, and proceed to the next one. If there are no more result sets, this is how the story ends.

RETURN_CANCEL
RETURN_ERROR

From the point of view of "sql" these are the same. The entire batch will be cancelled and that's it. There are two different values, though, so when getting the return value from "sql" you can tell them apart. RETURN_CANCEL probably means that you found what you was looking for. RETURN_ERROR probably means that something went wrong. RETURN_ERROR conveniently has the value 0.

RETURN_ABORT

Something went terribly wrong and you don't want to live any more. You want "sql" to hold the axe though, so it can cancel the query before it aborts. Useful if "sql" was called from inside an eval.

A few goods and bads with callbacks:

  • Good thing: saves you from dealing with multi-dimensional structures, which can be confusing at times.

  • Good thing: if you are retrieving a large result set, you don't need to gather all the data in client before processing them.

  • (Slightly) bad thing: if you want to execute SQL statements from your callback, you need to use another handle for that, as DB-Library does not permit you to intiate a new query as long as there are results sets available.

  • Bad thing: if the tables are large you may hold a lock on them for a considerable time. This also means that it is not always a bright to idea to send an UPDATE on the tables from the callback. As this is done from another connection, you may in fact end up locking yourself.

COMPUTE BY

If the batch contains COMPUTE BY clauses, the COMPUTE rows will come interleaved with the regular rows. With the LIST and SCALAR rowstyle values, you have to do the best you can to tell which is which. With HASH, though, the COMPUTE rows will have an extra column COMPUTEID that holds the number of the COMPUTE BY clause in the query.

sql_one

%result = $X->sql_one($sql[, HASH]);
@result = $X->sql_one($sql, LIST);
$result = $X->sql_one($sql[, SCALAR)];

"sql_one" is similar to "sql" with the SINGLEROW result style. However, "sql_one" requires the SQL batch to return exactly one row, and will abort execution if no rows were found, or if there were more than one row. The batch may return more than one result set, but all but one must be empty.

sql_sp

$resultref = $X->sql_sp($sp [, \$sp_retstat] [, \@params] [, \%params]
                            [, $rowstyle] [, $resultstyle]);
@result    = $X->sql_sp($sp [, \$sp_retstat] [, \@params] [, \%params]
                            [, $rowstyle] [, $resultstyle]);
%result    = $X->sql_sp($sp [, \$sp_retstat] [, \@params] [, \%params],
                            HASH, SINGLESET);

$hashref   = $X->sql_sp($sp [, \$sp_retstat] [, \@params] [, \%params]
                            [, $rowstyle], KEYED, \@keys);
%hash      = $X->sql_sp($sp [, \$sp_retstat] [, \@params] [, \%params]
                            [,  $rowstyle], KEYED, \@keys);

$retstat   = $X->sql_sp($sp [, \$sp_retstat] [, \@params] [, \%params]
                            [, $rowstyle], \&callback);

$X is the handle you got from "sql_init".

"sql_sp" calls the stored procedure $sp and retrieves the return status of the SP and values of output parameters. To find out datatypes and output parameters, "sql_sp" interrogates the server and caches the parameter profile internally in case the same SP is called again. See the handle attribute procs for details about caching.

By default, if an error occurs in SQL Server or in DB-Library, or the procedure returns a non-zero value, execution is aborted. This behaviour is controlled by various elements the handle attribute "errInfo". See further this attribute.

The various return values of "sql_sp", as well as the parameters $rowstyle, $resultstyle, &callback, and @keys work exactly in the same way as with "sql", please see this routine for details.

\$sp_retstat is a reference to scalar that is to receive the return status of the stored procedure. This parameter is only of interest, if you have changed the checkRetStat or retStatOK elements of "errInfo" to permit one or more non-zero return values.

\@params is a reference to an array that holds unnamed parameters to the stored procedure in the order as they appear in the procedure declaration. The array elements can be scalars with the actual parameter values, or references to scalars that hold the values. Values and references can be mixed. If any of the parameters is an output parameter, the output value replaces the input value. When you send in an anonymous array, you need to pass output parameters as references, or else you will lose the changes. Of this reason "sql_sp" will emit a warning if warnings are active, when it detects that an output parameter has been passed as a value. (Yeah, this warning is bogus if you send in an actual array, but there is no way to tell them apart.) See also the second of the examples below.

\%params is a reference to a hash with the keys being names of the stored-procedure parameter. (Don't forget the @.) For the elements the same issues as for \@params apply.

Notes:

  • If you provide \@param as well as \%param, and the same parameter appears in both, the value in \@param will be used, and will in fact overwrite what's in \%param.

  • It is undefined whether output parameters of the datatypes binary and char will include trailing zeroes or blanks respectively. This actually depends on which version of SQL Server you are using.

  • To pass NULL for a parameter, send undef. "sql_sp" does not consider the attribute dbNullIsUndef.

Examples

sp_helpdb returns two result sets. Here is an example using a callback that prints out the result sets in a fairly simple manner:

sub print_hash {
   my($hash, $ressetno) = @_;
   my ($col);
   print "$ressetno: ";
   foreach $col (%$hash) {
        print "$col: $$hash{$col} ";
   }
   print "\n";
   RETURN_NEXTROW;
}
$X->sql_sp("sp_helpdb", ['tempdb'], HASH, \&print_hash);

And here is an example with a procedure that takes two dates as parameters to count the number of records in that interval. Passing NULL means no limit in that direction. The SP permits you to restrict the count to records of a certain flavour. The value is returned in an output parameter. There are no result sets.

CREATE PROCEDURE putte_sp @startdate datetime = NULL,
                          @enddate   datetime = NULL,
                          @flavour   smallint = NULL,
                          @no_of_rec int      OUTPUT AS

Say we want to know how many records there are from 1997 and on, of all flavours, and we also want the return value. In T-SQL the call would be:

EXEC @ret = putte_sp '19970101', @no_of_ref = @no_of_rec OUTPUT

In MSSQL::Sqllib this translates to:

$X->{errInfo}{checkRetStat} = 0;
$X->sql_sp('putte_sp', \$ret, ['19970101'], {'@no_of_rec' => \$no_of_rec});

Notice how we pass a reference to $no_of_rec, rather than the variable itself, so we can retrieve the output value later on. We also set the checkRetStat attribute, as else sql_sp would abort if the return value was non-zero.

sql_insert

$X->sql_insert($table, $valref);

"sql_insert" generates an INSERT statement from a hash and executes it. No data is returned.

$table is the name of the table, and $valref is a reference to a hash where the name of the keys are taken as column names. "sql_insert" interrogates the server to find out the datatypes of the table columns. This profile is cached internally, in case the same table is inserted to again. See also the handle attributes tables.

For string types, "sql_insert" will put quotes around the values (using "sql_string") when composing the INSERT statement. For binary datatypes, "sql_insert" will add add a leading 0x, unless a 0x is already there.

Notes:

  • To pass NULL for a parameter, send undef. "sql_insert" does not consider the attribute dbNullIsUndef.

  • While "sql_insert" is convenient, be aware of that there is a considerable performane penalty for inserting data to SQL Server with bare SQL statments, and there is a lot to gain by using stored procedures.

sql_string

$string = sql_string($string);

"sql_string" returns $string surrounded by single quotes, and any single quote in $string is doubled. If $string is undef, the string NULL (unquoted) is returned. Example:

print sql_string("This is Jim's house");
print sql_string;

prints

'This is Jim''s house'
NULL

Transaction routines

$X->sql_begin_trans;
$X->sql_commit;
$X->sql_rollback;

These are just a short way of saying

$X->sql("BEGIN TRANSACTION");
$X->sql("COMMIT TRANSACTION");
$X->sql("ROLLBACK TRANSACTION");

respectively.

sql_set_conversion

$X->sql_set_conversion([$client_cs [, $server_cs [, $direction]]]);

sql_set_conversion establishes a character conversion between client and server and/or vice versa for the given handle. If you don't provide a handle, the conversion is established for the handle returned by the first call to "sql_init".

To start from the back, $direction specify in which direction you want the conversion to apply. There are three possible values:

TO_CLIENT_ONLY

Convert only data that comes back from the server.

TO_SERVER_ONLY

Convert only data that is sent to the server.

TO_SERVER_CLIENT

Convert both when sending and receiving. This is the default.

$client_cs is the character set for the data on the client side, and $server_cs is the character set for the data on the server side. The chosen characters sets do not need to agree with the official character sets of the client or the server.

Specify the character sets as code pages, with or without the leading "CP". For $client_cs you can also specify "OEM" or "ANSI" to use the OEM or ANSI character set of the client. These are read from the key SYSTEM\CurrentControlSet\Control\Nls\CodePage in the HKEY_LOCAL_MACHINE hive. For the server character set, you can specify "iso_1" as an alternative to "CP1252" to get Latin-1.

The default for $client_cs is the local OEM character set. The default for $server_cs is the character set of the server (SQL7 and earlier) or the default collation for the server (SQL2000 and later). (Use sp_helpsort to find out if you don't know.)

To set up conversion "sql_set_conversion" looks in the SYSTEM32 directory of the NT installation as pointed to by the environment variable SystemRoot. In this directory it looks for the file SSSSCCCC.CPX and if this fails an attempt is made with CCCCSSSS.CPX . SSSS is the code-page number for the server character set, and CCCC is the code-page number for the client character set. For instance the file for converting to CP850 to Latin-1 (CP1252) is 12520850.CPX. If none of the files are found, execution is aborted.

Notes:

  • The conversion facility was added because SQL6.5 had insuffcient support in this area. The GUI tools would unconditionally convert between ANSI and OEM when reading and saving from disk. In other situations you could not get the conversion you wanted. Tools in SQL7 improved in this area, not the least BCP which offers the full range of choices just like MSSQL::Sqllib. But OSQL in SQL7 could convert at all. OSQL in SQL2000 can, because you can now specify a data source. But you still can convert to OEM if your data is ASCII and your server is OEM, because OSQL is an OEM tool.

  • These character-set conversions typically are not fully round-trip. That is, if you convert one character from set to another and back, you may not have the same character you started with. This is because the character sets contain different characters, and if a character is missing in the target character set, it will be converted to some other character, for instance the most similar-looking character.

  • You may think that the idea of a unidirectional conversion is funny, but say that you have a table in which the data is the CP850 character set, and you want it to be in Latin-1. This could be simple way of doing it:

    use MSSQL::Sqllib(:DEFAULT :directions)

my $X = sql_init(undef, "sa", "", "db");
$X->sql_set_conversion("CP850", "iso_1", TO_SERVER_ONLY);
@table = $X->sql(<<SQLEND);
   SELECT * INTO #tbl FROM tbl WHERE 1 = 0  -- Clone the table.
   SELECT * FROM tbl
SQLEND
foreach $row (@table) {
   $X->sql_insert("#tbl", $row);
}
$X->sql(<<SQLEND);
   BEGIN TRANSCTION
   DELETE tbl
   INSERT tbl SELECT * FROM #tbl
   COMMIT TRANSACTION
SQLEND

    Here we are converting the data as we send it back. Alternatively we could have said:

    $X->sql_set_conversion("ANSI", "CP850", TO_CLIENT_ONLY);

  • If the metadata names - names of tables, columns, stored procedures and their parameters - contain characters that are subject to conversion, these names are converted as well. Things may go well with bi-directional conversion. With uni-directional conversion you are likely to run into trouble, and with "sql_insert" and "sql_sp" you are almost bound to fail.

  • If you wish to have more specific conversion, for instance converting only some characters (something you may want to do when you have a mix of character sets in the database), compose your own CPX-file and put it in System32.

sql_unset_conversion

$X->sql_unset_conversion([$direction]);

Removes any previous conversion in the given direction. If you leave out $direction, conversion is removed in both directions. See "sql_set_conversion" for legal values of $direction.

If you don't provide a handle, the conversion is removed for the handle returned by the first call to "sql_init".

sql_message_handler

This routine is not exported, and thus not available by itself outside MSSQL::Sqllib. However, as it is called time each time SQL Server sends a message back to the client, its doings will still be of interest to you.

The behaviour of "sql_message_handler" is determined by the handle attribute "errInfo", a hash which is described in detail separately in its own section. Here I describe how "sql_message_handler" behaves with the default values of "errInfo" in force.

If the severity of the SQL Server message is > 10, "sql_message_handler" will set the dieFlag element of the "errInfo" hash. If DB-Library was called from within MSSQL::Sqllib, the calling routine will then terminate execution. If you called DB-Library outside of MSSQL::Sqllib, you need to look at dieFlag yourself.

This behaviour can be altered with the "errInfo" elements maxSeverity, alwaysStopOn and neverStopOn. They permit you to choose a different permitted severity level, and to specify exceptions for specific errors.

The message handler will also in its default configuration print a message to STDERR. Here is a sample:

SQL Server message 411, Severity 16, State 2, Server SOMMERSKOV
Line 1
Compute clause #2, aggregate expression #1 is not in the select list.
    1> SELECT  dbid, segmap, lstart, size, vstart FROM sysusages
    2> ORDER   BY dbid, segmap
    3> COMPUTE sum(size) BY dbid, segmap
    4> COMPUTE sum(2 * size) BY dbid
    5> COMPUTE sum(size)

Thus, you get both the error text as well as the code that caused the error.

If severity is 0 only the text part is printed. (That is Compute clause... in the example above.)

Two messages are suppressed completely: the messages Changed database context... and Changed language setting... On the other hand, the messages about arithmetic overflow and division by zero are printed in full, even if they have severity 0.

You can change what is being printed with the "errInfo" elements printMsg, printLines, printText, neverPrint and alwaysPrint.

sql_error_handler

Just like "sql_message_handler" this routine is not exported, but you can control its behaviour by means of "errInfo". Here follows a description of the default behaviour and a brief coverage of the control possibilities.

"sql_error_handler" is called when DB-Library generates a message. If the message has a severity level > 1, "sql_error_handler" will set dieFlag, and if DB-Library was called from MSSQL::DBlib, the execution will be aborted. However, the error General SQL Server error... is ignored, as this always is accompanied with a SQL Server error handled by "sql_message_handler".

You can override this behaviour with the "errInfo" elements maxLibSeverity, neverStopOn and alwaysStopOn.

"sql_error_hanlder" prints all messages it receives, with the exception of the aforementioned General SQL Server error... You can override this with the "errInfo" element neverPrint.

HANDLE ATTRIBUTES

The attributes of MSSQL::DBlib is available with MSSQL::Sqllib as well. See the section "Handle Attributes" in mssql-dblib in the MSSQL::DBlib doc for inforamtion on these attributes.

Here are the attributes specific to MSSQL::Sqllib.

Misc attributes

SQL_version

This is a read-only attribute (well, nothing prevents you from changing it, but if you do, please don't send me the error reports) that holds the current SQL Server version. The is the version string that you see in @@version, and also in xp_msver, for instance 8.00.194. (The release version of SQL 2000.)

logHandle

When defined this should be an open filehandle to which all code you pass to "sql" and "sql_one" is written, as well as the code generated by "sql_insert" and "sql_sp".

The code is written after any client-to-server character-set conversion has been applied.

Note that for "sql_sp" what you get is actually fake. "sql_sp" uses RPC calls, so the EXEC statement you get in the log has never been executed. It is nevertheless accurate, except that OUTPUT parameters are not marked as such.

noExec

Tells MSSQL::Sqllib to not execute the code it gets. This is useful in combination with logHandle if all you want to do is to generate a script that is to be run by isql later.

Note that you still need to log into the server to use noExec, and "sql_insert" and "sql_sp" will still call the server to retrieve information on table and procedures. Also "sql_set_conversion" may call the server.

procs

This is a hash that "sql_sp" uses to cache parameter profiles. Normally you have no reason to fiddle with this attribute, but if you have reason to believe that a stored procedure has changed interface, since the last time you called "sql_sp", you can delete the SP from procs.

tables

This is a hash that "sql_insert" uses to cache table definitions. Normally you have no reason to fiddle with this attribute, but if you have reason to believe that a table has changed, since the last time you called "sql_insert", you can delete the table from tables.

errInfo

This attribute is a hash whose components are used by "sql_message_handler", "sql_error_handler" and "sql_sp" to determine whether to abort execution, what and when to print and whether to return error messages to the caller. Some of the components are arrays and hashes themselves. A pseudo-code declaration with default values of "errInfo" looks like this:

RECORD errInfo
    -- Where to write error messages
    errFileHandle  IO::File = STDERR;

    -- Abort control for sql_message_handler and sql_error_handler
    dieFlag        flag;
    maxSeverity    integer = 10;
    maxLibSeverity integer = 1;
    neverStopOn    HASH OF flags = {'-10007' => 1};
    alwaysStopOn   HASH OF flags = undef;

    -- Print control for sql_messsage_handler and sql_error_handler
    printMsg     integer = 1;
    printText    integer = 0;
    printLines   integer = 11;
    neverPrint   HASH OF flags = {'5701' => 1, '5703' => 1, '-10007' => 1}
    alwaysPrint  HASH OF flags = {'3606' => 1, '3607' => 1, '3622' => 1}

    -- Abort control for sql_sp
    checkRetStat flag = 1;
    retStatOK    HASH OF flags = undef;

    -- Return error messages to caller
    saveMessages flag = 0;
    messages     ARRAY OF RECORD
                    state     integer;
                    errno     integer;
                    severity  integer;
                    text      string;
                    proc      string;
                    line      string;
                    oserr     integer;
                    oserrtext string;
                 END
END

In actual Perl code you would refer to an element in the messages array like this:

$X->{errInfo}{'messages'}[0]{'errno'}

This should give a clue on how to refer the other elements or "errInfo" as well.

Notice that you cannot use "errInfo" to control the behaviour during "sql_init".

errFileHandle

Where "sql_message_handler" and "sql_error_handler" are to write their messages. Initially undef, which will cause the handlers to write to STDERR. Set errFileHandle to a filehandle open for write to override this. errFileHandle is useful, when you want to save SQL errors to a file, but still want to see unexpected Perl warnings directly in the command window.

dieFlag

This is a read-only flag set by "sql_message_handler" or "sql_error_handler", suggesting the caller that execution is to be aborted. If the caller is a routine in MSSQL::Sqllib, the advice is followed.

MSSQL::Sqllib does not care about return status from DB-Library calls, but rely exclusively on dieFlag. As long as you only call MSSQL::Sqllib you have no reason to care about this flag. If you call MSSQL::DBlib directly, but still rely on the handlers in MSSQL::Sqllib, you can use dieFlag to determine whether to abort execution.

maxSeverity

The highest permitted severity level of messages from SQL Server. "sql_message_handler" will set the dieFlag when receiving messsages with severity above maxSeverity. See also neverStopOn and alwaysStopOn. Default: 10.

Note: it appears that SQL Server sends no messages with severities between 1 and 10, but in this case 0 is always used.

maxLibSeverity

The highest permitted severity level of messages from DB-Library. "sql_error_handler" will set the dieFlag when receiving messsages with severity above maxLibSeverity. See also neverStopOn and alwaysStopOn below. Default: 1 (= EXINFO).

Constants for DB-Library severities are defined in MSSQL::DBlib::Const::Severity.

neverStopOn

This is a hash with error numbers as keys. "sql_message_handler" will never set dieFlag for an error if neverStopOn{$msgno} is set. For DB-Library errors, the error numbers are negated. Thus if neverStopOn{-$dberr} is set, "sql_error_handler" will not set dieFlag. neverStopOn overrides maxSeverity and maxLibSeverity.

Constants for DB-Library errors are defined in MSSQL::DBlib::Const::Errors. If follows from the above that you must say:

$sql->{errInfo}{neverStopOn}{-SQLECOFL()}

to prevent the error Data conversion resulted in overflow causing dieFlag to be set. (You must include the parentheses, or else Perl will interpret -SQLECOFL as a bareword.)

By default, neverStopOn includes one entry -10007 (-SQLEMSG). DB-Library error 10007 is General SQL Server error: Check messages from SQL server. This message is generated when SQL Server generates a messages with severity 11 or higher. As "sql_message_handler" takes care of these errors, 10007 is exempted from handling in "sql_error_handler" for your convenience.

alwaysStopOn

This is a hash with error numbers as keys. "sql_message_handler" will always set dieFlag for an error if alwaysStopOn{$msgno} is set. For DB-Library errors, the error numbers are negated. Thus if alwaysStopOn{-$dberr} is set, "sql_error_handler" will set dieFlag. alwaysStopOn overrides maxSeverity and maxLibSeverity.

Empty by default.

printMsg

Controls whether the message information from SQL Server is to be printed by "sql_message_handler". That is, this part:

SQL Server message 411, Severity 16, State 2, Server SOMMERSKOV
Line 1

The value of printMsg relates to a severity level, so that messages from this severity and higher are printed. Default is 1, the message information are printed for all messages but PRINT statments, SHOWPLAN, DBCC information and alike.

See also neverPrint and alwaysPrint.

printText

Controls whether the text portion of an SQL messages. The value of printText relates to a severity level, so that messages from this severity and higher are printed. Default is 0, that is the text of all messages are printed. However. blank lines are never printed.

See also neverPrint and alwaysPrint.

printLines

Controls whether "sql_message_handler" is to print the lines of the offending SQL. The value of printLines relates to a severity level, so that command lines from this severity and higher are printed. The default is 11, the lowest level for error messages.

See also neverPrint and alwaysPrint.

neverPrint

This is a hash with error numbers as keys. If neverPrint{$msgno} has a true value, "sql_message_handler" will print no part of the message regardless of printMsg, printText and printLines.

Likewise "sql_error_handler" will not print a messages if neverPrint{-$dberr} is set. That is, DB-Library error codes should appear with a negative value.

By default the following entires are set: 5701, 5703 and -10007. 5701 is Changed database context... and 5703 is Changed language setting.... These messages are generated on every login, and you rarely want to see them. See neverStopOn for a discussion on DB-Library message 10007.

alwaysPrint

This is a hash with error numbers as keys. If alwaysPrint{$errno} has a true value, "sql_message_handler" will print all parts of the message regardless of printMsg, printText and printLines. By the default the messages 3606, 3607 are flagged. 3606 is Arithmetic overflow occurred, 3607 is Division by zero occurred and 3622 is A domain error occurred. These are raised with severity 0 if you have set none of ARITHABORT or ARITHIGNORE ON. At least this SQL programmer feels that he wants to know on where the problem occurred, even if he didn't think of setting ARITHABORT.

This hash is not applicable to "sql_error_handler".

checkRetStat

Determines whether "sql_sp" is to check return status of the stored procedures it calls. The default it is 1. Unset it, to have "sql_sp" to permit any return values.

retStatOK

This is a hash with permissible return values as keys. The values of the keys must have a true values. For instance if you say

$X->{errInfo}{retStatOK}{'4711'}++;

"sql_sp" will accept 4711 as a return status without aborting even when checkRetStat is in effect. Default: empty.

saveMessages

If you set this flag, "sql_message_handler" will save the messages from SQL Server in the array messages. By default this flag is off.

messages

This is an array of hashes, each hash containing one message, using the following keys:

state     - the state of the messages. -1 means that the message
            comes from DB-Library.
errno     - the number of the SQL message or the DB-Library message.
            (No negation of DB-Library error numbers are performed.)
severity  - the severity of the message.
text      - the text of the message.
proc      - the procedure involved in the error.
line      - the number of the line in the procedure/batch in which
            the error occurred.
oserr     - error code from the operating system, only applicable to
            certain DB-Library errors.
oserrtext - error text accompanying oserr, if any.

The hashes are added at the end of the array by "sql_message_handler" and "sql_error_handler" when saveMessages is set. No routine in MSSQL::Sqllib ever deletes anything from messages.

Restrictions with new datatypes

With MS SQL Server 7, Microsoft abadoned further development of DB-Library, on which MSSQL::Sqllib is based, including adaptions to new features in SQL Server.

The area where is most likely to give cause for concern is new and enhanced datatype. You can always pass a new or enhanced datatype as part of a command string (save the fact that MSSQL::Sqllib cannot handle Unicode data), but you run into to problems if you want to use them as a parameter to a stored procedure, or get them back in a result set. This is a list over known issues.

bigint

This datatype was added in SQL 2000. These values are passed as floats between SQL Server to DB-Library. In practice this means that you can use integers with 14-15 digits of accuracy.

bit

SQL 7 introduced nullable bit columns. If you retrieve a bit value with the value NULL, it will appear as 0 in DB-Library, not as undef. The same applies if you attempt to pass undef for a bit parameter with "sql_sp".

char and varchar

SQL 7 increased the maximum length for char and varchar values from 255 characters to 8000. With MSSQL::Sqllib values longer than 255 characters will be truncated, both in result sets, and when passing parameters with "sql_sp".

SQL 7 introduced the empty string as a possible varchar value. These will appear as strings of one blank in MSSQL::Sqllib. If you attempt to pass the empty string as a parameter value, it will be converted to one blank before being passed to SQL Server.

nchar and nvarchar

These datatypes were introduced in SQL 7 to support Unicode data. For DB-Library they appear just as char/varchar data, and long values will be truncated after 255 characters. Characters outside the range of 0-255 are converted to some fallback character by SQL Server.

ntext

This datatype is not supported at all. SQL Server refuses to return ntext values to legacy clients such as DB-Library.

binary and varbinary

Restrictions analogous to those for char and varchar apply.

uniqueidentifier

When you retrieve such a value in a result set, you will receive the value in a hex string where the bytes have been tossed around. You can use the MSSQL::DBlib function "reformat_uniqueid" to get a properly formatted GUID string.

You can however successfully pass a regular GUID string (e.g. "7223C906-2CF2-11D0-AFB8-00A024A82C78" to a uniqueidentifier parameter, and you can also retrieve a properly formatted GUID string back from an OUTPUT parameter.

sql_variant

This datatype was introduced in SQL 2000. All sql_variant data will be passed as char values to DB-Library clients, and all data you enter through "sql_sp" will have a base type of varchar.

ACKNOWLEDGEMENTS

Michael Peppler included the &sql routine in his Sybperl, a contribution he credits to Gisele Aas. This was the feather from which the Sqllib bird was born.

AUTHOR

Erland Sommarskog <sommar@algonet.se>.