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