1 /***************************************************************************
2 * Copyright (C) 2007 by www.databasecache.com *
3 * Contact: praba_tuty@databasecache.com *
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. *
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. *
15 ***************************************************************************/
17 #include<CacheTableLoader.h>
18 #include<TableConfig.h>
20 #include<SqlConnection.h>
21 #include<SqlLogConnection.h>
22 #include<SqlStatement.h>
23 #include<SqlFactory.h>
25 DbRetVal
CacheTableLoader::load(bool tabDefinition
)
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 rv
= hasRecords(dbMgr
, tableName
);
41 dbMgr
->setCanTakeCheckPoint(true);
49 rv
= load(conn
, stmt
, tabDefinition
);
52 dbMgr
->setCanTakeCheckPoint(true);
58 DbRetVal
CacheTableLoader::hasRecords(DatabaseManager
*dbMgr
, const char* tableName
)
60 Table
*tbl
= dbMgr
->openTable(tableName
);
62 printError(ErrNotEmpty
, "The table '\%s\' is not found", tableName
);
65 if (tbl
->numTuples()) {
66 printError(ErrNotEmpty
, "The table '\%s\' is not empty", tableName
);
67 dbMgr
->closeTable(tbl
);
70 dbMgr
->closeTable(tbl
);
73 DbRetVal
CacheTableLoader::connect(SQLHENV
&henv
, SQLHDBC
&hdbc
, SQLHSTMT
&hstmt
, char *dsn
, char* stmtBuf
)
76 SQLSMALLINT outstrlen
;
78 retValue
= SQLAllocHandle (SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &henv
);
80 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
83 SQLSetEnvAttr(henv
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
84 retValue
= SQLAllocHandle (SQL_HANDLE_DBC
, henv
, &hdbc
);
86 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
89 retValue
= SQLDriverConnect(hdbc
, NULL
, (SQLCHAR
*)dsn
, SQL_NTS
,
90 outstr
, sizeof(outstr
), &outstrlen
,
92 if (SQL_SUCCEEDED(retValue
)) {
93 printDebug(DM_Gateway
, "Connected to target database using dsn = %s\n", dsn
);
95 printError(ErrSysInit
, "Failed to connect to target database\n");
99 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmt
);
101 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
105 retValue
= SQLPrepare (hstmt
, (unsigned char *) stmtBuf
, SQL_NTS
);
107 printError(ErrSysInit
, "Unable to Prepare ODBC statement \n");
113 DbRetVal
CacheTableLoader::load(AbsSqlConnection
*conn
, AbsSqlStatement
*stmt
, bool tabDefinition
)
115 char dsn
[IDENTIFIER_LENGTH
];
116 TDBInfo tdbName
= mysql
;
119 bool isDSNExist
= resolveForDSN(dsn
, tdbName
, rv
);
120 if (!isDSNExist
) return rv
;
122 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
123 DatabaseManager
*dbMgr
= con
->getConnObject().getDatabaseManager();
129 /*SQLCHAR outstr[1024];
130 SQLSMALLINT outstrlen;
134 retValue = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
136 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
139 SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
140 retValue = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
142 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
145 retValue = SQLDriverConnect(hdbc, NULL, (SQLCHAR*)dsn, SQL_NTS,
146 outstr, sizeof(outstr), &outstrlen,
147 SQL_DRIVER_NOPROMPT);
148 if (SQL_SUCCEEDED(retValue)) {
149 printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", dsn);
151 printError(ErrSysInit, "Failed to connect to target database\n");
155 retValue=SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
157 printError(ErrSysInit, "Unable to allocate ODBC handle \n");
161 retValue = SQLPrepare (hstmt, (unsigned char *) stmtBuf, SQL_NTS);
163 printError(ErrSysInit, "Unable to Prepare ODBC statement \n");
167 generateCacheTableStatement(stmtBuf
);
168 connect(henv
, hdbc
, hstmt
, dsn
, stmtBuf
);
169 int nRecordsToFetch
= Conf::config
.getNoOfRowsToFetchFromTDB();
170 int nFetchedRecords
= 0;
171 SQLUSMALLINT
*rowStatus
= (SQLUSMALLINT
*)
172 malloc(nRecordsToFetch
* sizeof(SQLUSMALLINT
));
173 memset(rowStatus
, 0, nRecordsToFetch
* sizeof(SQLUSMALLINT
));
174 SQLSetStmtAttr(hstmt
, SQL_ATTR_ROW_BIND_TYPE
, SQL_BIND_BY_COLUMN
, 0);
175 SQLSetStmtAttr(hstmt
, SQL_ATTR_ROW_ARRAY_SIZE
, (void *) nRecordsToFetch
, 0);
176 SQLSetStmtAttr(hstmt
, SQL_ATTR_ROW_STATUS_PTR
, rowStatus
, 0 );
177 SQLSetStmtAttr(hstmt
, SQL_ATTR_ROWS_FETCHED_PTR
, &nFetchedRecords
, 0 );
180 retValue
= SQLNumResultCols (hstmt
, &totalFields
);
182 printError(ErrSysInit
, "Unable to retrieve ODBC total columns\n");
185 logFinest(Conf::logger
, "Cache Table noOfFields %hd", totalFields
);
187 UCHAR colName
[IDENTIFIER_LENGTH
];
191 SQLULEN colLength
= 0;
194 colNameMax
= IDENTIFIER_LENGTH
;
195 char columnname
[IDENTIFIER_LENGTH
];
196 short type
; short unique
;
198 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmtmeta
);
201 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
205 char crtIdxStmt
[1024];
206 char *ptr
=crtIdxStmt
;
207 HashIndexInitInfo
*inf
= new HashIndexInitInfo();
208 bool isPriIndex
= prepareCreateIndexStatement(hstmtmeta
, crtIdxStmt
, tdbName
, inf
);
210 bool iskeyfieldExist
=false;
211 bool isPKFieldSpecified
= false;
213 if((strcmp(fieldName
,"")!=0) && (strcmp(fieldName
,"NULL")!=0) )
215 isPKFieldSpecified
= true;
217 if ( isPriIndex
&& ( strcmp(fieldlistVal
,"")!=0 ) &&
218 ( strcmp(fieldlistVal
,"NULL") != 0 )) {
219 inf
->list
.resetIter();
220 while ( (name
=inf
->list
.nextFieldName()) != NULL
) {
221 iskeyfieldExist
= TableConf::config
.isFieldExist(name
);
222 if(!iskeyfieldExist
) { break; }
224 } else if (isPriIndex
) { iskeyfieldExist
= true; }
225 if ( isPKFieldSpecified
&& !(TableConf::config
.isFieldExist(fieldName
)) )
227 if ( Conf::config
.useTwoWayCache() &&
228 (strcmp(fieldlistVal
,"")!=0) &&
229 (strcmp(fieldlistVal
,"NULL")!=0))
231 printError(ErrSysInit
, "Bidirectional caching should have primary key in %s \n", tableName
);
232 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
233 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
234 SQLDisconnect (hdbc
);
235 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
236 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
241 if (!iskeyfieldExist
&& !isPKFieldSpecified
)
243 if(Conf::config
.useTwoWayCache())
245 printError(ErrSysInit
, "Bidirectional caching fail for no primary key in %s \n", tableName
);
246 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
247 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
248 SQLDisconnect (hdbc
);
249 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
250 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
257 char crtTblStmt
[1024];
258 rv
= prepareCreateTableStatement(crtTblStmt
, hstmt
, inf
, totalFields
,
261 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
262 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
263 SQLDisconnect (hdbc
);
264 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
265 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
269 //printf("table stmt '%s'\n", crtTblStmt);
270 if(((strcmp(fieldName
,"")!=0) && (strcmp(fieldName
,"NULL")!=0))
272 printError(ErrSysInit
, "Unable to cache Table for %s with key field %s\n", tableName
,fieldName
);
273 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
274 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
275 SQLDisconnect (hdbc
);
276 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
277 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
281 rv
= stmt
->prepare(crtTblStmt
);
283 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
284 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
285 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
286 SQLDisconnect (hdbc
);
287 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
288 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
293 rv
= stmt
->execute(rows
);
295 printError(ErrSysInit
, "Unable to execute create table stmt\n");
296 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
297 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
298 SQLDisconnect (hdbc
);
299 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
300 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
304 logFinest(Conf::logger
, "Cache Table: Table Created :%s", crtTblStmt
);
307 //Create primary key index if present
308 if (isPriIndex
&& ( iskeyfieldExist
||
309 (strcmp(fieldlistVal
,"")==0 || strcmp(fieldlistVal
,"NULL")== 0))) {
310 rv
= stmt
->prepare(crtIdxStmt
);
312 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
313 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
314 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
315 SQLDisconnect (hdbc
);
316 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
317 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
322 rv
= stmt
->execute(rows
);
324 printError(ErrSysInit
, "Unable to execute create table stmt\n");
325 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
326 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
327 SQLDisconnect (hdbc
);
328 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
329 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
333 //printf("Primary index created from create Index stmt\n");
335 retValue
= SQLCloseCursor(hstmtmeta
);
336 rv
= createIndex(hstmtmeta
, tableName
, inf
, stmt
,isPKFieldSpecified
);
338 dbMgr
->dropTable(tableName
);
339 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
340 SQLDisconnect (hdbc
);
341 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
342 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
346 logFinest(Conf::logger
, "Cache Table: Index :%s", crtIdxStmt
);
348 } // tableDefinition scope finishes here
350 else { /***Checking for Table Schema between CSQL and TDB(cachetable -s option)***/
351 rv
=checkingSchema(hdbc
,hstmt
,conn
,stmt
,tdbName
);
353 printError(ErrSysInit
,"Unable to cache the '%s' table due to schema mismatched.",tableName
);
354 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
355 SQLDisconnect (hdbc
);
356 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
357 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
362 // Now load the table with records
368 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
->getInnerStatement();
369 sqlStmt
->setConnection(con
);
370 prepareInsertStatement(sqlStmt
, &fNameList
, insStmt
);
371 int totalFields
= fNameList
.size();
372 rv
= stmt
->prepare(insStmt
);
374 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
375 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
376 SQLDisconnect (hdbc
);
377 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
378 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
381 sqlStmt
->setLoading(true);
382 ListIterator fNameIter
= fNameList
.getIterator();
383 FieldInfo
*info
= new FieldInfo();
384 int fcount
=1; void *valBuf
=NULL
;
385 Identifier
*elem
= NULL
;
386 void *tembuf
=NULL
;//For postgre BigInt type
390 while (fNameIter
.hasElement()) {
391 elem
= (Identifier
*) fNameIter
.nextElement();
392 sqlStmt
->getFieldInfo(tableName
, (const char*)elem
->name
, info
);
394 if (info
->type
== typeString
|| info
->type
== typeVarchar
) {
395 size
= nRecordsToFetch
* AllDataType::size(info
->type
,info
->length
);
397 size
= nRecordsToFetch
* AllDataType::size(info
->type
);
399 valBuf
= malloc(size
);
400 os::memset(valBuf
,0,size
);
402 if (info
->type
!= typeDate
&& info
->type
!= typeTime
&& info
->type
!= typeTimeStamp
) {
403 if (info
->type
== typeLongLong
&& tdbName
== postgres
)
406 bindLen
= AllDataType::size(info
->type
,
407 AllDataType::size(info
->type
, info
->length
));
410 case typeDate
: bindLen
= sizeof(DATE_STRUCT
); break;
411 case typeTime
: bindLen
= sizeof(TIME_STRUCT
); break;
412 case typeTimeStamp
: bindLen
= sizeof(TIMESTAMP_STRUCT
); break;
416 bBuf
= (BindBuffer
*) SqlStatement::fillBindBuffer(tdbName
, info
->type
, valBuf
, bindLen
, nRecordsToFetch
);
417 valBufList
.append(bBuf
);
418 retValue
= SQLBindCol (hstmt
, fcount
, AllDataType::convertToSQL_C_Type(info
->type
,tdbName
), valBuf
, bindLen
, bBuf
->nullData
);
421 if(tabDefinition
) dbMgr
->dropTable(tableName
);
422 printError(ErrSysInit
, "Unable to bind columns in ODBC\n");
423 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
424 SQLDisconnect (hdbc
);
425 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
426 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
432 while (fNameIter
.hasElement())
433 delete ((FieldName
*) fNameIter
.nextElement());
436 retValue
= SQLExecute (hstmt
);
438 printError(ErrSysInit
, "Unable to execute ODBC statement\n");
439 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
440 SQLDisconnect (hdbc
);
441 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
442 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
447 //TODO: if SQLFetch return other than record not found error
448 //it should drop the table
449 retValue
= SQLFetchScroll(hstmt
, SQL_FETCH_NEXT
, 0);
451 for (int row
= 0; row
< nFetchedRecords
; row
++) {
454 ListIterator bindIter
= valBufList
.getIterator();
455 while (bindIter
.hasElement()) {
456 bBuf
= (BindBuffer
*) bindIter
.nextElement();
457 if (bBuf
->nullData
[row
] == SQL_NULL_DATA
) {
458 stmt
->setNull(fldpos
+1);
460 val
= (void *) ((char *)bBuf
->csql
+ row
* bBuf
->length
);
461 switch (bBuf
->type
) {
464 val
= (void *) ((char *)bBuf
->csql
+ row
* bBuf
->length
);
465 if( tdbName
== postgres
)
466 Util::trimRight((char*)val
);
471 val
= (void *) ((char *)bBuf
->csql
+ row
* sizeof(Date
));
472 Date
*dtCSQL
= (Date
*) val
;
473 void *tVal
= (void *) ((char *)bBuf
->targetdb
+ row
* sizeof(DATE_STRUCT
));
474 DATE_STRUCT
*dtTarget
= (DATE_STRUCT
*) tVal
;
475 dtCSQL
->set(dtTarget
->year
,dtTarget
->month
,dtTarget
->day
);
480 val
= (void *) ((char *)bBuf
->csql
+ row
* sizeof(Time
));
481 Time
*dtCSQL
= (Time
*) val
;
482 void *tVal
= (void *) ((char *)bBuf
->targetdb
+ row
* sizeof(TIME_STRUCT
));
483 TIME_STRUCT
*dtTarget
= (TIME_STRUCT
*) tVal
;
484 dtCSQL
->set(dtTarget
->hour
,dtTarget
->minute
,dtTarget
->second
);
489 val
= (void *) ((char *)bBuf
->csql
+ row
* sizeof(TimeStamp
));
490 TimeStamp
*dtCSQL
= (TimeStamp
*) val
;
491 void *tVal
= (void *) ((char *)bBuf
->targetdb
+ row
* sizeof(TIMESTAMP_STRUCT
));
492 TIMESTAMP_STRUCT
*dtTarget
= (TIMESTAMP_STRUCT
*) tVal
;
493 dtCSQL
->setDate(dtTarget
->year
,dtTarget
->month
,dtTarget
->day
);
494 dtCSQL
->setTime(dtTarget
->hour
,dtTarget
->minute
,dtTarget
->second
, dtTarget
->fraction
);
499 val
= (void *) ((char *)bBuf
->csql
+ row
* sizeof(long long));
500 void *tVal
= (void *) ((char *)bBuf
->targetdb
+ row
* bBuf
->length
);
501 if (tdbName
== postgres
) {
502 sscanf((const char*)tVal
,"%lld",(long long*) val
);
507 SqlStatement::setParamValues(stmt
, fldpos
+1, bBuf
->type
, bBuf
->length
, val
);
512 rv
= stmt
->execute(rows
);
514 printError(ErrSysInit
, "Unable to cache record in CSQL.\n");
515 if(tabDefinition
) dbMgr
->dropTable(tableName
);
516 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
517 SQLDisconnect (hdbc
);
518 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
519 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
525 } while (SQL_SUCCEEDED(retValue
) && nFetchedRecords
== nRecordsToFetch
);
529 //PRABA::one operation per transaction gives the best
530 //performance than 100 /Txn in case of durability
531 //TODO::leak:: valBufList and its targetdb buffer
532 ListIterator it
= valBufList
.getIterator();
533 while(it
.hasElement()) {
534 BindBuffer
*bb
= (BindBuffer
*) it
.nextElement();
535 if (bb
->csql
) { free(bb
->csql
); bb
->csql
= NULL
; }
536 if (bb
->targetdb
) { free(bb
->targetdb
); bb
->targetdb
= NULL
; }
537 delete bb
; bb
= NULL
;
540 SQLCloseCursor (hstmt
);
541 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
543 SQLDisconnect (hdbc
);
544 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
545 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
546 logFine(Conf::logger
, "Cached Table: %s", tableName
);
550 DbRetVal
CacheTableLoader::reload()
553 DbRetVal rv
= unload(false);
554 if (rv
!= OK
) return rv
;
555 //get table cache senarios
556 fp
= fopen(Conf::config
.getTableConfigFile(),"r");
558 printError(ErrSysInit
, "csqltable.conf file does not exist");
563 char tablename
[IDENTIFIER_LENGTH
];
564 char fieldname
[IDENTIFIER_LENGTH
];
565 char field
[IDENTIFIER_LENGTH
];
566 char condition
[IDENTIFIER_LENGTH
];
567 char dsnname
[IDENTIFIER_LENGTH
];
570 fscanf(fp
, "%d %s %s %s %s %s\n", &mode
, tablename
,fieldname
,condition
,field
,dsnname
);
571 if(strcmp(tablename
,tableName
)==0) break;
574 setCondition(TableConf::config
.getRealConditionFromFile(condition
));
575 setFieldName(fieldname
);
576 setFieldListVal(field
);
582 DbRetVal
CacheTableLoader::unload(bool tabDefinition
)
584 AbsSqlConnection
*conn
= SqlFactory::createConnection(CSqlLog
);
585 DbRetVal rv
= conn
->connect(userName
, password
);
586 if (rv
!= OK
) return ErrSysInit
;
587 AbsSqlStatement
*stmt
= SqlFactory::createStatement(CSqlLog
);
588 stmt
->setConnection(conn
);
589 SqlLogConnection
*logConn
= (SqlLogConnection
*) conn
;
590 logConn
->setNoMsgLog(true);
591 char statement
[1024];
592 if (TableConf::config
.isTableCached(tableName
) != OK
) {
593 printError(ErrNotCached
, "The table \'%s\' is not cached", tableName
);
599 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
600 DatabaseManager
*dbMgr
= (DatabaseManager
*) con
->getConnObject().getDatabaseManager();
603 delete stmt
; delete conn
;
604 printError(ErrSysInit
, "Authentication failed\n");
609 sprintf(statement
, "DELETE FROM %s;", tableName
);
610 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
611 sqlStmt
->setLoading(true);
612 rv
= stmt
->prepare(statement
);
615 delete stmt
; delete conn
;
620 rv
= stmt
->execute(rows
);
623 delete stmt
; delete conn
;
630 rv
= TableConf::config
.removeFromCacheTableFile();
632 conn
->disconnect(); delete stmt
; delete conn
;
635 sprintf(statement
, "DROP TABLE %s;", tableName
);
636 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
637 sqlStmt
->setLoading(true);
638 rv
= stmt
->prepare(statement
);
640 //TableConf::config.addToCacheTableFile(false);
642 delete stmt
; delete conn
;
646 rv
= stmt
->execute(rows
);
648 //TableConf::config.addToCacheTableFile(false);
649 conn
->disconnect(); delete stmt
; delete conn
;
654 delete stmt
; delete conn
;
655 logFine(Conf::logger
, "Unloaded Cached Table: %s", tableName
);
659 DbRetVal
CacheTableLoader::refresh()
664 DbRetVal
CacheTableLoader::recoverAllCachedTables()
668 DbRetVal rv
= conn
.open(userName
, password
);
669 if(rv
!=OK
) return ErrSysInit
;
671 //Note: if connection is not open, configuration veriables may be incorrect
673 fp
= fopen(Conf::config
.getTableConfigFile(),"r");
675 printError(ErrSysInit
, "csqltable.conf file does not exist");
680 //TODO::take exclusive lock on database
681 char tablename
[IDENTIFIER_LENGTH
];
682 char fieldname
[IDENTIFIER_LENGTH
];
683 char condition
[IDENTIFIER_LENGTH
];
684 char field
[IDENTIFIER_LENGTH
];
685 char dsnname
[IDENTIFIER_LENGTH
];
692 scanItems
= fscanf(fp
, "%d %s %s %s %s %s\n", &mode
, tablename
,fieldname
,condition
,field
,dsnname
);
693 if (scanItems
!= 6) {
695 printf("There is no table to be cached.\n");
698 if (!TableConf::config
.isTableCached(mode
)) continue;
699 printDebug(DM_Gateway
, "Recovering Table from target db: %s\n", tablename
);
700 setCondition(TableConf::config
.getRealConditionFromFile(condition
));
701 if( (strcmp(Conf::config
.getDSN(),dsnname
)!=0) ){
704 setFieldName(fieldname
);
705 setFieldListVal(field
);
706 printf("Recovering table %s %s %s\n", tablename
,condition
,field
);
708 if (rv
!= OK
) { fclose(fp
); return rv
; }
710 setDsnName(Conf::config
.getDSN());
712 setFieldName(fieldname
);
713 setFieldListVal(field
);
714 printf("Recovering table %s %s %s\n", tablename
,condition
,field
);
716 if (rv
!= OK
) { fclose(fp
); return rv
; }
718 logFine(Conf::logger
, "Recovering Table from target db:%s", tablename
);
724 DbRetVal
CacheTableLoader::createIndex(SQLHSTMT hstmtmeta
, char *tableName
, HashIndexInitInfo
*inf
,AbsSqlStatement
*stmt
,bool isPKFieldSpecified
)
726 bool isKeyFld
= false;
728 char columnname
[IDENTIFIER_LENGTH
];
729 char indexname
[IDENTIFIER_LENGTH
];
734 retValue
= SQLStatistics(hstmtmeta
, NULL
, 0, NULL
, SQL_NTS
,
735 (SQLCHAR
*) tableName
, SQL_NTS
, SQL_INDEX_ALL
, SQL_QUICK
);
736 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_SHORT
,
738 retValue
= SQLBindCol(hstmtmeta
, 6, SQL_C_CHAR
,
739 indexname
, 129, NULL
);
740 retValue
= SQLBindCol(hstmtmeta
, 7, SQL_C_SHORT
,
742 retValue
= SQLBindCol(hstmtmeta
, 9, SQL_C_CHAR
,
743 columnname
, 129,NULL
);
745 bool isSecondTime
= false;
746 CacheIndexInfo
*info
=NULL
;
747 while ((retValue
= SQLFetch(hstmtmeta
)) == SQL_SUCCESS
) {
748 printDebug(DM_Gateway
, "Column: %-18s Index Name: %-18s unique:%hd type:%hd\n", columnname
, indexname
, unique
, type
);
751 bool isFldAdd
= false;
752 ListIterator iter
= indexList
.getIterator();
754 while (iter
.hasElement()) {
755 CacheIndexInfo
*indInfo
= (CacheIndexInfo
*)iter
.nextElement();
756 if(0 == strcmp( indInfo
->indexName
, indexname
))
758 indInfo
->fieldNameList
.append(columnname
);
763 info
= new CacheIndexInfo();
764 info
->fieldNameList
.append(columnname
);
765 strcpy(info
->indexName
, indexname
);
766 indexList
.append(info
);
771 ListIterator iter
= indexList
.getIterator();
773 int noOfPkfield
= inf
->list
.size();
776 while (iter
.hasElement()) {
778 bool isFieldExistInCondition
= false;
779 bool isPrimary
=false;
780 CacheIndexInfo
*indInfo
= (CacheIndexInfo
*)iter
.nextElement();
781 int noOfFld
= indInfo
->fieldNameList
.size();
782 indInfo
->fieldNameList
.resetIter();
783 while ((fName
= indInfo
->fieldNameList
.nextFieldName())!=NULL
) {
784 if(( 1 == noOfFld
) && (0 == strcmp(fName
,fieldName
))) {
787 inf
->list
.resetIter();
788 while ((name
=inf
->list
.nextFieldName())!=NULL
)
790 if(0==strcmp(fName
,name
)) { isPrimary
= true; break; }
793 if (!TableConf::config
.isFieldExist(fName
) &&
794 ( (strcmp(fieldlistVal
,"")!=0) &&
795 (strcmp(fieldlistVal
,"NULL")!=0) )) {
796 isFieldExistInCondition
=true;
799 sprintf(cptr
, "%s ,",fName
);
800 cptr
+= strlen(cptr
);
802 if(isFieldExistInCondition
) continue;
805 if (isPrimary
) { continue; }
806 char crtIdxStmt
[1024];
808 sprintf(indname
, "%s_%s", tableName
, indInfo
->indexName
);
809 sprintf(crtIdxStmt
, "CREATE INDEX %s on %s(%s) HASH SIZE 10007;", indname
, tableName
, columnname
);
810 //printf("create index stmt \n'%s'\n", crtIdxStmt);
811 rv
= stmt
->prepare(crtIdxStmt
);
813 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
817 rv
= stmt
->execute(rows
);
819 printError(ErrSysInit
, "Unable to execute create table stmt\n");
823 }// while meta data fetch for index creation
826 while (iter
.hasElement()) delete (CacheIndexInfo
*) iter
.nextElement();
829 SQLCloseCursor (hstmtmeta
);
830 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
831 if( !isKeyFld
&& isPKFieldSpecified
) {
833 char frcIndStmt
[1024];
835 sprintf(indname
, "%s_%s", tableName
, "keyInd");
836 sprintf(frcIndStmt
, "CREATE INDEX %s on %s(%s) HASH;", indname
, tableName
, fieldName
);
837 rv
= stmt
->prepare(frcIndStmt
);
839 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
843 rv
= stmt
->execute(rows
);
845 printError(ErrSysInit
, "Unable to execute create table stmt\n");
849 printError(ErrSysInit
, "Unable to cache Table for %s with key field %s\n", tableName
,fieldName
);
856 // Schema matching between CSQL and TDB table.(cachetable -t <tablename> -s)
857 DbRetVal
CacheTableLoader::checkingSchema(SQLHDBC hdbc
,SQLHSTMT hstmt
, AbsSqlConnection
*conn
, AbsSqlStatement
*stmt
,TDBInfo tdbName
)
860 int noOfPrimaryKey
=0;
864 SQLSMALLINT tdbFields
=0;
866 char columnname
[IDENTIFIER_LENGTH
];
869 UCHAR colName
[IDENTIFIER_LENGTH
];
873 SQLULEN colLength
= 0;
876 colNameMax
= IDENTIFIER_LENGTH
;
878 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
879 DatabaseManager
*dbMgr
= con
->getConnObject().getDatabaseManager();
881 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
->getInnerStatement();
882 sqlStmt
->setConnection(con
);
885 fNameList
= sqlStmt
->getFieldNameList(tableName
, rv
);
886 ListIterator fNameIter
= fNameList
.getIterator();
887 FieldInfo
*info
= new FieldInfo();
888 Identifier
*elem
= NULL
;
890 retValue
=SQLNumResultCols(hstmt
, &tdbFields
);
892 printError(ErrSysInit
, "Unable to retrieve ODBC total columns.\n");
893 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
897 fNameList
= sqlStmt
->getFieldNameList(tableName
, rv
);
898 csqlFields
= fNameList
.size();
899 // noOfFields in both the database are same or not.
900 if(tdbFields
!=csqlFields
){
901 printError(ErrSysInit
,"Number of fields between CSQL and TDB are not equal.");
902 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
905 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmtmeta
);
907 printError(ErrSysInit
, "Unable to allocate ODBC handle. \n");
908 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
911 retValue
=SQLPrimaryKeys(hstmtmeta
, NULL
, SQL_NTS
, NULL
, SQL_NTS
, (SQLCHAR
*) tableName
, SQL_NTS
);
912 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_CHAR
,columnname
, 129,NULL
);
914 while(icol
<=tdbFields
){
915 retValue
= SQLDescribeCol(hstmt
, icol
, colName
, colNameMax
,
916 &nameLength
, &colType
, &colLength
,
917 &scale
, &nullable
);//TDB Field Name
919 printError(ErrSysInit
, "Unable to retrieve ODBC column info.\n");
920 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
923 Util::str_tolower((char*)colName
);
924 elem
= (Identifier
*) fNameIter
.nextElement();
925 sqlStmt
->getFieldInfo(tableName
, (const char*)elem
->name
, info
);
929 rv
= stmt
->getParamFldInfo(icol
,info
);
930 char *name
=(info
->fldName
);//Getting field name for CSQL table.
931 Util::str_tolower((char*)name
);
932 if(strcmp(name
,(char *)colName
) != 0){ //Field name matching between CSQL and TDB.
933 printError(ErrSysInit
,"CSQL's '%s' field did not match with TDB's '%s' field.\n",name
,(char*)colName
);
934 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
938 // DataType matching between CSQL and TDB
939 char ptr
[IDENTIFIER_LENGTH
]; ptr
[0]='\0';
940 char ptr1
[IDENTIFIER_LENGTH
]; ptr1
[0]='\0';
942 sprintf(ptr
,"%s",AllDataType::getSQLString (AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
943 sprintf(ptr1
,"%s",AllDataType::getSQLString(info
->type
));//CSQL Type
944 if(strcmp(ptr
,ptr1
)!=0){
945 printError(ErrSysInit
,"DataType did not match for '%s' field in CSQL.\n",name
);
946 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
950 // Primary Key checking
952 if(SQLFetch( hstmtmeta
) == SQL_SUCCESS
) tdbPKey
=true;
953 if(tdbPKey
&& (!info
->isPrimary
))
954 printf("Warning: In CSQL, The %s's '%s' field should have Primery Key constraint.\n",tableName
,name
);
955 if((!tdbPKey
) && info
->isPrimary
)
956 printf("Warning: In TDB, The %s's '%s' field should have Primary Key constraint.\n",tableName
,colName
);
959 bool isCsqlNotNull
=false;
960 bool isTdbNotNull
=false;
962 if(info
->isNull
&& nullable
)
963 printf("Warning: In TDB, The %s's '%s' field should have a NOT NULL constraint.\n",tableName
,colName
);
964 if((!info
->isNull
) && (!nullable
))
965 printf("Warning: In CSQL, The %s's '%s' field should have a NOT NULL constraint.\n",tableName
,name
);
972 DbRetVal
CacheTableLoader::cacheAllTablesFromDs(char *dsnName
,bool tableDefinition
, bool isDirect
,char *username
, char *password
)
977 fp
= fopen(Conf :: config
.getDsConfigFile(),"r");
979 printError(ErrSysInit
, "csqlds.conf file does not exist");
982 char dsnId
[IDENTIFIER_LENGTH
]; dsnId
[0]='\0';
983 char user
[IDENTIFIER_LENGTH
]; user
[0] = '\0';
984 char passwd
[IDENTIFIER_LENGTH
]; passwd
[0] = '\0';
985 char tdb
[IDENTIFIER_LENGTH
]; tdb
[0]='\0';
989 // If -d option is disable, the If statementn will true.
990 if(strcmp(dsnName
,"")==0) {
991 strcpy(dsnName
, Conf::config
.getDSN());
993 bool isDSNExist
=false;
995 fscanf(fp
,"%s %s %s %s\n",dsnId
,user
,passwd
,tdb
);
996 if(strcmp(dsnId
,dsnName
)==0) {
997 if( strcmp(user
,"NULL")!=0 && strcmp(passwd
,"NULL")!=0) {
998 sprintf(dsn
,"DSN=%s;UID=%s;PWD=%s;",dsnName
,user
,passwd
);
1002 sprintf(dsn
,"DSN=%s;",dsnName
);
1009 printError(ErrNotExists
,"Entries is not present in the csqlds.conf file\n");
1011 return ErrNotExists
;
1015 TDBInfo tdbName
=mysql
;
1016 if (strcasecmp(tdb
,"mysql") == 0) tdbName
=mysql
;
1017 else if (strcasecmp(tdb
,"postgres")==0) tdbName
=postgres
;
1018 else printError(ErrNotFound
,"Target Database Name is not properly set.Tdb name could be MySql and Postgres.\n");
1019 logFine(Conf::logger
, "TDB Name:%s\n", tdb
);
1021 // The ODBC section in intended to get all the tables from TDB,
1022 // what SQLTables() is doing that.
1024 SQLCHAR outstr
[1024];
1025 SQLSMALLINT outstrlen
;
1030 SQLSMALLINT columns
;
1031 char table
[IDENTIFIER_LENGTH
][IDENTIFIER_LENGTH
];
1033 char buf
[IDENTIFIER_LENGTH
];
1035 SQLINTEGER indicator
[ 5 ];
1036 int colPos
;//Only to bind table name filed.
1038 CacheTableLoader cacheLoader
;
1040 retValue
= SQLAllocHandle (SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &henv
);
1042 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
1045 // We want ODBC 3 support
1046 SQLSetEnvAttr(henv
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
1047 retValue
= SQLAllocHandle (SQL_HANDLE_DBC
, henv
, &hdbc
);
1049 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
1052 retValue
= SQLDriverConnect(hdbc
, NULL
, (SQLCHAR
*)dsn
, SQL_NTS
,
1053 outstr
, sizeof(outstr
), &outstrlen
,
1054 SQL_DRIVER_NOPROMPT
);
1055 if (SQL_SUCCEEDED(retValue
)) {
1056 printDebug(DM_Gateway
, "Connected to target database using dsn = %s\n", dsn
);
1058 printError(ErrSysInit
, "Failed to connect to target database\n");
1061 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmt
);
1063 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
1066 if(tdbName
== mysql
){
1068 // User name is required in upper case for the SQLTables()'s 4th parameter
1069 Util::str_toupper((char*)user
);
1070 retValue
=SQLTables(hstmt
,NULL
, 0, (SQLCHAR
*)user
, SQL_NTS
, NULL
, 0, (SQLCHAR
*)"TABLE", SQL_NTS
);
1072 printError(ErrSysInit
, "Unable to retrieve list of tables\n");
1075 // Binding Column for 3rd parameter to get Table name.
1076 retValue
=SQLBindCol(hstmt
,3, SQL_C_CHAR
,buf
,sizeof(buf
),NULL
);
1078 printError(ErrSysInit
,"Unable to BindCol\n");
1081 // For Postgres DB , SQLTables() retrieves all system and metadata tables,along with User defined table.
1082 // So Here is a another option to fetch the user defined tables only
1083 }else if(tdbName
==postgres
){
1084 SQLCHAR table
[200]="SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema');";
1085 retValue
=SQLPrepare(hstmt
,table
,SQL_NTS
);
1087 printError(ErrSysInit
,"Unable to Prapare the statement\n");
1090 retValue
= SQLBindCol(hstmt
,1,SQL_C_CHAR
,buf
,sizeof(buf
),NULL
);
1092 printError(ErrSysInit
,"Unable to bind the column\n");
1095 retValue
= SQLExecute(hstmt
);
1097 printError(ErrSysInit
,"Unable to execute\n");
1102 while(SQL_SUCCEEDED(retValue
= SQLFetch(hstmt
))){
1103 // copy Buffer value
1104 //strcpy(&table[counter][0],buf);
1105 cacheLoader
.setDsnName(dsnName
);
1106 TableConf::config
.setDsnName(dsnName
);
1107 cacheLoader
.setConnParam(username
, password
);
1108 TableConf::config
.setConnParam(username
, password
);
1109 // Check table is cached or not
1110 mode
= TableConf::config
.getTableMode(buf
);
1111 cacheLoader
.setTable(buf
);
1112 TableConf::config
.setTable(buf
);
1113 isCached
= TableConf::config
.isTableCached(mode
);
1115 printf("Warning: Table '%s' is already cached.\n",buf
);
1117 rv
= cacheLoader
.load(tableDefinition
);
1119 printf("Warning: Table '%s' is present in CSQL locally.\n",buf
);
1121 TableConf::config
.addToCacheTableFile(isDirect
);
1122 printf("Cached Table:%s\n",buf
);
1123 TableConf::config
.init();
1128 // Checking couter value
1130 printf("There is no table present in Target Database.\n");
1131 retValue
=SQLCloseCursor(hstmt
);
1133 printError(ErrSysInit
,"Unable to close the cursor\n");
1136 retValue
=SQLTransact(henv
,hdbc
,SQL_COMMIT
);
1138 printError(ErrSysInit
,"Unable to commit the transaction\n");
1141 retValue
= SQLFreeHandle(SQL_HANDLE_STMT
,hstmt
);
1143 printError(ErrSysInit
,"Unable to free statement handle\n");
1146 retValue
= SQLDisconnect(hdbc
);
1148 printError(ErrSysInit
,"Unable to disconnect from DS handle\n");
1151 retValue
= SQLFreeHandle(SQL_HANDLE_DBC
,hdbc
);
1153 printError(ErrSysInit
,"Unable to free connection handle\n");
1156 retValue
= SQLFreeHandle(SQL_HANDLE_ENV
,henv
);
1158 printError(ErrSysInit
,"Unable to free environment handle\n");
1164 bool CacheTableLoader::resolveForDSN(char *dsn
, TDBInfo
&tdbName
, DbRetVal
&rv
)
1166 char dsnId
[IDENTIFIER_LENGTH
]; dsnId
[0]='\0';
1167 char user
[IDENTIFIER_LENGTH
]; user
[0] = '\0';
1168 char passwd
[IDENTIFIER_LENGTH
]; passwd
[0] = '\0';
1169 char tdb
[IDENTIFIER_LENGTH
]; tdb
[0]='\0';
1172 fp
= fopen(Conf::config
.getDsConfigFile(),"r");
1174 printError(ErrSysInit
, "csqlds.conf file does not exist");
1180 // DSN, user and password value is read here from csql.conf file and
1181 // csqlds.conf file.
1182 // it's true if -d option is specified and the DSN value not matched with
1184 if(strcmp(dsnName
,"")==0) strcpy(dsnName
, Conf::config
.getDSN());
1186 bool isDSNExist
=false;
1188 fscanf(fp
,"%s %s %s %s\n",dsnId
,user
,passwd
,tdb
);
1189 if(strcmp(dsnId
,dsnName
)==0) { // Both the DSN is matched here
1190 if(strcmp(user
,"NULL")!=0 && strcmp(passwd
,"NULL")!=0) {
1191 sprintf(dsn
,"DSN=%s;UID=%s;PWD=%s;",dsnName
,user
,passwd
);
1195 sprintf(dsn
,"DSN=%s;",dsnName
);
1202 printError(ErrNotExists
,"Entries is not present in the csqlds.conf file\n");
1208 if (strcasecmp(tdb
,"mysql") == 0) tdbName
=mysql
;
1209 else if (strcasecmp(tdb
,"postgres")==0) tdbName
=postgres
;
1211 printError(ErrNotFound
," Target Database Name is not properly set.Tdb name could be mysql, postgres\n");
1215 logFine(Conf::logger
, "TDB Name:%s\n", tdb
);
1219 void CacheTableLoader::generateCacheTableStatement(char *stmtBuf
)
1221 if(((strcmp(conditionVal
,"")==0) || (strcmp(conditionVal
,"NULL")==0)) &&
1222 ((strcmp(fieldlistVal
,"")==0) || (strcmp(fieldlistVal
,"NULL")==0)))
1224 sprintf(stmtBuf
, "SELECT * FROM %s;", tableName
);
1226 else if(((strcmp(conditionVal
,"")!=0) || (strcmp(conditionVal
,"NULL")!=0)) &&
1227 ((strcmp(fieldlistVal
,"")==0) || (strcmp(fieldlistVal
,"NULL")==0)))
1229 sprintf(stmtBuf
,"SELECT * FROM %s where %s;",tableName
,conditionVal
);
1232 else if(((strcmp(conditionVal
,"")==0) || (strcmp(conditionVal
,"NULL")==0)) &&
1233 ((strcmp(fieldlistVal
,"")!=0) || (strcmp(fieldlistVal
,"NULL")!=0)))
1235 sprintf(stmtBuf
,"SELECT %s FROM %s;",fieldlistVal
,tableName
);
1238 sprintf(stmtBuf
,"SELECT %s FROM %s where %s;",fieldlistVal
,tableName
,conditionVal
);
1240 logFinest(Conf::logger
, "Cache Table Stmt %s", stmtBuf
);
1243 bool CacheTableLoader::prepareCreateIndexStatement(SQLHSTMT hstmtmeta
,
1244 char *crtIdxStmt
, TDBInfo tdbName
, HashIndexInitInfo
*inf
)
1246 char columnname
[IDENTIFIER_LENGTH
];
1247 char indexname
[IDENTIFIER_LENGTH
];
1248 int retValue
=SQLPrimaryKeys(hstmtmeta
, NULL
, SQL_NTS
, NULL
, SQL_NTS
,
1249 (SQLCHAR
*) tableName
, SQL_NTS
);
1250 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_CHAR
,columnname
, 129,NULL
);
1251 char *ptr
=crtIdxStmt
;
1252 sprintf(ptr
, "CREATE INDEX %s_PRIMARY on %s ( ", tableName
, tableName
);
1254 bool isPriIndex
=false;
1255 char indname
[IDENTIFIER_LENGTH
];
1256 if(SQLFetch( hstmtmeta
) == SQL_SUCCESS
)
1258 Util::str_tolower(columnname
);
1259 inf
->list
.append(columnname
);
1260 sprintf(ptr
, "%s ", columnname
);
1262 while ( SQLFetch( hstmtmeta
) == SQL_SUCCESS
) {
1263 Util::str_tolower(columnname
);
1264 inf
->list
.append(columnname
);
1265 sprintf(ptr
, ", %s ", columnname
);
1268 sprintf(ptr
, ") PRIMARY SIZE 10007;");
1269 inf
->indType
= hashIndex
;
1270 inf
->bucketSize
= 10007;
1271 inf
->isUnique
= true; inf
->isPrimary
= true;
1272 strcpy(inf
->tableName
, tableName
);
1273 strcpy(indexname
,"PRIMARY");
1274 sprintf(indname
, "%s_%s", tableName
, indexname
);
1280 DbRetVal
CacheTableLoader::prepareCreateTableStatement(char *crtTblStmt
, SQLHSTMT hstmt
,
1281 HashIndexInitInfo
*inf
, int totalFields
, TDBInfo tdbName
, bool &isKeyFld
)
1285 UCHAR colName
[IDENTIFIER_LENGTH
];
1286 SWORD colNameMax
=IDENTIFIER_LENGTH
;
1289 SQLULEN colLength
= 0;
1293 bool isNullfld
=false;
1294 bool firstFld
= true;
1297 char *ptr
= crtTblStmt
;
1298 sprintf(ptr
, "CREATE TABLE %s ( ", tableName
);
1300 while (icol
<= totalFields
) {
1301 retValue
= SQLDescribeCol(hstmt
, icol
, colName
, colNameMax
,
1302 &nameLength
, &colType
, &colLength
,
1305 printError(ErrSysInit
, "Unable to retrieve ODBC column info\n");
1308 Util::str_tolower((char*)colName
);
1309 printDebug(DM_Gateway
, "Describe Column %s %d %d %d %d\n",
1310 colName
, colType
, colLength
, scale
, nullable
);
1311 logFinest(Conf::logger
, "Describe Column colName:%s \
1312 colType:%d colLen:%d scale:%d nullable:%d\n",
1313 colName
, colType
, colLength
, scale
, nullable
);
1315 if(strcmp((char*)colName
,fieldName
)== 0)
1320 bool isPriFld
=false;
1322 inf
->list
.resetIter();
1323 while ((name
=inf
->list
.nextFieldName())!=NULL
) {
1324 if(0==strcmp((char*)colName
,name
)) {
1327 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(
1328 AllDataType::convertFromSQLType(
1329 colType
,colLength
,scale
,tdbName
)));
1331 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
)
1333 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
1334 } else { sprintf(ptr
, " NOT NULL"); }
1337 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(
1338 AllDataType::convertFromSQLType(
1339 colType
,colLength
,scale
,tdbName
)));
1341 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
)
1343 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
1344 } else { sprintf(ptr
, " NOT NULL"); }
1347 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1348 // colType,colLength,scale,tdbName),
1349 // colLength +1, NULL, true);
1358 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(
1359 AllDataType::convertFromSQLType(
1360 colType
,colLength
,scale
,tdbName
)));
1362 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
1363 sprintf(ptr
, "(%d)",colLength
+1);
1367 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(
1368 AllDataType::convertFromSQLType(
1369 colType
,colLength
,scale
,tdbName
)));
1371 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
1372 sprintf(ptr
, "(%d)",colLength
+1);
1376 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1377 // colType,colLength,scale,tdbName), colLength+1);
1381 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(
1382 AllDataType::convertFromSQLType(
1383 colType
,colLength
,scale
,tdbName
)));
1385 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
1386 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
1387 } else { sprintf(ptr
, " NOT NULL"); }
1390 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(
1391 AllDataType::convertFromSQLType(
1392 colType
,colLength
,scale
,tdbName
)));
1394 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
1395 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
1396 } else { sprintf(ptr
, " NOT NULL"); }
1399 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1400 // colType,colLength,scale,tdbName),
1401 // colLength+1, NULL, true);
1408 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(
1409 AllDataType::convertFromSQLType(
1410 colType
,colLength
,scale
,tdbName
)));
1412 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
1413 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
1414 } else { sprintf(ptr
, " NOT NULL"); }
1417 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(
1418 AllDataType::convertFromSQLType(
1419 colType
,colLength
, scale
, tdbName
)));
1421 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
1422 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
1423 } else { sprintf(ptr
, " NOT NULL"); }
1426 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(
1427 // colType,colLength,scale, tdbName),
1428 // colLength +1, NULL, true);
1433 //printf("table stmt '%s'\n", crtTblStmt);
1437 void CacheTableLoader::prepareInsertStatement(AbsSqlStatement
*stmt
, List
*fNameList
, char *insStmt
)
1440 char *ptr
= insStmt
;
1441 sprintf(ptr
,"INSERT INTO %s VALUES(", tableName
);
1443 bool firstFld
= true;
1444 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
1445 *fNameList
= sqlStmt
->getFieldNameList(tableName
, rv
);
1446 int noOfFields
= fNameList
->size();
1447 while (noOfFields
--) {
1450 sprintf(ptr
,"?", tableName
);