From: CS_PAUL@GSBVAX.UCHICAGO.EDU (Paul Ford 312/702-0335)
Subject: FWD: using "ping" and "pong" tables
Organization: National Radio Astronomy Observatory
Date: Wed, 2 Sep 1992 19:28:28 GMT

Alan Croswell writes:

>I have a number of large "production" tables that are being hit on
>interactively 'round the clock primarily for reads plus a bunch of
>"overnight" update jobs lock these tables exclusive for extended
>periods.  I was thinking about have two versions of the same table,
>and ping-ponging back and forth between them by simply having all my
>programs use a variable for the "current" table which itself would
>be in a table somewhere.  This seems like it is probably a pretty
>common practice and I figured I see if any of you have any pointers....

An obvious limitation of this technique is that ESQL doesn't support
variable table names directly, as in:

        exec sql SELECT ...
                 FROM :variable_table   /* illegal */
                 WHERE ...
                 ;

That means you have two choices, dynamic queries, or two versions of each
query.

Constructing dynamic queries all over the place can be messy and you lose
REPEATED queries, which could degrade performance.   Duplicate queries all over
the place is also messy and a potential source of error (giving you two pieces
of parallel code to maintain), but at least you get repeated queries.  You
could mix the two techniques, for hot queries that benefit from being repeated
use the duplicate technique, for the others use dynamic queries.

Dynamic UPDATES, INSERTS, and DELETES can be done in one shot with EXECUTE
IMMEDIATE.   But my head always hurts when I contemplate writing dynamic
SELECTS.  I still have to drag out the manual to read up on PREPARE, DESCRIBE,
and EXECUTE when I need to use them.

Fortunately your case of a variable table name is the easy one.  Because the
result columns are fixed you don't need the full generality of the SQLDA.  If
you need a cursor (e.g., you do additional queries for each fetch) you have to
use a PREPARE statement in addition to the standard DECLARE, OPEN, FETCH, and
CLOSE.  If you can get by with a select loop all you need is the  EXECUTE
IMMEDIATE statement.

For what it's worth I've attached some sample code of dynamic select with  a
variable table name implemented both as a cursor and as a fetch loop.

Paul Ford
--------------------------------------------------------------------------
GSB Computing Services                  312.702.0335
University of Chicago                   cs_paul@gsbvax.uchicago.edu
1101 E. 58th Street
Chicago IL 60637
--------------------------------------------------------------------------

/* Sample dynamic selects with fixed result columns (no need to use SQLDA). */

exec sql include sqlca ;
exec sql WHENEVER sqlerror call sqlprint ;
#include <stdio.h>

main()
{

    exec sql BEGIN DECLARE SECTION ;
        char vtbl[100] = "iitables" ;
        char table_name[100] ;
        char buffer[1000] ;
    exec sql END DECLARE SECTION ;

    exec sql CONNECT iidbdb ;

/* Construct SELECT statement with a variable table name. */

    sprintf(buffer, "select table_name from %s", vtbl ) ;

/* Dynamic select using a prepare and a cursor. */

    printf("Dynamic cursor...\n" ) ;

    exec sql PREPARE test_statement FROM :buffer ;
    exec sql DECLARE test_cursor CURSOR FOR test_statement ;

    exec sql OPEN test_cursor ;
    while ( 0 == sqlca.sqlcode )
    {
        exec sql FETCH test_cursor
                 INTO :table_name ;
        if ( 0 > sqlca.sqlcode || 100 == sqlca.sqlcode) break ;
        printf("     %s\n", table_name ) ;
    }
    exec sql CLOSE test_cursor ;
    exec sql COMMIT ;

/* Dynamic select using execute immediate and a select loop. */

    printf("Execute immediate...\n") ;
    exec sql EXECUTE IMMEDIATE :buffer
             INTO :table_name ;
    exec sql BEGIN ;
        printf("     %s\n", table_name ) ;
    exec sql END ;
    exec sql COMMIT ;

}


Newsgroups: comp.databases.ingres