use MSSQL::Sqllib;
use MSSQL::Sqllib qw(:DEFAULT :consts);
use MSSQL::Sqllib qw(:DEFAULT [:resultstyles] [:rowstyles] [:directions] [:returns] [$SQLSEP]);
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. sql_init("", "sa", "", "master");
# Run a query. @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"; }
It is worth noting that while MSSQL::Sqllib is implemented for one certain RDBMS engine, on top of one certain client library, its interface is decently general, and should find no problems to be re-implemented with another client library and also another RDBMS engine.
That being said, we come to the part where I have to say that due to Microsoft's move not to develop DB-Library any further, MSSQL::Sqllib is fairly restricted in what it supports of the novelties in SQL Server 7. See the section SQL Server 7 issues at the end of this document.
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.
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 [, $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 prior to calling sql_init.
The following initializations are performed:
%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''.
eval
.)
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. Thus, in many cases, you don't actually need to take care of 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);
$sql, the only mandatory parameter to 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.
If you don't provide a handle, sql uses the handle created by the first call to sql_init.
For details on how the various data types in SQL Server is returned, see the MSSQL::DBlib routine dbnextrow2.
The data from SQL Server can be structured in several different ways, as
determined by the parameters $rowstyle
and $resultstyle.
$rowstyle
determines how individual rows appears,
$resultstyle
how the rows are structured. 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.
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.
$resultstyle controls how the rows are ``packaged''. There are five possible values:
SINGLESET is the default resultstyle.
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.
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 = 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 value val4 for the key ABC/17/X.
$result = 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 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.
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
.
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 or result set.
$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);
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.
If you don't provide a handle, sql_sp uses the handle created by the first call to sql_init.
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
.
undef
. sql_sp does not consider the attribute dbNullIsUndef.
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; } 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
Now we want to know how many records there are from 1997 and on, of all flavours:
sql_sp('putte_sp', ['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.
[$X->]sql_insert($table, $valref);
sql_insert generates an INSERT statement from a hash and executes it. No data is returned.
If you don't provide a handle, sql_insert uses the handle created by the first call to sql_init.
$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.
$string = [$X->]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
If you provide a handle, it is not really used anyway.
[$X->]sql_begin_trans; [$X->]sql_commit; [$X->]sql_rollback;
These are just a short way of saying
sql("BEGIN TRANSACTION"); sql("COMMIT TRANSACTION"); 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:
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.
(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)
sql_init(undef, "sa", "", "db"); sql_set_conversion("CP850", "iso_1", TO_SERVER_ONLY); @table = sql(<<SQLEND); SELECT * INTO #tbl FROM tbl WHERE 1 = 0 -- Clone the table. SELECT * FROM tbl SQLEND foreach $row (@table) { sql_insert("#tbl", $row); } 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:
sql_set_conversion("ANSI", "CP850", TO_CLIENT_ONLY);
While these two points are more of the author ranting's over Microsoft's insufficient support in this area, you might still find useful technical information here.
The good news is that the Query Analyzer that comes with SQL Server 7, permits you choose how files are to be read and stored from disk.
[$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.
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 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.
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.
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.
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}
-- 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.
Note: it appears that SQL Server sends no messages with severities between 1 and 10, but in this case 0 is always used.
Constants for DB-Library severities are defined in MSSQL::DBlib::Const::Severity.
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 inclued 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.
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.
See also neverPrint and alwaysPrint.
See also neverPrint and alwaysPrint.
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.
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.
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.
Disclaimer: This is what I am aware of. There might be more.
undef
. The same applies if you attempt to pass undef
for a bit parameter with sql_sp.
SQL Server 7 supports varchar values of the empty string. 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.
&sql
routine in his Sybperl, a
contribution he credits to Gisele Aas. This was the feather from which the
Sqllib bird was born.