パラメーター・マーカー
パラメーター・マーカー (通常、疑問符 (?) またはコロンの後に変数名が続く形 (:var1) で示される) は、ステートメント実行中に値が取得される SQL ステートメント内のプレースホルダーです。
アプリケーションはパラメーター・マーカーをアプリケーション変数に関連付けます。 ステートメントの実行中、これらの変数の値によって個々のパラメーター・マーカーがそれぞれ置き換えられます。 処理中にデータ変換が発生することがあります。
パラメーター・マーカーの利点
何度も実行する必要のある SQL ステートメントの場合、SQL ステートメントを一回だけ準備し、パラメーター・マーカーを使って実行時に入力値を置換することにより、照会プランを再利用する方がたいてい効率的です。 パラメーター・マーカーは、次の 2 つの方法のいずれかで示されます。
- 最初のスタイルは「？」で始まる 文字は、動的SQL実行（動的埋め込みSQL、CLI、 Perl など）で使用されます。
- 2 番目のスタイルは、組み込み SQL の標準的な構造を表し、変数の名前にコロンの接頭部が付きます (:var1)。 このスタイルは静的 SQL 実行で使用され、普通はホスト変数と呼ばれます。
どちらかのスタイルを使うことによって、アプリケーション変数が SQL ステートメント内で置換される場所を示します。 パラメーター・マーカーは番号で参照され、1 から始まり、左から右へ順番に番号が付けられます。 SQL ステートメントが実行される前に、アプリケーションは変数ストレージ域を SQL ステートメントに指定された各パラメーター・マーカーにバインドしなければなりません。 さらに、バインドされる変数は有効なストレージ域でなければならず、準備ステートメントがデータベースに対して実行されるときに入力データ値を含んでいなければなりません。
以下の例では、2 つのパラメーター・マーカーを含んだ SQL ステートメントを示しています。
SELECT
* FROM customers WHERE custid = ? AND lastname = ?
サポートされるタイプ
SQL ステートメント内の特定の場所で、非型付きパラメーター・マーカーを指定できます。 表 1 では、非型付きパラメーター・マーカー使用時の制約事項がリストされています。
| 非型付きパラメーター・マーカーの場所 | データ・タイプ |
|---|---|
| 式: 選択リスト内で単独 | エラー |
| 式: 算術演算子の両オペランド | エラー |
| 述部: IN 述部の左側のオペランド | エラー |
| 述部: 関係演算子の両オペランド | エラー |
| 関数: 集約関数のオペランド | エラー |
例
CLI/ODBC、JDBC、および ADO.NET といった、標準インターフェースを使用してデータベースにアクセスできます。 次のコード・スニペットは、各データ・アクセス API に対するパラメーター・マーカーの付いた準備ステートメントの使用法を示しています。
以下の表 t1 の表スキーマについて考慮してください。ここで、列 c1 が表 t1 の主キーです。
| 列名 | Db2® | NULL 可能 |
|---|---|---|
| c1 | INTEGER | いいえ |
| c2 | SMALLINT | はい |
| c3 | CHAR(20) | はい |
| c4 | VARCHAR(20) | はい |
| c5 | DECIMAL(8,2) | はい |
| c6 | 日付 | はい |
| c7 | 時刻 | はい |
| c8 | TIMESTAMP | はい |
| c9 | BLOB(30) | はい |
以下の例では、準備ステートメントを使って行を表 t1 に挿入する方法を示しています。
CLI の例
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 の例
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 の例
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 の例 [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;
}
}
}