RDBC/macros/RDBCscroll.C

0001 // $Id: RDBCscroll.C,v 1.1.1.1 2004/02/18 20:58:02 dave Exp $
0002 //
0003 //     This file is part of the RDBC
0004 //     Author: Valeriy Onuchin <onuchin@sirius.ihep.su>
0005 /////////////////////////////////////////////////////////////////////
0006 //
0007 // This example based on libodbc++/tests/scroll.cpp
0008 //
0009 ///*
0010 //  This should work with almost any almost-compliant database out there,
0011 //  providing it supports scrollable cursors.
0012 // */
0013 //
0014 //////////////////////////////////////////////////////////////////////
0015 //
0016 // Usage:
0017 //
0018 // root[] gSystem->Load("libRDBC.so");    // load library
0019 // root[] .L RDBCscroll.C                 // load macro
0020 // root[] RDBCscroll(dsn,usr,psw);        // execute the function from macro
0021 //
0022 //
0023 //////////////////////////////////////////////////////////////////////
0024 //
0025 //    REQUIREMENTS
0026 //
0027 //      o You must have create table/drop table rights
0028 //
0029
0030 #ifndef __CINT__
0031 // g++ -c -W RDBCscroll.C -I$ROOTSYS/include
0032
0033 #include <TError.h>
0034 #include <TString.h>
0035 #include <TSQLDriverManager.h>
0036 #include <TSQLConnection.h>
0037 #include <TSQLDatabaseMetaData.h>
0038 #include <TSQLResultSet.h>
0039 #include <TSQLResultSetMetaData.h>
0040 #include <TSQLPreparedStatement.h>
0041 #include <TROOT.h>
0042 #include <TInterpreter.h>
0043 #endif // __CINT__
0044
0045 const TString tableName = "odbcxx_test";
0046 const Int_t tableRows = 1000;
0047
0048 //__________________________________________________________________
0049 void CreateStuff(TSQLConnection* con)
0050 {
0051    // create table
0052
0053    TSQLStatement* stmt = con->CreateStatement();
0054    TString str = "create table ";
0055    str += tableName;
0056    str += "( id integer not null primary key, ";
0057    str += "name varchar(40) not null)";
0058    stmt->ExecuteUpdate(str.Data());
0059
0060    str = "Table ";
0061    str += tableName;
0062    str += " created.";
0063    printf("%s \n",str.Data());
0064
0065    delete stmt;
0066 }
0067
0068 //___________________________________________________________________
0069 void DropStuff(TSQLConnection* con)
0070 {
0071    // Drops the database objects.
0072
0073    TSQLStatement* stmt=con->CreateStatement();
0074
0075    TString str = "drop table ";
0076    str += tableName;
0077    stmt->ExecuteUpdate(str.Data());
0078
0079    str = "Dropped table ";
0080    str +=  tableName;
0081    printf("%s\n",str.Data());
0082
0083    delete stmt;
0084 }
0085
0086 //__________________________________________________________________
0087 TString MakeName(Int_t n)
0088 {
0089    TString str;
0090    char number[10];
0091
0092    sprintf(number,"%d",n);
0093    str  = "This is row number ";
0094    str += number;
0095    return str;
0096 }
0097
0098 //__________________________________________________________________
0099 void Populate(TSQLConnection* con)
0100 {
0101    //
0102
0103    TString str = "insert into ";
0104    str += tableName;
0105    str += " (id,name) values(?,?)";
0106
0107    TSQLPreparedStatement* pstmt = con->PrepareStatement(str.Data());
0108
0109    for(int i=0; i<tableRows; i++) {
0110       pstmt->SetInt(1,i);
0111       pstmt->SetString(2,MakeName(i));
0112       pstmt->ExecuteUpdate();
0113    }
0114
0115    delete pstmt;
0116    con->Commit();
0117
0118    printf("Inserted %d rows\n",tableRows);
0119 }
0120
0121 //__________________________________________________________________
0122 void Compare(TSQLConnection* con)
0123 {
0124    // decide whether we should use a scroll insensitive
0125    // or a scroll sensitive cursor
0126
0127    int rstype;
0128    int rsconc;
0129    TString str;
0130    TString name;
0131
0132    TSQLDatabaseMetaData* md = con->GetMetaData();
0133
0134    if(md->SupportsResultSetType(kTYPE_SCROLL_INSENSITIVE)) {
0135       rstype = kTYPE_SCROLL_INSENSITIVE;
0136    } else if(md->SupportsResultSetType(kTYPE_SCROLL_SENSITIVE)) {
0137       rstype = kTYPE_SCROLL_SENSITIVE;
0138    } else {
0139       printf("Skipping compare, data source does not support scrollable cursors\n");
0140       return;
0141    }
0142
0143    if(md->SupportsResultSetConcurrency(rstype,kCONCUR_READ_ONLY)) {
0144       // this is all we need
0145       rsconc = kCONCUR_READ_ONLY;
0146    } else {
0147       rsconc = kCONCUR_UPDATABLE;
0148    }
0149
0150    TSQLStatement* stmt = con->CreateStatement(rstype,rsconc);
0151    str = "select id,name from ";
0152    str += tableName;
0153    TSQLResultSet* rs = stmt->ExecuteQuery( str );
0154    if(!rs) return;
0155
0156    Assert(rs->IsBeforeFirst());
0157    Assert(rs->First());
0158    Assert(!rs->IsBeforeFirst());
0159    Assert(rs->IsFirst());
0160
0161    Assert(rs->Last());
0162    Assert(rs->IsLast());
0163    Assert(!rs->IsAfterLast());
0164    rs->AfterLast();
0165    Assert(rs->IsAfterLast());
0166
0167    Assert(rs->Previous());
0168    Assert(rs->IsLast());
0169
0170    printf("Positioned on the last row (%d)\n", rs->GetRow());
0171
0172    int i = tableRows;
0173
0174    do {
0175       i--;
0176       name = MakeName(i);
0177       Assert(rs->GetInt(1) == i);
0178       Assert(rs->GetString(2) == name);
0179    } while(rs->Previous());
0180
0181    Assert(i==0);
0182    Assert(rs->IsBeforeFirst());
0183
0184    printf("%d rows checked with expected values.\n",tableRows);;
0185
0186    delete stmt; //will kill rs
0187 }
0188
0189 //__________________________________________________________________
0190 Int_t RDBCscroll( const Text_t* dsn,
0191                   const Text_t* usr,
0192                   const Text_t* pwd )
0193 {
0194    //
0195
0196    TSQLConnection* con;
0197    TString str;
0198
0199    // connect to error handler
0200    TSQL::SetHandler("Throw(TSQLException*)");
0201
0202    str = "Connecting to dsn="; str += dsn;
0203    str += ", uid="; str += usr;
0204         str += ", pwd="; str += pwd;
0205    str += " ...";
0206    printf("%s\n",str.Data());
0207
0208    con = gSQLDriverManager->GetConnection(dsn,usr,pwd);
0209
0210    if(!con) return -1;  // failed to connect
0211
0212    printf("\t\t\t DONE.\n");
0213
0214    // we don't want autocommit
0215    if( con->GetMetaData()->SupportsTransactions() ) {
0216       con->SetAutoCommit(kFALSE);
0217    }
0218
0219    DropStuff(con);
0220    CreateStuff(con);
0221    Populate(con);
0222    Compare(con);
0223    con->Commit();
0224
0225    DropStuff(con);
0226    con->Commit();
0227    con->Close();
0228    return 0;
0229 }
0230
0231 //___________________________________________________________________
0232 void Catch(TSQLException* e)
0233 {
0234    // handle exceptions
0235
0236    TString str = e->GetMessage();
0237    printf("SQL Error: %s\n",str.Data());
0238 }