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


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 the mssql-dblib manpage before you call sql_init.

The following initializations are performed:

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

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:

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:

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 the sql_sp manpage 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:

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:

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 the mssql-dblib manpage 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 <esquel@sommarskog.se>.