"Fossies" - the Fresh Open Source Software Archive

Member "sqlrelay-1.5.2/doc/programming/c.wt" (23 Apr 2019, 36603 Bytes) of package /linux/privat/sqlrelay-1.5.2.tar.gz:


As a special service "Fossies" has tried to format the requested text file into HTML format (style: standard) with prefixed line numbers. Alternatively you can here view or download the uninterpreted source code file. See also the last Fossies "Diffs" side-by-side code changes report for "c.wt": 1.3.0_vs_1.4.0.

    1 = Programming with SQL Relay using the C API =
    2 
    3 * [#compiling Compiling an SQL Relay Client Program]
    4 * [#session Establishing a Session]
    5 * [#query Executing Queries]
    6 * [#commit Commits and Rollbacks]
    7 * [#temptables Temporary Tables]
    8 * [#errors Catching Errors]
    9 * [#bindvars Substitution and Bind Variables]
   10 * [#rebinding Re-Binding and Re-Executing]
   11 * [#fields Accessing Fields in the Result Set]
   12 * [#largeresultsets Dealing With Large Result Set]
   13 * [#cursors Cursors]
   14 * [#columns Getting Column Information]
   15 * [#storedprocedures Stored Procedures]
   16 * [#caching Caching The Result Set]
   17 * [#suspending Suspending and Resuming Sessions]
   18 * [#lastinsertid Getting the Last Insert ID]
   19 
   20 
   21 [=#compiling]
   22 == Compiling an SQL Relay Client Program ==
   23 
   24 When writing an SQL Relay client program using the C API, you need to
   25 include the sqlrclientwrapper.h file.
   26 
   27 {{{#!blockquote
   28 {{{#!code
   29 @parts/c-include.c@
   30 }}}
   31 }}}
   32 
   33 You'll also need to link against the sqlrclientwrapper, sqlrclient and 
   34 rudiments libraries.
   35 
   36 On Linux and Unix, the header files are usually found under
   37 /usr/local/firstworks/include and the libraries are usually found in 
   38 /usr/local/firstworks/lib.
   39 
   40 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.
   41 
   42 The command to compile your .c file to object code will look something like
   43 this (assuming you're using the GNU C compiler on Linux or Unix):
   44 
   45 {{{#!blockquote
   46 {{{#!code
   47 gcc -I/usr/local/firstworks/include -c myprogram.c
   48 }}}
   49 }}}
   50 
   51 The command to compile your .o file to an executable will look something like
   52 this (assuming you're using the GNU C++ compiler on Linux or Unix):
   53 
   54 {{{#!blockquote
   55 {{{#!code
   56 g++ -o myprogram myprogram.o -L/usr/local/firstworks/lib -lsqlrclientwrapper -lsqlrclient -lrudiments
   57 }}}
   58 }}}
   59 
   60 Note that g++ was used to link, not gcc.  You could alternatively link using
   61 gcc like this:
   62 
   63 {{{#!blockquote
   64 {{{#!code
   65 gcc -o myprogram myprogram.o -L/usr/local/firstworks/lib -lsqlrclientwrapper -lsqlrclient -lrudiments -lstdc++
   66 }}}
   67 }}}
   68 
   69 When using the C API, it is important to compile the .c files to object code
   70 using the C compiler before linking them using the C++ compiler.  
   71 Compiling/Linking in one step using the C++ compiler the will most likely fail
   72 as it will generate C++ style symbols for function calls which will not be 
   73 resolved in the sqlrclientwrapper library since it contains C style function 
   74 symbols.  Compiling to object code using the C compiler as a seperate step
   75 ensures that C style symbols will be generated for function calls.
   76 
   77 [=#session]
   78 == Establishing a Session ==
   79 
   80 To use SQL Relay, you have to identify the connection that you intend to 
   81 use.
   82 
   83 {{{#!blockquote
   84 {{{#!code
   85 @parts/c-session.c@
   86 }}}
   87 }}}
   88 
   89 After calling the constructor, a session is established when the first 
   90 query is run.
   91 
   92 For the duration of the session, the client occupies one of the database
   93 connections, so care should be taken to minimize the length of a
   94 session.
   95 
   96 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.
   97 
   98 See the [../api/c/html/group_sqlrclientwrapper.html C API reference] for information about these functions and the SQL Relay Configuration Guide for more information about [../admin/configguide.html#krb Kerberos/Active Directory] and [../admin/configguide.html#tls TLS/SSL] configurations.  In particular, note that user and password are not typically used when using Kerberos/AD.
   99 
  100 [=#query]
  101 == Executing Queries ==
  102 
  103 Call sqlrcur_sendQuery() or sqlrcur_sendFileQuery() to run a query.
  104 
  105 {{{#!blockquote
  106 {{{#!code
  107 @parts/c-execute.c@
  108 }}}
  109 }}}
  110 
  111 Note the call to sqlrcon_endSession() after the call to sqlrcur_sendFileQuery().
  112 Since the program does some stuff that takes a long time between that query and
  113 the next, ending the session there allows another client an opportunity to use 
  114 that database connection while your client is busy.  The next call to 
  115 sqlrcur_sendQuery() establishes another session.  Since the program does some 
  116 stuff that takes a short time between the first two queries, it's OK to leave 
  117 the session open between them.
  118 
  119 [=#commit]
  120 == Commits and Rollbacks ==
  121 
  122 If you need to execute a commit or rollback, you should use the 
  123 sqlrcon_commit() and sqlrcon_rollback() functions rather than sending a "commit"
  124 or "rollback" query.  There are two reasons for this.  First, it's much more 
  125 efficient to call the functions.  Second, if you're writing code that can run on 
  126 transactional or non-transactional databases, some non-transactional databases 
  127 will throw errors if they receive a "commit" or "rollback" query, but by calling
  128 the sqlrcon_commit() and sqlrcon_rollback() functions you instruct the database
  129 connection daemon to call the commit and rollback API functions for that database
  130 rather than issuing them as queries.  If the API's have no commit or rollback 
  131 functions, the calls do nothing and the database throws no error.
  132 
  133 You can also turn Autocommit on or off with the sqlrcon_autoCommitOn() and 
  134 sqlrcon_autoCommitOff() functions.  When Autocommit is on, the database 
  135 performs a commit after each successful DML or DDL query.  When Autocommit is 
  136 off, the database commits when the client instructs it to, or (by default) when
  137 a client disconnects.  For databases that don't support Autocommit, 
  138 sqlrcon_autoCommitOn() and sqlrcon_autoCommitOff() have no effect.
  139 
  140 [=#temptables]
  141 == Temporary Tables ==
  142 
  143 Some databases support temporary tables.  That is, tables which are
  144 automatically dropped or truncated when an application closes its connection
  145 to the database or when a transaction is committed or rolled back.
  146 
  147 For databases which drop or truncate tables when a transaction is committed
  148 or rolled back, temporary tables work naturally.
  149 
  150 However, for databases which drop or truncate tables when an application
  151 closes its connection to the database, there is an issue.  Since SQL Relay
  152 maintains persistent database connections, when an application disconnects from
  153 SQL Relay, the connection between SQL Relay and the database remains, so
  154 the database does not know to drop or truncate the table.  To remedy this
  155 situation, SQL Relay parses each query to see if it created a temporary table,
  156 keeps a list of temporary tables and drops (or truncates them) when the
  157 application disconnects from SQL Relay.  Since each database has slightly
  158 different syntax for creating a temporary table, SQL Relay parses each query
  159 according to the rules for that database.
  160 
  161 In effect, temporary tables should work when an application connects to
  162 SQL Relay in the same manner that they would work if the application connected
  163 directly to the database.
  164 
  165 [=#errors]
  166 == Catching Errors ==
  167 
  168 If your call to sqlrcur_sendQuery() or sqlrcur_sendFileQuery() returns a 0, the 
  169 query failed.  You can find out why by calling sqlrcur_errorMessage().
  170 
  171 {{{#!blockquote
  172 {{{#!code
  173 @parts/c-errors.c@
  174 }}}
  175 }}}
  176 
  177 
  178 [=#bindvars]
  179 == Substitution and Bind Variables ==
  180 
  181 Programs rarely execute fixed queries.  More often than not, some part
  182 of the query is dynamically generated.  The SQL Relay API provides functions for
  183 making substitutions and binds in those queries.
  184 
  185 For a detailed discussion of substitutions and binds, see
  186 [binds.html this document].
  187 
  188 Rather than just calling sendQuery() you call prepareQuery(),
  189 substitution(), inputBind() and executeQuery().  If you using queries
  190 stored in a file, you can call prepareFileQuery() instead of prepareQuery().
  191 
  192 When passing a floating point number in as a bind or substitution variable,
  193 you have to supply precision and scale for the number.  See
  194 [precisionscale.html this page] for a discussion of precision and
  195 scale.
  196 
  197 {{{#!blockquote
  198 {{{#!code
  199 @parts/c-bind.c@
  200 }}}
  201 }}}
  202 
  203 If you are curious how many bind variables have been declared in a query,
  204 you can call countBindVariables() after preparing the query.
  205 
  206 If you're using a database with an embedded procedural language, you may
  207 want to retrieve data from function calls.  To facilitate this, SQL Relay
  208 provides functions for defining and retrieving output bind variables.
  209 
  210 {{{#!blockquote
  211 {{{#!code
  212 @parts/c-bind-out.c@
  213 }}}
  214 }}}
  215 
  216 The getOutputBindString() function returns a NULL value as an empty string.  
  217 If you would it to come back as a NULL instead, you can call the 
  218 getNullsAsNulls() function.  To revert to the default behavior, you can
  219 call getNullsAsEmptyStrings().
  220 
  221 You can insert data into BLOB and CLOB columns using the inputBindBlob(), inputBindClob() functions.
  222 
  223 {{{#!blockquote
  224 {{{#!code
  225 @parts/c-bind-lob.c@
  226 }}}
  227 }}}
  228 
  229 Likewise, you can retreive BLOB or CLOB data using
  230 defineOutputBindBlob()/getOutputBindBlob() and
  231 defineOutputBindClob()/getOutputBindClob().
  232 
  233 {{{#!blockquote
  234 {{{#!code
  235 @parts/c-bind-lob-out.c@
  236 }}}
  237 }}}
  238 
  239 Sometimes it's convenient to bind a bunch of variables that may or may not 
  240 actually be in the query.  For example, if you are building a web based
  241 application, it may be easy to just bind all the form variables/values from the
  242 previous page, even though some of them don't appear in the query.  Databases
  243 usually generate errors in this case.  Calling validateBinds() just prior to
  244 calling executeQuery() causes the API to check the query for each bind variable
  245 before actually binding it, preventing those kinds of errors.   You can also
  246 call validBind() to see if a specific variable is valid.  However there is
  247 a performance cost associated with calling validateBinds() and validBind().
  248 
  249 [=#rebinding]
  250 == Re-Binding and Re-Execution ==
  251 
  252 Another feature of the prepare/bind/execute paradigm is the ability to 
  253 prepare, bind and execute a query once, then re-bind and re-execute the query 
  254 over and over without re-preparing it.  If your backend database natively 
  255 supports this paradigm, you can reap a substantial performance improvement.
  256 
  257 {{{#!blockquote
  258 {{{#!code
  259 @parts/c-reexecute.c@
  260 }}}
  261 }}}
  262 
  263 
  264 [=#fields]
  265 == Accessing Fields in the Result Set ==
  266 
  267 The sqlrcur_rowCount(), sqlrcur_colCount(), sqlrcur_getFieldByName() and
  268 sqlrcur_getFieldByIndex() functions are useful for processing result sets.
  269 
  270 {{{#!blockquote
  271 {{{#!code
  272 @parts/c-fields.c@
  273 }}}
  274 }}}
  275 
  276 The sqlrcur_getFieldByName() and sqlrcur_getFieldByIndex() functions return
  277 strings.  If you would like to get a field as a long or double, you can use
  278 sqlrcur_getFieldAsLongByName()/sqlrcur_getFieldAsLongByIndex() and
  279 sqlrcur_getFieldAsDoubleByName()/sqlrcur_getFieldAsDoubleByIndex().
  280 
  281 You can also use sqlrcur_getRow() which returns a NULL-terminated array of
  282 the fields in the row.
  283 
  284 {{{#!blockquote
  285 {{{#!code
  286 @parts/c-fields-row.c@
  287 }}}
  288 }}}
  289 
  290 The sqlrcur_getFieldByIndex(), sqlrcur_getFieldByName() and sqlrcur_getRow() 
  291 functions return NULL fields as empty strings.  If you would like them to come 
  292 back as NULL's instead, you can call the sqlrcur_getNullsAsNulls() function.  To 
  293 revert to the default behavior, you can call sqlrcur_getNullsAsEmptyStrings().
  294 
  295 If you want to access the result set, but don't care about the
  296 column information (column names, types or sizes) and don't mind getting
  297 fields by their numeric index instead of by name,  you can call the
  298 sqlrcur_dontGetColumnInfo() function prior to executing your query.  This can 
  299 result in a performance improvement, especially when many queries with small
  300 result sets are executed in rapid succession.  You can call 
  301 sqlrcur_getColumnInfo() again later to turn off this feature.
  302 
  303 [=#largeresultsets]
  304 == Dealing With Large Result Sets ==
  305 
  306 SQL Relay normally buffers the entire result set.  This can speed things up 
  307 at the cost of memory.  With large enough result sets, it makes sense to buffer the result set in chunks instead of all at once.
  308 
  309 Use sqlrcur_setResultSetBufferSize() to set the number of rows to buffer at a 
  310 time.  Calls to sqlrcur_getRow(), sqlrcur_getFieldByIndex() and 
  311 sqlrcur_getFieldByName() cause the chunk containing the requested field to be 
  312 fetched.  Rows in that chunk are accessible but rows before it are not.
  313 
  314 For example, if you setResultSetBufferSize(5) and execute a query that 
  315 returns 20 rows, rows 0-4 are available at once, then rows 5-9, then 10-14, 
  316 then 15-19.  When rows 5-9 are available, getFieldByIndex(0,0) will return NULL
  317 and getFieldByIndex(11,0) will cause rows 10-14 to be fetched and return the 
  318 requested value.
  319 
  320 When buffering the result set in chunks, don't end the session until after
  321 you're done with the result set.
  322 
  323 If you call sqlrcur_setResultSetBufferSize() and forget what you set it to, 
  324 you can always call sqlrcur_getResultSetBufferSize().
  325 
  326 When buffering a result set in chunks, the sqlrcur_rowCount() function returns
  327 the number of rows returned so far.  The sqlrcur_firstRowIndex() function returns 
  328 the index of the first row of the currently buffered chunk.
  329 
  330 {{{#!blockquote
  331 {{{#!code
  332 @parts/c-rsbuffersize.c@
  333 }}}
  334 }}}
  335 
  336 [=#cursors]
  337 == Cursors ==
  338 
  339 Cursors make it possible to execute queries while processing the result
  340 set of another query.  You can select rows from a table in one query, then 
  341 iterate through its result set, inserting rows into another table, using only 
  342 1 database connection for both operations.
  343 
  344 For example:
  345 
  346 {{{#!blockquote
  347 {{{#!code
  348 @parts/c-cursors.c@
  349 }}}
  350 }}}
  351 
  352 If you are using stored procedures with Oracle, a stored
  353 procedure can execute a query and return a cursor.  A cursor bind variable can
  354 then retrieve that cursor.  Your program can retrieve the result set from the
  355 cursor.  All of this can be accomplished using defineOutputBindCursor(),
  356 getOutputBindCursor() and fetchFromOutputBindCursor().
  357 
  358 {{{#!blockquote
  359 {{{#!code
  360 @parts/c-bind-cursor.c@
  361 }}}
  362 }}}
  363 
  364 The number of cursors simultaneously available per-connection is set at 
  365 compile time and defaults to 5.
  366 
  367 [=#columns]
  368 == Getting Column Information ==
  369 
  370 For each column, the API supports getting the name, type and length of each
  371 field.  All databases support these attributes.  The API also supports
  372 getting the precision, scale (see [precisionscale.html this page]
  373 for a discussion of precision and scale), length of the longest field, and
  374 whether the
  375 column is nullable, the primary key, unique, part of a key, unsigned,
  376 zero-filled, binary, or an auto-incrementing field.  However, not all databases
  377 support these attributes.  If a database doesn't support an attribute, it is
  378 always returned as false.
  379 
  380 {{{#!blockquote
  381 {{{#!code
  382 @parts/c-columninfo.c@
  383 }}}
  384 }}}
  385 
  386 Some databases force column names to upper case, others force column names
  387 to lower case, and others still support mixed-case column names.  Sometimes,
  388 when migrating between databases, you can run into trouble.  You can use
  389 upperCaseColumnNames() and lowerCaseColumnNames() to cause column names to be
  390 converted to upper or lower case, or you can use mixedCaseColumnNames() to
  391 cause column names to be returned in the same case as they are defined in the
  392 database.
  393 
  394 {{{#!blockquote
  395 {{{#!code
  396 @parts/c-columncase.c@
  397 }}}
  398 }}}
  399 
  400 
  401 [=#storedprocedures]
  402 == Stored Procedures ==
  403 
  404 Many databases support stored procedures.  Stored procedures are sets of
  405 queries and procedural code that are executed inside of the database itself.
  406 For example, a stored procedure may select rows from one table, iterate through
  407 the result set and, based on the values in each row, insert, update or delete
  408 rows in other tables.  A client program could do this as well, but a stored
  409 procedure is generally more efficient because queries and result sets don't
  410 have to be sent back and forth between the client and database.  Also, stored
  411 procedures are generally stored in the database in a compiled state, while
  412 queries may have to be re-parsed and re-compiled each time they are sent.
  413 
  414 While many databases support stored procedures.  The syntax for creating
  415 and executing stored procedures varies greatly between databases.
  416 
  417 Stored procedures typically take input paramters from client programs through
  418 input bind variables and return values back to client programs either through
  419 bind variables or result sets.  Stored procedures can be broken down into
  420 several categories, based on the values that they return.  Some stored
  421 procedures don't return any values, some return a single value, some return
  422 multiple values and some return entire result sets.
  423 
  424 === No Values ===
  425 
  426 Some stored procedures don't return any values.  Below are examples,
  427 illustrating how to create, execute and drop this kind of stored procedure for
  428 each database that SQL Relay supports.
  429 
  430 ==== Oracle ====
  431 
  432 To create the stored procedure, run a query like the following.
  433 
  434 {{{#!blockquote
  435 {{{#!code
  436 @parts/oracle-create-testproc.sql@
  437 }}}
  438 }}}
  439 
  440 To execute the stored procedure from an SQL Relay program, use code like the
  441 following.
  442 
  443 {{{#!blockquote
  444 {{{#!code
  445 @parts/c-oracle-testproc.c@
  446 }}}
  447 }}}
  448 
  449 To drop the stored procedure, run a query like the following.
  450 
  451 {{{#!blockquote
  452 {{{#!code
  453 @parts/oracle-drop-testproc.sql@
  454 }}}
  455 }}}
  456 
  457 
  458 ==== Sybase and Microsoft SQL Server ====
  459 
  460 To create the stored procedure, run a query like the following.
  461 
  462 {{{#!blockquote
  463 {{{#!code
  464 @parts/sybase-create-testproc.sql@
  465 }}}
  466 }}}
  467 
  468 To execute the stored procedure from an SQL Relay program, use code like the
  469 following.
  470 
  471 {{{#!blockquote
  472 {{{#!code
  473 @parts/c-sybase-testproc.c@
  474 }}}
  475 }}}
  476 
  477 To drop the stored procedure, run a query like the following.
  478 
  479 {{{#!blockquote
  480 {{{#!code
  481 @parts/sybase-drop-testproc.sql@
  482 }}}
  483 }}}
  484 
  485 ==== Firebird ====
  486 
  487 To create the stored procedure, run a query like the following.
  488 
  489 {{{#!blockquote
  490 {{{#!code
  491 @parts/firebird-create-testproc.sql@
  492 }}}
  493 }}}
  494 
  495 To execute the stored procedure from an SQL Relay program, use code like the
  496 following.
  497 
  498 {{{#!blockquote
  499 {{{#!code
  500 @parts/c-firebird-testproc.c@
  501 }}}
  502 }}}
  503 
  504 To drop the stored procedure, run a query like the following.
  505 
  506 {{{#!blockquote
  507 {{{#!code
  508 @parts/firebird-drop-testproc.sql@
  509 }}}
  510 }}}
  511 
  512 ==== DB2 ====
  513 
  514 To create the stored procedure, run a query like the following.
  515 
  516 {{{#!blockquote
  517 {{{#!code
  518 @parts/db2-create-testproc.sql@
  519 }}}
  520 }}}
  521 
  522 To execute the stored procedure from an SQL Relay program, use code like the
  523 following.
  524 
  525 {{{#!blockquote
  526 {{{#!code
  527 @parts/c-db2-testproc.c@
  528 }}}
  529 }}}
  530 
  531 To drop the stored procedure, run a query like the following.
  532 
  533 {{{#!blockquote
  534 {{{#!code
  535 @parts/db2-drop-testproc.sql@
  536 }}}
  537 }}}
  538 
  539 ==== Postgresql ====
  540 
  541 To create the stored procedure, run a query like the following.
  542 
  543 {{{#!blockquote
  544 {{{#!code
  545 @parts/postgresql-create-testproc.sql@
  546 }}}
  547 }}}
  548 
  549 To execute the stored procedure from an SQL Relay program, use code like the
  550 following.
  551 
  552 {{{#!blockquote
  553 {{{#!code
  554 @parts/c-postgresql-testproc.c@
  555 }}}
  556 }}}
  557 
  558 To drop the stored procedure, run a query like the following.
  559 
  560 {{{#!blockquote
  561 {{{#!code
  562 @parts/postgresql-drop-testproc.sql@
  563 }}}
  564 }}}
  565 
  566 ==== !MySQL/MariaDB ====
  567 
  568 To create the stored procedure, run a query like the following.
  569 
  570 {{{#!blockquote
  571 {{{#!code
  572 @parts/mysql-create-testproc.sql@
  573 }}}
  574 }}}
  575 
  576 To execute the stored procedure from an SQL Relay program, use code like the
  577 following.
  578 
  579 {{{#!blockquote
  580 {{{#!code
  581 @parts/c-mysql-testproc.c@
  582 }}}
  583 }}}
  584 
  585 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.
  586 
  587 To drop the stored procedure, run a query like the following.
  588 
  589 {{{#!blockquote
  590 {{{#!code
  591 @parts/mysql-drop-testproc.sql@
  592 }}}
  593 }}}
  594 
  595 
  596 
  597 === Single Values ===
  598 
  599 Some stored procedures return single values.  Below are examples,
  600 illustrating how to create, execute and drop this kind of stored procedure for
  601 each database that SQL Relay supports.
  602 
  603 ==== Oracle ====
  604 
  605 In Oracle, stored procedures can return values through output parameters or
  606 as return values of the procedure itself.
  607 
  608 Here is an example where the procedure itself returns a value.  Note that
  609 Oracle calls these functions.
  610 
  611 To create the stored procedure, run a query like the following.
  612 
  613 {{{#!blockquote
  614 {{{#!code
  615 @parts/oracle-create-testproc-1value.sql@
  616 }}}
  617 }}}
  618 
  619 To execute the stored procedure from an SQL Relay program, use code like the
  620 following.
  621 
  622 {{{#!blockquote
  623 {{{#!code
  624 @parts/c-oracle-testproc-1value.c@
  625 }}}
  626 }}}
  627 
  628 To drop the stored procedure, run a query like the following.
  629 
  630 {{{#!blockquote
  631 {{{#!code
  632 @parts/oracle-drop-testproc.sql@
  633 }}}
  634 }}}
  635 
  636 Here is an example where the value is returned through an output
  637 parameter.
  638 
  639 To create the stored procedure, run a query like the following.
  640 
  641 {{{#!blockquote
  642 {{{#!code
  643 @parts/oracle-create-testproc-1value-output-param.sql@
  644 }}}
  645 }}}
  646 
  647 To execute the stored procedure from an SQL Relay program, use code like the
  648 following.
  649 
  650 {{{#!blockquote
  651 {{{#!code
  652 @parts/c-oracle-testproc-1value-output-param.c@
  653 }}}
  654 }}}
  655 
  656 To drop the stored procedure, run a query like the following.
  657 
  658 {{{#!blockquote
  659 {{{#!code
  660 @parts/oracle-drop-testproc.sql@
  661 }}}
  662 }}}
  663 
  664 
  665 ==== Sybase and Microsoft SQL Server ====
  666 
  667 In Sybase and Microsoft SQL Server, stored procedures return values
  668 through output parameters rather than as return values of the procedure
  669 itself.
  670 
  671 To create the stored procedure, run a query like the following.
  672 
  673 {{{#!blockquote
  674 {{{#!code
  675 @parts/sybase-create-testproc-1value.sql@
  676 }}}
  677 }}}
  678 
  679 To execute the stored procedure from an SQL Relay program, use code like the
  680 following.
  681 
  682 {{{#!blockquote
  683 {{{#!code
  684 @parts/c-sybase-testproc-1value.c@
  685 }}}
  686 }}}
  687 
  688 To drop the stored procedure, run a query like the following.
  689 
  690 {{{#!blockquote
  691 {{{#!code
  692 @parts/sybase-drop-testproc.sql@
  693 }}}
  694 }}}
  695 
  696 
  697 ==== Firebird ====
  698 
  699 To create the stored procedure, run a query like the following.
  700 
  701 {{{#!blockquote
  702 {{{#!code
  703 @parts/firebird-create-testproc-1value.sql@
  704 }}}
  705 }}}
  706 
  707 To execute the stored procedure from an SQL Relay program, use code like the
  708 following.
  709 
  710 {{{#!blockquote
  711 {{{#!code
  712 @parts/c-firebird-testproc-1value.c@
  713 }}}
  714 }}}
  715 
  716 Alternatively, you can run a query like the following and receive the result
  717 using an output bind variable.  Note that in Firebird, input and
  718 output bind variable indices are distict from one another.  The index of the
  719 output bind variable is 1 rather than 4, even though there were 3 input bind
  720 variables.
  721 
  722 {{{#!blockquote
  723 {{{#!code
  724 @parts/c-firebird-testproc-1value-output-param.c@
  725 }}}
  726 }}}
  727 
  728 To drop the stored procedure, run a query like the following.
  729 
  730 {{{#!blockquote
  731 {{{#!code
  732 @parts/firebird-drop-testproc.sql@
  733 }}}
  734 }}}
  735 
  736 
  737 ==== DB2 ====
  738 
  739 In DB2, stored procedures return values through output parameters rather
  740 than as return values of the procedure itself.
  741 
  742 To create the stored procedure, run a query like the following.
  743 
  744 {{{#!blockquote
  745 {{{#!code
  746 @parts/db2-create-testproc-1value.sql@
  747 }}}
  748 }}}
  749 
  750 To execute the stored procedure from an SQL Relay program, use code like the
  751 following.
  752 
  753 {{{#!blockquote
  754 {{{#!code
  755 @parts/c-db2-testproc-1value.c@
  756 }}}
  757 }}}
  758 
  759 To drop the stored procedure, run a query like the following.
  760 
  761 {{{#!blockquote
  762 {{{#!code
  763 @parts/db2-drop-testproc.sql@
  764 }}}
  765 }}}
  766 
  767 
  768 ==== Postgresql ====
  769 
  770 To create the stored procedure, run a query like the following.
  771 
  772 {{{#!blockquote
  773 {{{#!code
  774 @parts/postgresql-create-testproc-1value.sql@
  775 }}}
  776 }}}
  777 
  778 To execute the stored procedure from an SQL Relay program, use code like the
  779 following.
  780 
  781 {{{#!blockquote
  782 {{{#!code
  783 @parts/c-postgresql-testproc-1value.c@
  784 }}}
  785 }}}
  786 
  787 To drop the stored procedure, run a query like the following.
  788 
  789 {{{#!blockquote
  790 {{{#!code
  791 @parts/postgresql-drop-testproc-1value.sql@
  792 }}}
  793 }}}
  794 
  795 ==== !MySQL/MariaDB ====
  796 
  797 A single value can be returned from a !MySQL/MariaDB function.
  798 
  799 To create the function, run a query like the following.
  800 
  801 {{{#!blockquote
  802 {{{#!code
  803 @parts/mysql-create-testproc-1value.sql@
  804 }}}
  805 }}}
  806 
  807 To execute the function from an SQL Relay program, use code like the
  808 following.
  809 
  810 {{{#!blockquote
  811 {{{#!code
  812 @parts/c-mysql-testproc-1value.c@
  813 }}}
  814 }}}
  815 
  816 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.
  817 
  818 To drop the function, run a query like the following.
  819 
  820 {{{#!blockquote
  821 {{{#!code
  822 @parts/mysql-drop-testproc.sql@
  823 }}}
  824 }}}
  825 
  826 A single value can be returned in the result set of a !MySQL/MariaDB procedure.
  827 
  828 To create the procedure, run a query like the following.
  829 
  830 {{{#!blockquote
  831 {{{#!code
  832 @parts/mysql-create-testproc-1value-result-set.sql@
  833 }}}
  834 }}}
  835 
  836 To execeute the procedure from an SQL Relay program, use code like the
  837 following.
  838 
  839 {{{#!blockquote
  840 {{{#!code
  841 @parts/c-mysql-testproc-1value-result-set.c@
  842 }}}
  843 }}}
  844 
  845 To drop the procedure, run a query like the following.
  846 
  847 {{{#!blockquote
  848 {{{#!code
  849 @parts/mysql-drop-testproc.sql@
  850 }}}
  851 }}}
  852 
  853 A single value can be returned using the output variable of a !MySQL/MariaDB
  854 procedure.
  855 
  856 To create the procedure, run a query like the following.
  857 
  858 {{{#!blockquote
  859 {{{#!code
  860 @parts/mysql-create-testproc-1value-output-param.sql@
  861 }}}
  862 }}}
  863 
  864 To execeute the procedure from an SQL Relay program, use code like the
  865 following.
  866 
  867 {{{#!blockquote
  868 {{{#!code
  869 @parts/c-mysql-testproc-1value-output-param.c@
  870 }}}
  871 }}}
  872 
  873 To drop the procedure, run a query like the following.
  874 
  875 {{{#!blockquote
  876 {{{#!code
  877 @parts/mysql-drop-testproc.sql@
  878 }}}
  879 }}}
  880 
  881 
  882 
  883 === Multiple Values ===
  884 
  885 Some stored procedures return multiple values.  Below are examples,
  886 illustrating how to create, execute and drop this kind of stored procedure for
  887 each database that SQL Relay supports.
  888 
  889 ==== Oracle ====
  890 
  891 In Oracle, stored procedures can return values through output parameters or
  892 as return values of the procedure itself.  If a procedure needs to return
  893 multiple values, it can return one of them as the return value of the procedure
  894 itself, but the rest must be returned through output parameters.
  895 
  896 To create the stored procedure, run a query like the following.
  897 
  898 {{{#!blockquote
  899 {{{#!code
  900 @parts/oracle-create-testproc-values.sql@
  901 }}}
  902 }}}
  903 
  904 To execute the stored procedure from an SQL Relay program, use code like the
  905 following.
  906 
  907 {{{#!blockquote
  908 {{{#!code
  909 @parts/c-oracle-testproc-values.c@
  910 }}}
  911 }}}
  912 
  913 To drop the stored procedure, run a query like the following.
  914 
  915 {{{#!blockquote
  916 {{{#!code
  917 @parts/oracle-drop-testproc.sql@
  918 }}}
  919 }}}
  920 
  921 
  922 ==== Sybase and Microsoft SQL Server ====
  923 
  924 To create the stored procedure, run a query like the following.
  925 
  926 {{{#!blockquote
  927 {{{#!code
  928 @parts/sybase-create-testproc-values.sql@
  929 }}}
  930 }}}
  931 
  932 To execute the stored procedure from an SQL Relay program, use code like the
  933 following.
  934 
  935 {{{#!blockquote
  936 {{{#!code
  937 @parts/c-sybase-testproc-values.c@
  938 }}}
  939 }}}
  940 
  941 To drop the stored procedure, run a query like the following.
  942 
  943 {{{#!blockquote
  944 {{{#!code
  945 @parts/sybase-drop-testproc.sql@
  946 }}}
  947 }}}
  948 
  949 
  950 ==== Firebird ====
  951 
  952 To create the stored procedure, run a query like the following.
  953 
  954 {{{#!blockquote
  955 {{{#!code
  956 @parts/firebird-create-testproc-values.sql@
  957 }}}
  958 }}}
  959 
  960 To execute the stored procedure from an SQL Relay program, use code like the
  961 following.
  962 
  963 {{{#!blockquote
  964 {{{#!code
  965 @parts/c-firebird-testproc-values.c@
  966 }}}
  967 }}}
  968 
  969 Alternatively, you can run a query like the following and receive the result
  970 using a output bind variables.  Note that in Firebird, input and
  971 output bind variable indices are distict from one another.  The index of the
  972 first output bind variable is 1 rather than 4, even though there were 3 input
  973 bind variables.
  974 
  975 {{{#!blockquote
  976 {{{#!code
  977 @parts/c-firebird-testproc-values-output-params.c@
  978 }}}
  979 }}}
  980 
  981 To drop the stored procedure, run a query like the following.
  982 
  983 {{{#!blockquote
  984 {{{#!code
  985 @parts/firebird-drop-testproc.sql@
  986 }}}
  987 }}}
  988 
  989 
  990 ==== DB2 ====
  991 
  992 To create the stored procedure, run a query like the following.
  993 
  994 {{{#!blockquote
  995 {{{#!code
  996 @parts/db2-create-testproc-values.sql@
  997 }}}
  998 }}}
  999 
 1000 To execute the stored procedure from an SQL Relay program, use code like the
 1001 following.
 1002 
 1003 {{{#!blockquote
 1004 {{{#!code
 1005 @parts/c-db2-testproc-values.c@
 1006 }}}
 1007 }}}
 1008 
 1009 To drop the stored procedure, run a query like the following.
 1010 
 1011 {{{#!blockquote
 1012 {{{#!code
 1013 @parts/db2-drop-testproc.sql@
 1014 }}}
 1015 }}}
 1016 
 1017 
 1018 ==== Postgresql ====
 1019 
 1020 To create the stored procedure, run a query like the following.
 1021 
 1022 {{{#!blockquote
 1023 {{{#!code
 1024 @parts/postgresql-create-testproc-values.sql@
 1025 }}}
 1026 }}}
 1027 
 1028 To execute the stored procedure from an SQL Relay program, use code like the
 1029 following.
 1030 
 1031 {{{#!blockquote
 1032 {{{#!code
 1033 @parts/c-postgresql-testproc-values.c@
 1034 }}}
 1035 }}}
 1036 
 1037 To drop the stored procedure, run a query like the following.
 1038 
 1039 {{{#!blockquote
 1040 {{{#!code
 1041 @parts/postgresql-drop-testproc-values.sql@
 1042 }}}
 1043 }}}
 1044 
 1045 ==== !MySQL/MariaDB ====
 1046 
 1047 Here's how you can get multiple values from the result
 1048 set of a !MySQL/MariaDB procedure.
 1049 
 1050 To create the stored procedure, run a query like the following.
 1051 
 1052 {{{#!blockquote
 1053 {{{#!code
 1054 @parts/mysql-create-testproc-values.sql@
 1055 }}}
 1056 }}}
 1057 
 1058 To execute the stored procedure from an SQL Relay program, use code like the
 1059 following.
 1060 
 1061 {{{#!blockquote
 1062 {{{#!code
 1063 @parts/c-mysql-testproc-values.c@
 1064 }}}
 1065 }}}
 1066 
 1067 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.
 1068 
 1069 To drop the stored procedure, run a query like the following.
 1070 
 1071 {{{#!blockquote
 1072 {{{#!code
 1073 @parts/mysql-drop-testproc.sql@
 1074 }}}
 1075 }}}
 1076 
 1077 Here's how you can get multiple values from the output variables
 1078 of a !MySQL/MariaDB procedure.
 1079 
 1080 To create the stored procedure, run a query like the following.
 1081 
 1082 {{{#!blockquote
 1083 {{{#!code
 1084 @parts/mysql-create-testproc-values-output-params.sql@
 1085 }}}
 1086 }}}
 1087 
 1088 To execute the stored procedure from an SQL Relay program, use code like the
 1089 following.
 1090 
 1091 {{{#!blockquote
 1092 {{{#!code
 1093 @parts/c-mysql-testproc-values-output-params.c@
 1094 }}}
 1095 }}}
 1096 
 1097 To drop the stored procedure, run a query like the following.
 1098 
 1099 {{{#!blockquote
 1100 {{{#!code
 1101 @parts/mysql-drop-testproc.sql@
 1102 }}}
 1103 }}}
 1104 
 1105 
 1106 
 1107 === Result Sets ===
 1108 
 1109 Some stored procedures return entire result sets.  Below are examples,
 1110 illustrating how to create, execute and drop this kind of stored procedure for
 1111 each database that SQL Relay supports.
 1112 
 1113 ==== Oracle ====
 1114 
 1115 To create the stored procedure, run a query like the following.
 1116 
 1117 {{{#!blockquote
 1118 {{{#!code
 1119 @parts/oracle-create-testproc-result-set.sql@
 1120 }}}
 1121 }}}
 1122 
 1123 To execute the stored procedure from an SQL Relay program, use code like the
 1124 following.
 1125 
 1126 {{{#!blockquote
 1127 {{{#!code
 1128 @parts/c-oracle-testproc-result-set.c@
 1129 }}}
 1130 }}}
 1131 
 1132 To drop the stored procedure, run a query like the following.
 1133 
 1134 {{{#!blockquote
 1135 {{{#!code
 1136 @parts/oracle-drop-testproc-result-set.sql@
 1137 }}}
 1138 }}}
 1139 
 1140 
 1141 ==== Sybase and Microsoft SQL Server ====
 1142 
 1143 To create the stored procedure, run a query like the following.
 1144 
 1145 {{{#!blockquote
 1146 {{{#!code
 1147 @parts/sybase-create-testproc-result-set.sql@
 1148 }}}
 1149 }}}
 1150 
 1151 To exceute the stored procedure from an SQL Relay program, ue code like the
 1152 following.
 1153 
 1154 {{{#!blockquote
 1155 {{{#!code
 1156 @parts/c-sybase-testproc-result-set.c@
 1157 }}}
 1158 }}}
 1159 
 1160 To drop the stored procedure, run a query like the following.
 1161 
 1162 {{{#!blockquote
 1163 {{{#!code
 1164 @parts/sybase-drop-testproc.sql@
 1165 }}}
 1166 }}}
 1167 
 1168 
 1169 ==== Firebird ====
 1170 
 1171 Stored procedures in Firebird can return a result set if a
 1172 select query in the procedure selects values into the output parameters and
 1173 then issues a suspend command, however SQL Relay doesn't currently support
 1174 stored procedures that return result sets.
 1175 
 1176 ==== DB2 ====
 1177 
 1178 Stored procedures in DB2 can return a result set if the procedure is declared
 1179 to return one, however SQL Relay doesn't currently support stored procedures
 1180 that return result sets.
 1181 
 1182 ==== Postgresql ====
 1183 
 1184 To create the stored procedure, run a query like the following.
 1185 
 1186 {{{#!blockquote
 1187 {{{#!code
 1188 @parts/postgresql-create-testproc-result-set.sql@
 1189 }}}
 1190 }}}
 1191 
 1192 To execute the stored procedure from an SQL Relay program, use code like the
 1193 following.
 1194 
 1195 {{{#!blockquote
 1196 {{{#!code
 1197 @parts/c-postgresql-testproc-result-set.c@
 1198 }}}
 1199 }}}
 1200 
 1201 To drop the stored procedure, run a query like the following.
 1202 
 1203 {{{#!blockquote
 1204 {{{#!code
 1205 @parts/postgresql-drop-testproc-result-set.sql@
 1206 }}}
 1207 }}}
 1208 
 1209 ==== !MySQL/MariaDB ====
 1210 
 1211 The result sets of all select statements called within !MySQL/MariaDB stored
 1212 procedures (that aren't selected into variables) are returned from the procedure
 1213 call.  Though !MySQL/MariaDB stored procedures can return multiple result sets,
 1214 currently SQL Relay can only fetch the first result set.
 1215 
 1216 To create the stored procedure which returns a result set, run a query like
 1217 the following.
 1218 
 1219 {{{#!blockquote
 1220 {{{#!code
 1221 @parts/mysql-create-testproc-result-set.sql@
 1222 }}}
 1223 }}}
 1224 
 1225 To execute the stored procedure from an SQL Relay program, use code like the
 1226 following.
 1227 
 1228 {{{#!blockquote
 1229 {{{#!code
 1230 @parts/c-mysql-testproc-result-set.c@
 1231 }}}
 1232 }}}
 1233 
 1234 To drop the stored procedure, run a query like the following.
 1235 
 1236 {{{#!blockquote
 1237 {{{#!code
 1238 @parts/mysql-drop-testproc.sql@
 1239 }}}
 1240 }}}
 1241 
 1242 
 1243 [=#caching]
 1244 == Caching The Result Set ==
 1245 
 1246 Say you're writing a web-based report where a query with a huge result set
 1247 is executed and 20 rows are displayed per page.  Rather than rerunning the 
 1248 query for every page every time and dumping all but the 20 rows you want to
 1249 display, you can run the query once, cache the result set to a local file and
 1250 just open the file for each page of the report.
 1251 
 1252 First page:
 1253 
 1254 {{{#!blockquote
 1255 {{{#!code
 1256 @parts/c-caching-firstpage.c@
 1257 }}}
 1258 }}}
 1259 
 1260 Second page:
 1261 
 1262 {{{#!blockquote
 1263 {{{#!code
 1264 @parts/c-caching-secondpage.c@
 1265 }}}
 1266 }}}
 1267 
 1268 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.
 1269 
 1270 To enable the sqlr-cachemanager at boot on systemd platforms:
 1271 
 1272 {{{#!blockquote
 1273 {{{#!code
 1274 systemctl enable sqlrcachemanager.service
 1275 }}}
 1276 }}}
 1277 
 1278 To start the sqlr-cachemanager on systemd platforms:
 1279 
 1280 {{{#!blockquote
 1281 {{{#!code
 1282 systemctl start sqlrcachemanager.service
 1283 }}}
 1284 }}}
 1285 
 1286 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:
 1287 
 1288 {{{#!blockquote
 1289 {{{#!code
 1290 cd /etc/rc2.d
 1291 ln -s ../init.d/sqlrcachemanager S15sqlrcachemanager
 1292 }}}
 1293 
 1294 or
 1295 
 1296 {{{#!code
 1297 cd /etc/rc3.d
 1298 ln -s ../init.d/sqlrcachemanager S15sqlrcachemanager
 1299 }}}
 1300 }}}
 1301 
 1302 To start the sqlr-cachemanager on most non-systemd platforms:
 1303 
 1304 {{{#!blockquote
 1305 {{{#!code
 1306 /etc/init.d/sqlrcachemanager start
 1307 }}}
 1308 }}}
 1309 
 1310 To enable the sqlr-cachemanager at boot on !FreeBSD platforms, edit /etc/rc.conf and add a line like:
 1311 
 1312 {{{#!blockquote
 1313 {{{#!code
 1314 sqlrcachemanager_enable=YES
 1315 }}}
 1316 }}}
 1317 
 1318 To enable the sqlr-cachemanager at boot on !NetBSD platforms, edit /etc/rc.conf and add a line like:
 1319 
 1320 {{{#!blockquote
 1321 {{{#!code
 1322 sqlrcachemanager=YES
 1323 }}}
 1324 }}}
 1325 
 1326 To enable the sqlr-cachemanager at boot on !OpenBSD platforms, edit /etc/rc.conf and add a line like:
 1327 
 1328 {{{#!blockquote
 1329 {{{#!code
 1330 sqlrcachemanager_flags=YES
 1331 }}}
 1332 }}}
 1333 
 1334 To start the sqlr-cachemanager on BSD platforms:
 1335 
 1336 {{{#!blockquote
 1337 {{{#!code
 1338 /etc/init.d/sqlrcachemanager start
 1339 }}}
 1340 }}}
 1341 
 1342 
 1343 [=#suspending]
 1344 == Suspending and Resuming Sessions ==
 1345 
 1346 Sometimes web-based applications need a single database transaction to span
 1347 multiple pages.  Since SQL Relay sessions can be suspended and resumed, this
 1348 is possible.
 1349 
 1350 First page:
 1351 
 1352 {{{#!blockquote
 1353 {{{#!code
 1354 @parts/c-suspend.c@
 1355 }}}
 1356 }}}
 1357 
 1358 Second page:
 1359 
 1360 {{{#!blockquote
 1361 {{{#!code
 1362 @parts/c-resume.c@
 1363 }}}
 1364 }}}
 1365 
 1366 You can also distribute the processing of a result set across a series of
 1367 pages using suspended sessions.  If you're buffering a result set in chunks
 1368 instead of all at once and suspend a session, when you resume the session you 
 1369 can continue to retrieve rows from the result set.
 1370 
 1371 Similarly, if you're buffering a result set in chunks, caching that
 1372 result set and suspend your session.  When you resume the session, you can
 1373 continue caching the result set.  You must use sqlrcur_resumeCachedResultSet()
 1374 instead of sqlrcur_resumeResultSet() however.
 1375 
 1376 [=#lastinsertid]
 1377 == Getting the Last Insert ID ==
 1378 
 1379 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.
 1380 
 1381 SQL Relay provides the sqlrcon_getLastInsertId() function to get this value.
 1382 
 1383 When using the SQLite database, you can also get the last insert id by running the query:
 1384 
 1385 {{{#!blockquote
 1386 '''select last insert rowid'''
 1387 }}}