JezK
Edit File: samplecode.html
<?xml version="1.0" encoding="UTF-8" standalone="no"?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>DB-Library for the Tenderfoot</title><link rel="stylesheet" type="text/css" href="userguide.css" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="home" href="index.html" title="FreeTDS User Guide" /><link rel="up" href="programming.html" title="Chapter 11. Programming" /><link rel="prev" href="odbc.api.summary.html" title="ODBC API Implementation Summary" /><link rel="next" href="acknowledgments.html" title="Chapter 12. Acknowledgments" /></head><body><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="3" align="center">DB-Library for the Tenderfoot</th></tr><tr><td width="20%" align="left"><a accesskey="p" href="odbc.api.summary.html">Prev</a> </td><th width="60%" align="center">Chapter 11. Programming</th><td width="20%" align="right"> <a accesskey="n" href="acknowledgments.html">Next</a></td></tr></table><hr /></div><div class="sect1"><div class="titlepage"><div><div><h2 class="title" style="clear: both"><a id="samplecode"></a>DB-Library for the Tenderfoot</h2></div></div></div><div class="epigraph"><p>Few things are harder to put up with than the annoyance of a good example.</p><div class="attribution"><span>—<span class="attribution">Mark Twain</span></span></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p>Below is a complete working <code class="systemitem">DB-Library</code> program, presented as a series of examples. </p><div class="itemizedlist"><p class="title"><strong>Features of sample code</strong></p><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>Processes command-line options to select the server, database, username, and password</p></li><li class="listitem"><p>Remaining arguments on the command line comprise the SQL query to execute</p></li><li class="listitem"><p>Installs error and message handlers</p></li><li class="listitem"><p>Illustrates correct row-processing</p></li><li class="listitem"><p>Illustrates correct error detection and handling</p></li></ul></div><p> Other sample code may be found in the distribution, in the cleverly named <code class="filename">samples</code> directory. A complete program, heavily commented for your perusal, is <code class="filename">apps/bsqldb.c</code>.</p></div><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Important"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Important]" src="../images/important.gif" /></td><th align="left">Important</th></tr><tr><td align="left" valign="top"><div class="sidebar"><div class="titlepage"><div><div><p class="title"><strong>What's the big deal with errors?</strong></p></div></div></div><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"><em>seem</em></span> to have updated the data, when in fact it did not.</p><p>Every <code class="systemitem">DB-Library</code> 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></div><div class="orderedlist"><p class="title"><strong>How to Get and Build the sample code</strong></p><ol class="orderedlist" type="1"><li class="listitem"><p>Run <code class="filename">doc/grep_sample_code</code> to extract the <span class="symbol">C</span> code from the User Guide <span class="symbol">XML</span> source.</p></li><li class="listitem"><p>Compile</p></li><li class="listitem"><p>Link</p></li></ol></div><p> </p><div class="itemizedlist"><p class="title"><strong>Files Required to Build the Sample Code</strong></p><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="filename">sybfront.h</code></p></li><li class="listitem"><p><code class="filename">sybdb.h </code></p></li><li class="listitem"><p><code class="filename">libsybdb.a</code> or <code class="filename">libsybdb.so</code></p></li></ul></div><p> 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. </p><div class="example"><a id="idm5895"></a><p class="title"><strong>Example 11.1. Building the Sample Code</strong></p><div class="example-contents"><pre class="screen"> <code class="prompt">$ </code><strong class="userinput"><code>../doc/grep_sample_code ../doc/userguide.xml > sample.c</code></strong> <code class="prompt">$ </code><strong class="userinput"><code>cc -I /usr/local/include -Wl,-L/usr/local/lib -Wl,-R/usr/local/lib sample.c -lsybdb -o sample</code></strong></pre></div></div><p><br class="example-break" /> where <code class="filename">/usr/local/include</code> and <code class="filename">/usr/local/lib</code> are respectively the locations of your header files and libraries.</p><p>We now proceed to the code proper.</p><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.include"></a>Header files</h3></div></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p>We need two header files to use <code class="systemitem">DB-Library</code>. 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></div><div class="example"><a id="e.g.samplecode.dblib.include"></a><p class="title"><strong>Example 11.2. Sample Code: <code class="systemitem">DB-Library</code> header files</strong></p><div class="example-contents"><pre class="programlisting"> #include <stdio.h> #include <stdlib.h> #include <string.h> #include <assert.h> #include <errno.h> #include <unistd.h> #include <libgen.h> #include <sybfront.h> <em class="lineannotation"><span class="lineannotation">/* <code class="filename">sybfront.h</code> always comes first */</span></em> #include <sybdb.h> <em class="lineannotation"><span class="lineannotation">/* <code class="filename">sybdb.h</code> is the only other file you need */</span></em> int err_handler(DBPROCESS*, int, int, int, char*, char*); int msg_handler(DBPROCESS*, DBINT, int, int, char*, char*, char*, int); </pre></div></div><p><br class="example-break" /></p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.prolog"></a>Prolog</h3></div></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p>Nothing special here. Collect the command line parameters. We do this with the standard <code class="function">getopts(3)</code> function. Cf. <span class="command"><strong>man 3 getopts</strong></span> for details.</p></div><div class="example"><a id="e.g.samplecode.dblib.prolog"></a><p class="title"><strong>Example 11.3. Sample Code: <code class="systemitem">DB-Library</code> prolog</strong></p><div class="example-contents"><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 id="samplecode.init.loginrec"></a><span><img src="images/callouts/1.png" alt="1" border="0" /></span> DBPROCESS *dbproc; <a id="samplecode.init.dbprocess"></a><span><img src="images/callouts/2.png" alt="2" border="0" /></span> RETCODE erc; <a id="samplecode.init.retcode"></a><span><img src="images/callouts/3.png" alt="3" border="0" /></span> 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 && options.username && options.password)) { fprintf(stderr, syntax); exit(1); } </pre></div></div><p><br class="example-break" /></p><div class="calloutlist"><p class="title"><strong>Prolog Notes</strong></p><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.init.loginrec"><span><img src="images/callouts/1.png" alt="1" border="0" /></span></a> </p></td><td valign="top" align="left"><p><span class="symbol">LOGINREC</span> is a structure that describes the client. It's passed to the server at connect time.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.init.dbprocess"><span><img src="images/callouts/2.png" alt="2" border="0" /></span></a> </p></td><td valign="top" align="left"><p><span class="symbol">DBPROCESS</span> is a structure that describes the connection. It is returned by <code class="function">dbopen()</code>.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.init.retcode"><span><img src="images/callouts/3.png" alt="3" border="0" /></span></a> </p></td><td valign="top" align="left"><p><span class="symbol">RETCODE</span> is the most common return code type for <code class="systemitem">DB-Library</code> functions.</p></td></tr></table></div></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.init"></a>Initialize</h3></div></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p>Initialize the library. Create and populate a <span class="symbol">LOGINREC</span> record.</p></div><div class="example"><a id="e.g.samplecode.dblib.Initialize"></a><p class="title"><strong>Example 11.4. Sample Code: <code class="systemitem">DB-Library</code> Initialize</strong></p><div class="example-contents"><pre class="programlisting"> <a id="samplecode.init.dbinit"></a><span><img src="images/callouts/1.png" alt="1" border="0" /></span> if (dbinit() == FAIL) { fprintf(stderr, "%s:%d: dbinit() failed\n", options.appname, __LINE__); exit(1); } <a id="samplecode.init.handlers"></a><span><img src="images/callouts/2.png" alt="2" border="0" /></span> dberrhandle(err_handler); dbmsghandle(msg_handler); <a id="samplecode.init.login"></a><span><img src="images/callouts/3.png" alt="3" border="0" /></span> if ((login = dblogin()) == NULL) { fprintf(stderr, "%s:%d: unable to allocate login structure\n", options.appname, __LINE__); exit(1); } <a id="samplecode.init.login.populate"></a><span><img src="images/callouts/4.png" alt="4" border="0" /></span> DBSETLUSER(login, options.username); DBSETLPWD(login, options.password); </pre></div></div><p><br class="example-break" /> </p><div class="calloutlist"><p class="title"><strong>Initialization Notes</strong></p><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.init.dbinit"><span><img src="images/callouts/1.png" alt="1" border="0" /></span></a> </p></td><td valign="top" align="left"><p><span class="emphasis"><em>Always</em></span> make <code class="function">dbinit()</code> the first <code class="systemitem">DB-Library</code> call.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.init.handlers"><span><img src="images/callouts/2.png" alt="2" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Install the error- and mesage-handlers right away. They're explained in more detail later.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.init.login"><span><img src="images/callouts/3.png" alt="3" border="0" /></span></a> </p></td><td valign="top" align="left"><p><code class="function">dblogin()</code> almost never fails. But check! No point in trying to use a null pointer.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.init.login.populate"><span><img src="images/callouts/4.png" alt="4" border="0" /></span></a> </p></td><td valign="top" align="left"><p>The <span class="symbol">LOGIN</span> record isn't directly accessible. It's populated via macros like these. There are other fields, but these two are essential. Look for <span class="symbol">SETLsomething</span> in the documentation.</p></td></tr></table></div></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.connect"></a>Connect to the server</h3></div></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p><code class="function">dbopen()</code> forms a connection with the server. We pass our <span class="symbol">LOGINREC</span> 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></div><div class="example"><a id="e.g.samplecode.dblib.Connect"></a><p class="title"><strong>Example 11.5. Sample Code: <code class="systemitem">DB-Library</code> Connect to the server</strong></p><div class="example-contents"><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 && (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></div><p><br class="example-break" /></p></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.query"></a>Send a query</h3></div></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p><code class="systemitem">DB-Library</code> maintains a <em class="firstterm">command buffer</em> to hold the SQL to be sent to the server. Two functions — <code class="function">dbcmd()</code> and <code class="function">dbfcmd()</code> — 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></div><div class="example"><a id="e.g.samplecode.dblib.send"></a><p class="title"><strong>Example 11.6. Sample Code: <code class="systemitem">DB-Library</code> Send a query</strong></p><div class="example-contents"><pre class="programlisting"> for (i=0; i < 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 id="samplecode.query.dbfcmd"></a><span><img src="images/callouts/1.png" alt="1" border="0" /></span> } } printf("\n"); if ((erc = dbsqlexec(dbproc)) == FAIL) { fprintf(stderr, "%s:%d: dbsqlexec() failed\n", options.appname, __LINE__); exit(1); <a id="samplecode.query.exec"></a><span><img src="images/callouts/2.png" alt="2" border="0" /></span> } </pre></div></div><p><br class="example-break" /> </p><div class="calloutlist"><p class="title"><strong>Initialization Notes</strong></p><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.query.dbfcmd"><span><img src="images/callouts/1.png" alt="1" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Failure at this juncture is rare. The library is merely allocating memory to hold the SQL.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.query.exec"><span><img src="images/callouts/2.png" alt="2" border="0" /></span></a> </p></td><td valign="top" align="left"><p><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.</p></td></tr></table></div><p> <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"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.results"></a>Fetch Results</h3></div></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p>A query may produce zero, one, or more results. The application normally provides buffers to <code class="systemitem">DB-Library</code> to fill, and iterates over the results a row (and column) at a time.</p></div><h4><a id="samplecode.results.kinds.of.results"></a>Kinds of Results</h4><p><em class="firstterm">Results</em> is a special term: it means more than rows or no rows. To <span class="emphasis"><em>process the results</em></span> means to gather the data returned by the server into the application's variables. </p><div class="table"><a id="tab.kinds.of.results"></a><p class="title"><strong>Table 11.4. Kinds of Results</strong></p><div class="table-contents"><table class="table" summary="Kinds of Results" border="1"><colgroup><col align="left" class="type" /><col align="left" class="meta" /><col align="left" class="reg" /><col align="left" class="comp" /><col align="left" class="ret" /><col align="left" class="eg" /></colgroup><thead><tr><th align="left">Type</th><th align="left">Metadata</th><th align="left">Regular Rows</th><th align="left">Compute Rows</th><th align="left">Return Status</th><th align="left">Example SQL</th></tr></thead><tbody><tr><td align="left">None</td><td align="left">None</td><td align="left">None</td><td align="left">None</td><td align="left">None</td><td align="left">Any <span class="symbol">INSERT</span>, <span class="symbol">UPDATE</span>, or <span class="symbol">DELETE</span> statement </td></tr><tr><td align="left">Empty</td><td align="left">1 set</td><td align="left">None</td><td align="left">0 or more</td><td align="left">None</td><td align="left"><span class="symbol">SELECT name FROM systypes WHERE 0 = 1</span></td></tr><tr><td align="left">Simple </td><td align="left">1 set </td><td align="left">0 or more </td><td align="left">None </td><td align="left">None </td><td align="left"><strong class="userinput"><code>SELECT name FROM sysobjects</code></strong> </td></tr><tr><td align="left">Complex </td><td align="left">2 or more </td><td align="left">0 or more </td><td align="left">1 or more </td><td align="left">None </td><td align="left"><strong class="userinput"><code>SELECT name FROM sysobjects COMPUTE COUNT(name)</code></strong> </td></tr><tr><td align="left">Stored Procedure </td><td align="left">0 or more </td><td align="left">0 or more </td><td align="left">0 or more </td><td align="left">1 or more</td><td align="left"><strong class="userinput"><code>EXEC sp_help sysobjects</code></strong> </td></tr></tbody></table></div></div><p><br class="table-break" /></p><p>As the above table shows, results can comprise ordinary rows and <em class="firstterm">compute rows</em> (resulting from a <span class="symbol">COMPUTE</span> clause). Stored procedures may of course contain multiple SQL statements, some of which may be <span class="symbol">SELECT</span> statements and might include <span class="symbol">COMPUTE</span> clauses. In addition, they generate a <em class="firstterm">return status</em> (with a <span class="symbol">RETURN</span> statement or else automatically) and perhaps <span class="symbol">OUTPUT</span> parameters.</p><h4><a id="samplecode.results.metadata.and.data"></a>Data and Metadata</h4><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 <em class="firstterm">metadata</em>. The server always returns metadata before any data (even for a <span class="symbol">SELECT</span> statement that produced no rows).</p><p> </p><div class="table"><a id="tab.result.fetching.functions"></a><p class="title"><strong>Table 11.5. Result-fetching functions</strong></p><div class="table-contents"><table class="table" summary="Result-fetching functions" border="1"><colgroup><col align="left" class="func" /><col align="left" class="type" /><col align="left" class="ret" /><col align="left" class="etc" /></colgroup><thead><tr><th align="left">Function</th><th align="left">Fetches</th><th align="left">Returns</th><th align="left">Comment</th></tr></thead><tbody><tr><td align="left"><code class="function">dbresults()</code></td><td align="left">metadata</td><td align="left"><span class="symbol">SUCCEED</span>, <span class="symbol">FAIL</span> or, <span class="symbol">NO_MORE_RESULTS</span>. </td><td align="left"><span class="symbol">SUCCEED</span> 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 align="left"><code class="function">dbnextrow()</code></td><td align="left">data</td><td align="left"> <span class="symbol">REG_ROW</span>, <em class="firstterm">compute_id</em>, <span class="symbol">NO_MORE_ROWS</span>, <span class="symbol">BUF_FULL</span>, or <span class="symbol">FAIL</span>. </td><td align="left">Places fetched data into bound columns, if any. </td></tr></tbody></table></div></div><p><br class="table-break" /></p><h4><a id="samplecode.results.binding"></a>Binding</h4><p>Each time <span class="symbol">dbresults()</span> returns <span class="symbol">SUCCEED</span>, there is something to retrieve. <code class="systemitem">DB-Library</code> 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 <em class="firstterm">binding</em>, and those that return the data in <span class="quote">“<span class="quote">native</span>”</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">“<span class="quote">native</span>”</span> form, 4 bytes for an <span class="symbol">INT</span>, 8 bytes for a <span class="symbol">DATETIME</span> 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 <span class="symbol">C double</span>. 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"><em>bind a column</em></span> is to provide a buffer to <code class="systemitem">DB-Library</code> for it to fill, and indicate which datatype the buffer is meant to hold. <a href="#ftn.idm6152" class="footnote" id="idm6152"><sup class="footnote">[29]</sup></a></p><p>It may be well to pause here to observe the three ways a datatype is described in a <code class="systemitem">DB-Library</code> program. </p><div class="variablelist"><a id="list.datatypes"></a><p class="title"><strong><code class="systemitem">DB-Library</code> Datatype Descriptors</strong></p><dl class="variablelist"><dt><span class="term">Sever Datatype</span></dt><dd><p>Describes the data as an abstract type, not representing any particular kind of storage. <span class="symbol">SYBREAL</span>, for example, doesn't imply any particular arrangement of bits; it just means <span class="quote">“<span class="quote">a floating-point datatype corresponding to the <span class="symbol">T-SQL REAL</span> type on the server.</span>”</span> These all begin with <span class="symbol">SYB</span>, e.g. <span class="symbol">SYBINT4</span>.</p></dd><dt><span class="term">Program Variable Datatype</span></dt><dd><p>Defines a <span class="symbol">C</span> variable in a machine-independent way. Because a <span class="symbol">C</span> defines its <span class="symbol">int</span> type according the CPU architecture, it may have 2, 4, 8, or some other number of bytes. A <span class="symbol">DBINT</span> on the other hand, is guaranteed to be 4 bytes and, as such, assuredly will hold any value returned by the server from a <span class="symbol">T-SQL INT</span> column. These all begin with <span class="symbol">DB</span>, e.g. <span class="symbol">DBREAL</span>.</p></dd><dt><span class="term">Bind Type</span></dt><dd><p>Prescribes a conversion operation. Indicates to <code class="function">dbbind()</code> the <span class="emphasis"><em>Program Variable Datatype</em></span> defined by the target buffer. Sybase and Microsoft call this the <span class="quote">“<span class="quote">vartype</span>”</span>. These all <span class="emphasis"><em>end</em></span> with <span class="symbol">BIND</span>, e.g. <span class="symbol">STRINGBIND</span>.</p></dd></dl></div><p>Typically it's convenient to have <code class="systemitem">DB-Library</code> 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><h4><a id="samplecode.results.fetching.data"></a>Fetching Data</h4><p> </p><div class="table"><a id="tab.data.fetching.functions"></a><p class="title"><strong>Table 11.6. Data-fetching functions</strong></p><div class="table-contents"><table class="table" summary="Data-fetching functions" border="1"><colgroup><col align="left" class="type" /><col align="left" class="reg" /><col align="left" class="comp" /><col align="left" class="ret" /><col align="left" class="out" /></colgroup><thead><tr><th align="left">Type</th><th align="left">Regular rows</th><th align="left">Compute rows</th><th align="left">Return status</th><th align="left"><span class="symbol">OUTPUT</span> parameters</th></tr></thead><tbody><tr><td align="left">Meta </td><td align="left"><code class="function">dbnumcols()</code> </td><td align="left"> <code class="function">dbnumcompute()</code>, <code class="function">dbnumalts()</code>, <code class="function">dbaltop()</code>, <code class="function">dbbylist()</code> </td><td align="left"><code class="function">dbhasretstatus()</code> </td><td align="left"><code class="function">dbnumrets()</code> </td></tr><tr><td align="left">Binding </td><td align="left"><code class="function">dbbind()</code>, <code class="function">dbnullbind()</code> </td><td align="left"> <code class="function">dbaltbind()</code>, <code class="function">dbanullbind()</code> </td><td align="left"><code class="function">dbretstatus()</code> </td><td align="left">none </td></tr><tr><td align="left">Native </td><td align="left"><code class="function">dbdatlen()</code>, <code class="function">dbdata()</code> </td><td align="left"> <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 align="left">none </td><td align="left"> <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></div><p><br class="table-break" /></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> </p><div class="table"><a id="tab.putting.it.all.together"></a><p class="title"><strong>Table 11.7. Putting it all together </strong></p><div class="table-contents"><table class="table" summary="Putting it all together " border="1"><colgroup><col align="left" class="step" /><col align="left" class="func" /><col align="left" class="once" /><col align="left" class="freq" /></colgroup><thead><tr><th align="left">Step </th><th align="left">Function </th><th align="left">Once Per </th><th align="left">Many Times Per </th></tr></thead><tbody><tr><td align="left">Query </td><td align="left"><code class="function">dbsqlexec()</code> </td><td align="left">Query</td><td align="left">Program</td></tr><tr><td align="left">Fetch metadata </td><td align="left"><code class="function">dbresults()</code> </td><td align="left">SQL statement </td><td align="left">Query </td></tr><tr><td align="left">Prepare variables </td><td align="left"><code class="function">dbbind()</code> </td><td align="left">Column</td><td align="left">Statement</td></tr><tr><td align="left">Fetch regular data </td><td align="left"><code class="function">dbnextrow()</code> </td><td align="left">Row </td><td align="left">Statement </td></tr><tr><td align="left">Fetch compute data </td><td align="left"><code class="function">dbnextrow()</code> </td><td align="left">Compute column </td><td align="left">Statement </td></tr><tr><td align="left">Fetch output parameters </td><td align="left"><code class="function">dbretdata()</code> </td><td align="left">output parameter </td><td align="left">Stored procedure </td></tr><tr><td align="left">Fetch return status </td><td align="left"><code class="function">dbretstatus()</code> </td><td align="left">Stored procedure </td><td align="left">Program </td></tr></tbody></table></div></div><p><br class="table-break" /></p><div class="important" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Important: Fetch All Rows!"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Important]" src="../images/important.gif" /></td><th align="left">Fetch All Rows!</th></tr><tr><td align="left" valign="top"><div class="sidebar"><div class="titlepage"><div><div><p class="title"><strong></strong></p></div></div></div><p><code class="systemitem">DB-Library</code> doesn't insist every column — or even any column — be bound or otherwise retrieved into the application's variables. There is, however, one absolutely <span class="emphasis"><em>crucial, inflexible, unalterable</em></span> requirement: the application must <span class="emphasis"><em>process all rows produced by the query</em></span>. Before the <span class="symbol">DBPROCESS</span> 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"><em> fetch all rows</em></span>.</p></div></td></tr></table></div><p>Now, at last, some sample code that fetches data. In the interest of simplicity, we don't bind anything except regular rows.</p><div class="example"><a id="e.g.samplecode.dblib.fetch"></a><p class="title"><strong>Example 11.7. Sample Code: <code class="systemitem">DB-Library</code> Fetch Results</strong></p><div class="example-contents"><pre class="programlisting"> while ((erc = dbresults(dbproc)) != NO_MORE_RESULTS) { struct COL <a id="samplecode.results.dbresults"></a><span><img src="images/callouts/1.png" alt="1" border="0" /></span> { 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 < ncols; pcol++) { int c = pcol - columns + 1; pcol->name = dbcolname(dbproc, c); <a id="samplecode.results.c"></a><span><img src="images/callouts/2.png" alt="2" border="0" /></span> pcol->type = dbcoltype(dbproc, c); pcol->size = dbcollen(dbproc, c); if (SYBCHAR != pcol->type) { <a id="samplecode.results.dbcollen"></a><span><img src="images/callouts/3.png" alt="3" border="0" /></span> pcol->size = dbprcollen(dbproc, c); if (pcol->size > 255) pcol->size = 255; } printf("%*s ", pcol->size, pcol->name); if ((pcol->buffer = calloc(1, pcol->size + 1)) == NULL){ perror(NULL); exit(1); } erc = dbbind(dbproc, c, NTBSTRINGBIND, <a id="samplecode.results.dbbind"></a><span><img src="images/callouts/4.png" alt="4" border="0" /></span> pcol->size+1, (BYTE*)pcol->buffer); if (erc == FAIL) { fprintf(stderr, "%s:%d: dbbind(%d) failed\n", options.appname, __LINE__, c); exit(1); } erc = dbnullbind(dbproc, c, &pcol->status); <a id="samplecode.results.dbnullbind"></a><span><img src="images/callouts/5.png" alt="5" border="0" /></span> 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 id="samplecode.results.dbnextrow"></a><span><img src="images/callouts/6.png" alt="6" border="0" /></span> switch (row_code) { case REG_ROW: for (pcol=columns; pcol - columns < ncols; pcol++) { char *buffer = pcol->status == -1? "NULL" : pcol->buffer; printf("%*s ", pcol->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 id="samplecode.results.computeid"></a><span><img src="images/callouts/7.png" alt="7" border="0" /></span> printf("Data for computeid %d ignored\n", row_code); } } /* free metadata and data buffers */ for (pcol=columns; pcol - columns < ncols; pcol++) { free(pcol->buffer); } free(columns); /* * Get row count, if available. */ if (DBCOUNT(dbproc) > -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><p><br class="example-break" /> </p><div class="calloutlist"><a id="co.fetching"></a><p class="title"><strong>Data-fetching Notes</strong></p><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.results.dbresults"><span><img src="images/callouts/1.png" alt="1" border="0" /></span></a> </p></td><td valign="top" align="left"><p>As soon as <code class="function">dbresults()</code> reports <span class="symbol">SUCCESS</span>, the row's metadata are available.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.results.c"><span><img src="images/callouts/2.png" alt="2" border="0" /></span></a> </p></td><td valign="top" align="left"><p><code class="systemitem">DB-Library</code> columns start with 1.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.results.dbcollen"><span><img src="images/callouts/3.png" alt="3" border="0" /></span></a> </p></td><td valign="top" align="left"><p><code class="function">dbcollen()</code> returns the sizeof the native data (e.g. 4 bytes for a T-SQL <span class="symbol">INT</span>). We'll use <code class="function">dbbind()</code> to convert everything to strings. If the column is <span class="symbol">[VAR]CHAR</span>, we want the column's defined size, otherwise we want its maximum size when represented as a string.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.results.dbbind"><span><img src="images/callouts/4.png" alt="4" border="0" /></span></a> </p></td><td valign="top" align="left"><p><span class="symbol">NTBSTRINGBIND</span> null-terminates the character array for us. <span class="quote">“<span class="quote">NTB</span>”</span> might perhaps stand for <span class="quote">“<span class="quote">null terminating byte</span>”</span>.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.results.dbnullbind"><span><img src="images/callouts/5.png" alt="5" border="0" /></span></a> </p></td><td valign="top" align="left"><p>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.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.results.dbnextrow"><span><img src="images/callouts/6.png" alt="6" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Each time <code class="function">dbnextrow()</code> returns <span class="symbol">REG_ROW</span>, it has filled the bound buffers with the converted values for the row.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.results.computeid"><span><img src="images/callouts/7.png" alt="7" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Computed rows are left as an exercise to the reader.</p></td></tr></table></div></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.errors"></a>Messages and Errors</h3></div></div></div><div class="abstract"><p class="title"><strong>Abstract</strong></p><p>Errors may originate on the server or in the library itself. The former are known as <em class="firstterm">messages</em> (because they are: they arrive as messages from the server); the latter are termed <em class="firstterm">errors</em>. Their handling is a little intimidating. It requires writing and installing a callback function (whose parameters are predefined by <code class="systemitem">DB-Library</code>), and thinking about how to handle different types of errors.</p></div><div class="variablelist"><p class="title"><strong>Kinds of Errors</strong></p><dl class="variablelist"><dt><span class="term">Messages</span></dt><dd><p><span class="emphasis"><em>Messages</em></span> arise because the server has something to say. <a href="#ftn.idm6377" class="footnote" id="idm6377"><sup class="footnote">[30]</sup></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><span class="term">Errors</span></dt><dd><p><span class="emphasis"><em>Errors</em></span> arise either because the application has misused <code class="systemitem">DB-Library</code> in some way — say, passed a NULL <span class="symbol">DBPROCESS</span> pointer or tried to issue a query while results were pending — 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 <code class="systemitem">DB-Library</code> 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><div class="example"><a id="e.g.samplecode.dblib.errors"></a><p class="title"><strong>Example 11.8. Sample Code: <code class="systemitem">DB-Library</code> Error and Message handlers</strong></p><div class="example-contents"><pre class="programlisting"> int msg_handler(DBPROCESS *dbproc, DBINT msgno, int msgstate, int severity, char *msgtext, char *srvname, char *procname, int line) { <a id="samplecode.errors.msghandler.args"></a><span><img src="images/callouts/1.png" alt="1" border="0" /></span> enum {changed_database = 5701, changed_language = 5703 }; <a id="samplecode.errors.msghandler.suppress"></a><span><img src="images/callouts/2.png" alt="2" border="0" /></span> if (msgno == changed_database || msgno == changed_language) return 0; if (msgno > 0) { fprintf(stderr, "Msg %ld, Level %d, State %d\n", (long) msgno, severity, msgstate); if (strlen(srvname) > 0) fprintf(stderr, "Server '%s', ", srvname); if (strlen(procname) > 0) fprintf(stderr, "Procedure '%s', ", procname); if (line > 0) fprintf(stderr, "Line %d", line); fprintf(stderr, "\n\t"); } fprintf(stderr, "%s\n", msgtext); if (severity > 10) { <a id="samplecode.errors.msghandler.severity"></a><span><img src="images/callouts/3.png" alt="3" border="0" /></span> fprintf(stderr, "%s: error: severity %d > 10, exiting\n", options.appname, severity); exit(severity); } return 0; <a id="samplecode.errors.msghandler.return"></a><span><img src="images/callouts/4.png" alt="4" border="0" /></span> } int err_handler(DBPROCESS * dbproc, int severity, int dberr, int oserr, char *dberrstr, char *oserrstr) { <a id="samplecode.errors.errhandler.args"></a><span><img src="images/callouts/5.png" alt="5" border="0" /></span> if (dberr) { <a id="samplecode.errors.errhandler.msgs"></a><span><img src="images/callouts/6.png" alt="6" border="0" /></span> 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 id="samplecode.errors.errhandler.return"></a><span><img src="images/callouts/7.png" alt="7" border="0" /></span> } </pre></div></div><p><br class="example-break" /> </p><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Note"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Note]" src="../images/note.gif" /></td><th align="left">Note</th></tr><tr><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><p> </p><div class="calloutlist"><p class="title"><strong>Error Handling Notes</strong></p><table border="0" summary="Callout list"><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.errors.msghandler.args"><span><img src="images/callouts/1.png" alt="1" border="0" /></span></a> </p></td><td valign="top" align="left"><p>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 <span class="symbol">C</span> compiler that the address of your function is of the type accepted by <code class="function">dbmsghandle()</code>. <a href="#ftn.idm6412" class="footnote" id="idm6412"><sup class="footnote">[31]</sup></a></p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.errors.msghandler.suppress"><span><img src="images/callouts/2.png" alt="2" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Some messages don't convey much, as though the server gets lonely sometimes. You're not obliged to print every one.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.errors.msghandler.severity"><span><img src="images/callouts/3.png" alt="3" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Severities are defined in the <span class="emphasis"><em>server</em></span> documentation, and can be set by the <span class="symbol">T-SQL RAISERROR</span> statement.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.errors.msghandler.return"><span><img src="images/callouts/4.png" alt="4" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Message handlers <span class="emphasis"><em>always and only ever</em></span> return zero.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.errors.errhandler.args"><span><img src="images/callouts/5.png" alt="5" border="0" /></span></a> </p></td><td valign="top" align="left"><p>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 <span class="symbol">C</span> compiler that the address of your function is of the type accepted by <code class="function">dberrhandle()</code>. <a href="#ftn.idm6427" class="footnote" id="idm6427"><sup class="footnote">[32]</sup></a></p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.errors.errhandler.msgs"><span><img src="images/callouts/6.png" alt="6" border="0" /></span></a> </p></td><td valign="top" align="left"><p>Some messages are so severe they provoke <code class="systemitem">DB-Library</code> into calling the error handler, too! If you have both installed — and of course you do, right? — then you can skip those lacking an error number.</p></td></tr><tr><td width="5%" valign="top" align="left"><p><a href="#samplecode.errors.errhandler.return"><span><img src="images/callouts/7.png" alt="7" border="0" /></span></a> </p></td><td valign="top" align="left"><p>While <span class="symbol">INT_CANCEL</span> 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 <code class="systemitem">DB-Library</code> keeps retrying timeout errors. See the documentation for details.</p></td></tr></table></div><div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Note"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Note]" src="../images/note.gif" /></td><th align="left">Note</th></tr><tr><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"><em>still</em></span> an error and usually the best that can happen is that the function will return <span class="symbol">FAIL</span>. The exception is timeout conditions, when the handler can stave off failure by requesting retries.</p></td></tr></table></div><p>You may be asking yourself, <span class="quote">“<span class="quote">OK, fine, I can print the error message. But what if I want to communicate something back to the line in my program where the error occurred? How to do that?</span>”</span> First of all, remember the calling function — that's your application — will learn of an error from the return code. If it needs more detail, though, there are two ways to pass it. </p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>Set a global variable.</p></li><li class="listitem"><p>Use <code class="function">setuserdata()</code> and <code class="function">getuserdata()</code>.</p></li></ol></div><p> </p><div class="tip" style="margin-left: 0.5in; margin-right: 0.5in;"><table border="0" summary="Tip"><tr><td rowspan="2" align="center" valign="top" width="25"><img alt="[Tip]" src="../images/tip.gif" /></td><th align="left">Tip</th></tr><tr><td align="left" valign="top"><p>If your application is written in <span class="symbol">C++</span>, you may be tempted to use <code class="function">throw()</code>. Don't! Your handler is a <span class="symbol">C</span> function and, more important, <span class="emphasis"><em>it's an extension of <code class="systemitem">DB-Library</code></em></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 <code class="systemitem">DB-Library</code>'s stack. Your application will be unuseable at that point, if it doesn't cause a segment fault.</p></td></tr></table></div></div><div class="sect2"><div class="titlepage"><div><div><h3 class="title"><a id="samplecode.wrapup"></a>Last Remarks</h3></div></div></div><p>We've reached the end of our <code class="systemitem">DB-Library</code> tour. The almost 300 lines of <span class="symbol">C</span> above constitute program with these features: </p><div class="itemizedlist"><p class="title"><strong> Sample Code features</strong></p><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>Accepts command-line parameters and SQL.</p></li><li class="listitem"><p>Checks for errors and server messages.</p></li><li class="listitem"><p>Processes any number of results..</p></li><li class="listitem"><p>Prints results in columns of suitable widths.</p></li></ul></div><p> There are things it doesn't do, in the name of simplicity. </p><div class="itemizedlist"><p class="title"><strong> Sample Code nonfeatures</strong></p><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>No BCP (bulk copy) mode</p></li><li class="listitem"><p>No RPC (remote procedure call) mode, preventing it from retrieving output parameters.</p></li></ul></div><p> Your humble author hopes you found it worthwhile. Happy Hacking.</p></div><div class="footnotes"><br /><hr style="width:100; text-align:left;margin-left: 0" /><div id="ftn.idm6152" class="footnote"><p><a href="#idm6152" class="para"><sup class="para">[29] </sup></a>This is the sort of thing <span class="symbol">C++</span>'s type system does so much better</p></div><div id="ftn.idm6377" class="footnote"><p><a href="#idm6377" class="para"><sup class="para">[30] </sup></a>Just one more way in which databases differ from files.</p></div><div id="ftn.idm6412" class="footnote"><p><a href="#idm6412" class="para"><sup class="para">[31] </sup></a>Back in K&R days, that wasn't such a problem. But there were other problems, some much worse.</p></div><div id="ftn.idm6427" class="footnote"><p><a href="#idm6427" class="para"><sup class="para">[32] </sup></a>If that advice sounds familiar, it's because it bears repeating.</p></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="odbc.api.summary.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="programming.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="acknowledgments.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ODBC API Implementation Summary </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> Chapter 12. Acknowledgments</td></tr></table></div></body></html>