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