sqldb01.cpp

简单的ODBC访问接口

#include "stdafx.h"
#include <iostream>
#include <iomanip>
#include <string>
using namespace std;

#include "SQLDatabase.h"
#include "SQLRecordset.h"
#include "SQLInsert.h"
#include "SQLUpdate.h"
#include "SQLDelete.h"

int main(int argc, char* argv[])
{
   CSQLDatabase db;
   if ( ! db.Open( "MPHSample" ) ) 
   {
      cout << "couldn't open data source name" << endl;
      return 0;
   }

   cout << "good db open" << endl;
   cout << "supports transactions: " << (char*)((db.SupportsTransactions())?"yes":"no") << endl;
   db.SetAutoCommit( true );
   cout << "try getting some data..." << endl;

   ////////////////////////////////////////////////////
   //
   // Start by printing some records from the database
   //
   //
   CSQLRecordset rs( db );
   rs << "select provider, address1, city, state,"
      " zip from provider";
   rs.SetWhere( "provider < 'B'" ); // lt 1 screen full
   rs.SQLExecDirect();
   while ( rs.SQLFetch() )
   {
      int n = 1; // order is crucial; hence, the ctr
      string sProvider = rs.SQLGetData( n++ );
      string sAddress1 = rs.SQLGetData( n++ );
      string sCity = rs.SQLGetData( n++ );
      string sState = rs.SQLGetData( n++ );
      string sZip = rs.SQLGetData( n++ );

      cout  << left
            << setw(22) << sProvider
            << sAddress1 << ", "
            << sCity << ", "
            << sState << "  "
            << sZip
            << endl;
   }

   ////////////////////////////////////////////////////
   //
   // add a record
   //
   //
   cout << "insert Josh Angler's record..." << endl;
   try
   {
      CSQLInsert s( "provider" );
      s.SetColumn( "provider", "Angler, Josh" );
      // note that the column name's case is ignored
      // by the odbc translator; however, the data
      // goes in exactly as you typed it.
      s.SetColumn( "aDDRess1", "123 Anystreet" ); 
      s.SetColumn( "City", "Spokane" );
      s.SetColumn( "state", "WA" );
      s.SetColumn( "zip", "99204" );
      s.SetColumn( "dob", true, 12, 13, 1955 );
      s.SetColumn( "referrals", 0 );
      db.ExecuteSQL( s.GetStatement() );
   }
   catch ( CSQLException* e )
   {
      // in case the inserted record exists
      cout << endl << "Error on insert" << endl;
      cout << e->m_strError << endl;
   }

   ////////////////////////////////////////////////////
   //
   // update a record
   //
   //
   cout << "update Josh Angler's record..." << endl;
   try
   {
      CSQLUpdate s( "provider" );
      // where clause necessary for update
      s.SetWhere( "provider", "Angler, Josh" );
      s.SetColumn( "address1", "2903 West Pacific Ave." );
      db.ExecuteSQL( s.GetStatement() );
   }
   catch ( CSQLException* e )
   {
      cout << endl << "Error on update" << endl;
      cout << e->m_strError << endl;
   }

   ////////////////////////////////////////////////////
   //
   // Print records after the changes
   //
   //
   cout << endl << endl << "Print records after changes..." << endl;
   try
   {
      CSQLRecordset rs( db );
      rs << "select provider, address1, city, state,"
         " zip, dob from provider";
      rs.SetWhere( "provider < 'B'" ); // lt 1 screen full
      rs.SQLExecDirect();
      while ( rs.SQLFetch() )
      {
         int n = 1; // order is crucial; hence, the ctr
         string sProvider = rs.SQLGetData( n++ );
         string sAddress1 = rs.SQLGetData( n++ );
         string sCity = rs.SQLGetData( n++ );
         string sState = rs.SQLGetData( n++ );
         string sZip = rs.SQLGetData( n++ );
         string sDOB = rs.SQLGetData( n++ );

         cout  << left
               << setw(22) << sProvider
               << sAddress1 << ", "
               << sCity << ", "
               << sState << "  "
               << sZip << " "
               << sDOB
               << endl;
      }
   }
   catch ( CSQLException* e )
   {
      cout << endl << "Error on second recordset" << endl;
      cout << e->m_strError << endl;
   }

   ////////////////////////////////////////////////////
   //
   // delete a record
   //
   //
   cout << "delete Josh Angler's record..." << endl;
   try
   {
      CSQLDelete s( "provider" );
      // where clause necessary for delete
      s.SetWhere( "provider", "Angler, Josh" );
      cout << s.GetStatement() << endl;
      db.ExecuteSQL( s.GetStatement() );
   }
   catch ( CSQLException* e )
   {
      cout << endl << "Error on delete" << endl;
      cout << e->m_strError << endl;
   }

   int a = 0;

	return 0;
}