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

最新推荐文章于 2022-08-11 09:46:18 发布

最新推荐文章于 2022-08-11 09:46:18 发布 · 113 阅读

文章标签：

博客介绍了 SQL 参数标记，对于需多次执行的 SQL 语句，可准备一次并通过参数标记替换输入值。在 DB2 Everyplace 中，参数标记用“?”表示，按编号引用。还给出包含参数标记的 SQL 语句示例，以及使用 CLI、JDBC、ADO.NET 等接口操作的代码示例。

参数标记的概述

对于需要执行多次的 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[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;
      }
   }
}