src/adapter/SqlOdbcStatement.cxx

   1 /***************************************************************************
   2  *   Copyright (C) 2007 by Prabakaran Thirumalai   *
   3  *   praba_tuty@yahoo.com   *
   4  *                                                                         *
   5  *   This program is free software; you can redistribute it and/or modify  *
   6  *   it under the terms of the GNU General Public License as published by  *
   7  *   the Free Software Foundation; either version 2 of the License, or     *
   8  *   (at your option) any later version.                                   *
   9  *                                                                         *
  10  *   This program is distributed in the hope that it will be useful,       *
  11  *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
  12  *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
  13  *   GNU General Public License for more details.                          *
  14  *                                                                         *
  15  *   You should have received a copy of the GNU General Public License     *
  16  *   along with this program; if not, write to the                         *
  17  *   Free Software Foundation, Inc.,                                       *
  18  *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
  19  ***************************************************************************/
  20 #include <SqlOdbcStatement.h>
  21 //Important Note: MySQL Bug
  22 //Bug #1382     SQLDescribeParam returns the same type information for any type
  23 //as varchar of length 255. To avoid this, this class converts every data type
  24 //to varchar by using appropriate conversion functions.
  25
  26 DbRetVal SqlOdbcStatement::executeDirect(char *stmtstr)
  27 {
  28     DbRetVal rv = OK;
  29     int retValue = 0;
  30     SqlOdbcConnection *conn = (SqlOdbcConnection*)con;
  31     retValue=SQLAllocHandle (SQL_HANDLE_STMT, conn->dbHdl, &hstmt);
  32     if (retValue) return ErrBadCall;
  33     SQLCHAR* sstr= (SQLCHAR*)stmtstr;
  34     retValue=(*SqlOdbcConnection::ODBCFuncPtrs.SQLExecDirectPtr) (hstmt, sstr, SQL_NTS);
  35     if (retValue) return ErrBadCall;
  36     logFinest(Conf::logger, "executeDirect %s", stmtstr);
  37     return rv;
  38 }
  39
  40 DbRetVal SqlOdbcStatement::prepare(char *stmtstr)
  41 {
  42     DbRetVal rv = OK;
  43     if (innerStmt) rv = ErrBadCall;
  44     if (rv != OK) return rv;
  45     int retValue =0;
  46     isPrepared = false;
  47     isProcedureCallStmt = false;
  48     SqlOdbcConnection *conn = (SqlOdbcConnection*)con;
  49     //retValue=SQLAllocHandle (SQL_HANDLE_STMT, conn->dbHdl, &hstmt);
  50     retValue=(*SqlOdbcConnection::ODBCFuncPtrs.SQLAllocHandlePtr) (SQL_HANDLE_STMT, conn->dbHdl, &hstmt);
  51     if (retValue) return ErrBadCall;
  52     SQLCHAR* sstr= (SQLCHAR*)stmtstr;
  53     //retValue = SQLPrepare (hstmt, sstr, SQL_NTS);
  54     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLPreparePtr) (hstmt, sstr, SQL_NTS);
  55     if (retValue) {
  56     //    setErrorState(hstmt);
  57         printError(ErrSysInternal, "Unable to prepare statement");
  58         return ErrSysInternal;
  59     }
  60     isSelStmt=chechStmtType(stmtstr);
  61     isPrepared = true;
  62     if(strstr(stmtstr,"call ")!=NULL || strstr(stmtstr,"CALL ")!=NULL)
  63     {
  64         logFinest(Conf::logger, "Procedure call statement =true");
  65         isProcedureCallStmt=true;
  66     }
  67     short totalFields=0;
  68     tdbname = conn->getTrDbName();
  69     rv = resolveForBindField(hstmt);
  70     if(rv!=OK) return rv;
  71     UWORD                   icol;
  72     SWORD                   colNameMax;
  73     SWORD                   scale;
  74     SWORD                   nullable;
  75     BindSqlField *bindField;
  76     //retValue = SQLNumParams (hstmt, &totalFields);
  77     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLNumParamsPtr) (hstmt, &totalFields);
  78     if (retValue) return ErrBadCall;
  79     icol = 1; colNameMax = IDENTIFIER_LENGTH;
  80     SWORD                   cType=0;
  81     SQLULEN                 cLength=0;
  82     scale=0;
  83     logFinest(Conf::logger, "NumParams %d", totalFields);
  84     if(totalFields != 0)
  85     {
  86         paramlen =(SQLINTEGER *) malloc((totalFields+1)*sizeof(SQLINTEGER));
  87         for(int i=0;i<=totalFields;i++) { paramlen[i] = SQL_NTS; }
  88     }
  89
  90     while (icol <= totalFields)
  91     {
  92         //retValue = SQLDescribeParam(hstmt, icol, &cType, &cLength,
  93         //Note: MySQL Bug
  94         //Bug #1382     SQLDescribeParam returns the same type information
  95
  96         bindField = new BindSqlField();
  97         bindField->type = typeString;
  98         bindField->length = 512;
  99         bindField->value =  AllDataType::alloc(bindField->type, bindField->length);
 100         bindField->targetvalue = NULL;
 101         int fieldsize =0;
 102         switch(bindField->type)
 103         {
 104             case typeString:
 105                 fieldsize = bindField->length;
 106                 bindField->targetvalue = malloc(fieldsize);
 107                 break;
 108             case typeDate:
 109                 fieldsize = sizeof(DATE_STRUCT);
 110                 bindField->targetvalue = malloc(sizeof(DATE_STRUCT));
 111                 break;
 112             case typeTime:
 113                 fieldsize = sizeof(TIME_STRUCT);
 114                 bindField->targetvalue = malloc(sizeof(TIME_STRUCT));
 115                 break;
 116             case typeTimeStamp:
 117                 fieldsize = sizeof(TIMESTAMP_STRUCT);
 118                 bindField->targetvalue = malloc(sizeof(TIMESTAMP_STRUCT));
 119                 break;
 120             default:
 121                 bindField->targetvalue = AllDataType::alloc(bindField->type, cLength);
 122                 break;
 123
 124         }
 125         //retValue = SQLBindParameter(hstmt, icol, SQL_PARAM_INPUT,
 126         retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLBindParameterPtr)(hstmt, icol, SQL_PARAM_INPUT,
 127                       AllDataType::convertToSQL_C_Type(bindField->type,tdbname),
 128                        AllDataType::convertToSQLType(bindField->type), fieldsize, scale, bindField->targetvalue,
 129                       fieldsize, &paramlen[icol]);
 130         if (retValue) return ErrBadCall;
 131         paramList.append(bindField);
 132         icol++;
 133     }
 134     //TODO::deallocate memory and remove elements from list in case of any
 135     //failure in any of the above ODBC functions
 136     logFinest(Conf::logger, "Statement prepared %s", stmtstr);
 137     return OK;
 138 }
 139
 140 bool SqlOdbcStatement::isSelect()
 141 {
 142     //TODO
 143     return isSelStmt;
 144 }
 145
 146 DbRetVal SqlOdbcStatement::execute(int &rowsAffected)
 147 {
 148     DbRetVal rv = OK;
 149     if (!isPrepared) return ErrNotPrepared;
 150     int retValue=0;
 151     ListIterator iter = paramList.getIterator();
 152     BindSqlField *bindField = NULL;
 153     int col = 0;
 154     while (iter.hasElement())
 155     {
 156         bindField = (BindSqlField*)iter.nextElement();
 157         if(paramlen[++col] == SQL_NULL_DATA){
 158            ::free(bindField->targetvalue);
 159            bindField->targetvalue = NULL;
 160            continue;
 161         }
 162         switch(bindField->type)
 163         {
 164             case typeDate: {
 165                 Date *dtCSQL = (Date*) bindField->value;
 166                 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
 167                 dtTarget->year= dtCSQL->year();
 168                 dtTarget->month= dtCSQL->month();
 169                 dtTarget->day = dtCSQL->dayOfMonth();
 170                 break;
 171             }
 172             case typeTime: {
 173                 Time *dtCSQL = (Time*) bindField->value;
 174                 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
 175                 dtTarget->hour = dtCSQL->hours();
 176                 dtTarget->minute = dtCSQL->minutes();
 177                 dtTarget->second = dtCSQL->seconds();
 178                 break;
 179             }
 180             case typeTimeStamp: {
 181                 TimeStamp *dtCSQL = (TimeStamp*) bindField->value;
 182                 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bindField->targetvalue;
 183                 dtTarget->year= dtCSQL->year();
 184                 dtTarget->month= dtCSQL->month();
 185                 dtTarget->day = dtCSQL->dayOfMonth();
 186                 dtTarget->hour = dtCSQL->hours();
 187                 dtTarget->minute = dtCSQL->minutes();
 188                 dtTarget->second = dtCSQL->seconds();
 189                 break;
 190             }
 191             default: {
 192                 AllDataType::cachecopyVal(bindField->targetvalue, bindField->value,
 193                                      bindField->type, bindField->length);
 194                 break;
 195             }
 196         }
 197     }
 198     //int retValue = SQLExecute (hstmt);
 199     if(isProcedureCallStmt)
 200     {
 201         retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLExecutePtr) (hstmt);
 202         if ((retValue != SQL_SUCCESS) && (retValue != SQL_SUCCESS_WITH_INFO )) {
 203             return ErrBadCall;
 204         }
 205         rv = resolveForBindField(hstmt);
 206         if(rv!=OK) return rv;
 207         logFinest(Conf::logger, "Procedure executed");
 208     }else{
 209         retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLExecutePtr) (hstmt);
 210         if ((retValue != SQL_SUCCESS) && (retValue != SQL_SUCCESS_WITH_INFO )) {
 211         //    setErrorState(hstmt);
 212            return ErrBadCall;
 213          }
 214          logFinest(Conf::logger, "Statement executed");
 215     }
 216     //retValue=SQLRowCount(hstmt,(SQLINTEGER*)&rowsAffected);
 217     retValue= (*SqlOdbcConnection::ODBCFuncPtrs.SQLRowCountPtr)(hstmt,(SQLINTEGER*)&rowsAffected);
 218     if(isSelStmt) rowsAffected = 0;
 219     return rv;
 220 }
 221
 222 DbRetVal SqlOdbcStatement::bindParam(int pos, void* value)
 223 {
 224     DbRetVal rv = OK;
 225     printError(ErrWarning, "Deprecated. Use setParamXXX instead\n");
 226     return rv;
 227 }
 228
 229 DbRetVal SqlOdbcStatement::bindField(int pos, void* value)
 230 {
 231     if (!isPrepared) return OK;
 232     BindSqlProjectField *bindField = (BindSqlProjectField*)bindList.get(pos);
 233     if (NULL == bindField)
 234     {
 235         printError(ErrBadArg, "Could not get the projection list. Should be called only for SELECT statement");
 236         return ErrBadArg;
 237     }
 238     bindField->value = value;
 239     return OK;
 240 }
 241
 242 //void SqlOdbcStatement::setNullInfo(Table *table)
 243 void SqlOdbcStatement::setNullInfo(AbsSqlStatement *stmt)
 244 {
 245     int fldpos=0;
 246     while(fldpos < totalFld)
 247     {
 248         if(len[++fldpos] == SQL_NULL_DATA)
 249         {
 250             stmt->setNull(fldpos);
 251         }
 252     }
 253 }
 254
 255 void* SqlOdbcStatement::fetch()
 256 {
 257     if (!isPrepared) return NULL;
 258     //int retValue = SQLFetch (hstmt);
 259     int retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLFetchPtr) (hstmt);
 260     if (retValue) return NULL;
 261     ListIterator iter = bindList.getIterator();
 262     BindSqlProjectField *bindField = NULL;
 263     void *ptrToFirstField = NULL;
 264     int icol=0;
 265     while (iter.hasElement())
 266     {
 267         bindField = (BindSqlProjectField*)iter.nextElement();
 268         if (ptrToFirstField == NULL) ptrToFirstField=bindField->value;
 269         if(len[++icol] == SQL_NULL_DATA)
 270         {
 271             if(bindField->value) AllDataType::memoryset(bindField->value,bindField->type);
 272             continue;
 273         }
 274         if( isSelStmt && NULL == bindField->value )
 275         {
 276             if (ptrToFirstField == NULL) ptrToFirstField=bindField->targetvalue;
 277             continue;
 278         }
 279         switch(bindField->type)
 280         {
 281             case typeDate: {
 282                 Date *dtCSQL = (Date*) bindField->value;
 283                 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
 284                 dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
 285                 break;
 286             }
 287             case typeTime: {
 288                 Time *dtCSQL = (Time*) bindField->value;
 289                 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
 290                 dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 291                 break;
 292             }
 293             case typeTimeStamp: {
 294                 TimeStamp *dtCSQL = (TimeStamp*) bindField->value;
 295                 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bindField->targetvalue;
 296                 dtCSQL->setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 297                 dtCSQL->setTime(dtTarget->hour,dtTarget->minute,
 298                                 dtTarget->second, 0);//dtTarget->fraction);
 299                 break;
 300             }
 301             default: {
 302                 AllDataType::cachecopyVal(bindField->value, bindField->targetvalue,
 303                                      bindField->type, bindField->length,tdbname);
 304                 break;
 305             }
 306         }
 307     }
 308     return ptrToFirstField;
 309 }
 310
 311 void* SqlOdbcStatement::fetch(DbRetVal &rv)
 312 {
 313     SqlOdbcConnection *sqlOdbcCon = (SqlOdbcConnection *)con;
 314     if (! sqlOdbcCon->isConnectionOpen()) {
 315         printError(ErrNotOpen, "Connection not open");
 316         rv = ErrNoConnection;
 317         return NULL;
 318     }
 319     if (!isPrepared) return NULL;
 320     //int retValue = SQLFetch (hstmt);
 321     int retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLFetchPtr) (hstmt);
 322     if (retValue) { rv = OK; return NULL; }
 323     ListIterator iter = bindList.getIterator();
 324     BindSqlProjectField *bindField = NULL;
 325     void *ptrToFirstField = NULL;
 326     int icol=0;
 327     while (iter.hasElement())
 328     {
 329         bindField = (BindSqlProjectField*)iter.nextElement();
 330         if (ptrToFirstField == NULL) ptrToFirstField=bindField->value;
 331         if(len[++icol] == SQL_NULL_DATA)
 332         {
 333             if(bindField->value) AllDataType::memoryset(bindField->value,bindField->type);
 334             continue;
 335         }
 336         if( isSelStmt && NULL == bindField->value )
 337         {
 338             if (ptrToFirstField == NULL) ptrToFirstField=bindField->targetvalue;
 339             continue;
 340         }
 341         switch(bindField->type)
 342         {
 343             case typeDate: {
 344                 Date *dtCSQL = (Date*) bindField->value;
 345                 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
 346                 dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
 347                 break;
 348             }
 349             case typeTime: {
 350                 Time *dtCSQL = (Time*) bindField->value;
 351                 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
 352                 dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 353                 break;
 354             }
 355             case typeTimeStamp: {
 356                 TimeStamp *dtCSQL = (TimeStamp*) bindField->value;
 357                 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bindField->targetvalue;
 358                 dtCSQL->setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 359                 dtCSQL->setTime(dtTarget->hour,dtTarget->minute,
 360                                 dtTarget->second, 0);// dtTarget->fraction);
 361                 break;
 362             }
 363             default: {
 364                 AllDataType::cachecopyVal(bindField->value, bindField->targetvalue, bindField->type, bindField->length, tdbname);
 365                 break;
 366             }
 367         }
 368     }
 369     return ptrToFirstField;
 370 }
 371
 372 void* SqlOdbcStatement::fetchAndPrint(bool SQL)
 373 {
 374     if (!isPrepared) return NULL;
 375     //int retValue = SQLFetch (hstmt);
 376     int retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLFetchPtr) (hstmt);
 377     if (retValue) return NULL;
 378     ListIterator iter = bindList.getIterator();
 379     BindSqlProjectField *bindField = NULL;
 380     void *ptrToFirstField = NULL;
 381     int icol=1;
 382     while (iter.hasElement())
 383     {
 384
 385         bindField = (BindSqlProjectField*)iter.nextElement();
 386         if (ptrToFirstField == NULL) ptrToFirstField=bindField->targetvalue;
 387         if(len[icol++] == SQL_NULL_DATA)
 388         {
 389             printf("NULL");
 390             continue;
 391         }
 392         switch(bindField->type)
 393         {
 394             case typeDate: {
 395                 Date dtCSQL;
 396                 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
 397                 dtCSQL.set(dtTarget->year,dtTarget->month,dtTarget->day);
 398                 AllDataType::printVal(&dtCSQL, bindField->type, bindField->length);
 399                 break;
 400             }
 401             case typeTime: {
 402                 Time dtCSQL;
 403                 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
 404                 dtCSQL.set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 405                 AllDataType::printVal(&dtCSQL, bindField->type, bindField->length);
 406                 break;
 407             }
 408             case typeTimeStamp: {
 409                 TimeStamp dtCSQL;
 410                 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bindField->targetvalue;
 411                 dtCSQL.setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 412                 dtCSQL.setTime(dtTarget->hour,dtTarget->minute,
 413                                 dtTarget->second, 0);//dtTarget->fraction);
 414                 AllDataType::printVal(&dtCSQL, bindField->type, bindField->length);
 415                 break;
 416             }
 417             default: {
 418                 AllDataType::printVal(bindField->targetvalue,
 419                                      bindField->type, bindField->length,tdbname);
 420                 break;
 421             }
 422         }
 423         printf("\t");
 424     }
 425     return ptrToFirstField;
 426 }
 427
 428 void* SqlOdbcStatement::next()
 429 {
 430     return fetch();
 431 }
 432
 433 DbRetVal SqlOdbcStatement::close()
 434 {
 435     if (!isPrepared) return OK;
 436     (*SqlOdbcConnection::ODBCFuncPtrs.SQLCloseCursorPtr)(hstmt);
 437     logFinest(Conf::logger, "CloseCursor");
 438     return OK;
 439 }
 440 bool SqlOdbcStatement::chechStmtType(char *buf)
 441 {
 442     char c;
 443     while(1)
 444     {
 445         c=*buf;
 446         if(c !=' ') break;
 447         buf++;
 448     }
 449     if (strncasecmp (buf, "SELECT", 6) == 0)  { return true;}
 450     return false;
 451 }
 452 void* SqlOdbcStatement::getFieldValuePtr( int pos )
 453 {
 454
 455    ListIterator biter = bindList.getIterator();
 456    BindSqlProjectField *elem = NULL;
 457     int count =0;
 458     while (biter.hasElement())
 459     {
 460         elem = (BindSqlProjectField*) biter.nextElement();
 461         if (count == pos)
 462         {
 463             if(elem->value == NULL)
 464              {
 465                  switch(elem->type)
 466                  {
 467                       case typeDate: {
 468                           Date *dtCSQL=(Date *) elem->jdbcBindValue;
 469                           DATE_STRUCT *dtTarget = (DATE_STRUCT*) elem->targetvalue;
 470                           dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
 471                           return dtCSQL;
 472                       }
 473                       case typeTime: {
 474                          Time *dtCSQL = (Time *) elem->jdbcBindValue;
 475                           TIME_STRUCT *dtTarget = (TIME_STRUCT*) elem->targetvalue;
 476                           dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 477                           return dtCSQL;
 478                       }
 479                       case typeTimeStamp: {
 480                           TimeStamp *dtCSQL= (TimeStamp *) elem->jdbcBindValue;
 481                           TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) elem->targetvalue;
 482                           dtCSQL->setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 483                           dtCSQL->setTime(dtTarget->hour,dtTarget->minute,dtTarget->second, 0);//dtTarget->fraction);
 484                           return dtCSQL;
 485                       }
 486                 }
 487              }
 488              return elem->targetvalue;
 489         }
 490         count++;
 491     }
 492     return NULL;
 493
 494 }
 495 void SqlOdbcStatement::getProjFieldType(int *data)
 496 {
 497     ListIterator biter = bindList.getIterator();
 498     BindSqlProjectField *elem = NULL;
 499     int i = 1;
 500     while (biter.hasElement())
 501     {
 502         elem = (BindSqlProjectField*) biter.nextElement();
 503         if((tdbname == postgres) && typeLongLong == elem->type)
 504             data[i++] = typeString;
 505         else
 506             data[i++] = elem->type;
 507     }
 508 }
 509
 510 void* SqlOdbcStatement::getFieldValuePtr( char *name )
 511 {
 512     ListIterator biter = bindList.getIterator();
 513     BindSqlProjectField *elem = NULL;
 514     int count =0;
 515     while (biter.hasElement())
 516     {
 517         elem = (BindSqlProjectField*) biter.nextElement();
 518         if ( strcpy(elem->fName,name) == 0 )
 519         {
 520             return elem->targetvalue;
 521         }
 522     }
 523     return NULL;
 524
 525 }
 526 int SqlOdbcStatement::noOfProjFields()
 527 {
 528     if (!isPrepared) return 0;
 529     return bindList.size();
 530 }
 531
 532 int SqlOdbcStatement::noOfParamFields()
 533 {
 534     if (!isPrepared) return 0;
 535     return paramList.size();
 536 }
 537
 538 DbRetVal SqlOdbcStatement::getProjFldInfo (int projpos, FieldInfo *&fInfo)
 539 {
 540     ListIterator biter = bindList.getIterator();
 541     BindSqlProjectField *elem = NULL;
 542     int count =0;
 543     while (biter.hasElement())
 544     {
 545         elem = (BindSqlProjectField*) biter.nextElement();
 546         if (count == projpos-1)
 547         {
 548             strcpy(fInfo->fldName, elem->fName);
 549             fInfo->length = elem->length;
 550             fInfo->type =elem->type;
 551             return OK;
 552         }
 553         count++;
 554     }
 555     return ErrNotFound;
 556 }
 557
 558 DbRetVal SqlOdbcStatement::getParamFldInfo (int parampos, FieldInfo *&fInfo)
 559 {
 560     ListIterator biter = paramList.getIterator();
 561     BindSqlField *elem = NULL;
 562     int count =0;
 563     while (biter.hasElement())
 564     {
 565         elem = (BindSqlField*) biter.nextElement();
 566         if (count == parampos-1)
 567         {
 568             fInfo->length = elem->length;
 569             fInfo->type =elem->type;
 570             return OK;
 571         }
 572         count++;
 573     }
 574     return ErrNotFound;
 575 }
 576
 577 DbRetVal SqlOdbcStatement::free()
 578 {
 579     if(!isPrepared) return OK;
 580     ListIterator biter = bindList.getIterator();
 581     BindSqlProjectField *elem = NULL;
 582     while (biter.hasElement())
 583     {
 584         elem = (BindSqlProjectField*) biter.nextElement();
 585         if(elem->targetvalue)
 586             ::free(elem->targetvalue);
 587         if(elem->jdbcBindValue)
 588             ::free(elem->jdbcBindValue);
 589         delete elem;
 590     }
 591     bindList.reset();
 592     ListIterator piter = paramList.getIterator();
 593     BindSqlField *bindField = NULL;
 594     while (piter.hasElement())
 595     {
 596         bindField = (BindSqlField*) piter.nextElement();
 597         ::free(bindField->value);
 598         ::free(bindField->targetvalue);
 599         delete bindField;
 600     }
 601     paramList.reset();
 602     if(len){ ::free(len); len = NULL;}
 603     if(paramlen) {::free(paramlen); paramlen = NULL;}
 604     (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_STMT, hstmt);
 605     isPrepared = false;
 606     isProcedureCallStmt = false;
 607     logFinest(Conf::logger, "Statement Freed");
 608     return OK;
 609 }
 610 void SqlOdbcStatement::setShortParam(int paramPos, short value)
 611 {
 612     if (!isPrepared) return ;
 613     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 614     AllDataType::convertToString(bindField->value, &value, typeShort, 0,tdbname);
 615     return;
 616 }
 617 void SqlOdbcStatement::setIntParam(int paramPos, int value)
 618 {
 619     if (!isPrepared) return ;
 620     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 621      //Note: MySQL Bug
 622      //Bug #1382     SQLDescribeParam returns the same type information, varchar
 623     AllDataType::convertToString(bindField->value, &value, typeInt,0,tdbname);
 624     return;
 625
 626 }
 627 void SqlOdbcStatement::setLongParam(int paramPos, long value)
 628 {
 629     if (!isPrepared) return ;
 630     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 631      //Note: MySQL Bug
 632      //Bug #1382     SQLDescribeParam returns the same type information, varchar
 633     AllDataType::convertToString(bindField->value, &value, typeLong,0,tdbname);
 634     return;
 635
 636 }
 637 void SqlOdbcStatement::setLongLongParam(int paramPos, long long value)
 638 {
 639     if (!isPrepared) return ;
 640     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 641     AllDataType::convertToString(bindField->value, &value, typeLongLong,0,tdbname);
 642     return;
 643 }
 644 void SqlOdbcStatement::setByteIntParam(int paramPos, ByteInt value)
 645 {
 646     if (!isPrepared) return ;
 647     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 648     AllDataType::convertToString(bindField->value, &value, typeByteInt,0,tdbname);
 649     return;
 650 }
 651 void SqlOdbcStatement::setFloatParam(int paramPos, float value)
 652 {
 653     if (!isPrepared) return ;
 654     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 655     AllDataType::convertToString(bindField->value, &value, typeFloat,0, tdbname);
 656     return;
 657 }
 658 void SqlOdbcStatement::setDoubleParam(int paramPos, double value)
 659 {
 660     if (!isPrepared) return ;
 661     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 662     AllDataType::convertToString(bindField->value, &value, typeDouble,0,tdbname);
 663     return;
 664 }
 665 void SqlOdbcStatement::setStringParam(int paramPos, char *value)
 666 {
 667     if (!isPrepared) return ;
 668     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 669     if (bindField->type != typeString) return;
 670     char *dest = (char*)bindField->value;
 671     strncpy(dest, value, bindField->length);
 672     dest[ bindField->length - 1] ='\0';
 673     return;
 674 }
 675 void SqlOdbcStatement::setDateParam(int paramPos, Date value)
 676 {
 677     if (!isPrepared) return ;
 678     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 679     AllDataType::convertToString(bindField->value, &value, typeDate,0,tdbname);
 680     return;
 681 }
 682 void SqlOdbcStatement::setTimeParam(int paramPos, Time value)
 683 {
 684     if (!isPrepared) return ;
 685     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 686     AllDataType::convertToString(bindField->value, &value, typeTime,0,tdbname);
 687     return;
 688 }
 689 void SqlOdbcStatement::setTimeStampParam(int paramPos, TimeStamp value)
 690 {
 691     if (!isPrepared) return ;
 692     BindSqlField *bindField = (BindSqlField*) paramList.get(paramPos);
 693     AllDataType::convertToString(bindField->value, &value, typeTimeStamp,0, tdbname);
 694     return;
 695 }
 696 void SqlOdbcStatement::setBinaryParam(int paramPos, void *value, int length)
 697 {
 698     if (!isPrepared) return;
 699     BindSqlField *bindField = (BindSqlField*) paramList.get(paramPos);
 700     AllDataType::convertToString(bindField->value, value, typeBinary, bindField->length,tdbname);
 701     return;
 702 }
 703
 704 void SqlOdbcStatement::getPrimaryKeyFieldName(char *tablename, char *pkfieldname)
 705 {
 706     if (pkfieldname == NULL) return;
 707     SQLCHAR outstr[1024];
 708     SQLSMALLINT outstrlen;
 709     int retValue =0;
 710     SQLHENV henv;
 711     SQLHDBC hdbc;
 712     SQLHSTMT hstmt;
 713     SqlOdbcConnection *conn = (SqlOdbcConnection*)con;
 714     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLAllocHandlePtr) (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 715     if (retValue) {
 716         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 717     }
 718     (*SqlOdbcConnection::ODBCFuncPtrs.SQLSetEnvAttrPtr)(henv, SQL_ATTR_ODBC_VERSION,(void *) SQL_OV_ODBC3, 0);
 719     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLAllocHandlePtr) (SQL_HANDLE_DBC, henv, &hdbc);
 720     if (retValue) {
 721         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 722     }
 723     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLDriverConnectPtr)(hdbc, NULL, (SQLCHAR*)conn->dsn, SQL_NTS, outstr, sizeof(outstr), &outstrlen,SQL_DRIVER_NOPROMPT);
 724     if (SQL_SUCCEEDED(retValue)) {
 725         printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", conn->dsn);
 726     } else {
 727         printError(ErrSysInit, "Failed to connect to target database\n");
 728     }
 729     retValue=(*SqlOdbcConnection::ODBCFuncPtrs.SQLAllocHandlePtr) (SQL_HANDLE_STMT, hdbc, &hstmt);
 730     if (retValue) {
 731         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 732     }
 733     char columnname[IDENTIFIER_LENGTH];
 734     retValue=(*SqlOdbcConnection::ODBCFuncPtrs.SQLPrimaryKeysPtr)(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, (SQLCHAR*) tablename, SQL_NTS);
 735     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLBindColPtr)(hstmt, 4, SQL_C_CHAR,columnname, 129,NULL);
 736     bool isPkExists=false;
 737     if((*SqlOdbcConnection::ODBCFuncPtrs.SQLFetchPtr)( hstmt ) == SQL_SUCCESS)
 738     {
 739         Util::str_tolower(columnname);
 740         strcpy(pkfieldname, columnname);
 741         isPkExists = true;
 742     }
 743     tdbname = conn->getTrDbName();
 744     (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_STMT, hstmt);
 745     (*SqlOdbcConnection::ODBCFuncPtrs.SQLDisconnectPtr)(hdbc);
 746     (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_DBC, hdbc);
 747     (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_ENV, henv);
 748     return;
 749 }
 750
 751 bool SqlOdbcStatement::isFldNull(int pos)
 752 {
 753     if( len[pos] == SQL_NULL_DATA )
 754         return true;
 755     else
 756         return false;
 757 }
 758 bool SqlOdbcStatement::isFldNull(char *name)
 759 {
 760     ListIterator iter = bindList.getIterator();
 761     BindSqlProjectField *bindField = NULL;
 762     int pos=1;
 763     while (iter.hasElement())
 764     {
 765         bindField = (BindSqlProjectField*)iter.nextElement();
 766         if(strcmp(name,bindField->fName)==0)
 767         {
 768             break;
 769         }
 770         pos++;
 771     }
 772     if( len[pos] == SQL_NULL_DATA )
 773         return true;
 774     else
 775         return false;
 776 }
 777
 778 void SqlOdbcStatement::setNull(int pos)
 779 {
 780     paramlen[pos] = SQL_NULL_DATA ;
 781 }
 782
 783 bool SqlOdbcStatement::isTableExists( char *name)
 784 {
 785
 786     SQLCHAR outstr[1024];
 787     SQLSMALLINT outstrlen;
 788     int retValue =0;
 789     SQLHENV henv;
 790     SQLHDBC hdbc;
 791     SQLHSTMT hstmt;
 792     SqlOdbcConnection *conn = (SqlOdbcConnection*)con;
 793     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLAllocHandlePtr) (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
 794     if (retValue) {
 795         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 796     }
 797
 798     (*SqlOdbcConnection::ODBCFuncPtrs.SQLSetEnvAttrPtr)(henv, SQL_ATTR_ODBC_VERSION,(void *) SQL_OV_ODBC3, 0);
 799     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLAllocHandlePtr) (SQL_HANDLE_DBC, henv, &hdbc);
 800     if (retValue) {
 801         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 802     }
 803     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLDriverConnectPtr)(hdbc, NULL, (SQLCHAR*)conn->dsn, SQL_NTS, outstr, sizeof(outstr), &outstrlen,SQL_DRIVER_NOPROMPT);
 804     if (SQL_SUCCEEDED(retValue)) {
 805         printDebug(DM_Gateway, "Connected to target database using dsn = %s\n", conn->dsn);
 806     } else {
 807         printError(ErrSysInit, "Failed to connect to target database\n");
 808     }
 809
 810     retValue=(*SqlOdbcConnection::ODBCFuncPtrs.SQLAllocHandlePtr) (SQL_HANDLE_STMT, hdbc, &hstmt);
 811     if (retValue) {
 812         printError(ErrSysInit, "Unable to allocate ODBC handle \n");
 813     }
 814     char tablename[IDENTIFIER_LENGTH];
 815     //retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLTablesPtr)(hstmt, (SQLCHAR*)"test", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"", SQL_NTS, (SQLCHAR*)"TABLE", SQL_NTS );
 816     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLTablesPtr)(hstmt, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS, NULL, SQL_NTS );
 817     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLBindColPtr)(hstmt,3,SQL_C_CHAR,tablename,sizeof(tablename),NULL);
 818     while(SQL_SUCCEEDED(retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLFetchPtr)(hstmt)))
 819     {
 820         if(strcmp(tablename,name)==0){
 821             (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_STMT, hstmt);
 822             (*SqlOdbcConnection::ODBCFuncPtrs.SQLDisconnectPtr)(hdbc);
 823             (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_DBC, hdbc);
 824             (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_ENV, henv);
 825             return true;
 826         }
 827     }
 828
 829     (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_STMT, hstmt);
 830     (*SqlOdbcConnection::ODBCFuncPtrs.SQLDisconnectPtr)(hdbc);
 831     (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_DBC, hdbc);
 832     (*SqlOdbcConnection::ODBCFuncPtrs.SQLFreeHandlePtr)(SQL_HANDLE_ENV, henv);
 833     return false;
 834 }
 835 void SqlOdbcStatement::setErrorState(SQLHSTMT hStmt)
 836 {
 837     SQLINTEGER   i = 0;
 838     SQLINTEGER   native;
 839     SQLCHAR      state[ 7 ];
 840     SQLCHAR      text[256];
 841     SQLSMALLINT  len;
 842     SQLRETURN    ret;
 843     ret = (*SqlOdbcConnection::ODBCFuncPtrs.SQLGetDiagRecPtr)(SQL_HANDLE_STMT, hStmt, ++i,
 844                                    state, &native, text, sizeof(text), &len );
 845
 846     if (SQL_SUCCEEDED(ret)){
 847         printf("%s:%ld:%ld:%s\n", state, i, native, text);
 848         strcpy(errState,(char*)state);
 849     }
 850 }
 851
 852 DbRetVal SqlOdbcStatement::resolveForBindField(SQLHSTMT hstmt)
 853 {
 854     short totalFields=0;
 855     int retValue = 0;
 856     retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLNumResultColsPtr) (hstmt, &totalFields);
 857     if (retValue){   return ErrBadCall;  }
 858     BindSqlProjectField *bindProjField = NULL;
 859     UWORD                   icol;
 860     UCHAR                   colName[IDENTIFIER_LENGTH];
 861     SWORD                   colNameMax;
 862     SWORD                   nameLength;
 863     SWORD                   colType;
 864     SQLULEN                 colLength;
 865     SWORD                   scale;
 866     SWORD                   nullable;
 867     icol = 1; colNameMax = IDENTIFIER_LENGTH;
 868     if(totalFields != 0)
 869     {
 870         if(isProcedureCallStmt) isSelStmt = true;
 871         len = (SQLINTEGER *)malloc((totalFields+1)*sizeof(SQLINTEGER));
 872         for(int i=0;i<=totalFields;i++) { len[i] = SQL_NTS ;}
 873     }
 874     while (icol <= totalFields)
 875     {
 876         retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLDescribeColPtr)(hstmt, icol, colName, colNameMax,
 877                    &nameLength, &colType, &colLength,
 878                    &scale, &nullable);
 879         if (retValue) return ErrBadCall;
 880         bindProjField = new BindSqlProjectField();
 881         strcpy(bindProjField->fName, (char*)colName);
 882         bindProjField->type = AllDataType::convertFromSQLType(colType,colLength,scale,tdbname);
 883         bindProjField->length = AllDataType::size(bindProjField->type, colLength+1);
 884         bindProjField->value = NULL;
 885         bindProjField->targetvalue = NULL;
 886         int fieldsize =0;
 887         if(tdbname==postgres && -5 == colType) {
 888             bindProjField->targetvalue = AllDataType::alloc(typeString, 128);
 889             retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLBindColPtr)(hstmt, icol,
 890                               AllDataType::convertToSQLType(typeString),
 891                               bindProjField->targetvalue, 128, &len[icol]);
 892         }else{
 893              switch(bindProjField->type)
 894              {
 895                 case typeString:
 896                 case typeVarchar:
 897                     fieldsize = colLength+1;
 898                     bindProjField->targetvalue = malloc(fieldsize);
 899                     break;
 900                 case typeDate:
 901                     fieldsize = sizeof(DATE_STRUCT);
 902                     bindProjField->targetvalue = malloc(sizeof(DATE_STRUCT));
 903                     bindProjField->jdbcBindValue = AllDataType::alloc(typeDate);
 904                     break;
 905                 case typeTime:
 906                     fieldsize = sizeof(TIME_STRUCT);
 907                     bindProjField->targetvalue = malloc(sizeof(TIME_STRUCT));
 908                     bindProjField->jdbcBindValue = AllDataType::alloc(typeTime);
 909                     break;
 910                 case typeTimeStamp:
 911                     fieldsize = sizeof(TIMESTAMP_STRUCT);
 912                     bindProjField->targetvalue = malloc(sizeof(TIMESTAMP_STRUCT));
 913                     bindProjField->jdbcBindValue = AllDataType::alloc(typeTimeStamp);
 914                     break;
 915                 default:
 916                     bindProjField->targetvalue = AllDataType::alloc(bindProjField->type, bindProjField->length);
 917             }
 918         retValue = (*SqlOdbcConnection::ODBCFuncPtrs.SQLBindColPtr)(hstmt, icol,
 919                               AllDataType::convertToSQL_C_Type(bindProjField->type,tdbname),
 920                               bindProjField->targetvalue, fieldsize, &len[icol]);
 921         }
 922         if (retValue) return ErrBadCall;
 923         bindList.append(bindProjField);
 924         icol++;
 925     }
 926     totalFld = totalFields;
 927     return OK;
 928 }
 929