有关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;
TCHAR p4;
TCHAR p5;
TCHAR p6;
TCHAR p7;
TCHAR p8;
charp9;
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 = 'X';
p9 = 'Y';
p9 = '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, 100, &len);
// check return code ...
rc = SQLBindParameter(hstmt, (unsigned short)4, SQL_PARAM_INPUT, SQL_C_TCHAR,
SQL_VARCHAR, 0, 0, &p4, 100, &len);
// check return code ...
rc = SQLBindParameter(hstmt, (unsigned short)5, SQL_PARAM_INPUT, SQL_C_TCHAR,
SQL_DECIMAL, 8, 2, &p5, 100, &len);
// check return code ...
rc = SQLBindParameter(hstmt, (unsigned short)6, SQL_PARAM_INPUT, SQL_C_TCHAR,
SQL_TYPE_DATE, 0, 0, &p6, 100, &len);
// check return code ...
rc = SQLBindParameter(hstmt, (unsigned short)7, SQL_PARAM_INPUT, SQL_C_TCHAR,
SQL_TYPE_TIME, 0, 0, &p7, 100, &len);
// check return code ...
rc = SQLBindParameter(hstmt, (unsigned short)8, SQL_PARAM_INPUT, SQL_C_TCHAR,
SQL_TYPE_TIMESTAMP, 0, 0, &p8, 100, &len);
// check return code ...
len = 3;
rc = SQLBindParameter(hstmt, (unsigned short)9, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_BINARY, 0, 0, &p9, 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 示例
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;
barr = (byte)'X';
barr = (byte)'Y';
barr = (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;
}
}
}
页:
[1]