"Fossies" - the Fresh Open Source Software Archive

Member "sqlrelay-1.5.2/doc/programming/cs.wt" (23 Apr 2019, 37369 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 "cs.wt": 1.3.0_vs_1.4.0.

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