RDBC/macros/RDBCmysql.C

0001 // $Id: RDBCmysql.C,v 1.1.1.1 2004/02/18 20:58:02 dave Exp $
0002 //
0003 //   !!! not working yet !!!
0004 //  This is a simple test to be run against a mysql database
0005 //  Assure you have create table permissions before you run.
0006 //
0007
0008 #ifndef __CINT__
0009 // g++ -c -Wall RDBCmysql.C -I$ROOTSYS/include -I../include
0010
0011 #include <RDBC/TSQLDriverManager.h>
0012 #include <RDBC/TSQLConnection.h>
0013 #include <RDBC/TSQLResultSet.h>
0014 #include <RDBC/TSQLResultSetMetaData.h>
0015 #include <RDBC/TSQLPreparedStatement.h>
0016
0017 #endif // __CINT__
0018
0019
0020 const char* testchars="abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
0021
0022 const int odbctestRows=5000;
0023
0024 const int longtestRows=30;
0025 const int longtestSize=123456;
0026
0027 int assertionsFailed=0;
0028
0029 //___________________________________________________________________
0030 Bool_t feq(const double& a, const double& b)
0031 {
0032    //
0033    return (fabs(a-b)<=a*0.0000001);
0034 }
0035
0036 //////////////////////////////////////////////////////////////////////
0037 class ValueGen1
0038 {
0039 private:
0040    Int_t fCnt;
0041    Int_t fMax;
0042
0043 public:
0044    ValueGen1(Int_t max) : fCnt(0), fMax(max) {}
0045    ~ValueGen1() {}
0046
0047    Bool_t Next() { return ((++fCnt) <= fMax); }
0048    Int_t  cnt() const {return fCnt; }
0049
0050    signed char i1() const { return (signed char)(fCnt%256 - 128); }
0051    short       i2() const { return (short)(fCnt%65536 - 32768); }
0052    int         i3() const { return (int)(fCnt%16777215-8388608); }
0053    int         i4() const { return (int)fCnt; }
0054    Long_t      i5() const { return ((Long_t)fCnt)*1000000L; }
0055    float       f1() const { return ((float)fCnt); }
0056    float       f2() const { return ((float)fCnt/10); }
0057    double      f3() const { return ((double)fCnt/20);}
0058    Long_t      d1() const { return (((Long_t)fCnt)-fMax/2)*4567;}
0059    TString     s1() const { return TString(&testchars[fCnt%strlen(testchars)],1); }
0060    TString     s3() const { return this->s1(); }
0061    TString     s2() const { char buf[41];
0062                             snprintf(buf,41,"This is row number %d",fCnt);
0063                             return TString_C(buf); }
0064    TString     s4() const { return this->s2(); }
0065
0066   Date dt() {
0067     return Date((time_t)fCnt*10000);
0068   }
0069
0070   Time t() {
0071     return Time((time_t)fCnt);
0072   }
0073
0074   Timestamp ts() {
0075     return Timestamp((time_t)fCnt*10000);
0076   }
0077 //this generates the values for odbctest
0078 };
0079
0080
0081 //////////////////////////////////////////////////////////////////////
0082 class ValueGen2 {
0083 private:
0084   Int_t fCnt;
0085   Int_t rows_;
0086
0087   char currentC[longtestSize+1];
0088   char currentB[longtestSize];
0089
0090 public:
0091   ValueGen2(Int_t rows)
0092     :fCnt(0), rows_(rows) {}
0093   ~ValueGen2() {}
0094
0095   Bool_t Next() {
0096     return ((++fCnt) <= rows_);
0097   }
0098
0099   int id() {
0100     return fCnt;
0101   }
0102
0103   TBuffer* c() {
0104     int len=strlen(testchars);
0105     for(int i=0; i<longtestSize; i++) {
0106       currentC[i]=testchars[(i+fCnt)%len];
0107     }
0108     currentC[longtestSize]=0;
0109 #if !defined(ODBCXX_QT)
0110     isstream* s=new isstream(currentC,longtestSize);
0111 #else
0112     QBuffer* s=new QBuffer();
0113     s->open(IO_WriteOnly);
0114     s->writeBlock(currentC,longtestSize);
0115     s->close();
0116     s->open(IO_ReadOnly);
0117 #endif
0118     return s;
0119   }
0120
0121   TBuffer* b() {
0122     for(int i=0; i<longtestSize; i++) {
0123       currentB[i]=((i+fCnt)%256);
0124     }
0125 #if !defined(ODBCXX_QT)
0126     isstream* s=new isstream(currentB,longtestSize);
0127 #else
0128     QBuffer* s=new QBuffer();
0129     s->open(IO_WriteOnly);
0130     s->writeBlock(currentB,longtestSize);
0131     s->close();
0132     s->open(IO_ReadOnly);
0133 #endif
0134     return s;
0135   }
0136
0137 //this is for the BLOB/CLOB test
0138 };
0139
0140 //___________________________________________________________________
0141 Bool_t compareStreams(TBuffer* s1, TBuffer* s2)
0142 {
0143   Int_t cnt=0;
0144 #if !defined(ODBCXX_QT)
0145   char c1, c2;
0146   while(s1->get(c1) && s2->get(c2)) {
0147     cnt++;
0148     if(c1!=c2)
0149       return false;
0150   }
0151 #else
0152   char buf1[1024];
0153   char buf2[1024];
0154   int r1, r2;
0155   while((r1=s1->readBlock(buf1,1024))!=-1 &&
0156     (r2=s2->readBlock(buf2,1024))!=-1) {
0157     if(r1!=r2) return false;
0158
0159     for(int i=0; i<r1; i++) {
0160       cnt++;
0161       if(buf1[i]!=buf2[i]) return false;
0162     }
0163   }
0164 #endif
0165   return (cnt==longtestSize);
0166 }
0167
0168 //___________________________________________________________________
0169 void createTables(TSQLConnection* con)
0170 {
0171   cout << "Creating tables:" << flush;
0172   TSQLPreparedStatement* pstmt = con->PrepareStatement
0173     ("create table odbctest ("
0174      "i1 tinyint not null, "
0175      "i2 smallint not null, "
0176      "i3 mediumint not null, "
0177      "i4 int not null, "
0178      "i5 bigint not null, "
0179      "f1 float(4) not null, "
0180      "f2 float(8) not null, "
0181      "f3 double(10,3) not null, "
0182      "d1 decimal(20,5) not null, "
0183      "s1 char(1) not null, "
0184      "s2 char(40) not null, "
0185      "s3 varchar(1) not null, "
0186      "s4 varchar(40) not null, "
0187      "dt date not null, "
0188      "t time not null, "
0189      "ts datetime not null"
0190      ")");
0191   pstmt->executeUpdate();
0192   cout << " odbctest" << flush;
0193   delete pstmt;
0194
0195   pstmt=con->PrepareStatement
0196     ("create table odbctest2 ("
0197      "id int not null, "
0198      "c mediumtext, "
0199      "b mediumblob)");
0200   pstmt->executeUpdate();
0201   cout << " odbctest2" << flush;
0202
0203   delete pstmt;
0204
0205   cout << endl;
0206 }
0207
0208 //___________________________________________________________________
0209 void populateTables(TSQLConnection* con)
0210 {
0211
0212   cout << "Populating:" << flush;
0213
0214   PreparedStatement* pstmt=con->PrepareStatement
0215     ("insert into odbctest(i1,i2,i3,i4,i5,f1,f2,f3,d1,s1,s2,s3,s4,dt,t,ts) "
0216      "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
0217
0218   ValueGen1 vg(odbctestRows);
0219   while(vg.Next()) {
0220     pstmt->setByte(1,vg.i1());
0221     pstmt->setShort(2,vg.i2());
0222     pstmt->setInt(3,vg.i3());
0223     pstmt->setInt(4,vg.i4());
0224     pstmt->setLong(5,vg.i5());
0225     pstmt->setFloat(6,vg.f1());
0226     pstmt->setFloat(7,vg.f2());
0227     pstmt->setDouble(8,vg.f3());
0228     pstmt->setLong(9,vg.d1());
0229     pstmt->setString(10,vg.s1());
0230     pstmt->setString(11,vg.s2());
0231     pstmt->setString(12,vg.s3());
0232     pstmt->setString(13,vg.s4());
0233     pstmt->setDate(14,vg.dt());
0234     pstmt->setTime(15,vg.t());
0235     pstmt->setTimestamp(16,vg.ts());
0236     pstmt->executeUpdate();
0237   }
0238
0239   delete pstmt;
0240   cout << " odbctest (" << odbctestRows << ")" << flush;
0241
0242   pstmt=con->PrepareStatement
0243     ("insert into odbctest2(id,c,b) values(?,?,?)");
0244
0245   ValueGen2 vg2(longtestRows);
0246   while(vg2.Next()) {
0247     pstmt->setInt(1,vg2.id());
0248     TBuffer* cs=vg2.c();
0249     pstmt->setAsciiStream(2,cs,longtestSize);
0250     TBuffer* bs=vg2.b();
0251     pstmt->setBinaryStream(3,bs,longtestSize);
0252
0253     pstmt->executeUpdate();
0254
0255     delete cs;
0256     delete bs;
0257   }
0258
0259   // insert an extra row containing NULL for c and b
0260   pstmt->setInt(1,vg2.id()+1);
0261   pstmt->setNull(2,Types::LONGVARCHAR);
0262   pstmt->setNull(3,Types::LONGVARBINARY);
0263   pstmt->executeUpdate();
0264
0265   delete pstmt;
0266
0267   cout << " odbctest2 (" << longtestRows << ")" << flush;
0268
0269   cout << endl;
0270 }
0271
0272 //___________________________________________________________________
0273 void checkTables(TSQLConnection* con)
0274 {
0275   cout << "Checking:" << flush;
0276
0277   Statement* stmt=con->createStatement();
0278   stmt->setFetchSize(37); // some odd number
0279   ResultSet* rs=stmt->executeQuery
0280     ("select i1,i2,i3,i4,i5,f1,f2,f3,d1,s1,s2,s3,s4,dt,t,ts from odbctest");
0281
0282   cout << " odbctest" << flush;
0283
0284   ValueGen1 vg(odbctestRows);
0285   Int_t cnt=0;
0286   while(rs->Next() && vg.Next()) {
0287     cnt++;
0288
0289     Assert(vg.cnt()==rs->GetRow());
0290
0291     Assert(vg.i1()==rs->GetByte(1) &&
0292        vg.i1()==rs->GetByte("i1"));
0293     Assert(vg.i2()==rs->GetShort(2) &&
0294        vg.i2()==rs->GetShort("i2"));
0295     Assert(vg.i3()==rs->GetInt(3) &&
0296        vg.i3()==rs->GetInt("i3"));
0297     Assert(vg.i4()==rs->GetInt(4) &&
0298        vg.i4()==rs->GetInt("i4"));
0299     Assert(vg.i5()==rs->GetLong(5) &&
0300        vg.i5()==rs->GetLong("i5"));
0301
0302     Assert(feq(vg.f1(),rs->GetFloat(6)) &&
0303        feq(vg.f1(),rs->GetFloat("f1")));
0304     Assert(feq(vg.f2(),rs->GetFloat(7)) &&
0305        feq(vg.f2(),rs->GetFloat("f2")));
0306     Assert(feq(vg.f3(),rs->GetDouble(8)) &&
0307        feq(vg.f3(),rs->GetDouble("f3")));
0308
0309     Assert(vg.d1()==rs->GetLong(9) &&
0310        vg.d1()==rs->GetLong("d1"));
0311
0312     Assert(vg.s1()==rs->GetString(10)
0313        && vg.s1()==rs->GetString("s1"));
0314     Assert(vg.s2()==rs->GetString(11) &&
0315        vg.s2()==rs->GetString("s2"));
0316     Assert(vg.s3()==rs->GetString(12) &&
0317        vg.s3()==rs->GetString("s3"));
0318     Assert(vg.s4()==rs->GetString(13) &&
0319        vg.s4()==rs->GetString("s4"));
0320
0321     Assert(vg.dt().toString()==rs->GetString(14));
0322     Assert(vg.t().toString()==rs->GetString(15));
0323     Assert(vg.ts().toString()==rs->GetString(16));
0324   }
0325
0326   delete rs;
0327   delete stmt;
0328
0329   Assert(cnt==odbctestRows);
0330   cout << "(" << cnt << ")" << flush;
0331
0332   stmt=con->createStatement();
0333   stmt->setFetchSize(10);
0334   rs=stmt->executeQuery("select id,c,b from odbctest2");
0335   // since we have LONGVARwhatevers in the result set
0336   // this should fall down to 1
0337   Assert(rs->GetFetchSize()==1);
0338
0339   ValueGen2 vg2(longtestRows);
0340   cnt=0;
0341   cout << " odbctest2" << flush;
0342   while(rs->Next() && vg2.Next()) {
0343     cnt++;
0344     Assert(vg2.id()==rs->GetInt("id"));
0345     TBuffer* cs=vg2.c();
0346     TBuffer* bs=vg2.b();
0347     Assert(compareStreams(cs,rs->GetAsciiStream("c")));
0348     Assert(compareStreams(bs,rs->GetBinaryStream("b")));
0349     delete cs;
0350     delete bs;
0351   }
0352
0353   // here, rs->next has been called an extra time above
0354   // (we're at the row containing NULL values)
0355   TBuffer* tmp=rs->GetAsciiStream("c");
0356   Assert(rs->WasNull());
0357   tmp=rs->GetBinaryStream("b");
0358   Assert(rs->WasNull());
0359
0360   Assert(cnt==longtestRows);
0361   cout << "(" << cnt << ")" << flush;
0362
0363   delete rs;
0364   delete stmt;
0365
0366   cout << endl;
0367 }
0368
0369 //___________________________________________________________________
0370 Bool_t dropTable(TSQLConnection* con, const TString& tableName)
0371 {
0372   Bool_t r=true;
0373   TSQLPreparedStatement* pstmt=con->PrepareStatement
0374     ("drop table "+tableName);
0375   try {
0376     pstmt->executeUpdate();
0377   } catch(SQLException& e) { r=false; }
0378
0379   delete pstmt;
0380   return r;
0381 }
0382
0383 //___________________________________________________________________
0384 void dropTables(TSQLConnection* con)
0385 {
0386   cout << "Dropping tables:" << flush;
0387   if(dropTable(con,"odbctest")) {
0388     cout << " odbctest" << flush;
0389   }
0390
0391   if(dropTable(con," odbctest2")) {
0392     cout << " odbctest2" << flush;
0393   }
0394   cout << endl;
0395 }
0396
0397
0398 #include "../src/dtconv.h"
0399
0400 ////////////////////////////////////////////////////////////////////
0401 int main(int argc, char** argv)
0402 {
0403   if(argc!=2 && argc!=4) {
0404     cerr << "Usage: " << argv[0] << " connect-string" << endl
0405            << "or     " << argv[0] << " dsn username password" << endl;
0406     return 0;
0407   }
0408
0409
0410   try {
0411     Connection* con;
0412     if(argc==2) {
0413       cout << "Connecting to " << argv[1] << "..." << flush;
0414       con=TSQLDriverManager::GetConnection(argv[1]);
0415     } else {
0416       cout << "Connecting to dsn=" << argv[1]
0417        << ", uid=" << argv[2]
0418        << ", pwd=" << argv[3] << "..." << flush;
0419       con=TSQLDriverManager::GetConnection(argv[1],argv[2],argv[3]);
0420     }
0421     cout << " done." << endl;
0422
0423     dropTables(con);
0424     createTables(con);
0425
0426     populateTables(con);
0427
0428     checkTables(con);
0429
0430     dropTables(con);
0431
0432     delete con;
0433
0434     DriverManager::shutdown();
0435
0436     if(assertionsFailed) {
0437       cerr << assertionsFailed << " assertions failed" << endl;
0438       return 1;
0439     } else {
0440       cout << "Apparently, this worked!" << endl;
0441     }
0442
0443
0444   } catch(SQLException& e) {
0445     cerr << endl << e.getMessage() << endl;
0446     return 2;
0447   }
0448
0449   return 0;
0450 }