|
|
IBM DB2 Parameters
The following source code shows an example of how DBConnect API handles specific IBM DB2 bind parameters.
/*
* db2_types testing parameters with DB2 and dbConnect API
* Copyright (C) 2004 Johnathan Ingram, jingram@rogueware.org
*
* This library is free software; you can redistribute it and/or
* modify it under the terms of the GNU Lesser General Public
* License as published by the Free Software Foundation; either
* version 2.1 of the License, or (at your option) any later version.
*
* This library is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this library; if not, write to the Free Software
* Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 US
*
* Note: This example requires the TypeTest table as defined in the SQL for DB2
*/
#include <iostream>
#include <string>
#include <limits.h>
#include "dbconn/dbconnect.h"
#include "dbconn/jDate.h"
using namespace std;
void
showTypeData(
DbQueryVar &conn,
const string &fields,
long fieldId)
throw(
BaseException)
{
char sqlQuery[2048];
sprintf(sqlQuery, "SELECT %s FROM TypeTest WHERE id = %ld", fields.c_str(), fieldId);
conn->command(sqlQuery);
conn->execute();
// Connection 1: List the contents of inserted record
cout << "Result Set:" << endl;
cout << "----------------------------------------- " << endl;
cout << "Field\tasString\tasLong\tasUnsignedLong\tasFloat\tasDateTime\tasBoolean\tasBinary" << endl;
cout << "-----\t--------\t------\t--------------\t-------\t----------\t---------\t--------" << endl;
if (conn->eof())
cout << "No rows in result set for this query" << endl;
else
{
while (!conn->eof())
{
conn->fetchNext();
// Output each field and do translation
for(int i=0; i<conn->fieldCount(); i++)
{
if (conn->getFieldByColumn(i)->isNULL())
cout << "NULL" << "\t";
else
{
cout << conn->getFieldByColumn(i)->name() << "\t";
// View the JDate class for what can be done with Date/Time values from asDateTime
// LOB/BLOB values will be hex encoded when obtained with asString
// LOB/BLOB will return a pointer to the binary data in mememory
// Can use cout << conn->getFieldByColumn(i)->getSize() to obtain the size of the LOB/BLOB
cout << conn->getFieldByColumn(i)->asString() << "\t";
cout << conn->getFieldByColumn(i)->asLong() << "\t";
cout << conn->getFieldByColumn(i)->asUnsignedLong() << "\t";
cout << conn->getFieldByColumn(i)->asFloat() << "\t";
cout << conn->getFieldByColumn(i)->asDateTime().asString() << "\t";
cout << conn->getFieldByColumn(i)->asBoolean() << "\t";
cout << conn->getFieldByColumn(i)->asBinary() << "\t";
cout << endl;
}
}
}
}
cout << endl;
}
int
main(
int argc, char** argv)
{
DbConnectionVar driver;
DbQueryVar conn;
string sqlQuery;
try
{
driver = new DbConnection(DbConnection::DB2, "./dbconnect.cfg");
// Connect to the database.
// Note: The host is not used as it uses the catalog system
driver->connect("dbconn", "letmein", "dbConnDB", "");
// Get a query connection object
conn = driver->requestQueryConnection();
// Test 1: DB2 Integer Parameter Bindings
cout << endl << endl;
cout << "TEST 1: DB2 Integer Parameter Bindings" << endl;
cout << "---------------------------" << endl;
sqlQuery =
"INSERT INTO TypeTest "
" (id, description, t_SMALLINT, t_INTEGER, t_BIGINT) "
"VALUES "
" (:id, :description, :t_SMALLINT, :t_INTEGER, :t_BIGINT) ";
conn->command(sqlQuery);
conn->bindParam("id")->setLong(1001);
conn->bindParam("description")->setString("10001: Integer Parameter Bindings");
conn->bindParam("t_SMALLINT")->setLong(900);
conn->bindParam("t_INTEGER")->setLong(LONG_MIN);
conn->bindParam("t_BIGINT")->setUnsignedLong(ULONG_MAX);
conn->execute();
showTypeData(conn, "id, description, t_SMALLINT, t_INTEGER, t_BIGINT", 1001);
// Test 2: DB2 Float/Real Parameter Bindings
cout << endl << endl;
cout << "TEST 2: DB2 Float/Real Parameter Bindings" << endl;
cout << "---------------------------" << endl;
sqlQuery =
"INSERT INTO TypeTest "
" (id, description, t_NUMERIC, t_FLOAT, t_REAL, t_DOUBLE, t_DECIMAL) "
"VALUES "
" (:id, :description, :t_NUMERIC, :t_FLOAT, :t_REAL, :t_DOUBLE, :t_DECIMAL) ";
conn->command(sqlQuery);
conn->bindParam("id")->setLong(1002);
conn->bindParam("description")->setString("10002: Float/Real Parameter Bindings");
conn->bindParam("t_NUMERIC")->setFloat(23459.2345);
conn->bindParam("t_FLOAT")->setFloat(9999.1234567);
conn->bindParam("t_REAL")->setFloat(1000.1234444);
conn->bindParam("t_DOUBLE")->setFloat(100000000.999954325);
conn->bindParam("t_DECIMAL")->setFloat(55.54);
conn->execute();
showTypeData(conn, "id, description, t_NUMERIC, t_FLOAT, t_REAL, t_DOUBLE, t_DECIMAL", 1002);
// Test 3: DB2 Character (String) Parameter Bindings
cout << endl << endl;
cout << "TEST 3: DB2 Character (String) Parameter Bindings" << endl;
cout << "---------------------------" << endl;
sqlQuery =
"INSERT INTO TypeTest "
" (id, description, t_CHAR, t_VARCHAR, t_CLOB, t_GRAPHIC, t_VARGRAPHIC) "
"VALUES "
" (:id, :description, :t_CHAR, :t_VARCHAR, :t_CLOB, :t_GRAPHIC, :t_VARGRAPHIC) ";
conn->command(sqlQuery);
conn->bindParam("id")->setLong(1003);
conn->bindParam("description")->setString("10003: Character (String) Parameter Bindings");
conn->bindParam("t_CHAR")->setString("This is the CHAR Field");
conn->bindParam("t_VARCHAR")->setString("This is the VARCHAR Field");
conn->bindParam("t_CLOB")->setString("This is the CLOB Field");
conn->bindParam("t_GRAPHIC")->setString("This is the GRAPHIC Field");
conn->bindParam("t_VARGRAPHIC")->setString("This is the VARGRAPHIC Field");
conn->execute();
showTypeData(conn, "id, description, t_CHAR, t_VARCHAR, t_CLOB, t_GRAPHIC, t_VARGRAPHIC", 1003);
// Test 4: DB2 Date & Time Parameter Bindings
cout << endl << endl;
cout << "TEST 4: DB2 Date & Time Parameter Bindings" << endl;
cout << "---------------------------" << endl;
JDate sTimestamp("1999-12-31 23:59:59");
JDate sDate("1623-12-31", JDate::SET_FMT_DATE_ISO);
JDate sTime("08:30:45", JDate::SET_FMT_TIME_ISO);
sqlQuery =
"INSERT INTO TypeTest "
" (id, description, t_TIMESTAMP, t_DATE, t_TIME) "
"VALUES "
" (:id, :description, :t_TIMESTAMP, :t_DATE, :t_TIME) ";
conn->command(sqlQuery);
conn->bindParam("id")->setLong(1004);
conn->bindParam("description")->setString("10004: Date & Time Parameter Bindings");
conn->bindParam("t_TIMESTAMP")->setDateTime(sTimestamp);
conn->bindParam("t_DATE")->setDate(sDate);
conn->bindParam("t_TIME")->setTime(sTime);
conn->execute();
showTypeData(conn, "id, description, t_TIMESTAMP, t_DATE, t_TIME", 1004);
// Test 5: DB2 Date & Time Parameter Bindings (Binding all as Timestamp)
cout << endl << endl;
cout << "TEST 5: DB2 Date & Time Parameter Bindings (Binding all as Timestamp)" << endl;
cout << "---------------------------" << endl;
JDate sTimestamp2("2099-12-31 23:59:59");
JDate sTimestamp3("2005-01-15", JDate::SET_FMT_DATE_ISO);
JDate sTimestamp4("18:47:12", JDate::SET_FMT_TIME_ISO);
sqlQuery =
"INSERT INTO TypeTest "
" (id, description, t_TIMESTAMP, t_DATE, t_TIME) "
"VALUES "
" (:id, :description, :t_TIMESTAMP, :t_DATE, :t_TIME) ";
conn->command(sqlQuery);
conn->bindParam("id")->setLong(1005);
conn->bindParam("description")->setString("10005: Date & Time Parameter Bindings (Binding all as Timestamp)");
conn->bindParam("t_TIMESTAMP")->setDateTime(sTimestamp2);
conn->bindParam("t_DATE")->setDateTime(sTimestamp3);
conn->bindParam("t_TIME")->setDateTime(sTimestamp4);
conn->execute();
showTypeData(conn, "id, description, t_TIMESTAMP, t_DATE, t_TIME", 1005);
// Test 6: DB2 Binary Types Parameter Bindings
cout << endl << endl;
cout << "TEST 6: DB2 Binary Types Parameter Bindings" << endl;
cout << "---------------------------" << endl;
char *sBinData = "Test BLOB ABCD";
sqlQuery =
"INSERT INTO TypeTest "
" (id, description, t_BLOB, t_BINARY) "
"VALUES "
" (:id, :description, :t_BLOB, :t_BINARY) ";
conn->command(sqlQuery);
conn->bindParam("id")->setLong(1006);
conn->bindParam("description")->setString("10006: Binary Types Parameter Bindings");
conn->bindParam("t_BLOB")->setBinary(sBinData, strlen(sBinData) + 1); // Include NULL Char
conn->bindParam("t_BINARY")->setBinary(sBinData, 1);
conn->execute();
showTypeData(conn, "id, description, t_BLOB, t_BINARY", 1006);
// Test 7: DB2 NULL Parameter Bindings
cout << endl << endl;
cout << "TEST 7: DB2 NULL Parameter Bindings" << endl;
cout << "---------------------------" << endl;
sqlQuery =
"INSERT INTO TypeTest "
" (id, description, t_TIMESTAMP) "
"VALUES "
" (:id, :description, :t_TIMESTAMP) ";
conn->command(sqlQuery);
conn->bindParam("id")->setLong(1007);
conn->bindParam("description")->setString("10007: NULL Parameter Bindings");
conn->bindParam("t_TIMESTAMP")->setNULL();
conn->execute();
showTypeData(conn, "id, description, t_TIMESTAMP", 1007);
// NB: We did not commit anything, so all our changes will rollback
// when connection goes out of scope
}
catch(BaseException &ex)
{
// Only need to catch a single exception. Can use 'name' etc to determine the actual exception
cout << "DbConnect Exception: " << ex.name << endl
<< " " << ex.code << " : " << ex.description << endl;
}
catch(...)
{
cout << "An Unknown exception has been trapped!\n" << endl;
}
cout << endl;
// The 'conn' and 'driver' objects are smart pointers and will cleanup as soon as they go out of scope
return 0;
}
|
