Sophie

Sophie

distrib > Mageia > 5 > x86_64 > media > core-release > by-pkgid > 13eec89779171a321fe518ddb0e0fec6 > files > 545

freetds-doc-0.91-8.mga5.x86_64.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<HTML
><HEAD
><TITLE
>DB-Library for the Tenderfoot</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REL="HOME"
TITLE="FreeTDS User Guide"
HREF="index.htm"><LINK
REL="UP"
TITLE="Programming"
HREF="programming.htm"><LINK
REL="PREVIOUS"
TITLE="ODBC API Implementation Summary"
HREF="odbc.api.summary.htm"><LINK
REL="NEXT"
TITLE="Acknowledgments"
HREF="acknowledgments.htm"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="userguide.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=utf-8"></HEAD
><BODY
CLASS="SECT1"
BGCOLOR="#FFFFFF"
TEXT="#000000"
LINK="#0000FF"
VLINK="#840084"
ALINK="#0000FF"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
><SPAN
CLASS="PRODUCTNAME"
>FreeTDS</SPAN
> User Guide: A Guide to Installing, Configuring, and Running <SPAN
CLASS="PRODUCTNAME"
>FreeTDS</SPAN
></TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="odbc.api.summary.htm"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 11. Programming</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="acknowledgments.htm"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="SAMPLECODE"
>DB-Library for the Tenderfoot</A
></H1
><TABLE
BORDER="0"
WIDTH="100%"
CELLSPACING="0"
CELLPADDING="0"
CLASS="EPIGRAPH"
><TR
><TD
WIDTH="45%"
>&nbsp;</TD
><TD
WIDTH="45%"
ALIGN="LEFT"
VALIGN="TOP"
><I
><P
><I
>Few things are harder to put up with than the annoyance of a good example.</I
></P
></I
></TD
></TR
><TR
><TD
WIDTH="45%"
>&nbsp;</TD
><TD
WIDTH="45%"
ALIGN="RIGHT"
VALIGN="TOP"
><I
><SPAN
CLASS="ATTRIBUTION"
>Mark Twain</SPAN
></I
></TD
></TR
></TABLE
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN5688"
></A
><P
>Below is a complete sample working <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> program, presented as a series of examples.
				<P
></P
><P
><B
>Features of sample code</B
></P
><UL
><LI
><P
>Processes command-line options to select the server, database, username, and password</P
></LI
><LI
><P
>Remaining arguments on the command line comprise the SQL query to execute</P
></LI
><LI
><P
>Installs error and message handlers</P
></LI
><LI
><P
>Illustrates correct row-processing</P
></LI
><LI
><P
>Illustrates correct error detection and handling</P
></LI
></UL
>
				Other sample code may be found in the distribution, in the cleverly named <TT
CLASS="FILENAME"
>samples</TT
> directory. A complete program, heavily commented for your perusal, is <TT
CLASS="FILENAME"
>apps/bsqldb.c</TT
>.</P
><P
></P
></DIV
></BLOCKQUOTE
><P
><DIV
CLASS="IMPORTANT"
><P
></P
><TABLE
CLASS="IMPORTANT"
WIDTH="100%"
BORDER="0"
><TR
><TD
WIDTH="25"
ALIGN="CENTER"
VALIGN="TOP"
><IMG
SRC="../images/important.gif"
HSPACE="5"
ALT="Important"></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><TABLE
CLASS="SIDEBAR"
BORDER="1"
CELLPADDING="5"
><TR
><TD
><DIV
CLASS="SIDEBAR"
><A
NAME="AEN5707"
></A
><P
><B
>What's the big deal with errors?</B
></P
><P
>Correct handling of errors is extremely important in database applications because they involve two systems most others don't: the network and the database server.  Both can give rise to errors that, if not detected and reported when they occur, let the application proceed blithely on until something truly mysterious happens.  In the worst case, in the absence of a properly reported error, the application may <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>seem</I
></SPAN
> to have updated the data, when in fact it did not.</P
><P
>Every <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> application uses the network, making it subject to network failures.  Database programs also almost always have very high data integrity requirements.  It is necessary to know the row was absolutely, positively committed, once and only once, without error or exception.  Without taking great care to trap and handle all error conditions, no statement about the program's reliability can be made with confidence.</P
></DIV
></TD
></TR
></TABLE
></TD
></TR
></TABLE
></DIV
></P
><P
><P
></P
><P
><B
>How to Get and Build the sample code</B
></P
><OL
TYPE="1"
><LI
><P
>Run <TT
CLASS="FILENAME"
>doc/grep_sample_code</TT
> to extract the <CODE
CLASS="SYMBOL"
>C</CODE
> code from the User Guide <CODE
CLASS="SYMBOL"
>SGML</CODE
> source.</P
></LI
><LI
><P
>Compile</P
></LI
><LI
><P
>Link</P
></LI
></OL
>
			
			<P
></P
><P
><B
>Files Required to Build the Sample Code</B
></P
><UL
><LI
><P
><TT
CLASS="FILENAME"
>sybfront.h</TT
></P
></LI
><LI
><P
><TT
CLASS="FILENAME"
>sybdb.h </TT
></P
></LI
><LI
><P
><TT
CLASS="FILENAME"
>libsybdb.a</TT
> or <TT
CLASS="FILENAME"
>libsybdb.so</TT
></P
></LI
></UL
>
			Your library's extension may vary according to your operating system.</P
><P
>The source code may be built with commands similar to these.  The precise options and paths depend on your particular system.  The commands below work with the GNU compiler and linker on an ELF system with dynamic linking, common on Linux and BSD systems.
			<DIV
CLASS="EXAMPLE"
><A
NAME="AEN5738"
></A
><P
><B
>Example 11-1. Building the Sample Code</B
></P
><PRE
CLASS="SCREEN"
>	<SAMP
CLASS="PROMPT"
>$ </SAMP
><KBD
CLASS="USERINPUT"
>../doc/grep_sample_code ../doc/userguide.sgml &#62; sample.c</KBD
>
	<SAMP
CLASS="PROMPT"
>$ </SAMP
><KBD
CLASS="USERINPUT"
>cc -I /usr/local/include -Wl,-L/usr/local/lib -Wl,-R/usr/local/lib sample.c -lsybdb -o sample</KBD
></PRE
></DIV
>
	where <TT
CLASS="FILENAME"
>/usr/local/include</TT
> and <TT
CLASS="FILENAME"
>/usr/local/lib</TT
> are respectively the locations of your header files and libraries.</P
><P
>We now proceed to the code proper.</P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.INCLUDE"
>Header files</A
></H2
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN5750"
></A
><P
>We need two header files to use <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
>.  We need a few others to deal with I/O in C, as you know.  Also declare the error and message handler functions, more about which later.</P
><P
></P
></DIV
></BLOCKQUOTE
><P
><DIV
CLASS="EXAMPLE"
><A
NAME="E.G.SAMPLECODE.DBLIB.INCLUDE"
></A
><P
><B
>Example 11-2. Sample Code: <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> header files</B
></P
><PRE
CLASS="PROGRAMLISTING"
>#include &#60;stdio.h&#62;
#include &#60;stdlib.h&#62;
#include &#60;string.h&#62;
#include &#60;assert.h&#62;
#include &#60;errno.h&#62;
#include &#60;unistd.h&#62;
#include &#60;libgen.h&#62;
						
						
#include &lt;sybfront.h&gt;	/* <TT
CLASS="FILENAME"
>sybfront.h</TT
> always comes first */
#include &lt;sybdb.h&gt;	/* <TT
CLASS="FILENAME"
>sybdb.h</TT
> is the only other file you need */

int err_handler(DBPROCESS*, int, int, int, char*, char*);
int msg_handler(DBPROCESS*, DBINT, int, int, char*, char*, char*, int);</PRE
></DIV
></P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.PROLOG"
>Prolog</A
></H2
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN5764"
></A
><P
>Nothing special here.  Collect the command line parameters.  We do this with the standard <CODE
CLASS="FUNCTION"
>getopts(3)</CODE
> function.  Cf. <B
CLASS="COMMAND"
>man 3 getopts</B
> for details.</P
><P
></P
></DIV
></BLOCKQUOTE
><P
><DIV
CLASS="EXAMPLE"
><A
NAME="E.G.SAMPLECODE.DBLIB.PROLOG"
></A
><P
><B
>Example 11-3. Sample Code: <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> prolog</B
></P
><PRE
CLASS="PROGRAMLISTING"
>	extern char *optarg;
	extern int optind;
	
	const static char syntax[] =
	"syntax: example -S server -D db -U user -P passwd\n";
	
	struct {
	char *appname, *servername, *dbname, *username, *password;
	} options = {0,0,0,0,0};
	
	int
	main(int argc, char *argv[])
	{
	int i, ch;
	LOGINREC *login;	<A
NAME="SAMPLECODE.INIT.LOGINREC"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
>
	DBPROCESS *dbproc;	<A
NAME="SAMPLECODE.INIT.DBPROCESS"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
>
	RETCODE erc;		<A
NAME="SAMPLECODE.INIT.RETCODE"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
>
	
	options.appname = basename(argv[0]);
	
	while ((ch = getopt(argc, argv, "U:P:S:D:")) != -1) {
	switch (ch) {
	case 'S':
	options.servername = strdup(optarg);
	break;
	case 'D':
	options.dbname = strdup(optarg);
	break;
	case 'U':
	options.username = strdup(optarg);
	break;
	case 'P':
	options.password = strdup(optarg);
	break;
	case '?':
	default:
	fprintf(stderr, syntax);
	exit(1);
	}
	}
	
	argc -= optind;
	argv += optind;
	
	if (! (options.servername
	&#38;&#38; options.username   &#38;&#38; options.password)) {
	fprintf(stderr, syntax);
	exit(1);
	}
	</PRE
></DIV
>
				<DIV
CLASS="CALLOUTLIST"
><P
><B
> 111Prolog Notes</B
></P
><DL
COMPACT="COMPACT"
><DT
><A
HREF="samplecode.htm#SAMPLECODE.INIT.LOGINREC"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
></DT
><DD
><CODE
CLASS="SYMBOL"
>LOGINREC</CODE
> is a structure that describes the client.  It's passed to the server at connect time.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.INIT.DBPROCESS"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
></DT
><DD
><CODE
CLASS="SYMBOL"
>DBPROCESS</CODE
> is a structure that describes the connection.  It is returned by <CODE
CLASS="FUNCTION"
>dbopen()</CODE
>.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.INIT.RETCODE"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
></DT
><DD
><CODE
CLASS="SYMBOL"
>RETCODE</CODE
> is the most common return code type for <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> functions.</DD
></DL
></DIV
></P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.INIT"
>Initialize</A
></H2
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN5791"
></A
><P
>Initialize the library.  Create and populate a <CODE
CLASS="SYMBOL"
>LOGINREC</CODE
> record.</P
><P
></P
></DIV
></BLOCKQUOTE
><P
><DIV
CLASS="EXAMPLE"
><A
NAME="E.G.SAMPLECODE.DBLIB.INITIALIZE"
></A
><P
><B
>Example 11-4. Sample Code: <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> Initialize</B
></P
><PRE
CLASS="PROGRAMLISTING"
>	
	<A
NAME="SAMPLECODE.INIT.DBINIT"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
>
	if (dbinit() == FAIL) {
	fprintf(stderr, "%s:%d: dbinit() failed\n",
	options.appname, __LINE__);
	exit(1);
	}
	
	<A
NAME="SAMPLECODE.INIT.HANDLERS"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
>
	dberrhandle(err_handler);
	dbmsghandle(msg_handler);
	
	<A
NAME="SAMPLECODE.INIT.LOGIN"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
>
	if ((login = dblogin()) == NULL) {
	fprintf(stderr, "%s:%d: unable to allocate login structure\n",
	options.appname, __LINE__);
	exit(1);
	}
	
	<A
NAME="SAMPLECODE.INIT.LOGIN.POPULATE"
><IMG
SRC="../images/callouts/4.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(4)"></A
>
	DBSETLUSER(login, options.username);
	DBSETLPWD(login, options.password);
													
	</PRE
></DIV
>
				<DIV
CLASS="CALLOUTLIST"
><P
><B
> 112Initialization Notes</B
></P
><DL
COMPACT="COMPACT"
><DT
><A
HREF="samplecode.htm#SAMPLECODE.INIT.DBINIT"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
></DT
><DD
><SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Always</I
></SPAN
> make <CODE
CLASS="FUNCTION"
>dbinit()</CODE
> the first db-lib call.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.INIT.HANDLERS"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
></DT
><DD
>Install the error- and mesage-handlers right away.  They're explained in more detail later.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.INIT.LOGIN"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
></DT
><DD
><CODE
CLASS="FUNCTION"
>dblogin()</CODE
> almost never fails.
							But check!  No point in trying to use a null pointer.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.INIT.LOGIN.POPULATE"
><IMG
SRC="../images/callouts/4.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(4)"></A
></DT
><DD
>The <CODE
CLASS="SYMBOL"
>LOGIN</CODE
> record isn't directly accessible.  It's populated via macros like these.  There are other fields, but these two are essential. Look for <CODE
CLASS="SYMBOL"
>SETLsomething</CODE
> in the documentation.</DD
></DL
></DIV
></P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.CONNECT"
>Connect to the server</A
></H2
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN5820"
></A
><P
><CODE
CLASS="FUNCTION"
>dbopen()</CODE
> forms a connection with the server.  We pass our <CODE
CLASS="SYMBOL"
>LOGINREC</CODE
> pointer (which describes the client end), and the name of the server.  Then, optionally, we change to our favored database.  If that step is skipped, the user lands in his default database.</P
><P
></P
></DIV
></BLOCKQUOTE
><P
><DIV
CLASS="EXAMPLE"
><A
NAME="E.G.SAMPLECODE.DBLIB.CONNECT"
></A
><P
><B
>Example 11-5. Sample Code: <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> Connect to the server</B
></P
><PRE
CLASS="PROGRAMLISTING"
>	if ((dbproc = dbopen(login, options.servername)) == NULL) {
	fprintf(stderr, "%s:%d: unable to connect to %s as %s\n",
	options.appname, __LINE__,
	options.servername, options.username);
	exit(1);
	}
	
	if (options.dbname  &#38;&#38; (erc = dbuse(dbproc, options.dbname)) == FAIL) {
	fprintf(stderr, "%s:%d: unable to use to database %s\n",
	options.appname, __LINE__, options.dbname);
	exit(1);
	}
							
	</PRE
></DIV
></P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.QUERY"
>Send a query</A
></H2
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN5831"
></A
><P
><SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> maintains a <I
CLASS="FIRSTTERM"
>command buffer</I
> to hold the SQL to be sent to the server.  Two functions &mdash; <CODE
CLASS="FUNCTION"
>dbcmd()</CODE
> and <CODE
CLASS="FUNCTION"
>dbfcmd()</CODE
> &mdash; build up the query from strings of text. The command buffer is reset after the query is sent to the server.</P
><P
>We left the SQL on the command line.  We fetch it now and send it to the server.</P
><P
></P
></DIV
></BLOCKQUOTE
><P
><DIV
CLASS="EXAMPLE"
><A
NAME="E.G.SAMPLECODE.DBLIB.SEND"
></A
><P
><B
>Example 11-6. Sample Code: <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> Send a query</B
></P
><PRE
CLASS="PROGRAMLISTING"
>	for (i=0; i &#60; argc; i++) {
	assert(argv[i]);
	printf("%s ", argv[i]);
	if ((erc = dbfcmd(dbproc, "%s ", argv[i])) == FAIL) {
	fprintf(stderr, "%s:%d: dbcmd() failed\n", options.appname, __LINE__);
	exit(1);	<A
NAME="SAMPLECODE.QUERY.DBFCMD"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
>
							
	}
	}
	printf("\n");
	
	if ((erc = dbsqlexec(dbproc)) == FAIL) {
	fprintf(stderr, "%s:%d: dbsqlexec() failed\n", options.appname, __LINE__);
	exit(1);		<A
NAME="SAMPLECODE.QUERY.EXEC"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
>
	
	}
	</PRE
></DIV
>
				<DIV
CLASS="CALLOUTLIST"
><P
><B
> 113Initialization Notes</B
></P
><DL
COMPACT="COMPACT"
><DT
><A
HREF="samplecode.htm#SAMPLECODE.QUERY.DBFCMD"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
></DT
><DD
>Failure at this juncture is rare.  The library is merely allocating memory to hold the SQL.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.QUERY.EXEC"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
></DT
><DD
><CODE
CLASS="FUNCTION"
>dbsqlexec()</CODE
> waits for the server to execute the query.  Depending on the complexity of the query, that may take a while.</DD
></DL
></DIV
>
				<CODE
CLASS="FUNCTION"
>dbsqlexec()</CODE
> will fail if something is grossly wrong with the query, e.g. incorrect syntax or a reference to nonexistent table.  It's only the first of a few places where an error can crop up in processing the query, though.  Just because <CODE
CLASS="FUNCTION"
>dbsqlexec()</CODE
> succeeded doesn't mean you're in the clear.</P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.RESULTS"
>Fetch Results</A
></H2
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN5856"
></A
><P
>A query may produce zero, one, or more results.  Broadly, that entails providing buffers to <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> to fill, and iterating over the results a row (and column) at a time.</P
><P
></P
></DIV
></BLOCKQUOTE
><H3
CLASS="BRIDGEHEAD"
><A
NAME="SAMPLECODE.RESULTS.KINDS.OF.RESULTS"
></A
>Kinds of Results</H3
><P
><I
CLASS="FIRSTTERM"
>Results</I
> is a special term: it means more than rows or no rows.  To <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>process the results</I
></SPAN
> means to gather the data returned by the server into the application's variables.
				<DIV
CLASS="TABLE"
><A
NAME="TAB.KINDS.OF.RESULTS"
></A
><P
><B
>Table 11-4. Kinds of Results</B
></P
><TABLE
BORDER="1"
RULES="all"
CLASS="CALSTABLE"
><COL
WIDTH="1*"
TITLE="TYPE"><COL
WIDTH="1*"
TITLE="META"><COL
WIDTH="1*"
TITLE="REG"><COL
WIDTH="1*"
TITLE="COMP"><COL
WIDTH="1*"
TITLE="RET"><COL
WIDTH="1*"
TITLE="EG"><THEAD
><TR
><TH
>Type</TH
><TH
>Metadata</TH
><TH
>Regular Rows</TH
><TH
>Compute Rows</TH
><TH
>Return Status</TH
><TH
>Example SQL</TH
></TR
></THEAD
><TBODY
><TR
><TD
>None</TD
><TD
>None</TD
><TD
>None</TD
><TD
>None</TD
><TD
>None</TD
><TD
>Any <CODE
CLASS="SYMBOL"
>INSERT</CODE
>, <CODE
CLASS="SYMBOL"
>UPDATE</CODE
>, or <CODE
CLASS="SYMBOL"
>DELETE</CODE
> statement </TD
></TR
><TR
><TD
>Empty</TD
><TD
>1 set</TD
><TD
>None</TD
><TD
>0 or more</TD
><TD
>None</TD
><TD
><CODE
CLASS="SYMBOL"
>SELECT name FROM systypes WHERE 0 = 1</CODE
></TD
></TR
><TR
><TD
>Simple </TD
><TD
>1 set </TD
><TD
>0 or more </TD
><TD
>None </TD
><TD
>None </TD
><TD
><KBD
CLASS="USERINPUT"
>SELECT name FROM sysobjects</KBD
> </TD
></TR
><TR
><TD
>Complex </TD
><TD
>2 or more </TD
><TD
>0 or more </TD
><TD
>1 or more </TD
><TD
>None </TD
><TD
><KBD
CLASS="USERINPUT"
>SELECT name FROM sysobjects COMPUTE COUNT(name)</KBD
> </TD
></TR
><TR
><TD
>Stored Procedure </TD
><TD
>0 or more </TD
><TD
>0 or more </TD
><TD
>0 or more </TD
><TD
>1 or more</TD
><TD
><KBD
CLASS="USERINPUT"
>EXEC sp_help sysobjects</KBD
> </TD
></TR
></TBODY
></TABLE
></DIV
></P
><P
>As the above table shows, results can comprise ordinary rows and <I
CLASS="FIRSTTERM"
>compute rows</I
> (resulting from a <CODE
CLASS="SYMBOL"
>COMPUTE</CODE
> clause). Stored procedures may of course contain multiple SQL statements, some of which may be <CODE
CLASS="SYMBOL"
>SELECT</CODE
> statements and might include <CODE
CLASS="SYMBOL"
>COMPUTE</CODE
> clauses. In addition, they generate a  <I
CLASS="FIRSTTERM"
>return status</I
> (with a <CODE
CLASS="SYMBOL"
>RETURN</CODE
> statement or else automatically) and perhaps <CODE
CLASS="SYMBOL"
>OUTPUT</CODE
> parameters.</P
><H3
CLASS="BRIDGEHEAD"
><A
NAME="SAMPLECODE.RESULTS.METADATA.AND.DATA"
></A
>Data and Metadata</H3
><P
>Observe that a row is set of columns, and each column has attributes such as type and size.  The column attributes of a row are collectively known as <I
CLASS="FIRSTTERM"
>metadata</I
>.  The server always returns metadata before any data (even for a a <CODE
CLASS="SYMBOL"
>SELECT</CODE
> statement that produced no rows).</P
><P
> <DIV
CLASS="TABLE"
><A
NAME="TAB.RESULT.FETCHING.FUNCTIONS"
></A
><P
><B
>Table 11-5. Result-fetching functions</B
></P
><TABLE
BORDER="1"
RULES="all"
CLASS="CALSTABLE"
><COL
WIDTH="1*"
TITLE="FUNC"><COL
WIDTH="1*"
TITLE="TYPE"><COL
WIDTH="1*"
TITLE="RET"><COL
WIDTH="1*"
TITLE="ETC"><THEAD
><TR
><TH
>Function</TH
><TH
>Fetches</TH
><TH
>Returns</TH
><TH
>Comment</TH
></TR
></THEAD
><TBODY
><TR
><TD
><CODE
CLASS="FUNCTION"
>dbresults()</CODE
></TD
><TD
>metadata</TD
><TD
><CODE
CLASS="SYMBOL"
>SUCCEED</CODE
>, <CODE
CLASS="SYMBOL"
>FAIL</CODE
> or, <CODE
CLASS="SYMBOL"
>NO_MORE_RESULTS</CODE
>.  </TD
><TD
><CODE
CLASS="SYMBOL"
>SUCCEED</CODE
> indicates just that: the query executed successfully (whew!). There may be metadata (and perhaps data) and/or stored procedure outputs available.  </TD
></TR
><TR
><TD
><CODE
CLASS="FUNCTION"
>nextrow()</CODE
></TD
><TD
>data</TD
><TD
>	<CODE
CLASS="SYMBOL"
>REG_ROW</CODE
>,
									<I
CLASS="FIRSTTERM"
>compute_id</I
>,
									<CODE
CLASS="SYMBOL"
>NO_MORE_ROWS</CODE
>,
									<CODE
CLASS="SYMBOL"
>BUF_FULL</CODE
>,
									or <CODE
CLASS="SYMBOL"
>FAIL</CODE
>.
									</TD
><TD
>Places fetched data into bound columns, if any.  </TD
></TR
></TBODY
></TABLE
></DIV
></P
><H3
CLASS="BRIDGEHEAD"
><A
NAME="SAMPLECODE.RESULTS.BINDING"
></A
>Binding</H3
><P
>Each time <CODE
CLASS="SYMBOL"
>dbresults()</CODE
> returns <CODE
CLASS="SYMBOL"
>SUCCEED</CODE
>, there is something to retrieve. <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> has different functions to deal with the different kinds of results.  The functions are of two kinds: those that convert the data into a form desired by the application, known as <I
CLASS="FIRSTTERM"
>binding</I
>, and those that return the data in <SPAN
CLASS="QUOTE"
>"native"</SPAN
> form.</P
><P
>To understand binding, it may be easiest to examine two primitive functions, <CODE
CLASS="FUNCTION"
>dbdata()</CODE
> and <CODE
CLASS="FUNCTION"
>dbconvert()</CODE
>.  <CODE
CLASS="FUNCTION"
>dbdata()</CODE
> returns a pointer to the column's data.  The data to which it points are in <SPAN
CLASS="QUOTE"
>"native"</SPAN
> form, 4 bytes for an <CODE
CLASS="SYMBOL"
>INT</CODE
>, 8 bytes for a <CODE
CLASS="SYMBOL"
>DATETIME</CODE
> and so on.  <CODE
CLASS="FUNCTION"
>dbconvert()</CODE
> converts between datatypes; you can hand it an integer and get back a character array (or a <CODE
CLASS="SYMBOL"
>C double</CODE
>.  You might think of  <CODE
CLASS="FUNCTION"
>dbconvert()</CODE
> as <CODE
CLASS="FUNCTION"
>atoi(3)</CODE
> on steroids). <CODE
CLASS="FUNCTION"
>dbbind()</CODE
> combines these two functions.  The application indicates in what form it would like to use each column, and the library converts them  on the fly as each row is read.</P
><P
>To <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>bind a column</I
></SPAN
> is to provide a buffer to <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> to fill and indicate which datatype the buffer is meant to hold. <A
NAME="AEN5993"
HREF="#FTN.AEN5993"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
></P
><P
>It may be well to pause here to observe the three ways a datatype is described in a <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> program.
				<P
></P
><DIV
CLASS="VARIABLELIST"
><P
><B
><CODE
CLASS="FUNCTION"
>db-lib</CODE
> Datatype Descriptors</B
></P
><DL
><DT
>Sever Datatype</DT
><DD
><P
>Describes the data as an abstract type, not representing any particular kind of storage.  <CODE
CLASS="SYMBOL"
>SYBREAL</CODE
>, for example, doesn't imply any particular arrangement of bits; it just means <SPAN
CLASS="QUOTE"
>"a floating-point datatype corresponding to the <CODE
CLASS="SYMBOL"
>T-SQL REAL</CODE
> type on the server."</SPAN
> These all begin with <CODE
CLASS="SYMBOL"
>SYB</CODE
>, e.g. <CODE
CLASS="SYMBOL"
>SYBINT4</CODE
>.</P
></DD
><DT
>Program Variable Datatype</DT
><DD
><P
>Defines a <CODE
CLASS="SYMBOL"
>C</CODE
> variable in a machine-independent way. Because a <CODE
CLASS="SYMBOL"
>C</CODE
> defines its  <CODE
CLASS="SYMBOL"
>int</CODE
> type according the CPU architecture, it may have 2, 4, 8, or some other number of bytes.  A <CODE
CLASS="SYMBOL"
>DBINT</CODE
> on the other hand, is guaranteed to be 4 bytes and, as such, assuredly will hold any value returned by the server from a <CODE
CLASS="SYMBOL"
>T-SQL INT</CODE
> column.  These all begin with <CODE
CLASS="SYMBOL"
>DB</CODE
>, e.g. <CODE
CLASS="SYMBOL"
>DBREAL</CODE
>.</P
></DD
><DT
>Bind Type</DT
><DD
><P
>Prescribes a conversion operation.  Indicates to <CODE
CLASS="FUNCTION"
>dbbind()</CODE
> the  <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Program Variable Datatype</I
></SPAN
> defined by the target buffer.  Sybase and Microsoft call this the <SPAN
CLASS="QUOTE"
>"vartype"</SPAN
>. These all <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>end</I
></SPAN
> with <CODE
CLASS="SYMBOL"
>BIND</CODE
>, e.g. <CODE
CLASS="SYMBOL"
>STRINGBIND</CODE
>.</P
></DD
></DL
></DIV
></P
><P
>Typically it's more convenient to have <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> convert the data into the desired form.  The function that does that is <CODE
CLASS="FUNCTION"
>dbind()</CODE
>.  So: after fetching the metadata, and before fetching the data, we usually prepare the bound columns.</P
><H3
CLASS="BRIDGEHEAD"
><A
NAME="SAMPLECODE.RESULTS.FETCHING.DATA"
></A
>Fetching Data</H3
><P
> <DIV
CLASS="TABLE"
><A
NAME="TAB.DATA.FETCHING.FUNCTIONS"
></A
><P
><B
>Table 11-6. Data-fetching functions</B
></P
><TABLE
BORDER="1"
RULES="all"
CLASS="CALSTABLE"
><COL
WIDTH="1*"
TITLE="TYPE"><COL
WIDTH="1*"
TITLE="REG"><COL
WIDTH="1*"
TITLE="COMP"><COL
WIDTH="1*"
TITLE="RET"><COL
WIDTH="1*"
TITLE="OUT"><THEAD
><TR
><TH
>Type</TH
><TH
>Regular rows</TH
><TH
>Compute rows</TH
><TH
>Return status</TH
><TH
><CODE
CLASS="SYMBOL"
>OUTPUT</CODE
> parameters</TH
></TR
></THEAD
><TBODY
><TR
><TD
>Meta </TD
><TD
><CODE
CLASS="FUNCTION"
>dbnumcols()</CODE
> </TD
><TD
>	<CODE
CLASS="FUNCTION"
>dbnumcompute()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbnumalts()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbaltop()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbbylist()</CODE
> </TD
><TD
><CODE
CLASS="FUNCTION"
>dbhasretstatus()</CODE
> </TD
><TD
><CODE
CLASS="FUNCTION"
>dbnumrets()</CODE
> </TD
></TR
><TR
><TD
>Binding </TD
><TD
><CODE
CLASS="FUNCTION"
>dbbind()</CODE
>, <CODE
CLASS="FUNCTION"
>dbnullbind()</CODE
> </TD
><TD
>	<CODE
CLASS="FUNCTION"
>dbaltbind()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbanullbind()</CODE
> </TD
><TD
><CODE
CLASS="FUNCTION"
>dbretstatus()</CODE
> </TD
><TD
>none </TD
></TR
><TR
><TD
>Native </TD
><TD
><CODE
CLASS="FUNCTION"
>dbdatlen()</CODE
>, <CODE
CLASS="FUNCTION"
>dbdata()</CODE
> </TD
><TD
>	<CODE
CLASS="FUNCTION"
>dbadlen()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbalttype()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbaltutype()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbaltlen()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbadata()</CODE
> </TD
><TD
>none </TD
><TD
>	<CODE
CLASS="FUNCTION"
>dbretdata()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbretlen()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbretname()</CODE
>,
									<CODE
CLASS="FUNCTION"
>dbrettype()</CODE
> </TD
></TR
></TBODY
></TABLE
></DIV
></P
><P
>The paradigm may now perhaps be clear:  Query, fetch results, bind columns, fetch regular rows, fetch compute rows, fetch stored procedure outputs.  Repeat as necessary.</P
><P
> <DIV
CLASS="TABLE"
><A
NAME="TAB.PUTTING.IT.ALL.TOGETHER"
></A
><P
><B
>Table 11-7. Putting it all together </B
></P
><TABLE
BORDER="1"
RULES="all"
CLASS="CALSTABLE"
><COL
WIDTH="1*"
TITLE="STEP"><COL
WIDTH="1*"
TITLE="FUNC"><COL
WIDTH="1*"
TITLE="ONCE"><COL
WIDTH="1*"
TITLE="FREQ"><THEAD
><TR
><TH
>Step </TH
><TH
>Function </TH
><TH
>Once Per </TH
><TH
>Many Times Per </TH
></TR
></THEAD
><TBODY
><TR
><TD
>Query </TD
><TD
><CODE
CLASS="FUNCTION"
>dbsqlexec()</CODE
> </TD
><TD
>Query</TD
><TD
>Program</TD
></TR
><TR
><TD
>Fetch metadata </TD
><TD
><CODE
CLASS="FUNCTION"
>dbresults()</CODE
> </TD
><TD
>SQL statement </TD
><TD
>Query </TD
></TR
><TR
><TD
>Prepare variables </TD
><TD
><CODE
CLASS="FUNCTION"
>dbbind()</CODE
> </TD
><TD
>Column</TD
><TD
>Statement</TD
></TR
><TR
><TD
>Fetch regular data </TD
><TD
><CODE
CLASS="FUNCTION"
>dbnextrow()</CODE
> </TD
><TD
>Row </TD
><TD
>Statement </TD
></TR
><TR
><TD
>Fetch compute data </TD
><TD
><CODE
CLASS="FUNCTION"
>dbnextrow()</CODE
>  </TD
><TD
>Compute column </TD
><TD
>Statement </TD
></TR
><TR
><TD
>Fetch output parameters </TD
><TD
><CODE
CLASS="FUNCTION"
>dbretdata()</CODE
> </TD
><TD
>output parameter </TD
><TD
>Stored procedure </TD
></TR
><TR
><TD
>Fetch return status </TD
><TD
><CODE
CLASS="FUNCTION"
>dbretstatus()</CODE
> </TD
><TD
>Stored procedure </TD
><TD
>Program </TD
></TR
></TBODY
></TABLE
></DIV
></P
><P
><DIV
CLASS="IMPORTANT"
><P
></P
><TABLE
CLASS="IMPORTANT"
WIDTH="100%"
BORDER="0"
><TR
><TD
WIDTH="25"
ALIGN="CENTER"
VALIGN="TOP"
><IMG
SRC="../images/important.gif"
HSPACE="5"
ALT="Important"></TD
><TH
ALIGN="LEFT"
VALIGN="MIDDLE"
><B
>Fetch All Rows!</B
></TH
></TR
><TR
><TD
>&nbsp;</TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><TABLE
CLASS="SIDEBAR"
BORDER="1"
CELLPADDING="5"
><TR
><TD
><DIV
CLASS="SIDEBAR"
><P
></P
><A
NAME="AEN6155"
></A
><P
><SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> doesn't insist every column &mdash; or even any column &mdash; be bound or otherwise retrieved into the application's variables.  There is, however, one absolutely <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>crucial, inflexible, unalterable</I
></SPAN
> requirement: the application must <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>process all rows produced by the query</I
></SPAN
>.  Before the <CODE
CLASS="SYMBOL"
>DBPROCESS</CODE
> can be used for another query, the application must either fetch all rows, or cancel the results and receive an acknowledgement from the server. Cancelling is beyond the scope of this document, so for now  <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
> fetch all rows</I
></SPAN
>.</P
><P
></P
></DIV
></TD
></TR
></TABLE
></TD
></TR
></TABLE
></DIV
></P
><P
>Now, at last, some sample code that fetches data.  In the interest of simplicity, we don't bind anything except regular rows.</P
><P
><DIV
CLASS="EXAMPLE"
><A
NAME="E.G.SAMPLECODE.DBLIB.FETCH"
></A
><P
><B
>Example 11-7. Sample Code: <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> Fetch Results</B
></P
><PRE
CLASS="PROGRAMLISTING"
>	while ((erc = dbresults(dbproc)) != NO_MORE_RESULTS) {
	struct COL 						<A
NAME="SAMPLECODE.RESULTS.DBRESULTS"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
>
	{
	char *name;
	char *buffer;
	int type, size, status;
	} *columns, *pcol;
	int ncols;
	int row_code;
	
	if (erc == FAIL) {
	fprintf(stderr, "%s:%d: dbresults failed\n",
	options.appname, __LINE__);
	exit(1);
	}
	
	ncols = dbnumcols(dbproc);
	
	if ((columns = calloc(ncols, sizeof(struct COL))) == NULL) {
	perror(NULL);
	exit(1);
	}
	
	/*
	* Read metadata and bind.
	*/
	for (pcol = columns; pcol - columns &#60; ncols; pcol++) {
	int c = pcol - columns + 1;
	
	pcol-&#62;name = dbcolname(dbproc, c);		<A
NAME="SAMPLECODE.RESULTS.C"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
>
	pcol-&#62;type = dbcoltype(dbproc, c);
	pcol-&#62;size = dbcollen(dbproc, c);
	
	if (SYBCHAR != pcol-&#62;type) {			<A
NAME="SAMPLECODE.RESULTS.DBCOLLEN"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
>
	pcol-&#62;size = dbwillconvert(pcol-&#62;type, SYBCHAR);
	}
	
	printf("%*s ", pcol-&#62;size, pcol-&#62;name);
	
	if ((pcol-&#62;buffer = calloc(1, pcol-&#62;size + 1)) == NULL){
	perror(NULL);
	exit(1);
	}
	
	erc = dbbind(dbproc, c, NTBSTRINGBIND,	<A
NAME="SAMPLECODE.RESULTS.DBBIND"
><IMG
SRC="../images/callouts/4.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(4)"></A
>
	pcol-&#62;size+1, (BYTE*)pcol-&#62;buffer);
	if (erc == FAIL) {
	fprintf(stderr, "%s:%d: dbbind(%d) failed\n",
	options.appname, __LINE__, c);
	exit(1);
	}
	erc = dbnullbind(dbproc, c, &#38;pcol-&#62;status);	<A
NAME="SAMPLECODE.RESULTS.DBNULLBIND"
><IMG
SRC="../images/callouts/5.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(5)"></A
>
	if (erc == FAIL) {
	fprintf(stderr, "%s:%d: dbnullbind(%d) failed\n",
	options.appname, __LINE__, c);
	exit(1);
	}
	}
	printf("\n");
	
	/*
	* Print the data to stdout.
	*/
	while ((row_code = dbnextrow(dbproc)) != NO_MORE_ROWS){	<A
NAME="SAMPLECODE.RESULTS.DBNEXTROW"
><IMG
SRC="../images/callouts/6.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(6)"></A
>
	switch (row_code) {
	case REG_ROW:
	for (pcol=columns; pcol - columns &#60; ncols; pcol++) {
	char *buffer = pcol-&#62;status == -1?
	"NULL" : pcol-&#62;buffer;
	printf("%*s ", pcol-&#62;size, buffer);
	}
	printf("\n");
	break;
	
	case BUF_FULL:
	assert(row_code != BUF_FULL);
	break;
	
	case FAIL:
	fprintf(stderr, "%s:%d: dbresults failed\n",
	options.appname, __LINE__);
	exit(1);
	break;
	
	default: 					<A
NAME="SAMPLECODE.RESULTS.COMPUTEID"
><IMG
SRC="../images/callouts/7.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(7)"></A
>
	printf("Data for computeid %d ignored\n", row_code);
	}
							
	}
	
	/* free metadata and data buffers */
	for (pcol=columns; pcol - columns &#60; ncols; pcol++) {
	free(pcol-&#62;buffer);
	}
	free(columns);
	
	/*
	* Get row count, if available.
	*/
	if (DBCOUNT(dbproc) &#62; -1)
	fprintf(stderr, "%d rows affected\n", DBCOUNT(dbproc));
	
	/*
	* Check return status
	*/
	if (dbhasretstat(dbproc) == TRUE) {
	printf("Procedure returned %d\n", dbretstatus(dbproc));
	}
	}
	
	dbclose(dbproc);
	dbexit();
	exit(0);
	}
	</PRE
></DIV
>
				<DIV
CLASS="CALLOUTLIST"
><P
><B
><A
NAME="CO.FETCHING"
></A
> 114Data-fetching Notes</B
></P
><DL
COMPACT="COMPACT"
><DT
><A
HREF="samplecode.htm#SAMPLECODE.RESULTS.DBRESULTS"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
></DT
><DD
>As soon as <CODE
CLASS="FUNCTION"
>dbresults()</CODE
> reports <CODE
CLASS="SYMBOL"
>SUCCESS</CODE
>, the row's metadata are available.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.RESULTS.C"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
></DT
><DD
><SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> columns start with 1.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.RESULTS.DBCOLLEN"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
></DT
><DD
><CODE
CLASS="FUNCTION"
>dbcollen()</CODE
> returns the sizeof the native data (e.g. 4 bytes for a T-SQL <CODE
CLASS="SYMBOL"
>INT</CODE
>).  We'll use <CODE
CLASS="FUNCTION"
>dbbind()</CODE
> to convert everything to strings.  If the column is <CODE
CLASS="SYMBOL"
>[VAR]CHAR</CODE
>, we want the column's defined size, otherwise we want its maximum size when represented as a string, which FreeTDS's <CODE
CLASS="FUNCTION"
>dbwillconvert()</CODE
> returns (for fixed-length datatypes).  <A
NAME="AEN6191"
HREF="#FTN.AEN6191"
><SPAN
CLASS="footnote"
>[2]</SPAN
></A
></DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.RESULTS.DBBIND"
><IMG
SRC="../images/callouts/4.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(4)"></A
></DT
><DD
><CODE
CLASS="SYMBOL"
>NTBSTRINGBIND</CODE
> null-terminates the character array for us.  <SPAN
CLASS="QUOTE"
>"NTB"</SPAN
> might perhaps stand for <SPAN
CLASS="QUOTE"
>"null terminating byte"</SPAN
>.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.RESULTS.DBNULLBIND"
><IMG
SRC="../images/callouts/5.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(5)"></A
></DT
><DD
>A zero-length string is not a NULL!  <CODE
CLASS="FUNCTION"
>dbnullbind()</CODE
> arranges for the passed buffer to be set to -1 whenever that column is NULL for a particular row.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.RESULTS.DBNEXTROW"
><IMG
SRC="../images/callouts/6.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(6)"></A
></DT
><DD
>Each time <CODE
CLASS="FUNCTION"
>dbnextrow()</CODE
> returns <CODE
CLASS="SYMBOL"
>REG_ROW</CODE
>, it has filled the bound buffers with the converted values for the row.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.RESULTS.COMPUTEID"
><IMG
SRC="../images/callouts/7.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(7)"></A
></DT
><DD
>Computed rows are left as an exercise to the reader.</DD
></DL
></DIV
></P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.ERRORS"
>Messages and Errors</A
></H2
><BLOCKQUOTE
CLASS="ABSTRACT"
><DIV
CLASS="ABSTRACT"
><P
></P
><A
NAME="AEN6210"
></A
><P
>Errors may originate on the server or in the library itself.  The former are known as <I
CLASS="FIRSTTERM"
>messages</I
> (because they are: they arrive as messages from the server); the latter are termed <I
CLASS="FIRSTTERM"
>errors</I
>.  Their handling is a little intimidating.  It requires writing and installing a callback function (whose parameters are predefined by <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
>), and thinking about how to handle different types of errors.</P
><P
></P
></DIV
></BLOCKQUOTE
><P
></P
><DIV
CLASS="VARIABLELIST"
><P
><B
>Kinds of Errors</B
></P
><DL
><DT
>Messages</DT
><DD
><P
><SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Messages</I
></SPAN
> arise because the server has something to say.  <A
NAME="AEN6222"
HREF="#FTN.AEN6222"
><SPAN
CLASS="footnote"
>[3]</SPAN
></A
>.  They usually describe some problem encountered executing the SQL.  Perhaps the SQL refers to a nonexistent object or attempted to violate a constraint.  But they can also be benign, indicating for instance merely that the default database has changed.</P
></DD
><DT
>Errors</DT
><DD
><P
><SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>Errors</I
></SPAN
> arise either because the application has misused <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> in some way &mdash; say, passed a NULL <CODE
CLASS="SYMBOL"
>DBPROCESS</CODE
> pointer or tried to issue a query while results were pending &mdash; or because some trouble cropped up in communicating with the server (couldn't find it, say, or didn't hear back from it).</P
></DD
></DL
></DIV
><P
>Why these two require distinct handling is lost in the mists of time. But it does help to keep them distinct in your mind, especially while reading the documentation.</P
><P
>To have <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> use your handler, pass its name to the appropriate  <CODE
CLASS="FUNCTION"
>dberrhandle()</CODE
> or <CODE
CLASS="FUNCTION"
>dbmsghandle()</CODE
> function immediately after calling <CODE
CLASS="FUNCTION"
>dbinit()</CODE
>.</P
><P
><DIV
CLASS="EXAMPLE"
><A
NAME="E.G.SAMPLECODE.DBLIB.ERRORS"
></A
><P
><B
>Example 11-8. Sample Code: <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> Error and Message handlers</B
></P
><PRE
CLASS="PROGRAMLISTING"
>	int
	msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity,
	char *msgtext, char *srvname, char *procname, int line)
	{									<A
NAME="SAMPLECODE.ERRORS.MSGHANDLER.ARGS"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
>
	enum {changed_database = 5701, changed_language = 5703 };	<A
NAME="SAMPLECODE.ERRORS.MSGHANDLER.SUPPRESS"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
>
	
	if (msgno == changed_database || msgno == changed_language)
	return 0;
	
	if (msgno &#62; 0) {
	fprintf(stderr, "Msg %ld, Level %d, State %d\n",
	(long) msgno, severity, msgstate);
	
	if (strlen(srvname) &#62; 0)
	fprintf(stderr, "Server '%s', ", srvname);
	if (strlen(procname) &#62; 0)
	fprintf(stderr, "Procedure '%s', ", procname);
	if (line &#62; 0)
	fprintf(stderr, "Line %d", line);
	
	fprintf(stderr, "\n\t");
	}
	fprintf(stderr, "%s\n", msgtext);
	
	if (severity &#62; 10) {						<A
NAME="SAMPLECODE.ERRORS.MSGHANDLER.SEVERITY"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
>
	fprintf(stderr, "%s: error: severity %d &#62; 10, exiting\n",
	options.appname, severity);
	exit(severity);
	}
	
	return 0;							<A
NAME="SAMPLECODE.ERRORS.MSGHANDLER.RETURN"
><IMG
SRC="../images/callouts/4.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(4)"></A
>
	}
	
	int
	err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr,
	char *dberrstr, char *oserrstr)
	{									<A
NAME="SAMPLECODE.ERRORS.ERRHANDLER.ARGS"
><IMG
SRC="../images/callouts/5.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(5)"></A
>
	if (dberr) {							<A
NAME="SAMPLECODE.ERRORS.ERRHANDLER.MSGS"
><IMG
SRC="../images/callouts/6.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(6)"></A
>
	fprintf(stderr, "%s: Msg %d, Level %d\n",
	options.appname, dberr, severity);
	fprintf(stderr, "%s\n\n", dberrstr);
	}
	
	else {
	fprintf(stderr, "%s: DB-LIBRARY error:\n\t", options.appname);
	fprintf(stderr, "%s\n", dberrstr);
	}
	
	return INT_CANCEL;						<A
NAME="SAMPLECODE.ERRORS.ERRHANDLER.RETURN"
><IMG
SRC="../images/callouts/7.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(7)"></A
>
	}
	</PRE
></DIV
>
				<DIV
CLASS="NOTE"
><P
></P
><TABLE
CLASS="NOTE"
WIDTH="100%"
BORDER="0"
><TR
><TD
WIDTH="25"
ALIGN="CENTER"
VALIGN="TOP"
><IMG
SRC="../images/note.gif"
HSPACE="5"
ALT="Note"></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><P
>Handlers are always called before the function that engendered them returns control to the application.</P
></TD
></TR
></TABLE
></DIV
>
				<DIV
CLASS="CALLOUTLIST"
><P
><B
> 115Error Handling Notes</B
></P
><DL
COMPACT="COMPACT"
><DT
><A
HREF="samplecode.htm#SAMPLECODE.ERRORS.MSGHANDLER.ARGS"
><IMG
SRC="../images/callouts/1.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(1)"></A
></DT
><DD
>When first writing a handler, pay careful attention to the precise type of each parameter.  Only by carefully matching them will you convince a modern <CODE
CLASS="SYMBOL"
>C</CODE
> compiler that the address of your function is of the type accepted by <CODE
CLASS="FUNCTION"
>dbmsghandle()</CODE
>. <A
NAME="AEN6257"
HREF="#FTN.AEN6257"
><SPAN
CLASS="footnote"
>[4]</SPAN
></A
></DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.ERRORS.MSGHANDLER.SUPPRESS"
><IMG
SRC="../images/callouts/2.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(2)"></A
></DT
><DD
>Some messages don't convey much, as though the server gets lonely sometimes.  You're not obliged to print every one.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.ERRORS.MSGHANDLER.SEVERITY"
><IMG
SRC="../images/callouts/3.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(3)"></A
></DT
><DD
>Severities are defined in the <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>server</I
></SPAN
> documentation, and can be set by the <CODE
CLASS="SYMBOL"
>T-SQL RAISERROR</CODE
> statement.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.ERRORS.MSGHANDLER.RETURN"
><IMG
SRC="../images/callouts/4.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(4)"></A
></DT
><DD
>Message handlers <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>always and only ever</I
></SPAN
> return zero.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.ERRORS.ERRHANDLER.ARGS"
><IMG
SRC="../images/callouts/5.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(5)"></A
></DT
><DD
>When first writing the handler, pay careful attention to the precise type of each parameter.  Only by carefully matching them will you convince a modern <CODE
CLASS="SYMBOL"
>C</CODE
> compiler that the address of your function is of the type accepted by <CODE
CLASS="FUNCTION"
>dberrhandle()</CODE
>. <A
NAME="AEN6272"
HREF="#FTN.AEN6272"
><SPAN
CLASS="footnote"
>[5]</SPAN
></A
></DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.ERRORS.ERRHANDLER.MSGS"
><IMG
SRC="../images/callouts/6.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(6)"></A
></DT
><DD
>Some messages are so severe they provoke <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> into calling the error handler, too!  If you have both installed &mdash; and of course you do, right? &mdash; then you can skip those lacking an error number.</DD
><DT
><A
HREF="samplecode.htm#SAMPLECODE.ERRORS.ERRHANDLER.RETURN"
><IMG
SRC="../images/callouts/7.gif"
HSPACE="0"
VSPACE="0"
BORDER="0"
ALT="(7)"></A
></DT
><DD
>While <CODE
CLASS="SYMBOL"
>INT_CANCEL</CODE
> is the most common return code, it's not the only one.  For one thing, the error handler's return code can control how long <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> keeps retrying timeout errors.  See the documentation for details.</DD
></DL
></DIV
></P
><P
><DIV
CLASS="NOTE"
><P
></P
><TABLE
CLASS="NOTE"
WIDTH="100%"
BORDER="0"
><TR
><TD
WIDTH="25"
ALIGN="CENTER"
VALIGN="TOP"
><IMG
SRC="../images/note.gif"
HSPACE="5"
ALT="Note"></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><P
>No matter what the error handler says or does, it can't remedy the error.  It's <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>still</I
></SPAN
> an error and usually the best that can happen is that the function will return <CODE
CLASS="SYMBOL"
>FAIL</CODE
>.  The exception is timeout conditions, when the handler can stave off failure by requesting retries.</P
></TD
></TR
></TABLE
></DIV
></P
><P
>You may be asking yourself, <SPAN
CLASS="QUOTE"
>"OK, fine, I can print the error message.  But what if I want to communicate something back to line in my program where the error occurred?  How to do that?"</SPAN
>  First of all, remember  the calling function &mdash; that's your application &mdash; will learn of an error from the return code.  If it needs more detail, though, there are two ways to pass it.
				
				<P
></P
><OL
TYPE="1"
><LI
><P
>Set a global variable.</P
></LI
><LI
><P
>Use 	<CODE
CLASS="FUNCTION"
>setuserdata()</CODE
> and
							<CODE
CLASS="FUNCTION"
>getuserdata()</CODE
>.</P
></LI
></OL
>
				
				<DIV
CLASS="TIP"
><P
></P
><TABLE
CLASS="TIP"
WIDTH="100%"
BORDER="0"
><TR
><TD
WIDTH="25"
ALIGN="CENTER"
VALIGN="TOP"
><IMG
SRC="../images/tip.gif"
HSPACE="5"
ALT="Tip"></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
><P
>If your application is written in <CODE
CLASS="SYMBOL"
>C++</CODE
>, you may be tempted to use <CODE
CLASS="FUNCTION"
>throw()</CODE
>. Don't!  Your handler is a <CODE
CLASS="SYMBOL"
>C</CODE
> function and, more important, <SPAN
CLASS="emphasis"
><I
CLASS="EMPHASIS"
>it's an extension of <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
></I
></SPAN
>.  You can put a <CODE
CLASS="FUNCTION"
>throw()</CODE
> in your handler and it will compile. But when it executes, it's going to rip through <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
>'s stack.  Your application will be unuseable at that point, if it doesn't cause a segment fault.</P
></TD
></TR
></TABLE
></DIV
></P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="SAMPLECODE.WRAPUP"
>Last Remarks</A
></H2
><P
>We've reached the end of our <SPAN
CLASS="SYSTEMITEM"
>DB-Library</SPAN
> tour.  The almost 300 lines of <CODE
CLASS="SYMBOL"
>C</CODE
> above constitute program with these features:
				<P
></P
><P
><B
>Sample Code features</B
></P
><UL
><LI
><P
>Accepts command-line parameters and SQL.</P
></LI
><LI
><P
>Checks for errors and server messages.</P
></LI
><LI
><P
>Processes any number of results..</P
></LI
><LI
><P
>Prints results in columns of suitable widths.</P
></LI
></UL
>
				
				There are things it doesn't do, in the name of simplicity.
				<P
></P
><P
><B
>Sample Code nonfeatures</B
></P
><UL
><LI
><P
>No BCP (bulk copy) mode</P
></LI
><LI
><P
>No RPC (remote procedure call) mode, preventing it from  retrieving output parameters.</P
></LI
></UL
>
				Your humble author hopes you found it worthwhile.  Happy Hacking.</P
></DIV
></DIV
><H3
CLASS="FOOTNOTES"
>Notes</H3
><TABLE
BORDER="0"
CLASS="FOOTNOTES"
WIDTH="100%"
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN5993"
HREF="samplecode.htm#AEN5993"
><SPAN
CLASS="footnote"
>[1]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>This is the sort of thing <CODE
CLASS="SYMBOL"
>C++</CODE
>'s type system does so much better</P
></TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN6191"
HREF="samplecode.htm#AEN6191"
><SPAN
CLASS="footnote"
>[2]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>For IMAGE data, we need to multiply by 2, because <CODE
CLASS="FUNCTION"
>dbbind()</CODE
> will convert each byte to a hexadecimal pair.  The example program will report an error with IMAGE data.</P
></TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN6222"
HREF="samplecode.htm#AEN6222"
><SPAN
CLASS="footnote"
>[3]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>Just one more way in which databases differ from files.</P
></TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN6257"
HREF="samplecode.htm#AEN6257"
><SPAN
CLASS="footnote"
>[4]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>Back in K&amp;R days, that wasn't such a problem.  But there were other problems, some much worse.</P
></TD
></TR
><TR
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="5%"
><A
NAME="FTN.AEN6272"
HREF="samplecode.htm#AEN6272"
><SPAN
CLASS="footnote"
>[5]</SPAN
></A
></TD
><TD
ALIGN="LEFT"
VALIGN="TOP"
WIDTH="95%"
><P
>If that 					advice sounds familiar, it's because it bears repeating.</P
></TD
></TR
></TABLE
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="odbc.api.summary.htm"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.htm"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="acknowledgments.htm"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>ODBC API Implementation Summary</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="programming.htm"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Acknowledgments</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>

Perl :: Catalyst :: PostgreSQL :: RPM Valid HTML 4.01 Transitional