src/cache/CacheTableLoader.cxx

   1 /***************************************************************************
   2  *   Copyright (C) 2007 by www.databasecache.com                           *
   3  *   Contact: praba_tuty@databasecache.com                                 *
   4  *                                                                         *
   5  *   This program is free software; you can redistribute it and/or modify  *
   6  *   it under the terms of the GNU General Public License as published by  *
   7  *   the Free Software Foundation; either version 2 of the License, or     *
   8  *   (at your option) any later version.                                   *
   9  *                                                                         *
  10  *   This program is distributed in the hope that it will be useful,       *
  11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  13  *   GNU General Public License for more details.                          *
  14  *                                                                         *
  15   ***************************************************************************/
  16 #include<os.h>
  17 #include<CacheTableLoader.h>
  18 #include<TableConfig.h>
  19 #include<Util.h>
  20 #include<SqlConnection.h>
  21 #include<SqlLogConnection.h>
  22 #include<SqlStatement.h>
  23 #include<SqlFactory.h>
  24
  25 DbRetVal CacheTableLoader::load(bool tabDefinition)
  26 {
  27     AbsSqlConnection *conn = SqlFactory::createConnection(CSqlLog);
  28     DbRetVal rv = conn->connect(userName, password);
  29     if (rv != OK) { delete conn; return ErrSysInit; }
  30     AbsSqlStatement *stmt = SqlFactory::createStatement(CSqlLog);
  31     stmt->setConnection(conn);
  32     SqlLogConnection *logConn = (SqlLogConnection *) conn;
  33     logConn->setNoMsgLog(true);
  34     logConn->setNoOfflineLog(true);
  35     SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
  36     DatabaseManager *dbMgr = con->getConnObject().getDatabaseManager();
  37     dbMgr->setCanTakeCheckPoint(false);
  38     if (tabDefinition == false) {
  39         Table *tbl = dbMgr->openTable(tableName);
  40         if (tbl == NULL) {
  41             dbMgr->setCanTakeCheckPoint(true);
  42             conn->disconnect();
  43             delete stmt;
  44             delete conn;
  45             return ErrNotExists;
  46         }
  47         if (tbl->numTuples()) {
  48             printError(ErrNotEmpty, "The table '\%s\' is not empty", tableName);
  49             dbMgr->closeTable(tbl);
  50             dbMgr->setCanTakeCheckPoint(true);
  51             conn->disconnect();
  52             delete stmt;
  53             delete conn;
  54             return ErrNotEmpty;
  55         }
  56         dbMgr->closeTable(tbl);
  57     }
  58     conn->beginTrans();
  59     rv = load(conn, stmt, tabDefinition);
  60     conn->commit();
  61     stmt->free();
  62     dbMgr->setCanTakeCheckPoint(true);
  63     conn->disconnect();
  64     delete stmt;
  65     delete conn;
  66     return rv;
  67 }
  68
  69 DbRetVal CacheTableLoader::load(AbsSqlConnection *conn, AbsSqlStatement *stmt, bool tabDefinition)
  70 {
  71     char dsn[IDENTIFIER_LENGTH];
  72     TDBInfo tdbName = mysql;
  73     DbRetVal rv = OK;
  74
  75     bool isDSNExist = resolveForDSN(dsn, tdbName, rv);
  76     if (!isDSNExist) return rv;
  77
  78     SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
  79     DatabaseManager *dbMgr = con->getConnObject().getDatabaseManager();
  80
  81     SQLCHAR outstr[1024];
  82     SQLSMALLINT outstrlen;
  83     int retValue =0;
  84     SQLHENV henv;
  85     SQLHDBC hdbc;
  86     SQLHSTMT hstmt;
  87     retValue = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
  88     if (retValue) {
  89         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
  90         return ErrSysInit;
  91     }
  92     SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
  93     retValue = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
  94     if (retValue) {
  95         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
  96         return ErrSysInit;
  97     }
  98     retValue = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS,
  99                          outstr, sizeof(outstr), &outstrlen,
 100                          SQL_DRIVER_NOPROMPT);
 101     if (SQL_SUCCEEDED(retValue)) {
 102         printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", dsn);
 103     } else {
 104         printError(ErrSysInit, "Failed to connect to target database\n");
 105         return ErrSysInit;
 106     }
 107
 108     retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
 109     if (retValue) {
 110         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 111         return ErrSysInit;
 112     }
 113
 114     char stmtBuf[1024];
 115     generateCacheTableStatement(stmtBuf);
 116     retValue = SQLPrepare (hstmt, (unsigned char *) stmtBuf, SQL_NTS);
 117     if (retValue) {
 118         printError(ErrSysInit, "Unable to Prepare ODBC statement \n");
 119         return ErrSysInit;
 120     }
 121     int nRecordsToFetch = Conf::config.getNoOfRowsToFetchFromTDB();
 122     int nFetchedRecords = 0;
 123     SQLUSMALLINT *rowStatus = (SQLUSMALLINT *)
 124                                 malloc(nRecordsToFetch * sizeof(SQLUSMALLINT));
 125     memset(rowStatus, 0, nRecordsToFetch * sizeof(SQLUSMALLINT));
 126     SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0);
 127     SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_ARRAY_SIZE, (void *) nRecordsToFetch, 0);
 128     SQLSetStmtAttr(hstmt, SQL_ATTR_ROW_STATUS_PTR, rowStatus, 0 );
 129     SQLSetStmtAttr(hstmt, SQL_ATTR_ROWS_FETCHED_PTR, &nFetchedRecords, 0 );
 130     if (tabDefinition) {
 131         short totalFields=0;
 132         retValue = SQLNumResultCols (hstmt, &totalFields);
 133         if (retValue) {
 134             printError(ErrSysInit, "Unable to retrieve ODBC total columns\n");
 135             return ErrSysInit;
 136         }
 137         logFinest(Conf::logger, "Cache Table noOfFields %hd", totalFields);
 138         UWORD                   icol=1;
 139         UCHAR                   colName[IDENTIFIER_LENGTH];
 140         SWORD                   colNameMax=0;
 141         SWORD                   nameLength=0;
 142         SWORD                   colType=0;
 143         SQLULEN                 colLength = 0;
 144         SWORD                   scale=0;
 145         SWORD                   nullable=0;
 146         colNameMax = IDENTIFIER_LENGTH;
 147         char columnname[IDENTIFIER_LENGTH];
 148         short type; short unique;
 149         SQLHSTMT hstmtmeta;
 150         retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtmeta);
 151         if (retValue)
 152         {
 153             printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 154             return ErrSysInit;
 155         }
 156
 157         char crtIdxStmt[1024];
 158         char *ptr=crtIdxStmt;
 159         HashIndexInitInfo *inf = new HashIndexInitInfo();
 160         bool isPriIndex = prepareCreateIndexStatement(hstmtmeta, crtIdxStmt, tdbName, inf);
 161
 162         bool iskeyfieldExist=false;
 163         bool isPKFieldSpecified = false;
 164         char *name = NULL;
 165         if((strcmp(fieldName,"")!=0) && (strcmp(fieldName,"NULL")!=0) )
 166         {
 167             isPKFieldSpecified = true;
 168         }
 169         if ( isPriIndex && ( strcmp(fieldlistVal,"")!=0 ) &&
 170                                        ( strcmp(fieldlistVal,"NULL") != 0 )) {
 171             inf->list.resetIter();
 172             while ( (name=inf->list.nextFieldName()) != NULL) {
 173                 iskeyfieldExist = TableConf::config.isFieldExist(name);
 174                 if(!iskeyfieldExist) { break; }
 175             }
 176         } else if (isPriIndex) { iskeyfieldExist = true; }
 177         if ( isPKFieldSpecified &&  !(TableConf::config.isFieldExist(fieldName)) )
 178         {
 179             if ( Conf::config.useTwoWayCache() &&
 180                              (strcmp(fieldlistVal,"")!=0) &&
 181                                          (strcmp(fieldlistVal,"NULL")!=0))
 182             {
 183                 printError(ErrSysInit, "Bidirectional caching should have primary key in %s \n", tableName);
 184                 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 185                 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 186                 SQLDisconnect (hdbc);
 187                 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 188                 SQLFreeHandle (SQL_HANDLE_ENV, henv);
 189                 delete inf;
 190                 return ErrSysInit;
 191             }
 192         }
 193         if (!iskeyfieldExist && !isPKFieldSpecified )
 194         {
 195             if(Conf::config.useTwoWayCache())
 196             {
 197                 printError(ErrSysInit, "Bidirectional caching fail for no primary key in %s \n", tableName);
 198                 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 199                 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 200                 SQLDisconnect (hdbc);
 201                 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 202                 SQLFreeHandle (SQL_HANDLE_ENV, henv);
 203                 delete inf;
 204                 return ErrSysInit;
 205             }
 206         }
 207
 208         bool isKeyFld=false;
 209         char crtTblStmt[1024];
 210         rv = prepareCreateTableStatement(crtTblStmt, hstmt, inf, totalFields,
 211                                                             tdbName, isKeyFld);
 212         if (rv != OK) {
 213             SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 214             SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 215             SQLDisconnect (hdbc);
 216             SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 217             SQLFreeHandle (SQL_HANDLE_ENV, henv);
 218             delete inf;
 219             return ErrSysInit;
 220         }
 221         //printf("table stmt '%s'\n", crtTblStmt);
 222         if(((strcmp(fieldName,"")!=0) && (strcmp(fieldName,"NULL")!=0))
 223                                                                && !isKeyFld) {
 224             printError(ErrSysInit, "Unable to cache Table for %s with key field %s\n", tableName,fieldName);
 225             SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 226             SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 227             SQLDisconnect (hdbc);
 228             SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 229             SQLFreeHandle (SQL_HANDLE_ENV, henv);
 230             delete inf;
 231             return ErrSysInit;
 232         }
 233         rv = stmt->prepare(crtTblStmt);
 234         if (rv != OK) {
 235             printError(ErrSysInit, "Unable to prepare create table stmt\n");
 236             SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 237             SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 238             SQLDisconnect (hdbc);
 239             SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 240             SQLFreeHandle (SQL_HANDLE_ENV, henv);
 241             delete inf;
 242             return ErrSysInit;
 243         }
 244         int rows = 0;
 245         rv = stmt->execute(rows);
 246         if (rv != OK) {
 247             printError(ErrSysInit, "Unable to execute create table stmt\n");
 248             SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 249             SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 250             SQLDisconnect (hdbc);
 251             SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 252             SQLFreeHandle (SQL_HANDLE_ENV, henv);
 253             delete inf;
 254             return ErrSysInit;
 255         }
 256         logFinest(Conf::logger, "Cache Table: Table Created :%s", crtTblStmt);
 257
 258         //Table is created.
 259         //Create primary key index if present
 260         if (isPriIndex && ( iskeyfieldExist ||
 261            (strcmp(fieldlistVal,"")==0  || strcmp(fieldlistVal,"NULL")== 0))) {
 262             rv = stmt->prepare(crtIdxStmt);
 263             if (rv != OK) {
 264                 printError(ErrSysInit, "Unable to prepare create table stmt\n");
 265                 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 266                 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 267                 SQLDisconnect (hdbc);
 268                 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 269                 SQLFreeHandle (SQL_HANDLE_ENV, henv);
 270                 delete inf;
 271                 return ErrSysInit;
 272             }
 273             int rows = 0;
 274             rv = stmt->execute(rows);
 275             if (rv != OK) {
 276                 printError(ErrSysInit, "Unable to execute create table stmt\n");
 277                 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 278                 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 279                 SQLDisconnect (hdbc);
 280                 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 281                 SQLFreeHandle (SQL_HANDLE_ENV, henv);
 282                 delete inf;
 283                 return ErrSysInit;
 284             }
 285             //printf("Primary index created from create Index stmt\n");
 286         }
 287         retValue = SQLCloseCursor(hstmtmeta);
 288         rv = createIndex(hstmtmeta, tableName, inf, stmt,isPKFieldSpecified);
 289         if(rv!=OK) {
 290             dbMgr->dropTable(tableName);
 291             SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 292             SQLDisconnect (hdbc);
 293             SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 294             SQLFreeHandle (SQL_HANDLE_ENV, henv);
 295             delete inf;
 296             return rv;
 297         }
 298         logFinest(Conf::logger, "Cache Table: Index :%s", crtIdxStmt);
 299         delete inf;
 300     } // tableDefinition scope finishes here
 301
 302     else { /***Checking for Table Schema between CSQL and TDB(cachetable -s option)***/
 303         rv=checkingSchema(hdbc,hstmt,conn,stmt,tdbName);
 304         if(rv != OK){
 305             printError(ErrSysInit,"Unable to cache the '%s' table due to schema mismatched.",tableName);
 306             SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 307             SQLDisconnect (hdbc);
 308             SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 309             SQLFreeHandle (SQL_HANDLE_ENV, henv);
 310             return ErrSysInit;
 311         }
 312     } //***Ends Here
 313
 314     // Now load the table with records
 315     stmt->free();
 316
 317     char insStmt[1024];
 318     List fNameList;
 319     fNameList.init();
 320     SqlStatement *sqlStmt = (SqlStatement *)stmt->getInnerStatement();
 321     sqlStmt->setConnection(con);
 322     prepareInsertStatement(sqlStmt, &fNameList, insStmt);
 323     int totalFields = fNameList.size();
 324     rv = stmt->prepare(insStmt);
 325     if (rv != OK) {
 326         printError(ErrSysInit, "Unable to prepare create table stmt\n");
 327         SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 328         SQLDisconnect (hdbc);
 329         SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 330         SQLFreeHandle (SQL_HANDLE_ENV, henv);
 331         return ErrSysInit;
 332     }
 333     sqlStmt->setLoading(true);
 334     ListIterator fNameIter = fNameList.getIterator();
 335     FieldInfo *info = new FieldInfo();
 336     int fcount =1; void *valBuf=NULL;
 337     Identifier *elem = NULL;
 338     void *tembuf=NULL;//For postgre BigInt type
 339     BindBuffer *bBuf;
 340     List valBufList;
 341     int i=0;
 342     while (fNameIter.hasElement()) {
 343         elem = (Identifier*) fNameIter.nextElement();
 344         sqlStmt->getFieldInfo(tableName, (const char*)elem->name, info);
 345         int size = 0;
 346         if (info->type == typeString || info->type == typeVarchar) {
 347             size = nRecordsToFetch * AllDataType::size(info->type,info->length);
 348         } else {
 349             size = nRecordsToFetch * AllDataType::size(info->type);
 350         }
 351         valBuf = malloc(size);
 352         os::memset(valBuf,0,size);
 353         int bindLen = 0;
 354         if (info->type != typeDate && info->type != typeTime && info->type != typeTimeStamp) {
 355             if (info->type == typeLongLong && tdbName == postgres)
 356                 bindLen = 40;
 357             else
 358             bindLen =  AllDataType::size(info->type,
 359                                   AllDataType::size(info->type, info->length));
 360         } else {
 361             switch(info->type) {
 362             case typeDate: bindLen = sizeof(DATE_STRUCT); break;
 363             case typeTime: bindLen = sizeof(TIME_STRUCT); break;
 364             case typeTimeStamp: bindLen = sizeof(TIMESTAMP_STRUCT); break;
 365             }
 366         }
 367
 368         bBuf = (BindBuffer *) SqlStatement::fillBindBuffer(tdbName, info->type, valBuf, bindLen, nRecordsToFetch);
 369         valBufList.append(bBuf);
 370         retValue = SQLBindCol (hstmt, fcount, AllDataType::convertToSQL_C_Type(info->type,tdbName), valBuf, bindLen, bBuf->nullData);
 371         fcount++;
 372         if (retValue) {
 373             if(tabDefinition) dbMgr->dropTable(tableName);
 374             printError(ErrSysInit, "Unable to bind columns in ODBC\n");
 375             SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 376             SQLDisconnect (hdbc);
 377             SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 378             SQLFreeHandle (SQL_HANDLE_ENV, henv);
 379             return ErrSysInit;
 380         }
 381     }
 382     delete info;
 383     fNameIter.reset();
 384     while (fNameIter.hasElement())
 385         delete ((FieldName *) fNameIter.nextElement());
 386     fNameList.reset();
 387
 388     retValue = SQLExecute (hstmt);
 389     if (retValue) {
 390         printError(ErrSysInit, "Unable to execute ODBC statement\n");
 391         SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 392         SQLDisconnect (hdbc);
 393         SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 394         SQLFreeHandle (SQL_HANDLE_ENV, henv);
 395         return ErrSysInit;
 396     }
 397     int fldpos=0;
 398     do {
 399         //TODO: if SQLFetch return other than record not found error
 400         //it should drop the table
 401         retValue = SQLFetchScroll(hstmt, SQL_FETCH_NEXT, 0);
 402         if (retValue) break;
 403         for (int row = 0; row < nFetchedRecords; row++) {
 404             fldpos = 0;
 405             void *val = NULL;
 406             ListIterator bindIter = valBufList.getIterator();
 407             while (bindIter.hasElement()) {
 408                 bBuf = (BindBuffer*) bindIter.nextElement();
 409                 if (bBuf->nullData[row] == SQL_NULL_DATA) {
 410                     stmt->setNull(fldpos+1);
 411                 } else {
 412                     val = (void *) ((char *)bBuf->csql + row * bBuf->length);
 413                     switch (bBuf->type) {
 414                         case typeString:
 415                         {
 416                             val = (void *) ((char *)bBuf->csql + row * bBuf->length);
 417                             if( tdbName == postgres)
 418                                 Util::trimRight((char*)val);
 419                             break;
 420                         }
 421                         case typeDate:
 422                         {
 423                             val = (void *) ((char *)bBuf->csql + row * sizeof(Date));
 424                             Date *dtCSQL = (Date*) val;
 425                             void *tVal = (void *) ((char *)bBuf->targetdb + row * sizeof(DATE_STRUCT));
 426                             DATE_STRUCT *dtTarget = (DATE_STRUCT*) tVal;
 427                             dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
 428                             break;
 429                         }
 430                         case typeTime:
 431                         {
 432                             val = (void *) ((char *)bBuf->csql + row * sizeof(Time));
 433                             Time *dtCSQL = (Time*) val;
 434                             void *tVal = (void *) ((char *)bBuf->targetdb + row * sizeof(TIME_STRUCT));
 435                             TIME_STRUCT *dtTarget = (TIME_STRUCT*) tVal;
 436                             dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 437                             break;
 438                         }
 439                         case typeTimeStamp:
 440                         {
 441                             val = (void *) ((char *)bBuf->csql + row * sizeof(TimeStamp));
 442                             TimeStamp *dtCSQL = (TimeStamp*) val;
 443                             void *tVal = (void *) ((char *)bBuf->targetdb + row * sizeof(TIMESTAMP_STRUCT));
 444                             TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) tVal;
 445                             dtCSQL->setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 446                             dtCSQL->setTime(dtTarget->hour,dtTarget->minute,dtTarget->second, dtTarget->fraction);
 447                             break;
 448                         }
 449                         case typeLongLong:
 450                         {
 451                             val = (void *) ((char *)bBuf->csql + row * sizeof(long long));
 452                             void *tVal = (void *) ((char *)bBuf->targetdb + row * bBuf->length);
 453                             if (tdbName == postgres) {
 454                                 sscanf((const char*)tVal,"%lld",(long long*) val);
 455                             }
 456                             break;
 457                         }
 458                     }
 459                     SqlStatement::setParamValues(stmt, fldpos+1, bBuf->type, bBuf->length, val);
 460                 }
 461                 fldpos++;
 462             }
 463             int rows = 0;
 464             rv = stmt->execute(rows);
 465             if (rv != OK) {
 466                 printError(ErrSysInit, "Unable to cache record in CSQL.\n");
 467                 if(tabDefinition) dbMgr->dropTable(tableName);
 468                 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 469                 SQLDisconnect (hdbc);
 470                 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 471                 SQLFreeHandle (SQL_HANDLE_ENV, henv);
 472                 return ErrSysInit;
 473             }
 474             conn->commit();
 475             conn->beginTrans();
 476         }
 477     } while (SQL_SUCCEEDED(retValue) && nFetchedRecords == nRecordsToFetch);
 478     conn->commit();
 479     conn->beginTrans();
 480
 481     //PRABA::one operation per transaction gives the best
 482     //performance than 100 /Txn in case of durability
 483     //TODO::leak:: valBufList and its targetdb buffer
 484     ListIterator it = valBufList.getIterator();
 485     while(it.hasElement()) {
 486         BindBuffer *bb = (BindBuffer *) it.nextElement();
 487         if (bb->csql) { free(bb->csql); bb->csql = NULL; }
 488         if (bb->targetdb) { free(bb->targetdb); bb->targetdb = NULL; }
 489         delete bb; bb = NULL;
 490     }
 491     valBufList.reset();
 492     SQLCloseCursor (hstmt);
 493     SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 494     ::free(rowStatus);
 495     SQLDisconnect (hdbc);
 496     SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
 497     SQLFreeHandle (SQL_HANDLE_ENV, henv);
 498     logFine(Conf::logger, "Cached Table: %s", tableName);
 499     return OK;
 500 }
 501
 502 DbRetVal CacheTableLoader::reload()
 503 {
 504     FILE *fp=NULL;
 505     DbRetVal rv = unload(false);
 506     if (rv != OK) return rv;
 507     //get table cache senarios
 508     fp = fopen(Conf::config.getTableConfigFile(),"r");
 509     if( fp == NULL ) {
 510         printError(ErrSysInit, "csqltable.conf file does not exist");
 511         return OK;
 512     }
 513     int mode;
 514     rv = OK;
 515     char tablename[IDENTIFIER_LENGTH];
 516     char fieldname[IDENTIFIER_LENGTH];
 517     char field[IDENTIFIER_LENGTH];
 518     char condition[IDENTIFIER_LENGTH];
 519     char dsnname[IDENTIFIER_LENGTH];
 520     while(!feof(fp))
 521     {
 522         fscanf(fp, "%d %s %s %s %s %s\n", &mode, tablename,fieldname,condition,field,dsnname);
 523         if(strcmp(tablename,tableName)==0) break;
 524     }
 525     fclose(fp);
 526     setCondition(TableConf::config.getRealConditionFromFile(condition));
 527     setFieldName(fieldname);
 528     setFieldListVal(field);
 529     setDsnName(dsnname);
 530     rv = load(false);
 531     return rv;
 532 }
 533
 534 DbRetVal CacheTableLoader::unload(bool tabDefinition)
 535 {
 536     AbsSqlConnection *conn = SqlFactory::createConnection(CSqlLog);
 537     DbRetVal rv = conn->connect(userName, password);
 538     if (rv != OK) return ErrSysInit;
 539     AbsSqlStatement *stmt = SqlFactory::createStatement(CSqlLog);
 540     stmt->setConnection(conn);
 541     SqlLogConnection *logConn = (SqlLogConnection *) conn;
 542     logConn->setNoMsgLog(true);
 543     char statement[1024];
 544     if (TableConf::config.isTableCached(tableName) != OK) {
 545         printError(ErrNotCached, "The table \'%s\' is not cached", tableName);
 546         conn->disconnect();
 547         delete stmt;
 548         delete conn;
 549         return ErrNotCached;
 550     }
 551     SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
 552     DatabaseManager *dbMgr = (DatabaseManager*) con->getConnObject().getDatabaseManager();
 553     if (dbMgr == NULL) {
 554         conn->disconnect();
 555         delete stmt; delete conn;
 556         printError(ErrSysInit, "Authentication failed\n");
 557         return ErrSysInit;
 558     }
 559     if (!tabDefinition)
 560     {
 561         sprintf(statement, "DELETE FROM %s;", tableName);
 562         SqlStatement *sqlStmt = (SqlStatement*)stmt;
 563         sqlStmt->setLoading(true);
 564         rv = stmt->prepare(statement);
 565         if (rv != OK) {
 566             conn->disconnect();
 567             delete stmt; delete conn;
 568             return ErrBadCall;
 569         }
 570         conn->beginTrans();
 571         int rows = 0;
 572         rv = stmt->execute(rows);
 573         if (rv != OK) {
 574             conn->disconnect();
 575             delete stmt; delete conn;
 576             return ErrBadCall;
 577         }
 578         conn->commit();
 579     }
 580     else
 581     {
 582         rv = TableConf::config.removeFromCacheTableFile();
 583         if (rv != OK) {
 584             conn->disconnect(); delete stmt; delete conn;
 585             return ErrBadCall;
 586         }
 587         sprintf(statement, "DROP TABLE %s;", tableName);
 588         SqlStatement *sqlStmt = (SqlStatement*)stmt;
 589         sqlStmt->setLoading(true);
 590         rv = stmt->prepare(statement);
 591         if (rv != OK) {
 592             //TableConf::config.addToCacheTableFile(false);
 593             conn->disconnect();
 594             delete stmt; delete conn;
 595             return ErrBadCall;
 596         }
 597         int rows = 0;
 598         rv = stmt->execute(rows);
 599         if (rv != OK) {
 600             //TableConf::config.addToCacheTableFile(false);
 601             conn->disconnect(); delete stmt; delete conn;
 602             return ErrBadCall;
 603         }
 604     }
 605     conn->disconnect();
 606     delete stmt; delete conn;
 607     logFine(Conf::logger, "Unloaded Cached Table: %s", tableName);
 608     return rv;
 609 }
 610
 611 DbRetVal CacheTableLoader::refresh()
 612 {
 613     return OK;
 614 }
 615
 616 DbRetVal CacheTableLoader::recoverAllCachedTables()
 617 {
 618     FILE *fp;
 619     Connection conn;
 620     DbRetVal rv = conn.open(userName, password);
 621     if(rv !=OK) return ErrSysInit;
 622
 623     //Note: if connection is not open, configuration veriables may be incorrect
 624
 625     fp = fopen(Conf::config.getTableConfigFile(),"r");
 626     if( fp == NULL ) {
 627         printError(ErrSysInit, "csqltable.conf file does not exist");
 628         conn.close();
 629         return OK;
 630     }
 631     conn.close();
 632     //TODO::take exclusive lock on database
 633     char tablename[IDENTIFIER_LENGTH];
 634     char fieldname[IDENTIFIER_LENGTH];
 635     char condition[IDENTIFIER_LENGTH];
 636     char field[IDENTIFIER_LENGTH];
 637     char dsnname[IDENTIFIER_LENGTH];
 638
 639     int mode;
 640     int scanItems=0;
 641     rv = OK;
 642     while(!feof(fp))
 643     {
 644         scanItems = fscanf(fp, "%d %s %s %s %s %s\n", &mode, tablename,fieldname,condition,field,dsnname);
 645         if (scanItems != 6) {
 646             tablename[0]='\0';
 647             printf("There is no table to be cached.\n");
 648             return OK;
 649         }
 650         if (!TableConf::config.isTableCached(mode)) continue;
 651         printDebug(DM_Gateway, "Recovering Table from target db: %s\n", tablename);
 652         setCondition(TableConf::config.getRealConditionFromFile(condition));
 653         if( (strcmp(Conf::config.getDSN(),dsnname)!=0) ){
 654                      setDsnName(dsnname);
 655              setTable(tablename);
 656              setFieldName(fieldname);
 657              setFieldListVal(field);
 658              printf("Recovering table %s %s %s\n", tablename,condition,field);
 659              rv = load();
 660              if (rv != OK) { fclose(fp); return rv; }
 661         } else {
 662              setDsnName(Conf::config.getDSN());
 663              setTable(tablename);
 664              setFieldName(fieldname);
 665              setFieldListVal(field);
 666              printf("Recovering table %s %s %s\n", tablename,condition,field);
 667              rv = load();
 668              if (rv != OK) { fclose(fp); return rv; }
 669         }
 670         logFine(Conf::logger, "Recovering Table from target db:%s", tablename);
 671     }
 672     fclose(fp);
 673     return OK;
 674 }
 675
 676 DbRetVal CacheTableLoader::createIndex(SQLHSTMT hstmtmeta, char *tableName, HashIndexInitInfo *inf,AbsSqlStatement *stmt,bool isPKFieldSpecified)
 677 {
 678         bool isKeyFld= false;
 679         int retValue = 0;
 680         char columnname[IDENTIFIER_LENGTH];
 681         char indexname[IDENTIFIER_LENGTH];
 682         short type;
 683         short unique;
 684         char *name = NULL;
 685         DbRetVal rv = OK;
 686         retValue = SQLStatistics(hstmtmeta, NULL, 0, NULL, SQL_NTS,
 687                     (SQLCHAR*) tableName, SQL_NTS, SQL_INDEX_ALL, SQL_QUICK);
 688         retValue = SQLBindCol(hstmtmeta, 4, SQL_C_SHORT,
 689                           &unique, 2, NULL);
 690         retValue = SQLBindCol(hstmtmeta, 6, SQL_C_CHAR,
 691                           indexname, 129, NULL);
 692         retValue = SQLBindCol(hstmtmeta, 7, SQL_C_SHORT,
 693                           &type, 2, NULL);
 694         retValue = SQLBindCol(hstmtmeta, 9, SQL_C_CHAR,
 695                           columnname, 129,NULL);
 696         List indexList;
 697         bool isSecondTime = false;
 698         CacheIndexInfo *info=NULL;
 699         while ((retValue = SQLFetch(hstmtmeta)) == SQL_SUCCESS) {
 700             printDebug(DM_Gateway, "Column: %-18s Index Name: %-18s unique:%hd type:%hd\n", columnname, indexname, unique, type);
 701
 702             if (type == 3) {
 703                 bool isFldAdd = false;
 704                 ListIterator iter = indexList.getIterator();
 705                 iter.reset();
 706                 while (iter.hasElement()) {
 707                     CacheIndexInfo *indInfo = (CacheIndexInfo *)iter.nextElement();
 708                     if(0 == strcmp( indInfo->indexName, indexname))
 709                     {
 710                          indInfo->fieldNameList.append(columnname);
 711                          isFldAdd = true;
 712                     }
 713                 }
 714                 if(!isFldAdd){
 715                     info = new CacheIndexInfo();
 716                     info->fieldNameList.append(columnname);
 717                     strcpy(info->indexName, indexname);
 718                     indexList.append(info);
 719                     isSecondTime = true;
 720                 }
 721             }
 722         }
 723         ListIterator iter = indexList.getIterator();
 724         iter.reset();
 725         int noOfPkfield = inf->list.size();
 726         char *fName=NULL;
 727         char *cptr = NULL;
 728         while (iter.hasElement()) {
 729             cptr = columnname;
 730             bool isFieldExistInCondition = false;
 731             bool isPrimary=false;
 732             CacheIndexInfo *indInfo = (CacheIndexInfo *)iter.nextElement();
 733             int noOfFld= indInfo->fieldNameList.size();
 734             indInfo->fieldNameList.resetIter();
 735             while ((fName = indInfo->fieldNameList.nextFieldName())!=NULL) {
 736                 if(( 1 == noOfFld) && (0 == strcmp(fName,fieldName))) {
 737                     isKeyFld=true;
 738                 }
 739                 inf->list.resetIter();
 740                 while ((name=inf->list.nextFieldName())!=NULL)
 741                 {
 742                     if(0==strcmp(fName,name)) {  isPrimary = true;  break;  }
 743                     isPrimary = false;
 744                 }
 745                         if (!TableConf::config.isFieldExist(fName) &&
 746                                      ( (strcmp(fieldlistVal,"")!=0) &&
 747                                           (strcmp(fieldlistVal,"NULL")!=0) )) {
 748                      isFieldExistInCondition =true;
 749                      continue;
 750                 }
 751                 sprintf(cptr, "%s ,",fName);
 752                 cptr += strlen(cptr);
 753             }
 754             if(isFieldExistInCondition) continue;
 755             cptr -=1;
 756             *cptr = '\0';
 757             if (isPrimary) { continue; }
 758             char crtIdxStmt[1024];
 759             char indname[128];
 760             sprintf(indname, "%s_%s", tableName, indInfo->indexName);
 761             sprintf(crtIdxStmt, "CREATE INDEX %s on %s(%s) HASH SIZE 10007;", indname, tableName, columnname);
 762             //printf("create index stmt \n'%s'\n", crtIdxStmt);
 763             rv = stmt->prepare(crtIdxStmt);
 764             if (rv != OK) {
 765                 printError(ErrSysInit, "Unable to prepare create table stmt\n");
 766                 return ErrSysInit;
 767             }
 768             int rows = 0;
 769             rv = stmt->execute(rows);
 770             if (rv != OK) {
 771                 printError(ErrSysInit, "Unable to execute create table stmt\n");
 772                 return ErrSysInit;
 773             }
 774             //delete indInfo;
 775         }// while meta data fetch for index creation
 776
 777         iter.reset();
 778         while (iter.hasElement()) delete (CacheIndexInfo *) iter.nextElement();
 779         indexList.reset();
 780
 781         SQLCloseCursor (hstmtmeta);
 782         SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 783         if( !isKeyFld && isPKFieldSpecified) {
 784             if(shouldForce) {
 785                 char frcIndStmt[1024];
 786                 char indname[128];
 787                 sprintf(indname, "%s_%s", tableName, "keyInd");
 788                 sprintf(frcIndStmt, "CREATE INDEX %s on %s(%s) HASH;", indname, tableName, fieldName);
 789                 rv = stmt->prepare(frcIndStmt);
 790                 if (rv != OK) {
 791                     printError(ErrSysInit, "Unable to prepare create table stmt\n");
 792                     return ErrSysInit;
 793                 }
 794                 int rows = 0;
 795                 rv = stmt->execute(rows);
 796                 if (rv != OK) {
 797                     printError(ErrSysInit, "Unable to execute create table stmt\n");
 798                     return ErrSysInit;
 799                 }
 800             } else {
 801                 printError(ErrSysInit, "Unable to cache Table for %s with key field %s\n", tableName,fieldName);
 802                 return ErrSysInit;
 803             }
 804         }
 805      return OK;
 806 }
 807
 808 // Schema matching between CSQL and TDB table.(cachetable -t <tablename> -s)
 809 DbRetVal CacheTableLoader::checkingSchema(SQLHDBC hdbc,SQLHSTMT hstmt, AbsSqlConnection *conn, AbsSqlStatement *stmt,TDBInfo tdbName)
 810 {
 811     DbRetVal rv=OK;
 812     int noOfPrimaryKey=0;
 813     int retValue=0;
 814     int csqlFields=0;
 815
 816     SQLSMALLINT tdbFields=0;
 817     SQLHSTMT hstmtmeta;
 818     char columnname[IDENTIFIER_LENGTH];
 819
 820     UWORD                   icol=1;
 821     UCHAR                   colName[IDENTIFIER_LENGTH];
 822     SWORD                   colNameMax=0;
 823     SWORD                   nameLength=0;
 824     SWORD                   colType=0;
 825     SQLULEN                 colLength = 0;
 826     SWORD                   scale=0;
 827     SWORD                   nullable=0;
 828     colNameMax = IDENTIFIER_LENGTH;
 829
 830     SqlConnection *con = (SqlConnection *) conn->getInnerConnection();
 831     DatabaseManager *dbMgr = con->getConnObject().getDatabaseManager();
 832
 833     SqlStatement *sqlStmt = (SqlStatement *)stmt->getInnerStatement();
 834     sqlStmt->setConnection(con);
 835
 836     List fNameList ;
 837     fNameList = sqlStmt->getFieldNameList(tableName, rv);
 838     ListIterator fNameIter = fNameList.getIterator();
 839     FieldInfo *info = new FieldInfo();
 840     Identifier *elem = NULL;
 841
 842     retValue=SQLNumResultCols(hstmt, &tdbFields);
 843     if(retValue) {
 844        printError(ErrSysInit, "Unable to retrieve ODBC total columns.\n");
 845        SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 846        return ErrSysInit;
 847     }
 848     /* CSQL Table fields */
 849     fNameList = sqlStmt->getFieldNameList(tableName, rv);
 850     csqlFields = fNameList.size();
 851     /* noOfFields in both the database are same or not. */
 852     if(tdbFields!=csqlFields){
 853         printError(ErrSysInit,"Number of fields between CSQL and TDB are not equal.");
 854         SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 855         return ErrSysInit;
 856     }
 857     retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmtmeta);
 858     if(retValue){
 859         printError(ErrSysInit, "Unable to allocate ODBC handle. \n");
 860         SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 861         return ErrSysInit;
 862     }
 863     retValue=SQLPrimaryKeys(hstmtmeta, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*) tableName, SQL_NTS);
 864     retValue = SQLBindCol(hstmtmeta, 4, SQL_C_CHAR,columnname, 129,NULL);
 865
 866     while(icol<=tdbFields){
 867         retValue = SQLDescribeCol(hstmt, icol, colName, colNameMax,
 868                              &nameLength, &colType, &colLength,
 869                              &scale, &nullable);//TDB Field Name
 870         if(retValue){
 871             printError(ErrSysInit, "Unable to retrieve ODBC column info.\n");
 872             SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 873             return ErrSysInit;
 874         }
 875         Util::str_tolower((char*)colName);
 876         elem = (Identifier*) fNameIter.nextElement();
 877         sqlStmt->getFieldInfo(tableName, (const char*)elem->name, info);
 878         char fldName[20];
 879         int isNull;
 880         int isPrimary;
 881         rv = stmt->getParamFldInfo(icol,info);
 882         char *name=(info->fldName);//Getting field name for CSQL table.
 883         Util::str_tolower((char*)name);
 884         if(strcmp(name,(char *)colName) != 0){ //Field name matching between CSQL and TDB.
 885             printError(ErrSysInit,"CSQL's '%s' field did not match with TDB's '%s' field.\n",name,(char*)colName);
 886             SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 887             return ErrSysInit;
 888         }
 889
 890         /* DataType matching between CSQL and TDB */
 891         char ptr[IDENTIFIER_LENGTH]; ptr[0]='\0';
 892         char ptr1[IDENTIFIER_LENGTH]; ptr1[0]='\0';
 893
 894         sprintf(ptr,"%s",AllDataType::getSQLString (AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
 895         sprintf(ptr1,"%s",AllDataType::getSQLString(info->type));//CSQL Type
 896         if(strcmp(ptr,ptr1)!=0){
 897            printError(ErrSysInit,"DataType did not match for '%s' field in CSQL.\n",name);
 898            SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
 899            return ErrSysInit;
 900         }
 901
 902        /* Primary Key checking */
 903        bool tdbPKey=false;
 904        if(SQLFetch( hstmtmeta ) == SQL_SUCCESS) tdbPKey=true;
 905        if(tdbPKey && (!info->isPrimary))
 906        printf("Warning: In CSQL, The %s's '%s' field should have Primery Key constraint.\n",tableName,name);
 907        if((!tdbPKey) && info->isPrimary)
 908            printf("Warning: In TDB, The %s's '%s' field should have Primary Key constraint.\n",tableName,colName);
 909
 910        /* NotNull Checking */
 911        bool isCsqlNotNull=false;
 912        bool isTdbNotNull=false;
 913        if(tdbName==mysql){
 914            if(info->isNull && nullable)
 915            printf("Warning: In TDB, The %s's '%s' field should have a NOT NULL constraint.\n",tableName,colName);
 916            if((!info->isNull) && (!nullable))
 917                printf("Warning: In CSQL, The %s's '%s' field should have a NOT NULL constraint.\n",tableName,name);
 918        }
 919        icol++;
 920    }
 921    return OK;
 922 }
 923
 924 DbRetVal CacheTableLoader::cacheAllTablesFromDs(char *dsnName,bool tableDefinition, bool isDirect,char *username, char *password)
 925 {
 926     char dsn[72];
 927     DbRetVal rv = OK;
 928     FILE *fp;
 929     fp = fopen(Conf :: config.getDsConfigFile(),"r");
 930     if(fp==NULL) {
 931         printError(ErrSysInit, "csqlds.conf file does not exist");
 932         return ErrSysInit;
 933     }
 934     char dsnId[IDENTIFIER_LENGTH]; dsnId[0]='\0';
 935     char user[IDENTIFIER_LENGTH]; user[0] = '\0';
 936     char passwd[IDENTIFIER_LENGTH]; passwd[0] = '\0';
 937     char tdb[IDENTIFIER_LENGTH]; tdb[0]='\0';
 938     unsigned int mode;
 939     bool isCached=false;
 940
 941     /* If -d option is disable, the If statementn will true. */
 942     if(strcmp(dsnName,"")==0) {
 943         strcpy(dsnName, Conf::config.getDSN());
 944     }
 945     bool isDSNExist=false;
 946     while(!feof(fp)) {
 947        fscanf(fp,"%s %s %s %s\n",dsnId,user,passwd,tdb);
 948        if(strcmp(dsnId,dsnName)==0) {
 949            if( strcmp(user,"NULL")!=0 && strcmp(passwd,"NULL")!=0) {
 950                sprintf(dsn,"DSN=%s;UID=%s;PWD=%s;",dsnName,user,passwd);
 951                isDSNExist=true;
 952                break;
 953            }else{
 954                sprintf(dsn,"DSN=%s;",dsnName);
 955                isDSNExist=true;
 956                break;
 957            }
 958        }
 959     }
 960      if(!isDSNExist) {
 961        printError(ErrNotExists,"Entries is not present in the csqlds.conf file\n");
 962        fclose(fp);
 963        return ErrNotExists;
 964     }
 965     fclose(fp);
 966
 967     TDBInfo tdbName=mysql;
 968     if (strcasecmp(tdb,"mysql") == 0) tdbName=mysql;
 969     else if (strcasecmp(tdb,"postgres")==0) tdbName=postgres;
 970     else printError(ErrNotFound,"Target Database Name is not properly set.Tdb name could be MySql and Postgres.\n");
 971     logFine(Conf::logger, "TDB Name:%s\n", tdb);
 972      /* The ODBC section in intended to get all the tables from TDB,
 973  *      * what SQLTables() is doing that. */
 974
 975     SQLCHAR outstr[1024];
 976     SQLSMALLINT outstrlen;
 977     int retValue =0;
 978     SQLHENV henv;
 979     SQLHDBC hdbc;
 980     SQLHSTMT hstmt;
 981     SQLSMALLINT columns;
 982     char table[IDENTIFIER_LENGTH][IDENTIFIER_LENGTH];
 983     int counter=0;
 984     char buf[IDENTIFIER_LENGTH];
 985     int row = 0;
 986     SQLINTEGER indicator[ 5 ];
 987      int colPos;//Only to bind table name filed.
 988
 989     CacheTableLoader cacheLoader;
 990
 991     /* Environment Handle. */
 992     retValue = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 993     if (retValue) {
 994         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 995         return ErrSysInit;
 996     }
 997      /* We want ODBC 3 support */
 998     SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
 999     /* Conenction handle. */
1000     retValue = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
1001      if (retValue) {
1002         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
1003         return ErrSysInit;
1004     }
1005      /* Connect to TDB */
1006     retValue = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS,
1007                          outstr, sizeof(outstr), &outstrlen,
1008                          SQL_DRIVER_NOPROMPT);
1009     if (SQL_SUCCEEDED(retValue)) {
1010         printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", dsn);
1011     }else{
1012         printError(ErrSysInit, "Failed to connect to target database\n");
1013         return ErrSysInit;
1014     }
1015      /* Statement handle */
1016     retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
1017     if (retValue) {
1018         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
1019         return ErrSysInit;
1020     }
1021     if(tdbName == mysql){
1022         colPos=3;
1023         /* User name is required in upper case for the SQLTables()'s  4th parameter */
1024         Util::str_toupper((char*)user);
1025         retValue=SQLTables(hstmt,NULL, 0, (SQLCHAR*)user, SQL_NTS, NULL, 0, (SQLCHAR*)"TABLE", SQL_NTS);
1026         if(retValue){
1027             printError(ErrSysInit, "Unable to retrieve list of tables\n");
1028             return ErrSysInit;
1029         }
1030         /* Binding Column for 3rd parameter to get Table name. */
1031         retValue=SQLBindCol(hstmt,3, SQL_C_CHAR,buf,sizeof(buf),NULL);
1032         if(retValue){
1033             printError(ErrSysInit,"Unable to BindCol\n");
1034             return ErrSysInit;
1035         }
1036         /* For Postgres DB , SQLTables() retrieves all system and metadata tables,along with User defined table.
1037          * So Here is a another option to fetch the user defined tables only */
1038     }else if(tdbName==postgres){
1039         SQLCHAR table[200]="SELECT table_name FROM information_schema.tables  WHERE table_schema NOT IN ('pg_catalog','information_schema');";
1040         /* Preparing the query */
1041         retValue=SQLPrepare(hstmt,table,SQL_NTS);
1042         if(retValue){
1043             printError(ErrSysInit,"Unable to Prapare the statement\n");
1044             return ErrSysInit;
1045         }
1046         /* Binding the "table_name" column only */
1047         retValue = SQLBindCol(hstmt,1,SQL_C_CHAR,buf,sizeof(buf),NULL);
1048         if(retValue){
1049             printError(ErrSysInit,"Unable to bind the column\n");
1050             return ErrSysInit;
1051         }
1052         /* Execute the SELECT statement */
1053         retValue = SQLExecute(hstmt);
1054         if(retValue){
1055             printError(ErrSysInit,"Unable to execute\n");
1056             return ErrSysInit;
1057         }
1058     }
1059
1060     while(SQL_SUCCEEDED(retValue = SQLFetch(hstmt))){
1061         /* copy Buffer value */
1062         //strcpy(&table[counter][0],buf);
1063         /* settign DSN */
1064         cacheLoader.setDsnName(dsnName);
1065         TableConf::config.setDsnName(dsnName);
1066         cacheLoader.setConnParam(username, password);
1067         TableConf::config.setConnParam(username, password);
1068         /* Check table is cached or not */
1069         mode = TableConf::config.getTableMode(buf);
1070         /* Settign up table */
1071         cacheLoader.setTable(buf);
1072         TableConf::config.setTable(buf);
1073         isCached = TableConf::config.isTableCached(mode);
1074         if(isCached){
1075             printf("Warning: Table '%s' is already cached.\n",buf);
1076         }else{
1077              rv = cacheLoader.load(tableDefinition);
1078              if(rv != OK){
1079                  printf("Warning: Table '%s' is present in CSQL locally.\n",buf);
1080              }else{
1081                   TableConf::config.addToCacheTableFile(isDirect);
1082                   printf("Cached Table:%s\n",buf);
1083                   TableConf::config.init();
1084              }
1085         }
1086         counter++;
1087     }
1088     /* Checking couter value */
1089     if(counter==0)
1090     printf("There is no table present in Target Database.\n");
1091     /*Closing opening forwarded Cursor */
1092     retValue=SQLCloseCursor(hstmt);
1093     if(retValue){
1094         printError(ErrSysInit,"Unable to close the cursor\n");
1095         return ErrSysInit;
1096     }
1097     /* Commiting the transaction */
1098     retValue=SQLTransact(henv,hdbc,SQL_COMMIT);
1099     if(retValue){
1100         printError(ErrSysInit,"Unable to commit the transaction\n");
1101         return ErrSysInit;
1102     }
1103      /* Freeing Statement handle */
1104     retValue = SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
1105     if(retValue){
1106         printError(ErrSysInit,"Unable to free statement handle\n");
1107         return ErrSysInit;
1108     }
1109      /* Disconnecting from TDB */
1110     retValue = SQLDisconnect(hdbc);
1111     if(retValue){
1112          printError(ErrSysInit,"Unable to disconnect from DS handle\n");
1113          return ErrSysInit;
1114     }
1115      /* Freeing Connection handle */
1116     retValue = SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
1117     if(retValue){
1118         printError(ErrSysInit,"Unable to free connection handle\n");
1119         return ErrSysInit;
1120     }
1121     /* Freeing Environmant handle */
1122     retValue = SQLFreeHandle(SQL_HANDLE_ENV,henv);
1123     if(retValue){
1124         printError(ErrSysInit,"Unable to free environment handle\n");
1125         return ErrSysInit;
1126     }
1127     return OK;
1128 }/* -----------------------------End------------------------------- */
1129
1130 bool CacheTableLoader::resolveForDSN(char *dsn, TDBInfo &tdbName, DbRetVal &rv)
1131 {
1132     char dsnId[IDENTIFIER_LENGTH]; dsnId[0]='\0';
1133     char user[IDENTIFIER_LENGTH]; user[0] = '\0';
1134     char passwd[IDENTIFIER_LENGTH]; passwd[0] = '\0';
1135     char tdb[IDENTIFIER_LENGTH]; tdb[0]='\0';
1136     FILE *fp;
1137
1138     fp = fopen(Conf::config.getDsConfigFile(),"r");
1139     if(fp==NULL) {
1140         printError(ErrSysInit, "csqlds.conf file does not exist");
1141         rv = ErrSysInit;
1142         return false;
1143     }
1144
1145     // STARTs Here:
1146     // DSN, user and password value is read here from csql.conf file and
1147     // csqlds.conf file.
1148     // it's true if -d option is specified and the DSN value not matched with
1149     // csql.conf's DSN.
1150     if(strcmp(dsnName,"")==0) strcpy(dsnName, Conf::config.getDSN());
1151
1152     bool isDSNExist=false;
1153     while(!feof(fp)) {
1154         fscanf(fp,"%s %s %s %s\n",dsnId,user,passwd,tdb);
1155     if(strcmp(dsnId,dsnName)==0) {   // Both the DSN is matched here
1156         if(strcmp(user,"NULL")!=0 && strcmp(passwd,"NULL")!=0) {
1157                 sprintf(dsn,"DSN=%s;UID=%s;PWD=%s;",dsnName,user,passwd);
1158                 isDSNExist=true;
1159                 break;
1160             } else {
1161                 sprintf(dsn,"DSN=%s;",dsnName);
1162                 isDSNExist=true;
1163                 break;
1164             }
1165         }
1166     }
1167     if(!isDSNExist) {
1168         printError(ErrNotExists,"Entries is not present in the csqlds.conf file\n");
1169         fclose(fp);
1170         rv = ErrNotExists;
1171         return false;
1172     }
1173     fclose(fp);
1174     if (strcasecmp(tdb,"mysql") == 0) tdbName=mysql;
1175     else if (strcasecmp(tdb,"postgres")==0) tdbName=postgres;
1176     else {
1177         printError(ErrNotFound," Target Database Name is not properly set.Tdb name could be mysql, postgres\n");
1178         rv = ErrNotFound;
1179         return false;
1180     }
1181     logFine(Conf::logger, "TDB Name:%s\n", tdb);
1182     return isDSNExist;
1183 }
1184
1185 void CacheTableLoader::generateCacheTableStatement(char *stmtBuf)
1186 {
1187     if(((strcmp(conditionVal,"")==0) || (strcmp(conditionVal,"NULL")==0)) && ((strcmp(fieldlistVal,"")==0) || (strcmp(fieldlistVal,"NULL")==0)))
1188     {
1189        sprintf(stmtBuf, "SELECT * FROM %s;", tableName);
1190     }
1191     else if(((strcmp(conditionVal,"")!=0) || (strcmp(conditionVal,"NULL")!=0)) && ((strcmp(fieldlistVal,"")==0) || (strcmp(fieldlistVal,"NULL")==0)))
1192     {
1193        sprintf(stmtBuf,"SELECT * FROM %s where %s;",tableName,conditionVal);
1194
1195     }
1196     else if(((strcmp(conditionVal,"")==0) || (strcmp(conditionVal,"NULL")==0)) && ((strcmp(fieldlistVal,"")!=0) || (strcmp(fieldlistVal,"NULL")!=0)))
1197     {
1198         sprintf(stmtBuf,"SELECT %s FROM %s;",fieldlistVal,tableName);
1199     }
1200     else
1201         sprintf(stmtBuf,"SELECT %s FROM %s where %s;",fieldlistVal,tableName,conditionVal);
1202     logFinest(Conf::logger, "Cache Table Stmt %s", stmtBuf);
1203 }
1204
1205 bool CacheTableLoader::prepareCreateIndexStatement(SQLHSTMT hstmtmeta, char *crtIdxStmt, TDBInfo tdbName, HashIndexInitInfo *inf)
1206 {
1207     char columnname[IDENTIFIER_LENGTH];
1208     char indexname[IDENTIFIER_LENGTH];
1209     int retValue=SQLPrimaryKeys(hstmtmeta, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*) tableName, SQL_NTS);
1210     retValue = SQLBindCol(hstmtmeta, 4, SQL_C_CHAR,columnname, 129,NULL);
1211 //    char *name = NULL;
1212     char *ptr=crtIdxStmt;
1213     sprintf(ptr, "CREATE INDEX %s_PRIMARY on %s ( ", tableName, tableName);
1214     ptr += strlen(ptr);
1215     bool isPriIndex=false;
1216     char indname[IDENTIFIER_LENGTH];
1217     if(SQLFetch( hstmtmeta ) == SQL_SUCCESS)
1218     {
1219         Util::str_tolower(columnname);
1220         inf->list.append(columnname);
1221         sprintf(ptr, "%s ", columnname);
1222         ptr += strlen(ptr);
1223         while ( SQLFetch( hstmtmeta ) == SQL_SUCCESS ) {
1224             Util::str_tolower(columnname);
1225             inf->list.append(columnname);
1226             sprintf(ptr, ", %s ", columnname);
1227             ptr += strlen(ptr);
1228         }
1229         sprintf(ptr, ") PRIMARY SIZE 10007;");
1230         inf->indType =  hashIndex;
1231         inf->bucketSize = 10007;
1232         inf->isUnique = true; inf->isPrimary = true;
1233         strcpy(inf->tableName, tableName);
1234         strcpy(indexname,"PRIMARY");
1235         sprintf(indname, "%s_%s", tableName, indexname);
1236         isPriIndex=true;
1237     }
1238     return isPriIndex;
1239 }
1240
1241 DbRetVal CacheTableLoader::prepareCreateTableStatement(char *crtTblStmt, SQLHSTMT hstmt, HashIndexInitInfo *inf, int totalFields, TDBInfo tdbName, bool &isKeyFld)
1242 {
1243     DbRetVal rv = OK;
1244     UWORD icol=1;
1245     UCHAR colName[IDENTIFIER_LENGTH];
1246     SWORD colNameMax=IDENTIFIER_LENGTH;
1247     SWORD nameLength=0;
1248     SWORD colType=0;
1249     SQLULEN colLength = 0;
1250     SWORD scale=0;
1251     SWORD nullable=0;
1252     int retValue = 0;
1253     bool isNullfld=false;
1254     bool firstFld = true;
1255     char *name = NULL;
1256
1257     char *ptr = crtTblStmt;
1258     sprintf(ptr, "CREATE TABLE %s ( ", tableName);
1259     ptr += strlen(ptr);
1260     while (icol <= totalFields) {
1261         retValue = SQLDescribeCol(hstmt, icol, colName, colNameMax,
1262                                          &nameLength, &colType, &colLength,
1263                                                             &scale, &nullable);
1264         if (retValue) {
1265             printError(ErrSysInit, "Unable to retrieve ODBC column info\n");
1266             return ErrSysInit;
1267         }
1268         Util::str_tolower((char*)colName);
1269         printDebug(DM_Gateway, "Describe Column %s %d %d %d %d\n", colName, colType, colLength, scale, nullable);
1270         logFinest(Conf::logger, "Describe Column colName:%s colType:%d colLen:%d scale:%d nullable:%d\n", colName, colType, colLength, scale, nullable);
1271         icol++;
1272         if(strcmp((char*)colName,fieldName)== 0)
1273         {
1274             isKeyFld=true;
1275             isNullfld=true;
1276         }
1277         bool isPriFld=false;
1278         if (nullable) {
1279             inf->list.resetIter();
1280             while ((name=inf->list.nextFieldName())!=NULL) {
1281                 if(0==strcmp((char*)colName,name)) {
1282                     if (firstFld) {
1283                         firstFld = false;
1284                         sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
1285                         ptr += strlen(ptr);
1286                         if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY)
1287                         {
1288                             sprintf(ptr, "(%d) NOT NULL",colLength+1);
1289                         } else { sprintf(ptr, " NOT NULL"); }
1290                             ptr += strlen(ptr);
1291                     } else {
1292                         sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
1293                         ptr += strlen(ptr);
1294                         if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY)
1295                         {
1296                             sprintf(ptr, "(%d) NOT NULL",colLength+1);
1297                         } else { sprintf(ptr, " NOT NULL"); }
1298                         ptr += strlen(ptr);
1299                     }
1300                     //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale,tdbName), colLength +1, NULL, true);
1301                     isPriFld=true;
1302                     break;
1303                 }
1304             }
1305             if(!isPriFld) {
1306                 if(!isNullfld) {
1307                     if (firstFld) {
1308                         firstFld = false;
1309                         sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
1310                         ptr += strlen(ptr);
1311                         if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1312                             sprintf(ptr, "(%d)",colLength+1);
1313                             ptr += strlen(ptr);
1314                         }
1315                     } else {
1316                         sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
1317                         ptr += strlen(ptr);
1318                         if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1319                             sprintf(ptr, "(%d)",colLength+1);
1320                             ptr += strlen(ptr);
1321                         }
1322                     }
1323                     //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale,tdbName), colLength+1);
1324                 } else {
1325                     if (firstFld) {
1326                         firstFld = false;
1327                         sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
1328                         ptr += strlen(ptr);
1329                         if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1330                             sprintf(ptr, "(%d) NOT NULL",colLength+1);
1331                         } else { sprintf(ptr, " NOT NULL"); }
1332                         ptr += strlen(ptr);
1333                     } else {
1334                         sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
1335                         ptr += strlen(ptr);
1336                         if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1337                             sprintf(ptr, "(%d) NOT NULL",colLength+1);
1338                         } else { sprintf(ptr, " NOT NULL"); }
1339                         ptr += strlen(ptr);
1340                     }
1341                     //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale,tdbName), colLength+1, NULL, true);
1342                     isNullfld=false;
1343                 }
1344             }
1345         } else {
1346             if (firstFld) {
1347                 firstFld = false;
1348                 sprintf(ptr, "%s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength,scale,tdbName)));
1349                 ptr += strlen(ptr);
1350                 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1351                     sprintf(ptr, "(%d) NOT NULL",colLength+1);
1352                 } else { sprintf(ptr, " NOT NULL"); }
1353                 ptr += strlen(ptr);
1354             } else {
1355                 sprintf(ptr, ", %s %s", colName, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType,colLength, scale, tdbName)));
1356                 ptr += strlen(ptr);
1357                 if (colType == SQL_CHAR || colType == SQL_VARCHAR || colType == SQL_BINARY) {
1358                     sprintf(ptr, "(%d) NOT NULL",colLength+1);
1359                 } else { sprintf(ptr, " NOT NULL"); }
1360                 ptr += strlen(ptr);
1361             }
1362             //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale, tdbName), colLength +1, NULL, true);
1363         }
1364     }
1365     sprintf(ptr, ");");
1366     ptr += strlen(ptr);
1367     //printf("table stmt '%s'\n", crtTblStmt);
1368     return rv;
1369 }
1370
1371 void CacheTableLoader::prepareInsertStatement(AbsSqlStatement *stmt, List *fNameList, char *insStmt)
1372 {
1373     DbRetVal rv = OK;
1374     char *ptr = insStmt;
1375     sprintf(ptr,"INSERT INTO %s VALUES(", tableName);
1376     ptr += strlen(ptr);
1377     bool firstFld = true;
1378     SqlStatement *sqlStmt = (SqlStatement *)stmt;
1379     *fNameList = sqlStmt->getFieldNameList(tableName, rv);
1380     int noOfFields = fNameList->size();
1381     while (noOfFields--) {
1382         if (firstFld) {
1383             firstFld = false;
1384             sprintf(ptr,"?", tableName);
1385             ptr += strlen(ptr);
1386         } else {
1387             sprintf(ptr, ",?");
1388             ptr += strlen(ptr);
1389         }
1390     }
1391     sprintf(ptr, ");");
1392     ptr += strlen(ptr);
1393 }