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         printf("\t");
 317     }
 318
 319     return ptrToFirstField;
 320 }
 321
 322 void* SqlOdbcStatement::next()
 323 {
 324     return fetch();
 325 }
 326
 327 DbRetVal SqlOdbcStatement::close()
 328 {
 329     if (!isPrepared) return OK;
 330     SQLCloseCursor(hstmt);
 331     return OK;
 332 }
 333
 334 void* SqlOdbcStatement::getFieldValuePtr( int pos )
 335 {
 336     return NULL;
 337 }
 338
 339 int SqlOdbcStatement::noOfProjFields()
 340 {
 341     if (!isPrepared) return 0;
 342     return bindList.size();
 343 }
 344
 345 int SqlOdbcStatement::noOfParamFields()
 346 {
 347     if (!isPrepared) return 0;
 348     return paramList.size();
 349 }
 350
 351 DbRetVal SqlOdbcStatement::getProjFldInfo (int projpos, FieldInfo *&fInfo)
 352 {
 353     ListIterator biter = bindList.getIterator();
 354     BindSqlProjectField *elem = NULL;
 355     int count =0;
 356     while (biter.hasElement())
 357     {
 358         elem = (BindSqlProjectField*) biter.nextElement();
 359         if (count == projpos)
 360         {
 361             strcpy(fInfo->fldName, elem->fName);
 362             fInfo->length = elem->length;
 363             fInfo->type =elem->type;
 364             return OK;
 365         }
 366         count++;
 367     }
 368     return ErrNotFound;
 369 }
 370
 371 DbRetVal SqlOdbcStatement::getParamFldInfo (int parampos, FieldInfo *&fInfo)
 372 {
 373     ListIterator biter = paramList.getIterator();
 374     BindSqlField *elem = NULL;
 375     int count =0;
 376     while (biter.hasElement())
 377     {
 378         elem = (BindSqlField*) biter.nextElement();
 379         if (count == parampos)
 380         {
 381             fInfo->length = elem->length;
 382             fInfo->type =elem->type;
 383             return OK;
 384         }
 385         count++;
 386     }
 387     return ErrNotFound;
 388 }
 389
 390 DbRetVal SqlOdbcStatement::free()
 391 {
 392     isPrepared = false;
 393     ListIterator biter = bindList.getIterator();
 394     BindSqlProjectField *elem = NULL;
 395     while (biter.hasElement())
 396     {
 397         elem = (BindSqlProjectField*) biter.nextElement();
 398         ::free(elem->targetvalue);
 399         delete elem;
 400     }
 401     bindList.reset();
 402     ListIterator piter = paramList.getIterator();
 403     BindSqlField *bindField = NULL;
 404     while (piter.hasElement())
 405     {
 406         bindField = (BindSqlField*) piter.nextElement();
 407         ::free(bindField->value);
 408         ::free(bindField->targetvalue);
 409         delete bindField;
 410     }
 411     paramList.reset();
 412
 413     SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 414     return OK;
 415 }
 416 void SqlOdbcStatement::setShortParam(int paramPos, short value)
 417 {
 418     if (!isPrepared) return ;
 419     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 420     if (bindField->type != typeShort) return;
 421     *(short*)(bindField->value) = value;
 422     return;
 423 }
 424 void SqlOdbcStatement::setIntParam(int paramPos, int value)
 425 {
 426     if (!isPrepared) return ;
 427     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 428     /*if (bindField->type != typeInt) return;
 429     *(int*)(bindField->value) = value;
 430     */
 431
 432      //Note: MySQL Bug
 433      //Bug #1382     SQLDescribeParam returns the same type information, varchar
 434     AllDataType::convertToString(bindField->value, &value, typeInt);
 435
 436     return;
 437
 438 }
 439 void SqlOdbcStatement::setLongParam(int paramPos, long value)
 440 {
 441     if (!isPrepared) return ;
 442     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 443     //if (bindField->type != typeLong) return;
 444     //*(long*)(bindField->value) = value;
 445      //Note: MySQL Bug
 446      //Bug #1382     SQLDescribeParam returns the same type information, varchar
 447     AllDataType::convertToString(bindField->value, &value, typeLong);
 448     return;
 449
 450 }
 451 void SqlOdbcStatement::setLongLongParam(int paramPos, long long value)
 452 {
 453     if (!isPrepared) return ;
 454     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 455     //if (bindField->type != typeLongLong) return;
 456     //*(long long*)(bindField->value) = value;
 457     AllDataType::convertToString(bindField->value, &value, typeLongLong);
 458     return;
 459 }
 460 void SqlOdbcStatement::setByteIntParam(int paramPos, ByteInt value)
 461 {
 462     if (!isPrepared) return ;
 463     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 464     //if (bindField->type != typeByteInt) return;
 465     //*(char*)(bindField->value) = value;
 466     AllDataType::convertToString(bindField->value, &value, typeByteInt);
 467
 468 }
 469 void SqlOdbcStatement::setFloatParam(int paramPos, float value)
 470 {
 471     if (!isPrepared) return ;
 472     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 473     //if (bindField->type != typeFloat) return;
 474     //*(float*)(bindField->value) = value;
 475     AllDataType::convertToString(bindField->value, &value, typeFloat);
 476 }
 477 void SqlOdbcStatement::setDoubleParam(int paramPos, double value)
 478 {
 479     if (!isPrepared) return ;
 480     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 481     //if (bindField->type != typeDouble) return;
 482     //*(double*)(bindField->value) = value;
 483     AllDataType::convertToString(bindField->value, &value, typeDouble);
 484
 485 }
 486 void SqlOdbcStatement::setStringParam(int paramPos, char *value)
 487 {
 488     if (!isPrepared) return ;
 489     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 490     if (bindField->type != typeString) return;
 491     char *dest = (char*)bindField->value;
 492     strncpy(dest, value, bindField->length);
 493     dest[ bindField->length - 1] ='\0';
 494     return;
 495 }
 496 void SqlOdbcStatement::setDateParam(int paramPos, Date value)
 497 {
 498     if (!isPrepared) return ;
 499     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 500     //if (bindField->type != typeDate) return;
 501     //*(Date*)(bindField->value) = value;
 502     AllDataType::convertToString(bindField->value, &value, typeDate);
 503
 504 }
 505 void SqlOdbcStatement::setTimeParam(int paramPos, Time value)
 506 {
 507     if (!isPrepared) return ;
 508     BindSqlField *bindField = (BindSqlField*)paramList.get(paramPos);
 509     //if (bindField->type != typeTime) return;
 510     //*(Time*)(bindField->value) = value;
 511     AllDataType::convertToString(bindField->value, &value, typeTime);
 512
 513 }
 514 void SqlOdbcStatement::setTimeStampParam(int paramPos, TimeStamp value)
 515 {
 516     if (!isPrepared) return ;
 517     BindSqlField *bindField = (BindSqlField*) paramList.get(paramPos);
 518     //if (bindField->type != typeTimeStamp) return;
 519     //*(TimeStamp*)(bindField->value) = value;
 520     AllDataType::convertToString(bindField->value, &value, typeTimeStamp);
 521 }
 522
 523 void SqlOdbcStatement::getPrimaryKeyFieldName(char *tablename, char *pkfieldname)
 524 {
 525     if (pkfieldname == NULL) return;
 526     SqlOdbcConnection *conn = (SqlOdbcConnection*)con;
 527     int retValue=SQLAllocHandle (SQL_HANDLE_STMT, conn->dbHdl, &hstmt);
 528     if (retValue) return ;
 529     char columnName[128];
 530     SQLINTEGER cbData;     // Output length of data
 531     SQLPrimaryKeys(hstmt, NULL, 0, NULL, 0, (SQLCHAR*) tablename, SQL_NTS);
 532     SQLFetch(hstmt);
 533     SQLGetData(hstmt, 4, SQL_C_CHAR, (SQLCHAR*) columnName, sizeof(columnName),&cbData);
 534     strcpy(pkfieldname, columnName);
 535     SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
 536     return;
 537 }