<HTML>
<HEAD>
<META HTTP-EQUIV="Content-Language" CONTENT="en-gb" />
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=utf-8" />
<STYLE TYPE="text/css">
<!--
h1 {border: 5px solid #000000;
background-color: #DDDDDD;
text-align: center;
}
h2 {border: 2px solid #888888;
background-color: #DDDDDD;
width: 50%;
text-align: left;
}
h3 {border: 1px solid #BBBBBB;
background-color: #E0E0E0;
padding-right: 6pt;
display: inline;
text-align: left;
}
h4 {background-color: #E7E0E0;
display: inline;
text-align: left}
pre {margin-left: 18pt;
background-color: #E7E7E0;
}
td {padding-left: 3px;
padding-right: 3px;}
td.extrapad {padding-right: 20px;}
th {background-color: #CCCCCC;}
.allcaps {font-size: 90%;}
-->
</STYLE>
<SCRIPT TYPE="text/javascript">
<!--
var useragent=navigator.userAgent.toLowerCase();
var is_opera = useragent.indexOf("opera") != -1;
if (is_opera)
document.write('<STYLE TYPE="text/css"> code {font-size: 83%;} </STYLE>')
-->
</SCRIPT>
<TITLE>Win32::SqlServer</TITLE>
</HEAD>
<BODY>
<H1><A NAME="NAME">Name</A></H1>
<P>Win32::SqlServer – Access Microsoft <SMALL CLASS="allcaps">SQL</SMALL> Server from Perl through <SMALL CLASS="allcaps">OLE DB</SMALL>.</P>
<PRE>use Win32::SqlServer;
use Win32::SqlServer qw([<I>explicit_name</I> ...] [:DEFAULT] [:cmdstates] [:consts]
[:datetime] [:directions] [:providers] [:resultstyles]
[:returns] [:routines] [:rowstyles]);</PRE>
<P><I>explicit_name</I> is any importable routine or enumeration constant. For a
complete list of what is importable, including description of export tags, please see the
section <A HREF="#EXPORTTAGS">Exported Names and Export Tags</A>.</P>
<H1>Contents</H1>
<UL STYLE="list-style-type: none;">
<LI><B><A HREF="#OVERVIEW">Overview and Introduction</A></B></LI>
<UL STYLE="list-style-type: none;">
<LI><A HREF="#docstructure">Structure of this Document</A></LI>
<LI><A HREF="#Prerequisites">Prerequisites and Supported Platforms</A></LI>
<LI><A HREF="#functionsummary">Summary of Functions</A></LI>
<LI><A HREF="#introerrorhandling">Error Handling</A></LI>
<LI><A HREF="#quickexamples">Quick Examples</A></LI>
</UL>
<LI><B><A HREF="#datatypes">Data-Type Representation</A></B></LI>
<LI><B><A HREF="#Initroutines">Connection Routines</A></B></LI>
<UL STYLE="list-style-type: none;">
<LI><A HREF="#sql_init"><NOBR>sql_init()</NOBR></A></LI>
<LI><A HREF="#new">new</A></LI>
<LI><A HREF="#setloginproperty"><NOBR>setloginproperty()</NOBR></A></LI>
<LI><A HREF="#connect"><NOBR>connect()</NOBR></A></LI>
<LI><A HREF="#disconnect"><NOBR>disconnect()</NOBR></A></LI>
<LI><A HREF="#isconnected"><NOBR>isconnected()</NOBR></A></LI>
</UL>
<LI><B><A HREF="#High-level">High-Level Query Functions</A></B></LI>
<UL STYLE="list-style-type: none;">
<LI><A HREF="#sql"><NOBR>sql()</NOBR></A></LI>
<LI><A HREF="#sql_one"><NOBR>sql_one()</NOBR></A></LI>
<LI><A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A></LI>
<LI><A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A></LI>
</UL>
<LI><B><A HREF="#Mid-level">Mid-Level Query Functions</A></B></LI>
<UL STYLE="list-style-type: none;">
<LI><A HREF="#initbatch"><NOBR>initbatch()</NOBR></A></LI>
<LI><A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A></LI>
<LI><A HREF="#executebatch"><NOBR>executebatch()</NOBR></A></LI>
<LI><A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A></LI>
<LI><A HREF="#nextrow"><NOBR>nextrow()</NOBR></A></LI>
<LI><A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A></LI>
<LI><A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A></LI>
<LI><A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A></LI>
<LI><A HREF="#cancelbatch"><NOBR>cancelbatch()</NOBR></A></LI>
<LI><A HREF="#getcmdstate"><NOBR>getcmdstate()</NOBR></A></LI>
<LI><A HREF="#midlevelexamples">Mid-Level Functions Examples</A></LI>
</UL>
<LI><B><A HREF="#Conversion_Routines">ANSI/OEM Conversion Routines</A></B></LI>
<UL STYLE="list-style-type: none;">
<LI><A HREF="#sql_set_conversion"><NOBR>sql_set_conversion()</NOBR></A> </LI>
<LI><A HREF="#sql_unset_conversion"><NOBR>sql_unset_conversion()</NOBR></A> </LI>
</UL>
<LI><B><A HREF="#Utility_Routines">Utility Routines</A></B></LI>
<UL STYLE="list-style-type: none;">
<LI><A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A></LI>
<LI><A HREF="#sql_get_command_text"><NOBR>sql_get_command_text()</NOBR></A></LI>
<LI><A HREF="#sql_string"><NOBR>sql_string()</NOBR></A></LI>
<LI><A HREF="#sql_begin_trans"><NOBR>sql_begin_trans(), sql_commit(), sql_rollback()</NOBR></A></LI>
</UL>
<LI><B><A HREF="#OBJECT_PROPERTIES">Object Properties</A></B></LI>
<LI><B><A HREF="#ErrorHandling">Error Handling</A></B></LI>
<LI><B><A HREF="#Misctopics">Miscellaneous Topics</A></B></LI>
<UL STYLE="list-style-type: none;">
<LI><A HREF="#EXPORTTAGS">Exported Names and Export Tags</A></LI>
<LI><A HREF="#SQLLIB">Notes on Migration from MSSQL::Sqllib</A></LI>
<LI><A HREF="#QueryNotification">Using Query Notifications with Win32::SqlServer</A></LI>
<LI><A HREF="#Threads">Using Win32::SqlServer in Threaded Scripts</A></LI>
<LI><A HREF="#BUGS">Bugs and Known Restrictions</A></LI>
</UL>
<LI><B><A HREF="#ACKNOWLEDGEMENTS">Acknowledgements</A></B></LI>
<LI><B><A HREF="#AUTHOR">Author, Copyright and Licence</A></B></LI>
</UL>
<H1><A NAME="OVERVIEW">Overview and Introduction</A></H1>
<P>Win32::SqlServer is a module that permits you to access Microsoft <SMALL CLASS="allcaps">SQL</SMALL> Server
from Perl with full access to all (well, almost) features of <SMALL CLASS="allcaps">MS SQL</SMALL> Server. The
module can use any of the two <SMALL CLASS="allcaps">OLE DB</SMALL> providers <SMALL CLASS="allcaps">SQLOLEDB</SMALL> and <SMALL CLASS="allcaps">SQLNCLI (SQL</SMALL>
Native Client). Win32::SqlServer has a high-level interface that permits you to submit an
<SMALL CLASS="allcaps">SQL</SMALL> statement or call a stored procedure and retrieve the result in one
single function call. There are several options that you can use to specify the
structure of the result set(s) – arrays of rows that are hashes,
arrays or scalars; a hash keyed by the primary key of the result set;
multiple result sets can be flattened out or you can get an array of result
sets.</P>
<P>Win32::SqlServer is intended to be best choice for Perl programmers who need to access <SMALL CLASS="allcaps">MS SQL</SMALL> Server from Windows,
but who have no requirements for portability. With no generic layer in
between, you can expect good performance. May be more important is that the
interface throughout is tailored to the data types and features of <SMALL CLASS="allcaps">SQL</SMALL>
Server. A prime example is error diagnostics: with Win32::SqlServer you get full access to the
error information from <SMALL CLASS="allcaps">SQL</SMALL> Server.</P>
<P>If you have previously used my module
<A HREF="http://www.sommarskog.se/mssqlperl/mssql-sqllib.html"><SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib</A>,
you should know that Win32::SqlServer carries over the same high-level interface,
and as long you have not used any routines from
<A HREF="http://www.sommarskog.se/mssqlperl/mssql-dblib.html"><SMALL CLASS="allcaps">MSSQL</SMALL>::DBlib</A>,
your modules should port with only minor modifications, see the section
<A HREF="#SQLLIB">Notes on Migration from <SMALL CLASS="allcaps">MSSQL::SQLLIB</SMALL></A>. </P>
<P><SMALL>Originally, I released Win32::SqlServer as <SMALL CLASS="allcaps">
MSSQL</SMALL>::OlleDB. However, the friendly people that maintains the Perl
module list, convinced me that the module should be in the Win32 space.
OlleDB was originally an internal working name, but I was not able to think
of anything better. The Olle name still appears in some corners, and if you
work with the <A HREF="Tests.html">test suite</A> or venture into the code,
you may see it in a few places.</P>
<P><SMALL></SMALL>And, oh, a terminology note: <I>method</I> is a popular word in
object-oriented terminology. But being of the old school, having learnt the
object-oriented paradigm though Simula, I prefer to use <I>functions</I> or
simply <I>routines</I>, and those are the words you will mainly see in this
document. </SMALL> </P>
<H2>Note on Documented vs. Undocumented</H2>
<P>While this a long and extensive manual, you will find more routines and
properties if you read the source code. Given the nature of Perl, these will be
accessible to your script if you like. However, <B>any use of undocumented
routines is entirely unsupported</B>, and particularly I feel <B>no obligation
to maintain backwards compatibility</B>. Such routines may be dropped,
renamed or altered between different versions of Win32::SqlServer. Thus stay away, and if
you don't: you have been warned!</P>
<H2><A NAME="docstructure">Structure of this Document</A></H2>
<P>This section gives a high-level overview of Win32::SqlServer for the impatient.
I first detail what <A HREF="#Prerequisites">platforms</A> you can expect
this module to run on. You get a list of the <A HREF="#functionsummary">functions</A>
with their parameters without any details and a
quick list of <A HREF="#propsummary">object properties</A>. I introduce the possibilities for <A HREF="#introerrorhandling">error handling</A>
with Win32::SqlServer. The introductory section closes with a couple of
<A HREF="#quickexamples">quick examples</A>
demonstrating how to access data with Win32::SqlServer's high-level functions.</P>
<P>Next chapter discusses how Win32::SqlServer represents the <A HREF="#datatypes"><SMALL CLASS="allcaps">SQL</SMALL> Server data
types</A> in Perl. This is followed by descriptions of the functions in
Win32::SqlServer. These are split up over no less than five chapters. <A HREF="#Initroutines">Connection functions</A>,
<A HREF="#High-level">High-level query functions</A>. <A HREF="#Mid-level">Mid-level query functions</A>,
<A HREF="#Conversion_Routines">ANSI/OEM Conversion routines</A> an
<A HREF="#Utility_Routines">Utility
routines</A>. After the function descriptions, there is a section that
describes all <A HREF="#OBJECT_PROPERTIES">properties</A> of the
Win32::SqlServer
object. Next chapter is on the important topic of
<A HREF="#ErrorHandling">Error Handling</A>, an area where Win32::SqlServer offers
quite a few knobs.</P>
<P>Then there is a chapter of <A HREF="#Misctopics">Miscellaneous minor topics</A>:
<A HREF="#EXPORTTAGS">exported identifiers and export tags</A>, some notes on
<A HREF="#SQLLIB">migrating code</A> from <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, some information about
<A HREF="#Threads">threaded scripts</A> and the inevitable tale of sorrow
about known <A HREF="#BUGS">bugs and restrictions</A>. Finally, you find
<A HREF="#ACKNOWLEDGEMENTS">acknowledgements</A> and <A HREF="#AUTHOR">author
information</A>.</P>
<H2><A NAME="Prerequisites">Prerequisites and Supported Platforms</A></H2>
<DL>
<DT><B>Hardware</B></DT>
<DD>Unfortunately, there is only a 32-bit version. 64-bit support will come
later.</DD>
<DT> </DT>
<DT><B>Perl</B></DT>
<DD>Perl 5.8.3 or later is required. (This is ActivePerl build 809).</DD>
<DT> </DT>
<DT><B>Operating Systems</B></DT>
<DD>Windows NT4 SP6a, Windows 2000, Windows XP and Windows 2003. The module <I>may</I> run on Windows 95/98/ME, but I am not supporting these platforms. As for
Windows Vista, this will be a supported platform, once it is released. This
module does <I>not</I> run on non-Windows platforms.</DD>
<DT> </DT>
<DT><B>MDAC/SQL Native Client </B></DT>
<DD>Win32::SqlServer can use any of the two providers <SMALL CLASS="allcaps">SQLOLEDB</SMALL> and <SMALL CLASS="allcaps">SQLNCLI</SMALL>. See the
object property <B> <A HREF="#Provider">Provider</A></B> for a discussion of which to
use.
<P>
For <SMALL CLASS="allcaps">SQLOLEDB</SMALL> you need <SMALL CLASS="allcaps">MDAC</SMALL> 2.6 or later. Note that if you have
Windows 2000 without <SMALL CLASS="allcaps">SQL</SMALL> Server, you are likely to have <SMALL CLASS="allcaps">MDAC</SMALL> 2.5 only.
(<SMALL CLASS="allcaps">MDAC</SMALL> 2.6 was released with <SMALL CLASS="allcaps">SQL</SMALL> 2000.) Windows NT does not come
with <SMALL CLASS="allcaps">MDAC</SMALL> at all. You can download the <SMALL CLASS="allcaps">MDAC</SMALL> from
<A HREF="http://www.microsoft.com/downloads/search.aspx">http://download.microsoft.com/</A>.</P>
<P>To use <SMALL CLASS="allcaps">SQLNCLI</SMALL>, you need <SMALL CLASS="allcaps">SQL</SMALL> Native Client which comes with the <SMALL CLASS="allcaps">SQL</SMALL> 2005
as a separate install, which is redistributable. (Notice that <SMALL CLASS="allcaps">SQL</SMALL> Native
Client is still in beta as of this writing.) Your <SMALL CLASS="allcaps">MDAC</SMALL> version does not
matter when you use <SMALL CLASS="allcaps">SQLNCLI</SMALL>.</P></DD>
<DT><B>SQL Server</B></DT>
<DD>All versions from <SMALL CLASS="allcaps">SQL</SMALL> Server 6.5 and on
are supported. </DD>
<DT> </DT>
<DT><B>Compiler</B></DT>
<DD>If you want to compile Win32::SqlServer on your own, you need Visual Studio 2002 or
later. You will also need the include file for <SMALL CLASS="allcaps">SQL</SMALL> Native Client, <I>sqlncli.h</I>.
This file comes with <SMALL CLASS="allcaps">SQL</SMALL> 2005. (If you use any other compiler than
Visual C++, feel to try. I have not tested that though.)
<P>If you have downloaded a binary, you do not need any compiler.</P></DD>
</DL>
<H2><A NAME="functionsummary">Summary of Functions</A></H2>
<P>The functions of Win32::SqlServer fall into five groups: 1) connection
routines. 2) high-level query funcitons. 3) mid-level query functions 4)
conversion routines. 5) utility
routines. Note that many of the parameters are optional. For the sake of
simplicity, I have not
indicated this in the list below. Please see the full function descriptions
for this.</P>
<H3>Connection Routines</H3>
<DL>
<DT><B><CODE>$sqlsrv = Win32::SqlServer->sql_init($server, $user, $pw, $database);</CODE></B></DT>
<DD>Creates an Win32::SqlServer object and connects to the specified server and database.</DD>
<DT> </DT>
<DT><B><CODE>$sqlsrv = Win32::SqlServer->new();</CODE></B></DT>
<DD>Creates an unconnected Win32::SqlServer object.</DD>
<DT> </DT>
<DT><B><CODE>$sqlsrv->setloginproperty($property, $value);</CODE></B></DT>
<DD>Sets a login property such as server, user, network packet size etc.</DD>
<DT> </DT>
<DT><CODE><B>$stats = $sqlsrv->connect();</B></CODE></DT>
<DD>Connects to <SMALL CLASS="allcaps">SQL</SMALL> Server with the login
information <NOBR>set through <A HREF="#setloginproperty">setloginproperty()</A></NOBR>.</DD>
<DT> </DT>
<DT><CODE><B>$sqlsrv->disconnect();</B></CODE></DT>
<DD>Disconnects from <SMALL CLASS="allcaps">SQL</SMALL> Server.</DD>
<DT> </DT>
<DT><CODE><B>$ret = $sqlsrv->isconnected();</B></CODE></DT>
<DD>Reports whether you are connected to <SMALL CLASS="allcaps">SQL</SMALL> Server.</DD>
</DL>
<H3>High-level Query Functions</H3>
<DL>
<DT><CODE><B>$result = $sqlsrv->sql($batch, \@unnamed, \%named, <BR />
$resultstyle, $rowstyle, \@keys);</B></CODE></DT>
<DD>Passes a single query batch to <SMALL CLASS="allcaps">SQL</SMALL> Server and returns a (reference to)
one or more result sets. The batch may have parameters marked with ? or <SMALL CLASS="allcaps">T-SQL</SMALL>
style <NOBR>@variables</NOBR> with values passed in <CODE><NOBR>\@unnamed</NOBR></CODE> and/or
<NOBR><CODE>\%named</CODE></NOBR>. The parameters <CODE>$resultstyle</CODE>,
<CODE>$rowstyle</CODE>
and <NOBR><CODE>\@keys</CODE></NOBR> controls the structure of the returned data.</DD>
<DT> </DT>
<DT><CODE><B>$result = $sqlsrv->sql_one($batch, \@unnamed, \%named, $rowstyle);</B></CODE></DT>
<DD>Like <A HREF="#sql"><NOBR>sql()</NOBR></A>, but has a built-in assertion that <CODE>$batch</CODE> returns
a single row, and <CODE>croaks</CODE> if it does not.</DD>
<DT> </DT>
<DT><CODE><B>$result = $sqlsrv->sql_sp($SP, \$retval, \@unnamed, \%named,
<BR />
$resultstyle, $rowstyle, \@keys);</B></CODE></DT>
<DD>Calls the stored procedure <CODE>$SP</CODE>, else similar to <A HREF="#sql"><NOBR>sql()</NOBR></A>. The
return value from <CODE>$SP</CODE> is returned in <CODE>$retval</CODE>.</DD>
<DT> </DT>
<DT><CODE><B>$sqlsrv->sql_insert($table, \%data);</B></CODE></DT>
<DD>Inserts the data in <NOBR><CODE>\%data</CODE></NOBR> into the table <CODE>$table</CODE>,
using the keys in <NOBR><CODE>\%data</CODE></NOBR> as column names.</DD>
</DL>
<H3>Mid-level Query Access</H3>
<P>The high-level routines are implemented in Perl, and they use the mid-level
functions implemented in C++ to access <SMALL CLASS="allcaps">OLE DB</SMALL>. Occasionally, you may find
that they give you more degrees of freedom. </P>
<DL>
<DT><B><CODE>$sqlsrv->initbatch($cmdtext);</CODE></B></DT>
<DD>Initiates a command batch by setting the command text. When you use the
mid-level routines, you must always start with this function.</DD>
<DT> </DT>
<DT><B><CODE>$status = $sqlsrv->enterparameter($typename, $maxlen, $paramname,
<BR />
$isinput, $isoutput, $value,
<BR />
$prec, $scale, $typeinfo);</CODE></B></DT>
<DD>Describes a parameter in the command batch and passes a value for the
parameter. You must call <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A> once for each parameter in
the command batch.</DD>
<DT> </DT>
<DT><CODE><B>$status = $sqlsrv->executebatch(\$rows_affected);</B></CODE></DT>
<DD>Executes the command batch you defined with <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>,
but does not make any result set active.</DD>
<DT> </DT>
<DT><B><CODE>$more = $sqlsrv->nextresultset(\$rows_affected);</CODE></B></DT>
<DD>Make the next result set active. Returns a false value if there are no
more result sets. You must call this function at least once for the batch.</DD>
<DT> </DT>
<DT><B><CODE>$more = $sqlsrv->nextrow($hashref, $arrayref);</CODE></B></DT>
<DD>Retrieves the next row and places the data in <NOBR><CODE>%$hashref</CODE></NOBR>
and <NOBR><CODE>@$arrayref</CODE></NOBR>. Returns false, if there are no more rows
in the result set. </DD>
<DT> </DT>
<DT><B><CODE>$result = $sqlsrv->get_result_sets($rowstyle, $resultstyle,
\@keys);</CODE></B></DT>
<DD>
Rather than iterating over <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> and <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A>, you can call
<A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A> to get all results in one go.</DD>
<DT> </DT>
<DT><B><CODE>$sqlsrv->getoutputparams($hashref, $arrayref);</CODE></B></DT>
<DD>Retrieves the value of all output parameters (including the return
value from stored procedures) and puts the data into <NOBR><CODE>%$hashref</CODE></NOBR>
and <NOBR><CODE>@$arrayref</CODE></NOBR>. You cannot call <A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A>
until you have retrieved all result sets and all rows.</DD>
<DT> </DT>
<DT><B><CODE>$sqlsrv->cancelbatch();</CODE></B></DT>
<DD>Cancels the current batch, and makes Win32::SqlServer ready to accept a call to
<A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>.</DD>
<DT> </DT>
<DT><B><CODE>$sqlsrv->cancelresultset();</CODE></B></DT>
<DD>Moves to the end of the current result set. You still need to call
<A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> to move to the
next resultset.</DD>
</DL>
<H3>ANSI/OEM Conversion routines</H3>
<DL>
<DT><CODE><B>$sqlsrv->sql_set_conversion($cliend_cs, $server_cs, $direction);</B></CODE></DT>
<DD>Sets up a conversion from one code page to another.</DD>
<DT> </DT>
<DT><CODE><B>$sqlsrv->sql_unset_conversion($direction);</B></CODE></DT>
<DD>Removes an active code-page conversion.</DD>
</DL>
<H3>Utility Routines</H3>
<DL>
<DT><CODE><B>$ret = $sqlsrv->sql_has_errors($keepinfo);</B></CODE></DT>
<DD>Returns whether the command batch or stored procedure generated an error.
Requires that you have set the <B><A HREF="#ErrInfo">ErrInfo</A></B> property <B><A HREF="#SaveMessages">SaveMessages</A></B>.</DD>
<DT> </DT>
<DT><B><CODE>$cmdtext = $sqlsrv->sql_get_command_text();</CODE></B></DT>
<DD>Returns the text for the current command; to be used from a message
handler.</DD>
<DT> </DT>
<DT><B><CODE>$quotedstring = $sqlsrv->sql_string($string);</CODE></B></DT>
<DD>Quotes <CODE>$string</CODE>, doubling any embedded quote characters in
<CODE>$string</CODE> and returns the quoted string.</DD>
<DT> </DT>
<DT><CODE><B>$sqlsrv->sql_begin_trans();</B></CODE></DT>
<DT><CODE><B>$sqlsrv->sql_commit();</B></CODE></DT>
<DT><B><CODE>$sqlsrv->sql_rollback();</CODE></B></DT>
<DD>Issues <SMALL CLASS="allcaps">BEGIN/COMMIT/ROLLBACK TRANSACTION</SMALL>.</DD>
</DL>
<H3><A NAME="propsummary">Summary of Properties</A></H3>
<P>Property names are case-sensitive. However, to be compatible with
<SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, initial lowercase is permitted. (e.g. <I>errInfo</I> is just
as good as <I>ErrInfo</I>).</P>
<P>Win32::SqlServer is a tied hash, and if you refer to undefined property
Win32::SqlServer <CODE>croaks</CODE>.</P>
<DL>
<DT><B>AutoConnect</B></DT>
<DD>When it has a true value, Win32::SqlServer will <NOBR>connect</NOBR> and <NOBR>disconnect</NOBR>
automatically, if you have not connected explicitly.</DD>
<DT> </DT>
<DT><B>BinaryAsStr</B></DT>
<DD>Controls how binary data in <SMALL CLASS="allcaps">SQL</SMALL> Server is represented in Perl, as hex
strings (the default) or binary values. </DD>
<DT> </DT>
<DT><B>CommandTimeout</B></DT>
<DD>How long Win32::SqlServer should wait before timing out on a command. The default
is 0, which means wait forever.</DD>
<DT> </DT>
<DT><B>DateFormat</B></DT>
<DD>Controls how dates are formatted when <B><A HREF="#datetime">DatetimeOption</A></B> has the value
<A HREF="#DATETIME_STRFMT"><SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL></A>. </DD>
<DT> </DT>
<DT><B>DatetimeOption</B></DT>
<DD>Controls how datetime values returned from <SMALL CLASS="allcaps">SQL</SMALL> Server are represented in Perl.
Default is <SMALL CLASS="allcaps">ISO</SMALL> format, <FONT COLOR="#FF0000"> <I><CODE>YYYY-MM-DD HH:MM:SS.fff</CODE></I></FONT>. </DD>
<DT> </DT>
<DT><B>DecimalAsStr</B></DT>
<DD>Controls whether bigint, decimal and money values returned from <SMALL CLASS="allcaps">SQL</SMALL>
Server are represented in Perl. Floating point (the default) or as string.</DD>
<DT> </DT>
<DT><B>ErrInfo</B></DT>
<DD>A complex structure that controls how Win32::SqlServer reacts on (error) messages
from <SMALL CLASS="allcaps">SQL</SMALL> error. See next section for a little more introduction.</DD>
<DT> </DT>
<DT><B>LogHandle</B></DT>
<DD>A file handle to which Win32::SqlServer writes the commands it passes to <SMALL CLASS="allcaps">SQL</SMALL>
Server.</DD>
<DT> </DT>
<DT><B>MsecFormat</B></DT>
<DD>Controls how the milliseconds portion of a datetime value is formatted
when <B><A HREF="#datetime">DatetimeOption</A></B> has the value <A HREF="#DATETIME_STRFMT"><SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL></A>.</DD>
<DT> </DT>
<DT><B>MsgHandler</B></DT>
<DD>A reference to a Perl <CODE>sub</CODE> that is invoked when <SMALL CLASS="allcaps">SQL</SMALL> Server
generates a message
(error or informational). Also invoked for some errors from
the <SMALL CLASS="allcaps">OLE DB</SMALL> provider and Win32::SqlServer itself.
Win32::SqlServer comes with
a default message handler, <NOBR><CODE>Win32::SqlServer::sql_message_handler</CODE></NOBR>. </DD>
<DT> </DT>
<DT><B>NoExec</B></DT>
<DD>Prevents Win32::SqlServer from sending any command batches to <SMALL CLASS="allcaps">SQL</SMALL>
Server. </DD>
<DD>.</DD>
<DT><B>procs</B></DT>
<DD>A hash with parameter profiles for stored procedures, used by <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> as cache.</DD>
<DT> </DT>
<DT><B>PropsDebug</B></DT>
<DD>When set, Win32::SqlServer will dump login properties and their status, in case of
an unhandled error at login.</DD>
<DT> </DT>
<DT><B>Provider</B></DT>
<DD>Selects which provider to use, <SMALL CLASS="allcaps">SQLOLEDB</SMALL> or <SMALL CLASS="allcaps">SQLNCLI</SMALL>. The default is
<SMALL CLASS="allcaps">SQLNCLI</SMALL>, if <SMALL CLASS="allcaps">SQL</SMALL> Native Client is installed, else it's <SMALL CLASS="allcaps">SQLOLEDB</SMALL>.</DD>
<DT> </DT>
<DT><B>QueryNotification</B></DT>
<DD>A hash that specifies parameters for query notification. This feature is
only available with <SMALL CLASS="allcaps">SQL</SMALL> 2005 and <SMALL CLASS="allcaps">SQL</SMALL> Native Client.</DD>
<DT> </DT>
<DT><B>RowsAtATime</B></DT>
<DD>Controls how many rows at time <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> requests from the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. The default is 100.</DD>
<DT> </DT>
<DT><B>SQL_version</B></DT>
<DD>Returns the version number for the <SMALL CLASS="allcaps">SQL</SMALL> Server you are connected to. This
is a read-only property.</DD>
<DT> </DT>
<DT><B>tables</B></DT>
<DD>A hash with table definitions, used as a cache by <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A>.</DD>
<DT> </DT>
<DT><B>UserData</B></DT>
<DD>An entry where you can save you own data in an Win32::SqlServer object.</DD>
</DL>
<H2><A NAME="introerrorhandling">Error Handling</A></H2>
<P>When you call Win32::SqlServer, (error) messages can come from three different sources:</P>
<UL>
<LI><SMALL CLASS="allcaps">SQL</SMALL> Server.</LI>
<LI>The <SMALL CLASS="allcaps">OLE DB</SMALL> Provider.</LI>
<LI>Win32::SqlServer itself.</LI>
</UL>
<P>The prime source for messages is <SMALL CLASS="allcaps">SQL</SMALL> Server, but
Win32::SqlServer fits provider errors and its own errors and warnings into the same
mould. (Errors that are plain abuse of the Perl interface or internal
errors result in the traditional <CODE>croak</CODE>.)</P>
<P>Messages can be informational, warnings or errors, as determined by the
severity level. If the level is 11 or higher, the message is an error
condition, else not. </P>
<P>The default behaviour of Win32::SqlServer is to print all messages. And if
the message is an error condition, Win32::SqlServer <B> <I>aborts execution</I></B> with <CODE>
croak</CODE>. You can trap this through the traditional exception handling in Perl by
calling Win32::SqlServer routines with <CODE>eval</CODE> and then check <CODE>$@</CODE>. </P>
<P>However, the full story is far more sophisticated. When there is a message
from <SMALL CLASS="allcaps">SQL</SMALL> Server, Win32::SqlServer invokes the message handler defined by the
<B><A HREF="#MsgHandler">MsgHandler</A></B> property. That is, you can establish your own callback
routine. Bow, you don't have to do this very often, because
Win32::SqlServer
comes with a built-in default handler, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, and this
handler is highly configurable through the <B><A HREF="#ErrInfo">ErrInfo</A></B> property. Thus, by setting
<B><A HREF="#ErrInfo">ErrInfo</A></B> elements, you can suppress printing, prevent
Win32::SqlServer to abort on
normal users errors (severity ≤ 16) but still abort on fatal errors such as
running out of disk space. You can control printing and abortion by severity
level or by individual message numbers.</P>
<P>By setting the <B><A HREF="#SaveMessages">SaveMessages</A></B> element, you can specify that you want messages
passed back to you in the <B>ErrInfo</B>.<B><A HREF="#Messages">Messages</A></B> array. This is necessary, if you
turn off the default abortion behaviour and you want to check for errors with
the function <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A>.</P>
<P>Say you want to do all error handling yourself, and you do not want
<A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> to print any
messages and never abort execution. Here is how you would do this:</P>
<PRE>$sqlsrv->{ErrInfo}{MaxSeverity} = 19;
$sqlsrv->{ErrInfo}{PrintLines} = 19;
$sqlsrv->{ErrInfo}{PrintMsg} = 19;
$sqlsrv->{ErrInfo}{PrintText} = 19;
$sqlsrv->{ErrInfo}{SaveMessages} = 1;</PRE>
<P>Then to check for errors when you submit a command batch, you would do something
like:</P>
<PRE>delete $sqlsrv->{ErrInfo}{Messages};
$sqlsrv->sql('blafs');
if ($sqlsrv->sql_has_errors()) {
die "there was an error\n";
}</PRE>
<P>There is a full chapter devoted to <A HREF="#ErrorHandling">error handling</A>
with the full details on how you can configure
<A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, or set up your own message handler.. This chapter also includes two
<A HREF="#errorhandlingexamples">example scripts</A> on how to load <SMALL CLASS="allcaps">SQL</SMALL> files
with Win32::SqlServer demonstrating the use of <B><A HREF="#ErrInfo">ErrInfo</A></B>.</P>
<H2><A NAME="quickexamples">Quick Examples</A></H2>
<P>The intention of these examples is to give you a taste of how you work with
the <A HREF="#Initroutines">connection routines</A> and the
<A HREF="#High-level">high-level query functions</A> in Win32::SqlServer. (The chapter
on <A HREF="#Mid-level">mid-level query functions</A> includes some examples
on those functions.) I am not giving a full explanation of all details, but
I'm leaving that for
the description of the functions.</P>
<P>The most of these examples runs in the Northwind database. This database does
not ship with <SMALL CLASS="allcaps">SQL</SMALL> 2005, but you can download it
<A HREF="http://www.microsoft.com/downloads/details.aspx?FamilyId=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en">here</A>.
Alas, Northwind will not install on <SMALL CLASS="allcaps">SQL</SMALL> 6.5.</P>
<H3>Example 1</H3>
<P>Here we run a parameterised query and get back a result set in the default
format. The result set is an array. Each row is then a hash, keyed by column
names.</P>
<PRE>use strict;
use Win32::SqlServer;
# Create object and login in one step to local server with
# integrated security.
my $sqlsrv = sql_init(undef, undef, undef, 'Northwind');
# Our SQL statement. The ? are placeholders for the parameters.
my $stmnt = <<SQLEND;
SELECT OrderID, OrderDate, Freight
FROM Orders
WHERE CustomerID = ?
AND OrderDate > ?
ORDER BY OrderID
SQLEND
# Run query. The return value is a reference to an array. The
# second parameter is an arrayref of parameter descriptions.
my $result = $sqlsrv->sql($stmnt, [['nchar(5)', 'BERGS'],
['datetime', '1997-06-01']]);
# Print results. Each array entry is a hash reference.
foreach my $row (@$result) {
print "OrderID: $$row{OrderID} ";
print "OrderDate: " . substr($$row{OrderDate}, 0, 10), " ";
print "Freight: $$row{Freight}\n";
}</PRE>
<H3>Example 2</H3>
<P>Same query, but this time the parameters are named, and we get the columns
into an array.</P>
<PRE>use strict;
use Win32::SqlServer;
# Create object, set database and then connect with integrated security.
my $sqlsrv = Win32::SqlServer->new;
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();
# Our SQL statement. @ neeeds to be escaped, because this is an SQL @.
my $stmnt = <<SQLEND;
SELECT OrderID, OrderDate, Freight
FROM Orders
WHERE CustomerID = \@custid
AND OrderDate > \@fromdate
ORDER BY OrderID
SQLEND
# This time we receive the result set directly into an array. Since
# we use named parameters, the second parameter is now a hash reference.
my @result = $sqlsrv->sql($stmnt, {'@custid' => ['nchar(5)', 'BERGS'],
'@fromdate' => ['datetime', '1997-06-01']},
Win32::SqlServer::LIST);
# Print results. Each array entry is an array reference.
foreach my $row (@result) {
print "OrderID: $$row[0] ";
print "OrderDate: " . substr($$row[1], 0, 10), " ";
print "Freight: $$row[2]\n";
} </PRE>
<H3>Example 3</H3>
<P>Here is a query that returns a single row, and we can then receive this row
directly into a hash.</P>
<PRE>use strict;
use Win32::SqlServer qw (SINGLEROW);
# Log in to the remote server with SQL authentication.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('Server', 'SVR1');
$sqlsrv->setloginproperty('Username', 'frits');
$sqlsrv->setloginproperty('Password', 'PaSsvvord');
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();
# Our SQL statement. This time the order id is the parameter.
my $stmnt = <<SQLEND;
SELECT CustomerID, OrderDate, Freight
FROM Orders
WHERE OrderID = \@orderid
SQLEND
# Run query. Since the result is a single row, we opt to receive this row
# directly in a hash.
my %order = $sqlsrv->sql($stmnt, {orderid => ['int', 10987]}, SINGLEROW);
# Print results.
print "CustomerID: $order{CustomerID} ";
print "OrderDate: " . substr($order{OrderDate}, 0, 10), " ";
print "Freight: $order{Freight}\n";</PRE>
<H3>Example 4</H3>
<P>Here we list all customer names in a one-column result set. Then we can
receive this column as a scalar value.</P>
<PRE>use strict;
use Win32::SqlServer qw(SCALAR);
# Log in to local server with Windows authentication.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('Database', 'Northwind');
$sqlsrv->connect();
# Our SQL statement. This time there is no parameter.
my $stmnt = <<SQLEND;
SELECT CompanyName
FROM Customers
ORDER BY CompanyName
SQLEND
# Run query. We get a reference to an array with scalar values.
my $result = $sqlsrv->sql($stmnt, SCALAR);
# Print results.
foreach my $name (@$result) {
print "$name\n";
}</PRE>
<H3>Example 5</H3>
<P>In this example, we list the number of orders and total freight per customer. This
time we receive a result set which is keyed by the customer id.</P>
<PRE>use strict;
use Win32::SqlServer qw (:rowstyles :resultstyles); # To get HASH and KEYED.
# Log in to remote server with SQL authentication.
my $sqlsrv = Win32::SqlServer::sql_init(
'SVR1', 'frits', 'PaSsvvord', 'Northwind');
# Our SQL statement.
my $stmnt = <<SQLEND;
SELECT CustomerID, cnt = COUNT(*), totfreight = SUM(Freight)
FROM Orders
GROUP BY CustomerID
SQLEND
# KEYED mandates the result set to be keyed by the data, in this case
# the CustomerID column.
my %customers = $sqlsrv->sql($stmnt, HASH, KEYED, ['CustomerID']);
# Print results. Each hash entry is a hash reference keyed by column name.
foreach my $cust (sort keys %customers) {
print "CustomerID: $cust ";
print "Order count: $customers{$cust}{'cnt'} ";
print "Total freight: $customers{$cust}{'totfreight'}\n";
}</PRE>
<H3>Example 6</H3>
<P>Here is an example of running a stored procedure with a single parameter.
This is also an example on how you can handle a query batch that returns more
than one result set.</P>
<PRE>use strict;
use Win32::SqlServer;
# Log in to local server with SQL authentication. Use tempdb.
my $sqlsrv = Win32::SqlServer::sql_init();
# Run sp_helpdb for tempdb. Note that here we don't specify the data type
# for the parameter; sql_sp looks that up. sp_helpdb returns two result
# set, with different structures. We therefore get the results as an array
# of result sets. Each result set is an array of rows, and rows are hashes,
# keyed by column name.
my $result = $sqlsrv->sql_sp('sp_helpdb', ['tempdb'],
Win32::SqlServer::MULTISET);
# Print results for first result set. For sp_helpdb this is a single row.
foreach my $col (keys %{$$result[0][0]}) {
print "$col: $$result[0][0]{$col}\n";
}
# And print results for the second result set. This is always two rows,
# more if you have secondary data files.
foreach my $file (@{$$result[1]}) {
foreach my $col (keys %$file) {
my $value = $$file{$col};
$value = '<NULL>' if not defined $value;
print "$col: $value\n";
}
print '-' x 50 . "\n";
}</PRE>
<H3>Example 7</H3>
<P>In this last example, we call a procedure with an output parameter that we
retrieve. The example also shows how you can use named and unnamed parameters.</P>
<PRE>use strict;
use Win32::SqlServer;
# Log in to local server with SQL authentication. Stay in tempdb.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->connect();
# First, create a temporary procedure, to use in the example.
$sqlsrv->sql(<<'SQLEND');
CREATE PROCEDURE #ordercnt @custid nchar(5),
@fromdate datetime,
@no_of_orders int OUTPUT AS
SELECT @no_of_orders = COUNT(*)
FROM Northwind..Orders
WHERE CustomerID = @custid
AND OrderDate > @fromdate
SQLEND
# Run this procedure. We can pass positional parameters in an array. For the
# output parameter we pass a reference to a scalar that will receive the value.
my $no_of_orders;
$sqlsrv->sql_sp('#ordercnt', ['BERGS', '1997-06-01', \$no_of_orders]);
print "There are $no_of_orders orders for BERGS later than 1997-06-01.\n";
# We can also use named parameters, by using a hash. The @ is implicit.
$sqlsrv->sql_sp('#ordercnt', {custid => 'ALFKI',
fromdate => '1997-06-01',
no_of_orders => \$no_of_orders});
print "There are $no_of_orders orders for ALFKI later than 1997-06-01.\n";
# We can even mix named and unnamed. And specify @ if we feel like.
$sqlsrv->sql_sp('#ordercnt', ['VINET', '1997-06-01'],
{'@no_of_orders' => \$no_of_orders});
print "There are $no_of_orders orders for VINET later than 1997-06-01.\n";</PRE>
<H3>More Examples</H3>
<P>There are some more examples this document. Under the description of
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, there is <A HREF="#sqlsp_examples">one more example</A> of using this
function. The chapter of the mid-level query functions closes with
<A HREF="#midlevelexamples">two examples</A> of using these functions. And
the chapter on error handling has <A HREF="#errorhandlingexamples">two
examples</A> of loading stored procedures with Win32::SqlServer.</P>
<H1><A NAME="datatypes">Data-Type Representation</A></H1>
<H2><A NAME="perlrecap">Introduction</A></H2>
<P>Note: this chapter appears here, because, well I had to place it somewhere.
You may prefer to first read the function descriptions, and then come back
here when you need to know how Win32::SqlServer handles a certain data type.</P>
<P>At a glance, Perl may appear to be an untyped language, but in fact it is
dynamically typed, and a variable may change data types several time during
its existence. These are the four the main scalar data types in Perl:</P>
<UL>
<LI>Integer number.</LI>
<LI>Floating-point number.</LI>
<LI>String.</LI>
<LI>Reference.</LI>
</UL>
<P><SMALL>(Well, depending on you look at the glass, you may be able to find more
data types. But in the context of Win32::SqlServer, these are the ones we have to
consider.)</SMALL></P>
<P><SMALL CLASS="allcaps">SQL</SMALL> on the other hand has a multitude of data types, and this chapter is
about how Win32::SqlServer maps the <SMALL CLASS="allcaps">SQL</SMALL> data types to Perl values. In some cases, there
is only a single mapping. In other cases, there are Win32::SqlServer properties you can use to control the mapping.</P>
<P>Mapping occurs on both input and output. Input to <SMALL CLASS="allcaps">SQL</SMALL> Server happens
when you pass a Perl value as a parameter to a stored procedure or
parameterised statement with any of the routines <A HREF="#sql"><NOBR>sql()</NOBR></A>,
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, <A HREF="#sql_one"><NOBR>sql_one()</NOBR></A>,
<A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> or
<A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>. (There is also the case when you pass a complete <SMALL CLASS="allcaps">SQL</SMALL>
string where you have interpolated Perl values, but in this case the mapping
happens outside Win32::SqlServer.) </P>
<P>Output from <SMALL CLASS="allcaps">SQL</SMALL> Server can happen in three different ways: 1)
A column in a
result set. 2) An output parameter from a stored procedure. 3) The return
value from a stored procedure or a scalar user-defined function. The
mapping is the same in all three cases.</P>
<P>When converting input data from Perl to <SMALL CLASS="allcaps">SQL</SMALL> Server, this conversion may fail
because the Perl value cannot be mapped to a value of the <SMALL CLASS="allcaps">SQL</SMALL> data type. In
this case, Win32::SqlServer issues a warning message through the <A HREF="#MsgHandler">current message handler</A>.
If you attempt to execute the command
batch, this results in an error. (You do not get an error directly for the
conversion failure, to permit you get information of conversion failures for
all input values.)</P>
<H2><A NAME="NULL">NULL</A></H2>
<P><SMALL CLASS="allcaps">SQL</SMALL>'s special value <SMALL CLASS="allcaps">NULL</SMALL> always maps to Perl's special value <CODE>undef</CODE>,
even though they don't have exactly the same semantics.</P>
<H2><A NAME="integers">bit, tinyint, smallint, int</A></H2>
<H3>Output</H3>
<P>You get an integer number in Perl from <SMALL CLASS="allcaps">SQL</SMALL> values of this type.</P>
<H3>Input</H3>
<P>
The Perl value is auto-converted to an integer number, according to the
standard Perl rules. Thus, a string like <CODE>'9F'</CODE> will be interpreted as 9 and a
string like <CODE>'ABC'</CODE> as 0 (and both these strings will yield Perl's standard
warning <I>Argument "<NOBR><CODE>%s</CODE></NOBR>" isn't number in subroutine entry</I>
if you are running with <CODE>-w</CODE>.)</P>
<H2><A NAME="float">real, float</A></H2>
<H3>Output</H3>
<P>You get a floating-point number in Perl.</P>
<H3>Input</H3>
<P>Input values are auto-converted to floating-point according to the standard
rules in Perl.</P>
<H2><A NAME="bignumbers">bigint, decimal, numeric, <NOBR>(small)money</NOBR></A></H2>
<H3>Output</H3>
<P>You get back the value as a floating-point value or a string, depending on the
boolean property <B>
<A HREF="#DecimalAsStr">DecimalAsStr</A></B>. As the name indicates, when this property has a false
value, you get a floating-point value, else a string. The default for <B>
<A HREF="#DecimalAsStr">DecimalAsStr</A></B> is 0.</P>
<P>With a floating-point value, you may lose precision, whereas with a string
value the exact number is retained. Note however, that if you then go on to
use the number in a numeric expression in Perl, the string will be
auto-converted to floating-point anyway. Receiving the number as a string, is
mainly useful when:</P>
<UL>
<LI>You are going to pass the value as-is as output in a report.</LI>
<LI>You are going to pass the value as-is as input to a parameter of the same
type in some other Win32::SqlServer call.</LI>
<LI>You are using a module like Math::BigInt for your calculations.</LI>
</UL>
<H3>Input</H3>
<P>On input, Win32::SqlServer looks at the Perl data type only and does not consider <B><A HREF="#DecimalAsStr">DecimalAsStr</A></B>.</P>
<P>If the value is not a string, Win32::SqlServer handles it as a floating-point number,
and converts the value to the target type with the <SMALL CLASS="allcaps">OLE DB</SMALL> interface <I>IDataConvert</I>. If the target type cannot fit the
Perl value, the conversion fails and Win32::SqlServer issues a warning message through the <A HREF="#MsgHandler">current
message handler</A>. .</P>
<P>If the value is a string, the string is converted to the target data type,
using <I>IDataConvert</I>. This interface is different from Perl, and the conversion fails if the string
cannot be converted to the target type. <SMALL>
(Curiously though, while a string like <CODE>'9E'</CODE> gives an error for <B><CODE>
decimal</CODE></B> and
<B><CODE>money</CODE></B>, for <CODE><B>bigint</B></CODE> <I>IDataConvert</I> converts this
value without any error to – 0.)</SMALL> </P>
<P>As for the format of the string, well, the short story is: use decimal point
as delimiter and no thousands separators. The longer story is that for <B><CODE>
bigint</CODE></B> and <B><CODE>decimal</CODE></B>/<B><CODE>numeric</CODE></B>
nothing else works. Whereas for <B><CODE>money</CODE></B> and <B><CODE>
smallmoney</CODE></B>, <I>IDataConvert</I>
appears to look at the regional settings, but if a string has only one point and
no other separator
this point is taken as a decimal point, even if point is a thousands separator for
the current regional setting. Thus with regional settings set to Spanish
(Spain), "<CODE>111.999</CODE>" is the decimal number 111.999 (one
hundred and eleven point 999), where as "<CODE>1.111.999</CODE>"
is 1111999 (one million one hundred eleven thousand) and "<CODE>€ 111.999</CODE>" is 111999
(one hundred eleven thousand). You are now warned.</P>
<H2><A NAME="datetime">(small)datetime</A></H2>
<H3>Output</H3>
<P>The property <B><A HREF="#datetime">DatetimeOption</A></B> determines how
Win32::SqlServer converts to the datetime
value. This option can have five different values:</P>
<DL>
<DT><B><A NAME="DATETIME_ISO">DATETIME_ISO</A></B></DT>
<DD>You get a string on the form <NOBR>
<CODE> <FONT COLOR="#DD0000"><I>YYYY-MM-DD HH:MM:SS.fff</I></FONT></CODE></NOBR>.
(For <CODE><B>smalldatetime</B></CODE>, seconds and milliseconds are not
included.) This is the default setting. </DD>
<DT> </DT>
<DT><B><A NAME="DATETIME_REGIONAL">DATETIME_REGIONAL</A></B></DT>
<DD>You get a string that is formatted according to the user's regional
settings. Notice that milliseconds are never included with this format.
Win32::SqlServer uses the Automation routine <I>VarBstrFromDate</I> for the conversion,
and it appears that this function does not include the time portion if this
is 00:00:00.</DD>
<DT> </DT>
<DT><B><A NAME="DATETIME_HASH">DATETIME_HASH</A></B></DT>
<DD>You get a reference to hash with seven keys: <B>Year</B>, <B>Month</B>,
<B>Day</B>, <B>Hour</B>, <B>Minute</B>, <B>Second</B> and <B>Fraction</B>,
each containing that part of the datetime value.</DD>
<DT> </DT>
<DT><B><A NAME="DATETIME_FLOAT">DATETIME_FLOAT</A></B></DT>
<DD>You get a floating-point number that represents the number of fractional
days since 1899-12-30 00:00:00.000. This format can be useful if you need to
pass the datetime value to other <SMALL CLASS="allcaps">COM</SMALL> methods that use this form
to represent datetime values. (Note that down in <SMALL CLASS="allcaps">SQL</SMALL> Server a float value of 0 converts
to 1900-01-01 00:00:00.000.)</DD>
<DT> </DT>
<DT><B><A NAME="DATETIME_STRFMT">DATETIME_STRFMT</A></B></DT>
<DD>You get a string formatted according to the properties <B>
<A HREF="#datetime">DateFormat</A></B> and
<B><A HREF="#datetime">MsecFormat</A></B>. The value of <B><A HREF="#datetime">DateFormat</A></B> is passed to the C run-time
function <I>strftime</I> and controls how all portions of the datetime value
is formatted, save the milliseconds (not supported by <I>strftime</I>). The
value of <B><A HREF="#datetime">MsecFormat</A></B> is passed to <I>sprintf</I> and controls how the
milliseconds are formatted. The default for <B><A HREF="#datetime">DateFormat</A></B> is
<FONT COLOR="#DD0000"><I><CODE><NOBR>%Y%m%d %H:%M:%S</NOBR></CODE></I></FONT>
and for <B><A HREF="#datetime">MsecFormat</A></B> the default is <NOBR><CODE><FONT COLOR="#DD0000"><I>.%3.3d</I></FONT></CODE></NOBR>.
While you can use these properties to get about any format you like, beware
that Win32::SqlServer will never consider this format on input.
<P>
For a complete listing of the available format codes, see a reference for
<I>strftime</I>. Here is a sample of the codes that are likely to be the most
useful:
</P>
<P>
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
<TR><TD><CODE><NOBR>%b</NOBR>, <NOBR>%B</NOBR></CODE></TD><TD>Abbreviated/full month name.</TD></TR>
<TR><TD><CODE><NOBR>%c</NOBR></CODE></TD><TD>Date and time representation
appropriate for locale.</TD></TR>
<TR><TD><CODE><NOBR>%d</NOBR></CODE></TD><TD>Day of month as decimal number (01 –
31)</TD></TR>
<TR><TD><CODE><NOBR>%H</NOBR>, <NOBR>%I</NOBR></CODE></TD><TD>Hour in 24/12-hour format (00 –
23)/(01 - 12)</TD></TR>
<TR><TD><CODE><NOBR>%m</NOBR></CODE></TD><TD>Month as decimal number (01 – 12)</TD></TR>
<TR><TD><CODE><NOBR>%M</NOBR></CODE></TD><TD>Minute as decimal number (00 – 59)</TD></TR>
<TR><TD><CODE><NOBR>%p</NOBR></CODE></TD><TD>Current locale's <SMALL CLASS="allcaps">A.M./P.M</SMALL>. indicator
for 12-hour clock.</TD></TR>
<TR><TD><CODE><NOBR>%S</NOBR></CODE></TD><TD>Second as decimal number (00 – 59)</TD></TR>
<TR><TD><CODE><NOBR>%y</NOBR>, <NOBR>%Y</NOBR></CODE></TD><TD>Year without/with century, as
decimal number.</TD></TR>
<TR><TD><CODE>%%</CODE></TD><TD>Percent sign</TD></TR>
</TABLE>
</P>
<P>Note: do not use codes that involve weekdays, or day number within the
year, as Win32::SqlServer does not supply these values to <I>strftime</I>.</P>
<P>
The main purpose of <A HREF="#DATETIME_STRFMT"><SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL></A> is
to provide compatibility with
<A HREF="http://www.sommarskog.se/mssqlperl/mssql-sqllib.html"><SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib</A>.</P></DD>
</DL>
<P>
These constants are not imported by default. To refer to them, you need
to say e.g. <CODE><NOBR>Win32::SqlServer::DATETIME_ISO</NOBR></CODE> or
import them when you <CODE>use Win32::SqlServer</CODE>, explicitly or with an
<A HREF="#EXPORTTAGS">export tag</A>.
</P>
<H3>Input</H3>
<P>On input, Win32::SqlServer looks at the Perl data type only, and does not consider <B><A HREF="#datetime">DatetimeOption</A></B>.</P>
<DL>
<DT><B>string value</B></DT>
<DD>If the Perl value is a string, Win32::SqlServer first attempts to interpret it as a
date formatted according to <SMALL CLASS="allcaps">ISO</SMALL> 8601. The date part can be formatted as with
delimiters as <FONT COLOR="#DD0000"><CODE><NOBR>YYYY-MM-DD</NOBR></CODE></FONT>
or undelimited <FONT COLOR="#DD0000"><CODE>YYYYMMDD</CODE></FONT>. The date
and time portion can be separated with space or with a T. (The format with T
is produced with format 126 to the <SMALL CLASS="allcaps">T-SQL</SMALL> function <SMALL CLASS="allcaps"><NOBR>CONVERT()</NOBR></SMALL>, and is the
format used in <SMALL CLASS="allcaps">XML</SMALL> documents.) Thus all these formats are legal: <NOBR>
<CODE>"20050730 20:30"</CODE></NOBR>, <NOBR><CODE>"2005-07-30 20:30"</CODE></NOBR>, <NOBR>
<CODE>"2005-07-30T20:30"</CODE></NOBR>.
You can also use Z to terminate a date value without a time portion, for
instance <CODE>"2005-07-30Z"</CODE>. (This also comes from <SMALL>XML</SMALL>.)<P>
If the string does not conform to <SMALL CLASS="allcaps">ISO</SMALL> format,
Win32::SqlServer attempts to interpret
the strings according to regional settings. Note that conversion to regional
settings will fail if the time portion has milliseconds specified.</P>
<P>If the string can neither be interpreted according to regional settings fails, the conversion fails and the
<A HREF="#MsgHandler">current
message handler</A> is invoked. Thus, Win32::SqlServer never considers the
format defined by <B><A HREF="#datetime">DateFormat</A></B> and <B><A HREF="#datetime">MsecFormat</A></B>.
</P></DD>
<DT><B>hash reference</B></DT>
<DD>As a hash produced by Win32::SqlServer when <B><A HREF="#datetime">DatetimeOption</A></B> is <A HREF="#DATETIME_HASH"><SMALL CLASS="allcaps">DATETIME_HASH</SMALL></A>, see
above. <B>Year</B>, <B>Month</B> and <B>Day</B> must be present in the hash,
or else conversion will fail.
The other parts are optional.</DD>
<DT> </DT>
<DT><B>numeric value</B> (integer or floating-point)</DT>
<DD>Number of (fractional) days since 1899-12-30, thus the reverse of <A HREF="#DATETIME_FLOAT"><SMALL CLASS="allcaps">DATETIME_FLOAT</SMALL></A>.</DD>
</DL>
<H2>(n)<A NAME="char">char, (n)varchar, (n)text</A></H2>
<P>For <B><CODE>(n)varchar(MAX)</CODE></B> and <B><CODE>(n)text</CODE></B>, see
also note about <A HREF="#largetypes">large data types</A> below.</P>
<H3>Output</H3>
<P>The <SMALL CLASS="allcaps">SQL</SMALL> value is a placed in a Perl string. If the data type is one of the
Unicode data types (<B><CODE>nchar</CODE></B>, <B><CODE>nvarchar</CODE></B>, <B>
<CODE>ntext</CODE></B>), the return string will always have the <SMALL CLASS="allcaps">UTF</SMALL>-8 bit set,
even if the string contains <SMALL CLASS="allcaps">ASCII</SMALL> characters only. (But this will not matter
to you, as long as you are programming in Perl only. At least
not if my understanding of Perl's handling of Unicode is correct.)</P>
<P><B>Note</B>: if you are using the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider and <NOBR>connect</NOBR> to <SMALL CLASS="allcaps">SQL</SMALL> 2005,
be aware of that output parameters of the types <B><CODE>varchar(MAX)</CODE></B>
and
<CODE><B>nvachar(MAX)</B></CODE>
will be truncated at 8000 and 4000 characters respectively. You cannot call
user-defined functions with a return value of these data types from <SMALL CLASS="allcaps">SQLOLEDB</SMALL>.
</P>
<H3>Input</H3>
<P>The Perl value is auto-converted to a string. If the <SMALL CLASS="allcaps">SQL</SMALL> data type is
any of the non-Unicode data types (<B><CODE>char</CODE></B>, <B><CODE>varchar</CODE></B>,
<B><CODE>text</CODE></B>), and the string is a <SMALL CLASS="allcaps">UTF</SMALL>-8 string with characters
not present in the current <SMALL CLASS="allcaps">ANSI</SMALL> code page,
Win32::SqlServer uses <I>IDataConvert</I> to
coerce the string into the <SMALL CLASS="allcaps">ANSI</SMALL> code page. Thus, if the string is <I>Dvořák</I>,
and the <SMALL CLASS="allcaps">ANSI</SMALL> code page is Latin-1,
Win32::SqlServer will pass <I>Dvorák</I> to <SMALL CLASS="allcaps">SQL</SMALL>
Server. Characters from scripts not covered by the <SMALL CLASS="allcaps">ANSI</SMALL> code page typically
appear as ?, the default fallback character. For instance, <I><SMALL CLASS="allcaps">MOCKBA</SMALL></I> in
Cyrillic letters, results in <I>??????</I> when your <SMALL CLASS="allcaps">ANSI</SMALL> code page is Latin-1.
</P>
<H2><A NAME="binary">binary, varbinary, image</A></H2>
<P>For <B><CODE>varbinary(MAX)</CODE></B> and <B><CODE>image</CODE></B>, see
also note about <A HREF="#largetypes">large data types</A> below.</P>
<H3>Output</H3>
<P>You always receive a binary value from <SMALL CLASS="allcaps">SQL</SMALL> Server in a Perl string. However,
the contents of this Perl string depend on the property <B><A HREF="#binary">BinaryAsStr</A></B>.
This property can have three different settings:</P>
<DL>
<DT><B>false</B></DT>
<DD>The string is the binary proper, which you can interpret with <CODE>unpack</CODE>, write
to a binary file or do whatever you want to do with it.</DD>
<DT> </DT>
<DT><B>Any true value but 'x'</B></DT>
<DD>You get a hex string with no leading 0x. The digits <SMALL CLASS="allcaps">A-F</SMALL> are always in
uppercase.</DD>
<DT> </DT>
<DT><B><CODE>'x'</CODE></B></DT>
<DD>A hex string preceded by 0x.</DD>
</DL>
<P><B>Note</B>: if you are using the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider and <NOBR>connect</NOBR> to <SMALL CLASS="allcaps">SQL</SMALL> 2005,
be aware of that output parameters of the type <B><CODE><CODE></CODE>varbinary(MAX)</CODE></B>
will be truncated at 8000 bytes. You cannot call user-defined functions with
a return value of this data type from <SMALL CLASS="allcaps">SQLOLEDB</SMALL>.
</P>
<H3>Input</H3>
<P>The input value is always auto-converted into a string. How this string
interpreted depends on the <B><A HREF="#binary">BinaryAsStr</A></B> property.</P>
<P>If this property has a false value, the string is passed as-is as a binary
value to <SMALL CLASS="allcaps">SQL</SMALL> Server.</P>
<P>If <B><A HREF="#binary">BinaryAsStr</A></B> has a true value, the string must be a hex string, with
or without leading 0x. Win32::SqlServer attempts to convert the hex string to a binary
value. If the conversion fails, the <A HREF="#MsgHandler">current message handler</A>
is invoked.
</P>
<H2><A NAME="guid">uniqueidentifier</A></H2>
<H3>Output</H3>
<P>Always a string in the traditional <SMALL CLASS="allcaps">GUID</SMALL> representation surrounded by braces,
for instance <CODE><NOBR>{902A1763-561D-4F66-85B1-D18ABE916FE0}</NOBR></CODE>.</P>
<H3>Input</H3>
<P>Must be a Perl string formatted as a <SMALL CLASS="allcaps">GUID</SMALL>. Enclosing braces are permitted but
not required. Other formats causes conversion to fail.</P>
<H2><A NAME="sql_variant">sql_variant</A></H2>
<H3>Output</H3>
<P>On output the <B><CODE>sql_variant</CODE></B> value is handled as the
underlying base type. Thus for a datetime value, Win32::SqlServer
will look at <B><A HREF="#datetime">DatetimeOption</A></B>, for a binary value at <B><A HREF="#binary">BinaryAsStr</A></B>
etc.</P>
<H3>Input</H3>
<P>On input, Win32::SqlServer only supports conversion to some possible base types for the
<SMALL CLASS="allcaps">SQL</SMALL> variant value, more precisely <B><CODE>int</CODE></B>, <B><CODE>float</CODE></B>,
<B>datetime</B>, <B><CODE>varchar</CODE></B> and <B><CODE>nvarchar</CODE></B>.
The conversion depends on the Perl data type of the input value:</P>
<DL>
<DT><B>hash reference</B></DT>
<DD>If the value is a reference to a <A HREF="#DATETIME_HASH">hash</A> that can be
converted to <CODE><B>datetime</B></CODE>, Win32::SqlServer passes this <B><CODE>datetime</CODE></B>
value to <SMALL CLASS="allcaps">SQL</SMALL> Server.</DD>
<DT> </DT>
<DT><B>integer number</B></DT>
<DD>Win32::SqlServer passes the value as an <B><CODE>int</CODE></B>.</DD>
<DT> </DT>
<DT><B>floating-point value</B></DT>
<DD>Win32::SqlServer passes the value as a <B><CODE>float</CODE></B>.</DD>
<DT> </DT>
<DT><B>string with the UTF-8 bit set</B></DT>
<DD>Win32::SqlServer passes the value as <B><CODE>nvarchar</CODE></B>. (Normally,
Perl only sets this bit if there really are characters that do not fit into
the current 8-bit character set. However, as noted above, Win32::SqlServer always set
this bit for output values for <B><CODE>nvarchar</CODE></B>/<B><CODE>nchar</CODE></B>/<B><CODE>ntext</CODE></B>
data.)</DD>
<DT> </DT>
<DT><B>anything else</B></DT>
<DD>Win32::SqlServer passes the value as <B><CODE>varchar</CODE></B>.</DD>
</DL>
<H2><A NAME="xml">xml</A> and FOR XML</H2>
<P>See also note about <A HREF="#largetypes">large data types</A> below.</P>
<H3>Output</H3>
<P>Values of the <B><CODE>xml</CODE></B> data type is passed as Unicode data. That
is as strings, and they will always have the <SMALL CLASS="allcaps">UTF</SMALL>-8 bit set.</P>
<P>Data produced with the <SMALL CLASS="allcaps">FOR XML</SMALL> clause is handled as binary
data, and you will get a binary string or a hex string depending on the
setting of <B>
<A HREF="#binary">BinaryAsStr</A></B>. No, I am not pulling your legs. This is
how <SMALL CLASS="allcaps">SQLOLEDB</SMALL> and <SMALL CLASS="allcaps">SQLNCLI</SMALL> relay <SMALL CLASS="allcaps">FOR XML</SMALL> data. A future version
of Win32::SqlServer may have some setting to handle <SMALL CLASS="allcaps">FOR XML</SMALL> better.</P>
<P><B>Note</B>: when using the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider you cannot call a procedure
through <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, that has an <SMALL CLASS="allcaps">OUTPUT</SMALL>
parameter of the <B><CODE>xml</CODE></B> data type. The same applies scalar
user-defined functions of which the return type is <B><CODE>xml</CODE></B>.</P>
<H3>Input</H3>
<P>The input value must be a string that is a legal <SMALL CLASS="allcaps">XML</SMALL> fragment, or else
conversion fails. As for specifying a schema collection, please see the
description of the functions <A HREF="#sql"><NOBR>sql()</NOBR></A> and
<A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>.</P>
<P>An <SMALL CLASS="allcaps">XML</SMALL> document can include a character-set specification, which always
appears first in the document as in this example:</P>
<PRE><?xml version="1.0" encoding="utf-8"?><ROOT>Text</ROOT></PRE>
<P>Win32::SqlServer inspects an input value of the <B><CODE>xml</CODE></B> data type, to
see if there is a charset specification, to determine how it should pass the
<SMALL CLASS="allcaps">XML</SMALL> document to <SMALL CLASS="allcaps">SQL</SMALL> Server. The following applies:</P>
<UL>
<LI>If there is no charset encoding, Win32::SqlServer assumes that the document is
encoded in <SMALL CLASS="allcaps">UTF-8</SMALL> as per the default in the <SMALL CLASS="allcaps">XML</SMALL> standard.</LI>
<LI>If the encoding is not any of <SMALL CLASS="allcaps">UTF-8, UTF-16</SMALL> or <SMALL CLASS="allcaps">UCS-2</SMALL>,
Win32::SqlServer assumes that
the encoding is an eight-bit or multi-byte encoding and clears any <SMALL CLASS="allcaps">UTF-8</SMALL>
bit for the Perl value, and coerces it into the current <SMALL CLASS="allcaps">ANSI</SMALL> code page, as
discussed for the <A HREF="#char">character</A> data types above. (Thus if
you know you are reading an <SMALL CLASS="allcaps">XML</SMALL> document which has an eight-bit encoding
different from your <SMALL CLASS="allcaps">ANSI</SMALL> page, you should change the encoding to <SMALL CLASS="allcaps">UTF-8</SMALL> or
remove it, before passing it to Win32::SqlServer.)</LI>
<LI>If the encoding is <SMALL CLASS="allcaps">UTF-8</SMALL>, and you are using a log file,
Win32::SqlServer will not
include the encoding specification in the <A HREF="#LogHandle">log file</A>,
as the log file would not be executable then. (Because in <SMALL CLASS="allcaps">T-SQL</SMALL> there is
no data type that can hold values in <SMALL CLASS="allcaps">UTF-8</SMALL>.)</LI>
<LI>If the encoding is <SMALL CLASS="allcaps">UTF-8</SMALL> and you are using the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider,
Win32::SqlServer
will replace the encoding with <SMALL CLASS="allcaps">UCS-2</SMALL> before passing it to <SMALL CLASS="allcaps">SQL</SMALL> Server, as
with <SMALL CLASS="allcaps">SQLOLEDB</SMALL>, Win32::SqlServer must pass the value as <B><CODE>ntext</CODE></B>,
and thus <SMALL CLASS="allcaps">UCS-2</SMALL>.</LI>
</UL>
<H2><A NAME="CLR_UDTs">CLR UDTs</A></H2>
<H3>Output</H3>
<P>The value is handled as a binary value, according to the setting of <B>
<A HREF="#binary">BinaryAsStr</A></B>.</P>
<P><B>Note</B>: when using the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider you cannot call a procedure
through <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, that has an <SMALL CLASS="allcaps">OUTPUT</SMALL>
parameter of some <SMALL CLASS="allcaps">CLR</SMALL> user-defined data type. The same applies scalar
user-defined functions of which the return type is a <SMALL CLASS="allcaps">CLR UDT</SMALL>.</P>
<H3>Input</H3>
<P>The value is handled as a binary value, according to the setting of <B>
<A HREF="#binary">BinaryAsStr</A></B>. Note that the binary value must map to a
proper serialisation of the <SMALL CLASS="allcaps">UDT</SMALL>. See the description of the functions
<A HREF="#sql"><NOBR>sql()</NOBR></A> and <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A> for how
to actually specify the name of a <SMALL CLASS="allcaps">CLR UDT</SMALL>.</P>
<H2><A NAME="largetypes">Large Data Types</A></H2>
<P>Large data types are types that may exceed 8000 bytes, that is <B><CODE>text</CODE></B>,
<B><CODE>ntext</CODE></B>,
<B><CODE>image</CODE></B>, <B><CODE>varchar(MAX)</CODE></B>, <CODE><NOBR><B>
nvarchar(MAX)</B></NOBR></CODE>,
<CODE><NOBR><B>varbinary(MAX)</B></NOBR></CODE> and <B><CODE>xml</CODE></B>.</P>
<P>Win32::SqlServer has no particular support for these data types, but these are handled
as just as any other types. This should be OK for values of moderate size.
However, with values of several megabytes, the performance becomes
unacceptable, and it can take several seconds to retrieve a 10 MB value. </P>
<P>I hope that a future version of Win32::SqlServer will
provide better options to deal with large
values.</P>
<H1><A NAME="Initroutines">Connection Routines</A></H1>
<P>There are two ways to create an Win32::SqlServer object. One is to use
<A HREF="#sql_init"><NOBR>sql_init()</NOBR></A>, which creates an object and connects to the
database in one go. The other way is to use the traditional
<A HREF="#new">new()</A> method, and the use the function <A HREF="#setloginproperty"><NOBR>setloginproperty()</NOBR></A> to set server,
database etc. You then connect with
<NOBR>
<A HREF="#connect">connect()</A> –</NOBR> or you set the property <B>
<A HREF="#AutoConnect">AutoConnect</A></B> which means that Win32::SqlServer connects
and disconnects for
each query.</P>
<H2><A NAME="sql_init"><NOBR>sql_init()</NOBR></A></H2>
<P>Creates an Win32::SqlServer object, connects to <SMALL CLASS="allcaps">SQL</SMALL> Server and returns the object.</P>
<PRE>$sqlsrv = [Win32::SqlServer::]sql_init([$server, [$username,
[$password, [$database]]]]);</PRE>
<DL>
<DT><CODE>$sqlsrv</CODE></DT>
<DD>A newly created Win32::SqlServer object.</DD>
<DT> </DT>
<DT><CODE>$server</CODE></DT>
<DD>The server to connect to. Default is the local server.</DD>
<DT> </DT>
<DT><CODE>$username</CODE></DT>
<DD>User name for <SMALL CLASS="allcaps">SQL</SMALL> authentication. The default is to connect with Windows
Authentication.</DD>
<DT> </DT>
<DT><CODE>$password</CODE></DT>
<DD>Password to use when you also have specified <CODE>$username</CODE>. Ignored when
<CODE>$username</CODE> is <CODE>undef</CODE>.</DD>
<DT> </DT>
<DT><CODE>$database</CODE></DT>
<DD>Database to connect to. Default is tempdb. <I>Thus, the default database for
the login is not honoured</I>.</DD>
</DL>
<P>All parameters are optional, but in order to specify <CODE>$database</CODE>,
you need to specify <CODE>undef</CODE> for the first three.</P>
<P>If the login fails, this aborts execution of the Perl script. You can only
catch this condition with <CODE>eval</CODE>. If you want to handle log in
failures in a <A HREF="#MsgHandler">message handler</A>, create the
Win32::SqlServer object
with <A HREF="#new"><NOBR>new()</NOBR></A> and set <B><A HREF="#ErrInfo">ErrInfo</A></B> elements (or
define <A HREF="#MsgHandler">your own message handler</A>).</P>
<P>Win32::SqlServer saves the first object you create with <A HREF="#sql_init"><NOBR>sql_init()</NOBR></A>, and then uses this a
default handle, so you when say things like:</P>
<PRE>sql('SET NOUCOUNT ON');</PRE>
<P>without any <CODE><NOBR>$sqlsrv-></NOBR></CODE> in front, Win32::SqlServer will use
the default handle. However, this
functionality exists only to provide compatibility with <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, and
is deprecated. If you want to refer to <A HREF="#sql"><NOBR>sql()</NOBR></A>,
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> etc this way, you need to import
them explicitly when you <CODE>use Win32::SqlServer</CODE> or use an <A HREF="#EXPORTTAGS">export tag</A>. Note that it is only the functions of which the name
starts with sql_ that you can use in this way. For <A HREF="#setloginproperty"><NOBR>setloginproperty()</NOBR></A>, <A HREF="#connect"><NOBR>connect()</NOBR></A>,
<A HREF="#disconnect"><NOBR>disconnect()</NOBR></A>, <A HREF="#isconnected"><NOBR>isconnected()</NOBR></A> and the <A HREF="#Mid-level">mid-level query functions</A> you must always specify the
Win32::SqlServer
object, as you must if you need to access an Win32::SqlServer property.</P>
<H2><A NAME="new">new</A></H2>
<P>Creates an un-connected Win32::SqlServer object.</P>
<PRE>$sqlsrv = new Win32::SqlServer;</PRE>
<H2><A NAME="setloginproperty"><NOBR>setloginproperty()</NOBR></A></H2>
<P>Sets a login property such as server, database, packet size etc.</P>
<PRE>$sqlsrv->setloginproperty($property, $value);</PRE>
<DL>
<DT><CODE>$property</CODE></DT>
<DD>The name of a login property. See below for a list of available
properties. Names of login properties are not case-sensitive.</DD>
<DT> </DT>
<DT><CODE>$value</CODE></DT>
<DD>The value to set for the property. The type of the value depends on the
property. </DD>
</DL>
<P>You can only set a login property when you are disconnected. (So you don't
get the idea that they would have effect directly.) If you try to set a
property when you are connected, Win32::SqlServer <CODE>croaks</CODE>. You can check
whether you are connected with <A HREF="#isconnected"><NOBR>isconnected()</NOBR></A>.</P>
<P>Each login property maps to an <SMALL CLASS="allcaps">OLE DB</SMALL> authorisation or initialisation
property, either a general <SMALL CLASS="allcaps">OLE DB</SMALL> property (names starting with
<SMALL CLASS="allcaps">DBPROP_</SMALL>) or
an <SMALL CLASS="allcaps">SQL</SMALL> Server-specific property (names starting with <SMALL CLASS="allcaps">
<SMALL CLASS="allcaps">SSPROP_</SMALL>). I</SMALL> will have to
admit that for some of these, I have only a vague notion of what they are
good for. So I will have to refer you to Books Online for complete
description in several cases. </P>
<P>In Books Online for <SMALL CLASS="allcaps">SQL</SMALL> 2000, you find these properties at <BR />
<I>Building <SMALL CLASS="allcaps">SQL</SMALL> Server Applications <BR />
<SMALL CLASS="allcaps">OLE DB</SMALL> and <SMALL CLASS="allcaps">SQL</SMALL> Server <BR />
Programming <SMALL CLASS="allcaps">OLE DB SQL</SMALL> Server Applications <BR />
Data Source Objects <BR />
Initialization and Authorization Properties</I></P>
<P>In the Books Online for <SMALL CLASS="allcaps">SQL</SMALL> 2005 the location is<BR />
<I><SMALL CLASS="allcaps">SQL</SMALL> Server Programming Reference<BR />
<SMALL CLASS="allcaps">SQL</SMALL> Native Client Programming<BR />
<SMALL CLASS="allcaps">SQL</SMALL> Native Client (<SMALL CLASS="allcaps">OLE DB</SMALL>)<BR />
Data Source Objects <BR />
Initialization and Authorization Properties</I></P>
<P>You can also find information about general properties (those starting with
<SMALL CLASS="allcaps">DBPROP)</SMALL> in the <SMALL CLASS="allcaps">MDAC</SMALL> Books Online, which also is included in <SMALL CLASS="allcaps">MSDN</SMALL> Library.</P>
<P>Not all <SMALL CLASS="allcaps">OLE DB</SMALL> properties are exposed by <A HREF="#setloginproperty"><NOBR>setloginproperty()</NOBR></A>, as not all are
applicable in the context of Win32::SqlServer. Note below that when I say that
Win32::SqlServer does not set a default value for a
property, this means that any default value set by the <SMALL CLASS="allcaps">OLE DB</SMALL> provider
applies. The same applies if you set a property to <CODE>undef</CODE> – this
means that Win32::SqlServer will not set it, leaving room for whatever default value
the provider fancies.</P>
<H3><A NAME="Appname">Appname</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH><NOBR>Data type</NOBR></TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad">
name of your Perl script, excluding directory path</TD>
<TH><NOBR><SMALL CLASS="allcaps">OLE DB</SMALL> Property</NOBR></TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_APPNAME</SMALL></TD></TR>
</TABLE>
</P>
<P> A string that represents the application name, and which can be retrieved
in <SMALL CLASS="allcaps">SQL</SMALL> Server with the <NOBR><SMALL CLASS="allcaps">APP_NAME</SMALL>()</NOBR> function. If you
set it to <CODE>undef</CODE>, <SMALL CLASS="allcaps">OLE DB</SMALL> will apply it's default, which will be
<I>ActivePerl</I> or somesuch.</P>
<H3><A NAME="AttachFilename">AttachFilename</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_FILENAME</SMALL></TD></TR>
</TABLE>
</P>
<P> The name of the primary database file for a database. In case the
database you specify with <B><A HREF="#LogpropDatabase">Database</A></B> is not
available, then <SMALL CLASS="allcaps">SQL</SMALL> Server looks for this file and attaches it. </P>
<H3><A NAME="Autotranslate">Autotranslate</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD><TH>Default</TH><TD CLASS="extrapad">true</TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_AUTOTRANSLATE</SMALL></TD></TR>
</TABLE>
</P>
<P> A boolean property, which, well I'm foggy on the exact effect. Books Online
says <SMALL CLASS="allcaps">"OEM/ANSI</SMALL> character conversion", but I don't think that is correct. I <I>
believe</I> this property comes into play for the <B><CODE>char</CODE></B>, <B>
<CODE>varchar</CODE></B> and <B><CODE>text</CODE></B> data
types when the client's <SMALL CLASS="allcaps">ANSI</SMALL> code page does not match the code page of the
server collation. Anyway, Win32::SqlServer does not set this property, but according to Books Online this property is on by default. </P>
<H3><A NAME="ConnectionString">ConnectionString</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE>
</TD><TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_INIT_PROVIDERSTRING</SMALL></TD></TR>
</TABLE>
</P>
<P>Instead of setting all properties individually, you can set all in one bang
with a connection string, just as they do in other interfaces. If you set this
property, Win32::SqlServer flushes the settings for all other login properties, including
its default values. So, for instance, if you set the <B>ConnectionString</B>
property, but do not include <I>Initial Catalog</I> in the connection string,
the user's default database will apply, not tempdb. If you then go on to set
other login properties with <A HREF="#setloginproperty"><NOBR>setloginproperty()</NOBR></A>, I believe that that these take
precedence over the connection string.</P>
<P>There is one property you should not include in the connection string, and
that is the <I>Provider</I> keyword. To set which provider to use you must use the
Win32::SqlServer property <B><A HREF="#Provider">Provider</A></B>.
</P>
<P>For a full list of available keywords for the connection string, please see
Books Online.</P>
<H3><A NAME="ConnectTimeout">ConnectTimeout</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">integer</TD><TH>Default</TH><TD CLASS="extrapad">15</TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_INIT_TIMEOUT</SMALL></TD></TR>
</TABLE>
</P>
<P>How many seconds Win32::SqlServer should wait before
giving up when trying to connect to <SMALL CLASS="allcaps">SQL</SMALL> Server. (This is not the same as the command timeout,
which you can set with the Win32::SqlServer property <B>
<A HREF="#CommandTimeout">CommandTimeout</A></B>.)</P>
<H3><A NAME="LogpropDatabase">Database</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>"tempdb"</CODE>
</TD><TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_INIT_CATALOG</SMALL></TD></TR>
</TABLE>
</P>
<P> Which database to connect to initially. Note that by Win32::SqlServer sets tempdb
as the initial database. If you want to connect to whichever database that
is registered as the default in <SMALL CLASS="allcaps">SQL</SMALL> Server, set this property to <CODE>undef</CODE>.</P>
<H3><A NAME="Encrypt">Encrypt</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD><TH>Default</TH><TD CLASS="extrapad">false</TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_ENCRYPT</SMALL></TD></TR>
</TABLE>
</P>
<P>Whether the connection is to be encrypted. Please refer to Books Online for
further details. </P>
<H3><A NAME="FailoverPartner">FailoverPartner</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_FAILOVERPARTNER</SMALL></TD></TR>
</TABLE>
</P>
<P>Failover partner for database mirroring. Please see Books Online for a
further discussion about this property. This property is available only with
the <SMALL CLASS="allcaps">SQLNCLI</SMALL> provider and applies to <SMALL CLASS="allcaps">SQL</SMALL> 2005 only.</P>
<H3><A NAME="Hostname">Hostname</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>$ENV{COMPUTERNAME}</CODE>
</TD><TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_WSID</SMALL></TD></TR>
</TABLE>
</P>
<P>A string that represents the machine you are connecting from and which
resurfaces in the <SMALL CLASS="allcaps">SQL</SMALL> function <NOBR><SMALL CLASS="allcaps">HOST_NAME</SMALL>()</NOBR>. </P>
<H3><A NAME="IntegratedSecurity">IntegratedSecurity</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>"SSPI"</CODE>
</TD><TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_AUTH_INTEGRATED</SMALL></TD></TR>
</TABLE>
</P>
<P>Specifies whether Windows Authentication is enabled. As far as I know, "<SMALL CLASS="allcaps">SSPI</SMALL>" is the only
available value. <A HREF="#setloginproperty"><NOBR>setloginproperty()</NOBR></A> accepts the number 1 as the equivalent to
"<SMALL CLASS="allcaps">SSPI</SMALL>". Note that if you set the login
property <B><A HREF="#LogPropUsername">Username</A></B>, Win32::SqlServer automatically
clears <B><A HREF="#IntegratedSecurity">IntegratedSecurity</A></B>. </P>
<H3><A NAME="LogpropLanguage">Language</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_CURRENTLANGUAGE</SMALL></TD></TR>
</TABLE>
</P>
<P>A string that represents an <SMALL CLASS="allcaps">SQL</SMALL> Server language. This property has the same
effect as the <SMALL CLASS="allcaps">SET LANGUAGE</SMALL> command. When you do not set a language, the default language defined
for the server login applies. The property does not work with <SMALL CLASS="allcaps">SQL</SMALL> 6.5. </P>
<H3><A NAME="LCID">LCID</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">integer</TD><TH>Default</TH><TD CLASS="extrapad">
see below.</TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_INIT_LCID</SMALL></TD></TR>
</TABLE>
</P>
<P>The locale ID. My interpretation of Books
Online is that it does not have much effect. Nevertheless, Win32::SqlServer sets a default
value by calling the Windows function <I>GetUserDefault<SMALL CLASS="allcaps">LCID</SMALL></I>.</P>
<H3><A NAME="Netlib">Netlib</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE>
</TD><TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_NETWORKLIBRARY</SMALL></TD></TR>
</TABLE>
</P>
<P>Which network library to use for the connection. Please refer to Books Online
for details.</P>
<H3><A NAME="NetworkAddress">NetworkAddress</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE>
</TD><TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_NETWORKADDRESS</SMALL></TD></TR>
</TABLE>
</P>
<P>A network address for the server defined by the <B>
<A HREF="#LogPropServer">Server</A></B> property. </P>
<H3><A NAME="OldPassword">OldPassword</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE>
</TD><TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_AUTH_OLD_PASSWORD</SMALL></TD></TR>
</TABLE>
</P>
<P>This property is only applicable when you use <SMALL CLASS="allcaps">SQL</SMALL> authentication. When set, it
should be the <I>current</I> password for the <SMALL CLASS="allcaps">SQL</SMALL> login specified by the <B>
<A HREF="#LogPropUsername">Username</A></B> login property. The <B>
<A HREF="#LogpropPassword">Password</A></B> property should in this case the hold the <I>new</I>
password for the <SMALL CLASS="allcaps">SQL</SMALL> login. Thus, you can use this property to change the
password for an <SMALL CLASS="allcaps">SQL</SMALL> login when connecting. This property is available only
with the <SMALL CLASS="allcaps">SQLNCLI</SMALL> provider, and only if you connect to <SMALL CLASS="allcaps">SQL</SMALL> 2005. </P>
<H3><A NAME="PacketSize">PacketSize</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">integer</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_PACKETSIZE</SMALL></TD></TR>
</TABLE>
</P>
<P>A number between 512 and 32767 which sets the network packet size. When
<CODE>undef</CODE>, the default is taken from the server-side configuration parameter <I>
network packet size</I>, which you can set with <I> <B>sp_configure</B></I>.</P>
<H3><A NAME="LogpropPassword">Password</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_AUTH_PASSWORD</SMALL></TD></TR>
</TABLE>
</P>
<P>This property applies only if you also have specified the <B>
<A HREF="#LogPropUsername">Username</A></B> property. It is the password for
that <SMALL CLASS="allcaps">SQL</SMALL> login. See also <B><A HREF="#OldPassword">OldPassword</A></B> on how
to change the password for an <SMALL CLASS="allcaps">SQL</SMALL> login when connecting.</P>
<H3><A NAME="Pooling">Pooling</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD><TH>Default</TH><TD CLASS="extrapad">true</TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_INIT_OLEDBSERVICES</SMALL></TD></TR>
</TABLE>
</P>
<P>Whether connection pooling is
enabled. Connection pooling is handled by <SMALL CLASS="allcaps">OLE DB</SMALL>
core services. With connection pooling on, <A HREF="#disconnect"><NOBR>disconnect()</NOBR></A>
does not really cause the <SMALL CLASS="allcaps">SQL</SMALL> Server connection to be terminated. The same
applies if the Win32::SqlServer object goes out of scope. Instead, the
<SMALL CLASS="allcaps">OLE DB</SMALL> provider retains the connection, and if you reconnect with the same login properties, the
connection is likely to be reused. If there is no reconnection for some time,
normally 60 seconds, <SMALL CLASS="allcaps">OLE DB</SMALL> disconnects. (You cannot change this setting through
Win32::SqlServer, as it is a registry setting. Please refer to the <I>Resource Pooling</I>
topic in the <SMALL CLASS="allcaps">MDAC</SMALL> Books Online. Also in <SMALL CLASS="allcaps">MSDN</SMALL> Library.)</P>
<P>
When this setting is false, connection is actually severed when you call <A HREF="#disconnect"><NOBR>disconnect()</NOBR></A>,
or the Win32::SqlServer object goes out of scope.</P>
<P>
The advantage of connection pooling is that if you connect and disconnect
frequently, for instance because you use the
<B><A HREF="#AutoConnect">AutoConnect</A></B> property, there is no overhead for
this. However, if you
have poor transaction handling, any outstanding transaction on the pooled
connection is not rolled back until the connection is reused or physically
disconnected. This can lead to blocking scenarios. Also, be aware that if you
are using application roles, you <I>cannot</I> use connection pooling.</P>
<P> (Readers well versed <SMALL CLASS="allcaps">in OLE DB</SMALL> may know that <SMALL CLASS="allcaps">DBPROP_INIT_OLEDBSERVICES</SMALL> is in fact a bit mask that controls other <SMALL CLASS="allcaps">OLE DB</SMALL>
services as well. Currently Win32::SqlServer always turns off these services.)
</P>
<H3><A NAME="LogpropPrompt">Prompt</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH><NOBR>Data type</NOBR></TH><TD CLASS="extrapad">integer</TD>
<TH>Default</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROMPT_NOPROMPT</SMALL>(4)</TD>
<TH><NOBR><SMALL CLASS="allcaps">OLE DB</SMALL> Property</NOBR></TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_INIT_PROMPT</SMALL></TD></TR>
</TABLE>
</P>
<P><SMALL CLASS="allcaps">OLE DB</SMALL> can display a small window where the user can
specify login information. This is controlled by <B><A HREF="#LogpropPrompt">Prompt</A>.</B>
This property can take any of these four different values (Text
from the <SMALL CLASS="allcaps">MDAC</SMALL> Books Online):</P>
<UL>
<LI><SMALL CLASS="allcaps">DBPROMPT_PROMPT</SMALL> (1) – Always prompt the user for
initialization information.
</LI>
<LI><SMALL CLASS="allcaps">DBPROMPT_COMPLETE</SMALL> (2) – Prompt the user only if more
information is needed.
</LI>
<LI><SMALL CLASS="allcaps">DBPROMPT_COMPLETEREQUIRED</SMALL> (3) – Prompt the user only if
more information is needed. Do not allow the user to enter optional
information.
</LI>
<LI><SMALL CLASS="allcaps">DBPROMPT_NOPROMPT</SMALL> (4) – Do not prompt the user. </LI>
</UL>
<P>Win32::SqlServer does not declare these constants for you, so you will have to declare
them yourself, or use the numbers.</P>
<H3><A NAME="LogPropServer">Server</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>"(local)"</CODE></TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_INIT_DATASOURCE</SMALL></TD></TR>
</TABLE>
</P>
<P>Which <SMALL CLASS="allcaps">SQL</SMALL> Server instance to connect to. </P>
<H3><A NAME="TrustServerCert">TrustServerCert</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD><TH>Default</TH><TD CLASS="extrapad">false</TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">SSPROP_INIT_TRUST_SERVER_CERTIFICATE</SMALL></TD></TR>
</TABLE>
</P>
<P>Whether you trust the server certificate when you use encryption. Please
refer to Books Online for further details. This property is only available
when you use the <SMALL CLASS="allcaps">SQLNCLI</SMALL> provider, and you connect to <SMALL CLASS="allcaps">SQL</SMALL> 2005. </P>
<H3><A NAME="LogPropUsername">Username</A></H3>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD>
<TH><SMALL CLASS="allcaps">OLE DB</SMALL> Property</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">DBPROP_AUTH_USERID</SMALL></TD></TR>
</TABLE>
</P>
<P>Specifies that you want to log in with <SMALL CLASS="allcaps">SQL</SMALL> authentication
<SMALL CLASS="allcaps">with the given username. </SMALL>When you set this property, the login property <B>
<A HREF="#IntegratedSecurity">IntegratedSecurity</A></B> is automatically
cleared. (Since if both are set, the provider honours integrated security.)
</P>
<H2><A NAME="connect"><NOBR>connect()</NOBR></A></H2>
<P>Connects to <SMALL CLASS="allcaps">SQL</SMALL> Server using the current login properties.</P>
<PRE>$ret = $sqlsrv->connect()</PRE>
<DL>
<DT><CODE>$ret</CODE></DT>
<DD>True if connection succeeded, else false.</DD>
</DL>
<P>It's an error to call this function, if you are already connected. You
can use <A HREF="#isconnected"><NOBR>isconnected()</NOBR></A> to check whether you are connected.</P>
<P>If the login to <SMALL CLASS="allcaps">SQL</SMALL> Server as such fails, <A HREF="#connect"><NOBR>connect()</NOBR></A> invokes the <A HREF="#MsgHandler">current
message handler</A> is invoked. If an error occurs when Win32::SqlServer
calls the <SMALL CLASS="allcaps">OLE DB</SMALL> provider to submit the login properties,
Win32::SqlServer aborts and
prints the error message from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider, but this message may be
<I>very</I> obscure. In this situation, it may help to set the <B>
<A HREF="#PropsDebug">PropsDebug</A></B> property to get a dump of the
current login properties.</P>
<H2><A NAME="disconnect"><NOBR>disconnect()</NOBR></A></H2>
<P>Disconnects from <SMALL CLASS="allcaps">SQL</SMALL> Server and frees up any resources allocated for queries.</P>
<PRE>$sqlsrv->disconnect()</PRE>
<P>Note that if connection pooling is active, the connection to <SMALL CLASS="allcaps">SQL</SMALL> Server is
not physically closed, only returned to the connection pool. See the <B>
<A HREF="#Pooling">Pooling</A></B> login property for a further discussion on
connection pooling.</P>
<P>It's permitted to call this function, even if you are already disconnected. </P>
<P>If an Win32::SqlServer object goes out of scope, it disconnects automatically
from <SMALL CLASS="allcaps">SQL</SMALL> Server.</P>
<H2><A NAME="isconnected"><NOBR>isconnected()</NOBR></A></H2>
<P>Returns whether you are connected to <SMALL CLASS="allcaps">SQL</SMALL> Server or not.</P>
<PRE>$ret = $sqlsrv->isconnected();</PRE>
<DL>
<DT><CODE>$ret</CODE></DT>
<DD>1 if you are currently connected to <SMALL CLASS="allcaps">SQL</SMALL> Server, 0 if you are
disconnected.</DD>
</DL>
<P>This function returns Win32::SqlServer's internal connection state. If the physical
connection somehow was severed – network error, severe <SMALL CLASS="allcaps">SQL</SMALL> Server
error – but Win32::SqlServer has not discovered this yet,
<A HREF="#isconnected"><NOBR>isconnected()</NOBR></A> returns 1.</P>
<P>Note that if even this function returns 0, the connection could still exist
in <SMALL CLASS="allcaps">SQL</SMALL> Server, due to <A HREF="#Pooling">connection pooling</A>. </P>
<H1><A NAME="High-level">High-Level Query Functions</A></H1>
<P>The intention is that 99% of the time, you will be running one of
<A HREF="#sql"><NOBR>sql()</NOBR></A> and <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> to run your queries.
<A HREF="#sql"><NOBR>sql()</NOBR></A> is for batches of one of more <SMALL CLASS="allcaps">SQL</SMALL> commands while <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>
is for running one stored procedure (or a scalar user-defined function).
The two share a common feature: the three parameters <CODE>$rowstyle</CODE>,
<CODE>$resultstyle</CODE> and <NOBR><CODE>\@keys</CODE></NOBR> that you use
to specify how you want the result set(s) from the command batch/stored
procedure to be structured in Perl. This is described in detail in the
section <A HREF="#RowResultStyles">Row Styles and Result Styles</A>
under the topic of <A HREF="#sql"><NOBR>sql()</NOBR></A>. Both functions also accept parameters to the
command batch/stored procedure, but here there is a difference between the
two:
<A HREF="#sql"><NOBR>sql()</NOBR></A> requires you to specify the data types, whereas
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> retrieves the parameter profile from <SMALL CLASS="allcaps">SQL</SMALL> Server about this (but only
once per procedure as it caches this information). This chapter also includes
<A HREF="#sql_one"><NOBR>sql_one()</NOBR></A> which is for queries that should return exactly one row and
<A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> that inserts into a table from hash.</P>
<P>The high-level query functions makes use of Perl's flexible nature, and
permits you leave out most parameters, even in the middle of the
parameter list.</P>
<H2><A NAME="sql"><NOBR>sql()</NOBR></A></H2>
<P>Runs a batch of one or more <SMALL CLASS="allcaps">SQL</SMALL> commands. The batch may be parameterised.</P>
<PRE>($|@|%)result = $sqlsrv->sql($batch
[, \@unnamed_parameters] [, \%named_parameters]
[, $rowstyle] [, $resultstyle [, \@keys]]);</PRE>
<H3>Parameters</H3>
<DL>
<DT><CODE>($|@|%)result</CODE></DT>
<DD>The result set(s) from <CODE>$batch</CODE>. Depending on the <CODE>
$rowstyle</CODE> and
<CODE>$resultstyle</CODE> parameters, this can an array, a hash or a scalar.
If the result set(s) come back as an array or a hash, and you receive the
return value into a scalar, you get a reference to the array/hash. This is
described more in detail in the section <A HREF="#RowResultStyles">Row Styles and Result Styles</A>
below.</DD>
<DT> </DT>
<DT><CODE>$batch</CODE></DT>
<DD>A batch of one or more <SMALL CLASS="allcaps">SQL</SMALL> statements. (Keep in mind that
<SMALL CLASS="allcaps">GO</SMALL>
is not an <SMALL CLASS="allcaps">SQL</SMALL> command, so don't include that in your command batch.)</DD>
<DT> </DT>
<DT><CODE>\@unnamed_parameters</CODE></DT>
<DD>A reference to an array with input for unnamed parameters. Unnamed
parameters appears as <CODE>?</CODE> in <CODE>$batch</CODE>. Each entry in the array is an
inner array with two or three entries. The first entry is the data type of the
parameter, the second is the value for the parameter. The third is used only
when the data type is <B><CODE>xml</CODE></B> or <B><CODE>UDT</CODE></B>, in
which case it specifies an <SMALL CLASS="allcaps">XML</SMALL> schema collection or the name of the <SMALL CLASS="allcaps">UDT</SMALL>. Ser
further the section <A HREF="#parameterspecdetails">Details on the Parameter
Specification</A>.</DD>
<DT> </DT>
<DT><CODE>\%named_parameters</CODE></DT>
<DD>A reference to a hash with input for named parameters. Named parameters
appears as <SMALL CLASS="allcaps">T-SQL</SMALL> variables in <CODE>$batch</CODE>. The key is the parameter
name, and you can specify it with or without the leading <CODE>@</CODE>. Each entry in the
hash is an array as described for the parameter <CODE><NOBR>\@unnamed_parameters</NOBR></CODE>.
You cannot use named parameters with <SMALL CLASS="allcaps">SQL</SMALL> 6.5.</DD>
<DT> </DT>
<DT><CODE>$rowstyle</CODE></DT>
<DD>Specifies how each row in the result is to be represented. Possible
values are <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A> (each row is a hash keyed by column name), <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A> (each
row is an array) and <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A> (each row is a scalar value). See the section
<A HREF="#RowResultStyles">Row Styles and Result Styles</A> below for
details. Default is <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A>.</DD>
<DT> </DT>
<DT><CODE>$resultstyle</CODE></DT>
<DD>Specifies how the result sets are to be structured. Possible values are
<A HREF="#MULTISET"><SMALL CLASS="allcaps">MULTISET</SMALL></A> (an array of result sets), <A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A> (an array of rows), <A HREF="#SINGLEROW"><SMALL CLASS="allcaps">SINGLEROW</SMALL></A>
(a single row according to <CODE>$rowstyle</CODE>), <A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A> (a hash, keyed by
the data in the result set), <A HREF="#NORESULT"><SMALL CLASS="allcaps">NORESULT</SMALL></A> (no result set returned) or a reference
to a callback routine that is to be called for each row. See the section
<A HREF="#RowResultStyles">Row Styles and Result Styles</A> below for
details. Default is <A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A>.</DD>
<DT> </DT>
<DT><CODE>\@keys</CODE></DT>
<DD>This parameter is only applicable when <CODE>$resultstyle</CODE> is
<A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A>, in which case it's mandatory. It specifies which column(s) in the
result set to use for keys.</DD>
</DL>
<H3>Passing Parameters to SQL Batches</H3>
<P>This section starts with two subsections on guidelines, before going into
the reference stuff. If you are in a hurry, you may jump directly to
<A HREF="#unnamednamed">Unnamed and Named Parameters</A>, or even
<A HREF="#parameterspecdetails">Details on the Parameter Specification</A>.</P>
<H4><A NAME="whyparameter">Why Parameterise SQL Commands at All?</A></H4>
<P>There are three reasons 1) simplicity, 2) security and 3) performance.</P>
<P>The first two reasons are actually two angles of the same problem. Say that
you have a script that permits a user to enter a part of a name and the
script looks up matching rows. A simple-minded version would perhaps be:</P>
<PRE>$batch = <<SQLEND;
SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE '$userentry%'
SQLEND
$result = $sqlsrv->sql($batch, Win32::SqlServer::SCALAR);</PRE>
<P>Quick testing indicates that this appears to work. But then a poor user enters <I>Let's</I>. Now the query
dies with a syntax error because of the odd number of single quotes, instead
of returning <I>Let's stop N shop</I>. That was
the poor and innocent user. Next user is outright malicious, and he enters:</P>
<PRE>La'; DROP TABLE Customers --</PRE>
<P>The resulting batch to sent to <SMALL CLASS="allcaps">SQL</SMALL> Server
becomes:</P>
<PRE>SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE 'La'; DROP TABLE Customers -- %'</PRE>
<P>And if this script runs with enough privileges, the table goes away. This
technique is known as <SMALL CLASS="allcaps">SQL</SMALL> injection, and is a common line of attack on web
sites that passes user input to a <SMALL CLASS="allcaps">DBMS</SMALL> (could be any; not just <SMALL CLASS="allcaps">SQL</SMALL>
Server).</P>
<P>As you see, what all the fuzz is about is handling string delimiters in the input data.
You could do something like <CODE>$userentry =~ s/'/''/g</CODE>,
on your own or call <A HREF="#sql_string"><NOBR>sql_string()</NOBR></A> for
the task. But if you
have several string parameters your code gets bulky, not the least if you
need to use <CODE>$userentry</CODE> later in it's original form. This is
where parameterised commands come to the rescue:</P>
<PRE>$batch = <<SQLEND;
SELECT CompanyName
FROM Customers
WHERE CompanyName LIKE ? + '%'
SQLEND
$result = $sqlsrv->sql($batch, [['nvarchar', $userentry]],
Win32::SqlServer::SCALAR);
</PRE>
<P>This will eventually result in a remote procedure call, where <CODE>
$userentry</CODE> will be a parameter and it will not matter if there are any
single quotes in it. Thus, there is no way an intruder can get in here, and
there is no risk a user gets a strange error with some data. </P>
<P>The third reason, as I said is performance. If you submit an ad-hoc batch,
<SMALL CLASS="allcaps">SQL</SMALL> Server attempts to auto-parameterise and save a plan
that can be used for a similar query, but this does not always happen. Thus, it is not unlikely that if the batch
above is executed many times, <SMALL CLASS="allcaps">SQL</SMALL> Server builds a query plan each
time. With a parameterised query, you get what we in Perl-speak
would call an
anonymous stored procedure. The plan for this query is saved in cache, and
will be reused on subsequent invocations. Not the least for <SMALL CLASS="allcaps">INSERTs</SMALL>
that can come in massive bursts, this can have considerable effect on
performance.</P>
<H4><A NAME="whennotparameter">When You Should Not Parameterise</A></H4>
<P>Yes, there are a few cases where you should not use parameterised statements.
An unparameterised command batch is passed to <SMALL CLASS="allcaps">SQL</SMALL> Server as-is, a batch in
the top scope of the connection. (A scope in
<SMALL CLASS="allcaps">T-SQL</SMALL> is a stored procedure, a trigger, a function or a batch of dynamic <SMALL CLASS="allcaps">SQL</SMALL>;
all very reminiscent of Perl.) A parameterised batch, on the other hand, is executed through
<B>
<A HREF="http://www.sommarskog.se/dynamic_sql.html"><I>sp_executesq</I>l</A></B>.
This creates a new scope, which means that if your command batch creates a
temp table, the temp table will be dropped when the batch exits. Thus, if you
create a temp table what you want to reuse in later command batches, you must
use an unparameterised command batch. The same issue applies to <SMALL CLASS="allcaps">SET</SMALL>
commands: the effect of a <SMALL CLASS="allcaps">SET</SMALL> command is reverted when the
scope exits, so if you want, say, <SMALL CLASS="allcaps">SET NOCOUNT ON</SMALL> to have
effect for the rest of connection, you should issue it in an unparameterised
batch.</P>
<P>(Note: if you are on <SMALL CLASS="allcaps">SQL</SMALL> 6.5 the above is not true. There is no <I> <B>
sp_executesql</B></I>
on 6.5, and parameters are expanded before they are passed to <SMALL CLASS="allcaps">SQL</SMALL> Server.)</P>
<P>There is one more case worth mentioning. You cannot parameterise everything.
This does not fly:</P>
<PRE>$result = $sqlsrv->sql("SELECT * FROM ?", [['nvarchar', 'MyTable']]):</PRE>
<P>This results in a syntax error on <SMALL CLASS="allcaps">SQL </SMALL>7 and
earlier. On <SMALL CLASS="allcaps">SQL</SMALL> 2000 and later, you are told
that <CODE><NOBR>@P1</NOBR></CODE> is not declared. This is because you can only use parameters where variables are permitted in the <SMALL CLASS="allcaps">T-SQL</SMALL>
syntax, and <SMALL CLASS="allcaps">T-SQL</SMALL> does not permit you to use a variable for a table
name. (In
fact that would be a table variable, but you cannot pass these as
parameters.)</P>
<H4><A NAME="unnamednamed">Unnamed and Named Parameters</A></H4>
<P>There are two ways to specify a parameter in a command batch: by using <CODE>?</CODE>
as
a parameter placeholders and by using a standard <SMALL CLASS="allcaps">T-SQL</SMALL> parameter name starting with
<CODE>@</CODE>. The former I refer to as unnamed parameters, the latter as
named parameters. Note that if you are on <SMALL CLASS="allcaps">SQL</SMALL> 6.5, you can only use unnamed
parameters. </P>
<P>Win32::SqlServer expands the <CODE>?</CODE> to <CODE><NOBR>@P1</NOBR></CODE>, <CODE><NOBR>@P2</NOBR></CODE>, <CODE><NOBR>@P3</NOBR></CODE> etc.
However, <CODE>?</CODE>
in string literals, quoted names and comments are not expanded. Here is a
contrived example:</P>
<PRE>SELECT ?, '?' AS x FROM [Questions?] WHERE col = ? -- eh?</PRE>
<P>This command batch is expanded to:</P>
<PRE>SELECT @P1, '?' AS x FROM [Questions?] WHERE col = @P2 -- eh?</PRE>
<P>The parameter <CODE><NOBR>
\@unnamed_parameters</NOBR></CODE>
is really just a shortcut for entries in <NOBR><CODE>\%named_parameters</CODE></NOBR>
where the keys are <CODE><NOBR>@P1</NOBR></CODE>, <CODE><NOBR>@P2</NOBR></CODE>
etc. From this follows, that it's perfectly possible to mix parameter
placeholders and named parameters in a command batch. </P>
<P>Parameter placeholders are convenient for single occurrences in short batches,
but if you need to refer to the same parameter in the same command batch,
named parameters are easier to use.</P>
<PRE>SELECT col1, col2 FROM tbl WHERE last_name = @name OR first_name = @name</PRE>
<P>Had you used <CODE><CODE>?</CODE></CODE> here, you would in fact have had two
parameters and not one. Named parameters are also more palatable when you have very many parameters. That <CODE>?</CODE> over
there, is that
<CODE><NOBR>@P5</NOBR></CODE> or <CODE><NOBR>@P6</NOBR></CODE>? </P>
<P>When you construct <CODE><NOBR>%named_parameters</NOBR></CODE>, you can leave
out the <CODE>@</CODE> from the parameter names; Win32::SqlServer will add these. This
saves you some typing. Compare:</P>
<PRE>{'@myparam' => ['nchar', $myvalue]}</PRE>
<P>with</P>
<PRE>{myparam => ['nchar', $myvalue]}</PRE>
<P><SMALL>(Win32::SqlServer could have left it to the OLE DB provider to expand the <CODE>
?</CODE>, but this only happens on SQL 6.5. There are two reasons why
Win32::SqlServer expands <CODE>
<CODE>?</CODE></CODE> itself on SQL 7 and later: 1) to support named
parameters. 2) The error message from the OLE DB provider when
you have errors with the parameters range from the vague and imprecise
to the completely opaque and impenetrable ones. Win32::SqlServer's error messages are
hopefully more informative.)</SMALL></P>
<H4><A NAME="parameterspecdetails">Details on the Parameter Specification</A></H4>
<P>An entry in <CODE><NOBR>\@unnamed_parameters</NOBR></CODE> and <CODE>
<NOBR>\%named_parameters</NOBR></CODE> can be either a scalar value or a short
array with two or three elements. If the entry is a scalar value, this is
the value for the parameter. But this is only meaningful in one case: you
want to pass <CODE>undef</CODE> (that is <SMALL CLASS="allcaps">NULL</SMALL>)
for the parameter. If you supply any other value, Win32::SqlServer applies a default data type of <CODE>
<B>char</B></CODE> and at the same time issue a warning through the <A HREF="#MsgHandler">current message handler</A>
if Perl warnings are enabled.</P>
<P>From this follows that in the normal case you will need to pass the short arrays. Here are the entries.</P>
<DL>
<DT><B>Index 0 – data type</B></DT>
<DD>The data type for the parameter. This must be a system type; you cannot
use a user-defined type. Nor can you use any of the data-type synonyms that
ships with <SMALL CLASS="allcaps">SQL</SMALL> Server, e.g. <B><CODE>integer</CODE></B> for <B><CODE>int</CODE></B>, with
one exception: you can use <B><CODE>rowversion</CODE></B> for <B><CODE>
timestamp</CODE></B>. For an alias data type (those defined with
<I>sp_addtype</I> or <SMALL CLASS="allcaps">CREATE TYPE FROM</SMALL>), use the underlying
base type. For <SMALL CLASS="allcaps">CLR</SMALL> user-defined types, use <B><CODE>UDT</CODE></B>. See
further remarks per data type below. The type name is case-insensitive.</DD>
<DT> </DT>
<DT><B>Index 1 – the value</B></DT>
<DD>The value for the parameter. See the section
<A HREF="#datatypes">Data-type Representation</A> how to specify the values for different data
types.</DD>
<DT> </DT>
<DT><B>Index 2 – extra type info</B></DT>
<DD>Applies only if the data type is <B><CODE>xml</CODE></B> or <B><CODE>UDT</CODE></B>.
See further under these data types below.</DD>
</DL>
<P>Here are remarks for specific data types that may require extra information
beside the type name.</P>
<DL>
<DT><B><CODE>char</CODE>, <CODE>varchar</CODE>, <CODE>nchar</CODE>, <CODE>
nvarchar</CODE>, <CODE>binary</CODE>, <CODE>varbinary</CODE></B></DT>
<DD>You can enter a complete specification, e.g. <B><CODE><NOBR>varchar(20)</NOBR></CODE></B>,
or just the type name, e.g. <B><CODE>varchar</CODE></B>. In the latter case,
Win32::SqlServer will infer the length from the actual value. Thus if you say <B><CODE>
nchar</CODE></B> and the value is <SMALL CLASS="allcaps">ALFKI</SMALL>,
Win32::SqlServer will use <B>
<CODE><NOBR>nchar(5)</NOBR></CODE></B>. (If the value is an empty string,
Win32::SqlServer will use a length of 1.)
<P>If the length of the value exceeds what the <SMALL CLASS="allcaps">SQL</SMALL> Server version supports,
Win32::SqlServer
uses the appropriate max value. That is, 255 for <SMALL CLASS="allcaps">SQL</SMALL> 6.5, 4000/8000 for <SMALL CLASS="allcaps">SQL</SMALL> 7 and
<SMALL CLASS="allcaps">SQL</SMALL> 2000. On <SMALL CLASS="allcaps">SQL</SMALL> 2005,
Win32::SqlServer will infer <B>
<CODE>(MAX)</CODE></B>.</P>
<P><B>Note:</B> While this is convenient, each new length of the type will
result in a new entry in the <SMALL CLASS="allcaps">SQL</SMALL> Server cache. So best practice is to
specify the length, particularly if you run this from a loop.</P></DD>
<DT><B><CODE>decimal</CODE></B>, <B><CODE>numeric</CODE></B></DT>
<DD>If you leave out precision and/or scale, Win32::SqlServer will apply the defaults
18 and 0, respectively and at the same time pass a warning through the
<A HREF="#MsgHandler">current message handler</A> unless the value is <CODE>undef</CODE>. That
is, best practice is to always supply precision and scale.</DD>
<DT> </DT>
<DT><CODE><B>UDT</B></CODE></DT>
<DD>You must specify the name of actual <SMALL CLASS="allcaps">UDT</SMALL> in one of these two ways:
in parentheses after <SMALL CLASS="allcaps">UDT</SMALL> in the first array entry, or separately in
the third entry (at index 2, "extra type info"). That is, your parameter entry
can read any of:
<PRE>['UDT(dbo.ComplexNumber)', $cmplx]
['UDT', $cmplx, 'dbo.ComplexNumber']</PRE>
You must use the latter method, if the type name includes special characters,
notably then parentheses.
<P>Whatever the method, you need to quote any part of
the name that is not a regular <SMALL CLASS="allcaps">T-SQL</SMALL> identifier
with brackets or double quotes. For instance:: </P>
<PRE>['UDT([My Database].MySchema.[Your UDT])', $value)]
['UDT', $value, '[My Database].MySchema.[Your UDT]']
['UDT', $value, '[This-Type]']
['UDT("This-Type")', $value] </PRE>
</DD>
<DT><B><CODE>xml</CODE></B></DT>
<DD>For <B><CODE>xml</CODE></B>, you can specify an <SMALL CLASS="allcaps">XML</SMALL> schema collection.
The mechanism is the same as for specifying a type name for <B><CODE>UDT</CODE></B>,
with one single difference: it's optional to specify a schema collection.
Thus, all these are fine:
<PRE>['xml', $myxmldoc] # Untyped xml
['xml(my_schema_collection)', $value)] # Typed xml
['xml', $myxmldoc, 'my_schema_collection'] # Ditto
['xml', $mydoc, '[my-schema-collection]'] # Must quote this name.</PRE>
</DD>
</DL>
<H4>Examples Anyone?</H4>
<P>By now, I can hear the reader screaming for example of all this mess. OK, so
I'm lazy and point you to the <A HREF="#quickexamples">Quick Examples</A>
earlier in the document. One conclusion you can draw from the examples is
that it's perfectly normal to use anonymous arrays and hashes to pass the
parameter information.</P>
<P>And, one thing I have not said elsewhere: you cannot use output parameters
with <A HREF="#sql"><NOBR>sql()</NOBR></A>.</P>
<H3><A NAME="RowResultStyles">Row Styles and Result Styles</A></H3>
<P>The parameters <CODE>$rowstyle</CODE>, <CODE>$resultstyle</CODE> and <CODE>
<NOBR>\@keys</NOBR></CODE> are all about shaping the result set(s) into the fashion you
want to digest the data from Perl. The styles are denoted by constants that
are not imported by default. You can either refer to them with the long name, for instance
<NOBR> <CODE>
Win32::SqlServer::HASH</CODE></NOBR>. You can also import them when you say <CODE>use
Win32::SqlServer</CODE>, either by explicit name or by using an appropriate
<A HREF="#EXPORTTAGS">export tag</A>.</P>
<P>By the way, if you don't remember in which order <CODE>$rowstyle</CODE> and
<CODE>$resultstyle</CODE> come in the parameter list, don't worry.
Win32::SqlServer can
tell them apart, and you can specify them in any order. (<CODE><NOBR>\@keys</NOBR></CODE> must always be
last, though.)</P>
<P>The row style constants are fairly easy to get a grip of, so we start with
these.</P>
<DL>
<DT><B><A NAME="HASH">HASH</A></B> (the default)</DT>
<DD>Each row appears as a hash, keyed by column name. Win32::SqlServer names columns without
name as <I>Col 1</I>, <I>Col 2</I> etc. If the same column
appears more than once in the result set, you will get a warning if
Perl warnings are enabled.</DD>
<DT> </DT>
<DT><B><A NAME="LIST">LIST</A></B></DT>
<DD>This gives you an array with the columns appearing in the order as they
appear in the query, with the first column at index 0.</DD>
<DT> </DT>
<DT><B><A NAME="SCALAR">SCALAR</A></B></DT>
<DD>Each row is returned as a scalar value. As you may guess, <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A> is
intended for one-column result sets. But if result set has more than one
column, you get one string for the entire row with the column values are separated by
the global Win32::SqlServer variable, <CODE><NOBR>$Win32::SqlServer::SQLSEP</NOBR></CODE>.
The default value for this variable is <CODE><NOBR>\022</NOBR></CODE>, a
control character. Note that if you change it, it affects all
Win32::SqlServer objects.
</DD>
</DL>
<P><B>Note</B>: above when I talk about "all columns", this is really "all
non-key columns" in the case the result style is <A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A>.</P>
<P>The result styles are more to the number, and may also be more bewildering
at first sight.</P>
<DL>
<DT><B><A NAME="SINGLESET">SINGLESET</A></B> (the default)</DT>
<DD>The return value is an array of rows, or – if you receive the return
value as a scalar – a reference to an array of rows. Each array entry
is a reference to a hash, a reference to an array or a scalar value depending
on the value of <CODE>$rowstyle</CODE>. The order of the rows is the same as
the order served by <SMALL CLASS="allcaps">SQL</SMALL> Server. (Keep in mind that if you want a certain
order, you must use <SMALL CLASS="allcaps">ORDER BY</SMALL>, else <SMALL CLASS="allcaps">SQL</SMALL> Server is free to use
any order.)
<P><A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A> is intended for the
common case when a command batch returns only one result set. However, if the command batch returns several result sets,
they are all included in the array, and it's up to you to find the boundaries
between them.</P>
<P>Say that you have this command call to <A HREF="#sql"><NOBR>sql()</NOBR></A>:</P>
<PRE>@result = $sqlsrv->sql(<<SQLEND, {orderid => ['int', $orderid]});
SELECT OrderDate, CustomerID, EmployeeID, ShippedDate
FROM Orders
WHERE OrderID = \@orderid
SELECT ProductID, UnitPrice, Quantity, Discount
FROM [Order Details]
WHERE OrderID = \@orderid
ORDER BY ProductID
SQLEND</PRE>
<P>To refer to the customer ID, you would say <CODE><NOBR>$result[0]{CustomerID}</NOBR></CODE>.
To refer to the Product IDs, you would say <CODE><NOBR>$result[$i]{ProductID}</NOBR></CODE>
where <CODE>$i</CODE> starts at 1 and goes to <CODE><NOBR>$#result</NOBR></CODE>.</P>
<P>If you instead say: </P>
<PRE>$result = $sqlsrv->sql(<<SQLEND, ...</PRE>
<P>you refer to the customer
ID as <CODE><NOBR>$$result[0]{CustomerID}</NOBR></CODE>, as you now get a reference to
an array.</P>
<P>If we change the row style to <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A>, the customer ID is now found at <NOBR><CODE>$result[0][1]</CODE></NOBR>,
and the ProductIDs at <CODE><NOBR>$result[$i]</NOBR>[0]</CODE> where <CODE>
$i</CODE> is as above. (Or <CODE><NOBR>$$result[0][1]</NOBR></CODE> and
<CODE><NOBR>$$result[$i][0]</NOBR></CODE>, if we receive the return value
as <CODE>$result = </CODE><NOBR><CODE>$sqlsrv->sql(...</CODE>)</NOBR></P></DD>
<DT><B><A NAME="MULTISET">MULTISET</A></B></DT>
<DD>You get an array of a result sets, or – if you receive the return value
as a scalar – a reference to an array of result sets. Each array entry
is a reference to an array of rows. Note that empty result sets will be
retained and appear as empty arrays. Using the same example as for <A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A>
above, but throwing in a result style of <A HREF="#MULTISET"><SMALL CLASS="allcaps">MULTISET</SMALL></A>, the customer ID is now at <CODE><NOBR>
$result[0][0]{CustomerID}</NOBR></CODE>, or <CODE>
<NOBR>$$result[0][0]{CustomerID}</NOBR></CODE> when receiving the result as a
reference to an array. The ProductID is at <CODE><NOBR>
$result[1][$i]{ProductID}</NOBR></CODE> where <CODE>$i</CODE> now goes from 0 to
<CODE><NOBR>$#{$result[1]}</NOBR></CODE>.
<P><SMALL>(If you as a reader feel overwhelmed by all these combinations of
<CODE>$</CODE>,
brackets and braces, you have my sympathy. However, I am not going into
details to explain what all that comes from, as this is a reference for a
Perl module, not on text on Perl itself. I can recommend thorough study of
chapter 4 of the second edition of the Camel book, or similar material if
you are not acquainted with arrays of arrays, hashes of hashes etc.)</SMALL></P></DD>
<DT><B><A NAME="SINGLEROW">SINGLEROW</A></B></DT>
<DD>The return value is supposed to be a single row. If you specify <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A>
for <CODE>$rowstyle</CODE>, the return value is thus a hash keyed by column
names, or – if you
receive the return value as a scalar – a reference to a hash. If <CODE>$rowstyle</CODE> is <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A>, you get an
array, or a reference to an array. And if <CODE>$rowstyle</CODE> is <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A> you
get the scalar value (but not a reference to it!). Would the command batch
return more than one row, Win32::SqlServer just keeps on adding the data and it may or
may not make sense. (There is also <A HREF="#sql_one"><NOBR>sql_one()</NOBR></A>
that returns exactly one row.)
<P>
We modify the example above somewhat:</P>
<PRE>$batch = <<SQLEND;
SELECT OrderDate, CustomerID, EmployeeID, ShippedDate
FROM Orders
WHERE OrderID = \@orderid
SQLEND
%result = $sqlsrv->sql($batch, {orderid => ['int', $orderid]},
SINGLEROW);</PRE>The customer ID is at <NOBR> <CODE>$result{CustomerID}</CODE>.</NOBR> When getting the result into <CODE>$result</CODE>
we find it at <CODE><NOBR>
$$result{CustomerID}</NOBR></CODE>. With row style <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A>, you
would say:
<PRE>$batch = <<SQLEND;
SELECT OrderDate, CustomerID, EmployeeID, ShippedDate
FROM Orders
WHERE OrderID = \@orderid
SQLEND
@result = $sqlsrv->sql($batch, {orderid => ['int', $orderid]},
LIST, SINGLEROW);</PRE>
You refer to the CustomerID as <CODE><NOBR>$result[1]</NOBR></CODE> or <CODE><NOBR>$$result[1]</NOBR></CODE>
in the reference case. And with <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A>? Here is an example:
<PRE>$batch = <<SQLEND;
SELECT COUNT(*) FROM [Order Details] WHERE OrderID = \@orderid
SQLEND
$count = $sqlsrv->sql($batch, {orderid => ['int', $orderid]},
SCALAR, SINGLEROW);</PRE>The reader may be appalled by the fact that this even looks simple and
understandable.
</DD>
<DT> </DT>
<DT><B><A NAME="KEYED">KEYED</A></B></DT>
<DD>The return value when you use <A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A> is a hash, or – if you receive the
return value as a scalar – a reference to a hash. This hash is keyed by
the <I>data</I> in the result set. The value of each hash is the non-key
rows, and they appear as a reference to a hash, a reference to an array or as
scalar value depending on <CODE>$rowstyle</CODE>.
<P>
When you use <A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A>, you must specify <CODE><NOBR>\@keys</NOBR></CODE> to
specify the keys in the result set. For the <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A> row style you refer to them by column
name, for <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A> and <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A>, you refer to them by column number, with the first
column at number 1. Here is an example:</P>
<PRE>$batch = <<SQLEND;
SELECT ProductID, UnitPrice, Quantity, Discount
FROM [Order Details]
WHERE OrderID = \@orderid
SQLEND
%result = $sqlsrv->sql($batch, {orderid => ['int', 11000]},
HASH, KEYED, ['ProductID']);</PRE>
To get the UnitPrice for product 77 on order 11000, you would say <CODE><NOBR>
$result{'77'}{UnitPrice}</NOBR></CODE>. As you see in the example, passing <CODE>
<NOBR>\@keys</NOBR></CODE> as an anonymous array is a normal thing to do.
<P>
Here is an example with the <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A> row style and a two-column key:</P>
<PRE>$batch = <<SQLEND;
SELECT O.CustomerID, OD.ProductID, OD.UnitPrice, OD.Quantity,
OD.Discount
FROM Orders O
JOIN [Order Details] OD ON O.OrderID = OD.OrderID
WHERE O.OrderID = \@orderid
SQLEND
$result = $sqlsrv->sql($batch, {orderid => ['int', 11000]},
LIST, KEYED, [1, 2]);</PRE>Now the unit price for product 77 is at
<CODE><NOBR>$$result{RATTC}{'77'}[0]</NOBR></CODE>. (<SMALL CLASS="allcaps">RATTC</SMALL> is the customer on
order 11000). The customer ID is the first since we in <CODE><NOBR>\@keys</NOBR></CODE>
said 1 before 2. The index for UnitPrice is 0, because UnitPrice is the first
non-key column.
<P>The keys you provide in <CODE><NOBR>\@keys</NOBR></CODE> 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 <CODE><NOBR>\@keys</NOBR></CODE> does not appear in one of the rows, this
is an error, and Win32::SqlServer will <CODE>croak</CODE>.
If a key value appears more than once in the result, Win32::SqlServer emits a
warning if Perl warnings are activated. It is undefined which values that
end up in the result set. Finally, if there are <SMALL CLASS="allcaps">NULL</SMALL> values in your key
columns, they wind up as <CODE>undef</CODE> on the Perl side, and Perl
will give you warnings about this. You may prefer to use
<SMALL CLASS="allcaps"><NOBR>COALESCE()</NOBR></SMALL> or <SMALL CLASS="allcaps"><NOBR>ISNULL()</NOBR></SMALL> in your query to avoid this.</P></DD>
<DT><B><A NAME="NORESULT">NORESULT</A></B></DT>
<DD>This means that whatever result set there may be, you throw it away. The
return value is <CODE>undef</CODE> or an empty array. The gains with <A HREF="#NORESULT"><SMALL CLASS="allcaps">NORESULT</SMALL></A>
are limited. Win32::SqlServer performs a <A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A>
on each result set, but most data probably crosses the wire anyway. But may you save
some client-side cycles on not building arrays of hashes and that. </DD>
<DT> </DT>
<DT><B><A NAME="Callbacks">Callbacks</A></B></DT>
<DD>Rather getting all data back at once, you can receive them one at a time
by for <CODE><NOBR>$resultstyle</NOBR></CODE> pass a reference to a Perl
<CODE>sub</CODE>. Win32::SqlServer calls this <CODE>sub</CODE> once for each row
that is returned from the query. <CODE>$rowstyle</CODE> then controls how
that calls looks like:
<PRE>$retstat = &$callback(\%row, $resultset_no); # HASH
$retstat = &$callback(\@row, $resultset_no); # LIST
$retstat = &$callback($row, $resultset_no); # SCALAR</PRE><CODE>$resultset_no</CODE> tells you which result set the row comes from. The
result sets are numbered from 1 and up.
<P>The callback should return any of the integer numbers as detailed below. The
return value from the last call to the callback is the return value from
<A HREF="#sql"><NOBR>sql()</NOBR></A>.</P>
<DL>
<DT><B><A NAME="RETURN_NEXTROW">RETURN_NEXTROW</A></B></DT>
<DD>Tells Win32::SqlServer 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 <A HREF="#RETURN_NEXTROW"><SMALL CLASS="allcaps">RETURN_NEXTROW</SMALL></A> is too noisy, I can reveal that 1 will do the same task.</DD>
<DT> </DT>
<DT><B><A NAME="RETURN_NEXTQUERY">RETURN_NEXTQUERY</A></B></DT>
<DD>Quit returning rows from the current result set, and move to the next
result set.</DD>
<DT> </DT>
<DT><B><A NAME="RETURN_CANCEL">RETURN_CANCEL</A></B></DT>
<DT><B><A NAME="RETURN_ERROR">RETURN_ERROR</A></B></DT>
<DD>Quit returning rows, and cancel the rest of the query. For
Win32::SqlServer there
is not really any difference, but you get a different return value back from
<A HREF="#sql"><NOBR>sql()</NOBR></A>. Use
<A HREF="#RETURN_CANCEL"><SMALL CLASS="allcaps">RETURN_CANCEL</SMALL></A>
when you found what you were looking for,
<A HREF="#RETURN_ERROR"><SMALL CLASS="allcaps">RETURN_ERROR</SMALL></A>
when something went wrong. <A HREF="#RETURN_ERROR"><SMALL CLASS="allcaps">RETURN_ERROR</SMALL></A> has the convenient value of 0.
<P><B>Important</B>: since the query is cancelled, you will not get
the values of the return value or output parameters from stored procedures,
but these will retain their input values. This does not apply to
<A HREF="#sql"><NOBR>sql()</NOBR></A>, but
well to <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>.</P></DD>
<DT><B><A NAME="RETURN_ABORT">RETURN_ABORT</A></B></DT>
<DD>Something went seriously wrong and you don't want to live any more.
Rather than issuing <CODE>die</CODE> yourself, you should use this return
code. Win32::SqlServer will then free up resources tied to the result set, before it
aborts execution. This is important in the case you catch the
condition with <CODE>eval</CODE>, so you don't have an unprocessed result set
when you come back from <CODE>eval</CODE></DD>
</DL>
<P>A few goods and bads with callbacks:</P>
<UL>
<LI><B>Good thing</B>: saves you from dealing with multi-dimensional
structures, which can be confusing at times. </LI>
<LI><B>Good thing</B>: if you are retrieving a large result set, you don't need
to gather all the data in client before processing them. </LI>
<LI><B>(Slightly) bad thing</B>: if you want to execute
<SMALL CLASS="allcaps">SQL</SMALL> statements from your
callback, you must use another Win32::SqlServer connection for that, as
Win32::SqlServer does not permit you
to initate a new query as long as there are results sets available. <SMALL>(And, no,
Win32::SqlServer does not support this new feature MARS, Multiple Active
Result Sets in SQL 2005. MARS does not really fit well with the
Win32::SqlServer
model.)</SMALL></LI>
<LI><B>Bad thing</B>: This is the reverse on the second point: if you don't get
all data to the client directly, <SMALL CLASS="allcaps">SQL</SMALL> Server needs to hold locks on the data
for a longer time, which can cause contention problems. From this follows
that an <SMALL CLASS="allcaps">UPDATE</SMALL> statement from the callback on a second
connection may cause you to block yourself.</LI>
</UL>
<P>All and all, callbacks are not really deprecated, but they smell a little
funny. However, I don't really know what will happen when I add better
support for large object in the future.</P>
</DD>
</DL>
If you want more examples on row styles and result styles, please refer to the
<A HREF="#quickexamples">Quick Examples</A> above. There is also an
<A HREF="#sqlsp_examples">example of using a callback</A> in the topic of
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>.
<P>This table summarises how
you could refer the column <I>col</I> in the first row in the first result
set, given a row style, result style and the type of return value. For
<A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A>, the assumption is that there is only one column to retrieve. If a
cell is greyed, that combination is not useful or applicable.</P>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=2>
<THEAD><TR><TD> </TD><TD> </TD><TH><A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A></TH><TH><A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A></TH><TH><A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A></TH></TR>
</THEAD>
<TR><TH ROWSPAN="3"><A HREF="#MULTISET"><SMALL CLASS="allcaps">MULTISET</SMALL></A></TH>
<TH><CODE><NOBR>%res</NOBR></CODE></TH>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD></TR>
<TR><TH><CODE><NOBR>@res</NOBR></CODE></TH>
<TD><CODE>$res[0][0]{'col'}</CODE></TD>
<TD><CODE>$res[0][0][$colno]</CODE></TD>
<TD><CODE>$res[0][0]</CODE></TD></TR>
<TR><TH><CODE>$res</CODE></TH>
<TD><CODE>$$res[0][0]{'col'}</CODE></TD>
<TD><CODE>$$res[0][0][$colno]</CODE></TD>
<TD><CODE>$$res[0][0]</CODE></TD></TR>
<TR><TH ROWSPAN="3"><A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A></TH>
<TH><CODE><NOBR>%res</NOBR></CODE></TH>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD></TR>
<TR><TH><CODE><NOBR>@res</NOBR></CODE></TH>
<TD><CODE>$res[0]{'col'}</CODE></TD>
<TD><CODE>$res[0][$colno]</CODE></TD>
<TD><CODE>$res[0]</CODE></TD></TR>
<TR><TH><CODE>$res</CODE></TH>
<TD><CODE>$$res[0]{'col'}</CODE></TD>
<TD><CODE>$$res[0][$colno]</CODE></TD>
<TD><CODE>$$res[0]</CODE></TD></TR>
<TR><TH ROWSPAN="3"><A HREF="#SINGLEROW"><SMALL CLASS="allcaps">SINGLEROW</SMALL></A></TH>
<TH><CODE><NOBR>%res</NOBR></CODE></TH>
<TD><CODE>$res{'col'}</CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD></TR>
<TR><TH><CODE><NOBR>@res</NOBR></CODE></TH>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD><CODE>$res[$colno]</CODE></TD>
<TD><CODE>$res[0]</CODE></TD></TR>
<TR><TH><CODE>$res</CODE></TH>
<TD><CODE>$$res{'col'}</CODE></TD>
<TD><CODE>$$res[$colno]</CODE></TD>
<TD><CODE>$res</CODE></TD></TR>
<TR><TH ROWSPAN="3"><A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A></TH>
<TH><CODE><NOBR>%res</NOBR></CODE></TH>
<TD><CODE>$res{'key'}{'col'}</CODE></TD>
<TD><CODE>$res{'key'}[$colno]</CODE></TD>
<TD><CODE>$res{'key'}</CODE></TD></TR>
<TR><TH><CODE><NOBR>@res</NOBR></CODE></TH>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD>
<TD BGCOLOR="#999999"><CODE> </CODE></TD></TR>
<TR><TH><CODE>$res</CODE></TH>
<TD><CODE>$$res{'key'}{'col'}</CODE></TD>
<TD><CODE>$$res{'key'}[$colno]</CODE></TD>
<TD><CODE>$$res{'key'}</CODE></TD></TR>
</TABLE>
</P>
<H2><A NAME="sql_one"><NOBR>sql_one()</NOBR></A></H2>
<P>Executes a command batch that should return exactly one row and fails if it
does not.</P>
<PRE>(%|@|$)result = $sqlsrv->sql_one($batch [, \@unnamed_parameters]
[, \%named_paraeters] [, $rowstyle]);</PRE>
<DL>
<DT><CODE>(%|@|$)result</CODE></DT>
<DD>The result set from the command batch. If <CODE>$rowstyle</CODE> is
<A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A> (the default), the result is a hash or a reference to a hash. It <CODE>
$rowstyle</CODE> is <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A>, the result is an array or a reference to an array.
If <CODE>$rowstyle</CODE> is <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A>, the result is a scalar value. </DD>
<DT> </DT>
<DT><CODE>$batch</CODE></DT>
<DD>A batch of one or more <SMALL CLASS="allcaps">SQL</SMALL> statements that is expected to return exactly
one row.</DD>
<DT> </DT>
<DT><CODE>\@unnamed_parameters</CODE></DT>
<DD>A reference to an array with input for unnamed parameters. This parameter
works exactly as the namesake parameter for <A HREF="#sql"><NOBR>sql()</NOBR></A>, see
further this function and particularly the section
<A HREF="#parameterspecdetails">Details on the Parameter Specification</A>. </DD>
<DT> </DT>
<DT><CODE>\%named_parameters</CODE></DT>
<DD>A reference to a hash with input for named parameters. This parameter
works exactly as the namesake parameter for <A HREF="#sql"><NOBR>sql()</NOBR></A>, see
further this function and particularly the section
<A HREF="#parameterspecdetails">Details on the Parameter Specification</A>. You cannot use named
parameters with <SMALL CLASS="allcaps">SQL</SMALL> 6.5.</DD>
<DT> </DT>
<DT><CODE>$rowstyle</CODE></DT>
<DD>Specifies how each row in the result is to be represented. Possible
values are <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A> (each row is a hash keyed by column name), <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A> (each
row is an array) and <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A> (each row is a scalar value). See the section
<A HREF="#RowResultStyles">Row Styles and Result Styles</A> under
<A HREF="#sql"><NOBR>sql()</NOBR></A> for details. Default is <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A>.</DD>
</DL>
<P><A HREF="#sql_one"><NOBR>sql_one()</NOBR></A> is similar to <A HREF="#sql"><NOBR>sql()</NOBR></A> with <CODE>$resultstyle</CODE>
set to <A HREF="#SINGLEROW"><SMALL CLASS="allcaps">SINGLEROW</SMALL></A>, but there is one important
difference: <A HREF="#sql_one"><NOBR>sql_one()</NOBR></A> will <CODE>croak</CODE> if the command batch returns
more than one row or no row at all.</P>
<H2><A NAME="sql_sp"><NOBR>sql_sp()</NOBR></A></H2>
<P>Executes a stored procedure or a scalar user-defined function. </P>
<PRE>($|@|%)result = $sqlsrv->sql_sp($SP_name
[, \$retvalue]
[, \@positional_parameters]
[, \%named_parameters]
[, $rowstyle] [, $resultstyle [, \@keys]]);</PRE>
<H3>Parameters</H3>
<DL>
<DT><CODE>($|@|%)result</CODE></DT>
<DD>The result set(s) from the stored procedure. The result set is returned
in exactly the same fashion as for <A HREF="#sql"><NOBR>sql()</NOBR></A>, please refer to
that function and the section <A HREF="#RowResultStyles">Row Styles and
Result Styles</A>.</DD>
<DT> </DT>
<DT><CODE>$SP_name</CODE></DT>
<DD>The name of a stored procedure or a scalar user-defined function. This
can be a one-, two- or three-part name. That is, you can supply database
and/or schema. Four-part names including a server-component are
not permitted. If the any component of the name includes special characters
such as space or period, quote that component with brackets or double quotes as
you would in <SMALL CLASS="allcaps">T-SQL</SMALL>, for instance:
<PRE>$sqlsrv->sql_sp('[My.Database].thisschema."that sp"');</PRE>
</DD>
<DT><CODE>\$retvalue</CODE></DT>
<DD>A reference to a scalar that will receive the return value of the stored
procedure or the user-defined function. See further the section
<A HREF="#Return_values">Return Values</A> below.</DD>
<DT> </DT>
<DT><CODE>\@positioaal_parameters</CODE></DT>
<DD>A reference to an array that holds unnamed parameters. The parameters
must appear in the order they appear in the declaration of the stored
procedure or <SMALL CLASS="allcaps">UDF</SMALL>. The entries in the array are scalar values, or reference to
scalar values. See further the section <A HREF="#SP_parameters">Passing Parameters to Stored
Procedures</A> below. Note that parameters to stored procedures works differently from
parameters to command batches with <A HREF="#sql"><NOBR>sql()</NOBR></A>.</DD>
<DT> </DT>
<DT><CODE>\%named_parameters</CODE></DT>
<DD>A reference to a hash for named parameters. The keys in the hash are the
parameter names, with or without the leading <CODE>@</CODE>. (If you specify
both, Win32::SqlServer discards one of them and issues a warning.) The hash entries are scalar values, or reference to scalars. If you have specified
the same parameter in <CODE><NOBR>\@positional_parameters</NOBR></CODE> and
<CODE><NOBR>\%named_parameters</NOBR></CODE>, the entry in <CODE><NOBR>\@positional_parameters</NOBR></CODE>
takes precedence and the other value is discarded, yielding a warning through
the <A HREF="#MsgHandler">current message handler</A>, if Perl warnings are
enabled. For further details, see the section <A HREF="#SP_parameters">Passing Parameters to Stored Procedures</A>
below. Note that
parameters to stored procedures works differently from parameters to command
batches with <A HREF="#sql"><NOBR>sql()</NOBR></A>.</DD>
<DT> </DT>
<DT><CODE>$rowstyle</CODE></DT>
<DD>Specifies how each row in the result is to be represented. Possible
values are <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A> (each row is a hash keyed by column
name),
<A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A> (each row is an array) and <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A>
(each row is a scalar value). This parameter works exactly as for
<A HREF="#sql"><NOBR>sql()</NOBR></A>. See the section <A HREF="#RowResultStyles">Row Styles and Result Styles</A>
under that function. Default is <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A>.</DD>
<DT> </DT>
<DT><CODE>$resultstyle</CODE></DT>
<DD>Specifies how the result sets are structured. Possible values are
<A HREF="#MULTISET"><SMALL CLASS="allcaps">MULTISET</SMALL></A> (an array of result sets),
<A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A> (an array of rows),
<A HREF="#SINGLEROW"><SMALL CLASS="allcaps">SINGLEROW</SMALL></A>
(a single row according to <CODE>$rowstyle</CODE>), <A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A>
(a hash, keyed by the data in the result set), <A HREF="#NORESULT"><SMALL CLASS="allcaps">NORESULT</SMALL></A>
(no result set returned) or a reference to a
<A HREF="#Callbacks">callback</A> routine called for each row. This
parameter works exactly as for <A HREF="#sql"><NOBR>sql()</NOBR></A>. See the section
<A HREF="#RowResultStyles">Row Styles and Result Styles</A> under that
function. Default is
<A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A>.</DD>
<DT> </DT>
<DT><CODE>\@keys</CODE></DT>
<DD>This parameter is only applicable when <CODE>$resultstyle</CODE> is
<A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A>, in which case it's mandatory. It specifies which
column(s) in the result that is/are the keys. See further the section
<A HREF="#RowResultStyles">Row Styles and Result Styles</A> under the topic
of <A HREF="#sql"><NOBR>sql()</NOBR></A>. </DD>
</DL>
<H3>General</H3>
<P><A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> uses the <SMALL CLASS="allcaps">RPC</SMALL> mechanism to call the stored procedure or <SMALL CLASS="allcaps">UDF</SMALL>, and does
thus not issue any <SMALL CLASS="allcaps">EXEC</SMALL> statement. The <SMALL CLASS="allcaps">RPC</SMALL> mechanism is a
faster way to call a stored procedure.</P>
<P>Note that there is one case you will not get the return value or the value
of the output parameters: this is when you use a <A HREF="#Callbacks">callback</A> routine as the result
style, and you return <A HREF="#RETURN_CANCEL"><SMALL CLASS="allcaps">RETURN_CANCEL</SMALL></A> or <A HREF="#RETURN_ERROR"><SMALL CLASS="allcaps">RETURN_ERROR</SMALL></A> from the callback. In
this case, the query is cancelled, whereupon output parameters and return
value are lost.</P>
<H3><A NAME="Return_values">Return Values</A></H3>
<P>If a stored procedure returns another value than zero, the default behaviour
of <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> is to <CODE>croak</CODE>. The assumption is that a stored procedure
returns 0 on successful execution, and any non-zero value indicates failure.
There is no requirement in <SMALL CLASS="allcaps">T-SQL</SMALL> that you must use return values this way,
but it's definitely best practice to do so. To return actual data values from stored
procedures, use output parameters instead.</P>
<P>You can override this behaviour with the <B><A HREF="#CheckRetStat">CheckRetStat</A></B> and <B><A HREF="#RetStatOK">RetStatOK</A></B> elements
of the <B><A HREF="#ErrInfo">ErrInfo</A></B> property.</P>
<P>This does not apply when you call scalar user-defined functions. In this case
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> never aborts execution because of the return value. </P>
<H3><A NAME="SP_parameters">Parameters to Stored Procedures</A></H3>
<P>(For brevity, I'm saying "stored procedures" in this section, but everything
which is said here applies to scalar user-defined functions as well. Except,
then, that they don't have output parameters.)</P>
<H4>Win32::SqlServer Retrieves and Caches Parameter Profiles</H4>
<P>When you call a stored procedure with <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, you don't have to bother
about the data types of the parameters, because the first time you call the
procedure Win32::SqlServer queries <SMALL CLASS="allcaps">SQL</SMALL>
Server to get the parameter profile of the stored procedure. On subsequent
calls, Win32::SqlServer retrieves the parameter profile from its cache in
the Win32::SqlServer property <B><A HREF="#procs">procs</A></B>. This property is a
hash keyed by the procedure name. (Exactly as you specified it, so "my_sp" and
"dbo.my_sp" result in two entries in <B><A HREF="#procs">procs</A></B>.)
Normally you don't have to bother, but if your script recreates a procedure
with a different parameter profile, you must delete the procedure from <B>
<A HREF="#procs">procs</A></B>. (As for the contents of the entry for a
stored procedure, that is off-limits. Any meddling is entirely
unsupported.)</P>
<P>A consequence of this is that there are some special system stored procedures
that you cannot call with <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> because they have dynamic parameter lists.
One such example is <I>sp_executesql</I>. For such procedures, call them
through <A HREF="#sql"><NOBR>sql()</NOBR></A> using <SMALL CLASS="allcaps">EXEC</SMALL> instead, or use the
<A HREF="#Mid-level">mid-level query routines</A>.</P>
<H4>Passing Values In and Out</H4>
<P>Each entry in <CODE><NOBR>\@positional_parameters</NOBR></CODE> and <CODE>
<NOBR>\%named_parameters</NOBR></CODE> is a parameter value, or a reference
to a parameter value. The latter is necessary for output parameters when you
use an anonymous array or hash to pass the parameters. </P>
<P>Say that you have this stored procedure:</P>
<PRE>CREATE PROCEDURE some_sp @in int,
@out int OUTPUT AS</PRE>
The proper way to call this procedure is:
<PRE>my ($out);
$sqlsrv->sql_sp('some_sp', [1, \$out]);</PRE>
<P>That is, in the array you must pass a <I>reference</I> to the variable that
is to receive the value in the output parameter. If you just passed <CODE>$out</CODE>,
then Win32::SqlServer would modify the entry in the anonymous array, but <CODE>$out</CODE>
would be unchanged. For this reason, <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> issues a warning, if an array/hash
entry for an output parameter is not a reference to a scalar. The warning is
only issued if Perl warnings are enabled.</P>
<P>(Yes, if you use a real array and not an anonymous one, like this:</P>
<PRE>my @params = (1, undef);
$sqlsrv->sql_sp('some_sp', \@params);</PRE>
<P>
<CODE>$params[1]</CODE> is set to the value of <CODE><NOBR>@out</NOBR></CODE>, despite not being a
reference. Thus, in this case the warning is incorrect. But there is no way for
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> to tell whether you passed an anonymous or a real array, and I expect anonymous arrays to be used more
often than real.)</P>
<P>As for how to specify the actual values for different data types, see the
section <A HREF="#datatypes">Data-type Representation</A>.</P>
<H3><A NAME="sqlsp_examples">Examples</A></H3>
<P><I>sp_helpdb</I> returns two result sets. Here is an example using a
<A HREF="#Callbacks">callback</A>
that prints out the result sets in a fairly simple manner:</P>
<PRE>sub print_hash {
my($hash, $ressetno) = @_;
my ($col);
print "$ressetno: ";
foreach $col (%$hash) {
print "$col: $$hash{$col} ";
}
print "\n";
RETURN_NEXTROW;
}
$sqlsrv->sql_sp("sp_helpdb", ['tempdb'], HASH, \&print_hash);</PRE>
<P>Here is an example with a procedure that takes two dates as parameters to
count the number of records in that interval. Passing <SMALL CLASS="allcaps">NULL</SMALL> 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.</P>
<PRE>CREATE PROCEDURE putte_sp @startdate datetime = NULL,
@enddate datetime = NULL,
@flavour smallint = NULL,
@no_of_rec int OUTPUT AS</PRE>
<P>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 <SMALL CLASS="allcaps">T-SQL</SMALL> the call would be:</P>
<PRE>EXEC @ret = putte_sp '19970101', @no_of_rec = @no_of_rec OUTPUT
SELECT @err = @@error
IF @err <> 0 OR @ret <> 0 GOTO error_exit</PRE>
<P>In Win32::SqlServer this translates to:</P>
<PRE>$sqlsrv->sql_sp('putte_sp', \$ret, ['19970101'], {'no_of_rec' => \$no_of_rec});</PRE>
<P>Notice how we pass a reference to <CODE>$no_of_rec</CODE>, rather than the
variable itself, so we can retrieve the output value later on. We also left
out the <CODE>@</CODE> from the hash key, knowing that Win32::SqlServer sorts it out
anyway. Finally, note that the
bulky error handling in <SMALL CLASS="allcaps">T-SQL</SMALL> is not present in the Perl code, as by default
<A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> aborts on non-zero return values or <SMALL CLASS="allcaps">SQL</SMALL> errors. </P>
<H2><A NAME="sql_insert"><NOBR>sql_insert()</NOBR></A></H2>
<P>
Inserts a row in to table from a hash, using the keys in the hash as column
names.</P>
<PRE>$sqlsrv->sql_insert($table, \%values);</PRE>
<DL>
<DT><CODE>$table</CODE></DT>
<DD>The name of a table or a view to insert into. The name can be a one-, two-
or three-part name. That is, you can specify database, schema and table. You
cannot use a four-part name to specify a remote table. If any component
includes special characters, you must put that component in brackets or
double quotes as in this example:</DD>
<PRE>$sqlsrv->sql_insert('"My.Database"..[Order Details]', \%order_details);</PRE>
<DT><CODE>\%values</CODE></DT>
<DD>
A reference to a hash which holds the values to insert. The keys of the hash
should agree with the column names of the table.</DD>
</DL>
<P>On the first invocation for a certain table, <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> inquires the system tables in <SMALL CLASS="allcaps">SQL</SMALL> Server to find out the data
types of all columns. This information is saved in the Win32::SqlServer property <B>
<A HREF="#tables">tables</A></B>, and <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> uses the cached
information on subsequent calls. <B><A HREF="#tables">tables</A></B> is a
hash, keyed by table name. You can force a refresh by deleting the hash key
for a table. The key is exactly as you entered it, thus "Orders"
and "orders" are different tables in this context, even if you have a
case-insensitive database.</P>
<P>As for specifying the data in the columns, please see the section
<A HREF="#datatypes">Data-type Representation</A>.</P>
<P>Note: while <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> may be convenient, you may get better performance by
using a stored procedure instead. <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> results in a parameterised call to <A HREF="#sql"><NOBR>sql()</NOBR></A>, and eventually
<I>sp_executesql</I>. Thus, there will be a cached query plan, but you never
know. (And if you have a lot of data to insert, you should consider using an
<SMALL CLASS="allcaps">XML</SMALL> document and <SMALL CLASS="allcaps">OPENXML</SMALL> or use <SMALL CLASS="allcaps">BCP</SMALL>.)</P>
<H1><A NAME="Mid-level">Mid-Level Query Functions</A></H1>
<P>The high-level query functions are implemented in Perl, and they in their
turn use the mid-level functions implemented in C++ (save for
<A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A>). These in their turn call the
low-level functions in the <SMALL CLASS="allcaps">OLE DB API</SMALL>, not exposed directly to Perl.</P>
<P>If you think that the high-level functions puts too much into a
straightjacket (or are just too bewildering for you), you can call the
mid-level functions directly. But... there is a disclaimer: the extensive
test suite for Win32::SqlServer does not include any tests of direct use of the mid-level functions. Thus, if your usage of these functions goes beyond what the
high-level functions uses, you are on somewhat less solid ground.</P>
<P>While the high-level functions presents the Win32::SqlServer as a stateless object by
submitting the query and getting the result sets in one single call, the
mid-level functions makes the OIleDB object very stateful, and there is a
certain order in which you must call these functions:</P>
<OL>
<LI>First define the text of the command batch with
<A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>.</LI>
<LI>Then for each parameter in the batch define the parameter with
<A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>.</LI>
<LI>Execute the command batch with <A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>.</LI>
<LI>Iterate over all result sets with <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>
until this function returns a false value.</LI>
<LI>Within a result set, iterate over all rows with <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A>
until returns false, alternatively skip the result set by calling
<A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A>.</LI>
<LI>When you have retrieved all result sets, get any output
parameters from the command batch with <A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A>.</LI>
</OL>
<P>If all you need is more freedom when you specify your
parameters, but you still want the result sets and the rows packaged
according to row styles and result styles, you can perform steps 4 and 5 in
one go by calling <A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A>.</P>
<P>You cannot initiate a new command until you have retrieved all
result sets and the output parameters, unless you first call
<A HREF="#cancelbatch"><NOBR>cancelbatch()</NOBR></A>. You can find out in which state you
are by calling <A HREF="#getcmdstate"><NOBR>getcmdstate()</NOBR></A>.</P>
<P>There are a couple of features in Win32::SqlServer that are not supported by the
mid-level functions (because the code to implement them is in the <A HREF="#High-level">high-level query functions</A>):</P>
<UL>
<LI>
Logging of <SMALL CLASS="allcaps">SQL</SMALL> statements with the <B><A HREF="#LogHandle">LogHandle</A></B> property is not performed.</LI>
<LI>
When you call a stored procedure with the <SMALL CLASS="allcaps">ODBC</SMALL> call syntax, and there is an
error, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, will print the batch as
you submitted it. (With <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>,
Win32::SqlServer constructs an <SMALL CLASS="allcaps">EXEC</SMALL>
string for logging and error messages.)</LI>
<LI>
Errors in the use of parameters are not intercepted by
Win32::SqlServer, but will be reported by <SMALL CLASS="allcaps">SQL</SMALL> Server or the <SMALL CLASS="allcaps">OLE DB</SMALL> provider. And
the messages from the latter can be very obscure. </LI>
<LI>
The <B><A HREF="#NoExec">NoExec</A></B> property has no effect.</LI>
<LI>
Conversions you have set up with <A HREF="#sql_set_conversion"><NOBR>sql_set_conversion()</NOBR></A> have no effect.</LI>
</UL>
<H2><A NAME="initbatch"><NOBR>initbatch()</NOBR></A></H2>
<P>Defines the text for a command batch.</P>
<PRE>$sqlsrv->initbatch($batch)</PRE>
<DL>
<DT><CODE>$batch</CODE></DT>
<DD>The text for the batch. The batch can be parameterised, with parameters
being indicated with <CODE>?</CODE> as placeholder. In
difference to <A HREF="#sql"><NOBR>sql()</NOBR></A>, names starting with <CODE>@</CODE> is
not understood as parameters. (If you want to use named parameters, an
alternative is to wrap the command batch in a call to <I>sp_executesql</I>.)
See also below about
<A HREF="#ODBCCall"><SMALL CLASS="allcaps">ODBC</SMALL> call syntax</A>.
</DD>
</DL>
<P>Once you have called <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>, you cannot call <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A> again until
you have fetched all result sets and the output parameters or cancelled
the batch with <A HREF="#cancelbatch"><NOBR>cancelbatch()</NOBR></A>.</P>
<P>As why you should parameterise your command in most cases, and when you
should not, please see the subsections <A HREF="#whyparameter">Why
Parameterising <SMALL CLASS="allcaps">SQL</SMALL> Commands at All?</A> and <A HREF="#whennotparameter">When
You Should Not Parameterise</A> in the <A HREF="#sql"><NOBR>sql()</NOBR></A> topic.</P>
<P>You do not have to be connected to call <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>.</P>
<H3><A NAME="ODBCCall">Using ODBC Call Syntax</A></H3>
<P>To initiate a call of a stored procedure through <SMALL CLASS="allcaps">RPC</SMALL>, use the <SMALL CLASS="allcaps">ODBC</SMALL> call
syntax, for instance:
</P>
<PRE>{? = call dbo.some_sp(?, ?)}</PRE>
<P>The first <CODE>?</CODE> is for the return value (which you can omit, if you
opt to ignore the return value), and the <CODE>?</CODE> in the parentheses
represent the parameters to the stored procedure. You should only supply as
many ? as you actually intend to provide. For instance, assume that <I>some_sp</I> actually
looks like this:</P>
<PRE>CREATE PROCEDURE some_sp @par1 int = 19,
@par2 char(2) = NULL,
@par3 datetime = NULL,
@par4 bit = 0 AS</PRE>
You intend to provide a value only for <CODE><NOBR>@par2</NOBR></CODE> and <CODE><NOBR>@par4</NOBR></CODE>
when you can
<A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>. In this case the parameter
list in the <SMALL CLASS="allcaps">ODBC</SMALL> call should look like above, that is include two <CODE>?</CODE>
only.
<P>You can in fact provide values directly in the command batch like this:</P>
<PRE>{? = call dbo.some_sp(?, ?, ?, 1)}</PRE>
<P>(Put please only do this when you supply all parameters. I have no idea
what happens if you leave out some.) You can also used named parameters:</P>
<PRE>{? = call dbo.some_sp(@par2 = ?, @par4 = ?)}</PRE>
<P><SMALL CLASS="allcaps">OLE DB</SMALL> permits you to mix <SMALL CLASS="allcaps">ODBC</SMALL> call syntax with regular commands, or to
provide two calls for the same command batch. However, I have not tested
this, nor analysed the ramifications of it. Be warned.</P>
<H2><A NAME="enterparameter"><NOBR>enterparameter()</NOBR></A></H2>
<P>Defines a parameter for a parameterised command batch.</P>
<PRE>$ret = $sqlsrv->enterparameter($nameoftype, $maxlen, $paramname,
$isinput, $isoutput [, $value
[, $precision [, $scale {, $typeinfo]]]]);</PRE>
<DL>
<DT><CODE>$ret</CODE></DT>
<DD>
True if the parameter was entered successfully, false if not.</DD>
<DT> </DT>
<DT><CODE>$nameoftype</CODE></DT>
<DD>The name of the type, without any specification of length, precision etc.
That is, only <B><CODE>varchar</CODE></B>, not <B><CODE><NOBR>varchar(3)</NOBR></CODE></B>.
This must be a system type, and it must be the main name for the type; you
cannot use the data-type synonyms that ships with <SMALL CLASS="allcaps">SQL</SMALL> Server, for instance
<B><CODE>integer</CODE></B> for <CODE><B>int</B></CODE>, with one exception: you can use <B>
<CODE>rowversion</CODE></B> for <B><CODE>timestamp</CODE></B>. For <SMALL CLASS="allcaps">CLR</SMALL> UDTs, use
<B>
<CODE>UDT</CODE></B>. The name must be in lowercase, except for <B><CODE>UDT</CODE></B>
which must be all uppercase. </DD>
<DT> </DT>
<DT><CODE>$maxlen</CODE></DT>
<DD>The maximum length for the parameter. You need to specify this for character
and binary data types. When you are calling a stored procedure, you would
typically use 20 for a <B><CODE><NOBR>varchar(20)</NOBR></CODE></B> parameter (as well as for
<B><CODE><NOBR>nvarchar(20)</NOBR></CODE></B>; the length is not in bytes). If
you leave it <CODE>undef</CODE>, you get a default value of 1, which may not be what you
want. Use -1, for large types, that is <B><CODE>(n)text</CODE></B>, <B><CODE>image</CODE></B>, the
<B><CODE>(MAX)</CODE></B>
types and <B><CODE>xml</CODE></B></DD>
<DT> </DT>
<DT><CODE>$paramname</CODE></DT>
<DD>The name for the parameter. This works differently depending on the context
where the parameter appears. This is something that is happening in the <SMALL CLASS="allcaps">OLE
DB</SMALL> provider, not in Win32::SqlServer. From my investigations, I've identified four
different cases.
<P><OL>
<LI>Parameters in regular <SMALL CLASS="allcaps">SQL</SMALL> batch.</LI>
<LI><SMALL CLASS="allcaps">ODBC</SMALL> Call Syntax – the return value.</LI>
<LI><SMALL CLASS="allcaps">ODBC</SMALL> Call Syntax – named parameters, <CODE>{? = call some_sp(<NOBR>@par3</NOBR> = ?)}</CODE>.</LI>
<LI><SMALL CLASS="allcaps">ODBC</SMALL> Call Syntax – positional parameters, e.g. <CODE>{? = call some_sp(?,
?)}</CODE>.</LI>
</OL></P>
<P>The first three cases appear to behave the same, whereas the fourth is
different.</P>
<P><B>Case 1-3</B> – you can set the parameter name to <CODE>undef</CODE>,
or you can use whatever name you like, <I>as long as it starts with</I> <CODE>
@</CODE>. (<B>Note</B>: for the return value in case 2, the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider
will actually accept a name that does not start with <CODE>@</CODE>. But this
is a bug! <SMALL CLASS="allcaps">SQL</SMALL> Native Client never accepts names without <CODE>@</CODE> in
front.) The name is never passed to <SMALL CLASS="allcaps">SQL</SMALL> Server, but you can use it when you retrieve the value of an output parameter, see
<A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A>. For a regular <SMALL CLASS="allcaps">SQL</SMALL> batch, the
<SMALL CLASS="allcaps">OLE DB</SMALL> provider will use names like <CODE><NOBR>@P1</NOBR></CODE>,
<CODE><NOBR>@P2</NOBR></CODE> etc when it constructs the call to <I>sp_executesql</I>.</P>
<P>
<B>Case 4</B> – in this case, if you set the parameter name to <CODE>undef</CODE>
for, say, the third <CODE>?</CODE>, this means that you pass a value for the
second parameter. (Since the first <CODE>?</CODE> is the return value). If you specify a
name, this must be the actual name of a parameter to the stored
procedure.
It appears that you can in fact mix named and unnamed parameters entirely
here, and provide a name for the first parameter (that is the second <CODE>?</CODE>),
but leave it out for the second. I would not recommend this, though.</P></DD>
<DT><CODE>$isinput</CODE></DT>
<DD>1 if the parameter is an input parameter. Most parameters are input
parameters, but not return values from stored procedures. Here is another
example of a parameter that is not an input parameter:
<PRE>SELECT ? = @@version</PRE>
</DD>
<DT><CODE>$isoutput</CODE></DT>
<DD>1 if the parameter is an output parameter. </DD>
<DT> </DT>
<DT><CODE>$value</CODE></DT>
<DD>The value for the parameter. You can leave this parameter out, if you are
passing <SMALL CLASS="allcaps">NULL</SMALL>, or you have specified 0 for <CODE>$isinput</CODE>. If you
specified 1 for <CODE>$isinpu</CODE>t, Win32::SqlServer will attempt to convert the value to type
specified in <CODE>$nameoftype</CODE>, as described in the chapter
<A HREF="#datatypes">Data-type Representation</A>.</DD>
<DT> </DT>
<DT><CODE>$precision</CODE></DT>
<DT><CODE>$scale</CODE></DT>
<DD>Precision and scale for <B><CODE>decimal</CODE></B> and <CODE><B>numeric</B></CODE>.
If you don't specify these, the default values are 18 and 0 respectively.</DD>
<DT> </DT>
<DT><CODE>$typeinfo</CODE></DT>
<DD>This parameter applies only to parameters of the types <B><CODE>xml</CODE></B>
and
<B><CODE>UDT</CODE></B>, and only when you use <SMALL CLASS="allcaps">SQL</SMALL> Native Client as the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. For <B><CODE>xml</CODE></B> it specifies a schema collection and is
optional. For <B><CODE>UDT</CODE></B> it specifies the name of the actual <SMALL CLASS="allcaps">CLR</SMALL>
type and is mandatory. Both names can be specified with three-part notation on
the form <I>database</I>.<I>schema</I>.<I>object</I>. If there are parts that
include special characters such as space or period, you must quote these in
brackets or double quotes. </DD>
</DL>
<P>You must <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A> prior to calling
<A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>. You cannot call <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>, once you have called
<A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>. You do not have to be connected when
you call <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>.</P>
<P>If Win32::SqlServer cannot convert <CODE>$value</CODE> to <CODE>
$nameoftype</CODE>, <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A> returns a false value. Once this has
happened for a parameter, you will not be able to execute the batch with
<A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>. Nevertheless, you can continue to
enter more parameters. This makes it possible to catch all parameter errors in
one go. Each parameter value that cannot be converted results in a warning
communicated through the <A HREF="#MsgHandler">current message handler</A>.</P>
<P>You must call <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A> exactly as many times as there are parameter
holders in your command batch. Specifying too many parameters, will yield the
message <I>Multiple-step <SMALL CLASS="allcaps">OLE DB</SMALL> operation generated errors</I>. But
this is a generic message and can appear in other situations as well. </P>
<P>Note that far from all parameter errors are detected by <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>, but
will not raise an error message until you call
<A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>, for instance an illegal parameter
name.</P>
<H2><A NAME="executebatch"><NOBR>executebatch()</NOBR></A></H2>
<P>Executes a batch previously defined with <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>.</P>
<PRE>$ret = $sqlsrv->executebatch();</PRE>
<DL>
<DT><CODE>$ret</CODE></DT>
<DD>True if the command batch was successfully submitted for execution, else
false.</DD>
</DL>
<P>You must first define a command batch with <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>,
and the enter definitions for all parameters to the batch before you can call
<A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>. Once you have executed the command batch, you cannot resubmit
the command. When you call <A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>, you must be connected, unless you have
set the <B><A HREF="#AutoConnect">AutoConnect</A></B> property. In the latter
case, <A HREF="#executebatch"><NOBR>executebatch()</NOBR></A> will connect automatically.
</P>
<P>Note that the return status reports whether the submission of the batch was
successful. That is, errors you get at this point are those detected by
Win32::SqlServer and the <SMALL CLASS="allcaps">OLE DB</SMALL> provider. Errors from <SMALL CLASS="allcaps">SQL</SMALL> Server are not reported until
you call <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>. (And if your batch
returns result sets before the error, you will not see the error until you
have called <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> once for each result
set.) If the command submission fails, the batch is cancelled, and you
cannot perform any further work with it.</P>
<P>Most errors are communicated through the <A HREF="#MsgHandler">current
message handler</A>, but pure usage errors like calling <A HREF="#executebatch"><NOBR>executebatch()</NOBR></A> without
being connected, causes Win32::SqlServer to <CODE>croak</CODE>.</P>
<H2><A NAME="nextresultset"><NOBR>nextresultset()</NOBR></A></H2>
<P>Moves on to the next result set from the command batch.</P>
<PRE>$more_results = $sqlsrv->nextresultset([$rowsaffected});</PRE>
<DL>
<DT><CODE>$more_results</CODE></DT>
<DD>True if there was indeed a result set. False if there were no more result
sets.</DD>
<DT> </DT>
<DT><CODE>$rowsaffected</CODE></DT>
<DD>An optional output parameter that reports the number of affected rows for
an <SMALL CLASS="allcaps">INSERT, DELETE</SMALL> or <SMALL CLASS="allcaps">UPDATE</SMALL>
statement. You don't get these rowcounts if <SMALL CLASS="allcaps">SET NOCOUNT ON</SMALL>
is in effect</DD>
</DL>
<P>You can only call <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>, if you previously have successfully
submitted a command batch with <A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>.
You cannot call <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>, if there are more rows to retrieve from the
current result set. You must either iterate over <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A>
until this function returns false, or discard the rows with <A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A> to be
able to move to the next result set. </P>
<P>For each command batch you will need to call <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> at least twice.
First once to move to the first result set which is always there, even if there
are no rows. And then a second time to move past the last result set.
It is first at this point that output parameters will be available. Once
<A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> have returned false, you cannot call <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> again. </P>
<P>Not all result sets have rows. Errors and other messages from <SMALL CLASS="allcaps">SQL</SMALL> Server also
yield a "result set", as does the row count from <SMALL CLASS="allcaps">INSERT,
UPDATE</SMALL> and <SMALL CLASS="allcaps">DELETE</SMALL> statements. And
there are entirely empty result sets. For result sets of
this kind, you can in fact move on the next result set directly without calling
<A HREF="#nextrow"><NOBR>nextrow()</NOBR></A>. But if you want to play safe, always enter a loop
over <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A>.</P>
<P>If the command batch results in <SMALL CLASS="allcaps">SQL</SMALL> errors, they are communicated through the
<A HREF="#MsgHandler">current message handler</A> when you call
<A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>. Note that if a batch returns several result sets, an error
is not reported until you have retrieved previous result sets. Consider this
batch.</P>
<PRE>SET NOCOUNT OFF
UPDATE tbl SET ... WHERE
INSERT tbl (...) VALUES ...
SELECT @@identity
DELETE tbl2 WHERE ...</PRE>
Assume now that the <SMALL CLASS="allcaps">DELETE</SMALL> statement causes a foreign-key
violation. This will not be reported until you call <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> the fourth
time. The two first result sets will bring you the rowcounts from the
<SMALL CLASS="allcaps">UPDATE</SMALL> and <SMALL CLASS="allcaps">INSERT</SMALL> statements, the third the value of <CODE>
<NOBR>@@identity</NOBR></CODE>.
<H2><A NAME="nextrow"><NOBR>nextrow()</NOBR></A></H2>
<P>Retrieves the next row from the current result set.</P>
<PRE>$more_rows = $sqlsrv->nextrow($hashref, $arrayref);</PRE>
<DL>
<DT><CODE>$more_rows</CODE></DT>
<DD>True, if a row was returned, and you there may be more rows. False means
that no row was returned, because you have reached the end of the result set.</DD>
<DT> </DT>
<DT><CODE>$hashref</CODE></DT>
<DD>A scalar variable that on output receives the reference to a hash that
holds the data for the row. The keys in the hash are the column names. For
nameless columns, you get <I>Col 1</I>, <I>Col 2</I> etc. Note that you must
pass a scalar variable. Passing a reference to a hash on the form <CODE>
<NOBR>\%hash</NOBR></CODE> won't do. If you don't care about the hash, you
can pass <CODE>undef</CODE>.</DD>
<DT> </DT>
<DT><CODE>$arrayref</CODE></DT>
<DD>A scalar variable that on output receives the reference to an array that
holds the data for the row. As for <CODE>$hashref</CODE>, you must pass a scalar,
<CODE><NOBR>\@array</NOBR></CODE> will not work. If you don't care about the
array, pass <CODE>undef</CODE>. You cannot omit the variable. </DD>
</DL>
<P>
You can only call <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> when you have an active result set, that is you have
previously called <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> and you have
not yet reached the end of that
result set. Once <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> has returned false, you cannot call <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> again,
until you have advanced to the next result set.</P>
<P>As noted under
<A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>, some result sets convey other information than rows from
queries. You can still call <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> once for these result sets, to save you
from special-casing these result sets.</P>
<H2><A NAME="get_result_sets"><NOBR>get_result_sets()</NOBR></A></H2>
<P>
Retrieves all result sets and rows for a command batch, and returns them on the
specified form.</P>
<PRE>($|@|%)result = $sqlsrv->get_result_sets([$rowstyle] [, $resultstyle] [\@keys]);</PRE>
<DL>
<DT><CODE>($|@|%)result</CODE></DT>
<DD>The result set(s) from the command batch. Depending on the <CODE>
$rowstyle</CODE> and
<CODE>$resultstyle</CODE> parameters, this can an array, a hash or a scalar.
If the result is an array or a hash, you can always use a scalar to receive
the return value as a reference to the array/hash. </DD>
<DT> </DT>
<DT><CODE>$rowstyle</CODE></DT>
<DD>How rows are represented in the result set. Can be <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A> (the
default), <A HREF="#LIST"><SMALL CLASS="allcaps">LIST</SMALL></A> (to get an array) or <A HREF="#SCALAR"><SMALL CLASS="allcaps">SCALAR</SMALL></A>. See the section
<A HREF="#RowResultStyles">Row Styles and Result Styles</A> in the
description of <A HREF="#sql"><NOBR>sql()</NOBR></A> for details.</DD>
<DT> </DT>
<DT><CODE>$resultstyle</CODE></DT>
<DD>Specifies how the result sets are to be structured. Possible values are
<A HREF="#MULTISET"><SMALL CLASS="allcaps">MULTISET</SMALL></A> (an array of result sets), <A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A> (an array of rows), <A HREF="#SINGLEROW"><SMALL CLASS="allcaps">SINGLEROW</SMALL></A>
(a single row according to <CODE>$rowstyle</CODE>), <A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A> (a hash, keyed by
the data in the result set), <A HREF="#NORESULT"><SMALL CLASS="allcaps">NORESULT</SMALL></A> (no result set returned) or a reference
to a callback routine that is called on each row. See the section
<A HREF="#RowResultStyles">Row Styles and Result Styles</A> in the
description for <A HREF="#sql"><NOBR>sql()</NOBR></A> for details. Default is <A HREF="#SINGLESET"><SMALL CLASS="allcaps">SINGLESET</SMALL></A>.</DD>
<DT> </DT>
<DT><CODE>\@keys</CODE></DT>
<DD>This parameter is only applicable when <CODE>$resultstyle</CODE> is
<A HREF="#KEYED"><SMALL CLASS="allcaps">KEYED</SMALL></A>, in which case it's mandatory. It specifies which column(s) in the
result that is/are the keys.</DD>
</DL>
<P>
You can only call <A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A> when you have executed a command batch with
<A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>. When <A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A> have completed, you can go on and fetch
output parameters with <A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A>. If there are no output parameters,
then all resources allocated for the batch are released, and Win32::SqlServer is ready to
accept a new call to <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A> or a
<A HREF="#High-level">high-level query function</A>.</P>
<P>
<A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A> is in fact the inner part of <A HREF="#sql"><NOBR>sql()</NOBR></A>
and <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, and
the return value and the style parameters works exactly as for these two. See
the section <A HREF="#RowResultStyles">Row Styles and Result Styles</A> in the
topic of <A HREF="#sql"><NOBR>sql()</NOBR></A> for details.</P>
<H2><A NAME="getoutputparams"><NOBR>getoutputparams()</NOBR></A></H2>
<P>Retrieves the output parameters from a command batch.</P>
<PRE>$sqlsrv->getoutputparams($hashref, $arrayref);</PRE>
<DL>
<DT><CODE>$hashref</CODE></DT>
<DD>A scalar variable that on output receives a reference to a hash. The keys
in the hash are the parameter names (don't forget the leading <CODE>@</CODE>)
you provided with <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>. For parameter for which you passed <CODE>undef</CODE>
for the name, the keys are <I>Par 1, Par 2</I> etc (without any <CODE>@</CODE>.)
You must pass a scalar, you cannot pass a reference to hash like <CODE><NOBR>
\%hash</NOBR></CODE>. You can pass <CODE>undef</CODE> if you don't care about
the hash.</DD>
<DT> </DT>
<DT><CODE>$arrayref</CODE></DT>
<DD>A scalar variable that on output, receives a reference to an array. The
output parameters appear in the array in the order you defined them. There
are no empty slots for input-only parameters. That is, if you enter
nine parameters, and parameters 2, 7 and 9 are output parameters, they will
appear at <CODE><NOBR>$$arrayref[0]</NOBR></CODE>, <CODE><NOBR>$$arrayref[1]</NOBR></CODE>
and <CODE><NOBR>$$arrayref[2]</NOBR></CODE>. </DD>
</DL>
You can only call <A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A> when you have retrieved all result sets with
<A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A> or <A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A>. You can only call <A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A> if the
command batch actually have any output parameters. If you are uncertain, you can
investigate this with <A HREF="#getcmdstate"><NOBR>getcmdstate()</NOBR></A>. Once you have called <A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A> all
resources allocated for the batch are released.
<H2><A NAME="cancelresultset"><NOBR>cancelresultset()</NOBR></A></H2>
<P>Discards all rows in the current result set.</P>
<PRE>$sqlsrv->cancelresult()</PRE>
<P>When you call <A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A>,
Win32::SqlServer moves directly to the end of the
result set without retrieving the rows. You must still call <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>
to get to the next result set.</P>
<P>If there is no active result set, <A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A> has no effect.</P>
<H2><A NAME="cancelbatch"><NOBR>cancelbatch()</NOBR></A></H2>
<P>Cancels the current command batch and frees all resources allocated for it.</P>
<PRE>$sqlsrv->cancelbatch()</PRE>
<P>You can call <A HREF="#cancelbatch"><NOBR>cancelbatch()</NOBR></A> at any time. Beware that cancelling a batch does
not roll back any transactions, neither any transactions started prior to
submitting the command batch, nor transactions started the command batch itself.</P>
<P>Since cancelling a command batch actually means that part of the command
batch may not be executed at all, using <A HREF="#cancelbatch"><NOBR>cancelbatch()</NOBR></A> casually can lead to
unexpected results. If you just want to discard all data, it's better go call
<A HREF="#get_result_sets"><NOBR>get_result_sets()</NOBR></A> with <CODE>$resultstyle</CODE> = <A HREF="#NORESULT"><SMALL CLASS="allcaps">NORESULT</SMALL></A>, or call
<A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A> for each result set.</P>
<H2><A NAME="getcmdstate"><NOBR>getcmdstate()</NOBR></A></H2>
<P>Returns the current command state for the Win32::SqlServer object.</P>
<PRE>$cmdstate = $sqlsrv->getcmdstate()</PRE>
<DL>
<DT><CODE>$cmdstate</CODE></DT>
<DD>The current command state. See below for possible value.</DD>
</DL>
<P>You can call <A HREF="#getcmdstate"><NOBR>getcmdstate()</NOBR></A> if you need to examine what to do next, for
instance if there are any output parameters to retrieve. These are the
possible return values:</P>
<DL>
<DT><B><A NAME="CMDSTATE_INIT">CMDSTATE_INIT</A></B></DT>
<DD>There is no active command batch, and to initiate one, you need to call
<A HREF="#initbatch"><NOBR>initbatch()</NOBR></A> (or a <A HREF="#High-level">high-level query function</A>).</DD>
<DT> </DT>
<DT><B><A NAME="CMDSTATE_ENTEREXEC">CMDSTATE_ENTEREXEC</A></B></DT>
<DD>A command batch have been entered with <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>, but it has not yet been
submitted for execution. You can now define parameters with <A HREF="#enterparameter"><NOBR>enterparameter()</NOBR></A>
or execute it with <A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>.</DD>
<DT> </DT>
<DT><B><A NAME="CMDSTATE_NEXTRES">CMDSTATE_NEXTRES</A></B></DT>
<DD>The command batch have been executed, and you can now retrieve the next
result set with <A HREF="#nextresultset"><NOBR>nextresultset()</NOBR></A>. You also get this value when you have
reached the end of the result set with <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> or
<NOBR><A HREF="#cancelresultset">cancelresultset()</A></NOBR>.</DD>
<DT> </DT>
<DT><B><A NAME="CMDSTATE_NEXTROW">CMDSTATE_NEXTROW</A></B></DT>
<DD>There is an active result set, and you should retrieve rows from it with
<A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> or skip it with <A HREF="#cancelresultset"><NOBR>cancelresultset()</NOBR></A>.</DD>
<DT> </DT>
<DT><B><A NAME="CMDSTATE_GETPARAMS">CMDSTATE_GETPARAMS</A></B></DT>
<DD>You have fetched all result sets, and there are output parameters
available. You should fetch these with <A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A>.</DD>
</DL>
These constants are not imported by default when you <CODE>use Win32::SqlServer</CODE>. Either refer to them as <CODE>Win32::SqlServer::CMDSTATE_INIT</CODE>
etc, import the constants you need implicitly or with an <A HREF="#EXPORTTAGS">export tag</A>.
<H2><A NAME="midlevelexamples">Mid-Level Functions</A> Examples</H2>
<P>Here are some examples using the mid-level functions. The first example has a
plain command batch with an output parameter and a result set.</P>
<PRE>use strict;
use Win32::SqlServer qw(CMDSTATE_GETPARAMS);
# Log in to local server with SQL authentication. Stay in tempdb.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('database', 'tempdb');
$sqlsrv->connect();
my $batch = <<'SQLEND';
SELECT ? = COUNT(*)
FROM Northwind..Orders
WHERE CustomerID = ?
AND OrderDate > ?
SELECT OrderID, OrderDate, Freight
FROM Northwind..Orders
WHERE CustomerID = ?
AND OrderDate > ?
SQLEND
$sqlsrv->initbatch($batch);
# Enter the parameters. $ok will stay 1 as long as all params are OK,
# This first is an output parameter. To make it easier to retrieve, we
# give it a name.
my $ok = $sqlsrv->enterparameter('int', -1, '@cnt', 0, 1);
# Then the parameters for the query. Since they both occur twice, we need
# to supply both twice. We don't care about the names.
$ok &= $sqlsrv->enterparameter('nchar', 5, undef, 1, 0, 'ALFKI');
$ok &= $sqlsrv->enterparameter('datetime', -1, undef, 1, 0, '1997-06-01');
$ok &= $sqlsrv->enterparameter('nchar', 5, undef, 1, 0, 'ALFKI');
$ok &= $sqlsrv->enterparameter('datetime', -1, undef, 1, 0, '1997-06-01');
if ($ok) {
$ok = $sqlsrv->executebatch();
}
if ($ok) {
while ($sqlsrv->nextresultset()) {
my ($rowref);
while ($sqlsrv->nextrow($rowref, undef)) {
print "OrderID: $$rowref{OrderID} ";
print "OrderDate: " . substr($$rowref{OrderDate}, 0, 10), " ";
print "Freight: $$rowref{Freight}\n";
}
}
if ($sqlsrv->getcmdstate() == CMDSTATE_GETPARAMS) {
my ($paramref);
$sqlsrv->getoutputparams($paramref, undef);
print "The count is: $$paramref{'@cnt'}.\n";
}
}
else {
# Something went wrong somewhere. Issue a cancelbatch to be sure.
$sqlsrv->cancelbatch();
}</PRE>
<P>And here is an example that creates a stored procedure with the same contents
as above. Here we use the <CODE>$arrayref</CODE> for <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> and
<A HREF="#getoutputparams"><NOBR>getoutputparams()</NOBR></A> for the sake of the example.</P>
<PRE>use strict;
use Win32::SqlServer qw(:cmdstates);
# Log in to local server with SQL authentication. Stay in tempdb.
my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('database', 'tempdb');
$sqlsrv->connect();
my $prc = <<'SQLEND';
CREATE PROCEDURE #orderswithcnt @custid nchar(5),
@orderdate datetime,
@no_of_orders int OUTPUT AS
SELECT @no_of_orders = COUNT(*)
FROM Northwind..Orders
WHERE CustomerID = @custid
AND OrderDate > @orderdate
SELECT OrderID, OrderDate, Freight
FROM Northwind..Orders
WHERE CustomerID = @custid
AND OrderDate > @orderdate
SQLEND
# Create the procedure. We don't call nextrow here, since there should be
# no rows from this batch.
$sqlsrv->initbatch($prc);
$sqlsrv->executebatch();
1 while $sqlsrv->nextresultset();
# Now let's try to run the procedure.
$sqlsrv->initbatch('{? = call #orderswithcnt(?, ? , ?)}');
# Enter the parameters. $ok will stay 1 as long as all params are OK,
# Now the first parameter is the return value.
my $ok = $sqlsrv->enterparameter('int', -1, undef, 0, 1);
# Then the parameters for the query.
$ok &= $sqlsrv->enterparameter('nchar', 5, '@custid', 1, 0, 'ALFKI');
$ok &= $sqlsrv->enterparameter('datetime', -1, '@orderdate', 1, 0, '1997-06-01');
# And the output parameter. But T-SQL has no OUTPUT-only parameter,
# so this is input and output.
$ok = $sqlsrv->enterparameter('int', -1, '@no_of_orders', 1, 1, undef);
if ($ok) {
$sqlsrv->executebatch();
}
else {
# Some parameter failed. Drop out.
$sqlsrv->cancelbatch();
exit;
}
# We can use getcmdstate to see if everything is OK. (Actually, we would
# not be here if there was an error, as the default behaviour is to abort
# in case of error.)
if ($sqlsrv->getcmdstate() == CMDSTATE_NEXTRES) {
while ($sqlsrv->nextresultset()) {
my ($rowref);
# In this example we use the arrayref to retrieve the data.
while ($sqlsrv->nextrow(undef, $rowref)) {
print "OrderID: $$rowref[0] ";
print "OrderDate: " . substr($$rowref[1], 0, 10), " ";
print "Freight: $$rowref[2]\n";
}
}
}
if ($sqlsrv->getcmdstate() == CMDSTATE_GETPARAMS) {
my ($paramref);
$sqlsrv->getoutputparams(undef, $paramref);
print "The return value is: $$paramref[0].\n";
print "The count is: $$paramref[1].\n";
}</PRE>
<H1><A NAME="Conversion_Routines">ANSI/OEM Conversion Routines</A></H1>
<P>
These routines are retained from
<A HREF="http://www.sommarskog.se/mssqlperl/mssql-sqllib.html"><SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib</A> mainly to provide backward
compatibility. I have the feeling that the problem they address is not
that common today, the one of <SMALL CLASS="allcaps">ANSI/OEM</SMALL> conversion. Way back then, when DB-Library ruled the world, it was common
to have <SMALL CLASS="allcaps">SQL</SMALL> Server that used an <SMALL CLASS="allcaps">OEM</SMALL> code page, for instance CP437
or CP850.
Also, typically tools that worked from the command line, assumed that data was
in the <SMALL CLASS="allcaps">OEM</SMALL> code page – while in fact the files had been created in Windows tools
using an <SMALL CLASS="allcaps">ANSI</SMALL> code page. This lead to effect that when you talked an <SMALL CLASS="allcaps">OEM</SMALL> server
you did not get a conversion – when you had needed one. But if you connected to an
<SMALL CLASS="allcaps">ANSI</SMALL> server, you got an <SMALL CLASS="allcaps">OEM</SMALL>-to-<SMALL CLASS="allcaps">ANSI</SMALL> conversion of what already was <SMALL CLASS="allcaps">ANSI</SMALL>. In both
cases, this resulted in garbage in the database.</P>
<P>
Today, you mainly have <SMALL CLASS="allcaps">ANSI</SMALL>-to-<SMALL CLASS="allcaps">ANSI</SMALL> and then you have Unicode.
Character-conversion problems still appear, but they more concern when different
<SMALL CLASS="allcaps">ANSI</SMALL> pages collide. <SMALL CLASS="allcaps">OEM</SMALL> is not an equally big problem. Or at least I have this
impression from the questions I see on the <SMALL CLASS="allcaps">SQL</SMALL> Server newsgroups.</P>
<P>
Anyway, they pass the old test suite carried over from <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib even if
after I added a few more vile tests. But: I have only tested with an <SMALL CLASS="allcaps">ANSI</SMALL> server
– really what the <SMALL CLASS="allcaps">OLE DB</SMALL> provider does with an server using an <SMALL CLASS="allcaps">OEM</SMALL> code page I
don't know.
</P>
<P><B>Very important</B>: when you activate a conversion, Win32::SqlServer converts about
everything that comes it ways. Blindly. Command text. Parameter names. Column
names. Data. Without discrimination. There are at least two cases, when this
is not likely to be desirable. when you work with the Unicode data types (<B><CODE>nchar</CODE></B>,
<B><CODE>nvarchar</CODE></B>, <B><CODE>ntext</CODE></B> and <B><CODE>xml</CODE></B>)
and binary data (<B><CODE>binary</CODE></B>, <B><CODE>varbinary</CODE></B>,
<B><CODE>image</CODE></B> and <B><CODE>UDT</CODE></B>) when you have <B>
<A HREF="#binary">BinaryAsStr</A></B> = 0. Thus, you should not
activate <SMALL CLASS="allcaps">ANSI/OEM</SMALL> conversion under these conditions.</P>
<H2><A NAME="sql_set_conversion"><NOBR>sql_set_conversion()</NOBR></A> </H2>
<P>Activates a character-set conversion.</P>
<PRE>$sqlsrv->sql_set_conversion([$client_cs [, $server_cs [, $direction]]]);</PRE>
<DL>
<DT><CODE>$client_cs</CODE></DT>
<DD>The character set for your client-side data. Specify the character set as
a code page, with or without CP. That is, both "850" and "CP850" are good
values. You can also specify <SMALL CLASS="allcaps">ANSI</SMALL> or <SMALL CLASS="allcaps">OEM</SMALL>, and <A HREF="#sql_set_conversion"><NOBR>sql_set_conversion()</NOBR></A> will look
the corresponding code page from <NOBR><CODE>
HKEY_LOCAL_MACHINESYSTEM\CurrentControlSet\Control\Nls\CodePage</CODE></NOBR>
in the registry. The default value is the <SMALL CLASS="allcaps">OEM</SMALL> character set for the client.
</DD>
<DT> </DT>
<DT><CODE>$server_cs</CODE></DT>
<DD>The character set of the server data. As for <CODE>$client_cs</CODE> you can specify
code page with or without CP. The default value is the code page for the
default collation on the server.</DD>
<DT> </DT>
<DT><CODE>$direction</CODE></DT>
<DD>In which direction conversion is to take place. Three possible values:
<SMALL CLASS="allcaps">TO_CLIENT_ONLY</SMALL> (convert only data from <SMALL CLASS="allcaps">SQL</SMALL> server) <SMALL CLASS="allcaps">TO_SERVER_ONLY</SMALL> (convert
only when sending data to <SMALL CLASS="allcaps">SQL</SMALL> Server) and <SMALL CLASS="allcaps">TO_SERVER_CLIENT</SMALL> (convert in both
directions). These constants are not imported by default. Either denote them
as <CODE><NOBR>Win32::SqlServer::TO_CLIENT_ONLY</NOBR></CODE>, or import them
explicitly or with an <A HREF="#EXPORTTAGS">export tag</A>.</DD>
</DL>
<P>To set up conversion <A HREF="#sql_set_conversion"><NOBR>sql_set_conversion()</NOBR></A> looks in the
<SMALL CLASS="allcaps">SYSTEM32</SMALL> directory of
the Windows installation as pointed to by the environment variable <I>SystemRoot</I>. In
this directory, it looks for the file <SMALL CLASS="allcaps">SSSSCCCC.CPX</SMALL> and if this fails an
attempt is made with <SMALL CLASS="allcaps">CCCCSSSS.CPX . SSSS</SMALL> is the code-page number for the
server character set, and <SMALL CLASS="allcaps">CCCC</SMALL> is the code-page number for the client
character set. For instance, the file for converting to CP850 to Latin-1
(CP1252) is 12520850.<SMALL CLASS="allcaps">CPX</SMALL>. If none of the files are found, execution is
aborted.</P>
<P>If <CODE>$clent_cs</CODE> and <CODE>$server_cs</CODE> are the same, the net
effect that any active conversion is removed.</P>
<P>If you intend to use this function, please refer to
the description of the function in the manual page for
<A HREF="http://www.sommarskog.se/mssqlperl/mssql-sqllib.html#sql_set_conversion"><SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib</A>
for some gory details that I have left out here.</P>
<H2><A NAME="sql_unset_conversion"><NOBR>sql_unset_conversion()</NOBR></A> </H2>
<P>Removes any previous conversion.</P>
<PRE>$sqlsrv->sql_unset_conversion([$direction]);</PRE>
<DL>
<DT><CODE>$direction</CODE></DT>
<DD>Specifies in which directions conversion is to be removed. Legal values
are <SMALL CLASS="allcaps">TO_CLIENT_ONLY</SMALL> (remove only conversion for data going to the client),
<SMALL CLASS="allcaps">TO_SERVER_ONLY</SMALL> (removes conversion only for data going to the server),
<SMALL CLASS="allcaps">TO_SERVER_CLIENT</SMALL> (removes all conversions). The default is <SMALL CLASS="allcaps">TO_SERVER_CLIENT</SMALL>.</DD>
</DL>
<H1><A NAME="Utility_Routines">Utility Routines</A></H1>
<H2><A NAME="sql_has_errors"><NOBR>sql_has_errors()</NOBR></A></H2>
<P>Returns whether there has been any errors on the Win32::SqlServer object. Presumes that
<B><A HREF="#SaveMessages">SaveMessages</A></B> is in effect.</P>
<PRE>$haserrors = $sqlsrv->sql_has_errors([$keepinfomsgs]);</PRE>
<DL>
<DT><CODE>$haserrors</CODE></DT>
<DD>True if there are errors in the <B><A HREF="#Messages">Messages</A></B>
array in <B><A HREF="#ErrInfo">ErrInfo</A></B>, else false.</DD>
<DT> </DT>
<DT><CODE>$keepinfomsgs</CODE></DT>
<DD>Normally, if there are only informational messages and warnings in <B>
<A HREF="#Messages">Messages</A></B>, <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A> deletes the <B><A HREF="#Messages">Messages</A></B> array from <B>
<A HREF="#ErrInfo">ErrInfo</A></B> to clear it.
Pass a true value for this parameter to keep the messages. Note that if there are errors in
<B><A HREF="#Messages">Messages</A></B>, <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A> retains all messages in <B><A HREF="#Messages">Messages</A></B>, and you
have to clear it yourself.</DD>
</DL>
<P>The Win32::SqlServer property <B><A HREF="#ErrInfo">ErrInfo</A></B> has an element <B><A HREF="#Messages">Messages</A></B> into which
<A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, Win32::SqlServers default message handler, saves messages from <SMALL CLASS="allcaps">SQL</SMALL>
Server if the <B><A HREF="#ErrInfo">ErrInfo</A></B> element <B><A HREF="#SaveMessages">SaveMessages</A></B> is true. In this case
you can check for errors by calling <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A>. </P>
<P>If <B><A HREF="#SaveMessages">SaveMessages</A></B> is not enabled when you
call <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A>, and you have Perl warnings enabled,
Win32::SqlServer issues a
warning.</P>
<H2><A NAME="sql_get_command_text"><NOBR>sql_get_command_text()</NOBR></A></H2>
<P>Returns the text for the current command batch.</P>
<PRE>$cmdtext = $sqlsrv->sql_get_command_text();</PRE>
<DL>
<DT><CODE>$cmdtext</CODE></DT>
<DD>If the current batch is parameterless, <CODE>$cmdtext</CODE> is simply
the text of the batch. For a parameterised batch, <CODE>$cmdtext</CODE> is an
<CODE>EXEC</CODE> of <I>sp_executesql</I> corresponding to the <SMALL CLASS="allcaps">RPC</SMALL> call
Win32::SqlServer actually performs. (On <SMALL CLASS="allcaps">SQL</SMALL> 6.5 you get a faked call, since <I>
sp_executesql</I> does not exist in 6.5.) For a call to <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, you get the
<SMALL CLASS="allcaps">EXEC</SMALL> command your call translates to (although, again, the
actual call is over <SMALL CLASS="allcaps">RPC</SMALL>.) If you use the <A HREF="#Mid-level">mid-level query routines</A>;
<CODE>$cmdtext</CODE> is simply the text you passed to <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>.</DD>
</DL>
<P>The one place where this routine can be useful to you, is if you write your
own <A HREF="#MsgHandler">message handler</A>, and you want to display the lines
for the current command.</P>
<H2><A NAME="sql_string"><NOBR>sql_string()</NOBR></A></H2>
<P>Quotes a string according to <SMALL CLASS="allcaps">SQL</SMALL> rules.</P>
<PRE>$quotedstring = [$sqlsrv->]sql_string($string);</PRE>
<DL>
<DT><CODE>$quoutedstring</CODE></DT>
<DD>Is <CODE>$string</CODE> bracketed by single quotes(<CODE>'</CODE>), and
with any single quotes in <CODE>$string</CODE> doubled. For instance <CODE>
O'Brien</CODE> becomes
<CODE>'O''Brien'</CODE>. If <CODE>$string</CODE> is <CODE>undef</CODE>, the return value
is the string
<CODE>NULL</CODE>.</DD>
<DT> </DT>
<DT><CODE>$sqlsrv</CODE></DT>
<DD>An Win32::SqlServer object. You don't have to specify it (it's not really used
anyway). <A HREF="#sql_string"><NOBR>sql_string()</NOBR></A> is one of the two items that are imported by default
when you <CODE>use Win32::SqlServer</CODE>.</DD>
<DT> </DT>
<DT><CODE>$string</CODE> </DT>
<DD>An unquoted string that needs to be quoted to be included in an <SMALL CLASS="allcaps">SQL</SMALL>
string.</DD>
</DL>
<P>You can use <A HREF="#sql_string"><NOBR>sql_string()</NOBR></A> if you need to embed a string value into a string
that is to hold an <SMALL CLASS="allcaps">SQL</SMALL> command. However, rather than building complete <SMALL CLASS="allcaps">SQL</SMALL>
strings, you should <A HREF="#whyparameter">parameterise your commands</A> whenever possible.</P>
<H2><A NAME="sql_begin_trans"><NOBR>sql_begin_trans()</NOBR></A>,
<A NAME="sql_commit"><NOBR>sql_commit()</NOBR></A>, <A NAME="sql_rollback"><NOBR>sql_rollback()</NOBR></A></H2>
<P>Routines to start, commit and rollback transactions.</P>
<PRE>$sqlsrv->sql_begin_trans()
$sqlsrv->sql_commit()
$sqlsrv->sql_rollback()</PRE>
<P>
These routines are just plain wrappers saying <CODE>$sqlsrv->sql("BEGIN
TRANSACTION")</CODE>
etc. Win32::SqlServer does keep any track of its own for handling transactions.</P>
<P>
They are only included to provide compatibility with <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib.</P>
<H1><A NAME="OBJECT_PROPERTIES">Object Properties</A></H1>
<H2><A NAME="AutoConnect">AutoConnect</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD><TH>Default</TH><TD CLASS="extrapad">false</TD></TR>
</TABLE>
</P>
<P>If you set <B><A HREF="#AutoConnect">AutoConnect</A></B> to a true value, and you don't connect explicitly
with <A HREF="#connect"><NOBR>connect()</NOBR></A> or <A HREF="#sql_init"><NOBR>sql_init()</NOBR></A>,
Win32::SqlServer will connect when you submit a
command batch and then disconnect when the command batch has been processed. If
you use the <A HREF="#High-level">high-level query functions</A> only, connection and disconnection
happens entirely in these. If you use the <A HREF="#Mid-level">mid-level functions</A>, connection
happens at <A HREF="#executebatch"><NOBR>executebatch()</NOBR></A>, and disconnection when you have retrieved all
result sets and the output parameters.</P>
<P>Note that the default behaviour of <SMALL CLASS="allcaps">OLE DB</SMALL> is to pool connections, which means
that there is a lag time before the actual disconnection occurs. Setting
<B><A HREF="#AutoConnect">AutoConnect</A></B> on, if you disable connection pooling is likely to be a poor idea.
See further the login property <B> <A HREF="#Pooling">Pooling</A></B>.</P>
<P>When false, you must connect with <A HREF="#connect"><NOBR>connect()</NOBR></A> or <A HREF="#sql_init"><NOBR>sql_init()</NOBR></A> and remain
connected to submit queries.</P>
<H2><A NAME="BinaryAsStr">BinaryAsStr</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean/string</TD><TH>Default</TH><TD CLASS="extrapad">1</TD></TR>
</TABLE>
</P>
<P>Controls how input and output values of the binary data types are
interpreted. Please see the topic on data-type representation of
<A HREF="#binary">binary data</A> above for details.</P>
<H2><A NAME="CommandTimeout">CommandTimeout</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">integer</TD><TH>Default</TH><TD CLASS="extrapad">0</TD></TR>
</TABLE>
</P>
<P>How long Win32::SqlServer should wait before timing out on a command. The default is 0,
which means wait forever. A time-out error is communicated through the
<A HREF="#MsgHandler">current message handler</A>. Be aware of that what
happens is that the <SMALL CLASS="allcaps">OLE DB</SMALL> provider cancels the command, but does not
rollback any outstanding transactions, not even those started in the command
batch. Thus, if you run into a command timeout, you should issue <CODE>IF
<NOBR>@@trancount</NOBR> > 0 ROLLBACK TRANSACTION</CODE> to be safe.</P>
<P><SMALL CLASS="allcaps">ODBC, ADO</SMALL> and <SMALL CLASS="allcaps">ADO</SMALL> .Net
all have a default timeout of 30 seconds, but in my opinion command timeouts are a really poor idea, and should only be used if you have
a real reason.</P>
<H2><A NAME="DateFormat">DateFormat</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE><NOBR>
"%Y</NOBR><NOBR>%m</NOBR><NOBR>%d</NOBR> <NOBR>%H</NOBR>:<NOBR>%M</NOBR>:<NOBR>%S"</NOBR></CODE></TD></TR>
</TABLE>
</P>
<P>Controls how datetime values are formatted when <B><A HREF="#datetime">DatetimeOption</A></B> has the value
<A HREF="#DATETIME_STRFMT"><SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL></A>. See further the section on the <A HREF="#datetime">representation of datetime</A>
values.</P>
<H2><A NAME="DatetimeOption">DatetimeOption</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">enumerated</TD><TH>Default</TH><TD CLASS="extrapad"><A HREF="#DATETIME_ISO"><SMALL CLASS="allcaps">DATETIME_ISO</SMALL></A></TD></TR>
</TABLE>
</P>
<P>Controls how datetime value returned from <SMALL CLASS="allcaps">SQL</SMALL> Server are represented in Perl. See
further the section on the <A HREF="#datetime">representation of datetime</A>
values.</P>
<H2><A NAME="DecimalAsStr">DecimalAsStr</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD>
<TH>Default</TH><TD CLASS="extrapad">false</TD></TR>
</TABLE>
</P>
<P>Whether Win32::SqlServer should return data of the types <B><CODE>bigint</CODE></B>,
<B><CODE>decimal</CODE></B>, <B><CODE>numeric</CODE></B>, <B><CODE>money </CODE>
</B>and <B><CODE>smallmoney</CODE></B> as strings or floating-point values. See
further the section on <A HREF="#bignumbers">representation of these data
types</A>.</P>
<H2>ErrInfo</H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad"><CODE>ref</CODE> to struct</TD><TH>Default</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">N/A</SMALL></TD></TR>
</TABLE>
</P>
<P>A complex structure through which you can control how Win32::SqlServer should behave
when there is an <SMALL CLASS="allcaps">SQL</SMALL> error. This property is detailed in the chapter on
<A HREF="#ErrInfo">Error
Handling</A> below.</P>
<H2><A NAME="LogHandle">LogHandle</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad"><CODE>ref</CODE> to file handle</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD></TR>
</TABLE>
</P>
<P>When you assign the <B><A HREF="#LogHandle">LogHandle</A></B> property a file-handle reference, the
<A HREF="#High-level">high-level query functions</A> will write the command batches they pass to <SMALL CLASS="allcaps">SQL</SMALL>
Server. This does not include queries they submit themselves to find the
parameters of stored procedures etc. The <A HREF="#Mid-level">mid-level
functions</A> do not perform any logging.</P>
<P>The logging is implemented with <A HREF="#sql_get_command_text"><NOBR>sql_get_command_text()</NOBR></A>, see this function
of what you get in the log.</P>
<P>Example:</P>
<PRE>open(F, 'MyFile.log');
$sqlsrv->{LogHandle} = \*F;
</PRE>
<H2><A NAME="MsecFormat">MsecFormat</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><CODE><NOBR>
"%3</NOBR>.3d"</CODE></TD></TR>
</TABLE>
</P>
<P>Controls how the milliseconds portion of a datetime value is formatted
when <B><A HREF="#datetime">DatetimeOption</A></B> has the value <A HREF="#DATETIME_STRFMT"><SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL></A>. See further the section on the
<A HREF="#datetime">representation of datetime</A> values.</P>
<H2>MsgHandler</H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad"><CODE>ref</CODE> to <CODE>sub</CODE></TD><TH>Default</TH>
<TD CLASS="extrapad"><CODE>\&Win32::SqlServer::sql_message_handler</CODE></TD></TR>
</TABLE>
</P>
<P>The currently installed message handler, which is invoked when <SMALL CLASS="allcaps">SQL</SMALL> Server
sends a message (error, warning or informational). Also be called in case of errors
from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider or and for warnings and errors from
Win32::SqlServer itself.
This property is detailed in the chapter on <A HREF="#MsgHandler">Error
Handling</A>.</P>
<H2><A NAME="NoExec">NoExec</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD><TH>Default</TH><TD CLASS="extrapad">false</TD></TR>
</TABLE>
</P>
<P>When set, the <A HREF="#High-level">high-level query functions</A> will not
submit your queries to <SMALL CLASS="allcaps">SQL</SMALL> Server, and they will always return empty result
set. However they will still submit calls to <SMALL CLASS="allcaps">SQL</SMALL> Server to retrieve meta-data.</P>
<P>The <A HREF="#Mid-level">mid-level query functions</A> do not honour this
flag. </P>
<H2><A NAME="procs">procs</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">ref to hash</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD></TR>
</TABLE>
</P>
<P>This hash is used by <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> to cache parameter profiles for stored
procedures. You should never write anything to <B><A HREF="#procs">procs</A></B>. The only permissible
operation is to delete of a key value if you need to force <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> to refresh the cache.</P>
<H2><A NAME="PropsDebug">PropsDebug</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">boolean</TD><TH>Default</TH><TD CLASS="extrapad">false</TD></TR>
</TABLE>
</P>
<P>If you get cryptic errors when login fails, because some login property
has a funny value, it <I>may</I> help to set this property.
Win32::SqlServer will
then dump login properties and their status in case of
an error at login.</P>
<H2><A NAME="Provider">Provider</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">enumeration</TD><TH>Default</TH><TD CLASS="extrapad">see below</TD></TR>
</TABLE>
</P>
<P>Win32::SqlServer can connect to <SMALL CLASS="allcaps">SQL</SMALL> Server though one of two <SMALL CLASS="allcaps">OLE DB</SMALL> providers:
<SMALL CLASS="allcaps">SQLOLEDB</SMALL> (<I>Microsoft <SMALL CLASS="allcaps">OLE DB</SMALL> Provider for <SMALL CLASS="allcaps">SQL</SMALL> Server</I>), which comes
with the <SMALL CLASS="allcaps">MDAC</SMALL> and Windows, and <SMALL CLASS="allcaps">SQLNCLI</SMALL> (<I>Microsoft <SMALL CLASS="allcaps">SQL</SMALL> Native Client</I>)
which is a redistributable component that comes with <SMALL CLASS="allcaps">SQL</SMALL> 2005. Only <SMALL CLASS="allcaps">SQL</SMALL>
Native Client has full support for <SMALL CLASS="allcaps">SQL</SMALL> 2005. Only <SMALL CLASS="allcaps">SQLOLEDB</SMALL> supports
connections to <SMALL CLASS="allcaps">SQL</SMALL> 6.5.</P>
<P>There are three different values:</P>
<DL>
<DT><B><A NAME="PROVIDER_SQLNCLI">PROVIDER_SQLNCLI</A></B></DT>
<DD>Use <SMALL CLASS="allcaps">SQL</SMALL> Native Client as the <SMALL CLASS="allcaps">OLE DB</SMALL> provider. This is the default if <SMALL CLASS="allcaps">SQL</SMALL>
Native Client is installed.</DD>
<DT> </DT>
<DT><B><A NAME="PROVIDER_SQLOLEDB">PROVIDER_SQLOLEDB</A></B></DT>
<DD>Use the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider. This is the default if <SMALL CLASS="allcaps">SQL</SMALL> Native Client is
not available. Note that you need at least <SMALL CLASS="allcaps">MDAC</SMALL> 2.6 to be able to use
<SMALL CLASS="allcaps">SQLOLEDB</SMALL>.</DD>
<DT> </DT>
<DT><B><A NAME="PROVIDER_DEFAULT">PROVIDER_DEFAULT</A></B></DT>
<DD>Use this value when you want to set the provider to the default provider
for the machine. When you set the property to this value, Win32::SqlServer will
automatically change it to one of the other two.</DD>
</DL>
<P>You cannot set this property when you are connected. You can check whether
you are connected with the function <A HREF="#isconnected"><NOBR>isconnected()</NOBR></A>.</P>
<H2>QueryNotification</H2>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0 ID="table1">
<TR><TH>Data type</TH><TD CLASS="extrapad">ref to hash</TD><TH>Default</TH><TD CLASS="extrapad">
<CODE>{}</CODE>, that is, empty</TD></TR>
</TABLE>
<P>You use this property to subscribe to notifications from <SMALL CLASS="allcaps">SQL</SMALL> Server
when the result set of a query has changed. This hash has three elements: <B>
Service</B> (string, mandatory), <B>Message</B> (string, optional) and <B>
Timeout</B> (integer, optional). You see these elements before you run a
query, and when the query has executed, Win32::SqlServer clears this hash. (So that you
don't set up query notifications for everything by mistake.)</P>
<P>For more details, please see the section <A HREF="#QueryNotification">Using
Query Notifications with Win32::SqlServer</A>.</P>
<H2><A NAME="RowsAtATime">RowsAtATime</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">integer</TD><TH>Default</TH><TD CLASS="extrapad">100</TD></TR>
</TABLE>
</P>
<P>Controls how many rows at a time <A HREF="#nextrow"><NOBR>nextrow()</NOBR></A> requests from the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. The idea was that that if the <SMALL CLASS="allcaps">OLE DB</SMALL> provider requests one row
at a time from <SMALL CLASS="allcaps">SQL</SMALL> Server, this could hurt performance. But I have not
noticed any difference when I've set <B><A HREF="#RowsAtATime">RowsAtATime</A></B> to 1. So I suspect the <SMALL CLASS="allcaps">OLE
DB</SMALL> providers buffer rows as well.</P>
<H2><A NAME="SQL_version">SQL_version</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">string</TD><TH>Default</TH><TD CLASS="extrapad"><SMALL CLASS="allcaps">N/A</SMALL></TD></TR>
</TABLE>
</P>
<P>This is a read-only property, that returns the version string, e.g. <CODE>8.00.2039</CODE>, for the <SMALL CLASS="allcaps">SQL</SMALL> Server you are connected to. If you try to retrieve the value before you have
connected the first time, and neither have set <B><A HREF="#AutoConnect">AutoConnect</A></B>, this will fail,
as Win32::SqlServer in this case tries to connect() to get the version number.</P>
<P>Once you have connected, Win32::SqlServer will use a cached value. If you change any of
the login properties <B><A HREF="#ConnectionString">ConnectionString</A></B>,
<B><A HREF="#NetworkAddress">NetworkAddress</A></B> or <B>
<A HREF="#LogPropServer">Server</A></B>, Win32::SqlServer will flush the cached value.</P>
<H2><A NAME="tables">tables</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad"><CODE>ref</CODE> to hash</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD></TR>
</TABLE>
</P>
<P>This hash is used by <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> to cache column information for tables. You should never write anything to
<B>tables</B>. The only permissible
operation is to delete of a key value if you want to force <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A> to refresh the cache.</P>
<H2><A NAME="UserData">UserData</A></H2>
<P>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=0>
<TR><TH>Data type</TH><TD CLASS="extrapad">any</TD><TH>Default</TH><TD CLASS="extrapad"><CODE>undef</CODE></TD></TR>
</TABLE>
</P>
<P>This attribute is not referenced by Win32::SqlServer, but permits you to save your own
data with an Win32::SqlServer object, would you need it. (You cannot use any key value,
as an Win32::SqlServer object is a tied hash that will reject unknown keys.) If you need to
store more than one value, you can make <B><A HREF="#UserData">UserData</A></B> a hash, for instance:</P>
<PRE>$sqlsrv->{UserData}{MyData1} = 4711;</PRE>
<H1><A NAME="ErrorHandling">Error Handling</A></H1>
<H2>Introduction</H2>
<P>A very simple start: when <SMALL CLASS="allcaps">SQL</SMALL> Server reports
an error, Win32::SqlServer <CODE>croaks</CODE>, and if you want to catch that, you can use
<CODE>eval</CODE>. However, this is merely the default behaviour, and there
several options to change this.</P>
<P>When you submit a command batch to <SMALL CLASS="allcaps">SQL</SMALL> Server, the batch may produce
messages. These messages may be errors or purely informational. Since <SMALL CLASS="allcaps">T-SQL</SMALL>
offers both <SMALL CLASS="allcaps">RAISERROR</SMALL> and <SMALL CLASS="allcaps">PRINT</SMALL> you may think of
these as different things, but <SMALL CLASS="allcaps">PRINT</SMALL> is really only syntactic sugar for <CODE>
RAISERROR('Message', 0, 1)</CODE>. </P>
<P>The mechanism Win32::SqlServer uses to communicate these messages to your script is a <B>
callback</B>. You can register a callback that Win32::SqlServer invokes for each message
from <SMALL CLASS="allcaps">SQL</SMALL> Server by setting the <B>
<A HREF="#MsgHandler">MsgHandler</A></B> property. Normally you don't
have to write your own message handler though, because Win32::SqlServer comes with a default message
handler, <B><A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A></B>. This handler is highly configurable
through the Win32::SqlServer property <B><A HREF="#ErrInfo">ErrInfo</A></B>, and it is also through <B><A HREF="#ErrInfo">ErrInfo</A></B>
your script can get information about the exact messages. </P>
<P>This chapter describes the <B><A HREF="#ErrInfo">ErrInfo</A></B> property, and thus the behaviour of
<A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>. Then it goes on to discuss the <B><A HREF="#MsgHandler">MsgHandler</A></B>
property and how to set up your own message handler. At the end of the
chapter, there are some example scripts that shows <B><A HREF="#ErrInfo">ErrInfo</A></B> in play. But first
a short digression about possible sources for messages, and an even shorter
about error messages and <SMALL CLASS="allcaps">SQL</SMALL>states.</P>
<H2><A NAME="MsgSources">Sources for Messages</A></H2>
<P><SMALL CLASS="allcaps">SQL</SMALL> Server is the mostly likely source to produce an error or an
informational message, but it is not the only one. This
section looks at each possible source, and how Win32::SqlServer handles them.</P>
<H3>SQL Server</H3>
<P><SMALL CLASS="allcaps">SQL</SMALL> Server can produce errors or informational
messages. Informational messages have severity level 0, errors have a severity
of 11 or higher. (For more details on severity levels, see <B><A HREF="#MaxSeverity">MaxSeverity</A></B>
below.) Win32::SqlServer passes all messages from <SMALL CLASS="allcaps">SQL</SMALL> Server to the message handler
defined by <B><A HREF="#MsgHandler">MsgHandler</A></B>.</P>
<H3>The OLE DB Providers</H3>
<P>Win32::SqlServer uses the <SMALL CLASS="allcaps">OLE DB API</SMALL> to call <SMALL CLASS="allcaps">SQL</SMALL> Server; these <SMALL CLASS="allcaps">API</SMALL> calls can result in
errors. Win32::SqlServer has two different strategies for these errors. 1) <CODE>croak</CODE>. 2) Pass the error to the message
handler defined by <B><A HREF="#MsgHandler">MsgHandler</A></B>. </P>
<P>Win32::SqlServer has a preference the latter, but the provider does not always give
enough information for Win32::SqlServer to work from. Consider this sequence:</P>
<PRE>my $sqlsrv = new Win32::SqlServer;
$sqlsrv->setloginproperty('IntegratedSecurity', 'Bogus');
$sqlsrv->connect();</PRE>
<P>This results in the very cryptic:</P>
<PRE>Internal error: init_ptr->Initialize failed with 80040e21.
No further error infoformation was collected at F:\Test\example.pl line 7.</PRE>
<P>Provider errors are mainly related to
the login sequence (for instance, if the specified server cannot be found)
and the use of the <A HREF="#Mid-level">mid-level query routines</A>.
Win32::SqlServer
tries to detect conditions that would cause a provider error and forestall
the provider with its own
error or warning message, as the provider errors are often obscure.</P>
<P>There are also provider messages that dress up as <SMALL CLASS="allcaps">SQL</SMALL> Server messages, so
that Win32::SqlServer cannot tell the difference. A typical example is <I>Specified <SMALL CLASS="allcaps">SQL</SMALL>
server not found.</I> Win32::SqlServer will tell you that this is <SMALL CLASS="allcaps">SQL</SMALL> Server message 6,
but there is no such message – and of course, <SMALL CLASS="allcaps">SQL</SMALL> Server did not itself
report that it wasn't found.</P>
<H3>Win32::SqlServer</H3>
<P>Win32::SqlServer can detect errors on its own, and as for <SMALL CLASS="allcaps">OLE DB</SMALL> errors,
Win32::SqlServer can 1)
<CODE>croak</CODE> or 2) invoke the current message
handler. Win32::SqlServer can also emit warnings, and these can be reported 1) with
<CODE>carp</CODE> or 2) through the current message handler.</P>
<P>The basic philosophy is that Win32::SqlServer uses <CODE>croak</CODE>/<CODE>carp</CODE>
when then error is related to Perl usage and the message handler when the
condition is related to <SMALL CLASS="allcaps">SQL</SMALL>. So a call <NOBR>
to
<A HREF="#executebatch">executebatch()</A></NOBR> without first having called <NOBR> <A HREF="#initbatch">initbatch()</A></NOBR>
results in an unconditional <CODE>croak</CODE>. While if <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>
is not able to find the stored procedure you want it to call, this goes to
the message handler. </P>
<P>Note: don't rely too much on what is what, as a certain error could be
treated differently in a future release. As a hint: it is more likely that
<CODE>croak</CODE>/<CODE>carp</CODE> is replaced by a call to the message
handler, than vice versa.</P>
<H3>IDataConvert</H3>
<P>IDataConvert is an <SMALL CLASS="allcaps">OLE DB</SMALL> interface that
Win32::SqlServer uses to convert data from
Perl to <SMALL CLASS="allcaps">SQL</SMALL> Server and vice versa. Errors when converting input data are
communicated through the current message handler, as if they came from
Win32::SqlServer
itself; you will never see IDataConvert in an error message.</P>
<P>If an error occurs when converting data from <SMALL CLASS="allcaps">SQL</SMALL> Server back to Perl,
Win32::SqlServer
will always <CODE>croak</CODE>. Such an error can only occur if there is a
bug in Win32::SqlServer, or if <SMALL CLASS="allcaps">SQL</SMALL> Server produces garbage.</P>
<H3>Other sources</H3>
<P>Errors and warnings can also come from Perl, Win32 system calls and similar.
They are never communicated through any message handler, but always through
<CODE>croak</CODE>/<CODE>carp</CODE>.</P>
<H2><A NAME="errornumbers">Error Numbers and SQLstates</A></H2>
<P>All errors from <SMALL CLASS="allcaps">SQL</SMALL> Server have an error number, from 1 and up. Errors below
50000 are from <SMALL CLASS="allcaps">SQL</SMALL> Server itself (or a system stored procedure or similar).
Errors from 50000 and up are from <CODE>RAISERROR</CODE>. <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>
permits you to control by a specific error number whether Win32::SqlServer should abort
on it or not.</P>
<P>Win32::SqlServer errors always have number -1. (Currently, I should hasten to add. A
future version may use more negative numbers.) This is also true for provider errors (save
for
those that look like <SMALL CLASS="allcaps">SQL</SMALL> Server messages).</P>
<P>SQLstates are five-letter codes, defined by the <SMALL CLASS="allcaps">ODBC</SMALL>
standard. set by the <SMALL CLASS="allcaps">OLE DB</SMALL> provider for <SMALL CLASS="allcaps">SQL</SMALL>
Server errors and some its own errors as well. For <SMALL CLASS="allcaps">SQL</SMALL> Server errors these are of less
interest, since an SQLstate can cover several error numbers from <SMALL CLASS="allcaps">SQL</SMALL>
Server. For provider errors, on the other hand, SQLstate is the only way to
identify the error. (This includes the provider errors that dress up as <SMALL CLASS="allcaps">SQL</SMALL>
Server errors.) You can find a list
of all SQLstates in the <SMALL CLASS="allcaps">MDAC</SMALL> Books Online, or in <SMALL CLASS="allcaps">MSDN</SMALL> Library. The section is
entitled <I>Appendix <SMALL CLASS="allcaps">A: ODBC</SMALL> Error Codes</I> and is found in the <I><SMALL CLASS="allcaps">ODBC</SMALL>
Programmer's Reference</I>.</P>
<P>Not all provider errors have SQLstate. For these errors, Win32::SqlServer passes the
return code from the <SMALL CLASS="allcaps">OLE DB API</SMALL> function that failed
as the "SQLstate",
formatted as an 8-character hex string. The main purpose of this hex string
to diagnose problems in Win32::SqlServer itself.</P>
<P>Win32::SqlServer errors never have an SQLstate.</P>
<H2><A NAME="ErrInfo">ErrInfo</A></H2>
<P><B><A HREF="#ErrInfo">ErrInfo</A></B> is a hash and some of the elements are hashes or arrays
in their turn. Here
is a pseudo-code declaration of <B><A HREF="#ErrInfo">ErrInfo</A></B>:</P>
<PRE>RECORD ErrInfo
-- Abort control: On which messages to abort/disconnect.
MaxSeverity integer = 10;
NeverStopOn HASH OF boolean = undef;
AlwaysStopOn HASH OF boolean = undef;
DisconnectOn HASH OF boolean =
{'2745' => 1, '4003' => 1, '5702' => 1, '17308' => 1,
'17310' => 1, '17311' => 1, '17571' => 1, '18002' => 1,
'08001' => 1, '08003' => 1, '08004' => 1, '08007' => 1,
'08S01' => 1};
-- Print control: what parts of a message to print and where.
PrintMsg integer = 1;
PrintText integer = 0;
PrintLines integer = 11;
CarpLevel integer = 10;
NeverPrint HASH OF boolean = {'5701' => 1, '5703' => 1};
AlwaysPrint HASH OF boolean = {'3606' => 1, '3607' => 1, '3622' => 1};
LinesWindow integer = undef;
ErrFileHandle IO::File = STDERR; -- Where to print errors.
-- Abort control for return values.
CheckRetStat flag = 1;
RetStatOK HASH OF boolean = undef;
-- Return error messages to caller
SaveMessages boolean = 0;
Messages ARRAY OF RECORD
Source string;
Errno integer;
Severity integer;
State integer;
Server string;
Proc string;
Line string;
Text string;
SQLstate string;
END;
END</PRE>
<P>Like Win32::SqlServer itself, <B><A HREF="#ErrInfo">ErrInfo</A></B> is a tied hash that permits only
the defined
keys to be used. Thus, if you try to access
an non-existing element, Win32::SqlServer will <CODE>croak</CODE>. In order to provide
compatibility with <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, initial lowercase is permitted. (And, yes,
<I>neverPrint</I> and <I>NeverPrint</I> are the same.)</P>
<H3><A NAME="ErrInfoAbort_control">Abort Control</A></H3>
<P>These elements control whether <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> will abort execution
or <A HREF="#disconnect"><NOBR>disconnect()</NOBR></A> or because of the message.</P>
<DL>
<DT><B><A NAME="MaxSeverity">MaxSeverity</A></B></DT>
<DD>The maximum severity level permitted. That is, if the severity level of
the message equals <B><A HREF="#MaxSeverity">MaxSeverity</A></B>, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> will not abort
execution, but if the severity level is higher, execution will be aborted.
The default value is 10, which means to stop on any error from <SMALL CLASS="allcaps">SQL</SMALL> Server.
<P>
Some guidelines may be in place here, by looking at how the severity levels
are used:</P>
<P>
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
<TR><TH>Level</TH><TH>Comment</TH></TR>
<TR><TD>0</TD><TD>Level 0 is informational messages from <SMALL CLASS="allcaps">SQL</SMALL> Server. </TD></TR>
<TR><TD>1-9</TD><TD>As far as I know, <SMALL CLASS="allcaps">SQL</SMALL> Server never itself generates
messages with this level, but you can use <SMALL CLASS="allcaps">RAISERROR</SMALL> to
produce messages with this level. </TD></TR>
<TR><TD>10</TD><TD><SMALL CLASS="allcaps">SQL</SMALL> Server cannot produce messages
with this level. If you specify level 10 with <SMALL CLASS="allcaps">RAISERROR</SMALL>, you do in
fact get a message with level 0. Win32::SqlServer, on the other hand, can issue
messages with severity level 10, and they are warnings or provide
supplementary message ahead of anticipated errors (for instance failed
conversion of an input value).</TD></TR>
<TR><TD><NOBR>11-16</NOBR></TD><TD>For normal <SMALL CLASS="allcaps">SQL</SMALL> errors – what Books Online calls
"user errors" – <SMALL CLASS="allcaps">SQL</SMALL> Server uses the levels 11-16, in a fairly arbitrary
fashion. For instance, a <SMALL CLASS="allcaps">NOT NULL</SMALL> violation is level 16,
while a permission error is level 13. The severity of Win32::SqlServer errors or errors from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider
is always 16.</TD></TR>
<TR><TD>17-25</TD><TD>Level 17 and above are various resource errors, such as
running out of disk space, or internal errors in <SMALL CLASS="allcaps">SQL</SMALL> Server. In difference
to levels 11-16, it really gets more fatal for each level. Then again, if the severity is 20 or higher,
<SMALL CLASS="allcaps">SQL</SMALL> Server terminates the connection, so for your script the difference
between 20 and 25 is academic. See more about these levels in Books
Online.</TD></TR>
</TABLE>
</P>
<P>From this
follows that the only reasonable values for <B><A HREF="#MaxSeverity">MaxSeverity</A></B> are 10
(stop on any error), 16 (permit users errors, but stop on resource
errors), 17-19 (stop on real fatal errors) and 25 (never stop).</P>
<P>If you decide to set <B><A HREF="#MaxSeverity">MaxSeverity</A></B> to a higher level than 10, you
should have a plan to handle errors. If you say "I can't
afford to let the script to abort", then ask yourself you afford to let the script
continue if there was an unhandled error? Maybe you failed to write some essential
data. Maybe a deadlock prevented you from reading important data, causing
the script to produce a bogus result.</P>
<P>
One strategy is to set <B><A HREF="#SaveMessages">SaveMessages</A></B>, and then use the utility
function <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A> after each call to an
Win32::SqlServer function to see if there
was an error. There are examples of this at the
<A HREF="#errorhandlingexamples">end of this section</A>.</P></DD>
<DT><B><A NAME="NeverStopOn">NeverStopOn</A></B></DT>
<DD>A hash keyed by <SMALL CLASS="allcaps">SQL</SMALL> Server message numbers or <A HREF="#errornumbers">SQLstates</A> from the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. A true value means that for this message, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> should not abort
execution.
<P>
Say you want to delete a row if there are no references to it. Rather that
doing an <SMALL CLASS="allcaps">IF EXISTS</SMALL> for each referencing table, you can to this:</P>
<PRE>$sqlsrv->{ErrInfo}{NeverStopOn}{547}++;
$sqlsrv->{ErrInfo}{NeverPrint}{547}++;
$sqlsrv->sql('DELETE tbl WHERE keycol = ?', [['int', $value]]);
delete $sqlsrv->{ErrInfo}{NeverStopOn}{547};
delete $sqlsrv->{ErrInfo}{NeverPrint}{547}++;</PRE>
<P>
This both suppresses the error message and prevents execution from being
aborted. At the same time, if there are other errors for which you don't
handle, for instance a deadlock or a permission error, they will abort
execution.</P>
<P>
By default, <B><A HREF="#NeverStopOn">NeverStopOn</A></B> is empty.</P></DD>
<DT><B><A NAME="AlwaysStopOn">AlwaysStopOn</A></B></DT>
<DD>A hash keyed by <SMALL CLASS="allcaps">SQL</SMALL> Server message numbers or <A HREF="#errornumbers">SQLstates</A> from the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. A true value
means that for this message, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> should always abort
execution, even if the message has a severity level <= <B><A HREF="#MaxSeverity">MaxSeverity</A></B>.
<P>By default, <B><A HREF="#AlwaysStopOn">AlwaysStopOn</A></B> is empty.</P></DD>
<DT><B><A NAME="DisconnectOn">DisconnectOn</A></B></DT>
<DD>A hash keyed by <SMALL CLASS="allcaps">SQL</SMALL> Server message numbers or <A HREF="#errornumbers">SQLstates</A> from the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. A true value means that for this message, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> should issue a call to
<NOBR>
<A HREF="#disconnect">disconnect()</A> </NOBR>to make sure that the
connection with <SMALL CLASS="allcaps">SQL</SMALL> Server is terminated..
<P>As you can see from the pseudo-declaration above,
by default quite a few message numbers and SQLstates are added to this hash.
All of these indicate that the physical connection with <SMALL CLASS="allcaps">SQL</SMALL> Server has been
severed. They appear in <B><A HREF="#DisconnectOn">DisconnectOn</A></B>, as the call to to <A HREF="#disconnect"><NOBR>disconnect()</NOBR></A> is
needed for Win32::SqlServer to change its internal state to "disconnected". Thus, you
should never remove any of the default entries from <B><A HREF="#DisconnectOn">DisconnectOn</A></B>.</P>
<P>Besides the listed errors,
<A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, always disconnects if the severity level is >= 20,
since in this case <SMALL CLASS="allcaps">SQL</SMALL> Server has already severed the connection. This is
not configurable.</P>
<P>
In many cases, it makes sense to reconnect. For instance, if <SMALL CLASS="allcaps">SQL</SMALL> Server was
restarted, your first query will get a <I>General Network Error</I> message
from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider with SQLstate 08S01. If you detect this, you can
reconnect. If you run with <B><A HREF="#AutoConnect">AutoConnect</A></B> on,
you don't even have to look for it.</P>
<P>
However, keep in mind that if you get disconnected, and you had some sort of transaction
in progress, it would be wrong to continue with that transaction as if nothing happened,
since the transaction would have been lost and rolled back.</P>
<P>
You can check whether you are connected with the function <A HREF="#isconnected"><NOBR>isconnected()</NOBR></A>.
(Note that this function reports Win32::SqlServer's internal connection state.)</P>
<P>
Which messages would you add to <B><A HREF="#DisconnectOn">DisconnectOn</A></B>? Well, I can think of one: SQLstate HYT00,
<I>Timeout expired</I>. Command timeouts are messy, because you don't really
know what state the command batch was left in. Particularly, the batch may
have started a transaction without your knowing. An easy way out could be to
disconnect and reconnect to give you a known state.<B> Note</B>: by default command timeouts are
not enabled in Win32::SqlServer, see further the property <B>
<A HREF="#CommandTimeout">CommandTimeout</A></B>.</P></DD>
</DL>
<H3><A NAME="errinfo_Print_control">Print Control</A></H3>
<P>A full-blown error message from Win32::SqlServer looks like any of these samples:</P>
<PRE>SQL Server message 15025, Severity 16, State 1, Server KESÄMETSÄ
Procedure sp_addlogin, Line 57
The login 'sommar' already exists.
1> EXEC sp_addlogin @loginame = N'sommar'
Message from SQL Server at F:\Test\example.pl line 8</PRE>
<PRE>Message HYT00 from 'Microsoft OLE DB Provider for SQL Server', Severity: 16
Timeout expired
1> WAITFOR DELAY '00:00:05'
Message from Microsoft OLE DB Provider for SQL Server at F:\Test\example.pl line 9</PRE>
<PRE>Message -1 from 'Win32::SqlServer', Severity: 16
One or more parameters were not convertible. Cannot execute query.
1> EXEC sp_executesql N'SELECT @P1',
2> N'@P1 datetime',
3> @P1 = '999'
Message from Win32::SqlServer at F:\Test\example.pl line 8</PRE>
<P>In the context of print-control in <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, these messages falls
into four parts, each controlled by an <B><A HREF="#ErrInfo">ErrInfo</A></B> element,
as detailed below.</P>
<P>By default, errors are written to <SMALL CLASS="allcaps">STDERR, this can be
over-ridden with <B><A HREF="#ErrFileHandle">ErrFileHandle</A></B>, see
below.</SMALL></P>
<DL>
<DT><B><A NAME="PrintMsg">PrintMsg</A></B></DT>
<DD>The minimum severity level for which <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> prints the
"header information" of the message. In the examples, this is the
first two lines in the first example and the first line only in the second
examples. You get
message number, severity level, state as well as server, procedure and line if
these are available. Fairly similar to what you see in Query Analyzer.
<P>
For messages from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider,
the message "number" is the <A HREF="#errornumbers">SQLstate</A> for the error. For some errors, <SMALL CLASS="allcaps">OLE DB</SMALL>
does not produce any SQLstate. In this case, you will in this case see a
eight-character hex string which is the return code from
<SMALL CLASS="allcaps">the OLE DB</SMALL> <SMALL CLASS="allcaps">API</SMALL> call that appears as
"procedure". In this case, please ignore the return code and the procedure,
and try to grasp the message text (although it is far from often helpful). Such errors can be the result of user
errors – but this is also how internal errors in Win32::SqlServer if it makes in
an incorrect <SMALL CLASS="allcaps">API</SMALL> call.</P>
<P>
The default is 1, which means that this information are printed for all
messages except those with severity 0.</P></DD>
<DT><B><A NAME="PrintText">PrintText</A></B></DT>
<DD>The minimum severity level for which <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> prints the text
of the error message. That is, the third line in the first example and the
second line in the second and third examples. The default is 0, which
means that it is always printed.</DD>
<DT> </DT>
<DT><B><A NAME="PrintLines">PrintLines</A></B></DT>
<DD>The minimum severity level for which <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> prints the text
of the command batch that caused the error. The numbers to the left are
simply line numbers.
The idea is that if your program stops unexpectedly, you want some clue about
the <SMALL CLASS="allcaps">SQL</SMALL> code that caused the problem.
<P>
Exactly what command text you see here, depends a little on which
Win32::SqlServer function you called.
When you call <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, Win32::SqlServer submits an <SMALL CLASS="allcaps">RPC</SMALL> call to <SMALL CLASS="allcaps">SQL</SMALL> Server. The same is
true if you submit a parameterised command with <A HREF="#sql"><NOBR>sql()</NOBR></A>. In both these cases,
Win32::SqlServer produces a textual representation of the corresponding <SMALL CLASS="allcaps">EXEC</SMALL>
command. When you use parameterised commands with the mid-level
functions, you will always see the command string you passed to <A HREF="#initbatch"><NOBR>initbatch()</NOBR></A>
as is. For some errors detected by Win32::SqlServer, this part is not printed, because
Win32::SqlServer detects the error before it has constructed the command text, so there
is nothing to print.</P>
<P>
The default is 11. That is, lines are only printed for errors.</P>
<P>
See also the element <B><A HREF="#LinesWindow">LinesWindow</A></B> below, with which you can control how much of the
batch that is printed.</P>
</DD>
<DT><B><A NAME="CarpLevel">CarpLevel</A></B></DT>
<DD>This property controls from which severity level <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>
supplements the message with a Perl warning (<CODE>carp</CODE>) to indicate
on which line in your script you submitted the batch that caused the message.
That is, the last line in the examples.<P>If there is a burst of messages, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> prints this part
only once, after the last message. This part never appears on its own.
Thus, if
the settings of <B><A HREF="#PrintMsg">PrintMsg</A></B>, <B><A HREF="#PrintText">PrintText</A></B> and <B><A HREF="#PrintLines">PrintLines</A></B> preclude printing the
message (or all messages in a burst), this part is not printed.</P>
<P>The default
is 10. That is, this part is printed for warnings and errors.</P></DD>
<DT><B><A NAME="NeverPrint">NeverPrint</A></B></DT>
<DD>An hash keyed by <SMALL CLASS="allcaps">SQL</SMALL> Server error numbers or SQLstates from the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. For entries with true values, printing of all parts of the message
is suppressed. See above under <B><A HREF="#NeverStopOn">NeverStopOn</A></B>
for an example on how to use it.
<P>
Two <SMALL CLASS="allcaps">SQL</SMALL> Server messages are added by default to <B><A HREF="#NeverPrint">NeverPrint</A></B>.
Messages 5701 <I>Changed database context to </I>and 5703 is <I>Changed
language setting to</I> which are always generated on login. Most <SMALL CLASS="allcaps">SQL</SMALL> tools
suppress these messages.</P></DD>
<DT><B><A NAME="AlwaysPrint">AlwaysPrint</A></B></DT>
<DD>An hash keyed by <SMALL CLASS="allcaps">SQL</SMALL> Server error numbers or SQLstates from the <SMALL CLASS="allcaps">OLE DB</SMALL>
provider. For entries with true values, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> prints of all parts of the message.
<P>Three messages are added to this hash by default. These are the
informational messages
for arithmetic errors you get if all of <SMALL CLASS="allcaps">ANSI_WARNINGS,
ARITHABORT</SMALL> and <SMALL CLASS="allcaps">ARITHIGNORE</SMALL> are off.
When you connect with Win3<SMALL CLASS="allcaps">2::SqlServer, ANSI_WARNINGS</SMALL> is on by default. These
settings are brought over from <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, which uses DB-Library and
that has all these settings off by default.</P></DD>
<DT><B><A NAME="LinesWindow">LinesWindow</A></B></DT>
<DD>If this property is defined, <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> does not print the full
text of the command batch that cause the error, but only the line on which
the error occurred (according to <SMALL CLASS="allcaps">SQL</SMALL> Server, which is not always accurate
with this information!), and <B><A HREF="#LinesWindow">LinesWindow</A></B> lines before and after.
For instance, if you set <B><A HREF="#LinesWindow">LinesWindow</A></B> to 5, you will get at most 11
lines of code printed in the error message.
<P>
By default, <B><A HREF="#LinesWindow">LinesWindow</A></B> is <CODE>undef</CODE>, and thus all lines
are printed.</P>
</DD>
<DT><B><A NAME="ErrFileHandle">ErrFileHandle</A></B></DT>
<DD>A file handle to which <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> should write the
messages. If <CODE>undef</CODE> – which is the default –
<A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> prints the messages to <SMALL CLASS="allcaps">STDERR</SMALL>.</DD>
</DL>
<H3><A NAME="abortcontrol_sql_sp">Abort Control for <NOBR>sql_sp()</NOBR></A></H3>
<P>The default behaviour for <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> is to abort execution on any non-zero
return status from a stored procedure. (Return values from scalar
user-defined functions are not checked.) <B><A HREF="#ErrInfo">ErrInfo</A></B> has two elements to permit you
control this:</P>
<DL>
<DT><B><A NAME="CheckRetStat">CheckRetStat</A></B></DT>
<DD>This element controls whether <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> checks return values at all. By
default, <B><A HREF="#CheckRetStat">CheckRetStat</A></B> is true. Set it to a false value, if you
never want
return values checked.</DD>
<DT> </DT>
<DT><B><A NAME="RetStatOK">RetStatOK</A></B></DT>
<DD>This is a hash, keyed by return values. If an entry has a true value, this
means that <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> should not abort execution for this return value.</DD>
</DL>
<H3><A NAME="Returning_errors_to_caller">Getting Messages Back</A></H3>
<DL>
<DT><B><A NAME="SaveMessages">SaveMessages</A></B></DT>
<DD>When set, all messages that passes through <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> are added
to the <B><A HREF="#Messages">Messages</A></B> array. <B><A HREF="#SaveMessages">SaveMessages</A></B> is off by default, but you
should set <B><A HREF="#SaveMessages">SaveMessages</A></B> if you set <B><A HREF="#MaxSeverity">MaxSeverity</A></B> to a value > 10.</DD>
<DT> </DT>
<DT><B><A NAME="Messages">Messages</A></B></DT>
<DD>An array with information about messages generated by <SMALL CLASS="allcaps">SQL</SMALL> Server, the
<SMALL CLASS="allcaps">OLE DB</SMALL> provider or Win32::SqlServer itself. To check whether there are any errors in <B>
<A HREF="#Messages">Messages</A></B>, you can use the Win32::SqlServer function
<A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A>. Each entry in
<B><A HREF="#Messages">Messages</A></B> can contain any of the elements below. Just like <B><A HREF="#ErrInfo">ErrInfo</A></B>, the entries in <B><A HREF="#Messages">Messages</A></B> are tied
hashes, so if you spell a key wrong, you will get an error. In order to
provide compatibility with <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, initial lowercase is permitted.
<DL>
<DT><B>Source</B></DT>
<DD>The source for the error message. <CODE>undef</CODE> for <SMALL CLASS="allcaps">SQL</SMALL> Server
messages. The name of the <SMALL CLASS="allcaps">OLE DB</SMALL> provider for <SMALL CLASS="allcaps">OLE DB</SMALL> messages. <I>
Win32::SqlServer</I> for Win32::SqlServer messages.</DD>
<DT> </DT>
<DT><B>Errno</B></DT>
<DD>The message number. A number > 0 for <SMALL CLASS="allcaps">SQL</SMALL> Server
messages. -1 for <SMALL CLASS="allcaps">OLE DB</SMALL> and
Win32::SqlServer messages.</DD>
<DT> </DT>
<DT><B>Severity</B></DT>
<DD>The severity level of the message.</DD>
<DT> </DT>
<DT><B>State</B></DT>
<DD>The state of the message. <SMALL CLASS="allcaps">SQL</SMALL> Server uses state to convey some
information that is not well documented. Always 1 for <SMALL CLASS="allcaps">OLE DB</SMALL> and
Win32::SqlServer
messages.</DD>
<DT> </DT>
<DT><B>Server</B></DT>
<DD>The server from which the message originated. Always <CODE>undef</CODE> for <SMALL CLASS="allcaps">OLE DB</SMALL> and
Win32::SqlServer messages. Can also be <CODE>undef</CODE> for <SMALL CLASS="allcaps">SQL</SMALL> Server messages.</DD>
<DT> </DT>
<DT><B>Proc</B></DT>
<DD>The name of the <SMALL CLASS="allcaps">SQL</SMALL> module (stored procedure, trigger etc) where the
error occurred. Also filled in for some <SMALL CLASS="allcaps">OLE DB</SMALL> message with internal
Win32::SqlServer
debug information.</DD>
<DT> </DT>
<DT><B>Line</B></DT>
<DD>The line in the command batch or in the <SMALL CLASS="allcaps">SQL</SMALL> module,
that the message
relates to. It appears that for non-error messages, <SMALL CLASS="allcaps">SQL</SMALL> Server provides bogus
line numbers. (And it is not that terribly exact for error messages either
for that matter.)</DD>
<DT> </DT>
<DT><B>Text</B></DT>
<DD>The text of the message.</DD>
<DT> </DT>
<DT><B>SQLstate</B></DT>
<DD>The <A HREF="#errornumbers">SQLstate</A> for the message for <SMALL CLASS="allcaps">SQL</SMALL> Server
errors and <SMALL CLASS="allcaps">OLE DB</SMALL> messages. Always <CODE>undef</CODE> for
Win32::SqlServer messages.</DD>
</DL>
<P>
Here is an example on how to refer to an entry in <B><A HREF="#Messages">Messages</A></B>:</P>
<PRE>$X->{ErrInfo}{Messages}[0]{Errno}</PRE>
</DD>
</DL>
<H2><A NAME="MsgHandler">MsgHandler</A></H2>
<H3>Setting a Message Handler</H3>
<P>
The Win32::SqlServer property <B><A HREF="#MsgHandler">MsgHandler</A></B> defines the current message handler. By
default it is set to <CODE><NOBR>\&Win32::SqlServer::sql_message_handler</NOBR></CODE>.
To install your own message handler, you can do any of:</P>
<PRE>$sqlsrv->{MsgHandler} = \&my_handler;
$sqlsrv->{MsgHandler} = "main::my_handler";
$sqlsrv->{MsgHandler} = "my_handler"; # Yields a warning.</PRE>
<P>Or even</P>
<PRE>$sqlsrv->{MsgHandler} = sub { warn "This is my handler!\n"};</PRE>
<P>When you pass the name of a <CODE>sub</CODE>, Win32::SqlServer resolves this name, and
if the <CODE>sub</CODE> does not exist, Win32::SqlServer <CODE>croaks</CODE>. If the
string does not include the name of a package, Win32::SqlServer emits a warning if Perl
warnings are enabled, as the name may resolve to another package than you
intended.</P>
<P>Consider this case:</P>
<PRE>$sqlsrv->{MsgHandler} = undef;</PRE>
<P>The effect of this is not that you are left without a message handler. When
there is no Perl callback defined for <B><A HREF="#MsgHandler">MsgHandler</A></B>,
Win32::SqlServer invokes a second default message handler, implemented in C++. This is a simple, non-configurable
handler that prints the message header if the severity level is >= 11, and
always prints the message text. It never print the <SMALL CLASS="allcaps">SQL</SMALL> code and it never aborts execution.
</P>
<H3>Return Value and Parameters of a Message Handler</H3>
<P>Win32::SqlServer calls the message handler like this:</P>
<PRE>$status = MsgHandler($sqlsrv, $errno, $state, $severity, $text, $server,
$procedure, $line, $sqlstate, $source, $n, $no_of_errs);</PRE>
<DL>
<DT><CODE>$status</CODE></DT>
<DD>The return value of the message handler. If this is a false value,
Win32::SqlServer
<CODE>croaks</CODE> and aborts execution. You should not call <CODE>die</CODE>, <CODE>croak</CODE>
or <CODE>exit</CODE> in your message handler, but leave this to
Win32::SqlServer. This
is because Win32::SqlServer needs to free resources associated with the current batch.
(Even if you <CODE>die</CODE>/<CODE>croak</CODE>, someone might catch this
with <CODE>
eval</CODE>.)</DD>
<DT> </DT>
<DT><CODE>$sqlsrv
</CODE></DT>
<DD>The Win32::SqlServer object bound to the connection on which the message was
generated.</DD>
<DT> </DT>
<DT><CODE>$errno</CODE></DT>
<DD>The <A HREF="#errornumbers">error number</A> for the message. For <SMALL CLASS="allcaps">SQL</SMALL>
Server messages, this is a number > 0. For provider and Win32::SqlServer errors, this is
always -1. (Except for provider errors that dress up as <SMALL CLASS="allcaps">SQL</SMALL> Server errors.)</DD>
<DT> </DT>
<DT><CODE>$state</CODE></DT>
<DD>The state for the message as reported by <SMALL CLASS="allcaps">SQL</SMALL> Server.</DD>
<DT> </DT>
<DT><CODE>$severity</CODE></DT>
<DD>The severity level of the message. See above under <B><A HREF="#MaxSeverity">MaxSeverity</A></B> for
a discussion on severity levels.</DD>
<DT> </DT>
<DT><CODE>$text</CODE></DT>
<DD>The message text.</DD>
<DT> </DT>
<DT><CODE>$server</CODE></DT>
<DD>The server from which the message originates. <CODE>undef </CODE>for
Win32::SqlServer messages and messages from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider.</DD>
<DT> </DT>
<DT><CODE>$procedure</CODE></DT>
<DD>Name of the <SMALL CLASS="allcaps">SQL</SMALL> module (stored procedure, <SMALL CLASS="allcaps">UDF</SMALL>, trigger etc) where the error
occurred. For messages from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider, it can be the name <SMALL CLASS="allcaps">API</SMALL> call
that failed (this string is mainly for debugging Win32::SqlServer itself). Always <CODE>undef</CODE> for
Win32::SqlServer messages.</DD>
<DT> </DT>
<DT><CODE>$line</CODE> </DT>
<DD>Line number for the line where the error occured. 0 for Win32::SqlServer messages
and messages from the <SMALL CLASS="allcaps">OLE DB</SMALL> provider.</DD>
<DT> </DT>
<DT><CODE>$sqlstate</CODE></DT>
<DD>The <A HREF="#errornumbers">SQLstate</A>. See the section above for a
discussion on SQLstates. Always <CODE>undef</CODE> for Win32::SqlServer messages.</DD>
<DT> </DT>
<DT><CODE>$source</CODE></DT>
<DD>The source for the error message. For <SMALL CLASS="allcaps">SQL</SMALL> Server messages <CODE>$source</CODE>
is <CODE>undef</CODE>. For <SMALL CLASS="allcaps">OLE DB</SMALL> messages, it is the name of the provider.
For Win32::SqlServer messages, <CODE>$source</CODE> is <I>Win32::SqlServer</I>. </DD>
<DT> </DT>
<DT><CODE>$n</CODE></DT>
<DT><CODE>$no_of_errs</CODE></DT>
<DD><SMALL CLASS="allcaps">SQL</SMALL> Server may generate more than one message at a time. In this case,
you may want to display all messages before you abort execution. <CODE>
$no_of_errs</CODE> is the number of a message in the burst. When you get a
burst, the message handler is first called with <CODE>$n</CODE> = 1, then 2
etc up to <CODE>$no_of_errs</CODE>.</DD>
</DL>
<H3>Some Tips on Writing Message Handlers</H3>
<P>If you simply want to change the format of the printed messages, you could
simply copy the code of <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, and use <B><A HREF="#ErrInfo">ErrInfo</A></B>
as before. Beware, though, that <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A> makes use of some
undocumented functions and properties that could disappear in a future
version.</P>
<P>If you would prefer to replace <B><A HREF="#ErrInfo">ErrInfo</A></B> with your own control
structure, you can use the <B><A HREF="#UserData">UserData</A></B> property of the
Win32::SqlServer object.</P>
<P>If you need to get the text of the current command, you can use the function
<A HREF="#sql_get_command_text"><NOBR>sql_get_command_text()</NOBR></A>. Beware that in some situations this function can
return <CODE>undef</CODE>, because Win32::SqlServer detects an error before it has
started to build the command string.</P>
<P>If you are rolling your own entirely, you should consider to have a handling
similar to what is described under <B><A HREF="#DisconnectOn">DisconnectOn</A></B>. That is, call
<A HREF="#disconnect"><NOBR>disconnect()</NOBR></A>
when the <SMALL CLASS="allcaps">SQL</SMALL> Server message or provider message indicates that the connection
with <SMALL CLASS="allcaps">SQL</SMALL> Server has been terminated.
Win32::SqlServer does not detect this situation on
its own, but relies on the message handler to do this work.</P>
<H2><A NAME="errorhandlingexamples">Examples on Error Handling</A></H2>
<H3>Example 1</H3>
<P>This script reads all files named .SP in a directory and passes these to <SMALL CLASS="allcaps">SQL</SMALL>
Server, assuming they contain stored procedures. The script assumes that the
procedure name agrees with the file name, and generates a <SMALL CLASS="allcaps">DROP
PROCEDURE</SMALL> for the procedure prior to running the file. The script
relies on the built-in printing supplied by <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>. At the end, it
prints out a summary of many files it attempted to load and how many that
failed. To this end, it uses <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A>. </P>
<PRE>use strict;
use Win32::SqlServer;
my ($no_of_errors) = 0;
my $dir = shift @ARGV;
my $db = shift @ARGV;
# Don't buffer STDOUT, so that we get all output in order.
$| = 1;
# Log in to local server with Windows authentication.
my $sqlsrv = sql_init(undef, undef, undef, $db);
# Since we are loading stored procedures, we don't want to stop on
# compilation errors.
$sqlsrv->{ErrInfo}{MaxSeverity} = 17;
# And when there is an error, we only want the line that is flagged
# for the error.
$sqlsrv->{ErrInfo}{LinesWindow} = 0;
# We need to save messages to see if the file loaded successfully.
$sqlsrv->{ErrInfo}{SaveMessages} = 1;
# Get the files to load.
chdir($dir) or die "chdir to '$dir' failed: $!\n";
opendir (D, '.') or die "Opendir failed: $!\n";
my @files = grep(/\.sp$/i, readdir(D));
closedir(D);
foreach my $f (@files) {
# Read the file.
open (F, $f);
my @filetext = <F>;
close F;
my $filetext = join('', @filetext);
# Log that we try to load the file.
print "$f\n";
# Remove any old copy of the procedure.
my ($procname) = $f;
$procname =~ s/\.sp$//i;
$sqlsrv->sql("IF object_id('$procname') IS NOT NULL " .
"DROP PROCEDURE $procname", Win32::SqlServer::NORESULT);
# Load the file. We must handle batching with go ourselves.
my @batches = split(/\n\s*go\s*(\n|$)/i, $filetext);
foreach my $batch (@batches) {
next if $batch !~ /\S/; # Skip blank batches.
$sqlsrv->sql($batch, Win32::SqlServer::NORESULT);
}
# Check whether it loaded successfully.
if ($sqlsrv->sql_has_errors()) {
$no_of_errors++;
# We must clear Messages ourselves.
delete $sqlsrv->{ErrInfo}{Messages};
}
}
my $no_of_files = scalar(@files);
print "Attempted to load $no_of_files files whereof " .
($no_of_errors ? "$no_of_errors failed" : "all loaded successfully") .
".\n";</PRE>
<P>Here is a sample output from some files that I had around (and that were not
in sync with the database I loaded them to):</P>
<PRE>ais_acq_upd_sp.sp
ais_acs_upd_instrument_sp.sp
SQL Server message 207, Severity 16, State 3, Server KESÄMETSÄ
Procedure ais_acs_upd_instrument_sp, Line 166
Invalid column name 'sypvarchar'.
166> SELECT @homecoun = sypvarchar FROM systemparameters WHERE sypcode = 'HOMECOUN'
Message from SQL Server at F:\Test\example.pl line 49
ais_acs_upd_money_sp.sp
SQL Server message 207, Severity 16, State 3, Server KESÄMETSÄ
Procedure ais_acs_upd_money_sp, Line 238
Invalid column name 'ismainaccountowner'.
238> IF NOT(SELECT natregno
Message from SQL Server at F:\Test\example.pl line 49
ais_activate_account_sp.sp
...
ais_sct_exec_one_sp.sp
ais_syp_get_nightjob_ready_ok_sp.sp
Attempted to load 89 files whereof 10 failed.</PRE>
<H3>Example 2</H3>
<P>This example reads a server name, a database name and a file name from the
command line, and loads the specified file into the specified server/database.
The script splits the file into batches and run them one by one. The script
turns off printing of error messages in <A HREF="#ErrInfo"><NOBR>sql_message_handler()</NOBR></A>, and instead prints
the error messages itself. The point is that the line number is modified to
refer to a line in the file, and not in the batch. Thus, if the file first
contains a batch for dropping the procedure if it exists, and there then is an
error on Line 24 in the procedure, the example script will report this error on
Line 28 in the file (if the <SMALL CLASS="allcaps">DROP</SMALL> batch is four lines). This is nice if you edit
<SMALL CLASS="allcaps">SQL</SMALL> files in an editor like Textpad from which you can run command-line tools.
You could format the error message, so that you could double-click the error
message and be taken to the line where there error is.</P>
<PRE>use strict;
use Win32::SqlServer;
my $server = shift @ARGV;
my $db = shift @ARGV;
my $file = shift @ARGV;
# Don't buffer STDOUT, so that we get all output in order.
$| = 1;
# Log in to the server with Windows authentication.
my $sqlsrv = sql_init($server, undef, undef, $db);
# Since we are loading stored procedures, we don't want to stop on
# compilation errors.
$sqlsrv->{ErrInfo}{MaxSeverity} = 17;
# We will print error messages ourselves, so turn off printing:
$sqlsrv->{ErrInfo}{PrintMsg} = 17;
$sqlsrv->{ErrInfo}{PrintText} = 17;
$sqlsrv->{ErrInfo}{PrintLines} = 17;
# We need to save messages so we can print them.
$sqlsrv->{ErrInfo}{SaveMessages} = 1;
# Read the file.
open (F, $file) or die "Cannot read file '$file': $!\n";
my @filetext = <F>;
close F;
print "$file\n";
# Load the file, batch by batch.
my $batchtext = '';
my $batchstart = 0;
my $no_of_errs = 0;
foreach my $ix (0..$#filetext) {
unless ($filetext[$ix] =~ /^\s*go\s*$/i) {
# Just append.
$batchtext .= $filetext[$ix];
next;
}
else {
# Do the batch.
$no_of_errs += run_batch($sqlsrv, $file, $batchstart, $batchtext);
# Set up for next;
$batchstart = $ix + 1;
$batchtext = '';
}
}
# There is likely to be a batch at the end as well...
$no_of_errs += run_batch($sqlsrv, $file, $batchstart, $batchtext);
# Print summary message.
if ($no_of_errs == 0) {
print "$file loaded successfully!\n";
}
else {
print "Loading of $file resulted in $no_of_errs error(s).\n";
}
exit;
sub run_batch {
my($sqlsrv, $file, $batchstart, $batchtext) = @_;
my $no_of_errs = 0;
# Skip empty batch.
return 0 if (not $batchtext or $batchtext !~ /\S/);
# Make sure Messages is empty.
delete $sqlsrv->{ErrInfo}{Messages};
# Run batch.
$sqlsrv->sql($batchtext, Win32::SqlServer::NORESULT);
# Check for messages.
foreach my $mess (@{$sqlsrv->{ErrInfo}{Messages}}) {
# Increase return value.
$no_of_errs += $mess->{Severity} >= 11;
# Translate line number in batch to line number in file.
my $lineno = $mess->{Line} + $batchstart;
# Print message header.
print "Msg $mess->{Errno}, Level $mess->{Severity}, " .
"Line $lineno, $file\n";
# And the message text.
print $mess->{Text}, "\n";
}
return $no_of_errs;
}</PRE>
<P>Sample output:</P>
<PRE>F:\Test\OLE DB\blafstest\variant_sp.sp
Msg 170, Level 15, Line 18, F:\Test\variant_sp.sp
Line 15: Incorrect syntax near '4567891234456789'.
Loading of F:\Test\variant_sp.sp resulted in 1 error(s).</PRE>
<P>Notice how the line number in the message text from <SMALL CLASS="allcaps">SQL</SMALL> Server is different from
the line number in the line above.
</P>
<H1><A NAME="Misctopics">Miscellaneous Topics</A></H1>
<H2><A NAME="EXPORTTAGS">Exported Names and Export Tags</A></H2>
<P>To minimize namespace pollution, when you say:</P>
<PRE>use Win32::SqlServer;</PRE>
<P>only two items are imported by default:
<A HREF="#sql_init"><NOBR>sql_init()</NOBR></A> and <A HREF="#sql_string"><NOBR>sql_string()</NOBR></A>.</P>
<P>A whole lot more can be imported, either explicitly by name, or by an
export tag. These tags are available:</P>
<DL>
<DT><CODE>:DEFAULT</CODE></DT>
<DD>The default import, that is <A HREF="#sql_init"><NOBR>sql_init()</NOBR></A> and <A HREF="#sql_string"><NOBR>sql_string()</NOBR></A></DD>
<DT> </DT>
<DT><CODE>:cmdstates</CODE></DT>
<DD>The possible return values from <A HREF="#getcmdstate"><NOBR>getcmdstate()</NOBR></A>
<SMALL CLASS="allcaps">CMDSTATE_INIT, CMDSTATE_ENTEREXEC, CMDSTATE_NEXTRES, CMDSTATE_NEXTROW</SMALL>
and <SMALL CLASS="allcaps">CMDSTATE_GETPARAMS</SMALL>.</DD>
<DT> </DT>
<DT><CODE>:consts</CODE></DT>
<DD><CODE><A HREF="#SCALAR">$SQLSEP</A></CODE></DD>
<DT> </DT>
<DT><CODE>:datetime</CODE></DT>
<DD>The possible values for the property <B>
<A HREF="#datetime">DatetimeOption</A></B>: <SMALL CLASS="allcaps">DATETIME_HASH, DATETIME_ISO, DATETIME_REGIONAL, DATETIME_FLOAT</SMALL>
and <SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL>.</DD>
<DT> </DT>
<DT><CODE>:directions</CODE></DT>
<DD>The possible values for the <CODE>$direction</CODE> parameter to <A HREF="#sql_set_conversion"><NOBR>sql_set_conversion()</NOBR></A>
and <A HREF="#sql_unset_conversion"><NOBR>sql_unset_conversion()</NOBR></A>: <SMALL CLASS="allcaps">TO_SERVER_ONLY, TO_CLIENT_ONLY</SMALL> and
<SMALL CLASS="allcaps">TO_SERVER_CLIENT</SMALL>.</DD>
<DT> </DT>
<DT><CODE>:providers</CODE></DT>
<DD>The possible values for the <B><A HREF="#Provider">Provider</A></B>
property: <SMALL CLASS="allcaps">PROVIDER_DEFAULT, PROVIDER_SQLNCLI</SMALL> and <SMALL CLASS="allcaps">PROVIDER_SQLOLEDB</SMALL>.</DD>
<DT> </DT>
<DT><CODE>:resultstyles</CODE></DT>
<DD>The possible values for the <CODE><A HREF="#RowResultStyles">$resultstyle</A></CODE> parameter:
<SMALL CLASS="allcaps">NORESULT, SINGLEROW, SINGLESET, MULTISET</SMALL> and <SMALL CLASS="allcaps">KEYED</SMALL></DD>
<DT> </DT>
<DT><CODE>:returns</CODE></DT>
<DD>The possible return values from a <A HREF="#Callbacks">callback</A> that
is used as a <A HREF="#RowResultStyles">result style</A>: <SMALL CLASS="allcaps">RETURN_NEXTROW, RETURN_NEXTQUERY, RETURN_CANCEL, RETURN_ERROR</SMALL>
and <SMALL CLASS="allcaps">RETURN_ABORT</SMALL>.</DD>
<DT> </DT>
<DT><CODE>:routines</CODE></DT>
<DD>All routines that start with sql_. Import these if you want to rely on the
<A HREF="#sql_init">default handle</A>. Note
that using the default handle is deprecated. This is the complete import list
for this tag: <A HREF="#sql"><NOBR>sql()</NOBR></A>, <A HREF="#sql_one"><NOBR>sql_one()</NOBR></A>, <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A>, <A HREF="#sql_insert"><NOBR>sql_insert()</NOBR></A>, <A HREF="#sql_has_errors"><NOBR>sql_has_errors()</NOBR></A>,
<NOBR><A HREF="#sql_get_command_text"><NOBR>sql_get_command_text()</NOBR></A>, <A HREF="#sql_set_conversion">sql_set_conversion()</A></NOBR>,
<A HREF="#sql_unset_conversion"><NOBR>sql_unset_conversion()</NOBR></A>, <A HREF="#sql_begin_trans"><NOBR>sql_begin_trans()</NOBR></A>, <A HREF="#sql_commit"><NOBR>sql_commit()</NOBR></A> and <A HREF="#sql_rollback"><NOBR>sql_rollback()</NOBR></A>.</DD>
<DT> </DT>
<DT><CODE>:rowstyles </CODE></DT>
<DD>The possible values for the <CODE><A HREF="#RowResultStyles">$rowstyle</A></CODE> parameter: <SMALL CLASS="allcaps">SCALAR, LIST</SMALL>
and <SMALL CLASS="allcaps">HASH</SMALL>.</DD>
</DL>
<P>Here is an example,
on how to import <A HREF="#sql_init"><NOBR>sql_init()</NOBR></A>, the result styles, the row styles and the property
value <A HREF="#DATETIME_REGIONAL"><SMALL CLASS="allcaps">DATETIME_REGIONAL</SMALL></A>:
</P>
<PRE>use Win32::SqlServer qw(:rowstyles :resultstyles sql_init DATETIME_REGIONAL);</PRE>
<P>All export tags must come before any explicit names. For more information about importing names and using export tags, please
refer to the Perl documentation.</P>
<H2><A NAME="SQLLIB">Notes on Migration from MSSQL::Sqllib</A></H2>
<P>If you have existing scripts that uses <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, it should be fairly
easy to port these to use Win32::SqlServer. This chapter highlights some
incompatibilities.</P>
<H3><A HREF="#sql"><NOBR>sql()</NOBR></A>, <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> etc
are Not Exported by Default</H3>
<P><SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib includes all functions in the export list. The idea was that
when you called <A HREF="#sql_init"><NOBR>sql_init()</NOBR></A> the first time, you
got a default handle, so you
could then say <CODE><NOBR>sql($batch)</NOBR></CODE> without using an object handle like
<CODE><NOBR>$X-></NOBR></CODE> in front. You can still do this in Win32::SqlServer, however,
this usage is now deprecated. In order to refer to <A HREF="#sql"><NOBR>sql()</NOBR></A>, <A HREF="#sql_sp"><NOBR>sql_sp()</NOBR></A> and other
functions without an handle, you need to import the functions explicitly, or
use an <A HREF="#EXPORTTAGS">export tag</A>. </P>
<H3>DB-Library Functions Not Available</H3>
<P>If you are using functions from <SMALL CLASS="allcaps">MSSQL</SMALL>::DBlib, these are not available with
Win32::SqlServer. Here is a list of the functions that you are most likely to have
used, and suggested replacements, if any.</P>
<P>
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
<TR VALIGN="top"><TD><I>dbclose</I></TD><TD>Replace with
<A HREF="#disconnect"><NOBR>disconnect()</NOBR></A>.</TD></TR>
<TR VALIGN="top"><TD><I>dbexit</I></TD><TD>Just remove any calls to
<I>dbexit</I>.</TD></TR>
<TR VALIGN="top"><TD><SMALL CLASS="allcaps">BCP</SMALL> routines</TD><TD>
Win32::SqlServer has no <SMALL CLASS="allcaps">BCP</SMALL>
capability. If you are using the <SMALL CLASS="allcaps">BCP</SMALL> routines, you are probably better
off staying with <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib for now.</TD></TR>
<TR VALIGN="top"><TD>text/image functions.</TD><TD>Win32::SqlServer
currently has no particular support for large data types. Functionally, you
can handle fields of any size, but performance starts to become poor when the
size exceeds 1MB.</TD></TR>
<TR VALIGN="top"><TD><I>dbsetopt</I></TD><TD>Just send the corresponding
<SMALL CLASS="allcaps">SET</SMALL> command with <A HREF="#sql"><NOBR>sql()</NOBR></A>.</TD></TR>
<TR VALIGN="top"><TD>DBSETLxxx</TD><TD>Most of these have
corresponding login properties you can set with
<A HREF="#setloginproperty"><NOBR>setloginproperty()</NOBR></A>.</TD></TR>
<TR VALIGN="top"><TD><I>dbmsghandle</I></TD><TD>You can establish your
own message handler with the <B><A HREF="#MsgHandler">MsgHandler</A></B>
attribute.</TD></TR>
</TABLE>
</P>
<H3>Windows Authetnication is Now Default</H3>
<P>In <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib, if you did not specify a username for the second parameter
to <A HREF="#sql_init"><NOBR>sql_init()</NOBR></A>, the default was to connect as <B><I>sa</I></B>.
To use Windows authentication (a.k.a. integrated security or trusted
connection), you had to say <NOBR><CODE>
MSSQL::DBlib::DBSETLSECURE()</CODE></NOBR>. In Win32::SqlServer, the default is
log in with Windows authentication. To login as <B>sa</B>, you must
explicitly specify this.</P>
<H3>Different Default Settings for SET Options</H3>
<P>With DB-Library, all options controlled by <SMALL CLASS="allcaps">SET</SMALL> are off by default. With <SMALL CLASS="allcaps">OLE
DB</SMALL>, and thus Win32::SqlServer these settings are on by default:
<SMALL CLASS="allcaps">ANSI_NULLS, ANSI_NULL_DFLT_ON, ANSI_PADDING,
ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL</SMALL>
and <SMALL CLASS="allcaps">QUOTED_IDENTIFIER</SMALL>. For
further details on these <SMALL CLASS="allcaps">SET</SMALL> options, see Books Online.</P>
<H3>Property Names are Now Initial Uppercase</H3>
<P>In <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib and <SMALL CLASS="allcaps">MSSQL</SMALL>::DBlib many properties have initial lowercase, but
mixed case inside, for instance <I>errInfo</I>, <I>dateFormat</I>.
Win32::SqlServer uses leading uppercase: <I><B><A HREF="#ErrInfo">ErrInfo</A></B></I>, <I><B><A HREF="#datetime">DateFormat</A></B></I>
(save two exceptions: the all-lowercase <B><A HREF="#procs">procs</A></B> and
<B><A HREF="#tables">tables</A></B>). This also applies to the <B><A HREF="#ErrInfo">ErrInfo</A></B> elements and to the elements of the <B><A HREF="#Messages">Messages</A></B> array.</P>
<P>However, to retain compatibility, Win32::SqlServer performs a second lookup with
initial lowercase if first lookup fails. Thus, you can still use <I>errInfo</I>, <I>
dateFormat</I> etc with Win32::SqlServer, with some slight performance penalty.</P>
<H3>Different Default Date Format</H3>
<P>The default date format with <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib was <FONT COLOR="#DD0000"><CODE>
YYYYMMDD</CODE></FONT>, whereas Win32::SqlServer has <FONT COLOR="#DD0000"><NOBR>
<CODE>YYYY-MM-DD</CODE></NOBR></FONT>. To get the <FONT COLOR="#FF0000"><CODE>YYYYMMDD</CODE></FONT> format, set the
property <B><A HREF="#datetime">DatetimeOption</A></B> to <A HREF="#DATETIME_STRFMT"><SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL></A>.
The <SMALL CLASS="allcaps">MSSQL</SMALL>::DBlib properties <B><A HREF="#DateFormat">dateFormat</A></B> and
<B><A HREF="#MsecFormat">msecFormat</A></B> are present in Win32::SqlServer as
well, and applies then <B><A HREF="#datetime">DatetimeOption</A></B>
has the value
<A HREF="#DATETIME_STRFMT"><SMALL CLASS="allcaps">DATETIME_STRFMT</SMALL></A>. However, the format codes for day of the week or
day of the year does not work with Win32::SqlServer.</P>
<H3>Properties Not Present in Win32::SqlServer</H3>
<P>Most of the <SMALL CLASS="allcaps">MSSQL</SMALL>::DBlib attributes have not been brought over to
Win32::SqlServer:</P>
<P>
<TABLE BORDER=1 CELLPADDING=0 CELLSPACING=0>
<TR VALIGN="top"><TD><I>dbNullIsUndef</I></TD><TD><SMALL CLASS="allcaps">NULL</SMALL> is always
represented as <CODE>undef</CODE> in Win32::SqlServer.</TD></TR>
<TR VALIGN="top"><TD><I>dbKeepNumeric</I></TD><TD>Integer values (except
bigint) and real/float are always numbers with Win32::SqlServer. Bigint, decimal and
money are controlled by the property <B><A HREF="#DecimalAsStr">DecimalAsStr</A></B>.</TD></TR>
<TR VALIGN="top"><TD><I>dbBin0x</I></TD><TD>Replaced by the property
<B><A HREF="#binary">BinaryAsStr</A></B>.</TD></TR>
<TR VALIGN="top"><TD><I>dateFormat,<BR />
msecFormat</I></TD><TD>Retained, see above about date format.</TD></TR>
<TR VALIGN="top"><TD><I>cloneFlag</I></TD><TD>This functionality is not
available in Win32::SqlServer.</TD></TR>
<TR VALIGN="top"><TD><I>DBstatus</I></TD><TD>This functionality is not
available in Win32::SqlServer.</TD></TR>
<TR VALIGN="top"><TD><I>ComputeID</I></TD><TD>Not applicable to Win32::SqlServer, see also
below about <SMALL CLASS="allcaps">COMPUTE BY</SMALL>.</TD></TR>
</TABLE>
</P>
<P>All attributes from <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib are available with
Win32::SqlServer. (But here known
as properties.)</P>
<H3>Win32::SqlServer and ErrInfo does Not Permit User-Defined Properties</H3>
<P><SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib had just a plain hash to keep track of its properties. As a
side effect, you would not get an error if you specified a non-existing
property. OIleDB uses a tied hash for its properties as well for <B><A HREF="#ErrInfo">ErrInfo</A></B> and
the <B><A HREF="#Messages">Messages</A></B> array, and <CODE>croaks</CODE> on all non-defined key names. If
you have used the Sqllib object to store your own data, you can use the
<B><A HREF="#UserData">UserData</A></B> property in Win32::SqlServer.</P>
<H3>COMPUTE BY</H3>
<P>With <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib you got the columns from a <SMALL CLASS="allcaps">COMPUTE BY</SMALL>
clause as special columns within the result set, and with the <A HREF="#HASH"><SMALL CLASS="allcaps">HASH</SMALL></A> row style
there was an extra column <SMALL CLASS="allcaps">COMPUTEID</SMALL>. With Win32::SqlServer, each <SMALL CLASS="allcaps">COMPUTE
BY</SMALL> row is returned in a result set of its own and splits the result
of the query into several result sets. There is no <SMALL CLASS="allcaps">COMPUTEID</SMALL> column. This
is because <SMALL CLASS="allcaps">OLE DB</SMALL> has no special support for
<SMALL CLASS="allcaps">COMPUTE BY</SMALL>. (The only <SMALL CLASS="allcaps">API</SMALL> to provide this is DB-Library.) Then again,
<SMALL CLASS="allcaps">COMPUTE BY</SMALL> is a highly
deprecated <SMALL CLASS="allcaps">SQL</SMALL> Server feature – and with a good reason.</P>
<H2><A NAME="QueryNotification">Using Query Notification with Win32::SqlServer</A></H2>
<P>Query notification is a new feature in <SMALL CLASS="allcaps">SQL</SMALL> 2005
that uses the new Service Broker infrastructure. It is a little funny in that
you cannot set up a subscription query notification from plain
<SMALL CLASS="allcaps">T-SQL</SMALL>, you must do it from client code. In
<SMALL CLASS="allcaps">ADO</SMALL> .Net this is packaged in the SqlDependency
class for a little more elegance, but the feature is available in
<SMALL CLASS="allcaps">SQL</SMALL> Native Client as well, and
Win32::SqlServer gives you access to query
notification through the <B><A HREF="#QueryNotification">QueryNotification</A></B>
hash. </P>
<P>For full information on Query Notification, please see Books Online: <I><SMALL CLASS="allcaps">SQL</SMALL> Server
Database Engine</I> → <I>Accessing and
Changing Database Data</I> → <I>Manipulating Result Sets</I> → <I>Using Query
Notifications</I>, for the full story in Query Notification. Here follows a crash
course in the context of Win32::SqlServer. The <B><A HREF="#QueryNotification">QueryNotification</A></B> hash has three
elements:</P>
<DL>
<DT><B>Service</B></DT>
<DD>A property-value string that specifies the service options for the
notification. The string has this format:
<PRE>service=<I>service-name</I>[;(local database=<I>db</I>|broker instance=<I>instance</I>)]</PRE>
This element is mandatory. If you do not set this element, Win32::SqlServer will
not set up a notification. If other elements in the hash are set, but <B>Service</B>
is not, Win32::SqlServer emits a warning through the current message handler, if Perl
warnings are enabled.</DD>
<DT> </DT>
<DT><B>Message</B></DT>
<DD>This is a text in free format that reappears in the message body of the
notification message, so that you can identify which notification fired, if you have
submitted more than one. If you don't set this element, Win32::SqlServer will set a
default message of <I>Query notification set by Win32::SqlServer</I>. The same applies
of you set <B>Message</B> to the empty string.</DD>
<DT> </DT>
<DT><B>Timeout</B></DT>
<DD>An integer value that specifies how many seconds you want the
notification subscription to be active. It appears that Service Broker only
clears out subscription about every minute or so, so if you set a small
value, you may find that the subscription lives longer than you expected. If
you do not specify any timeout, it appears that you get a timeout of 432000
seconds, or five days.</DD>
</DL>
<P>So how do you use this? You set the <B><A HREF="#QueryNotification">QueryNotification</A></B> property before you
submit a query batch, and the notification subscriptions then apply to the
queries in that batch. Once Win32::SqlServer has submitted the batch, it clears out the
<B><A HREF="#QueryNotification">QueryNotification</A></B> hash, so you will have to set it again to set up more
notification subscriptions. Note that you cannot set up query notification for
any query, but queries must adhere to certain rules, similar to those that
apply for indexed views. Please refer to Books Online for details.</P>
<P>Here is an example of how to use query notification with Win32::SqlServer. First run
this in a query window:</P>
<PRE>CREATE QUEUE MyQueue WITH RETENTION = OFF
CREATE SERVICE OlleService ON QUEUE MyQueue
([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification])
go
CREATE TABLE QNtest (a int NOT NULL PRIMARY KEY,
b nchar(5) NOT NULL,
c datetime NOT NULL)
go
INSERT QNtest (a, b, c)
SELECT 1, 'ALFKI', '19991212'</PRE>
<P>This snippet sets up a query notification on QNtest, and then polls to see if
there are any notifications:</P>
<PRE>$sqlsrv->{QueryNotification}{Service} =
'service=MyService;local database=somedatabase';
$sqlsrv->sql("SELECT a, b, c FROM dbo.QNtest WHERE b = N'ALFKI'");
my @notification;
while (not @notification) {
@notification = $sqlsrv->sql(<<'SQLEND');
DECLARE @xml TABLE (x xml NOT NULL);
RECEIVE convert(xml, message_body) FROM MyQueue INTO @xml;
WITH XMLNAMESPACES
('http://schemas.microsoft.com/SQL/Notifications/QueryNotification' AS qn)
SELECT Message = c.value(N'(qn:Message)[1]', 'nvarchar(MAX)'),
Source = c.value(N'@source', 'nvarchar(255)'),
Info = c.value(N'@info', 'nvarchar(255)'),
Type = c.value(N'@type', 'nvarchar(255)')
FROM @xml x
CROSS APPLY x.x.nodes(N'/qn:QueryNotification') AS T(c)
SQLEND
sleep(1) unles @notification;
} </PRE>
<P>There is a lot of new <SMALL CLASS="allcaps">SQL</SMALL> 2005 features in
this sample: The <SMALL CLASS="allcaps">RECEIVE</SMALL> command, part of the
Service Broker infrastructure, is how you receive your notifications. In this
sample I take the message body (or bodies) which for query notifications are
<SMALL CLASS="allcaps">XML</SMALL> document and save them into a table. Then
I use XQuery to extract the information from the <SMALL CLASS="allcaps">XML</SMALL>
into pieces that the Perl code then can work with. </P>
<P>There are several ways to achieve a notification: insert a row with b =
<SMALL CLASS="allcaps">ALFKI</SMALL>, delete the row there already is –
or just drop the table. Thus, when you have recieved a notification, you
need to inspect <CODE><NOBR>@</CODE><NOBR><CODE>notification</NOBR></CODE></NOBR> to see what event(s) occurred.
Note that if <SMALL CLASS="allcaps">SQL</SMALL> Server cannot set up a
subscription, for instance, because the query breaks the rules, you will not
get an error when you run the query. Instead you will get an immediate
notification with <CODE><NOBR>$$notification[0]{Source}</NOBR></CODE> set to
'statement'. Once you
have received a notification from your subscription, the subscription is no
longer active, but you would have to resubmit the query with the
<B><A HREF="#QueryNotification">QueryNotification</A></B> hash set to get
further notifications.</P>
<P>Again, please refer to Books Online for more details on query notification.
For instance, I have been entirely silent on permissions here.</P>
<H2><A NAME="Threads">Using Win32::SqlServer in Threaded Scripts</A></H2>
<P>Win32::SqlServer is designed to be thread-safe and should work with Perl threads
as in <CODE>use threads</CODE>, as well as running parallel Active-X scripts from
tools like <SMALL CLASS="allcaps">DTS</SMALL> or <SMALL CLASS="allcaps">ISAPI.</SMALL></P>
<P>When you create a new thread, Win32::SqlServer objects visible to the parent thread are
copied to child thread as well. These child objects inherit some data but not all
from the parent object:</P>
<UL>
<LI>The settings of all <A HREF="#OBJECT_PROPERTIES">Win32::SqlServer properties</A>
with the exception of <B><A HREF="#Provider">Provider</A></B>, are
inherited.</LI>
<LI>No <A HREF="#setloginproperty">login properties</A> are inherited.</LI>
<LI>No matter if the parent was connected or not, the child object is not
connected.</LI>
</UL>
<P>Thus, a new-born child process will have to fill in login information and
connect to the database (unless you have set <B><A HREF="#AutoConnect">AutoConnect</A></B>.)
The child and parent objects are distinct objects, and changes to one will not
affect the other.</P>
<P><CODE>share </CODE>from the package <CODE>threads::shared</CODE>, does not
work with Win32::SqlServer objects. </P>
<H2><A NAME="BUGS">Bugs and Known Restrictions</A></H2>
<H3>Issue 1</H3>
<P>When all this is true:</P>
<UL>
<LI>You run a stored procedure.</LI>
<LI><SMALL CLASS="allcaps">NOCOUNT</SMALL> is ON.</LI>
<LI>An error is raised. </LI>
<LI>A result set is returned after the error. </LI>
<LI>You are not using the combination of <SMALL CLASS="allcaps">SQL</SMALL> 2005 and <SMALL CLASS="allcaps">SQL</SMALL> Native Client.</LI>
</UL>
<P>then Win32::SqlServer fails to return the result set that comes directly after the
error. (Subsequent result sets are returned.) Consider this procedure:</P>
<PRE>CREATE PROCEDURE errors_with_result @nocount bit AS
IF @nocount = 1
SET NOCOUNT ON
ELSE
SET NOCOUNT OFF
RAISERROR('Hi there!', 16, 1)
SELECT getdate()
SELECT @@version</PRE>
<P>When you run this procedure and pass 1 for the <CODE><NOBR>@noucount</NOBR></CODE>
parameter, you will not get the result set for <CODE><NOBR>getdate()</NOBR></CODE>, but you
will get the result set for <CODE><NOBR>@@version</NOBR></CODE>.</P>
<P>This is due to some combination of bugs in <SMALL CLASS="allcaps">SQL</SMALL> Server and the <SMALL CLASS="allcaps">OLE
DB</SMALL> providers, which have been fixed with the release of <SMALL CLASS="allcaps">SQL</SMALL> 2005. But
if you connect to <SMALL CLASS="allcaps">SQL</SMALL> 2000 or earlier, or use the <SMALL CLASS="allcaps">SQLOLEDB</SMALL> provider, you are
exposed to this bug.</P>
<H3>Issue 2 </H3>
<P>Messages issued with <SMALL CLASS="allcaps">RAISERROR WITH NOWAIT</SMALL> are delayed, and appears one slot
too late. Consider this batch:</P>
<PRE>RAISERROR('One', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR('Two', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'
RAISERROR('Three', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:05'</PRE>
<P>It should print <I>One</I> immediately,
after five seconds print <I>Two</I>, after ten seconds print <I>Three</I> and then
five seconds later terminate. However, when Win32::SqlServer runs this batch, it does not
print <I>One</I> until five seconds have elapsed, <I>Two</I> appears after
ten seconds and <I>
Three</I> not until 15 seconds as the script terminates.</P>
<P>This is a behaviour you can reproduce with several other APIs built on top of
<SMALL CLASS="allcaps">OLE DB</SMALL>, for instance <SMALL CLASS="allcaps">ADO</SMALL> or the OleDb .Net Data
provider. Nevertheless, if you
run the batch above in <SMALL CLASS="allcaps">SQLCMD</SMALL>, the new command-line tool that comes with
<SMALL CLASS="allcaps">SQL</SMALL> 2005, you get the correct result. Thus,
Win32::SqlServer should be able do this correctly, but for now it doesn't.</P>
<H3>Issue 3</H3>
<P>Performance is poor when retrieving large objects. Retrieving a single 5MB <B><CODE>
varchar(MAX)</CODE></B> value could take 5-10 depending on your hardware. <SMALL CLASS="allcaps">A 50
MB</SMALL> value could take ten times as long.</P>
<P>The root cause is that Win32::SqlServer does not have any particular support for large
types, and does not use streaming to retrieve them. I hope to add this in a
latter release.</P>
<H3>Issue 4</H3>
<P>You cannot use the <CODE>share</CODE> operator from the <CODE>threads::shared</CODE>
package. I don't know if this is a problem in my code, or a restriction with
<CODE>threads::shared</CODE>. Thus, you cannot share Win32::SqlServer objects between
threads.</P>
<H1><A NAME="ACKNOWLEDGEMENTS">Acknowledgements</A></H1>
<P>Once upon a time Larry Wall invented Perl. Somewhat later Michael Peppler
wrote Sybperl for Unix. Christian Mallwitz ported Sybperl to Windows NT. Thanks
to their work I was able to convert Sybase::DBlib into <SMALL CLASS="allcaps">MSSQL</SMALL>::DBlib, and
without that base to stand on, I would not have been able to develop the XS
parts of Win32::SqlServer. The Sybperl distribution contained a simple <CODE>
<NOBR>sql()</NOBR></CODE> routine contributed by Gisle Aas. Simple as it may have been, it
was from this routine that <SMALL CLASS="allcaps">MSSQL</SMALL>::Sqllib was woven, and that interface made it
to Win32::SqlServer as well.</P>
<P>I also need to thank the people in the perl.xs and perl.unicode newsgroups for
answering my questions, and particularly Jarkko Hietaniemi and Nick Ing-Simmons. </P>
<H1><A NAME="AUTHOR">Author</A>, Copyright and Licence</H1>
<P>© 2005 Erland Sommarskog <<A HREF="mailto:esquel@sommarskog.se">esquel@sommarskog.se</A>>.</P>
<P>This module is available under any license you
want, as long as you don't claim that you wrote it yourself.</P>
<HR>
<I>
This file was last updated <!--$$Modtime:--> 05-11-26 23:35 <!-- $-->
</I>
</BODY>
</HTML>