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::prepare(char *stmtstr)
  27 {
  28     DbRetVal rv = OK;
  29     if (innerStmt) rv = ErrBadCall;
  30     if (rv != OK) return rv;
  31     int retValue;
  32     isPrepared = false;
  33     SqlOdbcConnection *conn = (SqlOdbcConnection*)con;
  34     retValue=SQLAllocHandle (SQL_HANDLE_STMT, conn->dbHdl, &hstmt);
  35     if (retValue) return ErrBadCall;
  36     retValue = SQLPrepare (hstmt, (unsigned char *) stmtstr, SQL_NTS);
  37     //printf("retvalue from prepare is %d %s\n", retValue, stmtstr);
  38     if (retValue) return ErrBadCall;
  39     isPrepared = true;
  40     short totalFields=0;
  41     retValue = SQLNumResultCols (hstmt, &totalFields);
  42     //printf("Total project fields %d\n", totalFields);
  43     if (retValue) return ErrBadCall;
  44     BindSqlProjectField *bindProjField = NULL;
  45     UWORD                   icol;
  46     UCHAR                   colName[IDENTIFIER_LENGTH];
  47     SWORD                   colNameMax;
  48     SWORD                   nameLength;
  49     SWORD                   colType;
  50     SQLULEN                 colLength;
  51     SWORD                   scale;
  52     SWORD                   nullable;
  53     icol = 1; colNameMax = IDENTIFIER_LENGTH;
  54     while (icol <= totalFields)
  55     {
  56         retValue = SQLDescribeCol(hstmt, icol, colName, colNameMax,
  57                    &nameLength, &colType, &colLength,
  58                    &scale, &nullable);
  59         if (retValue) return ErrBadCall;
  60
  61         bindProjField = new BindSqlProjectField();
  62         strncpy(bindProjField->fName, (char*)colName, IDENTIFIER_LENGTH);
  63         bindProjField->fName[IDENTIFIER_LENGTH] = '\0';
  64         bindProjField->type = AllDataType::convertFromSQLType(colType);
  65         bindProjField->length = colLength;
  66         bindProjField->value = NULL;
  67         bindProjField->targetvalue = NULL;
  68         int fieldsize =0;
  69         switch(bindProjField->type)
  70         {
  71             case typeString:
  72                 fieldsize = colLength;
  73                 bindProjField->targetvalue = malloc(fieldsize);
  74                 break;
  75             case typeDate:
  76                 fieldsize = sizeof(DATE_STRUCT);
  77                 bindProjField->targetvalue = malloc(sizeof(DATE_STRUCT));
  78                 break;
  79             case typeTime:
  80                 fieldsize = sizeof(TIME_STRUCT);
  81                 bindProjField->targetvalue = malloc(sizeof(TIME_STRUCT));
  82                 break;
  83             case typeTimeStamp:
  84                 fieldsize = sizeof(TIMESTAMP_STRUCT);
  85                 bindProjField->targetvalue = malloc(sizeof(TIMESTAMP_STRUCT));
  86                 break;
  87             default:
  88                 bindProjField->targetvalue = AllDataType::alloc(bindProjField->type, bindProjField->length);
  89         }
  90         retValue = SQLBindCol(hstmt, icol,
  91                               AllDataType::convertToSQLType(bindProjField->type),
  92                               bindProjField->targetvalue, fieldsize, NULL);
  93         if (retValue) return ErrBadCall;
  94         bindList.append(bindProjField);
  95         //printf("appending to bindlist %d\n", icol);
  96         icol++;
  97
  98     }
  99     totalFields =0;
 100     BindSqlField *bindField;
 101     retValue = SQLNumParams (hstmt, &totalFields);
 102     if (retValue) return ErrBadCall;
 103     //printf("SQLNUMParams returned %d\n", totalFields);
 104     icol = 1; colNameMax = IDENTIFIER_LENGTH;
 105     SWORD                   cType=0;
 106     SQLULEN                 cLength=0;
 107     while (icol <= totalFields)
 108     {
 109         retValue = SQLDescribeParam(hstmt, icol, &cType, &cLength,
 110                    &scale, &nullable);
 111         //Note: MySQL Bug
 112         //Bug #1382     SQLDescribeParam returns the same type information
 113
 114         if (retValue) return ErrBadCall;
 115
 116         bindField = new BindSqlField();
 117         bindField->type = AllDataType::convertFromSQLType(cType);
 118         bindField->length = cLength;
 119         bindField->value =  AllDataType::alloc(bindField->type, cLength);
 120         bindField->targetvalue = NULL;
 121         int fieldsize =0;
 122         switch(bindField->type)
 123         {
 124             case typeString:
 125                 fieldsize = cLength;
 126                 bindField->targetvalue = malloc(fieldsize);
 127                 break;
 128             case typeDate:
 129                 fieldsize = sizeof(DATE_STRUCT);
 130                 bindField->targetvalue = malloc(sizeof(DATE_STRUCT));
 131                 break;
 132             case typeTime:
 133                 fieldsize = sizeof(TIME_STRUCT);
 134                 bindField->targetvalue = malloc(sizeof(TIME_STRUCT));
 135                 break;
 136             case typeTimeStamp:
 137                 fieldsize = sizeof(TIMESTAMP_STRUCT);
 138                 bindField->targetvalue = malloc(sizeof(TIMESTAMP_STRUCT));
 139                 break;
 140             default:
 141                 bindField->targetvalue = AllDataType::alloc(bindField->type, cLength);
 142                 break;
 143
 144         }
 145         retValue = SQLBindParameter(hstmt, icol, SQL_PARAM_INPUT,
 146                       AllDataType::convertToSQL_C_Type(bindField->type),
 147                       cType, fieldsize, scale, bindField->targetvalue,
 148                       fieldsize, NULL);
 149         if (retValue) return ErrBadCall;
 150         //printf("adding to param list\n");
 151         paramList.append(bindField);
 152         icol++;
 153     }
 154     //TODO::deallocate memory and remove elements from list in case of any
 155     //failure in any of the above ODBC functions
 156     return OK;
 157 }
 158
 159 bool SqlOdbcStatement::isSelect()
 160 {
 161     //TODO
 162     return false;
 163 }
 164
 165 DbRetVal SqlOdbcStatement::execute(int &rowsAffected)
 166 {
 167     DbRetVal rv = OK;
 168     if (!isPrepared) return OK;
 169     //printf("Adapter calling execute\n");
 170     ListIterator iter = paramList.getIterator();
 171     BindSqlField *bindField = NULL;
 172     while (iter.hasElement())
 173     {
 174         bindField = (BindSqlField*)iter.nextElement();
 175         switch(bindField->type)
 176         {
 177             case typeDate: {
 178                 Date *dtCSQL = (Date*) bindField->value;
 179                 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
 180                 dtTarget->year= dtCSQL->year();
 181                 dtTarget->month= dtCSQL->month();
 182                 dtTarget->day = dtCSQL->dayOfMonth();
 183                 break;
 184             }
 185             case typeTime: {
 186                 Time *dtCSQL = (Time*) bindField->value;
 187                 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
 188                 dtTarget->hour = dtCSQL->hours();
 189                 dtTarget->minute = dtCSQL->minutes();
 190                 dtTarget->second = dtCSQL->seconds();
 191                 break;
 192             }
 193             case typeTimeStamp: {
 194                 TimeStamp *dtCSQL = (TimeStamp*) bindField->value;
 195                 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bindField->targetvalue;
 196                 dtTarget->year= dtCSQL->year();
 197                 dtTarget->month= dtCSQL->month();
 198                 dtTarget->day = dtCSQL->dayOfMonth();
 199                 dtTarget->hour = dtCSQL->hours();
 200                 dtTarget->minute = dtCSQL->minutes();
 201                 dtTarget->second = dtCSQL->seconds();
 202                 break;
 203             }
 204             default: {
 205                 AllDataType::copyVal(bindField->targetvalue, bindField->value,
 206                                      bindField->type, bindField->length);
 207                 break;
 208             }
 209         }
 210     }
 211     int retValue = SQLExecute (hstmt);
 212     //printf("EXECUTE returned %d\n", retValue);
 213     if (retValue) return ErrBadCall;
 214     return rv;
 215 }
 216
 217 DbRetVal SqlOdbcStatement::bindParam(int pos, void* value)
 218 {
 219     DbRetVal rv = OK;
 220     printError(ErrWarning, "Deprecated. Use setParamXXX instead\n");
 221     return rv;
 222 }
 223
 224 DbRetVal SqlOdbcStatement::bindField(int pos, void* value)
 225 {
 226     if (!isPrepared) return OK;
 227     BindSqlProjectField *bindField = (BindSqlProjectField*)bindList.get(pos);
 228     bindField->value = value;
 229     return OK;
 230 }
 231 void* SqlOdbcStatement::fetch()
 232 {
 233     if (!isPrepared) return NULL;
 234     int retValue = SQLFetch (hstmt);
 235     //printf("SQLFETCH return value %d\n", retValue);
 236     if (retValue) return NULL;
 237     ListIterator iter = bindList.getIterator();
 238     BindSqlProjectField *bindField = NULL;
 239     void *ptrToFirstField = NULL;
 240     while (iter.hasElement())
 241     {
 242         bindField = (BindSqlProjectField*)iter.nextElement();
 243         switch(bindField->type)
 244         {
 245             case typeDate: {
 246                 Date *dtCSQL = (Date*) bindField->value;
 247                 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
 248                 dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
 249                 break;
 250             }
 251             case typeTime: {
 252                 Time *dtCSQL = (Time*) bindField->value;
 253                 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
 254                 dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 255                 break;
 256             }
 257             case typeTimeStamp: {
 258                 TimeStamp *dtCSQL = (TimeStamp*) bindField->value;
 259                 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bindField->targetvalue;
 260                 dtCSQL->setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 261                 dtCSQL->setTime(dtTarget->hour,dtTarget->minute,
 262                                 dtTarget->second, dtTarget->fraction);
 263                 break;
 264             }
 265             default: {
 266                 AllDataType::copyVal(bindField->value, bindField->targetvalue,
 267                                      bindField->type, bindField->length);
 268                 break;
 269             }
 270         }
 271         if (ptrToFirstField == NULL) ptrToFirstField=bindField->value;
 272     }
 273     //printf("ptrToFirstField is %x\n", ptrToFirstField);
 274     return ptrToFirstField;
 275 }
 276
 277 void* SqlOdbcStatement::fetchAndPrint()
 278 {
 279     if (!isPrepared) return NULL;
 280     int retValue = SQLFetch (hstmt);
 281     if (retValue) return NULL;
 282     ListIterator iter = bindList.getIterator();
 283     BindSqlProjectField *bindField = NULL;
 284     void *ptrToFirstField = NULL;
 285     while (iter.hasElement())
 286     {
 287         bindField = (BindSqlProjectField*)iter.nextElement();
 288         switch(bindField->type)
 289         {
 290             case typeDate: {
 291                 Date dtCSQL;
 292                 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
 293                 dtCSQL.set(dtTarget->year,dtTarget->month,dtTarget->day);
 294                 AllDataType::printVal(&dtCSQL, bindField->type, bindField->length);
 295                 break;
 296             }
 297             case typeTime: {
 298                 Time dtCSQL;
 299                 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
 300                 dtCSQL.set(dtTarget->hour,dtTarget->minute,dtTarget->second);
 301                 AllDataType::printVal(&dtCSQL, bindField->type, bindField->length);
 302                 break;
 303             }
 304             case typeTimeStamp: {
 305                 TimeStamp dtCSQL;
 306                 TIMESTAMP_STRUCT *dtTarget = (TIMESTAMP_STRUCT*) bindField->targetvalue;
 307                 dtCSQL.setDate(dtTarget->year,dtTarget->month,dtTarget->day);
 308                 dtCSQL.setTime(dtTarget->hour,dtTarget->minute,
 309                                 dtTarget->second, dtTarget->fraction);
 310                 AllDataType::printVal(&dtCSQL, bindField->type, bindField->length);
 311                 break;
 312             }
 313             default: {
 314                 AllDataType::printVal(bindField->targetvalue,
 315                                      bindField->type, bindField->length);
 316                 break;
 317             }
 318         }
 319         if (ptrToFirstField == NULL) ptrToFirstField=bindField->targetvalue;
 320     }
 321
 322     return ptrToFirstField;
 323 }
 324
 325 void* SqlOdbcStatement::next()
 326 {
 327     return fetch();
 328 }
 329
 330 DbRetVal SqlOdbcStatement::close()
 331 {
 332     if (!isPrepared) return OK;
 333     SQLCloseCursor(hstmt);
 334     return OK;
 335 }
 336
 337 void* SqlOdbcStatement::getFieldValuePtr( int pos )
 338 {
 339     return NULL;
 340 }
 341
 342 int SqlOdbcStatement::noOfProjFields()
 343 {
 344     if (!isPrepared) return 0;
 345     return bindList.size();
 346 }
 347
 348 int SqlOdbcStatement::noOfParamFields()
 349 {
 350     if (!isPrepared) return 0;
 351     return paramList.size();
 352 }
 353
 354 DbRetVal SqlOdbcStatement::getProjFldInfo (int projpos, FieldInfo *&fInfo)
 355 {
 356     ListIterator biter = bindList.getIterator();
 357     BindSqlProjectField *elem = NULL;
 358     int count =0;
 359     while (biter.hasElement())
 360     {
 361         elem = (BindSqlProjectField*) biter.nextElement();
 362         if (count == projpos)
 363         {
 364             strcpy(fInfo->fldName, elem->fName);
 365             fInfo->length = elem->length;
 366             fInfo->type =elem->type;
 367             return OK;
 368         }
 369         count++;
 370     }
 371     return ErrNotFound;
 372 }
 373
 374 DbRetVal SqlOdbcStatement::getParamFldInfo (int parampos, FieldInfo *&fInfo)
 375 {
 376     ListIterator biter = paramList.getIterator();
 377     BindSqlField *elem = NULL;
 378     int count =0;
 379     while (biter.hasElement())
 380     {
 381         elem = (BindSqlField*) biter.nextElement();
 382         if (count == parampos)
 383         {
 384             fInfo->length = elem->length;
 385             fInfo->type =elem->type;
 386             return OK;
 387         }
 388         count++;
 389     }
 390     return ErrNotFound;
 391 }
 392
 393 DbRetVal SqlOdbcStatement::free()
 394 {
 395     isPrepared = false;
 396     ListIterator biter = bindList.getIterator();
 397     BindSqlProjectField *elem = NULL;
 398     while (biter.hasElement())
 399     {
 400         elem = (BindSqlProjectField*) biter.nextElement();
 401         ::free(elem->targetvalue);
 402         delete elem;
 403     }
 404     bindList.reset();
 405     ListIterator piter = paramList.getIterator();
 406     BindSqlField *bindField = NULL;
 407     while (piter.hasElement())
 408     {
 409         bindField = (BindSqlField*) piter.nextElement();
 410         ::free(bindField->value);
 411         ::free(bindField->targetvalue);
 412         delete bindField;
 413     }
 414     paramList.reset();
 415
 416     SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 417     return OK;
 418 }
 419 void SqlOdbcStatement::setShortParam(int paramPos, short value)
 420 {
 421     if (!isPrepared) return ;
 422     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 423     if (bindField->type != typeShort) return;
 424     *(short*)(bindField->value) = value;
 425     return;
 426 }
 427 void SqlOdbcStatement::setIntParam(int paramPos, int value)
 428 {
 429     if (!isPrepared) return ;
 430     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 431     /*if (bindField->type != typeInt) return;
 432     *(int*)(bindField->value) = value;
 433     */
 434
 435      //Note: MySQL Bug
 436      //Bug #1382     SQLDescribeParam returns the same type information, varchar
 437     AllDataType::convertToString(bindField->value, &value, typeInt);
 438
 439     return;
 440
 441 }
 442 void SqlOdbcStatement::setLongParam(int paramPos, long value)
 443 {
 444     if (!isPrepared) return ;
 445     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 446     //if (bindField->type != typeLong) return;
 447     //*(long*)(bindField->value) = value;
 448      //Note: MySQL Bug
 449      //Bug #1382     SQLDescribeParam returns the same type information, varchar
 450     AllDataType::convertToString(bindField->value, &value, typeLong);
 451     return;
 452
 453 }
 454 void SqlOdbcStatement::setLongLongParam(int paramPos, long long value)
 455 {
 456     if (!isPrepared) return ;
 457     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 458     //if (bindField->type != typeLongLong) return;
 459     //*(long long*)(bindField->value) = value;
 460     AllDataType::convertToString(bindField->value, &value, typeLongLong);
 461     return;
 462 }
 463 void SqlOdbcStatement::setByteIntParam(int paramPos, ByteInt value)
 464 {
 465     if (!isPrepared) return ;
 466     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 467     //if (bindField->type != typeByteInt) return;
 468     //*(char*)(bindField->value) = value;
 469     AllDataType::convertToString(bindField->value, &value, typeByteInt);
 470
 471 }
 472 void SqlOdbcStatement::setFloatParam(int paramPos, float value)
 473 {
 474     if (!isPrepared) return ;
 475     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 476     //if (bindField->type != typeFloat) return;
 477     //*(float*)(bindField->value) = value;
 478     AllDataType::convertToString(bindField->value, &value, typeFloat);
 479 }
 480 void SqlOdbcStatement::setDoubleParam(int paramPos, double value)
 481 {
 482     if (!isPrepared) return ;
 483     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 484     //if (bindField->type != typeDouble) return;
 485     //*(double*)(bindField->value) = value;
 486     AllDataType::convertToString(bindField->value, &value, typeDouble);
 487
 488 }
 489 void SqlOdbcStatement::setStringParam(int paramPos, char *value)
 490 {
 491     if (!isPrepared) return ;
 492     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 493     if (bindField->type != typeString) return;
 494     char *dest = (char*)bindField->value;
 495     strncpy(dest, value, bindField->length);
 496     dest[ bindField->length - 1] ='\0';
 497     return;
 498 }
 499 void SqlOdbcStatement::setDateParam(int paramPos, Date value)
 500 {
 501     if (!isPrepared) return ;
 502     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 503     //if (bindField->type != typeDate) return;
 504     //*(Date*)(bindField->value) = value;
 505     AllDataType::convertToString(bindField->value, &value, typeDate);
 506     //printf("Param value contains %s\n", bindField->value);
 507
 508 }
 509 void SqlOdbcStatement::setTimeParam(int paramPos, Time value)
 510 {
 511     if (!isPrepared) return ;
 512     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 513     //if (bindField->type != typeTime) return;
 514     //*(Time*)(bindField->value) = value;
 515     AllDataType::convertToString(bindField->value, &value, typeTime);
 516
 517 }
 518 void SqlOdbcStatement::setTimeStampParam(int paramPos, TimeStamp value)
 519 {
 520     if (!isPrepared) return ;
 521     BindSqlField *bindField = (BindSqlField*) paramList.get(paramPos);
 522     //if (bindField->type != typeTimeStamp) return;
 523     //*(TimeStamp*)(bindField->value) = value;
 524     AllDataType::convertToString(bindField->value, &value, typeTimeStamp);
 525     //printf("Param value contains %s\n", bindField->value);
 526 }