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>
27 DbRetVal
CacheTableLoader::checkSecondTimeSqlPrimaryKeys(SQLHSTMT hstmtmeta
,char *tableName
,char *ptr
,HashIndexInitInfo
*inf
,bool &isPriIndex
)
30 char columnname
[IDENTIFIER_LENGTH
];
31 retValue
=SQLPrimaryKeys(hstmtmeta
, NULL
, SQL_NTS
, NULL
, SQL_NTS
, (SQLCHAR
*) tableName
, SQL_NTS
);
32 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_CHAR
,columnname
, 129,NULL
);
33 char indname
[IDENTIFIER_LENGTH
];
34 if(SQLFetch( hstmtmeta
) == SQL_SUCCESS
)
36 Util::str_tolower(columnname
);
37 inf
->list
.append(columnname
);
38 sprintf(ptr
, "%s ", columnname
);
40 while ( SQLFetch( hstmtmeta
) == SQL_SUCCESS
) {
41 Util::str_tolower(columnname
);
42 inf
->list
.append(columnname
);
43 sprintf(ptr
, ", %s ", columnname
);
46 sprintf(ptr
, ") PRIMARY SIZE 10007;");
52 DbRetVal
CacheTableLoader::load(bool tabDefinition
)
54 AbsSqlConnection
*conn
= SqlFactory::createConnection(CSqlLog
);
55 DbRetVal rv
= conn
->connect(userName
, password
);
56 if (rv
!= OK
) { delete conn
; return ErrSysInit
; }
57 AbsSqlStatement
*stmt
= SqlFactory::createStatement(CSqlLog
);
58 stmt
->setConnection(conn
);
59 SqlLogConnection
*logConn
= (SqlLogConnection
*) conn
;
60 logConn
->setNoMsgLog(true);
61 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
62 DatabaseManager
*dbMgr
= con
->getConnObject().getDatabaseManager();
63 if (tabDefinition
== false) {
64 Table
*tbl
= dbMgr
->openTable(tableName
);
71 if (tbl
->numTuples()) {
72 printError(ErrNotEmpty
, "The table '\%s\' is not empty", tableName
);
73 dbMgr
->closeTable(tbl
);
79 dbMgr
->closeTable(tbl
);
82 rv
= load(conn
, stmt
, tabDefinition
);
91 DbRetVal
CacheTableLoader::load(AbsSqlConnection
*conn
, AbsSqlStatement
*stmt
, bool tabDefinition
)
96 fp
= fopen(Conf :: config
.getDsConfigFile(),"r");
98 printError(ErrSysInit
, "csqlds.conf file does not exist");
101 char dsnId
[IDENTIFIER_LENGTH
]; dsnId
[0]='\0';
102 char user
[IDENTIFIER_LENGTH
]; user
[0] = '\0';
103 char passwd
[IDENTIFIER_LENGTH
]; passwd
[0] = '\0';
104 char tdb
[IDENTIFIER_LENGTH
]; tdb
[0]='\0';
107 // DSN, user and password value is read here from csql.conf fiel and csqlds.conf file.
109 if(strcmp(dsnName
,"")==0) { // it's true if -d option is specified and the DSN value not matched with csql.conf's DSN.
110 strcpy(dsnName
, Conf::config
.getDSN());
112 bool isDSNExist
=false;
114 fscanf(fp
,"%s %s %s %s\n",dsnId
,user
,passwd
,tdb
);
115 if(strcmp(dsnId
,dsnName
)==0) { // Both the DSN is matched here
116 if( strcmp(user
,"NULL")!=0 && strcmp(passwd
,"NULL")!=0) {
117 sprintf(dsn
,"DSN=%s;UID=%s;PWD=%s;",dsnName
,user
,passwd
);
121 sprintf(dsn
,"DSN=%s;",dsnName
);
128 printError(ErrNotExists
,"Entries is not present in the csqlds.conf file\n");
133 TDBInfo tdbName
=mysql
;
134 if (strcasecmp(tdb
,"postgres")==0) tdbName
=postgres
;
135 else if (strcasecmp(tdb
,"postgres")==0) tdbName
=mysql
;
136 else printError(ErrNotFound
,"Target Database Name is not properly set.Tdb name could be mysql, postgres, sybase, db2, oracle\n");
138 logFine(Conf::logger
, "TDB Name:%s\n", tdb
);
140 //DatabaseManager *dbMgr = (DatabaseManager *) conn->getDatabaseManager();
142 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
143 DatabaseManager
*dbMgr
= con
->getConnObject().getDatabaseManager();
145 SQLCHAR outstr
[1024];
146 SQLSMALLINT outstrlen
;
151 retValue
= SQLAllocHandle (SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &henv
);
153 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
156 SQLSetEnvAttr(henv
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
157 retValue
= SQLAllocHandle (SQL_HANDLE_DBC
, henv
, &hdbc
);
159 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
162 retValue
= SQLDriverConnect(hdbc
, NULL
, (SQLCHAR
*)dsn
, SQL_NTS
,
163 outstr
, sizeof(outstr
), &outstrlen
,
164 SQL_DRIVER_NOPROMPT
);
165 if (SQL_SUCCEEDED(retValue
)) {
166 printDebug(DM_Gateway
, "Connected to target database using dsn = %s\n", dsn
);
168 printError(ErrSysInit
, "Failed to connect to target database\n");
172 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmt
);
174 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
179 if(((strcmp(conditionVal
,"")==0) || (strcmp(conditionVal
,"NULL")==0)) && ((strcmp(fieldlistVal
,"")==0) || (strcmp(fieldlistVal
,"NULL")==0)))
181 sprintf(stmtBuf
, "SELECT * FROM %s;", tableName
);
183 else if(((strcmp(conditionVal
,"")!=0) || (strcmp(conditionVal
,"NULL")!=0)) && ((strcmp(fieldlistVal
,"")==0) || (strcmp(fieldlistVal
,"NULL")==0)))
185 sprintf(stmtBuf
,"SELECT * FROM %s where %s;",tableName
,conditionVal
);
188 else if(((strcmp(conditionVal
,"")==0) || (strcmp(conditionVal
,"NULL")==0)) && ((strcmp(fieldlistVal
,"")!=0) || (strcmp(fieldlistVal
,"NULL")!=0)))
190 sprintf(stmtBuf
,"SELECT %s FROM %s;",fieldlistVal
,tableName
);
193 sprintf(stmtBuf
,"SELECT %s FROM %s where %s;",fieldlistVal
,tableName
,conditionVal
);
195 retValue
= SQLPrepare (hstmt
, (unsigned char *) stmtBuf
, SQL_NTS
);
197 printError(ErrSysInit
, "Unable to Prepare ODBC statement \n");
200 logFinest(Conf::logger
, "Cache Table Stmt %s", stmtBuf
);
203 retValue
= SQLNumResultCols (hstmt
, &totalFields
);
205 printError(ErrSysInit
, "Unable to retrieve ODBC total columns\n");
209 UCHAR colName
[IDENTIFIER_LENGTH
];
213 SQLULEN colLength
= 0;
218 colNameMax
= IDENTIFIER_LENGTH
;
219 char columnname
[IDENTIFIER_LENGTH
];
220 char indexname
[IDENTIFIER_LENGTH
];
221 short type
; short unique
;
223 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmtmeta
);
226 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
230 retValue
=SQLPrimaryKeys(hstmtmeta
, NULL
, SQL_NTS
, NULL
, SQL_NTS
, (SQLCHAR
*) tableName
, SQL_NTS
);
231 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_CHAR
,columnname
, 129,NULL
);
232 HashIndexInitInfo
*inf
= new HashIndexInitInfo();
233 char crtIdxStmt
[1024];
235 char *ptr
=crtIdxStmt
;
236 sprintf(ptr
, "CREATE INDEX %s_PRIMARY on %s ( ", tableName
, tableName
);
238 bool isPriIndex
=false;
239 char indname
[IDENTIFIER_LENGTH
];
240 if(SQLFetch( hstmtmeta
) == SQL_SUCCESS
)
242 Util::str_tolower(columnname
);
243 inf
->list
.append(columnname
);
244 sprintf(ptr
, "%s ", columnname
);
246 while ( SQLFetch( hstmtmeta
) == SQL_SUCCESS
) {
247 Util::str_tolower(columnname
);
248 inf
->list
.append(columnname
);
249 sprintf(ptr
, ", %s ", columnname
);
252 sprintf(ptr
, ") PRIMARY SIZE 10007;");
253 inf
->indType
= hashIndex
;
254 inf
->bucketSize
= 10007;
255 inf
->isUnique
= true; inf
->isPrimary
= true;
256 strcpy(inf
->tableName
, tableName
);
257 strcpy(indexname
,"PRIMARY");
258 sprintf(indname
, "%s_%s", tableName
, indexname
);
261 bool iskeyfieldExist
=false;
262 bool isPKFieldSpecified
= false;
263 if((strcmp(fieldName
,"")!=0) && (strcmp(fieldName
,"NULL")!=0) )
265 isPKFieldSpecified
= true;
267 if ( isPriIndex
&& ( strcmp(fieldlistVal
,"")!=0 ) &&
268 ( strcmp(fieldlistVal
,"NULL") != 0 )) {
269 inf
->list
.resetIter();
270 while ( (name
=inf
->list
.nextFieldName()) != NULL
) {
271 iskeyfieldExist
= TableConf::config
.isFieldExist(name
);
272 if(!iskeyfieldExist
) { break; }
274 } else if (isPriIndex
) { iskeyfieldExist
= true; }
275 if ( isPKFieldSpecified
&& !(TableConf::config
.isFieldExist(fieldName
)) )
277 if ( Conf::config
.useTwoWayCache() &&
278 (strcmp(fieldlistVal
,"")!=0) &&
279 (strcmp(fieldlistVal
,"NULL")!=0))
281 printError(ErrSysInit
, "Bidirectional caching should have primary key in %s \n", tableName
);
282 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
283 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
284 SQLDisconnect (hdbc
);
285 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
286 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
291 if (!iskeyfieldExist
&& !isPKFieldSpecified
)
293 if(Conf::config
.useTwoWayCache())
295 printError(ErrSysInit
, "Bidirectional caching fail for no primary key in %s \n", tableName
);
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
);
307 else if (Conf::config.useTwoWayCache() && !iskeyfieldExist) {
308 printError(ErrSysInit, "Bidirectonal caching fail for no primary key in %s \n", tableName);
309 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
310 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
311 SQLDisconnect (hdbc);
312 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
313 SQLFreeHandle (SQL_HANDLE_ENV, henv);
318 bool isNullfld
=false;
319 bool firstFld
= true;
320 char crtTblStmt
[1024];
322 sprintf(ptr
, "CREATE TABLE %s ( ", tableName
);
324 while (icol
<= totalFields
) {
325 retValue
= SQLDescribeCol(hstmt
, icol
, colName
, colNameMax
,
326 &nameLength
, &colType
, &colLength
,
329 printError(ErrSysInit
, "Unable to retrieve ODBC column info\n");
330 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
331 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
332 SQLDisconnect (hdbc
);
333 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
334 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
338 Util::str_tolower((char*)colName
);
339 printDebug(DM_Gateway
, "Describe Column %s %d %d %d %d \n", colName
, colType
, colLength
, scale
, nullable
);
340 logFinest(Conf::logger
, "Describe Column colName:%s colType:%d colLen:%d scale:%d nullable:%d\n", colName
, colType
, colLength
, scale
, nullable
);
343 if(strcmp((char*)colName
,fieldName
)== 0)
350 inf
->list
.resetIter();
351 while ((name
=inf
->list
.nextFieldName())!=NULL
) {
352 if(0==strcmp((char*)colName
,name
)) {
355 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
357 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
)
359 sprintf(ptr
, "(%d) NOT NULL",colLength
);
360 } else { sprintf(ptr
, " NOT NULL"); }
363 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
365 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
)
367 sprintf(ptr
, "(%d) NOT NULL",colLength
);
368 } else { sprintf(ptr
, " NOT NULL"); }
371 tabDef
.addField((char*)colName
, AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
), colLength
+1, NULL
, true);
380 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
382 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
383 sprintf(ptr
, "(%d)",colLength
);
387 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
389 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
390 sprintf(ptr
, "(%d)",colLength
);
394 tabDef
.addField((char*)colName
, AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
), colLength
+1);
398 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
400 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
401 sprintf(ptr
, "(%d) NOT NULL",colLength
);
402 } else { sprintf(ptr
, " NOT NULL",colLength
); }
405 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
407 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
408 sprintf(ptr
, "(%d) NOT NULL",colLength
);
409 } else { sprintf(ptr
, " NOT NULL",colLength
); }
412 tabDef
.addField((char*)colName
, AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
), colLength
+1, NULL
, true);
419 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
421 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
422 sprintf(ptr
, "(%d) NOT NULL",colLength
);
423 } else { sprintf(ptr
, " NOT NULL",colLength
); }
426 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
, scale
, tdbName
)));
428 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
429 sprintf(ptr
, "(%d) NOT NULL",colLength
);
430 } else { sprintf(ptr
, " NOT NULL",colLength
); }
433 tabDef
.addField((char*)colName
, AllDataType::convertFromSQLType(colType
,colLength
,scale
, tdbName
), colLength
+1, NULL
, true);
438 //printf("table stmt '%s'\n", crtTblStmt);
439 if(((strcmp(fieldName
,"")!=0) && (strcmp(fieldName
,"NULL")!=0))
441 printError(ErrSysInit
, "Unable to cache Table for %s with key field %s\n", tableName
,fieldName
);
442 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
443 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
444 SQLDisconnect (hdbc
);
445 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
446 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
450 rv
= stmt
->prepare(crtTblStmt
);
452 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
453 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
454 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
455 SQLDisconnect (hdbc
);
456 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
457 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
462 rv
= stmt
->execute(rows
);
464 printError(ErrSysInit
, "Unable to execute create table stmt\n");
465 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
466 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
467 SQLDisconnect (hdbc
);
468 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
469 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
473 logFinest(Conf::logger
, "Cache Table: Table Created :%s", crtTblStmt
);
476 //Create primary key index if present
477 if (isPriIndex
&& ( iskeyfieldExist
||
478 (strcmp(fieldlistVal
,"")==0 || strcmp(fieldlistVal
,"NULL")== 0))) {
479 rv
= stmt
->prepare(crtIdxStmt
);
481 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
482 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
483 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
484 SQLDisconnect (hdbc
);
485 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
486 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
491 rv
= stmt
->execute(rows
);
493 printError(ErrSysInit
, "Unable to execute create table stmt\n");
494 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
495 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
496 SQLDisconnect (hdbc
);
497 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
498 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
502 //printf("Primary index created from create Index stmt\n");
504 retValue
= SQLCloseCursor(hstmtmeta
);
505 rv
= createIndex(hstmtmeta
, tableName
, inf
, stmt
,isPKFieldSpecified
);
507 dbMgr
->dropTable(tableName
);
508 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
509 SQLDisconnect (hdbc
);
510 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
511 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
517 // Now load the table with records
520 sprintf(ptr
,"INSERT INTO %s VALUES(", tableName
);
522 bool firstFld
= true;
523 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
->getInnerStatement();
524 sqlStmt
->setConnection(con
);
525 List fNameList
= sqlStmt
->getFieldNameList(tableName
);
526 int noOfFields
= fNameList
.size();
528 while (noOfFields
--) {
531 sprintf(ptr
,"?", tableName
);
540 //printf("insert stmt: '%s'\n", insStmt);
542 rv
= stmt
->prepare(insStmt
);
544 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
545 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
546 SQLDisconnect (hdbc
);
547 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
548 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
551 ListIterator fNameIter
= fNameList
.getIterator();
552 FieldInfo
*info
= new FieldInfo();
553 int fcount
=1; void *valBuf
;
554 Identifier
*elem
= NULL
;
555 void *tembuf
=NULL
;//For postgre BigInt type
558 SQLINTEGER len
[IDENTIFIER_LENGTH
];
559 while (fNameIter
.hasElement()) {
560 elem
= (Identifier
*) fNameIter
.nextElement();
561 sqlStmt
->getFieldInfo(tableName
, (const char*)elem
->name
, info
);
562 if (info
->type
== typeString
) {
563 valBuf
= AllDataType::alloc(info
->type
, info
->length
+1);
564 os::memset(valBuf
,0,info
->length
+1);
566 valBuf
= AllDataType::alloc(info
->type
);
567 os::memset(valBuf
,0,AllDataType::size(info
->type
));
572 bBuf
= new BindBuffer();
574 bBuf
->type
= typeDate
;
575 bBuf
->length
= sizeof(DATE_STRUCT
);
576 bBuf
->targetdb
= malloc(bBuf
->length
);
577 memset(bBuf
->targetdb
, 0, bBuf
->length
);
578 valBuf
= bBuf
->targetdb
;
579 valBufList
.append(bBuf
);
582 bBuf
= new BindBuffer();
584 bBuf
->type
= typeTime
;
585 bBuf
->length
= sizeof(TIME_STRUCT
);
586 bBuf
->targetdb
= malloc(bBuf
->length
);
587 memset(bBuf
->targetdb
, 0, bBuf
->length
);
588 valBuf
= bBuf
->targetdb
;
589 valBufList
.append(bBuf
);
592 bBuf
= new BindBuffer();
594 bBuf
->type
= typeTimeStamp
;
595 bBuf
->length
= sizeof(TIMESTAMP_STRUCT
);
596 bBuf
->targetdb
= malloc(bBuf
->length
);
597 memset(bBuf
->targetdb
, 0, bBuf
->length
);
598 valBuf
= bBuf
->targetdb
;
599 valBufList
.append(bBuf
);
603 if( tdbName
== postgres
)
605 bBuf
= new BindBuffer();
606 bBuf
->type
= typeLongLong
;
609 bBuf
->targetdb
= AllDataType::alloc(typeString
,bBuf
->length
);
610 memset(bBuf
->targetdb
, 0, bBuf
->length
);
611 valBuf
= bBuf
->targetdb
;
612 valBufList
.append(bBuf
);
617 bBuf
= new BindBuffer();
618 bBuf
->type
= info
->type
;
620 valBufList
.append(bBuf
);
621 bBuf
->length
= info
->length
;
626 if( tdbName
!= mysql
)
628 bBuf
= new BindBuffer();
629 bBuf
->type
= typeString
;
631 bBuf
->length
= info
->length
+1;
632 valBufList
.append(bBuf
);
636 bBuf
= new BindBuffer();
637 bBuf
->type
= info
->type
;
639 valBufList
.append(bBuf
);
640 bBuf
->length
= info
->length
;
643 //os::memset(valBuf,0,bBuf->length);
644 retValue
= SQLBindCol (hstmt
, fcount
, AllDataType::convertToSQL_C_Type(info
->type
,tdbName
), valBuf
, bBuf
->length
, &len
[fcount
]);
647 printError(ErrSysInit
, "Unable to bind columns in ODBC\n");
648 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
649 SQLDisconnect (hdbc
);
650 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
651 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
657 while (fNameIter
.hasElement())
658 delete ((FieldName
*) fNameIter
.nextElement());
661 retValue
= SQLExecute (hstmt
);
663 printError(ErrSysInit
, "Unable to execute ODBC statement\n");
664 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
665 SQLDisconnect (hdbc
);
666 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
667 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
671 int countForCommit
= 0;
673 //TODO: if SQLFetch return other than record not found error
674 //it should drop the table
675 retValue
= SQLFetch (hstmt
);
677 ListIterator bindIter
= valBufList
.getIterator();
679 while (bindIter
.hasElement()) {
680 bBuf
= (BindBuffer
*) bindIter
.nextElement();
681 switch (bBuf
->type
) {
684 if( tdbName
!= mysql
)
686 Util::trimRight((char*)bBuf
->csql
);
692 Date
*dtCSQL
= (Date
*) bBuf
->csql
;
693 DATE_STRUCT
*dtTarget
= (DATE_STRUCT
*) bBuf
->targetdb
;
694 dtCSQL
->set(dtTarget
->year
,dtTarget
->month
,dtTarget
->day
);
699 Time
*dtCSQL
= (Time
*) bBuf
->csql
;
700 TIME_STRUCT
*dtTarget
= (TIME_STRUCT
*) bBuf
->targetdb
;
701 dtCSQL
->set(dtTarget
->hour
,dtTarget
->minute
,dtTarget
->second
);
706 TimeStamp
*dtCSQL
= (TimeStamp
*) bBuf
->csql
;
707 TIMESTAMP_STRUCT
*dtTarget
= (TIMESTAMP_STRUCT
*) bBuf
->targetdb
;
708 dtCSQL
->setDate(dtTarget
->year
,dtTarget
->month
,dtTarget
->day
);
709 dtCSQL
->setTime(dtTarget
->hour
,dtTarget
->minute
,dtTarget
->second
, dtTarget
->fraction
);
714 if ( tdbName
== postgres
) {
715 sscanf((const char*)bBuf
->targetdb
,"%lld",(long long*) bBuf
->csql
);
720 setParamValues(stmt
, ++fldpos
, bBuf
->type
, bBuf
->length
, (char *) bBuf
->csql
);
723 //table->resetNullinfo();
724 while(fldpos
< fcount
-1) {
725 if(len
[++fldpos
] == SQL_NULL_DATA
) {
726 stmt
->setNull(fldpos
);
730 rv
= stmt
->execute(rows
);
732 printError(ErrSysInit
, "Unable to cache record in CSQL.\n");
733 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
734 SQLDisconnect (hdbc
);
735 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
736 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
740 if (countForCommit
== 1000) {
746 //TODO::leak:: valBufList and its targetdb buffer
747 ListIterator it
= valBufList
.getIterator();
748 while(it
.hasElement()) {
749 BindBuffer
*bb
= (BindBuffer
*) it
.nextElement();
750 if (bb
->csql
) { free(bb
->csql
); bb
->csql
= NULL
; }
751 if (bb
->targetdb
) { free(bb
->targetdb
); bb
->targetdb
= NULL
; }
752 delete bb
; bb
= NULL
;
755 SQLCloseCursor (hstmt
);
756 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
757 SQLDisconnect (hdbc
);
758 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
759 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
763 DbRetVal
CacheTableLoader::reload()
766 DbRetVal rv
= unload(false);
767 if (rv
!= OK
) return rv
;
768 //get table cache senarios
769 fp
= fopen(Conf::config
.getTableConfigFile(),"r");
771 printError(ErrSysInit
, "cachetable.conf file does not exist");
776 char tablename
[IDENTIFIER_LENGTH
];
777 char fieldname
[IDENTIFIER_LENGTH
];
778 char field
[IDENTIFIER_LENGTH
];
779 char condition
[IDENTIFIER_LENGTH
];
780 char dsnname
[IDENTIFIER_LENGTH
];
783 fscanf(fp
, "%d %s %s %s %s %s\n", &mode
, tablename
,fieldname
,condition
,field
,dsnname
);
784 if(strcmp(tablename
,tableName
)==0) break;
787 setCondition(TableConf::config
.getRealConditionFromFile(condition
));
788 setFieldName(fieldname
);
789 setFieldListVal(field
);
795 DbRetVal
CacheTableLoader::unload(bool tabDefinition
)
797 AbsSqlConnection
*conn
= SqlFactory::createConnection(CSqlLog
);
798 DbRetVal rv
= conn
->connect(userName
, password
);
799 if (rv
!= OK
) return ErrSysInit
;
800 AbsSqlStatement
*stmt
= SqlFactory::createStatement(CSqlLog
);
801 stmt
->setConnection(conn
);
802 SqlLogConnection
*logConn
= (SqlLogConnection
*) conn
;
803 logConn
->setNoMsgLog(true);
804 char statement
[1024];
805 if (TableConf::config
.isTableCached(tableName
) != OK
) {
806 printError(ErrNotCached
, "The table \'%s\' is not cached", tableName
);
812 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
813 DatabaseManager
*dbMgr
= (DatabaseManager
*) con
->getConnObject().getDatabaseManager();
816 delete stmt
; delete conn
;
817 printError(ErrSysInit
, "Authentication failed\n");
822 sprintf(statement
, "DELETE FROM %s;", tableName
);
823 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
824 sqlStmt
->setLoading(true);
825 rv
= stmt
->prepare(statement
);
828 delete stmt
; delete conn
;
833 rv
= stmt
->execute(rows
);
836 delete stmt
; delete conn
;
843 rv
= TableConf::config
.removeFromCacheTableFile();
845 conn
->disconnect(); delete stmt
; delete conn
;
848 sprintf(statement
, "DROP TABLE %s;", tableName
);
849 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
850 sqlStmt
->setLoading(true);
851 rv
= stmt
->prepare(statement
);
853 //TableConf::config.addToCacheTableFile(false);
855 delete stmt
; delete conn
;
859 rv
= stmt
->execute(rows
);
861 //TableConf::config.addToCacheTableFile(false);
862 conn
->disconnect(); delete stmt
; delete conn
;
867 delete stmt
; delete conn
;
868 logFine(Conf::logger
, "Unloaded Cached Table: %s", tableName
);
872 DbRetVal
CacheTableLoader::refresh()
877 DbRetVal
CacheTableLoader::recoverAllCachedTables()
881 DbRetVal rv
= conn
.open(userName
, password
);
882 if(rv
!=OK
) return ErrSysInit
;
884 //Note: if connection is not open, configuration veriables may be incorrect
886 fp
= fopen(Conf::config
.getTableConfigFile(),"r");
888 printError(ErrSysInit
, "cachetable.conf file does not exist");
893 //TODO::take exclusive lock on database
894 char tablename
[IDENTIFIER_LENGTH
];
895 char fieldname
[IDENTIFIER_LENGTH
];
896 char condition
[IDENTIFIER_LENGTH
];
897 char field
[IDENTIFIER_LENGTH
];
898 char dsnname
[IDENTIFIER_LENGTH
];
905 scanItems
= fscanf(fp
, "%d %s %s %s %s %s\n", &mode
, tablename
,fieldname
,condition
,field
,dsnname
);
906 if (scanItems
!= 6) {
908 printf("There is no table to be cached.\n");
911 //if (mode ==2 ) //just replicated table and not cached
913 printDebug(DM_Gateway
, "Recovering Table from target db: %s\n", tablename
);
914 setCondition(TableConf::config
.getRealConditionFromFile(condition
));
915 if( (strcmp(Conf::config
.getDSN(),dsnname
)!=0) ){
918 setFieldName(fieldname
);
919 setFieldListVal(field
);
920 printf("Recovering table %s %s %s\n", tablename
,condition
,field
);
922 if (rv
!= OK
) { fclose(fp
); return rv
; }
924 setDsnName(Conf::config
.getDSN());
926 setFieldName(fieldname
);
927 setFieldListVal(field
);
928 printf("Recovering table %s %s %s\n", tablename
,condition
,field
);
930 if (rv
!= OK
) { fclose(fp
); return rv
; }
932 logFine(Conf::logger
, "Recovering Table from target db:%s", tablename
);
938 void CacheTableLoader::setParamValues(AbsSqlStatement
*stmt
, int parampos
, DataType type
, int length
, char *value
)
943 stmt
->setIntParam(parampos
, *(int*)value
);
946 stmt
->setLongParam(parampos
, *(long*)value
);
949 stmt
->setLongLongParam(parampos
, *(long long*)value
);
952 stmt
->setShortParam(parampos
, *(short*)value
);
955 stmt
->setByteIntParam(parampos
, *(char*)value
);
958 stmt
->setDoubleParam(parampos
, *(double*)value
);
961 stmt
->setFloatParam(parampos
, *(float*)value
);
964 stmt
->setDateParam(parampos
, *(Date
*)value
);
967 stmt
->setTimeParam(parampos
, *(Time
*)value
);
970 stmt
->setTimeStampParam(parampos
, *(TimeStamp
*)value
);
974 char *d
=(char*)value
;
976 stmt
->setStringParam(parampos
, (char*)value
);
980 stmt
->setBinaryParam(parampos
, (char *) value
, length
);
986 DbRetVal
CacheTableLoader::createIndex(SQLHSTMT hstmtmeta
, char *tableName
, HashIndexInitInfo
*inf
,AbsSqlStatement
*stmt
,bool isPKFieldSpecified
)
988 bool isKeyFld
= false;
990 char columnname
[IDENTIFIER_LENGTH
];
991 char indexname
[IDENTIFIER_LENGTH
];
996 retValue
= SQLStatistics(hstmtmeta
, NULL
, 0, NULL
, SQL_NTS
,
997 (SQLCHAR
*) tableName
, SQL_NTS
, SQL_INDEX_ALL
, SQL_QUICK
);
998 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_SHORT
,
1000 retValue
= SQLBindCol(hstmtmeta
, 6, SQL_C_CHAR
,
1001 indexname
, 129, NULL
);
1002 retValue
= SQLBindCol(hstmtmeta
, 7, SQL_C_SHORT
,
1004 retValue
= SQLBindCol(hstmtmeta
, 9, SQL_C_CHAR
,
1005 columnname
, 129,NULL
);
1007 bool isSecondTime
= false;
1008 CacheIndexInfo
*info
=NULL
;
1009 while ((retValue
= SQLFetch(hstmtmeta
)) == SQL_SUCCESS
) {
1010 //if (type != SQL_TABLE_STAT)
1012 printDebug(DM_Gateway
, "Column: %-18s Index Name: %-18s unique:%hd type:%hd\n", columnname
, indexname
, unique
, type
);
1018 bool isFldAdd
= false;
1019 ListIterator iter
= indexList
.getIterator();
1021 while (iter
.hasElement())
1023 CacheIndexInfo
*indInfo
= (CacheIndexInfo
*)iter
.nextElement();
1024 if(0 == strcmp( indInfo
->indexName
, indexname
))
1026 indInfo
->fieldList
.append(columnname
);
1031 info
= new CacheIndexInfo();
1032 info
->fieldList
.append(columnname
);
1033 strcpy(info
->indexName
, indexname
);
1034 indexList
.append(info
);
1035 isSecondTime
= true;
1040 ListIterator iter
= indexList
.getIterator();
1042 int noOfPkfield
= inf
->list
.size();
1045 while (iter
.hasElement())
1048 bool isFieldExistInCondition
= false;
1049 bool isPrimary
=false;
1050 CacheIndexInfo
*indInfo
= (CacheIndexInfo
*)iter
.nextElement();
1051 int noOfFld
= indInfo
->fieldList
.size();
1052 indInfo
->fieldList
.resetIter();
1053 while ((fName
= indInfo
->fieldList
.nextFieldName())!=NULL
)
1055 if(( 1 == noOfFld
) && (0 == strcmp(fName
,fieldName
))) { isKeyFld
=true; }
1056 inf
->list
.resetIter();
1057 while ((name
=inf
->list
.nextFieldName())!=NULL
)
1059 if(0==strcmp(fName
,name
)) { isPrimary
= true; break; }
1062 if (!TableConf::config
.isFieldExist(fName
) && ( (strcmp(fieldlistVal
,"")!=0) && (strcmp(fieldlistVal
,"NULL")!=0) ))
1064 isFieldExistInCondition
=true;
1067 sprintf(cptr
, "%s ,",fName
);
1068 cptr
+= strlen(cptr
);
1071 if(isFieldExistInCondition
) continue;
1075 if (isPrimary
) { continue; }
1076 char crtIdxStmt
[1024];
1078 sprintf(indname
, "%s_%s", tableName
, indInfo
->indexName
);
1079 sprintf(crtIdxStmt
, "CREATE INDEX %s on %s(%s) HASH SIZE 10007;", indname
, tableName
, columnname
);
1080 //printf("create index stmt \n'%s'\n", crtIdxStmt);
1081 rv
= stmt
->prepare(crtIdxStmt
);
1083 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
1087 rv
= stmt
->execute(rows
);
1089 printError(ErrSysInit
, "Unable to execute create table stmt\n");
1093 }// while meta data fetch for index creation
1095 SQLCloseCursor (hstmtmeta
);
1096 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1097 if( !isKeyFld
&& isPKFieldSpecified
) {
1099 char frcIndStmt
[1024];
1101 sprintf(indname
, "%s_%s", tableName
, "keyInd");
1102 sprintf(frcIndStmt
, "CREATE INDEX %s on %s(%s) HASH;", indname
, tableName
, fieldName
);
1103 rv
= stmt
->prepare(frcIndStmt
);
1105 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
1109 rv
= stmt
->execute(rows
);
1111 printError(ErrSysInit
, "Unable to execute create table stmt\n");
1115 printError(ErrSysInit
, "Unable to cache Table for %s with key field %s\n", tableName
,fieldName
);