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 logConn
->setNoOfflineLog(true);
62 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
63 DatabaseManager
*dbMgr
= con
->getConnObject().getDatabaseManager();
64 if (tabDefinition
== false) {
65 Table
*tbl
= dbMgr
->openTable(tableName
);
72 if (tbl
->numTuples()) {
73 printError(ErrNotEmpty
, "The table '\%s\' is not empty", tableName
);
74 dbMgr
->closeTable(tbl
);
80 dbMgr
->closeTable(tbl
);
83 rv
= load(conn
, stmt
, tabDefinition
);
92 DbRetVal
CacheTableLoader::load(AbsSqlConnection
*conn
, AbsSqlStatement
*stmt
, bool tabDefinition
)
97 fp
= fopen(Conf :: config
.getDsConfigFile(),"r");
99 printError(ErrSysInit
, "csqlds.conf file does not exist");
102 char dsnId
[IDENTIFIER_LENGTH
]; dsnId
[0]='\0';
103 char user
[IDENTIFIER_LENGTH
]; user
[0] = '\0';
104 char passwd
[IDENTIFIER_LENGTH
]; passwd
[0] = '\0';
105 char tdb
[IDENTIFIER_LENGTH
]; tdb
[0]='\0';
108 // DSN, user and password value is read here from csql.conf fiel and csqlds.conf file.
110 if(strcmp(dsnName
,"")==0) { // it's true if -d option is specified and the DSN value not matched with csql.conf's DSN.
111 strcpy(dsnName
, Conf::config
.getDSN());
113 bool isDSNExist
=false;
115 fscanf(fp
,"%s %s %s %s\n",dsnId
,user
,passwd
,tdb
);
116 if(strcmp(dsnId
,dsnName
)==0) { // Both the DSN is matched here
117 if( strcmp(user
,"NULL")!=0 && strcmp(passwd
,"NULL")!=0) {
118 sprintf(dsn
,"DSN=%s;UID=%s;PWD=%s;",dsnName
,user
,passwd
);
122 sprintf(dsn
,"DSN=%s;",dsnName
);
129 printError(ErrNotExists
,"Entries is not present in the csqlds.conf file\n");
134 TDBInfo tdbName
=mysql
;
135 if (strcasecmp(tdb
,"postgres")==0) tdbName
=postgres
;
136 else if (strcasecmp(tdb
,"mysql")==0) tdbName
=mysql
;
137 else printError(ErrNotFound
,"Target Database Name is not properly set.Tdb name could be mysql, postgres, sybase, db2, oracle\n");
139 logFine(Conf::logger
, "TDB Name:%s\n", tdb
);
141 //DatabaseManager *dbMgr = (DatabaseManager *) conn->getDatabaseManager();
143 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
144 DatabaseManager
*dbMgr
= con
->getConnObject().getDatabaseManager();
146 SQLCHAR outstr
[1024];
147 SQLSMALLINT outstrlen
;
152 retValue
= SQLAllocHandle (SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &henv
);
154 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
157 SQLSetEnvAttr(henv
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
158 retValue
= SQLAllocHandle (SQL_HANDLE_DBC
, henv
, &hdbc
);
160 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
163 retValue
= SQLDriverConnect(hdbc
, NULL
, (SQLCHAR
*)dsn
, SQL_NTS
,
164 outstr
, sizeof(outstr
), &outstrlen
,
165 SQL_DRIVER_NOPROMPT
);
166 if (SQL_SUCCEEDED(retValue
)) {
167 printDebug(DM_Gateway
, "Connected to target database using dsn = %s\n", dsn
);
169 printError(ErrSysInit
, "Failed to connect to target database\n");
173 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmt
);
175 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
180 if(((strcmp(conditionVal
,"")==0) || (strcmp(conditionVal
,"NULL")==0)) && ((strcmp(fieldlistVal
,"")==0) || (strcmp(fieldlistVal
,"NULL")==0)))
182 sprintf(stmtBuf
, "SELECT * FROM %s;", tableName
);
184 else if(((strcmp(conditionVal
,"")!=0) || (strcmp(conditionVal
,"NULL")!=0)) && ((strcmp(fieldlistVal
,"")==0) || (strcmp(fieldlistVal
,"NULL")==0)))
186 sprintf(stmtBuf
,"SELECT * FROM %s where %s;",tableName
,conditionVal
);
189 else if(((strcmp(conditionVal
,"")==0) || (strcmp(conditionVal
,"NULL")==0)) && ((strcmp(fieldlistVal
,"")!=0) || (strcmp(fieldlistVal
,"NULL")!=0)))
191 sprintf(stmtBuf
,"SELECT %s FROM %s;",fieldlistVal
,tableName
);
194 sprintf(stmtBuf
,"SELECT %s FROM %s where %s;",fieldlistVal
,tableName
,conditionVal
);
196 retValue
= SQLPrepare (hstmt
, (unsigned char *) stmtBuf
, SQL_NTS
);
198 printError(ErrSysInit
, "Unable to Prepare ODBC statement \n");
201 logFinest(Conf::logger
, "Cache Table Stmt %s", stmtBuf
);
204 retValue
= SQLNumResultCols (hstmt
, &totalFields
);
206 printError(ErrSysInit
, "Unable to retrieve ODBC total columns\n");
210 UCHAR colName
[IDENTIFIER_LENGTH
];
214 SQLULEN colLength
= 0;
219 colNameMax
= IDENTIFIER_LENGTH
;
220 char columnname
[IDENTIFIER_LENGTH
];
221 char indexname
[IDENTIFIER_LENGTH
];
222 short type
; short unique
;
224 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmtmeta
);
227 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
231 retValue
=SQLPrimaryKeys(hstmtmeta
, NULL
, SQL_NTS
, NULL
, SQL_NTS
, (SQLCHAR
*) tableName
, SQL_NTS
);
232 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_CHAR
,columnname
, 129,NULL
);
233 HashIndexInitInfo
*inf
= new HashIndexInitInfo();
234 char crtIdxStmt
[1024];
236 char *ptr
=crtIdxStmt
;
237 sprintf(ptr
, "CREATE INDEX %s_PRIMARY on %s ( ", tableName
, tableName
);
239 bool isPriIndex
=false;
240 char indname
[IDENTIFIER_LENGTH
];
241 if(SQLFetch( hstmtmeta
) == SQL_SUCCESS
)
243 Util::str_tolower(columnname
);
244 inf
->list
.append(columnname
);
245 sprintf(ptr
, "%s ", columnname
);
247 while ( SQLFetch( hstmtmeta
) == SQL_SUCCESS
) {
248 Util::str_tolower(columnname
);
249 inf
->list
.append(columnname
);
250 sprintf(ptr
, ", %s ", columnname
);
253 sprintf(ptr
, ") PRIMARY SIZE 10007;");
254 inf
->indType
= hashIndex
;
255 inf
->bucketSize
= 10007;
256 inf
->isUnique
= true; inf
->isPrimary
= true;
257 strcpy(inf
->tableName
, tableName
);
258 strcpy(indexname
,"PRIMARY");
259 sprintf(indname
, "%s_%s", tableName
, indexname
);
262 bool iskeyfieldExist
=false;
263 bool isPKFieldSpecified
= false;
264 if((strcmp(fieldName
,"")!=0) && (strcmp(fieldName
,"NULL")!=0) )
266 isPKFieldSpecified
= true;
268 if ( isPriIndex
&& ( strcmp(fieldlistVal
,"")!=0 ) &&
269 ( strcmp(fieldlistVal
,"NULL") != 0 )) {
270 inf
->list
.resetIter();
271 while ( (name
=inf
->list
.nextFieldName()) != NULL
) {
272 iskeyfieldExist
= TableConf::config
.isFieldExist(name
);
273 if(!iskeyfieldExist
) { break; }
275 } else if (isPriIndex
) { iskeyfieldExist
= true; }
276 if ( isPKFieldSpecified
&& !(TableConf::config
.isFieldExist(fieldName
)) )
278 if ( Conf::config
.useTwoWayCache() &&
279 (strcmp(fieldlistVal
,"")!=0) &&
280 (strcmp(fieldlistVal
,"NULL")!=0))
282 printError(ErrSysInit
, "Bidirectional caching should have primary key in %s \n", tableName
);
283 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
284 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
285 SQLDisconnect (hdbc
);
286 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
287 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
292 if (!iskeyfieldExist
&& !isPKFieldSpecified
)
294 if(Conf::config
.useTwoWayCache())
296 printError(ErrSysInit
, "Bidirectional caching fail for no primary key in %s \n", tableName
);
297 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
298 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
299 SQLDisconnect (hdbc
);
300 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
301 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
308 else if (Conf::config.useTwoWayCache() && !iskeyfieldExist) {
309 printError(ErrSysInit, "Bidirectonal caching fail for no primary key in %s \n", tableName);
310 SQLFreeHandle (SQL_HANDLE_STMT, hstmtmeta);
311 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
312 SQLDisconnect (hdbc);
313 SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
314 SQLFreeHandle (SQL_HANDLE_ENV, henv);
319 bool isNullfld
=false;
320 bool firstFld
= true;
321 char crtTblStmt
[1024];
323 sprintf(ptr
, "CREATE TABLE %s ( ", tableName
);
325 while (icol
<= totalFields
) {
326 retValue
= SQLDescribeCol(hstmt
, icol
, colName
, colNameMax
,
327 &nameLength
, &colType
, &colLength
,
330 printError(ErrSysInit
, "Unable to retrieve ODBC column info\n");
331 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
332 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
333 SQLDisconnect (hdbc
);
334 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
335 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
339 Util::str_tolower((char*)colName
);
340 printDebug(DM_Gateway
, "Describe Column %s %d %d %d %d \n", colName
, colType
, colLength
, scale
, nullable
);
341 logFinest(Conf::logger
, "Describe Column colName:%s colType:%d colLen:%d scale:%d nullable:%d\n", colName
, colType
, colLength
, scale
, nullable
);
344 if(strcmp((char*)colName
,fieldName
)== 0)
351 inf
->list
.resetIter();
352 while ((name
=inf
->list
.nextFieldName())!=NULL
) {
353 if(0==strcmp((char*)colName
,name
)) {
356 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
358 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
)
360 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
361 } else { sprintf(ptr
, " NOT NULL"); }
364 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
366 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
)
368 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
369 } else { sprintf(ptr
, " NOT NULL"); }
372 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale,tdbName), colLength +1, NULL, true);
381 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
383 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
384 sprintf(ptr
, "(%d)",colLength
+1);
388 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
390 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
391 sprintf(ptr
, "(%d)",colLength
+1);
395 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale,tdbName), colLength+1);
399 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
401 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
402 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
403 } else { sprintf(ptr
, " NOT NULL",colLength
); }
406 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
408 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
409 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
410 } else { sprintf(ptr
, " NOT NULL",colLength
); }
413 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale,tdbName), colLength+1, NULL, true);
420 sprintf(ptr
, "%s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
422 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
423 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
424 } else { sprintf(ptr
, " NOT NULL",colLength
); }
427 sprintf(ptr
, ", %s %s", colName
, AllDataType::getSQLString(AllDataType::convertFromSQLType(colType
,colLength
, scale
, tdbName
)));
429 if (colType
== SQL_CHAR
|| colType
== SQL_VARCHAR
|| colType
== SQL_BINARY
) {
430 sprintf(ptr
, "(%d) NOT NULL",colLength
+1);
431 } else { sprintf(ptr
, " NOT NULL",colLength
); }
434 //tabDef.addField((char*)colName, AllDataType::convertFromSQLType(colType,colLength,scale, tdbName), colLength +1, NULL, true);
439 //printf("table stmt '%s'\n", crtTblStmt);
440 if(((strcmp(fieldName
,"")!=0) && (strcmp(fieldName
,"NULL")!=0))
442 printError(ErrSysInit
, "Unable to cache Table for %s with key field %s\n", tableName
,fieldName
);
443 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
444 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
445 SQLDisconnect (hdbc
);
446 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
447 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
451 rv
= stmt
->prepare(crtTblStmt
);
453 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
454 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
455 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
456 SQLDisconnect (hdbc
);
457 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
458 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
463 rv
= stmt
->execute(rows
);
465 printError(ErrSysInit
, "Unable to execute create table stmt\n");
466 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
467 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
468 SQLDisconnect (hdbc
);
469 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
470 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
474 logFinest(Conf::logger
, "Cache Table: Table Created :%s", crtTblStmt
);
477 //Create primary key index if present
478 if (isPriIndex
&& ( iskeyfieldExist
||
479 (strcmp(fieldlistVal
,"")==0 || strcmp(fieldlistVal
,"NULL")== 0))) {
480 rv
= stmt
->prepare(crtIdxStmt
);
482 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
483 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
484 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
485 SQLDisconnect (hdbc
);
486 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
487 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
492 rv
= stmt
->execute(rows
);
494 printError(ErrSysInit
, "Unable to execute create table stmt\n");
495 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
496 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
497 SQLDisconnect (hdbc
);
498 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
499 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
503 //printf("Primary index created from create Index stmt\n");
505 retValue
= SQLCloseCursor(hstmtmeta
);
506 rv
= createIndex(hstmtmeta
, tableName
, inf
, stmt
,isPKFieldSpecified
);
508 dbMgr
->dropTable(tableName
);
509 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
510 SQLDisconnect (hdbc
);
511 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
512 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
518 rv
=checkingSchema(hdbc
,hstmt
,conn
,stmt
,tdbName
);
520 printError(ErrSysInit
,"Unable to cache the '%s' table due to schema mismatched.",tableName
);
521 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
522 SQLDisconnect (hdbc
);
523 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
524 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
530 // Now load the table with records
533 sprintf(ptr
,"INSERT INTO %s VALUES(", tableName
);
535 bool firstFld
= true;
536 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
->getInnerStatement();
537 sqlStmt
->setConnection(con
);
538 List fNameList
= sqlStmt
->getFieldNameList(tableName
);
539 int noOfFields
= fNameList
.size();
540 int totalFields
= noOfFields
;
541 while (noOfFields
--) {
544 sprintf(ptr
,"?", tableName
);
553 //printf("insert stmt: '%s'\n", insStmt);
555 rv
= stmt
->prepare(insStmt
);
557 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
558 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
559 SQLDisconnect (hdbc
);
560 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
561 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
564 ListIterator fNameIter
= fNameList
.getIterator();
565 FieldInfo
*info
= new FieldInfo();
566 int fcount
=1; void *valBuf
;
567 Identifier
*elem
= NULL
;
568 void *tembuf
=NULL
;//For postgre BigInt type
571 SQLLEN
*len
= (SQLLEN
*)malloc((totalFields
+1)*sizeof(SQLLEN
));
572 for(int i
=0;i
<=totalFields
;i
++) { len
[i
] = SQL_NTS
;}
573 while (fNameIter
.hasElement()) {
574 elem
= (Identifier
*) fNameIter
.nextElement();
575 sqlStmt
->getFieldInfo(tableName
, (const char*)elem
->name
, info
);
576 if (info
->type
== typeString
|| info
->type
== typeVarchar
) {
577 valBuf
= AllDataType::alloc(info
->type
, info
->length
+1);
578 os::memset(valBuf
,0,info
->length
+1);
580 valBuf
= AllDataType::alloc(info
->type
);
581 os::memset(valBuf
,0,AllDataType::size(info
->type
));
586 bBuf
= new BindBuffer();
588 bBuf
->type
= typeDate
;
589 bBuf
->length
= sizeof(DATE_STRUCT
);
590 bBuf
->targetdb
= malloc(bBuf
->length
);
591 memset(bBuf
->targetdb
, 0, bBuf
->length
);
592 valBuf
= bBuf
->targetdb
;
593 valBufList
.append(bBuf
);
596 bBuf
= new BindBuffer();
598 bBuf
->type
= typeTime
;
599 bBuf
->length
= sizeof(TIME_STRUCT
);
600 bBuf
->targetdb
= malloc(bBuf
->length
);
601 memset(bBuf
->targetdb
, 0, bBuf
->length
);
602 valBuf
= bBuf
->targetdb
;
603 valBufList
.append(bBuf
);
606 bBuf
= new BindBuffer();
608 bBuf
->type
= typeTimeStamp
;
609 bBuf
->length
= sizeof(TIMESTAMP_STRUCT
);
610 bBuf
->targetdb
= malloc(bBuf
->length
);
611 memset(bBuf
->targetdb
, 0, bBuf
->length
);
612 valBuf
= bBuf
->targetdb
;
613 valBufList
.append(bBuf
);
617 if( tdbName
== postgres
)
619 bBuf
= new BindBuffer();
620 bBuf
->type
= typeLongLong
;
623 bBuf
->targetdb
= AllDataType::alloc(typeString
,bBuf
->length
);
624 memset(bBuf
->targetdb
, 0, bBuf
->length
);
625 valBuf
= bBuf
->targetdb
;
626 valBufList
.append(bBuf
);
631 bBuf
= new BindBuffer();
632 bBuf
->type
= info
->type
;
634 valBufList
.append(bBuf
);
635 bBuf
->length
= info
->length
;
640 if( tdbName
!= mysql
)
642 bBuf
= new BindBuffer();
643 bBuf
->type
= typeString
;
645 bBuf
->length
= info
->length
+1;
646 valBufList
.append(bBuf
);
650 bBuf
= new BindBuffer();
651 bBuf
->type
= info
->type
;
653 valBufList
.append(bBuf
);
654 bBuf
->length
= info
->length
;
657 //os::memset(valBuf,0,bBuf->length);
658 retValue
= SQLBindCol (hstmt
, fcount
, AllDataType::convertToSQL_C_Type(info
->type
,tdbName
), valBuf
, bBuf
->length
, &len
[fcount
]);
661 printError(ErrSysInit
, "Unable to bind columns in ODBC\n");
662 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
663 SQLDisconnect (hdbc
);
664 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
665 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
672 while (fNameIter
.hasElement())
673 delete ((FieldName
*) fNameIter
.nextElement());
676 retValue
= SQLExecute (hstmt
);
678 printError(ErrSysInit
, "Unable to execute ODBC statement\n");
679 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
680 SQLDisconnect (hdbc
);
681 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
682 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
688 //TODO: if SQLFetch return other than record not found error
689 //it should drop the table
690 retValue
= SQLFetch (hstmt
);
692 ListIterator bindIter
= valBufList
.getIterator();
694 while (bindIter
.hasElement()) {
695 bBuf
= (BindBuffer
*) bindIter
.nextElement();
696 switch (bBuf
->type
) {
699 if( tdbName
!= mysql
)
701 Util::trimRight((char*)bBuf
->csql
);
707 Date
*dtCSQL
= (Date
*) bBuf
->csql
;
708 DATE_STRUCT
*dtTarget
= (DATE_STRUCT
*) bBuf
->targetdb
;
709 dtCSQL
->set(dtTarget
->year
,dtTarget
->month
,dtTarget
->day
);
714 Time
*dtCSQL
= (Time
*) bBuf
->csql
;
715 TIME_STRUCT
*dtTarget
= (TIME_STRUCT
*) bBuf
->targetdb
;
716 dtCSQL
->set(dtTarget
->hour
,dtTarget
->minute
,dtTarget
->second
);
721 TimeStamp
*dtCSQL
= (TimeStamp
*) bBuf
->csql
;
722 TIMESTAMP_STRUCT
*dtTarget
= (TIMESTAMP_STRUCT
*) bBuf
->targetdb
;
723 dtCSQL
->setDate(dtTarget
->year
,dtTarget
->month
,dtTarget
->day
);
724 dtCSQL
->setTime(dtTarget
->hour
,dtTarget
->minute
,dtTarget
->second
, dtTarget
->fraction
);
729 if ( tdbName
== postgres
) {
730 sscanf((const char*)bBuf
->targetdb
,"%lld",(long long*) bBuf
->csql
);
735 setParamValues(stmt
, ++fldpos
, bBuf
->type
, bBuf
->length
, (char *) bBuf
->csql
);
738 //table->resetNullinfo();
739 while(fldpos
< fcount
-1) {
740 if(len
[++fldpos
] == SQL_NULL_DATA
) {
741 stmt
->setNull(fldpos
);
745 rv
= stmt
->execute(rows
);
747 printError(ErrSysInit
, "Unable to cache record in CSQL.\n");
748 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
749 SQLDisconnect (hdbc
);
750 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
751 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
757 //Note:one operation per txn gives best performance than 100/txn
760 //TODO::leak:: valBufList and its targetdb buffer
761 ListIterator it
= valBufList
.getIterator();
762 while(it
.hasElement()) {
763 BindBuffer
*bb
= (BindBuffer
*) it
.nextElement();
764 if (bb
->csql
) { free(bb
->csql
); bb
->csql
= NULL
; }
765 if (bb
->targetdb
) { free(bb
->targetdb
); bb
->targetdb
= NULL
; }
766 delete bb
; bb
= NULL
;
769 SQLCloseCursor (hstmt
);
770 SQLFreeHandle (SQL_HANDLE_STMT
, hstmt
);
771 SQLDisconnect (hdbc
);
772 SQLFreeHandle (SQL_HANDLE_DBC
, hdbc
);
773 SQLFreeHandle (SQL_HANDLE_ENV
, henv
);
777 DbRetVal
CacheTableLoader::reload()
780 DbRetVal rv
= unload(false);
781 if (rv
!= OK
) return rv
;
782 //get table cache senarios
783 fp
= fopen(Conf::config
.getTableConfigFile(),"r");
785 printError(ErrSysInit
, "csqltable.conf file does not exist");
790 char tablename
[IDENTIFIER_LENGTH
];
791 char fieldname
[IDENTIFIER_LENGTH
];
792 char field
[IDENTIFIER_LENGTH
];
793 char condition
[IDENTIFIER_LENGTH
];
794 char dsnname
[IDENTIFIER_LENGTH
];
797 fscanf(fp
, "%d %s %s %s %s %s\n", &mode
, tablename
,fieldname
,condition
,field
,dsnname
);
798 if(strcmp(tablename
,tableName
)==0) break;
801 setCondition(TableConf::config
.getRealConditionFromFile(condition
));
802 setFieldName(fieldname
);
803 setFieldListVal(field
);
809 DbRetVal
CacheTableLoader::unload(bool tabDefinition
)
811 AbsSqlConnection
*conn
= SqlFactory::createConnection(CSqlLog
);
812 DbRetVal rv
= conn
->connect(userName
, password
);
813 if (rv
!= OK
) return ErrSysInit
;
814 AbsSqlStatement
*stmt
= SqlFactory::createStatement(CSqlLog
);
815 stmt
->setConnection(conn
);
816 SqlLogConnection
*logConn
= (SqlLogConnection
*) conn
;
817 logConn
->setNoMsgLog(true);
818 char statement
[1024];
819 if (TableConf::config
.isTableCached(tableName
) != OK
) {
820 printError(ErrNotCached
, "The table \'%s\' is not cached", tableName
);
826 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
827 DatabaseManager
*dbMgr
= (DatabaseManager
*) con
->getConnObject().getDatabaseManager();
830 delete stmt
; delete conn
;
831 printError(ErrSysInit
, "Authentication failed\n");
836 sprintf(statement
, "DELETE FROM %s;", tableName
);
837 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
838 sqlStmt
->setLoading(true);
839 rv
= stmt
->prepare(statement
);
842 delete stmt
; delete conn
;
847 rv
= stmt
->execute(rows
);
850 delete stmt
; delete conn
;
857 rv
= TableConf::config
.removeFromCacheTableFile();
859 conn
->disconnect(); delete stmt
; delete conn
;
862 sprintf(statement
, "DROP TABLE %s;", tableName
);
863 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
;
864 sqlStmt
->setLoading(true);
865 rv
= stmt
->prepare(statement
);
867 //TableConf::config.addToCacheTableFile(false);
869 delete stmt
; delete conn
;
873 rv
= stmt
->execute(rows
);
875 //TableConf::config.addToCacheTableFile(false);
876 conn
->disconnect(); delete stmt
; delete conn
;
881 delete stmt
; delete conn
;
882 logFine(Conf::logger
, "Unloaded Cached Table: %s", tableName
);
886 DbRetVal
CacheTableLoader::refresh()
891 DbRetVal
CacheTableLoader::recoverAllCachedTables()
895 DbRetVal rv
= conn
.open(userName
, password
);
896 if(rv
!=OK
) return ErrSysInit
;
898 //Note: if connection is not open, configuration veriables may be incorrect
900 fp
= fopen(Conf::config
.getTableConfigFile(),"r");
902 printError(ErrSysInit
, "csqltable.conf file does not exist");
907 //TODO::take exclusive lock on database
908 char tablename
[IDENTIFIER_LENGTH
];
909 char fieldname
[IDENTIFIER_LENGTH
];
910 char condition
[IDENTIFIER_LENGTH
];
911 char field
[IDENTIFIER_LENGTH
];
912 char dsnname
[IDENTIFIER_LENGTH
];
919 scanItems
= fscanf(fp
, "%d %s %s %s %s %s\n", &mode
, tablename
,fieldname
,condition
,field
,dsnname
);
920 if (scanItems
!= 6) {
922 printf("There is no table to be cached.\n");
925 //if (mode ==2 ) //just replicated table and not cached
927 printDebug(DM_Gateway
, "Recovering Table from target db: %s\n", tablename
);
928 setCondition(TableConf::config
.getRealConditionFromFile(condition
));
929 if( (strcmp(Conf::config
.getDSN(),dsnname
)!=0) ){
932 setFieldName(fieldname
);
933 setFieldListVal(field
);
934 printf("Recovering table %s %s %s\n", tablename
,condition
,field
);
936 if (rv
!= OK
) { fclose(fp
); return rv
; }
938 setDsnName(Conf::config
.getDSN());
940 setFieldName(fieldname
);
941 setFieldListVal(field
);
942 printf("Recovering table %s %s %s\n", tablename
,condition
,field
);
944 if (rv
!= OK
) { fclose(fp
); return rv
; }
946 logFine(Conf::logger
, "Recovering Table from target db:%s", tablename
);
952 void CacheTableLoader::setParamValues(AbsSqlStatement
*stmt
, int parampos
, DataType type
, int length
, char *value
)
957 stmt
->setIntParam(parampos
, *(int*)value
);
960 stmt
->setLongParam(parampos
, *(long*)value
);
963 stmt
->setLongLongParam(parampos
, *(long long*)value
);
966 stmt
->setShortParam(parampos
, *(short*)value
);
969 stmt
->setByteIntParam(parampos
, *(char*)value
);
972 stmt
->setDoubleParam(parampos
, *(double*)value
);
975 stmt
->setFloatParam(parampos
, *(float*)value
);
978 stmt
->setDateParam(parampos
, *(Date
*)value
);
981 stmt
->setTimeParam(parampos
, *(Time
*)value
);
984 stmt
->setTimeStampParam(parampos
, *(TimeStamp
*)value
);
989 char *d
=(char*)value
;
991 stmt
->setStringParam(parampos
, (char*)value
);
995 stmt
->setBinaryParam(parampos
, (char *) value
, length
);
1001 DbRetVal
CacheTableLoader::createIndex(SQLHSTMT hstmtmeta
, char *tableName
, HashIndexInitInfo
*inf
,AbsSqlStatement
*stmt
,bool isPKFieldSpecified
)
1003 bool isKeyFld
= false;
1005 char columnname
[IDENTIFIER_LENGTH
];
1006 char indexname
[IDENTIFIER_LENGTH
];
1011 retValue
= SQLStatistics(hstmtmeta
, NULL
, 0, NULL
, SQL_NTS
,
1012 (SQLCHAR
*) tableName
, SQL_NTS
, SQL_INDEX_ALL
, SQL_QUICK
);
1013 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_SHORT
,
1015 retValue
= SQLBindCol(hstmtmeta
, 6, SQL_C_CHAR
,
1016 indexname
, 129, NULL
);
1017 retValue
= SQLBindCol(hstmtmeta
, 7, SQL_C_SHORT
,
1019 retValue
= SQLBindCol(hstmtmeta
, 9, SQL_C_CHAR
,
1020 columnname
, 129,NULL
);
1022 bool isSecondTime
= false;
1023 CacheIndexInfo
*info
=NULL
;
1024 while ((retValue
= SQLFetch(hstmtmeta
)) == SQL_SUCCESS
) {
1025 //if (type != SQL_TABLE_STAT)
1027 printDebug(DM_Gateway
, "Column: %-18s Index Name: %-18s unique:%hd type:%hd\n", columnname
, indexname
, unique
, type
);
1033 bool isFldAdd
= false;
1034 ListIterator iter
= indexList
.getIterator();
1036 while (iter
.hasElement())
1038 CacheIndexInfo
*indInfo
= (CacheIndexInfo
*)iter
.nextElement();
1039 if(0 == strcmp( indInfo
->indexName
, indexname
))
1041 indInfo
->fieldList
.append(columnname
);
1046 info
= new CacheIndexInfo();
1047 info
->fieldList
.append(columnname
);
1048 strcpy(info
->indexName
, indexname
);
1049 indexList
.append(info
);
1050 isSecondTime
= true;
1055 ListIterator iter
= indexList
.getIterator();
1057 int noOfPkfield
= inf
->list
.size();
1060 while (iter
.hasElement())
1063 bool isFieldExistInCondition
= false;
1064 bool isPrimary
=false;
1065 CacheIndexInfo
*indInfo
= (CacheIndexInfo
*)iter
.nextElement();
1066 int noOfFld
= indInfo
->fieldList
.size();
1067 indInfo
->fieldList
.resetIter();
1068 while ((fName
= indInfo
->fieldList
.nextFieldName())!=NULL
)
1070 if(( 1 == noOfFld
) && (0 == strcmp(fName
,fieldName
))) { isKeyFld
=true; }
1071 inf
->list
.resetIter();
1072 while ((name
=inf
->list
.nextFieldName())!=NULL
)
1074 if(0==strcmp(fName
,name
)) { isPrimary
= true; break; }
1077 if (!TableConf::config
.isFieldExist(fName
) && ( (strcmp(fieldlistVal
,"")!=0) && (strcmp(fieldlistVal
,"NULL")!=0) ))
1079 isFieldExistInCondition
=true;
1082 sprintf(cptr
, "%s ,",fName
);
1083 cptr
+= strlen(cptr
);
1086 if(isFieldExistInCondition
) continue;
1090 if (isPrimary
) { continue; }
1091 char crtIdxStmt
[1024];
1093 sprintf(indname
, "%s_%s", tableName
, indInfo
->indexName
);
1094 sprintf(crtIdxStmt
, "CREATE INDEX %s on %s(%s) HASH SIZE 10007;", indname
, tableName
, columnname
);
1095 //printf("create index stmt \n'%s'\n", crtIdxStmt);
1096 rv
= stmt
->prepare(crtIdxStmt
);
1098 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
1102 rv
= stmt
->execute(rows
);
1104 printError(ErrSysInit
, "Unable to execute create table stmt\n");
1108 }// while meta data fetch for index creation
1110 SQLCloseCursor (hstmtmeta
);
1111 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1112 if( !isKeyFld
&& isPKFieldSpecified
) {
1114 char frcIndStmt
[1024];
1116 sprintf(indname
, "%s_%s", tableName
, "keyInd");
1117 sprintf(frcIndStmt
, "CREATE INDEX %s on %s(%s) HASH;", indname
, tableName
, fieldName
);
1118 rv
= stmt
->prepare(frcIndStmt
);
1120 printError(ErrSysInit
, "Unable to prepare create table stmt\n");
1124 rv
= stmt
->execute(rows
);
1126 printError(ErrSysInit
, "Unable to execute create table stmt\n");
1130 printError(ErrSysInit
, "Unable to cache Table for %s with key field %s\n", tableName
,fieldName
);
1137 DbRetVal
CacheTableLoader::checkingSchema(SQLHDBC hdbc
,SQLHSTMT hstmt
, AbsSqlConnection
*conn
, AbsSqlStatement
*stmt
,TDBInfo tdbName
)
1140 int noOfPrimaryKey
=0;
1144 SQLSMALLINT tdbFields
=0;
1146 char columnname
[IDENTIFIER_LENGTH
];
1149 UCHAR colName
[IDENTIFIER_LENGTH
];
1153 SQLULEN colLength
= 0;
1156 colNameMax
= IDENTIFIER_LENGTH
;
1158 SqlConnection
*con
= (SqlConnection
*) conn
->getInnerConnection();
1159 DatabaseManager
*dbMgr
= con
->getConnObject().getDatabaseManager();
1161 SqlStatement
*sqlStmt
= (SqlStatement
*)stmt
->getInnerStatement();
1162 sqlStmt
->setConnection(con
);
1165 fNameList
= sqlStmt
->getFieldNameList(tableName
);
1166 ListIterator fNameIter
= fNameList
.getIterator();
1167 FieldInfo
*info
= new FieldInfo();
1168 Identifier
*elem
= NULL
;
1170 retValue
=SQLNumResultCols(hstmt
, &tdbFields
);
1172 printError(ErrSysInit
, "Unable to retrieve ODBC total columns.\n");
1173 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1176 /* CSQL Table fields */
1177 fNameList
= sqlStmt
->getFieldNameList(tableName
);
1178 csqlFields
= fNameList
.size();
1179 /* noOfFields in both the database are same or not. */
1180 if(tdbFields
!=csqlFields
){
1181 printError(ErrSysInit
,"Number of fields between CSQL and TDB are not equal.");
1182 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1185 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmtmeta
);
1187 printError(ErrSysInit
, "Unable to allocate ODBC handle. \n");
1188 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1191 retValue
=SQLPrimaryKeys(hstmtmeta
, NULL
, SQL_NTS
, NULL
, SQL_NTS
, (SQLCHAR
*) tableName
, SQL_NTS
);
1192 retValue
= SQLBindCol(hstmtmeta
, 4, SQL_C_CHAR
,columnname
, 129,NULL
);
1194 while(icol
<=tdbFields
){
1195 retValue
= SQLDescribeCol(hstmt
, icol
, colName
, colNameMax
,
1196 &nameLength
, &colType
, &colLength
,
1197 &scale
, &nullable
);//TDB Field Name
1199 printError(ErrSysInit
, "Unable to retrieve ODBC column info.\n");
1200 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1203 Util::str_tolower((char*)colName
);
1204 elem
= (Identifier
*) fNameIter
.nextElement();
1205 sqlStmt
->getFieldInfo(tableName
, (const char*)elem
->name
, info
);
1209 rv
= stmt
->getParamFldInfo(icol
,info
);
1210 char *name
=(info
->fldName
);//Getting field name for CSQL table.
1211 Util::str_tolower((char*)name
);
1212 if(strcmp(name
,(char *)colName
) != 0){ //Field name matching between CSQL and TDB.
1213 printError(ErrSysInit
,"CSQL's '%s' field did not match with TDB's '%s' field.\n",name
,(char*)colName
);
1214 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1218 /* DataType matching between CSQL and TDB */
1219 char ptr
[IDENTIFIER_LENGTH
]; ptr
[0]='\0';
1220 char ptr1
[IDENTIFIER_LENGTH
]; ptr1
[0]='\0';
1222 sprintf(ptr
,"%s",AllDataType::getSQLString (AllDataType::convertFromSQLType(colType
,colLength
,scale
,tdbName
)));
1223 sprintf(ptr1
,"%s",AllDataType::getSQLString(info
->type
));//CSQL Type
1224 if(strcmp(ptr
,ptr1
)!=0){
1225 printError(ErrSysInit
,"DataType did not match for '%s' field in CSQL.\n",name
);
1226 SQLFreeHandle (SQL_HANDLE_STMT
, hstmtmeta
);
1230 /* Primary Key checking */
1232 if(SQLFetch( hstmtmeta
) == SQL_SUCCESS
) tdbPKey
=true;
1233 if(tdbPKey
&& (!info
->isPrimary
))
1234 printf("Warning: In CSQL, The %s's '%s' field should have Primery Key constraint.\n",tableName
,name
);
1235 if((!tdbPKey
) && info
->isPrimary
)
1236 printf("Warning: In TDB, The %s's '%s' field should have Primary Key constraint.\n",tableName
,colName
);
1238 /* NotNull Checking */
1239 bool isCsqlNotNull
=false;
1240 bool isTdbNotNull
=false;
1242 if(info
->isNull
&& nullable
)
1243 printf("Warning: In TDB, The %s's '%s' field should have a NOT NULL constraint.\n",tableName
,colName
);
1244 if((!info
->isNull
) && (!nullable
))
1245 printf("Warning: In CSQL, The %s's '%s' field should have a NOT NULL constraint.\n",tableName
,name
);
1252 DbRetVal
CacheTableLoader::cacheAllTablesFromDs(char *dsnName
,bool tableDefinition
, bool isDirect
,char *username
, char *password
)
1257 fp
= fopen(Conf :: config
.getDsConfigFile(),"r");
1259 printError(ErrSysInit
, "csqlds.conf file does not exist");
1262 char dsnId
[IDENTIFIER_LENGTH
]; dsnId
[0]='\0';
1263 char user
[IDENTIFIER_LENGTH
]; user
[0] = '\0';
1264 char passwd
[IDENTIFIER_LENGTH
]; passwd
[0] = '\0';
1265 char tdb
[IDENTIFIER_LENGTH
]; tdb
[0]='\0';
1267 bool isCached
=false;
1269 /* If -d option is disable, the If statementn will true. */
1270 if(strcmp(dsnName
,"")==0) {
1271 strcpy(dsnName
, Conf::config
.getDSN());
1273 bool isDSNExist
=false;
1275 fscanf(fp
,"%s %s %s %s\n",dsnId
,user
,passwd
,tdb
);
1276 if(strcmp(dsnId
,dsnName
)==0) {
1277 if( strcmp(user
,"NULL")!=0 && strcmp(passwd
,"NULL")!=0) {
1278 sprintf(dsn
,"DSN=%s;UID=%s;PWD=%s;",dsnName
,user
,passwd
);
1282 sprintf(dsn
,"DSN=%s;",dsnName
);
1289 printError(ErrNotExists
,"Entries is not present in the csqlds.conf file\n");
1291 return ErrNotExists
;
1295 TDBInfo tdbName
=mysql
;
1296 if (strcasecmp(tdb
,"mysql") == 0) tdbName
=mysql
;
1297 else if (strcasecmp(tdb
,"postgres")==0) tdbName
=postgres
;
1298 else printError(ErrNotFound
,"Target Database Name is not properly set.Tdb name could be MySql and Postgres.\n");
1299 logFine(Conf::logger
, "TDB Name:%s\n", tdb
);
1300 /* The ODBC section in intended to get all the tables from TDB,
1301 * * what SQLTables() is doing that. */
1303 SQLCHAR outstr
[1024];
1304 SQLSMALLINT outstrlen
;
1309 SQLSMALLINT columns
;
1310 char table
[IDENTIFIER_LENGTH
][IDENTIFIER_LENGTH
];
1312 char buf
[IDENTIFIER_LENGTH
];
1314 SQLINTEGER indicator
[ 5 ];
1315 int colPos
;//Only to bind table name filed.
1317 CacheTableLoader cacheLoader
;
1319 /* Environment Handle. */
1320 retValue
= SQLAllocHandle (SQL_HANDLE_ENV
, SQL_NULL_HANDLE
, &henv
);
1322 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
1325 /* We want ODBC 3 support */
1326 SQLSetEnvAttr(henv
, SQL_ATTR_ODBC_VERSION
, (void *) SQL_OV_ODBC3
, 0);
1327 /* Conenction handle. */
1328 retValue
= SQLAllocHandle (SQL_HANDLE_DBC
, henv
, &hdbc
);
1330 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
1333 /* Connect to TDB */
1334 retValue
= SQLDriverConnect(hdbc
, NULL
, (SQLCHAR
*)dsn
, SQL_NTS
,
1335 outstr
, sizeof(outstr
), &outstrlen
,
1336 SQL_DRIVER_NOPROMPT
);
1337 if (SQL_SUCCEEDED(retValue
)) {
1338 printDebug(DM_Gateway
, "Connected to target database using dsn = %s\n", dsn
);
1340 printError(ErrSysInit
, "Failed to connect to target database\n");
1343 /* Statement handle */
1344 retValue
=SQLAllocHandle (SQL_HANDLE_STMT
, hdbc
, &hstmt
);
1346 printError(ErrSysInit
, "Unable to allocate ODBC handle \n");
1349 if(tdbName
== mysql
){
1351 /* User name is required in upper case for the SQLTables()'s 4th parameter */
1352 Util::str_toupper((char*)user
);
1353 retValue
=SQLTables(hstmt
,NULL
, 0, (SQLCHAR
*)user
, SQL_NTS
, NULL
, 0, (SQLCHAR
*)"TABLE", SQL_NTS
);
1355 printError(ErrSysInit
, "Unable to retrieve list of tables\n");
1358 /* Binding Column for 3rd parameter to get Table name. */
1359 retValue
=SQLBindCol(hstmt
,3, SQL_C_CHAR
,buf
,sizeof(buf
),NULL
);
1361 printError(ErrSysInit
,"Unable to BindCol\n");
1364 /* For Postgres DB , SQLTables() retrieves all system and metadata tables,along with User defined table.
1365 * So Here is a another option to fetch the user defined tables only */
1366 }else if(tdbName
==postgres
){
1367 SQLCHAR table
[200]="SELECT table_name FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog','information_schema');";
1368 /* Preparing the query */
1369 retValue
=SQLPrepare(hstmt
,table
,SQL_NTS
);
1371 printError(ErrSysInit
,"Unable to Prapare the statement\n");
1374 /* Binding the "table_name" column only */
1375 retValue
= SQLBindCol(hstmt
,1,SQL_C_CHAR
,buf
,sizeof(buf
),NULL
);
1377 printError(ErrSysInit
,"Unable to bind the column\n");
1380 /* Execute the SELECT statement */
1381 retValue
= SQLExecute(hstmt
);
1383 printError(ErrSysInit
,"Unable to execute\n");
1388 while(SQL_SUCCEEDED(retValue
= SQLFetch(hstmt
))){
1389 /* copy Buffer value */
1390 //strcpy(&table[counter][0],buf);
1392 cacheLoader
.setDsnName(dsnName
);
1393 TableConf::config
.setDsnName(dsnName
);
1394 cacheLoader
.setConnParam(username
, password
);
1395 TableConf::config
.setConnParam(username
, password
);
1396 /* Check table is cached or not */
1397 mode
= TableConf::config
.getTableMode(buf
);
1398 /* Settign up table */
1399 cacheLoader
.setTable(buf
);
1400 TableConf::config
.setTable(buf
);
1401 isCached
= TableConf::config
.isTableCached(mode
);
1403 printf("Warning: Table '%s' is already cached.\n",buf
);
1405 rv
= cacheLoader
.load(tableDefinition
);
1407 printf("Warning: Table '%s' is present in CSQL locally.\n",buf
);
1409 TableConf::config
.addToCacheTableFile(isDirect
);
1410 printf("Cached Table:%s\n",buf
);
1411 TableConf::config
.init();
1416 /* Checking couter value */
1418 printf("There is no table present in Target Database.\n");
1419 /*Closing opening forwarded Cursor */
1420 retValue
=SQLCloseCursor(hstmt
);
1422 printError(ErrSysInit
,"Unable to close the cursor\n");
1425 /* Commiting the transaction */
1426 retValue
=SQLTransact(henv
,hdbc
,SQL_COMMIT
);
1428 printError(ErrSysInit
,"Unable to commit the transaction\n");
1431 /* Freeing Statement handle */
1432 retValue
= SQLFreeHandle(SQL_HANDLE_STMT
,hstmt
);
1434 printError(ErrSysInit
,"Unable to free statement handle\n");
1437 /* Disconnecting from TDB */
1438 retValue
= SQLDisconnect(hdbc
);
1440 printError(ErrSysInit
,"Unable to disconnect from DS handle\n");
1443 /* Freeing Connection handle */
1444 retValue
= SQLFreeHandle(SQL_HANDLE_DBC
,hdbc
);
1446 printError(ErrSysInit
,"Unable to free connection handle\n");
1449 /* Freeing Environmant handle */
1450 retValue
= SQLFreeHandle(SQL_HANDLE_ENV
,henv
);
1452 printError(ErrSysInit
,"Unable to free environment handle\n");
1456 }/* -----------------------------End------------------------------- */