db2_parameters.cpp

C++连接一写常用数据库的接口

/*
 * db2_types testing parameters with DB2 and dbConnect API
 * Copyright (C) 2004 Johnathan Ingram, [e-mail]
 *
 * 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;
}