1<html> 2<head> 3 <title>Programming with SQL Relay using the C API</title> 4 <link rel="stylesheet" href="../css/styles.css"> 5</head> 6<body> 7<h1>Programming with SQL Relay using the C API</h1> 8 9<ul> 10 <li><a href="#compiling">Compiling an SQL Relay Client Program</a></li> 11 <li><a href="#session">Establishing a Session</a></li> 12 <li><a href="#query">Executing Queries</a></li> 13 <li><a href="#commit">Commits and Rollbacks</a></li> 14 <li><a href="#temptables">Temporary Tables</a></li> 15 <li><a href="#errors">Catching Errors</a></li> 16 <li><a href="#bindvars">Substitution and Bind Variables</a></li> 17 <li><a href="#rebinding">Re-Binding and Re-Executing</a></li> 18 <li><a href="#fields">Accessing Fields in the Result Set</a></li> 19 <li><a href="#largeresultsets">Dealing With Large Result Set</a></li> 20 <li><a href="#cursors">Cursors</a></li> 21 <li><a href="#columns">Getting Column Information</a></li> 22 <li><a href="#storedprocedures">Stored Procedures</a></li> 23 <li><a href="#caching">Caching The Result Set</a></li> 24 <li><a href="#suspending">Suspending and Resuming Sessions</a></li> 25 <li><a href="#lastinsertid">Getting the Last Insert ID</a></li> 26</ul> 27 28<br/><a name="compiling"/><h2>Compiling an SQL Relay Client Program</h2> 29 30<p>When writing an SQL Relay client program using the C API, you need to 31include the sqlrclientwrapper.h file.</p> 32 33<blockquote> 34<!-- Generator: GNU source-highlight 3.1.8 35by Lorenzo Bettini 36http://www.lorenzobettini.it 37http://www.gnu.org/software/src-highlite --> 38<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 39</tt></pre> 40 41</blockquote> 42<p>You'll also need to link against the sqlrclientwrapper, sqlrclient and 43rudiments libraries.</p> 44 45<p>On Linux and Unix, the header files are usually found under 46/usr/local/firstworks/include and the libraries are usually found in 47/usr/local/firstworks/lib.</p> 48 49<p>On Windows, the header files are usually found under C:\Program Files\Firstworks\include, the .lib files are usually found under C:\Program Files\Firstworks\lib and the .dll files are usually found under C:\Program Files\Firstworks\bin.</p> 50 51<p>The command to compile your .c file to object code will look something like 52this (assuming you're using the GNU C compiler on Linux or Unix):</p> 53 54<blockquote> 55<!-- Generator: GNU source-highlight 3.1.8 56by Lorenzo Bettini 57http://www.lorenzobettini.it 58http://www.gnu.org/software/src-highlite --> 59<pre><tt>gcc <font color="#990000">-</font>I<font color="#990000">/</font>usr<font color="#990000">/</font>local<font color="#990000">/</font>firstworks<font color="#990000">/</font>include <font color="#990000">-</font><font color="#008080">c</font> myprogram<font color="#990000">.</font>c</tt></pre> 60 61</blockquote> 62<p>The command to compile your .o file to an executable will look something like 63this (assuming you're using the GNU C++ compiler on Linux or Unix):</p> 64 65<blockquote> 66<!-- Generator: GNU source-highlight 3.1.8 67by Lorenzo Bettini 68http://www.lorenzobettini.it 69http://www.gnu.org/software/src-highlite --> 70<pre><tt>g<font color="#990000">++</font> <font color="#990000">-</font>o <font color="#008080">myprogram</font> myprogram<font color="#990000">.</font>o <font color="#990000">-</font>L<font color="#990000">/</font>usr<font color="#990000">/</font>local<font color="#990000">/</font>firstworks<font color="#990000">/</font>lib <font color="#990000">-</font>lsqlrclientwrapper <font color="#990000">-</font>lsqlrclient <font color="#990000">-</font>lrudiments</tt></pre> 71 72</blockquote> 73<p>Note that g++ was used to link, not gcc. You could alternatively link using 74gcc like this:</p> 75 76<blockquote> 77<!-- Generator: GNU source-highlight 3.1.8 78by Lorenzo Bettini 79http://www.lorenzobettini.it 80http://www.gnu.org/software/src-highlite --> 81<pre><tt>gcc <font color="#990000">-</font>o <font color="#008080">myprogram</font> myprogram<font color="#990000">.</font>o <font color="#990000">-</font>L<font color="#990000">/</font>usr<font color="#990000">/</font>local<font color="#990000">/</font>firstworks<font color="#990000">/</font>lib <font color="#990000">-</font>lsqlrclientwrapper <font color="#990000">-</font>lsqlrclient <font color="#990000">-</font>lrudiments <font color="#990000">-</font>lstdc<font color="#990000">++</font></tt></pre> 82 83</blockquote> 84<p>When using the C API, it is important to compile the .c files to object code 85using the C compiler before linking them using the C++ compiler. 86Compiling/Linking in one step using the C++ compiler the will most likely fail 87as it will generate C++ style symbols for function calls which will not be 88resolved in the sqlrclientwrapper library since it contains C style function 89symbols. Compiling to object code using the C compiler as a seperate step 90ensures that C style symbols will be generated for function calls.</p> 91 92<a name="session"/><h2>Establishing a Session</h2> 93 94<p>To use SQL Relay, you have to identify the connection that you intend to 95use.</p> 96 97<blockquote> 98<!-- Generator: GNU source-highlight 3.1.8 99by Lorenzo Bettini 100http://www.lorenzobettini.it 101http://www.gnu.org/software/src-highlite --> 102<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 103<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 104 105<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 106 107 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 108 109 <font color="#990000">...</font> execute <font color="#008080">some</font> queries <font color="#990000">...</font> 110 111 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 112<font color="#FF0000">}</font> 113</tt></pre> 114 115</blockquote> 116<p>After calling the constructor, a session is established when the first 117query is run.</p> 118 119<p>For the duration of the session, the client occupies one of the database 120connections, so care should be taken to minimize the length of a 121session.</p> 122 123<p>Sessions can also be encrypted and authenticated using Kerberos/Active Directory or TLS/SSL. The sqlrcon_enableKerberos() function is provided to enable Kerberos/Active Directory encryption and authentication and the sqlrcon_enableTls() function is provided to enable TLS/SSL encryption and authentication. The sqlrcon_disableEncryption() function is provided to disable any previously enabled encryption.</p> 124 125<p>See the <a href="../api/c/html/group_sqlrclientwrapper.html">C API reference</a> for information about these functions and the SQL Relay Configuration Guide for more information about <a href="../admin/configguide.html#krb">Kerberos/Active Directory</a> and <a href="../admin/configguide.html#tls">TLS/SSL</a> configurations. In particular, note that user and password are not typically used when using Kerberos/AD.</p> 126 127<a name="query"/><h2>Executing Queries</h2> 128 129<p>Call sqlrcur_sendQuery() or sqlrcur_sendFileQuery() to run a query.</p> 130 131<blockquote> 132<!-- Generator: GNU source-highlight 3.1.8 133by Lorenzo Bettini 134http://www.lorenzobettini.it 135http://www.gnu.org/software/src-highlite --> 136<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 137<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 138 139<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 140 141 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 142 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 143 144 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 145 146 <font color="#990000">...</font> <b><font color="#0000FF">do</font></b> some stuff that takes a <font color="#008080">short</font> time <font color="#990000">...</font> 147 148 <b><font color="#000000">sqlrcur_sendFileQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"/usr/local/myprogram/sql"</font><font color="#990000">,</font><font color="#FF0000">"myquery.sql"</font><font color="#990000">);</font> 149 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 150 151 <font color="#990000">...</font> <b><font color="#0000FF">do</font></b> some stuff that takes a <font color="#008080">long</font> time <font color="#990000">...</font> 152 153 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_other_table"</font><font color="#990000">);</font> 154 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 155 156 <font color="#990000">...</font> process the <font color="#008080">result</font> set <font color="#990000">...</font> 157 158 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 159 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 160<font color="#FF0000">}</font> 161</tt></pre> 162 163</blockquote> 164<p>Note the call to sqlrcon_endSession() after the call to sqlrcur_sendFileQuery(). 165Since the program does some stuff that takes a long time between that query and 166the next, ending the session there allows another client an opportunity to use 167that database connection while your client is busy. The next call to 168sqlrcur_sendQuery() establishes another session. Since the program does some 169stuff that takes a short time between the first two queries, it's OK to leave 170the session open between them.</p> 171 172<a name="commit"/><h2>Commits and Rollbacks</h2> 173 174<p>If you need to execute a commit or rollback, you should use the 175sqlrcon_commit() and sqlrcon_rollback() functions rather than sending a "commit" 176or "rollback" query. There are two reasons for this. First, it's much more 177efficient to call the functions. Second, if you're writing code that can run on 178transactional or non-transactional databases, some non-transactional databases 179will throw errors if they receive a "commit" or "rollback" query, but by calling 180the sqlrcon_commit() and sqlrcon_rollback() functions you instruct the database 181connection daemon to call the commit and rollback API functions for that database 182rather than issuing them as queries. If the API's have no commit or rollback 183functions, the calls do nothing and the database throws no error.</p> 184 185<p>You can also turn Autocommit on or off with the sqlrcon_autoCommitOn() and 186sqlrcon_autoCommitOff() functions. When Autocommit is on, the database 187performs a commit after each successful DML or DDL query. When Autocommit is 188off, the database commits when the client instructs it to, or (by default) when 189a client disconnects. For databases that don't support Autocommit, 190sqlrcon_autoCommitOn() and sqlrcon_autoCommitOff() have no effect.</p> 191 192<a name="temptables"/><h2>Temporary Tables</h2> 193 194<p>Some databases support temporary tables. That is, tables which are 195automatically dropped or truncated when an application closes its connection 196to the database or when a transaction is committed or rolled back.</p> 197 198<p>For databases which drop or truncate tables when a transaction is committed 199or rolled back, temporary tables work naturally.</p> 200 201<p>However, for databases which drop or truncate tables when an application 202closes its connection to the database, there is an issue. Since SQL Relay 203maintains persistent database connections, when an application disconnects from 204SQL Relay, the connection between SQL Relay and the database remains, so 205the database does not know to drop or truncate the table. To remedy this 206situation, SQL Relay parses each query to see if it created a temporary table, 207keeps a list of temporary tables and drops (or truncates them) when the 208application disconnects from SQL Relay. Since each database has slightly 209different syntax for creating a temporary table, SQL Relay parses each query 210according to the rules for that database.</p> 211 212<p>In effect, temporary tables should work when an application connects to 213SQL Relay in the same manner that they would work if the application connected 214directly to the database.</p> 215 216<a name="errors"/><h2>Catching Errors</h2> 217 218<p>If your call to sqlrcur_sendQuery() or sqlrcur_sendFileQuery() returns a 0, the 219query failed. You can find out why by calling sqlrcur_errorMessage().</p> 220 221<blockquote> 222<!-- Generator: GNU source-highlight 3.1.8 223by Lorenzo Bettini 224http://www.lorenzobettini.it 225http://www.gnu.org/software/src-highlite --> 226<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 227<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 228 229<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 230 231 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 232 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 233 234 <b><font color="#0000FF">if</font></b> <font color="#990000">(!</font><b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_nonexistant_table"</font><font color="#990000">))</font> <font color="#FF0000">{</font> 235 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"%s</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_errorMessage</font></b><font color="#990000">(</font>cur<font color="#990000">));</font> 236 <font color="#FF0000">}</font> 237 238 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 239 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 240<font color="#FF0000">}</font> 241</tt></pre> 242 243</blockquote> 244<br/><a name="bindvars"/><h2>Substitution and Bind Variables</h2> 245 246<p>Programs rarely execute fixed queries. More often than not, some part 247of the query is dynamically generated. The SQL Relay API provides functions for 248making substitutions and binds in those queries.</p> 249 250<p>For a detailed discussion of substitutions and binds, see 251<a href="binds.html">this document</a>.</p> 252 253<p>Rather than just calling sendQuery() you call prepareQuery(), 254substitution(), inputBind() and executeQuery(). If you using queries 255stored in a file, you can call prepareFileQuery() instead of prepareQuery().</p> 256 257<p>When passing a floating point number in as a bind or substitution variable, 258you have to supply precision and scale for the number. See 259<a href="precisionscale.html">this page</a> for a discussion of precision and 260scale.</p> 261 262<blockquote> 263<!-- Generator: GNU source-highlight 3.1.8 264by Lorenzo Bettini 265http://www.lorenzobettini.it 266http://www.gnu.org/software/src-highlite --> 267<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 268 269<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 270 271 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 272 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 273 274 <b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from mytable $(whereclause)"</font><font color="#990000">)</font> 275 <b><font color="#000000">sqlrcur_substitution</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"whereclause"</font><font color="#990000">,</font><font color="#FF0000">"where stringcol=:stringval and integercol>:integerval and floatcol>floatval"</font><font color="#990000">);</font> 276 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"stringval"</font><font color="#990000">,</font><font color="#FF0000">"true"</font><font color="#990000">);</font> 277 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"integerval"</font><font color="#990000">,</font><font color="#993399">10</font><font color="#990000">);</font> 278 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"floatval"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 279 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 280 281 <font color="#990000">...</font> process the <font color="#008080">result</font> set <font color="#990000">...</font> 282 283 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 284 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 285<font color="#FF0000">}</font> 286</tt></pre> 287 288</blockquote> 289<p>If you are curious how many bind variables have been declared in a query, 290you can call countBindVariables() after preparing the query.</p> 291 292<p>If you're using a database with an embedded procedural language, you may 293want to retrieve data from function calls. To facilitate this, SQL Relay 294provides functions for defining and retrieving output bind variables.</p> 295 296<blockquote> 297<!-- Generator: GNU source-highlight 3.1.8 298by Lorenzo Bettini 299http://www.lorenzobettini.it 300http://www.gnu.org/software/src-highlite --> 301<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 302 303<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 304 305 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 306 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 307 308 <b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"begin :result1:=addTwoIntegers(:integer1,:integer2); :result2=addTwoFloats(:float1,:float2); :result3=convertToString(:integer3); end;"</font><font color="#990000">);</font> 309 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"integer1"</font><font color="#990000">,</font><font color="#993399">10</font><font color="#990000">);</font> 310 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"integer2"</font><font color="#990000">,</font><font color="#993399">20</font><font color="#990000">);</font> 311 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"float1"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 312 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"float2"</font><font color="#990000">,</font><font color="#993399">2.2</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 313 <b><font color="#000000">sqlrcur_inputBind</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"integer3"</font><font color="#990000">,</font><font color="#993399">30</font><font color="#990000">);</font> 314 <b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"result1"</font><font color="#990000">);</font> 315 <b><font color="#000000">sqlrcur_defineOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"result2"</font><font color="#990000">);</font> 316 <b><font color="#000000">sqlrcur_defineOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"result3"</font><font color="#990000">,</font><font color="#993399">100</font><font color="#990000">);</font> 317 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 318 <font color="#008080">int64_t</font> result1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"result1"</font><font color="#990000">);</font> 319 <font color="#009900">double</font> result2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"result2"</font><font color="#990000">);</font> 320 <font color="#009900">char</font> <font color="#990000">*</font>result3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"result3"</font><font color="#990000">);</font> 321 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 322 323 <font color="#990000">...</font> <b><font color="#0000FF">do</font></b> something with <font color="#008080">the</font> result <font color="#990000">...</font> 324 325 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 326 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 327<font color="#FF0000">}</font> 328</tt></pre> 329 330</blockquote> 331<p>The getOutputBindString() function returns a NULL value as an empty string. 332If you would it to come back as a NULL instead, you can call the 333getNullsAsNulls() function. To revert to the default behavior, you can 334call getNullsAsEmptyStrings().</p> 335 336<p>You can insert data into BLOB and CLOB columns using the inputBindBlob(), inputBindClob() functions.</p> 337 338<blockquote> 339<!-- Generator: GNU source-highlight 3.1.8 340by Lorenzo Bettini 341http://www.lorenzobettini.it 342http://www.gnu.org/software/src-highlite --> 343<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 344 345<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 346 347 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 348 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 349 350 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"create table images (image blob, description clob)"</font><font color="#990000">);</font> 351 352 <font color="#009900">unsigned</font> <font color="#009900">char</font> imagedata<font color="#990000">[</font><font color="#993399">40000</font><font color="#990000">];</font> 353 <font color="#009900">unsigned</font> <font color="#009900">long</font> imagelength<font color="#990000">;</font> 354 355 <font color="#990000">...</font> read an image from a file into imagedata and the length of the 356 file <font color="#008080">into</font> imagelength <font color="#990000">...</font> 357 358 <font color="#009900">unsigned</font> <font color="#009900">char</font> description<font color="#990000">[</font><font color="#993399">40000</font><font color="#990000">];</font> 359 <font color="#009900">unsigned</font> <font color="#009900">long</font> desclength<font color="#990000">;</font> 360 361 <font color="#990000">...</font> read a description from a file into description and the length of 362 the file <font color="#008080">into</font> desclength <font color="#990000">...</font> 363 364 <b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"insert into images values (:image,:desc)"</font><font color="#990000">);</font> 365 <b><font color="#000000">sqlrcur_inputBindBlob</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"image"</font><font color="#990000">,</font>imagedata<font color="#990000">,</font>imagelength<font color="#990000">);</font> 366 <b><font color="#000000">sqlrcur_inputBindClob</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"desc"</font><font color="#990000">,</font>description<font color="#990000">,</font>desclength<font color="#990000">);</font> 367 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 368 369 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 370 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 371<font color="#FF0000">}</font> 372</tt></pre> 373 374</blockquote> 375<p>Likewise, you can retreive BLOB or CLOB data using 376defineOutputBindBlob()/getOutputBindBlob() and 377defineOutputBindClob()/getOutputBindClob().</p> 378 379<blockquote> 380<!-- Generator: GNU source-highlight 3.1.8 381by Lorenzo Bettini 382http://www.lorenzobettini.it 383http://www.gnu.org/software/src-highlite --> 384<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 385 386<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 387 388 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 389 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 390 391 <b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"begin select image into :image from images; select description into :desc from images; end;"</font><font color="#990000">);</font> 392 <b><font color="#000000">sqlrcur_defineOutputBindBlob</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"image"</font><font color="#990000">);</font> 393 <b><font color="#000000">sqlrcur_defineOutputBindClob</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"desc"</font><font color="#990000">);</font> 394 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 395 396 <font color="#009900">char</font> <font color="#990000">*</font>image<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindBlob</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"image"</font><font color="#990000">);</font> 397 <font color="#009900">long</font> imagelength<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindLength</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"image"</font><font color="#990000">);</font> 398 399 <font color="#009900">char</font> <font color="#990000">*</font>desc<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindClob</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"desc"</font><font color="#990000">);</font> 400 <font color="#009900">char</font> <font color="#990000">*</font>desclength<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindLength</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"desc"</font><font color="#990000">);</font> 401 402 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 403 404 <font color="#990000">...</font> <b><font color="#0000FF">do</font></b> something with image <font color="#008080">and</font> desc <font color="#990000">...</font> 405 406 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 407 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 408<font color="#FF0000">}</font> 409</tt></pre> 410 411</blockquote> 412<p>Sometimes it's convenient to bind a bunch of variables that may or may not 413actually be in the query. For example, if you are building a web based 414application, it may be easy to just bind all the form variables/values from the 415previous page, even though some of them don't appear in the query. Databases 416usually generate errors in this case. Calling validateBinds() just prior to 417calling executeQuery() causes the API to check the query for each bind variable 418before actually binding it, preventing those kinds of errors. You can also 419call validBind() to see if a specific variable is valid. However there is 420a performance cost associated with calling validateBinds() and validBind().</p> 421 422<a name="rebinding"/><h2>Re-Binding and Re-Execution</h2> 423 424<p>Another feature of the prepare/bind/execute paradigm is the ability to 425prepare, bind and execute a query once, then re-bind and re-execute the query 426over and over without re-preparing it. If your backend database natively 427supports this paradigm, you can reap a substantial performance improvement.</p> 428 429<blockquote> 430<!-- Generator: GNU source-highlight 3.1.8 431by Lorenzo Bettini 432http://www.lorenzobettini.it 433http://www.gnu.org/software/src-highlite --> 434<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 435<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 436 437<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 438 439 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 440 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 441 442 <b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from mytable where mycolumn>:value"</font><font color="#990000">);</font> 443 <b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"value"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 444 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 445 446 <font color="#990000">...</font> process the <font color="#008080">result</font> set <font color="#990000">...</font> 447 448 <b><font color="#000000">sqlrcur_clearBinds</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 449 <b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"value"</font><font color="#990000">,</font><font color="#993399">5</font><font color="#990000">);</font> 450 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 451 452 <font color="#990000">...</font> process the <font color="#008080">result</font> set <font color="#990000">...</font> 453 454 <b><font color="#000000">sqlrcur_clearBinds</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 455 <b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"value"</font><font color="#990000">,</font><font color="#993399">10</font><font color="#990000">);</font> 456 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 457 458 <font color="#990000">...</font> process the <font color="#008080">result</font> set <font color="#990000">...</font> 459 460 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 461 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 462<font color="#FF0000">}</font> 463</tt></pre> 464 465</blockquote> 466<br/><a name="fields"/><h2>Accessing Fields in the Result Set</h2> 467 468<p>The sqlrcur_rowCount(), sqlrcur_colCount(), sqlrcur_getFieldByName() and 469sqlrcur_getFieldByIndex() functions are useful for processing result sets.</p> 470 471<blockquote> 472<!-- Generator: GNU source-highlight 3.1.8 473by Lorenzo Bettini 474http://www.lorenzobettini.it 475http://www.gnu.org/software/src-highlite --> 476<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 477<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 478 479<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 480 481 <font color="#009900">int</font> row<font color="#990000">,</font>col<font color="#990000">;</font> 482 483 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 484 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 485 486 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 487 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 488 489 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>row<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> row<font color="#990000"><</font><b><font color="#000000">sqlrcur_rowCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> row<font color="#990000">++)</font> <font color="#FF0000">{</font> 490 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>col<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> col<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> col<font color="#990000">++)</font> <font color="#FF0000">{</font> 491 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"%s,"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>row<font color="#990000">,</font>col<font color="#990000">));</font> 492 <font color="#FF0000">}</font> 493 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">);</font> 494 <font color="#FF0000">}</font> 495 496 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 497 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 498<font color="#FF0000">}</font> 499</tt></pre> 500 501</blockquote> 502<p>The sqlrcur_getFieldByName() and sqlrcur_getFieldByIndex() functions return 503strings. If you would like to get a field as a long or double, you can use 504sqlrcur_getFieldAsLongByName()/sqlrcur_getFieldAsLongByIndex() and 505sqlrcur_getFieldAsDoubleByName()/sqlrcur_getFieldAsDoubleByIndex().</p> 506 507<p>You can also use sqlrcur_getRow() which returns a NULL-terminated array of 508the fields in the row.</p> 509 510<blockquote> 511<!-- Generator: GNU source-highlight 3.1.8 512by Lorenzo Bettini 513http://www.lorenzobettini.it 514http://www.gnu.org/software/src-highlite --> 515<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 516<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 517 518<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 519 520 <font color="#009900">int</font> row<font color="#990000">,</font>col<font color="#990000">;</font> 521 522 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 523 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 524 525 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 526 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 527 528 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>row<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> row<font color="#990000"><</font><b><font color="#000000">sqlrcur_rowCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> row<font color="#990000">++)</font> <font color="#FF0000">{</font> 529 <font color="#009900">char</font> <font color="#990000">**</font>rowarray<font color="#990000">=</font><b><font color="#000000">sqlrcur_getRow</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>row<font color="#990000">);</font> 530 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>col<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> col<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> col<font color="#990000">++)</font> <font color="#FF0000">{</font> 531 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"%s,"</font><font color="#990000">,</font>rowarray<font color="#990000">[</font>col<font color="#990000">]);</font> 532 <font color="#FF0000">}</font> 533 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">);</font> 534 <font color="#FF0000">}</font> 535 536 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 537 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 538<font color="#FF0000">}</font> 539</tt></pre> 540 541</blockquote> 542<p>The sqlrcur_getFieldByIndex(), sqlrcur_getFieldByName() and sqlrcur_getRow() 543functions return NULL fields as empty strings. If you would like them to come 544back as NULL's instead, you can call the sqlrcur_getNullsAsNulls() function. To 545revert to the default behavior, you can call sqlrcur_getNullsAsEmptyStrings().</p> 546 547<p>If you want to access the result set, but don't care about the 548column information (column names, types or sizes) and don't mind getting 549fields by their numeric index instead of by name, you can call the 550sqlrcur_dontGetColumnInfo() function prior to executing your query. This can 551result in a performance improvement, especially when many queries with small 552result sets are executed in rapid succession. You can call 553sqlrcur_getColumnInfo() again later to turn off this feature.</p> 554 555<a name="largeresultsets"/><h2>Dealing With Large Result Sets</h2> 556 557<p>SQL Relay normally buffers the entire result set. This can speed things up 558at the cost of memory. With large enough result sets, it makes sense to buffer the result set in chunks instead of all at once.</p> 559 560<p>Use sqlrcur_setResultSetBufferSize() to set the number of rows to buffer at a 561time. Calls to sqlrcur_getRow(), sqlrcur_getFieldByIndex() and 562sqlrcur_getFieldByName() cause the chunk containing the requested field to be 563fetched. Rows in that chunk are accessible but rows before it are not.</p> 564 565<p>For example, if you setResultSetBufferSize(5) and execute a query that 566returns 20 rows, rows 0-4 are available at once, then rows 5-9, then 10-14, 567then 15-19. When rows 5-9 are available, getFieldByIndex(0,0) will return NULL 568and getFieldByIndex(11,0) will cause rows 10-14 to be fetched and return the 569requested value.</p> 570 571<p>When buffering the result set in chunks, don't end the session until after 572you're done with the result set.</p> 573 574<p>If you call sqlrcur_setResultSetBufferSize() and forget what you set it to, 575you can always call sqlrcur_getResultSetBufferSize().</p> 576 577<p>When buffering a result set in chunks, the sqlrcur_rowCount() function returns 578the number of rows returned so far. The sqlrcur_firstRowIndex() function returns 579the index of the first row of the currently buffered chunk.</p> 580 581<blockquote> 582<!-- Generator: GNU source-highlight 3.1.8 583by Lorenzo Bettini 584http://www.lorenzobettini.it 585http://www.gnu.org/software/src-highlite --> 586<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 587<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 588 589<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 590 591 <font color="#009900">int</font> done<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> 592 <font color="#009900">int</font> row<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> 593 <font color="#009900">int</font> col<font color="#990000">;</font> 594 <font color="#009900">char</font> <font color="#990000">*</font>field<font color="#990000">;</font> 595 596 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 597 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 598 599 <b><font color="#000000">sqlrcur_setResultSetBufferSize</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">5</font><font color="#990000">);</font> 600 601 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 602 603 <b><font color="#0000FF">while</font></b> <font color="#990000">(!</font>done<font color="#990000">)</font> <font color="#FF0000">{</font> 604 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>col<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> col<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> col<font color="#990000">++)</font> <font color="#FF0000">{</font> 605 <b><font color="#0000FF">if</font></b> <font color="#990000">(</font>field<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>row<font color="#990000">,</font>col<font color="#990000">))</font> <font color="#FF0000">{</font> 606 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"%s,"</font><font color="#990000">,</font>field<font color="#990000">);</font> 607 <font color="#FF0000">}</font> <b><font color="#0000FF">else</font></b> <font color="#FF0000">{</font> 608 done<font color="#990000">=</font><font color="#993399">1</font><font color="#990000">;</font> 609 <font color="#FF0000">}</font> 610 <font color="#FF0000">}</font> 611 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">);</font> 612 row<font color="#990000">++;</font> 613 <font color="#FF0000">}</font> 614 615 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_other_table"</font><font color="#990000">);</font> 616 617 <font color="#990000">...</font> process <font color="#008080">this</font> query<font color="#FF0000">'s result set in chunks also ...</font> 618 619<font color="#FF0000"> sqlrcur_setResultSetBufferSize(cur,0);</font> 620 621<font color="#FF0000"> sqlrcur_sendQuery(cur,"select * from my_third_table");</font> 622 623<font color="#FF0000"> ... process this query'</font>s result set all <font color="#008080">at</font> once <font color="#990000">...</font> 624 625 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 626 627 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 628 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 629<font color="#FF0000">}</font> 630</tt></pre> 631 632</blockquote> 633<a name="cursors"/><h2>Cursors</h2> 634 635<p>Cursors make it possible to execute queries while processing the result 636set of another query. You can select rows from a table in one query, then 637iterate through its result set, inserting rows into another table, using only 6381 database connection for both operations.</p> 639 640<p>For example:</p> 641 642<blockquote> 643<!-- Generator: GNU source-highlight 3.1.8 644by Lorenzo Bettini 645http://www.lorenzobettini.it 646http://www.gnu.org/software/src-highlite --> 647<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 648 649<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 650 651 <font color="#008080">sqlrcon</font> con<font color="#990000">;</font> 652 <font color="#008080">sqlrcur</font> cursor1<font color="#990000">;</font> 653 <font color="#008080">sqlrcur</font> cursor2<font color="#990000">;</font> 654 <font color="#009900">int</font> index<font color="#990000">;</font> 655 656 con<font color="#990000">=</font><b><font color="#0000FF">new</font></b> <b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 657 cursor1<font color="#990000">=</font><b><font color="#0000FF">new</font></b> <b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 658 cursor2<font color="#990000">=</font><b><font color="#0000FF">new</font></b> <b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 659 660 <b><font color="#000000">sqlrcur_setResultSetBufferSize</font></b><font color="#990000">(</font>cursor1<font color="#990000">,</font><font color="#993399">10</font><font color="#990000">);</font> 661 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cursor1<font color="#990000">,</font><font color="#FF0000">"select * from my_huge_table"</font><font color="#990000">);</font> 662 663 index<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> 664 <b><font color="#0000FF">while</font></b> <font color="#990000">(!</font><b><font color="#000000">sqlrcur_endOfResultSet</font></b><font color="#990000">(</font>cursor1<font color="#990000">))</font> <font color="#FF0000">{</font> 665 <b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cursor2<font color="#990000">,</font><font color="#FF0000">"insert into my_other_table values (:1,:2,:3)"</font><font color="#990000">);</font> 666 <b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cursor2<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cursor1<font color="#990000">,</font>index<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">));</font> 667 <b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cursor2<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cursor1<font color="#990000">,</font>index<font color="#990000">,</font><font color="#993399">2</font><font color="#990000">));</font> 668 <b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cursor2<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cursor1<font color="#990000">,</font>index<font color="#990000">,</font><font color="#993399">3</font><font color="#990000">));</font> 669 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cursor2<font color="#990000">);</font> 670 <font color="#FF0000">}</font> 671 672 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cursor2<font color="#990000">);</font> 673 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cursor1<font color="#990000">);</font> 674 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 675<font color="#FF0000">}</font> 676</tt></pre> 677 678</blockquote> 679<p>If you are using stored procedures with Oracle, a stored 680procedure can execute a query and return a cursor. A cursor bind variable can 681then retrieve that cursor. Your program can retrieve the result set from the 682cursor. All of this can be accomplished using defineOutputBindCursor(), 683getOutputBindCursor() and fetchFromOutputBindCursor().</p> 684 685<blockquote> 686<!-- Generator: GNU source-highlight 3.1.8 687by Lorenzo Bettini 688http://www.lorenzobettini.it 689http://www.gnu.org/software/src-highlite --> 690<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 691<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 692 693<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 694 695 <font color="#009900">int</font> i<font color="#990000">,</font>j<font color="#990000">;</font> 696 697 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 698 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 699 700 <b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"begin :curs:=sp_mytable; end;"</font><font color="#990000">);</font> 701 <b><font color="#000000">sqlrcur_defineOutputBindCursor</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"curs"</font><font color="#990000">);</font> 702 <b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 703 704 <font color="#008080">sqlrcur</font> bindcur<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindCursor</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"curs"</font><font color="#990000">);</font> 705 <b><font color="#000000">sqlrcur_fetchFromBindCursor</font></b><font color="#990000">(</font>bindcur<font color="#990000">);</font> 706 707 <i><font color="#9A1900">// print fields from table</font></i> 708 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>i<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> i<font color="#990000"><</font><b><font color="#000000">sqlrcur_rowCount</font></b><font color="#990000">(</font>bindcur<font color="#990000">);</font> i<font color="#990000">++)</font> <font color="#FF0000">{</font> 709 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>j<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> j<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>bindcur<font color="#990000">);</font> j<font color="#990000">++)</font> <font color="#FF0000">{</font> 710 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"%s,"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>bindcur<font color="#990000">,</font>i<font color="#990000">,</font>j<font color="#990000">));</font> 711 <font color="#FF0000">}</font> 712 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">);</font> 713 <font color="#FF0000">}</font> 714 715 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>bindcur<font color="#990000">);</font> 716 717 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 718 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 719<font color="#FF0000">}</font> 720</tt></pre> 721 722</blockquote> 723<p>The number of cursors simultaneously available per-connection is set at 724compile time and defaults to 5.</p> 725 726<a name="columns"/><h2>Getting Column Information</h2> 727 728<p>For each column, the API supports getting the name, type and length of each 729field. All databases support these attributes. The API also supports 730getting the precision, scale (see <a href="precisionscale.html">this page</a> 731for a discussion of precision and scale), length of the longest field, and 732whether the 733column is nullable, the primary key, unique, part of a key, unsigned, 734zero-filled, binary, or an auto-incrementing field. However, not all databases 735support these attributes. If a database doesn't support an attribute, it is 736always returned as false.</p> 737 738<blockquote> 739<!-- Generator: GNU source-highlight 3.1.8 740by Lorenzo Bettini 741http://www.lorenzobettini.it 742http://www.gnu.org/software/src-highlite --> 743<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 744<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 745 746<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 747 748 <font color="#009900">int</font> i<font color="#990000">;</font> 749 750 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 751 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 752 753 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 754 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 755 756 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>i<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> i<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> i<font color="#990000">++)</font> <font color="#FF0000">{</font> 757 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Name: %s</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnName</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 758 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Type: %s</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnType</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 759 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Length: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnLength</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 760 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Precision: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnPrecision</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 761 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Scale: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnScale</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 762 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Longest Field: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getLongest</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 763 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Nullable: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsNullable</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 764 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Primary Key: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsPrimaryKey</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 765 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Unique: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsUnique</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 766 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Part of Key: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsPartOfKey</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 767 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Unsigned: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsUnsigned</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 768 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Zero Filled: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsZeroFilled</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 769 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Binary: %d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsBinary</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 770 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Auth Increment:%d</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnIsAutoIncrement</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 771 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">);</font> 772 <font color="#FF0000">}</font> 773 774 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 775 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 776<font color="#FF0000">}</font> 777</tt></pre> 778 779</blockquote> 780<p>Some databases force column names to upper case, others force column names 781to lower case, and others still support mixed-case column names. Sometimes, 782when migrating between databases, you can run into trouble. You can use 783upperCaseColumnNames() and lowerCaseColumnNames() to cause column names to be 784converted to upper or lower case, or you can use mixedCaseColumnNames() to 785cause column names to be returned in the same case as they are defined in the 786database.</p> 787 788<blockquote> 789<!-- Generator: GNU source-highlight 3.1.8 790by Lorenzo Bettini 791http://www.lorenzobettini.it 792http://www.gnu.org/software/src-highlite --> 793<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 794<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 795 796<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 797 798 <font color="#009900">int</font> i<font color="#990000">;</font> 799 800 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 801 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 802 803 <i><font color="#9A1900">// column names will be forced to upper case</font></i> 804 <b><font color="#000000">sqlrcur_upperCaseColumnNames</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 805 <b><font color="#000000">sqlrcur_endQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 806 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 807 808 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>i<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> i<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> i<font color="#990000">++)</font> <font color="#FF0000">{</font> 809 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Name: %s</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnName</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 810 <font color="#FF0000">}</font> 811 812 <i><font color="#9A1900">// column names will be forced to lower case</font></i> 813 <b><font color="#000000">sqlrcur_lowerCaseColumnNames</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 814 <b><font color="#000000">sqlrcur_endQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 815 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 816 817 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>i<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> i<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> i<font color="#990000">++)</font> <font color="#FF0000">{</font> 818 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Name: %s</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnName</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 819 <font color="#FF0000">}</font> 820 821 <i><font color="#9A1900">// column names will be the same as they are in the database</font></i> 822 <b><font color="#000000">sqlrcur_mixedCaseColumnNames</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 823 <b><font color="#000000">sqlrcur_endQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 824 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 825 826 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>i<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> i<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> i<font color="#990000">++)</font> <font color="#FF0000">{</font> 827 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"Name: %s</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getColumnName</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>i<font color="#990000">));</font> 828 <font color="#FF0000">}</font> 829 830 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 831 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 832<font color="#FF0000">}</font> 833</tt></pre> 834 835</blockquote> 836<br/><a name="storedprocedures"/><h2>Stored Procedures</h2> 837 838<p>Many databases support stored procedures. Stored procedures are sets of 839queries and procedural code that are executed inside of the database itself. 840For example, a stored procedure may select rows from one table, iterate through 841the result set and, based on the values in each row, insert, update or delete 842rows in other tables. A client program could do this as well, but a stored 843procedure is generally more efficient because queries and result sets don't 844have to be sent back and forth between the client and database. Also, stored 845procedures are generally stored in the database in a compiled state, while 846queries may have to be re-parsed and re-compiled each time they are sent.</p> 847 848<p>While many databases support stored procedures. The syntax for creating 849and executing stored procedures varies greatly between databases.</p> 850 851<p>Stored procedures typically take input paramters from client programs through 852input bind variables and return values back to client programs either through 853bind variables or result sets. Stored procedures can be broken down into 854several categories, based on the values that they return. Some stored 855procedures don't return any values, some return a single value, some return 856multiple values and some return entire result sets.</p> 857 858<h3>No Values</h3> 859 860<p>Some stored procedures don't return any values. Below are examples, 861illustrating how to create, execute and drop this kind of stored procedure for 862each database that SQL Relay supports.</p> 863 864<h4>Oracle</h4> 865 866<p>To create the stored procedure, run a query like the following.</p> 867 868<blockquote> 869<!-- Generator: GNU source-highlight 3.1.8 870by Lorenzo Bettini 871http://www.lorenzobettini.it 872http://www.gnu.org/software/src-highlite --> 873<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in1 <font color="#008080">in</font> number<font color="#990000">,</font> in2 <font color="#008080">in</font> number<font color="#990000">,</font> in3 <font color="#008080">in</font> varchar2<font color="#990000">)</font> is 874begin 875 insert into <font color="#008080">mytable</font> <b><font color="#000000">values</font></b> <font color="#990000">(</font>in1<font color="#990000">,</font>in2<font color="#990000">,</font>in3<font color="#990000">);</font> 876end<font color="#990000">;</font> 877</tt></pre> 878 879</blockquote> 880<p>To execute the stored procedure from an SQL Relay program, use code like the 881following.</p> 882 883<blockquote> 884<!-- Generator: GNU source-highlight 3.1.8 885by Lorenzo Bettini 886http://www.lorenzobettini.it 887http://www.gnu.org/software/src-highlite --> 888<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"begin exampleproc(:in1,:in2,:in3); end;"</font><font color="#990000">);</font> 889<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 890<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 891<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 892<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 893</tt></pre> 894 895</blockquote> 896<p>To drop the stored procedure, run a query like the following.</p> 897 898<blockquote> 899<!-- Generator: GNU source-highlight 3.1.8 900by Lorenzo Bettini 901http://www.lorenzobettini.it 902http://www.gnu.org/software/src-highlite --> 903<pre><tt>drop procedure exampleproc 904</tt></pre> 905 906</blockquote> 907<br/><h4>Sybase and Microsoft SQL Server</h4> 908 909<p>To create the stored procedure, run a query like the following.</p> 910 911<blockquote> 912<!-- Generator: GNU source-highlight 3.1.8 913by Lorenzo Bettini 914http://www.lorenzobettini.it 915http://www.gnu.org/software/src-highlite --> 916<pre><tt>create <font color="#008080">procedure</font> exampleproc @<font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> @<font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> @<font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">)</font> as 917 insert into <font color="#008080">mytable</font> <b><font color="#000000">values</font></b> <font color="#990000">(</font>@in1<font color="#990000">,</font>@in2<font color="#990000">,</font>@in3<font color="#990000">)</font> 918</tt></pre> 919 920</blockquote> 921<p>To execute the stored procedure from an SQL Relay program, use code like the 922following.</p> 923 924<blockquote> 925<!-- Generator: GNU source-highlight 3.1.8 926by Lorenzo Bettini 927http://www.lorenzobettini.it 928http://www.gnu.org/software/src-highlite --> 929<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"exec exampleproc"</font><font color="#990000">);</font> 930<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 931<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 932<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 933<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 934</tt></pre> 935 936</blockquote> 937<p>To drop the stored procedure, run a query like the following.</p> 938 939<blockquote> 940<!-- Generator: GNU source-highlight 3.1.8 941by Lorenzo Bettini 942http://www.lorenzobettini.it 943http://www.gnu.org/software/src-highlite --> 944<pre><tt>drop procedure exampleproc 945</tt></pre> 946 947</blockquote> 948<h4>Firebird</h4> 949 950<p>To create the stored procedure, run a query like the following.</p> 951 952<blockquote> 953<!-- Generator: GNU source-highlight 3.1.8 954by Lorenzo Bettini 955http://www.lorenzobettini.it 956http://www.gnu.org/software/src-highlite --> 957<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font><font color="#008080">in1</font> integer<font color="#990000">,</font> <font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> as 958begin 959 insert into <font color="#008080">mytable</font> <b><font color="#000000">values</font></b> <font color="#990000">(</font>in1<font color="#990000">,</font>in2<font color="#990000">,</font>in3<font color="#990000">);</font> 960 suspend<font color="#990000">;</font> 961end<font color="#990000">;</font> 962</tt></pre> 963 964</blockquote> 965<p>To execute the stored procedure from an SQL Relay program, use code like the 966following.</p> 967 968<blockquote> 969<!-- Generator: GNU source-highlight 3.1.8 970by Lorenzo Bettini 971http://www.lorenzobettini.it 972http://www.gnu.org/software/src-highlite --> 973<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"execute procedure exampleproc ?, ?, ?"</font><font color="#990000">);</font> 974<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 975<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 976<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 977<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 978</tt></pre> 979 980</blockquote> 981<p>To drop the stored procedure, run a query like the following.</p> 982 983<blockquote> 984<!-- Generator: GNU source-highlight 3.1.8 985by Lorenzo Bettini 986http://www.lorenzobettini.it 987http://www.gnu.org/software/src-highlite --> 988<pre><tt>drop procedure exampleproc 989</tt></pre> 990 991</blockquote> 992<h4>DB2</h4> 993 994<p>To create the stored procedure, run a query like the following.</p> 995 996<blockquote> 997<!-- Generator: GNU source-highlight 3.1.8 998by Lorenzo Bettini 999http://www.lorenzobettini.it 1000http://www.gnu.org/software/src-highlite --> 1001<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in <font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> in <font color="#008080">in2</font> <font color="#009900">double</font><font color="#990000">,</font> in <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> language sql 1002begin 1003 insert into <font color="#008080">mytable</font> <b><font color="#000000">values</font></b> <font color="#990000">(</font>in1<font color="#990000">,</font>in2<font color="#990000">,</font>in3<font color="#990000">);</font> 1004end<font color="#990000">;</font> 1005</tt></pre> 1006 1007</blockquote> 1008<p>To execute the stored procedure from an SQL Relay program, use code like the 1009following.</p> 1010 1011<blockquote> 1012<!-- Generator: GNU source-highlight 3.1.8 1013by Lorenzo Bettini 1014http://www.lorenzobettini.it 1015http://www.gnu.org/software/src-highlite --> 1016<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc(?,?,?)"</font><font color="#990000">);</font> 1017<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1018<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1019<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1020<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1021</tt></pre> 1022 1023</blockquote> 1024<p>To drop the stored procedure, run a query like the following.</p> 1025 1026<blockquote> 1027<!-- Generator: GNU source-highlight 3.1.8 1028by Lorenzo Bettini 1029http://www.lorenzobettini.it 1030http://www.gnu.org/software/src-highlite --> 1031<pre><tt>drop procedure exampleproc 1032</tt></pre> 1033 1034</blockquote> 1035<h4>Postgresql</h4> 1036 1037<p>To create the stored procedure, run a query like the following.</p> 1038 1039<blockquote> 1040<!-- Generator: GNU source-highlight 3.1.8 1041by Lorenzo Bettini 1042http://www.lorenzobettini.it 1043http://www.gnu.org/software/src-highlite --> 1044<pre><tt>create <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">(</font><font color="#009900">int</font><font color="#990000">,</font><font color="#009900">float</font><font color="#990000">,</font><b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> returns <font color="#008080">void</font> as <font color="#FF0000">'</font> 1045<font color="#FF0000">begin</font> 1046<font color="#FF0000"> insert into mytable values ($1,$2,$3);</font> 1047<font color="#FF0000"> return;</font> 1048<font color="#FF0000">end;'</font> language plpgsql 1049</tt></pre> 1050 1051</blockquote> 1052<p>To execute the stored procedure from an SQL Relay program, use code like the 1053following.</p> 1054 1055<blockquote> 1056<!-- Generator: GNU source-highlight 3.1.8 1057by Lorenzo Bettini 1058http://www.lorenzobettini.it 1059http://www.gnu.org/software/src-highlite --> 1060<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select examplefunc($1,$2,$3)"</font><font color="#990000">);</font> 1061<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1062<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1063<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1064<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1065</tt></pre> 1066 1067</blockquote> 1068<p>To drop the stored procedure, run a query like the following.</p> 1069 1070<blockquote> 1071<!-- Generator: GNU source-highlight 3.1.8 1072by Lorenzo Bettini 1073http://www.lorenzobettini.it 1074http://www.gnu.org/software/src-highlite --> 1075<pre><tt>drop <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">(</font><font color="#009900">int</font><font color="#990000">,</font><font color="#009900">float</font><font color="#990000">,</font><b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> 1076</tt></pre> 1077 1078</blockquote> 1079<h4>MySQL/MariaDB</h4> 1080 1081<p>To create the stored procedure, run a query like the following.</p> 1082 1083<blockquote> 1084<!-- Generator: GNU source-highlight 3.1.8 1085by Lorenzo Bettini 1086http://www.lorenzobettini.it 1087http://www.gnu.org/software/src-highlite --> 1088<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in <font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> in <font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> in <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> 1089begin 1090 insert into <font color="#008080">mytable</font> <b><font color="#000000">values</font></b> <font color="#990000">(</font>in1<font color="#990000">,</font>in2<font color="#990000">,</font>in3<font color="#990000">);</font> 1091end<font color="#990000">;</font> 1092</tt></pre> 1093 1094</blockquote> 1095<p>To execute the stored procedure from an SQL Relay program, use code like the 1096following.</p> 1097 1098<blockquote> 1099<!-- Generator: GNU source-highlight 3.1.8 1100by Lorenzo Bettini 1101http://www.lorenzobettini.it 1102http://www.gnu.org/software/src-highlite --> 1103<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc(?,?,?)"</font><font color="#990000">);</font> 1104<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1105<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1106<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1107<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1108</tt></pre> 1109 1110</blockquote> 1111<p>Note: Versions of MySQL prior to 5.0 had trouble calling stored procedures using bind variables. If you are using a version of MySQL prior to 5.0 then SQL relay must fake the bind variables and you must use colon-delimited variables (:1, :2, :3, etc.) in your queries rather than the native-mysql queston marks.</p> 1112 1113<p>To drop the stored procedure, run a query like the following.</p> 1114 1115<blockquote> 1116<!-- Generator: GNU source-highlight 3.1.8 1117by Lorenzo Bettini 1118http://www.lorenzobettini.it 1119http://www.gnu.org/software/src-highlite --> 1120<pre><tt>drop procedure exampleproc 1121</tt></pre> 1122 1123</blockquote> 1124<br/><h3>Single Values</h3> 1125 1126<p>Some stored procedures return single values. Below are examples, 1127illustrating how to create, execute and drop this kind of stored procedure for 1128each database that SQL Relay supports.</p> 1129 1130<h4>Oracle</h4> 1131 1132<p>In Oracle, stored procedures can return values through output parameters or 1133as return values of the procedure itself.</p> 1134 1135<p>Here is an example where the procedure itself returns a value. Note that 1136Oracle calls these functions.</p> 1137 1138<p>To create the stored procedure, run a query like the following.</p> 1139 1140<blockquote> 1141<!-- Generator: GNU source-highlight 3.1.8 1142by Lorenzo Bettini 1143http://www.lorenzobettini.it 1144http://www.gnu.org/software/src-highlite --> 1145<pre><tt>create <font color="#008080">function</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in1 <font color="#008080">in</font> number<font color="#990000">,</font> in2 <font color="#008080">in</font> number<font color="#990000">,</font> in3 <font color="#008080">in</font> varchar2<font color="#990000">)</font> returns number is 1146begin 1147 <b><font color="#0000FF">return</font></b> in1<font color="#990000">;</font> 1148end<font color="#990000">;</font> 1149</tt></pre> 1150 1151</blockquote> 1152<p>To execute the stored procedure from an SQL Relay program, use code like the 1153following.</p> 1154 1155<blockquote> 1156<!-- Generator: GNU source-highlight 3.1.8 1157by Lorenzo Bettini 1158http://www.lorenzobettini.it 1159http://www.gnu.org/software/src-highlite --> 1160<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select exampleproc(:in1,:in2,:in3) from dual"</font><font color="#990000">);</font> 1161<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1162<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1163<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1164<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1165<font color="#009900">char</font> <font color="#990000">*</font>result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1166</tt></pre> 1167 1168</blockquote> 1169<p>To drop the stored procedure, run a query like the following.</p> 1170 1171<blockquote> 1172<!-- Generator: GNU source-highlight 3.1.8 1173by Lorenzo Bettini 1174http://www.lorenzobettini.it 1175http://www.gnu.org/software/src-highlite --> 1176<pre><tt>drop procedure exampleproc 1177</tt></pre> 1178 1179</blockquote> 1180<p>Here is an example where the value is returned through an output 1181parameter.</p> 1182 1183<p>To create the stored procedure, run a query like the following.</p> 1184 1185<blockquote> 1186<!-- Generator: GNU source-highlight 3.1.8 1187by Lorenzo Bettini 1188http://www.lorenzobettini.it 1189http://www.gnu.org/software/src-highlite --> 1190<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in1 <font color="#008080">in</font> number<font color="#990000">,</font> in2 <font color="#008080">in</font> number<font color="#990000">,</font> in3 <font color="#008080">in</font> varchar2<font color="#990000">,</font> out1 <font color="#008080">out</font> number<font color="#990000">)</font> as 1191begin 1192 out1<font color="#990000">:=</font>in1<font color="#990000">;</font> 1193end<font color="#990000">;</font> 1194</tt></pre> 1195 1196</blockquote> 1197<p>To execute the stored procedure from an SQL Relay program, use code like the 1198following.</p> 1199 1200<blockquote> 1201<!-- Generator: GNU source-highlight 3.1.8 1202by Lorenzo Bettini 1203http://www.lorenzobettini.it 1204http://www.gnu.org/software/src-highlite --> 1205<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"begin exampleproc(:in1,:in2,:in3,:out1); end;"</font><font color="#990000">);</font> 1206<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1207<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1208<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1209<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1210<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1211<font color="#008080">int64_t</font> result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1212</tt></pre> 1213 1214</blockquote> 1215<p>To drop the stored procedure, run a query like the following.</p> 1216 1217<blockquote> 1218<!-- Generator: GNU source-highlight 3.1.8 1219by Lorenzo Bettini 1220http://www.lorenzobettini.it 1221http://www.gnu.org/software/src-highlite --> 1222<pre><tt>drop procedure exampleproc 1223</tt></pre> 1224 1225</blockquote> 1226<br/><h4>Sybase and Microsoft SQL Server</h4> 1227 1228<p>In Sybase and Microsoft SQL Server, stored procedures return values 1229through output parameters rather than as return values of the procedure 1230itself.</p> 1231 1232<p>To create the stored procedure, run a query like the following.</p> 1233 1234<blockquote> 1235<!-- Generator: GNU source-highlight 3.1.8 1236by Lorenzo Bettini 1237http://www.lorenzobettini.it 1238http://www.gnu.org/software/src-highlite --> 1239<pre><tt>create <font color="#008080">procedure</font> exampleproc @<font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> @<font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> @<font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font> @out1 <font color="#009900">int</font> output as 1240 select @out1<font color="#990000">=</font><b><font color="#000000">convert</font></b><font color="#990000">(</font><b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font>@in1<font color="#990000">)</font> 1241</tt></pre> 1242 1243</blockquote> 1244<p>To execute the stored procedure from an SQL Relay program, use code like the 1245following.</p> 1246 1247<blockquote> 1248<!-- Generator: GNU source-highlight 3.1.8 1249by Lorenzo Bettini 1250http://www.lorenzobettini.it 1251http://www.gnu.org/software/src-highlite --> 1252<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"exec exampleproc"</font><font color="#990000">);</font> 1253<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1254<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1255<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1256<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1257<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1258<font color="#008080">int64_t</font> result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1259</tt></pre> 1260 1261</blockquote> 1262<p>To drop the stored procedure, run a query like the following.</p> 1263 1264<blockquote> 1265<!-- Generator: GNU source-highlight 3.1.8 1266by Lorenzo Bettini 1267http://www.lorenzobettini.it 1268http://www.gnu.org/software/src-highlite --> 1269<pre><tt>drop procedure exampleproc 1270</tt></pre> 1271 1272</blockquote> 1273<br/><h4>Firebird</h4> 1274 1275<p>To create the stored procedure, run a query like the following.</p> 1276 1277<blockquote> 1278<!-- Generator: GNU source-highlight 3.1.8 1279by Lorenzo Bettini 1280http://www.lorenzobettini.it 1281http://www.gnu.org/software/src-highlite --> 1282<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font><font color="#008080">in1</font> integer<font color="#990000">,</font> <font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> <b><font color="#000000">returns</font></b> <font color="#990000">(</font><font color="#008080">out1</font> integer<font color="#990000">)</font> as 1283begin 1284 out1<font color="#990000">=</font>in1<font color="#990000">;</font> 1285 suspend<font color="#990000">;</font> 1286end<font color="#990000">;</font> 1287</tt></pre> 1288 1289</blockquote> 1290<p>To execute the stored procedure from an SQL Relay program, use code like the 1291following.</p> 1292 1293<blockquote> 1294<!-- Generator: GNU source-highlight 3.1.8 1295by Lorenzo Bettini 1296http://www.lorenzobettini.it 1297http://www.gnu.org/software/src-highlite --> 1298<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from exampleproc(?,?,?)"</font><font color="#990000">);</font> 1299<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1300<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1301<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1302<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1303<font color="#009900">char</font> <font color="#990000">*</font>result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1304</tt></pre> 1305 1306</blockquote> 1307<p>Alternatively, you can run a query like the following and receive the result 1308using an output bind variable. Note that in Firebird, input and 1309output bind variable indices are distict from one another. The index of the 1310output bind variable is 1 rather than 4, even though there were 3 input bind 1311variables.</p> 1312 1313<blockquote> 1314<!-- Generator: GNU source-highlight 3.1.8 1315by Lorenzo Bettini 1316http://www.lorenzobettini.it 1317http://www.gnu.org/software/src-highlite --> 1318<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"execute procedure exampleproc ?, ?, ?"</font><font color="#990000">);</font> 1319<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1320<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1321<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1322<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">);</font> 1323<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1324<font color="#008080">int64_t</font> result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">);</font> 1325</tt></pre> 1326 1327</blockquote> 1328<p>To drop the stored procedure, run a query like the following.</p> 1329 1330<blockquote> 1331<!-- Generator: GNU source-highlight 3.1.8 1332by Lorenzo Bettini 1333http://www.lorenzobettini.it 1334http://www.gnu.org/software/src-highlite --> 1335<pre><tt>drop procedure exampleproc 1336</tt></pre> 1337 1338</blockquote> 1339<br/><h4>DB2</h4> 1340 1341<p>In DB2, stored procedures return values through output parameters rather 1342than as return values of the procedure itself.</p> 1343 1344<p>To create the stored procedure, run a query like the following.</p> 1345 1346<blockquote> 1347<!-- Generator: GNU source-highlight 3.1.8 1348by Lorenzo Bettini 1349http://www.lorenzobettini.it 1350http://www.gnu.org/software/src-highlite --> 1351<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in <font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> in <font color="#008080">in2</font> <font color="#009900">double</font><font color="#990000">,</font> in <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font> out <font color="#008080">out1</font> <font color="#009900">int</font><font color="#990000">)</font> language sql 1352begin 1353 <font color="#008080">set</font> out1 <font color="#990000">=</font> in1<font color="#990000">;</font> 1354end 1355</tt></pre> 1356 1357</blockquote> 1358<p>To execute the stored procedure from an SQL Relay program, use code like the 1359following.</p> 1360 1361<blockquote> 1362<!-- Generator: GNU source-highlight 3.1.8 1363by Lorenzo Bettini 1364http://www.lorenzobettini.it 1365http://www.gnu.org/software/src-highlite --> 1366<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc(?,?,?,?)"</font><font color="#990000">);</font> 1367<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1368<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1369<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1370<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"4"</font><font color="#990000">);</font> 1371<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1372<font color="#008080">int64_t</font> result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"4"</font><font color="#990000">);</font> 1373</tt></pre> 1374 1375</blockquote> 1376<p>To drop the stored procedure, run a query like the following.</p> 1377 1378<blockquote> 1379<!-- Generator: GNU source-highlight 3.1.8 1380by Lorenzo Bettini 1381http://www.lorenzobettini.it 1382http://www.gnu.org/software/src-highlite --> 1383<pre><tt>drop procedure exampleproc 1384</tt></pre> 1385 1386</blockquote> 1387<br/><h4>Postgresql</h4> 1388 1389<p>To create the stored procedure, run a query like the following.</p> 1390 1391<blockquote> 1392<!-- Generator: GNU source-highlight 3.1.8 1393by Lorenzo Bettini 1394http://www.lorenzobettini.it 1395http://www.gnu.org/software/src-highlite --> 1396<pre><tt>create <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">(</font><font color="#009900">int</font><font color="#990000">,</font><font color="#009900">float</font><font color="#990000">,</font><font color="#009900">char</font><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> returns <font color="#008080">int</font> as <font color="#FF0000">'</font> 1397<font color="#FF0000">declare</font> 1398<font color="#FF0000"> in1 int;</font> 1399<font color="#FF0000"> in2 float;</font> 1400<font color="#FF0000"> in3 char(20);</font> 1401<font color="#FF0000">begin</font> 1402<font color="#FF0000"> in1:=$1;</font> 1403<font color="#FF0000"> return;</font> 1404<font color="#FF0000">end;</font> 1405<font color="#FF0000">'</font> language plpgsql 1406</tt></pre> 1407 1408</blockquote> 1409<p>To execute the stored procedure from an SQL Relay program, use code like the 1410following.</p> 1411 1412<blockquote> 1413<!-- Generator: GNU source-highlight 3.1.8 1414by Lorenzo Bettini 1415http://www.lorenzobettini.it 1416http://www.gnu.org/software/src-highlite --> 1417<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from examplefunc(1,2,3)"</font><font color="#990000">);</font> 1418<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1419<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">4</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1420<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1421<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1422<font color="#009900">char</font> <font color="#990000">*</font>result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1423</tt></pre> 1424 1425</blockquote> 1426<p>To drop the stored procedure, run a query like the following.</p> 1427 1428<blockquote> 1429<!-- Generator: GNU source-highlight 3.1.8 1430by Lorenzo Bettini 1431http://www.lorenzobettini.it 1432http://www.gnu.org/software/src-highlite --> 1433<pre><tt>drop <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">(</font><font color="#009900">int</font><font color="#990000">,</font><font color="#009900">float</font><font color="#990000">,</font><font color="#009900">char</font><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> 1434</tt></pre> 1435 1436</blockquote> 1437<h4>MySQL/MariaDB</h4> 1438 1439<p>A single value can be returned from a MySQL/MariaDB function.</p> 1440 1441<p>To create the function, run a query like the following.</p> 1442 1443<blockquote> 1444<!-- Generator: GNU source-highlight 3.1.8 1445by Lorenzo Bettini 1446http://www.lorenzobettini.it 1447http://www.gnu.org/software/src-highlite --> 1448<pre><tt>create <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">(</font>in <font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> in <font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> in <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> returns <font color="#009900">int</font> <b><font color="#0000FF">return</font></b> in1<font color="#990000">;</font> 1449</tt></pre> 1450 1451</blockquote> 1452<p>To execute the function from an SQL Relay program, use code like the 1453following.</p> 1454 1455<blockquote> 1456<!-- Generator: GNU source-highlight 3.1.8 1457by Lorenzo Bettini 1458http://www.lorenzobettini.it 1459http://www.gnu.org/software/src-highlite --> 1460<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select examplefunc(?,?,?)"</font><font color="#990000">);</font> 1461<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1462<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">4</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1463<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1464<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1465<font color="#009900">char</font> <font color="#990000">*</font>result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1466</tt></pre> 1467 1468</blockquote> 1469<p>Note: Versions of MySQL prior to 5.0 had trouble calling stored procedures using bind variables. If you are using a version of MySQL prior to 5.0 then SQL relay must fake the bind variables and you must use colon-delimited variables (:1, :2, :3, etc.) in your queries rather than the native-mysql queston marks.</p> 1470 1471<p>To drop the function, run a query like the following.</p> 1472 1473<blockquote> 1474<!-- Generator: GNU source-highlight 3.1.8 1475by Lorenzo Bettini 1476http://www.lorenzobettini.it 1477http://www.gnu.org/software/src-highlite --> 1478<pre><tt>drop procedure exampleproc 1479</tt></pre> 1480 1481</blockquote> 1482<p>A single value can be returned in the result set of a MySQL/MariaDB procedure.</p> 1483 1484<p>To create the procedure, run a query like the following.</p> 1485 1486<blockquote> 1487<!-- Generator: GNU source-highlight 3.1.8 1488by Lorenzo Bettini 1489http://www.lorenzobettini.it 1490http://www.gnu.org/software/src-highlite --> 1491<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">()</font> begin select <font color="#993399">1</font><font color="#990000">;</font> end<font color="#990000">;</font> 1492</tt></pre> 1493 1494</blockquote> 1495<p>To execeute the procedure from an SQL Relay program, use code like the 1496following.</p> 1497 1498<blockquote> 1499<!-- Generator: GNU source-highlight 3.1.8 1500by Lorenzo Bettini 1501http://www.lorenzobettini.it 1502http://www.gnu.org/software/src-highlite --> 1503<pre><tt><b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select exampleproc()"</font><font color="#990000">);</font> 1504<font color="#009900">char</font> <font color="#990000">*</font>result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1505</tt></pre> 1506 1507</blockquote> 1508<p>To drop the procedure, run a query like the following.</p> 1509 1510<blockquote> 1511<!-- Generator: GNU source-highlight 3.1.8 1512by Lorenzo Bettini 1513http://www.lorenzobettini.it 1514http://www.gnu.org/software/src-highlite --> 1515<pre><tt>drop procedure exampleproc 1516</tt></pre> 1517 1518</blockquote> 1519<p>A single value can be returned using the output variable of a MySQL/MariaDB 1520procedure.</p> 1521 1522<p>To create the procedure, run a query like the following.</p> 1523 1524<blockquote> 1525<!-- Generator: GNU source-highlight 3.1.8 1526by Lorenzo Bettini 1527http://www.lorenzobettini.it 1528http://www.gnu.org/software/src-highlite --> 1529<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>out <font color="#008080">out1</font> <font color="#009900">int</font><font color="#990000">)</font> begin select <font color="#993399">1</font> <font color="#008080">into</font> out1<font color="#990000">;</font> end<font color="#990000">;</font> 1530</tt></pre> 1531 1532</blockquote> 1533<p>To execeute the procedure from an SQL Relay program, use code like the 1534following.</p> 1535 1536<blockquote> 1537<!-- Generator: GNU source-highlight 3.1.8 1538by Lorenzo Bettini 1539http://www.lorenzobettini.it 1540http://www.gnu.org/software/src-highlite --> 1541<pre><tt><b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"set @out1=0"</font><font color="#990000">);</font> 1542<b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc(@out1)"</font><font color="#990000">);</font> 1543<b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select @out1"</font><font color="#990000">);</font> 1544<font color="#009900">char</font> <font color="#990000">*</font>result<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1545</tt></pre> 1546 1547</blockquote> 1548<p>To drop the procedure, run a query like the following.</p> 1549 1550<blockquote> 1551<!-- Generator: GNU source-highlight 3.1.8 1552by Lorenzo Bettini 1553http://www.lorenzobettini.it 1554http://www.gnu.org/software/src-highlite --> 1555<pre><tt>drop procedure exampleproc 1556</tt></pre> 1557 1558</blockquote> 1559<br/><h3>Multiple Values</h3> 1560 1561<p>Some stored procedures return multiple values. Below are examples, 1562illustrating how to create, execute and drop this kind of stored procedure for 1563each database that SQL Relay supports.</p> 1564 1565<h4>Oracle</h4> 1566 1567<p>In Oracle, stored procedures can return values through output parameters or 1568as return values of the procedure itself. If a procedure needs to return 1569multiple values, it can return one of them as the return value of the procedure 1570itself, but the rest must be returned through output parameters.</p> 1571 1572<p>To create the stored procedure, run a query like the following.</p> 1573 1574<blockquote> 1575<!-- Generator: GNU source-highlight 3.1.8 1576by Lorenzo Bettini 1577http://www.lorenzobettini.it 1578http://www.gnu.org/software/src-highlite --> 1579<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in1 <font color="#008080">in</font> number<font color="#990000">,</font> in2 <font color="#008080">in</font> number<font color="#990000">,</font> in3 <font color="#008080">in</font> varchar2<font color="#990000">,</font> out1 <font color="#008080">out</font> number<font color="#990000">,</font> out2 <font color="#008080">out</font> number<font color="#990000">,</font> out3 <font color="#008080">out</font> varchar2<font color="#990000">)</font> is 1580begin 1581 out1<font color="#990000">:=</font>in1<font color="#990000">;</font> 1582 out2<font color="#990000">:=</font>in2<font color="#990000">;</font> 1583 out3<font color="#990000">:=</font>in3<font color="#990000">;</font> 1584end<font color="#990000">;</font> 1585</tt></pre> 1586 1587</blockquote> 1588<p>To execute the stored procedure from an SQL Relay program, use code like the 1589following.</p> 1590 1591<blockquote> 1592<!-- Generator: GNU source-highlight 3.1.8 1593by Lorenzo Bettini 1594http://www.lorenzobettini.it 1595http://www.gnu.org/software/src-highlite --> 1596<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"begin exampleproc(:in1,:in2,:in3,:out1,:out2,:out3); end;"</font><font color="#990000">);</font> 1597<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1598<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1599<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1600<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1601<b><font color="#000000">sqlrcur_defineOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out2"</font><font color="#990000">);</font> 1602<b><font color="#000000">sqlrcur_defineOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out3"</font><font color="#990000">,</font><font color="#993399">20</font><font color="#990000">);</font> 1603<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1604<font color="#008080">int64_t</font> out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1605<font color="#009900">double</font> out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out2"</font><font color="#990000">);</font> 1606<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out3"</font><font color="#990000">);</font> 1607</tt></pre> 1608 1609</blockquote> 1610<p>To drop the stored procedure, run a query like the following.</p> 1611 1612<blockquote> 1613<!-- Generator: GNU source-highlight 3.1.8 1614by Lorenzo Bettini 1615http://www.lorenzobettini.it 1616http://www.gnu.org/software/src-highlite --> 1617<pre><tt>drop procedure exampleproc 1618</tt></pre> 1619 1620</blockquote> 1621<br/><h4>Sybase and Microsoft SQL Server</h4> 1622 1623<p>To create the stored procedure, run a query like the following.</p> 1624 1625<blockquote> 1626<!-- Generator: GNU source-highlight 3.1.8 1627by Lorenzo Bettini 1628http://www.lorenzobettini.it 1629http://www.gnu.org/software/src-highlite --> 1630<pre><tt>create <font color="#008080">procedure</font> exampleproc @<font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> @<font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> @<font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font> @out1 <font color="#008080">int</font> output<font color="#990000">,</font> @out2 <font color="#008080">int</font> output<font color="#990000">,</font> @out3 <font color="#009900">int</font> output as 1631 select @out1<font color="#990000">=</font><b><font color="#000000">convert</font></b><font color="#990000">(</font><b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font>@in1<font color="#990000">),</font> 1632 @out2<font color="#990000">=</font><b><font color="#000000">convert</font></b><font color="#990000">(</font><b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font>@in2<font color="#990000">),</font> 1633 @out2<font color="#990000">=</font><b><font color="#000000">convert</font></b><font color="#990000">(</font><b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font>@in2<font color="#990000">)</font> 1634</tt></pre> 1635 1636</blockquote> 1637<p>To execute the stored procedure from an SQL Relay program, use code like the 1638following.</p> 1639 1640<blockquote> 1641<!-- Generator: GNU source-highlight 3.1.8 1642by Lorenzo Bettini 1643http://www.lorenzobettini.it 1644http://www.gnu.org/software/src-highlite --> 1645<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"exec exampleproc"</font><font color="#990000">);</font> 1646<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1647<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1648<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"in3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1649<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1650<b><font color="#000000">sqlrcur_defineOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out2"</font><font color="#990000">);</font> 1651<b><font color="#000000">sqlrcur_defineOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out3"</font><font color="#990000">,</font><font color="#993399">20</font><font color="#990000">);</font> 1652<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1653<font color="#008080">int64_t</font> out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out1"</font><font color="#990000">);</font> 1654<font color="#009900">double</font> out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out2"</font><font color="#990000">);</font> 1655<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"out3"</font><font color="#990000">);</font> 1656</tt></pre> 1657 1658</blockquote> 1659<p>To drop the stored procedure, run a query like the following.</p> 1660 1661<blockquote> 1662<!-- Generator: GNU source-highlight 3.1.8 1663by Lorenzo Bettini 1664http://www.lorenzobettini.it 1665http://www.gnu.org/software/src-highlite --> 1666<pre><tt>drop procedure exampleproc 1667</tt></pre> 1668 1669</blockquote> 1670<br/><h4>Firebird</h4> 1671 1672<p>To create the stored procedure, run a query like the following.</p> 1673 1674<blockquote> 1675<!-- Generator: GNU source-highlight 3.1.8 1676by Lorenzo Bettini 1677http://www.lorenzobettini.it 1678http://www.gnu.org/software/src-highlite --> 1679<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font><font color="#008080">in1</font> integer<font color="#990000">,</font> <font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> <b><font color="#000000">returns</font></b> <font color="#990000">(</font><font color="#008080">out1</font> integer<font color="#990000">,</font> <font color="#008080">out2</font> <font color="#009900">float</font><font color="#990000">,</font> <font color="#008080">out3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> as 1680begin 1681 out1<font color="#990000">=</font>in1<font color="#990000">;</font> 1682 out2<font color="#990000">=</font>in2<font color="#990000">;</font> 1683 out3<font color="#990000">=</font>in3<font color="#990000">;</font> 1684 suspend<font color="#990000">;</font> 1685end<font color="#990000">;</font> 1686</tt></pre> 1687 1688</blockquote> 1689<p>To execute the stored procedure from an SQL Relay program, use code like the 1690following.</p> 1691 1692<blockquote> 1693<!-- Generator: GNU source-highlight 3.1.8 1694by Lorenzo Bettini 1695http://www.lorenzobettini.it 1696http://www.gnu.org/software/src-highlite --> 1697<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from exampleproc(?,?,?)"</font><font color="#990000">);</font> 1698<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1699<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1700<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1701<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1702<font color="#009900">char</font> <font color="#990000">*</font>out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1703<font color="#009900">char</font> <font color="#990000">*</font>out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1704<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1705</tt></pre> 1706 1707</blockquote> 1708<p>Alternatively, you can run a query like the following and receive the result 1709using a output bind variables. Note that in Firebird, input and 1710output bind variable indices are distict from one another. The index of the 1711first output bind variable is 1 rather than 4, even though there were 3 input 1712bind variables.</p> 1713 1714<blockquote> 1715<!-- Generator: GNU source-highlight 3.1.8 1716by Lorenzo Bettini 1717http://www.lorenzobettini.it 1718http://www.gnu.org/software/src-highlite --> 1719<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"execute procedure exampleproc ?, ?, ?"</font><font color="#990000">);</font> 1720<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1721<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1722<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1723<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">);</font> 1724<b><font color="#000000">sqlrcur_defineOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">);</font> 1725<b><font color="#000000">sqlrcur_defineOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#993399">20</font><font color="#990000">);</font> 1726<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1727<font color="#008080">int64_t</font> out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">);</font> 1728<font color="#009900">double</font> out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">);</font> 1729<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">);</font> 1730</tt></pre> 1731 1732</blockquote> 1733<p>To drop the stored procedure, run a query like the following.</p> 1734 1735<blockquote> 1736<!-- Generator: GNU source-highlight 3.1.8 1737by Lorenzo Bettini 1738http://www.lorenzobettini.it 1739http://www.gnu.org/software/src-highlite --> 1740<pre><tt>drop procedure exampleproc 1741</tt></pre> 1742 1743</blockquote> 1744<br/><h4>DB2</h4> 1745 1746<p>To create the stored procedure, run a query like the following.</p> 1747 1748<blockquote> 1749<!-- Generator: GNU source-highlight 3.1.8 1750by Lorenzo Bettini 1751http://www.lorenzobettini.it 1752http://www.gnu.org/software/src-highlite --> 1753<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in <font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> in <font color="#008080">in2</font> <font color="#009900">double</font><font color="#990000">,</font> in <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">),</font> out <font color="#008080">out1</font> <font color="#009900">int</font><font color="#990000">,</font> out <font color="#008080">out2</font> <font color="#009900">double</font><font color="#990000">,</font> out <font color="#008080">out3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> language sql 1754begin 1755 <font color="#008080">set</font> out1 <font color="#990000">=</font> in1<font color="#990000">;</font> 1756 <font color="#008080">set</font> out2 <font color="#990000">=</font> in2<font color="#990000">;</font> 1757 <font color="#008080">set</font> out3 <font color="#990000">=</font> in3<font color="#990000">;</font> 1758end 1759</tt></pre> 1760 1761</blockquote> 1762<p>To execute the stored procedure from an SQL Relay program, use code like the 1763following.</p> 1764 1765<blockquote> 1766<!-- Generator: GNU source-highlight 3.1.8 1767by Lorenzo Bettini 1768http://www.lorenzobettini.it 1769http://www.gnu.org/software/src-highlite --> 1770<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc(?,?,?,?,?,?)"</font><font color="#990000">);</font> 1771<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1772<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1773<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1774<b><font color="#000000">sqlrcur_defineOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"4"</font><font color="#990000">);</font> 1775<b><font color="#000000">sqlrcur_defineOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"5"</font><font color="#990000">);</font> 1776sqlrcur_defineOutputBindString<font color="#990000">-(</font>cur<font color="#990000">,</font><font color="#FF0000">"6"</font><font color="#990000">,</font><font color="#993399">25</font><font color="#990000">);</font> 1777<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1778<font color="#008080">int64_t</font> out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindInteger</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"4"</font><font color="#990000">);</font> 1779<font color="#009900">double</font> out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"5"</font><font color="#990000">);</font> 1780<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"6"</font><font color="#990000">);</font> 1781</tt></pre> 1782 1783</blockquote> 1784<p>To drop the stored procedure, run a query like the following.</p> 1785 1786<blockquote> 1787<!-- Generator: GNU source-highlight 3.1.8 1788by Lorenzo Bettini 1789http://www.lorenzobettini.it 1790http://www.gnu.org/software/src-highlite --> 1791<pre><tt>drop procedure exampleproc 1792</tt></pre> 1793 1794</blockquote> 1795<br/><h4>Postgresql</h4> 1796 1797<p>To create the stored procedure, run a query like the following.</p> 1798 1799<blockquote> 1800<!-- Generator: GNU source-highlight 3.1.8 1801by Lorenzo Bettini 1802http://www.lorenzobettini.it 1803http://www.gnu.org/software/src-highlite --> 1804<pre><tt>create <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">(</font><font color="#009900">int</font><font color="#990000">,</font><font color="#009900">float</font><font color="#990000">,</font><font color="#009900">char</font><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> returns <font color="#008080">record</font> as <font color="#FF0000">'</font> 1805<font color="#FF0000">declare</font> 1806<font color="#FF0000"> output record;</font> 1807<font color="#FF0000">begin</font> 1808<font color="#FF0000"> select $1,$2,$3 into output;</font> 1809<font color="#FF0000"> return output;</font> 1810<font color="#FF0000">end;</font> 1811<font color="#FF0000">'</font> language plpgsql 1812</tt></pre> 1813 1814</blockquote> 1815<p>To execute the stored procedure from an SQL Relay program, use code like the 1816following.</p> 1817 1818<blockquote> 1819<!-- Generator: GNU source-highlight 3.1.8 1820by Lorenzo Bettini 1821http://www.lorenzobettini.it 1822http://www.gnu.org/software/src-highlite --> 1823<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from examplefunc(1,2,3) as (col1 int, col2 float, col3 char(20))"</font><font color="#990000">);</font> 1824<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1825<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">4</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1826<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1827<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1828<font color="#009900">char</font> <font color="#990000">*</font>out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1829<font color="#009900">char</font> <font color="#990000">*</font>out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1830<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1831</tt></pre> 1832 1833</blockquote> 1834<p>To drop the stored procedure, run a query like the following.</p> 1835 1836<blockquote> 1837<!-- Generator: GNU source-highlight 3.1.8 1838by Lorenzo Bettini 1839http://www.lorenzobettini.it 1840http://www.gnu.org/software/src-highlite --> 1841<pre><tt>drop <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">(</font><font color="#009900">int</font><font color="#990000">,</font><font color="#009900">float</font><font color="#990000">,</font><font color="#009900">char</font><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> 1842</tt></pre> 1843 1844</blockquote> 1845<h4>MySQL/MariaDB</h4> 1846 1847<p>Here's how you can get multiple values from the result 1848set of a MySQL/MariaDB procedure.</p> 1849 1850<p>To create the stored procedure, run a query like the following.</p> 1851 1852<blockquote> 1853<!-- Generator: GNU source-highlight 3.1.8 1854by Lorenzo Bettini 1855http://www.lorenzobettini.it 1856http://www.gnu.org/software/src-highlite --> 1857<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>in <font color="#008080">in1</font> <font color="#009900">int</font><font color="#990000">,</font> in <font color="#008080">in2</font> <font color="#009900">float</font><font color="#990000">,</font> in <font color="#008080">in3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> begin <font color="#008080">select</font> in1<font color="#990000">,</font> in2<font color="#990000">,</font> in3<font color="#990000">;</font> end<font color="#990000">;</font> 1858</tt></pre> 1859 1860</blockquote> 1861<p>To execute the stored procedure from an SQL Relay program, use code like the 1862following.</p> 1863 1864<blockquote> 1865<!-- Generator: GNU source-highlight 3.1.8 1866by Lorenzo Bettini 1867http://www.lorenzobettini.it 1868http://www.gnu.org/software/src-highlite --> 1869<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc(?,?,?)"</font><font color="#990000">);</font> 1870<b><font color="#000000">sqlrcur_inputBindLong</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"1"</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1871<b><font color="#000000">sqlrcur_inputBindDouble</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"2"</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#993399">4</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1872<b><font color="#000000">sqlrcur_inputBindString</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"3"</font><font color="#990000">,</font><font color="#FF0000">"hello"</font><font color="#990000">);</font> 1873<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1874<font color="#009900">char</font> <font color="#990000">*</font>out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1875<font color="#009900">char</font> <font color="#990000">*</font>out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1876<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1877</tt></pre> 1878 1879</blockquote> 1880<p>Note: Versions of MySQL prior to 5.0 had trouble calling stored procedures using bind variables. If you are using a version of MySQL prior to 5.0 then SQL relay must fake the bind variables and you must use colon-delimited variables (:1, :2, :3, etc.) in your queries rather than the native-mysql queston marks.</p> 1881 1882<p>To drop the stored procedure, run a query like the following.</p> 1883 1884<blockquote> 1885<!-- Generator: GNU source-highlight 3.1.8 1886by Lorenzo Bettini 1887http://www.lorenzobettini.it 1888http://www.gnu.org/software/src-highlite --> 1889<pre><tt>drop procedure exampleproc 1890</tt></pre> 1891 1892</blockquote> 1893<p>Here's how you can get multiple values from the output variables 1894of a MySQL/MariaDB procedure.</p> 1895 1896<p>To create the stored procedure, run a query like the following.</p> 1897 1898<blockquote> 1899<!-- Generator: GNU source-highlight 3.1.8 1900by Lorenzo Bettini 1901http://www.lorenzobettini.it 1902http://www.gnu.org/software/src-highlite --> 1903<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">(</font>out <font color="#008080">out1</font> <font color="#009900">int</font><font color="#990000">,</font> out <font color="#008080">out2</font> <font color="#009900">float</font><font color="#990000">,</font> out <font color="#008080">out3</font> <b><font color="#000000">varchar</font></b><font color="#990000">(</font><font color="#993399">20</font><font color="#990000">))</font> begin select <font color="#993399">1</font><font color="#990000">,</font><font color="#993399">1.1</font><font color="#990000">,</font><font color="#FF0000">'hello'</font> <font color="#008080">into</font> out1<font color="#990000">,</font> out2<font color="#990000">,</font> out3<font color="#990000">;</font> end<font color="#990000">;</font> 1904</tt></pre> 1905 1906</blockquote> 1907<p>To execute the stored procedure from an SQL Relay program, use code like the 1908following.</p> 1909 1910<blockquote> 1911<!-- Generator: GNU source-highlight 3.1.8 1912by Lorenzo Bettini 1913http://www.lorenzobettini.it 1914http://www.gnu.org/software/src-highlite --> 1915<pre><tt><b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"set @out1=0, @out2=0.0, @out3=''"</font><font color="#990000">);</font> 1916<b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc(@out1,@out3,@out3)"</font><font color="#990000">);</font> 1917<b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select @out1,@out2,@out3"</font><font color="#990000">);</font> 1918<font color="#009900">char</font> <font color="#990000">*</font>out1<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1919<font color="#009900">char</font> <font color="#990000">*</font>out2<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1920<font color="#009900">char</font> <font color="#990000">*</font>out3<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1921</tt></pre> 1922 1923</blockquote> 1924<p>To drop the stored procedure, run a query like the following.</p> 1925 1926<blockquote> 1927<!-- Generator: GNU source-highlight 3.1.8 1928by Lorenzo Bettini 1929http://www.lorenzobettini.it 1930http://www.gnu.org/software/src-highlite --> 1931<pre><tt>drop procedure exampleproc 1932</tt></pre> 1933 1934</blockquote> 1935<br/><h3>Result Sets</h3> 1936 1937<p>Some stored procedures return entire result sets. Below are examples, 1938illustrating how to create, execute and drop this kind of stored procedure for 1939each database that SQL Relay supports.</p> 1940 1941<h4>Oracle</h4> 1942 1943<p>To create the stored procedure, run a query like the following.</p> 1944 1945<blockquote> 1946<!-- Generator: GNU source-highlight 3.1.8 1947by Lorenzo Bettini 1948http://www.lorenzobettini.it 1949http://www.gnu.org/software/src-highlite --> 1950<pre><tt>create or replace package types as 1951 type cursorType is <font color="#008080">ref</font> cursor<font color="#990000">;</font> 1952end<font color="#990000">;</font> 1953 1954create function exampleproc <font color="#008080">return</font> types<font color="#990000">.</font>cursortype is 1955 <font color="#008080">l_cursor</font> types<font color="#990000">.</font>cursorType<font color="#990000">;</font> 1956begin 1957 <font color="#008080">open</font> l_cursor <font color="#008080">for</font> select <font color="#990000">*</font> <font color="#008080">from</font> mytable<font color="#990000">;</font> 1958 <b><font color="#0000FF">return</font></b> l_cursor<font color="#990000">;</font> 1959end<font color="#990000">;</font> 1960</tt></pre> 1961 1962</blockquote> 1963<p>To execute the stored procedure from an SQL Relay program, use code like the 1964following.</p> 1965 1966<blockquote> 1967<!-- Generator: GNU source-highlight 3.1.8 1968by Lorenzo Bettini 1969http://www.lorenzobettini.it 1970http://www.gnu.org/software/src-highlite --> 1971<pre><tt><b><font color="#000000">sqlrcur_prepareQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"begin :curs:=exampleproc; end;"</font><font color="#990000">);</font> 1972<b><font color="#000000">sqlrcur_defineOutputBindCursor</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"curs"</font><font color="#990000">);</font> 1973<b><font color="#000000">sqlrcur_executeQuery</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 1974<font color="#008080">sqlrcur</font> bindcur<font color="#990000">=</font><b><font color="#000000">sqlrcur_getOutputBindCursor</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"curs"</font><font color="#990000">);</font> 1975<b><font color="#000000">sqlrcur_fetchFromBindCursor</font></b><font color="#990000">(</font>bindcur<font color="#990000">);</font> 1976<font color="#009900">char</font> <font color="#990000">*</font>field00<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>bindcur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1977<font color="#009900">char</font> <font color="#990000">*</font>field01<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>bindcur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1978<font color="#009900">char</font> <font color="#990000">*</font>field02<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>bindcur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1979<font color="#009900">char</font> <font color="#990000">*</font>field10<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>bindcur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 1980<font color="#009900">char</font> <font color="#990000">*</font>field11<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>bindcur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 1981<font color="#009900">char</font> <font color="#990000">*</font>field12<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>bindcur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 1982</tt></pre> 1983 1984</blockquote> 1985<p>To drop the stored procedure, run a query like the following.</p> 1986 1987<blockquote> 1988<!-- Generator: GNU source-highlight 3.1.8 1989by Lorenzo Bettini 1990http://www.lorenzobettini.it 1991http://www.gnu.org/software/src-highlite --> 1992<pre><tt>drop function exampleproc 1993drop package types 1994</tt></pre> 1995 1996</blockquote> 1997<br/><h4>Sybase and Microsoft SQL Server</h4> 1998 1999<p>To create the stored procedure, run a query like the following.</p> 2000 2001<blockquote> 2002<!-- Generator: GNU source-highlight 3.1.8 2003by Lorenzo Bettini 2004http://www.lorenzobettini.it 2005http://www.gnu.org/software/src-highlite --> 2006<pre><tt>create procedure exampleproc <font color="#008080">as</font> select <font color="#990000">*</font> from exampletable 2007</tt></pre> 2008 2009</blockquote> 2010<p>To exceute the stored procedure from an SQL Relay program, ue code like the 2011following.</p> 2012 2013<blockquote> 2014<!-- Generator: GNU source-highlight 3.1.8 2015by Lorenzo Bettini 2016http://www.lorenzobettini.it 2017http://www.gnu.org/software/src-highlite --> 2018<pre><tt><b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"exec exampleproc"</font><font color="#990000">);</font> 2019<font color="#009900">char</font> <font color="#990000">*</font>field00<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 2020<font color="#009900">char</font> <font color="#990000">*</font>field01<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2021<font color="#009900">char</font> <font color="#990000">*</font>field02<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 2022<font color="#009900">char</font> <font color="#990000">*</font>field10<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 2023<font color="#009900">char</font> <font color="#990000">*</font>field11<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2024<font color="#009900">char</font> <font color="#990000">*</font>field12<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 2025</tt></pre> 2026 2027</blockquote> 2028<p>To drop the stored procedure, run a query like the following.</p> 2029 2030<blockquote> 2031<!-- Generator: GNU source-highlight 3.1.8 2032by Lorenzo Bettini 2033http://www.lorenzobettini.it 2034http://www.gnu.org/software/src-highlite --> 2035<pre><tt>drop procedure exampleproc 2036</tt></pre> 2037 2038</blockquote> 2039<br/><h4>Firebird</h4> 2040 2041<p>Stored procedures in Firebird can return a result set if a 2042select query in the procedure selects values into the output parameters and 2043then issues a suspend command, however SQL Relay doesn't currently support 2044stored procedures that return result sets.</p> 2045 2046<h4>DB2</h4> 2047 2048<p>Stored procedures in DB2 can return a result set if the procedure is declared 2049to return one, however SQL Relay doesn't currently support stored procedures 2050that return result sets.</p> 2051 2052<h4>Postgresql</h4> 2053 2054<p>To create the stored procedure, run a query like the following.</p> 2055 2056<blockquote> 2057<!-- Generator: GNU source-highlight 3.1.8 2058by Lorenzo Bettini 2059http://www.lorenzobettini.it 2060http://www.gnu.org/software/src-highlite --> 2061<pre><tt>create <font color="#008080">function</font> <b><font color="#000000">examplefunc</font></b><font color="#990000">()</font> returns setof <font color="#008080">record</font> as <font color="#FF0000">'</font> 2062<font color="#FF0000"> declare output record;</font> 2063<font color="#FF0000">begin</font> 2064<font color="#FF0000"> for output in select * from mytable loop</font> 2065<font color="#FF0000"> return next output;</font> 2066<font color="#FF0000"> end loop;</font> 2067<font color="#FF0000"> return;</font> 2068<font color="#FF0000">end;</font> 2069<font color="#FF0000">'</font> language plpgsql 2070</tt></pre> 2071 2072</blockquote> 2073<p>To execute the stored procedure from an SQL Relay program, use code like the 2074following.</p> 2075 2076<blockquote> 2077<!-- Generator: GNU source-highlight 3.1.8 2078by Lorenzo Bettini 2079http://www.lorenzobettini.it 2080http://www.gnu.org/software/src-highlite --> 2081<pre><tt><b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from examplefunc() as (exampleint int, examplefloat float, examplechar char(40))"</font><font color="#990000">);</font> 2082<font color="#009900">char</font> <font color="#990000">*</font>field00<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 2083<font color="#009900">char</font> <font color="#990000">*</font>field01<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2084<font color="#009900">char</font> <font color="#990000">*</font>field02<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 2085<font color="#009900">char</font> <font color="#990000">*</font>field10<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 2086<font color="#009900">char</font> <font color="#990000">*</font>field11<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2087<font color="#009900">char</font> <font color="#990000">*</font>field12<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 2088</tt></pre> 2089 2090</blockquote> 2091<p>To drop the stored procedure, run a query like the following.</p> 2092 2093<blockquote> 2094<!-- Generator: GNU source-highlight 3.1.8 2095by Lorenzo Bettini 2096http://www.lorenzobettini.it 2097http://www.gnu.org/software/src-highlite --> 2098<pre><tt>drop function examplefunc 2099</tt></pre> 2100 2101</blockquote> 2102<h4>MySQL/MariaDB</h4> 2103 2104<p>The result sets of all select statements called within MySQL/MariaDB stored 2105procedures (that aren't selected into variables) are returned from the procedure 2106call. Though MySQL/MariaDB stored procedures can return multiple result sets, 2107currently SQL Relay can only fetch the first result set.</p> 2108 2109<p>To create the stored procedure which returns a result set, run a query like 2110the following.</p> 2111 2112<blockquote> 2113<!-- Generator: GNU source-highlight 3.1.8 2114by Lorenzo Bettini 2115http://www.lorenzobettini.it 2116http://www.gnu.org/software/src-highlite --> 2117<pre><tt>create <font color="#008080">procedure</font> <b><font color="#000000">exampleproc</font></b><font color="#990000">()</font> <font color="#008080">begin</font> select <font color="#990000">*</font> <font color="#008080">from</font> mytable<font color="#990000">;</font> end<font color="#990000">;</font> 2118</tt></pre> 2119 2120</blockquote> 2121<p>To execute the stored procedure from an SQL Relay program, use code like the 2122following.</p> 2123 2124<blockquote> 2125<!-- Generator: GNU source-highlight 3.1.8 2126by Lorenzo Bettini 2127http://www.lorenzobettini.it 2128http://www.gnu.org/software/src-highlite --> 2129<pre><tt><b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"call exampleproc()"</font><font color="#990000">);</font> 2130<font color="#009900">char</font> <font color="#990000">*</font>field00<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 2131<font color="#009900">char</font> <font color="#990000">*</font>field01<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2132<font color="#009900">char</font> <font color="#990000">*</font>field02<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 2133<font color="#009900">char</font> <font color="#990000">*</font>field10<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">);</font> 2134<font color="#009900">char</font> <font color="#990000">*</font>field11<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2135<font color="#009900">char</font> <font color="#990000">*</font>field12<font color="#990000">=</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">1</font><font color="#990000">,</font><font color="#993399">2</font><font color="#990000">);</font> 2136</tt></pre> 2137 2138</blockquote> 2139<p>To drop the stored procedure, run a query like the following.</p> 2140 2141<blockquote> 2142<!-- Generator: GNU source-highlight 3.1.8 2143by Lorenzo Bettini 2144http://www.lorenzobettini.it 2145http://www.gnu.org/software/src-highlite --> 2146<pre><tt>drop procedure exampleproc 2147</tt></pre> 2148 2149</blockquote> 2150<br/><a name="caching"/><h2>Caching The Result Set</h2> 2151 2152<p>Say you're writing a web-based report where a query with a huge result set 2153is executed and 20 rows are displayed per page. Rather than rerunning the 2154query for every page every time and dumping all but the 20 rows you want to 2155display, you can run the query once, cache the result set to a local file and 2156just open the file for each page of the report.</p> 2157 2158<p>First page:</p> 2159 2160<blockquote> 2161<!-- Generator: GNU source-highlight 3.1.8 2162by Lorenzo Bettini 2163http://www.lorenzobettini.it 2164http://www.gnu.org/software/src-highlite --> 2165<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 2166<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 2167 2168<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 2169 2170 <font color="#009900">char</font> <font color="#990000">*</font>filename<font color="#990000">;</font> 2171 2172 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2173 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2174 2175 <font color="#990000">...</font> generate a unique <font color="#008080">file</font> name <font color="#990000">...</font> 2176 2177 <b><font color="#000000">sqlrcur_cacheToFile</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>filename<font color="#990000">);</font> 2178 <b><font color="#000000">sqlrcur_setCacheTtl</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#993399">600</font><font color="#990000">);</font> 2179 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"select * from my_table"</font><font color="#990000">);</font> 2180 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2181 <b><font color="#000000">sqlrcur_cacheOff</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2182 2183 <font color="#990000">...</font> pass the filename to the <font color="#008080">next</font> page <font color="#990000">...</font> 2184 2185 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2186 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2187<font color="#FF0000">}</font> 2188</tt></pre> 2189 2190</blockquote> 2191<p>Second page:</p> 2192 2193<blockquote> 2194<!-- Generator: GNU source-highlight 3.1.8 2195by Lorenzo Bettini 2196http://www.lorenzobettini.it 2197http://www.gnu.org/software/src-highlite --> 2198<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 2199<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 2200 2201<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 2202 2203 <font color="#009900">int</font> row<font color="#990000">,</font>col<font color="#990000">;</font> 2204 2205 <font color="#990000">...</font> get the filename from the <font color="#008080">previous</font> page <font color="#990000">...</font> 2206 2207 <font color="#990000">...</font> get the page to display from the <font color="#008080">previous</font> page <font color="#990000">...</font> 2208 2209 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2210 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2211 2212 <b><font color="#000000">sqlrcur_openCachedResultSet</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>filename<font color="#990000">);</font> 2213 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2214 2215 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>row<font color="#990000">=</font>pagetodisplay<font color="#990000">*</font><font color="#993399">20</font><font color="#990000">;</font> row<font color="#990000"><(</font>pagetodisplay<font color="#990000">+</font><font color="#993399">1</font><font color="#990000">)*</font><font color="#993399">20</font><font color="#990000">;</font> row<font color="#990000">++)</font> <font color="#FF0000">{</font> 2216 <b><font color="#0000FF">for</font></b> <font color="#990000">(</font>col<font color="#990000">=</font><font color="#993399">0</font><font color="#990000">;</font> col<font color="#990000"><</font><b><font color="#000000">sqlrcur_colCount</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> col<font color="#990000">++)</font> <font color="#FF0000">{</font> 2217 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"%s,"</font><font color="#990000">,</font><b><font color="#000000">sqlrcur_getFieldByIndex</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>row<font color="#990000">,</font>col<font color="#990000">));</font> 2218 <font color="#FF0000">}</font> 2219 <b><font color="#000000">printf</font></b><font color="#990000">(</font><font color="#FF0000">"</font><font color="#CC33CC">\n</font><font color="#FF0000">"</font><font color="#990000">);</font> 2220 <font color="#FF0000">}</font> 2221 2222 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2223 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2224<font color="#FF0000">}</font> 2225</tt></pre> 2226 2227</blockquote> 2228<p>For result-set caching to be effective, the sqlr-cachemanager process must be enabled and running on the client system. The sqlr-cachemanager scans the cache periodically and deletes cached result sets whos ttl's have expired. If it is not running, stale result sets persist beyond their intended ttl's.</p> 2229 2230<p>To enable the sqlr-cachemanager at boot on systemd platforms:</p> 2231 2232<blockquote> 2233<!-- Generator: GNU source-highlight 3.1.8 2234by Lorenzo Bettini 2235http://www.lorenzobettini.it 2236http://www.gnu.org/software/src-highlite --> 2237<pre><tt>systemctl <font color="#008080">enable</font> sqlrcachemanager<font color="#990000">.</font>service</tt></pre> 2238 2239</blockquote> 2240<p>To start the sqlr-cachemanager on systemd platforms:</p> 2241 2242<blockquote> 2243<!-- Generator: GNU source-highlight 3.1.8 2244by Lorenzo Bettini 2245http://www.lorenzobettini.it 2246http://www.gnu.org/software/src-highlite --> 2247<pre><tt>systemctl <font color="#008080">start</font> sqlrcachemanager<font color="#990000">.</font>service</tt></pre> 2248 2249</blockquote> 2250<p>To enable the sqlr-cachemanager at boot on most non-systemd platforms, you must create a symlink into the /etc/rc2.d or /etc/rc3.d directory. Eg:</p> 2251 2252<blockquote> 2253<!-- Generator: GNU source-highlight 3.1.8 2254by Lorenzo Bettini 2255http://www.lorenzobettini.it 2256http://www.gnu.org/software/src-highlite --> 2257<pre><tt>cd <font color="#990000">/</font>etc<font color="#990000">/</font>rc2<font color="#990000">.</font>d 2258ln <font color="#990000">-</font>s <font color="#990000">../</font>init<font color="#990000">.</font>d<font color="#990000">/</font>sqlrcachemanager S15sqlrcachemanager</tt></pre> 2259 2260 <p>or</p> 2261 2262<!-- Generator: GNU source-highlight 3.1.8 2263by Lorenzo Bettini 2264http://www.lorenzobettini.it 2265http://www.gnu.org/software/src-highlite --> 2266<pre><tt>cd <font color="#990000">/</font>etc<font color="#990000">/</font>rc3<font color="#990000">.</font>d 2267ln <font color="#990000">-</font>s <font color="#990000">../</font>init<font color="#990000">.</font>d<font color="#990000">/</font>sqlrcachemanager S15sqlrcachemanager</tt></pre> 2268 2269</blockquote> 2270<p>To start the sqlr-cachemanager on most non-systemd platforms:</p> 2271 2272<blockquote> 2273<!-- Generator: GNU source-highlight 3.1.8 2274by Lorenzo Bettini 2275http://www.lorenzobettini.it 2276http://www.gnu.org/software/src-highlite --> 2277<pre><tt><font color="#990000">/</font>etc<font color="#990000">/</font>init<font color="#990000">.</font>d<font color="#990000">/</font>sqlrcachemanager start</tt></pre> 2278 2279</blockquote> 2280<p>To enable the sqlr-cachemanager at boot on FreeBSD platforms, edit /etc/rc.conf and add a line like:</p> 2281 2282<blockquote> 2283<!-- Generator: GNU source-highlight 3.1.8 2284by Lorenzo Bettini 2285http://www.lorenzobettini.it 2286http://www.gnu.org/software/src-highlite --> 2287<pre><tt>sqlrcachemanager_enable<font color="#990000">=</font>YES</tt></pre> 2288 2289</blockquote> 2290<p>To enable the sqlr-cachemanager at boot on NetBSD platforms, edit /etc/rc.conf and add a line like:</p> 2291 2292<blockquote> 2293<!-- Generator: GNU source-highlight 3.1.8 2294by Lorenzo Bettini 2295http://www.lorenzobettini.it 2296http://www.gnu.org/software/src-highlite --> 2297<pre><tt>sqlrcachemanager<font color="#990000">=</font>YES</tt></pre> 2298 2299</blockquote> 2300<p>To enable the sqlr-cachemanager at boot on OpenBSD platforms, edit /etc/rc.conf and add a line like:</p> 2301 2302<blockquote> 2303<!-- Generator: GNU source-highlight 3.1.8 2304by Lorenzo Bettini 2305http://www.lorenzobettini.it 2306http://www.gnu.org/software/src-highlite --> 2307<pre><tt>sqlrcachemanager_flags<font color="#990000">=</font>YES</tt></pre> 2308 2309</blockquote> 2310<p>To start the sqlr-cachemanager on BSD platforms:</p> 2311 2312<blockquote> 2313<!-- Generator: GNU source-highlight 3.1.8 2314by Lorenzo Bettini 2315http://www.lorenzobettini.it 2316http://www.gnu.org/software/src-highlite --> 2317<pre><tt><font color="#990000">/</font>etc<font color="#990000">/</font>init<font color="#990000">.</font>d<font color="#990000">/</font>sqlrcachemanager start</tt></pre> 2318 2319</blockquote> 2320<br/><a name="suspending"/><h2>Suspending and Resuming Sessions</h2> 2321 2322<p>Sometimes web-based applications need a single database transaction to span 2323multiple pages. Since SQL Relay sessions can be suspended and resumed, this 2324is possible.</p> 2325 2326<p>First page:</p> 2327 2328<blockquote> 2329<!-- Generator: GNU source-highlight 3.1.8 2330by Lorenzo Bettini 2331http://www.lorenzobettini.it 2332http://www.gnu.org/software/src-highlite --> 2333<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 2334<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 2335 2336<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 2337 2338 <font color="#009900">int</font> port<font color="#990000">;</font> 2339 <font color="#009900">char</font> <font color="#990000">*</font>socket<font color="#990000">;</font> 2340 <font color="#009900">int</font> rs<font color="#990000">;</font> 2341 2342 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2343 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2344 2345 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"insert into my_table values (1,2,3)"</font><font color="#990000">);</font> 2346 <b><font color="#000000">sqlrcur_suspendResultSet</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2347 <b><font color="#000000">sqlrcon_suspendSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2348 rs<font color="#990000">=</font><b><font color="#000000">sqlrcur_getResultSetId</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2349 port<font color="#990000">=</font><b><font color="#000000">getConnectionPort</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2350 socket<font color="#990000">=</font><b><font color="#000000">getConnectionSocket</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2351 2352 <font color="#990000">...</font> pass <font color="#008080">the</font> rs<font color="#990000">,</font> port and socket to the <font color="#008080">next</font> page <font color="#990000">...</font> 2353 2354 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2355 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2356<font color="#FF0000">}</font> 2357</tt></pre> 2358 2359</blockquote> 2360<p>Second page:</p> 2361 2362<blockquote> 2363<!-- Generator: GNU source-highlight 3.1.8 2364by Lorenzo Bettini 2365http://www.lorenzobettini.it 2366http://www.gnu.org/software/src-highlite --> 2367<pre><tt><b><font color="#000080">#include</font></b> <font color="#FF0000"><sqlrelay/sqlrclientwrapper.h></font> 2368<b><font color="#000080">#include</font></b> <font color="#FF0000"><stdio.h></font> 2369 2370<b><font color="#000000">main</font></b><font color="#990000">()</font> <font color="#FF0000">{</font> 2371 2372 <font color="#990000">...</font> <font color="#008080">get</font> rs<font color="#990000">,</font> port and socket from <font color="#008080">previous</font> page <font color="#990000">...</font> 2373 2374 <font color="#008080">sqlrcon</font> con<font color="#990000">=</font><b><font color="#000000">sqlrcon_alloc</font></b><font color="#990000">(</font><font color="#FF0000">"sqlrserver"</font><font color="#990000">,</font><font color="#993399">9000</font><font color="#990000">,</font><font color="#FF0000">"/tmp/example.socket"</font><font color="#990000">,</font><font color="#FF0000">"user"</font><font color="#990000">,</font><font color="#FF0000">"password"</font><font color="#990000">,</font><font color="#993399">0</font><font color="#990000">,</font><font color="#993399">1</font><font color="#990000">);</font> 2375 <font color="#008080">sqlrcur</font> cur<font color="#990000">=</font><b><font color="#000000">sqlrcur_alloc</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2376 2377 <b><font color="#000000">sqlrcon_resumeSession</font></b><font color="#990000">(</font>con<font color="#990000">,</font>port<font color="#990000">,</font>socket<font color="#990000">);</font> 2378 <b><font color="#000000">sqlrcur_resumeResultSet</font></b><font color="#990000">(</font>cur<font color="#990000">,</font>rs<font color="#990000">);</font> 2379 <b><font color="#000000">sqlrcur_sendQuery</font></b><font color="#990000">(</font>cur<font color="#990000">,</font><font color="#FF0000">"commit"</font><font color="#990000">);</font> 2380 <b><font color="#000000">sqlrcon_endSession</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2381 2382 <b><font color="#000000">sqlrcur_free</font></b><font color="#990000">(</font>cur<font color="#990000">);</font> 2383 <b><font color="#000000">sqlrcon_free</font></b><font color="#990000">(</font>con<font color="#990000">);</font> 2384<font color="#FF0000">}</font> 2385</tt></pre> 2386 2387</blockquote> 2388<p>You can also distribute the processing of a result set across a series of 2389pages using suspended sessions. If you're buffering a result set in chunks 2390instead of all at once and suspend a session, when you resume the session you 2391can continue to retrieve rows from the result set.</p> 2392 2393<p>Similarly, if you're buffering a result set in chunks, caching that 2394result set and suspend your session. When you resume the session, you can 2395continue caching the result set. You must use sqlrcur_resumeCachedResultSet() 2396instead of sqlrcur_resumeResultSet() however.</p> 2397 2398<a name="lastinsertid"/><h2>Getting the Last Insert ID</h2> 2399 2400<p>Databases with autoincrement or identity columns often provide functions which return the "last insert id"; the value of the autoincrement column that was generated during the insert into the database.</p> 2401 2402<p>SQL Relay provides the sqlrcon_getLastInsertId() function to get this value.</p> 2403 2404<p>When using the SQLite database, you can also get the last insert id by running the query:</p> 2405 2406<blockquote> 2407<b>select last insert rowid</b> 2408</blockquote> 2409</body> 2410</html> 2411