Parameter markers

A parameter marker, often denoted by a question mark (?) or a colon followed by a variable name (:var1), is a place holder in an SQL statement whose value is obtained during statement execution.

An application associates parameter markers to application variables. During the execution of the statement, the values of these variables replace each respective parameter marker. Data conversion might take place during the process.

Benefits of parameter markers

For SQL statements that need to be executed many times, it is often beneficial to prepare the SQL statement once, and reuse the query plan by using parameter markers to substitute the input values during runtime. A parameter marker is represented in one of two ways:

The first style, with a "?" character, is used in dynamic SQL execution (dynamic Embedded SQL, CLI, Perl, and others).
The second style represents the embedded SQL standard construction where the name of the variable is prefixed with a colon (:var1). This style is used in static SQL execution and is commonly referred to as a host variable.

Use of either style indicates where an application variable is to be substituted inside an SQL statement. Parameter markers are referenced by number, and are numbered sequentially from left to right, starting at one. Before the SQL statement is executed, the application must bind a variable storage area to each parameter marker specified in the SQL statement. In addition, the bound variables must be a valid storage area, and must contain input data values when the prepared statement is executed against the database.

The following example illustrates an SQL statement containing two parameter markers.

SELECT * FROM customers WHERE custid = ? AND lastname = ?

Supported types

You can specify untyped parameter markers in selected locations of an SQL statement. Table 1 lists the restrictions on untyped parameter marker usage.

Table 1. Restrictions on parameter marker usage
Untyped parameter marker location	Data type
Expression: Alone in a select list	Error
Expression: Both operands of an arithmetic operator	Error
Predicate: Left-hand side operand of an IN predicate	Error
Predicate: Both operands of a relational operator	Error
Function: Operand of an aggregation function	Error

Examples

You can use a standard interface such as CLI/ODBC, JDBC, and ADO.NET to access the database. The following code snippets show the use of prepared statement with parameter markers for each data access API.

Consider the following table schema for table t1, where column c1 is the primary key for table t1.

Table 2. Example table schema
Column name	Db2®data type	Nullable
c1	INTEGER	false
c2	SMALLINT	true
c3	CHAR(20)	true
c4	VARCHAR(20)	true
c5	DECIMAL(8,2)	true
c6	DATE	true
c7	TIME	true
c8	TIMESTAMP	true
c9	BLOB(30)	true

The following examples illustrate how to insert a row into table t1 using a prepared statement.

CLI Example

void parameterExample1(void) 
{ 
   SQLHENV henv; 
   SQLHDBC hdbc; 
   SQLHSTMT hstmt; 
   SQLRETURN rc; 
   TCHAR server[] = _T("C:\\mysample\\"); 
   TCHAR uid[] = _T("db2e"); 
   TCHAR pwd[] = _T("db2e"); 
   long p1 = 10; 
   short p2 = 100; 
   TCHAR p3[100]; 
   TCHAR p4[100]; 
   TCHAR p5[100]; 
   TCHAR p6[100]; 
   TCHAR p7[100]; 
   TCHAR p8[100]; 
   char  p9[100]; 
   long len = 0; 

   _tcscpy(p3, _T("data1")); 
   _tcscpy(p4, _T("data2")); 
   _tcscpy(p5, _T("10.12")); 
   _tcscpy(p6, _T("2003-06-30")); 
   _tcscpy(p7, _T("12:12:12")); 
   _tcscpy(p8, _T("2003-06-30-17.54.27.710000")); 

   memset(p9, 0, sizeof(p9)); 
   p9[0] = 'X'; 
   p9[1] = 'Y'; 
   p9[2] = 'Z'; 

   rc = SQLAllocEnv(&henv); 
   // check return code ... 

   rc = SQLAllocConnect(henv, &hdbc); 
   // check return code ... 

   rc = SQLConnect(hdbc, (SQLTCHAR*)server, SQL_NTS,
				(SQLTCHAR*)uid, SQL_NTS, (SQLTCHAR*)pwd, SQL_NTS); 
   // check return code ... 

   rc = SQLAllocStmt(hdbc, &hstmt); 
   // check return code ... 

   // prepare the statement 
   rc = SQLPrepare(hstmt, _T("INSERT INTO t1 VALUES (?,?,?,?,?,?,?,?,?)"), SQL_NTS); 
   // check return code ... 

   // bind input parameters 
   rc = SQLBindParameter(hstmt, (unsigned short)1, SQL_PARAM_INPUT,
				SQL_C_LONG, SQL_INTEGER, 4, 0, &p1, sizeof(p1), &len); 
   // check return code ... 

   rc = SQLBindParameter(hstmt, (unsigned short)2, SQL_PARAM_INPUT, SQL_C_LONG,
					SQL_SMALLINT, 2, 0, &p2, sizeof(p2), &len); 
   // check return code ... 

   len = SQL_NTS; 
   rc = SQLBindParameter(hstmt, (unsigned short)3, SQL_PARAM_INPUT, SQL_C_TCHAR,
					SQL_CHAR, 0, 0, &p3[0], 100, &len); 
   // check return code ... 

   rc = SQLBindParameter(hstmt, (unsigned short)4, SQL_PARAM_INPUT, SQL_C_TCHAR,
					SQL_VARCHAR, 0, 0, &p4[0], 100, &len); 
   // check return code ... 

   rc = SQLBindParameter(hstmt, (unsigned short)5, SQL_PARAM_INPUT, SQL_C_TCHAR,
					SQL_DECIMAL, 8, 2, &p5[0], 100, &len); 
   // check return code ... 

   rc = SQLBindParameter(hstmt, (unsigned short)6, SQL_PARAM_INPUT, SQL_C_TCHAR,
					SQL_TYPE_DATE, 0, 0, &p6[0], 100, &len); 
   // check return code ... 

   rc = SQLBindParameter(hstmt, (unsigned short)7, SQL_PARAM_INPUT, SQL_C_TCHAR,
					SQL_TYPE_TIME, 0, 0, &p7[0], 100, &len); 
   // check return code ... 

   rc = SQLBindParameter(hstmt, (unsigned short)8, SQL_PARAM_INPUT, SQL_C_TCHAR,
					SQL_TYPE_TIMESTAMP, 0, 0, &p8[0], 100, &len); 
   // check return code ... 

   len = 3; 
   rc = SQLBindParameter(hstmt, (unsigned short)9, SQL_PARAM_INPUT, SQL_C_BINARY,
					SQL_BINARY, 0, 0, &p9[0], 100, &len); 
   // check return code ... 

   // execute the prepared statement 
   rc = SQLExecute(hstmt); 
   // check return code ... 

   rc = SQLFreeStmt(hstmt, SQL_DROP); 
   // check return code ... 

   rc = SQLDisconnect(hdbc); 
   // check return code ... 

   rc = SQLFreeConnect(hdbc); 
   // check return code ... 

   rc = SQLFreeEnv(henv); 
   // check return code ...

C Example

EXEC SQL BEGIN DECLARE SECTION; 
  char hostVarStmt1[50]; 
  short hostVarDeptnumb; 
EXEC SQL END DECLARE SECTION; 

/* prepare the statement with a parameter marker */ 
strcpy(hostVarStmt1, "DELETE FROM org WHERE deptnumb = ?"); 
EXEC SQL PREPARE Stmt1 FROM :hostVarStmt1;

/* execute the statement for hostVarDeptnumb = 15 */ 
hostVarDeptnumb = 15; 
EXEC SQL EXECUTE Stmt1 USING :hostVarDeptnumb;

JDBC Example

public static void parameterExample1() { 

   String driver = "com.ibm.db2e.jdbc.DB2eDriver"; 
   String url    = "jdbc:db2e:mysample"; 
   Connection conn = null; 
   PreparedStatement pstmt = null; 

   try 
   { 
      Class.forName(driver); 

      conn = DriverManager.getConnection(url); 

      // prepare the statement 
      pstmt = conn.prepareStatement("INSERT INTO t1 VALUES
                                                    (?, ?, ?, ?, ?, ?, ?, ?, ?)");

      // bind the input parameters 
      pstmt.setInt(1, 1); 
      pstmt.setShort(2, (short)2); 
      pstmt.setString(3, "data1"); 
      pstmt.setString(4, "data2"); 
      pstmt.setBigDecimal(5, new java.math.BigDecimal("12.34")); 
      pstmt.setDate(6, new java.sql.Date(System.currentTimeMillis() ) ); 
      pstmt.setTime(7, new java.sql.Time(System.currentTimeMillis() ) ); 
      pstmt.setTimestamp (8, new java.sql.Timestamp(System.currentTimeMillis() ) );
      pstmt.setBytes(9, new byte[] { (byte)'X', (byte)'Y', (byte)'Z' } ); 

      // execute the statement 
      pstmt.execute(); 

      pstmt.close(); 

      conn.close(); 
   } 
   catch (SQLException sqlEx) 
   { 
      while(sqlEx != null) 
      { 
          System.out.println("SQLERROR: \n" + sqlEx.getErrorCode() + 
             ", SQLState: " + sqlEx.getSQLState() + 
             ", Message: " + sqlEx.getMessage() + 
             ", Vendor: " + sqlEx.getErrorCode() ); 
         sqlEx = sqlEx.getNextException(); 
      } 
   } 
   catch (Exception ex) 
   { 
      ex.printStackTrace(); 
   } 
}

ADO.NET Example [C#]

public static void ParameterExample1() 
{ 
   DB2eConnection conn = null; 
   DB2eCommand cmd  = null; 
   String connString   = @"database=.\; uid=db2e; pwd=db2e"; 
   int i = 1; 

   try 
   { 
      conn = new DB2eConnection(connString); 

      conn.Open(); 

      cmd = new DB2eCommand("INSERT INTO t1 VALUES 
                                      (?, ?, ?, ?, ?, ?, ?, ?, ?)", conn);

      // prepare the command 
      cmd.Prepare(); 

      // bind the input parameters 
      DB2eParameter p1 = new DB2eParameter("@p1", DB2eType.Integer); 
      p1.Value = ++i; 
      cmd.Parameters.Add(p1); 

      DB2eParameter p2 = new DB2eParameter("@p2", DB2eType.SmallInt); 
      p2.Value = 100; 
      cmd.Parameters.Add(p2); 

      DB2eParameter p3 = new DB2eParameter("@p3", DB2eType.Char); 
      p3.Value = "data1"; 
      cmd.Parameters.Add(p3); 

      DB2eParameter p4 = new DB2eParameter("@p4", DB2eType.VarChar); 
      p4.Value = "data2"; 
      cmd.Parameters.Add(p4); 

      DB2eParameter p5 = new DB2eParameter("@p5", DB2eType.Decimal); 
      p5.Value = 20.25; 
      cmd.Parameters.Add(p5); 

      DB2eParameter p6 = new DB2eParameter("@p6", DB2eType.Date); 
      p6.Value = DateTime.Now; 
      cmd.Parameters.Add(p6); 

      DB2eParameter p7 = new DB2eParameter("@p7", DB2eType.Time); 
      p7.Value = new TimeSpan(23, 23, 23); 
      cmd.Parameters.Add(p7); 

      DB2eParameter p8 = new DB2eParameter("@p8", DB2eType.Timestamp); 
      p8.Value = DateTime.Now; 
      cmd.Parameters.Add(p8); 

      byte []barr = new byte[3]; 
      barr[0] = (byte)'X'; 
      barr[1] = (byte)'Y'; 
      barr[2] = (byte)'Z'; 

      DB2eParameter p9 = new DB2eParameter("@p9", DB2eType.Blob); 
      p9.Value = barr; 
      cmd.Parameters.Add(p9); 

      // execute the prepared command 
      cmd.ExecuteNonQuery(); 
   } 
   catch (DB2eException e1) 
   { 
      for (int i=0; i < e1.Errors.Count; i++) 
      { 
         Console.WriteLine("Error #" + i + "\n" + 
            "Message: " + e1.Errors[i].Message + "\n" + 
            "Native: " + e1.Errors[i].NativeError.ToString() + "\n" + 
            "SQL: " + e1.Errors[i].SQLState + "\n"); 
      } 
   } 
   catch (Exception e2) 
   { 
       Console.WriteLine(e2.Message); 
   } 
   finally 
   { 
      if (conn != null && conn.State != ConnectionState.Closed) 
      { 
            conn.Close(); 
            conn = null; 
      } 
   } 
}