DB2 Everyplace provides a rich set of standard interfaces including CLI/ODBC, JDBC, and ADO.NET to access data efficiently. The following example 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.
| Column name | DB2 Everyplace 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.
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 ...
}
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();
}
}
[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;
}
}
}
Related concepts