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