1 /***************************************************************************
2 * Copyright (C) 2007 by Prabakaran Thirumalai *
3 * praba_tuty@yahoo.com *
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. *
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. *
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.
26 DbRetVal
SqlOciStatement::prepare(char *stmtstr
)
29 if (innerStmt
) rv
= ErrBadCall
;
30 if (rv
!= OK
) return rv
;
33 SqlOciConnection
*conn
= (SqlOciConnection
*)con
;
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;
42 retValue = SQLNumResultCols (hstmt, &totalFields);
43 if (retValue) return ErrBadCall;
44 BindSqlProjectField *bindProjField = NULL;
46 UCHAR colName[IDENTIFIER_LENGTH];
53 icol = 1; colNameMax = IDENTIFIER_LENGTH;
54 while (icol <= totalFields)
56 retValue = SQLDescribeCol(hstmt, icol, colName, colNameMax,
57 &nameLength, &colType, &colLength,
59 if (retValue) return ErrBadCall;
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;
69 switch(bindProjField->type)
72 fieldsize = colLength+1;
73 bindProjField->targetvalue = malloc(fieldsize);
76 fieldsize = sizeof(DATE_STRUCT);
77 bindProjField->targetvalue = malloc(sizeof(DATE_STRUCT));
80 fieldsize = sizeof(TIME_STRUCT);
81 bindProjField->targetvalue = malloc(sizeof(TIME_STRUCT));
84 fieldsize = sizeof(TIMESTAMP_STRUCT);
85 bindProjField->targetvalue = malloc(sizeof(TIMESTAMP_STRUCT));
88 bindProjField->targetvalue = AllDataType::alloc(bindProjField->type, bindProjField->length);
90 retValue = SQLBindCol(hstmt, icol,
91 AllDataType::convertToSQLType(bindProjField->type),
92 bindProjField->targetvalue, fieldsize, NULL);
93 if (retValue) return ErrBadCall;
94 bindList.append(bindProjField);
99 BindSqlField *bindField;
100 retValue = SQLNumParams (hstmt, &totalFields);
101 if (retValue) return ErrBadCall;
102 icol = 1; colNameMax = IDENTIFIER_LENGTH;
105 while (icol <= totalFields)
107 retValue = SQLDescribeParam(hstmt, icol, &cType, &cLength,
110 //Bug #1382 SQLDescribeParam returns the same type information
112 if (retValue) return ErrBadCall;
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;
120 switch(bindField->type)
124 bindField->targetvalue = malloc(fieldsize);
127 fieldsize = sizeof(DATE_STRUCT);
128 bindField->targetvalue = malloc(sizeof(DATE_STRUCT));
131 fieldsize = sizeof(TIME_STRUCT);
132 bindField->targetvalue = malloc(sizeof(TIME_STRUCT));
135 fieldsize = sizeof(TIMESTAMP_STRUCT);
136 bindField->targetvalue = malloc(sizeof(TIMESTAMP_STRUCT));
139 bindField->targetvalue = AllDataType::alloc(bindField->type, cLength);
143 retValue = SQLBindParameter(hstmt, icol, SQL_PARAM_INPUT,
144 AllDataType::convertToSQL_C_Type(bindField->type),
145 cType, fieldsize, scale, bindField->targetvalue,
147 if (retValue) return ErrBadCall;
148 paramList.append(bindField);
151 //TODO::deallocate memory and remove elements from list in case of any
152 //failure in any of the above ODBC functions
157 bool SqlOciStatement::isSelect()
163 DbRetVal
SqlOciStatement::execute(int &rowsAffected
)
166 if (!isPrepared
) return ErrNotPrepared
;
167 ListIterator iter
= paramList
.getIterator();
168 BindSqlField
*bindField
= NULL
;
169 /* while (iter.hasElement())
171 bindField = (BindSqlField*)iter.nextElement();
172 switch(bindField->type)
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();
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();
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();
202 AllDataType::copyVal(bindField->targetvalue, bindField->value,
203 bindField->type, bindField->length);
208 int retValue = SQLExecute (hstmt);
209 if (retValue) return ErrBadCall;
214 DbRetVal
SqlOciStatement::bindParam(int pos
, void* value
)
217 printError(ErrWarning
, "Deprecated. Use setParamXXX instead\n");
221 DbRetVal
SqlOciStatement::bindField(int pos
, void* value
)
223 if (!isPrepared
) return OK
;
224 BindSqlProjectField
*bindField
= (BindSqlProjectField
*)bindList
.get(pos
);
225 if (NULL
== bindField
)
227 printError(ErrBadArg
, "Could not get the projection list. Should be called only for SELECT statement");
230 bindField
->value
= value
;
233 void* SqlOciStatement::fetch()
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())
243 bindField = (BindSqlProjectField*)iter.nextElement();
244 switch(bindField->type)
247 Date *dtCSQL = (Date*) bindField->value;
248 DATE_STRUCT *dtTarget = (DATE_STRUCT*) bindField->targetvalue;
249 dtCSQL->set(dtTarget->year,dtTarget->month,dtTarget->day);
253 Time *dtCSQL = (Time*) bindField->value;
254 TIME_STRUCT *dtTarget = (TIME_STRUCT*) bindField->targetvalue;
255 dtCSQL->set(dtTarget->hour,dtTarget->minute,dtTarget->second);
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);
267 AllDataType::copyVal(bindField->value, bindField->targetvalue,
268 bindField->type, bindField->length);
272 if (ptrToFirstField == NULL) ptrToFirstField=bindField->value;
274 return ptrToFirstField
;
277 void* SqlOciStatement::fetchAndPrint(bool SQL
)
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())
287 bindField = (BindSqlProjectField*)iter.nextElement();
288 switch(bindField->type)
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);
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);
304 case typeTimeStamp: {
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);
314 AllDataType::printVal(bindField->targetvalue,
315 bindField->type, bindField->length);
319 if (ptrToFirstField == NULL) ptrToFirstField=bindField->targetvalue;
323 return ptrToFirstField
;
326 void* SqlOciStatement::next()
331 DbRetVal
SqlOciStatement::close()
333 if (!isPrepared
) return OK
;
334 //SQLCloseCursor(hstmt);
338 void* SqlOciStatement::getFieldValuePtr( int pos
)
343 int SqlOciStatement::noOfProjFields()
345 if (!isPrepared
) return 0;
346 return bindList
.size();
349 int SqlOciStatement::noOfParamFields()
351 if (!isPrepared
) return 0;
352 return paramList
.size();
355 DbRetVal
SqlOciStatement::getProjFldInfo (int projpos
, FieldInfo
*&fInfo
)
357 ListIterator biter
= bindList
.getIterator();
358 BindSqlProjectField
*elem
= NULL
;
360 while (biter
.hasElement())
362 elem
= (BindSqlProjectField
*) biter
.nextElement();
363 if (count
== projpos
)
365 strcpy(fInfo
->fldName
, elem
->fName
);
366 fInfo
->length
= elem
->length
;
367 fInfo
->type
=elem
->type
;
375 DbRetVal
SqlOciStatement::getParamFldInfo (int parampos
, FieldInfo
*&fInfo
)
377 ListIterator biter
= paramList
.getIterator();
378 BindSqlField
*elem
= NULL
;
380 while (biter
.hasElement())
382 elem
= (BindSqlField
*) biter
.nextElement();
383 if (count
== parampos
)
385 fInfo
->length
= elem
->length
;
386 fInfo
->type
=elem
->type
;
394 DbRetVal
SqlOciStatement::free()
397 ListIterator biter
= bindList
.getIterator();
398 BindSqlProjectField
*elem
= NULL
;
399 while (biter
.hasElement())
401 elem
= (BindSqlProjectField
*) biter
.nextElement();
402 ::free(elem
->targetvalue
);
406 ListIterator piter
= paramList
.getIterator();
407 BindSqlField
*bindField
= NULL
;
408 while (piter
.hasElement())
410 bindField
= (BindSqlField
*) piter
.nextElement();
411 ::free(bindField
->value
);
412 ::free(bindField
->targetvalue
);
417 //SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
420 void SqlOciStatement::setShortParam(int paramPos
, short value
)
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
);
429 void SqlOciStatement::setIntParam(int paramPos
, int value
)
431 if (!isPrepared
) return ;
432 BindSqlField
*bindField
= (BindSqlField
*)paramList
.get(paramPos
);
433 /*if (bindField->type != typeInt) return;
434 *(int*)(bindField->value) = value;
438 //Bug #1382 SQLDescribeParam returns the same type information, varchar
439 AllDataType::convertToString(bindField
->value
, &value
, typeInt
);
444 void SqlOciStatement::setLongParam(int paramPos
, long value
)
446 if (!isPrepared
) return ;
447 BindSqlField
*bindField
= (BindSqlField
*)paramList
.get(paramPos
);
448 //if (bindField->type != typeLong) return;
449 //*(long*)(bindField->value) = value;
451 //Bug #1382 SQLDescribeParam returns the same type information, varchar
452 AllDataType::convertToString(bindField
->value
, &value
, typeLong
);
456 void SqlOciStatement::setLongLongParam(int paramPos
, long long value
)
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
);
465 void SqlOciStatement::setByteIntParam(int paramPos
, ByteInt value
)
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
);
474 void SqlOciStatement::setFloatParam(int paramPos
, float value
)
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
);
482 void SqlOciStatement::setDoubleParam(int paramPos
, double value
)
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
);
491 void SqlOciStatement::setStringParam(int paramPos
, char *value
)
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';
501 void SqlOciStatement::setDateParam(int paramPos
, Date value
)
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
);
510 void SqlOciStatement::setTimeParam(int paramPos
, Time value
)
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
);
519 void SqlOciStatement::setTimeStampParam(int paramPos
, TimeStamp value
)
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
);
527 void SqlOciStatement::setBinaryParam(int paramPos
, void *value
)
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
);
536 void SqlOciStatement::getPrimaryKeyFieldName(char *tablename
, char *pkfieldname
)
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);
547 SQLGetData(hstmt, 4, SQL_C_CHAR, (SQLCHAR*) columnName, sizeof(columnName),&cbData);
548 strcpy(pkfieldname, columnName);
549 SQLFreeHandle (SQL_HANDLE_STMT, hstmt);