Application Development Guide

Variable Input to Dynamic SQL

This section shows you how to use parameter markers in your dynamic SQL applications to represent host variable information. It includes:

Using Parameter Markers

A dynamic SQL statement cannot contain host variables, because host variable information (data type and length) is available only during application precompilation. At execution time, the host variable information is not present. Therefore, a new method is needed to represent application variables. Host variables are represented by a question mark (?) which is called a parameter marker. Parameter markers indicate the places in which a host variable is to be substituted inside of an SQL statement. The parameter marker takes on an assumed data type and length that is dependent on the context of its use inside the SQL statement.

If the data type of a parameter marker is not obvious from the context of the statement in which it is used, the type can be specified using a CAST. Such a parameter marker is considered a typed parameter marker. Typed parameter markers will be treated like a host variable of the given type. For example, the statement SELECT ? FROM SYSCAT.TABLES is invalid because DB2 does not know the type of the result column. However, the statement SELECT CAST(? AS INTEGER) FROM SYSCAT.TABLES, is valid because the cast promises that the parameter marker represents an INTEGER, so DB2 knows the type of the result column.

A character string containing a parameter marker might look like the following:

     DELETE FROM TEMPL WHERE EMPNO = ?

When this statement is executed, a host variable or SQLDA structure is specified by the USING clause of the EXECUTE statement. The contents of the host variable are used when the statement executes.

If the SQL statement contains more than one parameter marker, then the USING clause of the EXECUTE statement must either specify a list of host variables (one for each parameter marker), or it must identify an SQLDA that has an SQLVAR entry for each parameter marker. (Note that for LOBs, there are two SQLVARs per parameter marker.) The host variable list or SQLVAR entries are matched according to the order of the parameter markers in the statement, and they must have compatible data types.

Note that using a parameter marker with dynamic SQL is like using host variables with static SQL. In either case, the optimizer does not use distribution statistics, and possibly may not choose the best access plan.

The rules that apply to parameter markers are listed under the PREPARE statement in the SQL Reference.

Example: VARINP Program

This is an example of an UPDATE that uses a parameter marker in the search and update conditions. The sample is available in the following programming languages:

C
varinp.sqc

Java
Varinp.java

COBOL
varinp.sqb

How the VARINP Program Works

  1. Prepare the SELECT SQL statement The PREPARE statement is called to dynamically prepare an SQL statement. In this SQL statement, parameter markers are denoted by the ?. The job field of staff is defined to be updatable, even though it is not specified in the result table.
  2. Declare the cursor. The DECLARE CURSOR statement associates the cursor c1 to the query that was prepared in (1).
  3. Open the cursor. The cursor c1 is opened, causing the database manager to perform the query and build a result table. The cursor is positioned before the first row.
  4. Prepare the UPDATE SQL statement The PREPARE statement is called to dynamically prepare an SQL statement. The parameter marker in this statement is set to be Clerk but can be changed dynamically to anything, as long as it conforms to the column data type it is being updated into.
  5. Retrieve a row. The FETCH statement positions the cursor at the next row and moves the contents of the row into the host variables. This row becomes the CURRENT row.
  6. Update the current row. The current row and specified column, job, is updated with the content of the passed parameter parm_var.
  7. Close the cursor. The CLOSE statement is issued, releasing the resources associated with the cursor. The cursor can be opened again, however.

The CHECKERR macro/function is an error checking utility which is external to the program. The location of this error checking utility depends upon the programming language used:

C
For C programs that call DB2 APIs, the sqlInfoPrint function in utilapi.c is redefined as API_SQL_CHECK in utilapi.h. For C embedded SQL programs, the sqlInfoPrint function in utilemb.sqc is redefined as EMB_SQL_CHECK in utilemb.h.

Java
Any SQL error is thrown as an SQLException and handled in the catch block of the application.

COBOL
CHECKERR is an external program named checkerr.cbl

See Using GET ERROR MESSAGE in Example Programs for the source code for this error checking utility.

C Example: VARINP.SQC

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "utilemb.h"
 
EXEC SQL INCLUDE SQLCA;
 
int main(int argc, char *argv[]) 
{
 
   EXEC SQL BEGIN DECLARE SECTION;
      char   pname[10];
      short  dept;
      char userid[9];
      char passwd[19];
      char st[255];
      char parm_var[6];
   EXEC SQL END DECLARE SECTION;
 
   printf( "Sample C program: VARINP \n" );
 
   if (argc == 1) 
   {
      EXEC SQL CONNECT TO sample;
	  EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else if (argc == 3) 
   { 
      strcpy (userid, argv[1]);
      strcpy (passwd, argv[2]);
      EXEC SQL CONNECT TO sample USER :userid USING :passwd;
      EMB_SQL_CHECK("CONNECT TO SAMPLE");
   }
   else 
   {
      printf ("\nUSAGE: varinp [userid passwd]\n\n");
      return 1;
   } /* endif */
   
   strcpy (st, "SELECT name, dept FROM staff ");
   strcat (st, "WHERE job = ? FOR UPDATE OF job");
   EXEC SQL PREPARE s1 FROM :st; (1)
   EMB_SQL_CHECK("PREPARE");
 
   EXEC SQL DECLARE c1 CURSOR FOR s1; (2)
 
   strcpy (parm_var, "Mgr");
   EXEC SQL OPEN c1 USING :parm_var; (3)
   EMB_SQL_CHECK("OPEN");
 
   strcpy (parm_var, "Clerk");
   strcpy (st, "UPDATE staff SET job = ? WHERE CURRENT OF c1");
   EXEC SQL PREPARE s2 from :st; (4)
 
   do 
   {
      EXEC SQL FETCH c1 INTO :pname, :dept; (5)
      if (SQLCODE != 0) break;
 
      printf( "%-10.10s in dept. %2d will be demoted to Clerk\n",
              pname, dept );
      EXEC SQL EXECUTE s2 USING :parm_var; (6)
      EMB_SQL_CHECK("EXECUTE");
   } while ( 1 );
 
   EXEC SQL CLOSE c1;  (7)
   EMB_SQL_CHECK("CLOSE CURSOR");
 
   EXEC SQL ROLLBACK;
   EMB_SQL_CHECK("ROLLBACK");
   printf( "\nOn second thought -- changes rolled back.\n" );
 
   EXEC SQL CONNECT RESET;
   EMB_SQL_CHECK("CONNECT RESET");
   return 0;
}
/* end of program : VARINP.SQC */

Java Example: Varinp.java

import java.sql.*;
 
class Varinp 
{   static
  {   try
    {   Class.forName ("COM.ibm.db2.jdbc.app.DB2Driver").newInstance ();
    }
    catch (Exception e)
    {   System.out.println ("\n  Error loading DB2 Driver...\n");
      System.out.println (e);
      System.exit(1);
    }
  }
 
  public static void main(String argv[])
  {   try 
    {   System.out.println ("  Java Varinp Sample");
      // Connect to Sample database
 
      Connection con = null;
      // URL is jdbc:db2:dbname
      String url = "jdbc:db2:sample";
 
      if (argv.length == 0) 
      {   // connect with default id/password
        con = DriverManager.getConnection(url);
      }
      else if (argv.length == 2)
      {   String userid = argv[0];
        String passwd = argv[1];
 
        // connect with user-provided username and password
        con = DriverManager.getConnection(url, userid, passwd);
      }
      else 
      {   throw new Exception("\nUsage: java Varinp [username password]\n");
      } 
 
      // Enable transactions
      con.setAutoCommit(false);
 
      // Perform dynamic SQL using JDBC
      try
      {   PreparedStatement pstmt1 = con.prepareStatement(
          "SELECT name, dept FROM staff WHERE job = ? FOR UPDATE OF job"); (1)
      // set cursor name for the positioned update statement
      pstmt1.setCursorName("c1");                                          (2)
      pstmt1.setString(1, "Mgr"); 
      ResultSet rs = pstmt1.executeQuery();                                (3)
 
      PreparedStatement pstmt2 = con.prepareStatement(
          "UPDATE staff SET job = ? WHERE CURRENT OF c1");               (4)
      pstmt2.setString(1, "Clerk"); 
 
      System.out.print("\n");
      while( rs.next() )                                                  (5) 
      {   String name = rs.getString("name");
        short  dept = rs.getShort("dept"); 
        System.out.println(name + " in dept. " + dept
          + " will be demoted to Clerk");
 
        pstmt2.executeUpdate();                                            (6)
      };
 
      rs.close();
      pstmt1.close();                                                     (7)
      pstmt2.close();
      }
      catch( Exception e )
      {   throw e; 
      } 
      finally
      {   // Rollback the transaction
        System.out.println("\nRollback the transaction...");
        con.rollback();
        System.out.println("Rollback done.");
      }
    } 
    catch( Exception e )
    {   System.out.println(e);
    } 
  }
}

COBOL Example: VARINP.SQB

       Identification Division.
       Program-ID. "varinp".
 
       Data Division.
       Working-Storage Section.
 
           copy "sqlca.cbl".
 
           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 pname             pic x(10).
       01 dept              pic s9(4) comp-5.
       01 st                pic x(127).
       01 parm-var          pic x(5).
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
           EXEC SQL END DECLARE SECTION END-EXEC.
 
       77 errloc          pic x(80).
 
       Procedure Division.
       Main Section.
           display "Sample COBOL program: VARINP".
 
      * Get database connection information.
           display "Enter your user id (default none): " 
                with no advancing.
           accept userid.
 
           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.
 
      * Passwords in a CONNECT statement must be entered in a VARCHAR format
      * with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial " ".
 
           EXEC SQL CONNECT TO sample USER :userid USING :passwd
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.
 
           move "SELECT name, dept FROM staff
      -         "   WHERE job = ? FOR UPDATE OF job" to st.
           EXEC SQL PREPARE s1 FROM :st END-EXEC.                       (1)
           move "PREPARE" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL DECLARE c1 CURSOR FOR s1 END-EXEC.                  (2)
 
           move "Mgr" to parm-var.
 
           EXEC SQL OPEN c1 USING :parm-var END-EXEC                    (3)
           move "OPEN" to errloc.
           call "checkerr" using SQLCA errloc.
 
           move "Clerk" to parm-var.
           move "UPDATE staff SET job = ? WHERE CURRENT OF c1" to st.
 
           EXEC SQL PREPARE s2 from :st END-EXEC.                       (4)
           move "PREPARE S2" to errloc.
           call "checkerr" using SQLCA errloc.
 
      * call the FETCH and UPDATE loop.
           perform Fetch-Loop thru End-Fetch-Loop
              until SQLCODE not equal 0.
 
           EXEC SQL CLOSE c1 END-EXEC.                                  (7)
           move "CLOSE" to errloc.
           call "checkerr" using SQLCA errloc.
 
           EXEC SQL ROLLBACK END-EXEC.
           move "ROLLBACK" to errloc.
           call "checkerr" using SQLCA errloc.
           DISPLAY "On second thought -- changes rolled back.".
 
           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
       End-Main.
           go to End-Prog.
 
       Fetch-Loop Section.
           EXEC SQL FETCH c1 INTO :pname, :dept END-EXEC.               (5)
           if SQLCODE not equal 0
              go to End-Fetch-Loop.
           display pname, " in dept. ", dept,
              " will be demoted to Clerk".
 
           EXEC SQL EXECUTE s2 USING :parm-var END-EXEC.                (6)
           move "EXECUTE" to errloc.
           call "checkerr" using SQLCA errloc.
 
       End-Fetch-Loop. exit.
 
       End-Prog.
           stop run.


[ Top of Page | Previous Page | Next Page ]