MSSQL::Sqllib - SQL Library for Microsoft SQL Server from Perl
use MSSQL::Sqllib;
use MSSQL::Sqllib qw(:DEFAULT :consts);
use MSSQL::Sqllib qw(:DEFAULT [:resultstyles] [:rowstyles] [:directions]
[:returns] [$SQLSEP]);
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.
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.
$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. :-)
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.
SCALAR, LIST and HASH. You use them
to choose how the rows in a result set is to be represented.
TO_SERVER_ONLY, TO_CLIENT_ONLY and
TO_SERVER_CLIENT. You use them with the configuration routines
sql_set_conversion and sql_unset_conversion.
RETURN_NEXTROW, RETURN_NEXTQUERY,
RETURN_CANCEL, RETURN_ERROR and RETURN_ABORT. You use them for
return values from callback routines.
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.
$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:
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.
$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:
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.
$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:
$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.
$rowstyle.
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.
$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.
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.
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.
%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.
$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:
\@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 the sql_sp manpage would abort if the return value
was non-zero.
$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:
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.
$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
$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.
$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:
$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:
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.
$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.
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.
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.
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.
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.
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.
tables.
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.
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.
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.
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.
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.
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.
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.
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 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 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{$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{$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.
$X->{errInfo}{retStatOK}{'4711'}++;
sql_sp will accept 4711 as a return status without aborting even
when checkRetStat is in effect. Default: empty.
messages. By default this flag is off.
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.
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.
undef. The same
applies if you attempt to pass undef for a bit parameter 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.
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.
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.
Erland Sommarskog <esquel@sommarskog.se>.