pgsql_types.cpp

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

/*
 * pgsql_types testing types with Postgresql 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 Postgresql
 */


#include <iostream>
#include <string>


#include "dbconn/dbconnect.h"

using namespace std;


void 
showTypeData(
      DbQueryVar &conn)
   throw(
      BaseException)
{   
   // Connection 1: List the contents of the table
   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::POSTGRESQL, "./dbconnect.cfg");

      // Connect to the database. 
      driver->connect("dbconnect", "letmein", "dbConnectDB", "localhost");

      // Get a query connection object
      conn = driver->requestQueryConnection();

      
      // Test 1: Postgresql Integer Types
      cout << endl << endl;
      cout << "TEST 1: Postgresql Integer Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_SMALLINT, t_INTEGER, t_BIGINT "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(1);
      conn->execute();
      showTypeData(conn);


      // Test 2: Postgresql Floating Point Types
      cout << endl << endl;
      cout << "TEST 2: Postgresql Floating Point Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_NUMERIC, t_REAL, t_DOUBLE_PRECISION "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(2);
      conn->execute();
      showTypeData(conn);


      // Test 3: Postgresql Character (String) Types
      cout << endl << endl;
      cout << "TEST 3: Postgresql Character (String) Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_CHAR, t_VARCHAR, t_TEXT "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(3);
      conn->execute();
      showTypeData(conn);


      // Test 4: Postgresql Date & Time Types
      cout << endl << endl;
      cout << "TEST 4: Postgresql Date & Time Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_TIMESTAMP, t_TIMESTAMPTZ, t_INTERVAL, t_DATE, t_TIME "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(4);
      conn->execute();
      showTypeData(conn);


      // Test 5: Postgresql Boolean Types
      cout << endl << endl;
      cout << "TEST 5: Postgresql Boolean Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_BOOLEAN "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(5);
      conn->execute();
      showTypeData(conn);


      // Test 6: Postgresql Binary Types
      cout << endl << endl;
      cout << "TEST 6: Postgresql Binary Types" << endl;
      cout << "---------------------------" << endl;
      
      sqlQuery = 
         "SELECT "
         "  t_BINARY "
         "FROM "
         "  TypeTest "
         "WHERE "
         "  id = :id ";
      conn->command(sqlQuery);
      conn->bindParam("id")->setLong(6);
      conn->execute();
      showTypeData(conn);
      
   }
   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;
}