有关prepare statement在ODBC、JDBC、ADO.NET上的对比使用(以DB2为例)

漂亮蓝影

参数标记的概述
　　对于需要执行多次的 SQL 语句，通常准备 SQL 语句一次并通过在运行时期间使用参数标记来替换输入值以重复使用查询方案比较好。
　　在 DB2 Everyplace 中，参数标记由“?”字符表示并指示在 SQL 语句中的何处替换应用程序变量。参数标记将按编号引用，且它们是从左至右连续编号的（从 1 开始）。在执行 SQL 语句之前，应用程序必须将变量存储区绑定至在 SQL 语句中指定的每个参数标记。此外，绑定变量必须是有效的存储区且在对数据库执行准备语句时必须包含输入数据值。
　　以下示例演示包含两个参数标记的 SQL 语句。
　　SELECT * FROM customers WHERE custid = ? AND lastname = ?

参数标记使用的示例
　　DB2 Everyplace 提供一组丰富的标准接口（包括 CLI/ODBC、JDBC 和 ADO.NET）以有效地存取数据。以下示例代码片段显示将带有参数标记的准备语句用于每个数据存取 API 的情况。
　　考虑表 t1 的以下表模式，其中列 c1 是表 t1 的主键。
　　表 1. 示例表模式


列名
DB2 Everyplace 数据类型
可空


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
　　以下示例演示如何使用准备语句将一行插入表 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 ...
}


　　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.Message + "\n" +
"Native: " + e1.Errors.NativeError.ToString() + "\n" +
"SQL: " + e1.Errors.SQLState + "\n");
}
}
catch (Exception e2)
{
Console.WriteLine(e2.Message);
}
finally
{
if (conn != null && conn.State != ConnectionState.Closed)
{
conn.Close();
conn = null;
}
}
}