"Fossies" - the Fresh Open Source Software Archive

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

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