00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011 #include <string.h>
00012
00013 #include "CTB.hxx"
00014 #include "CTBprintf.hxx"
00015 #include "CTBcarray.hxx"
00016 #include "CTBodbc.hxx"
00017 #include "CTBodbcConnect.hxx"
00018 #include "CTBodbcStatement.hxx"
00019
00025
00027
00028 CTBodbcStatement::CTBodbcStatement(CTBodbcConnect& dbc)
00029 : m_dbc(dbc),
00030 m_hstmt(0)
00031 {
00032 }
00033
00034
00036
00037 CTBodbcStatement::~CTBodbcStatement()
00038 {
00039 RETCODE i_rc;
00040
00041 if (m_hstmt != 0) {
00042 i_rc = SQLFreeStmt (m_hstmt,SQL_DROP);
00043 m_dbc.TraceODBCCall("CTBodbcStatement::~CTBodbcStatement->SQLFreeStmt",i_rc);
00044 }
00045 }
00046
00047
00049
00050 bool CTBodbcStatement::Prepare(const char* c_stmt)
00051 {
00052 RETCODE i_rc;
00053
00054 if (!AllocIfNull()) return false;
00055
00056 i_rc = SQLPrepare (m_hstmt,(UCHAR *) c_stmt,SQL_NTS);
00057 m_dbc.TraceODBCCall("CTBodbcStatement::Prepare->SQLPrepare",i_rc);
00058
00059 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00060 PrintError(cerr);
00061 return false;
00062 }
00063
00064 return true;
00065 }
00066
00067
00069
00070 bool CTBodbcStatement::Execute()
00071 {
00072 RETCODE i_rc;
00073
00074 if (!AllocIfNull()) return false;
00075
00076 i_rc = SQLExecute(m_hstmt);
00077 m_dbc.TraceODBCCall("CTBodbcStatement::Execute->SQLExecute",i_rc);
00078
00079 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00080 PrintError(cerr);
00081 return false;
00082 }
00083
00084 return true;
00085 }
00086
00087
00089
00090 bool CTBodbcStatement::Fetch()
00091 {
00092 RETCODE i_rc;
00093
00094 if (!AllocIfNull()) return false;
00095
00096 i_rc = SQLFetch(m_hstmt);
00097 m_dbc.TraceODBCCall("CTBodbcStatement::Fetch->SQLFetch",i_rc);
00098
00099 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00100 PrintError(cerr);
00101 return false;
00102 }
00103
00104 return true;
00105 }
00106
00107
00109
00110 bool CTBodbcStatement::Close()
00111 {
00112 RETCODE i_rc;
00113
00114 if (m_hstmt == 0) return true;
00115
00116 i_rc = SQLFreeStmt (m_hstmt,SQL_CLOSE);
00117 m_dbc.TraceODBCCall("CTBodbcStatement::Close->SQLFreeStmt",i_rc);
00118
00119 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00120 PrintError(cerr);
00121 return false;
00122 }
00123
00124 return true;
00125 }
00126
00127
00129
00130 bool CTBodbcStatement::UnbindParameter()
00131 {
00132 RETCODE i_rc;
00133
00134 if (m_hstmt == 0) return true;
00135
00136 i_rc = SQLFreeStmt(m_hstmt,SQL_RESET_PARAMS);
00137 m_dbc.TraceODBCCall("CTBodbcStatement::UnbindParameter->SQLFreeStmt",i_rc);
00138
00139 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00140 PrintError(cerr);
00141 return false;
00142 }
00143
00144 return true;
00145 }
00146
00147
00149
00150 bool CTBodbcStatement::UnbindCol()
00151 {
00152 RETCODE i_rc;
00153
00154 if (m_hstmt == 0) return true;
00155
00156 i_rc = SQLFreeStmt(m_hstmt,SQL_UNBIND);
00157 m_dbc.TraceODBCCall("CTBodbcStatement::UnbindCol->SQLFreeStmt",i_rc);
00158
00159 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00160 PrintError(cerr);
00161 return false;
00162 }
00163
00164 return true;
00165 }
00166
00167
00169
00170 bool CTBodbcStatement::NumResultCols(int& i_ncol)
00171 {
00172 RETCODE i_rc;
00173 SWORD i_l_ncol = 0;
00174
00175 i_ncol = 0;
00176
00177 if (m_hstmt == 0) return true;
00178
00179 i_rc = SQLNumResultCols(m_hstmt,&i_l_ncol);
00180 m_dbc.TraceODBCCall("CTBodbcStatement::NumResultCols->SQLNumResultCols",i_rc);
00181 if (i_rc != SQL_SUCCESS) {
00182 PrintError(cerr);
00183 return false;
00184 }
00185
00186 i_ncol = i_l_ncol;
00187 return true;
00188 }
00189
00190
00192
00202 bool CTBodbcStatement::DescribeCol(int i_icol, char* c_colname,
00203 int i_size, SWORD& i_sqltype,
00204 int& i_precision, int& i_scale,
00205 bool& b_nullable)
00206 {
00207 RETCODE i_rc;
00208 SWORD i_l_sqltype = 0;
00209 UDWORD i_l_precision = 0;
00210 SWORD i_l_scale = 0;
00211 SWORD i_l_nullable = SQL_NULLABLE_UNKNOWN;
00212
00213 if (i_size > 0) c_colname[0] = 0;
00214 i_sqltype = 0;
00215 i_precision = 0;
00216 i_scale = 0;
00217 b_nullable = true;
00218
00219 if (m_hstmt == 0) return true;
00220
00221 i_rc = SQLDescribeCol(m_hstmt,i_icol,(UCHAR*) c_colname,i_size,0,
00222 &i_l_sqltype,&i_l_precision,&i_l_scale,&i_l_nullable);
00223 m_dbc.TraceODBCCall("CTBodbcStatement::DescribeCol->SQLDescribeCol",i_rc);
00224 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00225 PrintError(cerr);
00226 return false;
00227 }
00228
00229 i_sqltype = i_l_sqltype;
00230 i_precision = i_l_precision;
00231 i_scale = i_l_scale;
00232 b_nullable = (i_l_nullable != SQL_NO_NULLS);
00233
00234 return true;
00235 }
00236
00237
00239
00245 bool CTBodbcStatement::FetchToStream(ostream& os,
00246 int i_format, int i_maxrow = 0)
00247 {
00248 static const int dim_col = 255;
00249 int i_ncol;
00250 int i_nrow = 0;
00251 char c_sep = 0;
00252
00253 CTBcarray<CTBcstring<256>,dim_col+1> c_colname;
00254 CTBcarray<int,dim_col+1> i_coltype;
00255 SWORD i_sqltype;
00256 int i_precision;
00257 int i_scale;
00258 bool b_nullable;
00259
00260 enum {
00261 e_bad = 0,
00262 e_int,
00263 e_double,
00264 e_char,
00265 e_date
00266 };
00267
00268 if (m_hstmt == 0) return true;
00269
00270 if (!NumResultCols(i_ncol)) return false;
00271 if (i_ncol >= dim_col) i_ncol = dim_col;
00272
00273 switch (i_format) {
00274 case format_bsv:
00275 case format_bsvh:
00276 c_sep = ' ';
00277 break;
00278 case format_tsv:
00279 case format_tsvh:
00280 c_sep = '\t';
00281 break;
00282 case format_csv:
00283 case format_csvh:
00284 c_sep = ',';
00285 break;
00286 default:
00287 c_sep = 0;
00288 break;
00289 }
00290
00291 for (int i_icol = 1; i_icol <= i_ncol; i_icol++) {
00292
00293 DescribeCol(i_icol,c_colname[i_icol],256,
00294 i_sqltype,i_precision,i_scale,b_nullable);
00295 switch (i_sqltype) {
00296
00297 case SQL_BIT:
00298 case SQL_TINYINT:
00299 case SQL_SMALLINT:
00300 case SQL_INTEGER:
00301 i_coltype[i_icol] = e_int;
00302 break;
00303
00304 case SQL_DOUBLE:
00305 case SQL_DECIMAL:
00306 case SQL_NUMERIC:
00307 case SQL_FLOAT:
00308 i_coltype[i_icol] = e_double;
00309 break;
00310
00311 case SQL_VARCHAR:
00312 case SQL_CHAR:
00313 i_coltype[i_icol] = e_char;
00314 break;
00315
00316 case SQL_TIMESTAMP:
00317 i_coltype[i_icol] = e_date;
00318 break;
00319
00320 default:
00321 i_coltype[i_icol] = e_bad;
00322 break;
00323 }
00324
00325 switch (i_format) {
00326 case format_bsvh:
00327 case format_tsvh:
00328 case format_csvh:
00329 if (i_icol > 1) os << c_sep;
00330 os << c_colname[i_icol];
00331 break;
00332 case format_lvph:
00333 os << "#" << c_colname[i_icol] << ": ";
00334 switch (i_sqltype) {
00335 case SQL_BIT: os << "BIT"; break;
00336 case SQL_TINYINT: os << "TINYINT"; break;
00337 case SQL_SMALLINT: os << "SMALLINT"; break;
00338 case SQL_INTEGER: os << "INTEGER"; break;
00339 case SQL_DOUBLE: os << "DOUBLE"; break;
00340 case SQL_DECIMAL: os << "DECIMAL"; break;
00341 case SQL_NUMERIC: os << "NUMERIC"; break;
00342 case SQL_FLOAT: os << "FLOAT"; break;
00343 case SQL_VARCHAR: os << "VARCHAR"; break;
00344 case SQL_CHAR: os << "CHAR"; break;
00345 case SQL_TIMESTAMP: os << "TIMESTAMP"; break;
00346 default: os << "?????????"; break;
00347 }
00348 os << " " << CTBprintf(i_precision,"d",4)
00349 << " " << CTBprintf(i_scale,"d",4)
00350 << endl;
00351
00352 break;
00353 }
00354 }
00355
00356 switch (i_format) {
00357 case format_bsvh:
00358 case format_tsvh:
00359 case format_csvh:
00360 os << endl;
00361 break;
00362 case format_lvph:
00363 os << "---" << endl;
00364 break;
00365 }
00366
00367 while (Fetch()) {
00368 int i_value;
00369 double d_value;
00370 static const int dim_c_value = 2000;
00371 char c_value[dim_c_value];
00372 CTBodbcTime time;
00373 bool b_null;
00374 bool b_drop_row = true;
00375 int i_ncol_null = 0;
00376
00377 i_nrow += 1;
00378 if (i_maxrow > 0 && i_nrow > i_maxrow) break;
00379
00380 for (int i_icol = 1; i_icol <= i_ncol; i_icol++) {
00381
00382 switch (i_coltype[i_icol]) {
00383 case e_int:
00384 GetData(i_icol,i_value,b_null);
00385 break;
00386 case e_double:
00387 GetData(i_icol,d_value,b_null);
00388 break;
00389 case e_char:
00390 GetData(i_icol,c_value,dim_c_value,b_null);
00391 break;
00392 case e_date:
00393 GetData(i_icol,time,b_null);
00394 break;
00395 }
00396
00397 if (!b_null) b_drop_row = false;
00398
00399 switch (i_format) {
00400 case format_bsv:
00401 case format_bsvh:
00402 if (!b_null && i_ncol_null > 0) {
00403 for (int i = 0; i < i_ncol_null; i++) {
00404 if (i > 0) os << " ";
00405 os << "NULL";
00406 }
00407 i_ncol_null = 0;
00408 }
00409 if (!b_drop_row && i_icol > 1) os << " ";
00410 if (b_null) {
00411 if (b_drop_row) {
00412 i_ncol_null += 1;
00413 } else {
00414 os << "NULL";
00415 }
00416 }
00417 break;
00418
00419 case format_tsv:
00420 case format_tsvh:
00421 case format_csv:
00422 case format_csvh:
00423 if (!b_null && i_ncol_null > 0) {
00424 for (int i = 1; i < i_ncol_null; i++) {
00425 os << c_sep;
00426 }
00427 i_ncol_null = 0;
00428 }
00429 if (!b_drop_row && i_icol > 1) os << c_sep;
00430 if (b_null && b_drop_row) i_ncol_null += 1;
00431 break;
00432
00433 case format_lvp:
00434 case format_lvph:
00435 b_drop_row = false;
00436 os << c_colname[i_icol] << ": ";
00437 break;
00438 }
00439
00440 if (!b_null) {
00441 switch (i_coltype[i_icol]) {
00442 case e_int:
00443 os << i_value;
00444 break;
00445 case e_double:
00446 os << d_value;
00447 break;
00448 case e_char:
00449 if (i_format == format_csv || i_format == format_csvh) {
00450 if (strpbrk(c_value,",\"\n") == 0) {
00451 os << c_value;
00452 } else {
00453 const char* p = c_value;
00454 char c;
00455 os << '"';
00456 while ((c = *p++)) {
00457 if (c == '"') os << '\\' << '"';
00458 else if (c == '\n') os << '\\' << 'n';
00459 else os << c;
00460 }
00461 os << '"';
00462 }
00463 } else {
00464 os << c_value;
00465 }
00466
00467 break;
00468 case e_date:
00469 os << time;
00470 break;
00471 }
00472 }
00473
00474 if (i_format == format_lvp || i_format == format_lvph) os << "\n";
00475 }
00476
00477 switch (i_format) {
00478 case format_bsv:
00479 case format_bsvh:
00480 case format_tsv:
00481 case format_tsvh:
00482 case format_csv:
00483 case format_csvh:
00484 if (!b_drop_row) os << endl;
00485 break;
00486 case format_lvp:
00487 case format_lvph:
00488 os << "---" << endl;
00489 break;
00490 }
00491 }
00492 Close();
00493
00494 return true;
00495 }
00496
00497
00498
00499
00500
00502
00510 bool CTBodbcStatement::GetData(int i_icol, SWORD i_ctype,
00511 void* p_value, int i_size, bool& b_null)
00512 {
00513 RETCODE i_rc;
00514 SDWORD i_rsize;
00515
00516 b_null = false;
00517 if (!AllocIfNull()) return false;
00518
00519 i_rc = SQLGetData (m_hstmt,i_icol,i_ctype,p_value,i_size,&i_rsize);
00520 m_dbc.TraceODBCCall("CTBodbcStatement::GetData->SQLGetData",i_rc);
00521
00522 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00523 PrintError(cerr);
00524 return false;
00525 }
00526
00527 b_null = (i_rsize == SQL_NULL_DATA);
00528 return true;
00529 }
00530
00531
00533
00540 bool CTBodbcStatement::BindIParameter(int i_ipar, SWORD i_ctype,
00541 SWORD i_sqltype, void* p_value)
00542 {
00543 RETCODE i_rc;
00544 static SDWORD i_rsize=0;
00545
00546 if (!AllocIfNull()) return false;
00547 if (i_rsize != 0)
00548 cerr << "%CTBodbcStatement::BindIParameter-F sanity" << endl;
00549 i_rc = SQLBindParameter(m_hstmt,i_ipar,SQL_PARAM_INPUT,i_ctype,
00550 i_sqltype,0,0,p_value,0,&i_rsize);
00551 m_dbc.TraceODBCCall("CTBodbcStatement::BindIParameter->SQLBindParameter",i_rc)
00552 ;
00553
00554 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00555 PrintError(cerr);
00556 return false;
00557 }
00558
00559 return true;
00560 }
00561
00562
00564
00570 bool CTBodbcStatement::BindIParameter(int i_ipar, SWORD i_sqltype,
00571 const char* c_pvalue)
00572 {
00573 RETCODE i_rc;
00574 static SDWORD i_rsize=SQL_NTS;
00575
00576 if (!AllocIfNull()) return false;
00577 if (i_rsize != SQL_NTS)
00578 cerr << "%CTBodbcStatement::BindIParameter-F sanity" << endl;
00579
00580 i_rc = SQLBindParameter(m_hstmt,i_ipar,SQL_PARAM_INPUT,SQL_C_CHAR,
00581 i_sqltype,0,0,(char*) c_pvalue,0,&i_rsize);
00582 m_dbc.TraceODBCCall("CTBodbcStatement::BindIParameter->SQLBindParameter",i_rc);
00583
00584 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00585 PrintError(cerr);
00586 return false;
00587 }
00588
00589 return true;
00590 }
00591
00592
00594
00602 bool CTBodbcStatement::BindCol(int i_icol, SWORD i_ctype,
00603 void* p_value, int i_size, CTBint32& i_rsize)
00604 {
00605 RETCODE i_rc;
00606
00607 if (!AllocIfNull()) return false;
00608
00609 i_rc = SQLBindCol(m_hstmt,i_icol,i_ctype,p_value,i_size,(SDWORD*) &i_rsize);
00610 m_dbc.TraceODBCCall("CTBodbcStatement::BindCol->SQLBindCol",i_rc);
00611
00612 if (i_rc != SQL_SUCCESS && i_rc != SQL_SUCCESS_WITH_INFO) {
00613 PrintError(cerr);
00614 return false;
00615 }
00616
00617 return true;
00618 }
00619
00620
00622
00623 void CTBodbcStatement::PrintError(ostream& os)
00624 {
00625 unsigned char c_state[16];
00626 unsigned char c_msg[SQL_MAX_MESSAGE_LENGTH];
00627
00628 while (SQLError(m_dbc.m_henv,m_dbc.m_hdbc,m_hstmt,c_state,0,
00629 c_msg,SQL_MAX_MESSAGE_LENGTH-1,0) == SQL_SUCCESS) {
00630 os << c_msg << " SQLSTATE " << c_state << endl;
00631 }
00632
00633 m_dbc.PrintError(os);
00634
00635 return;
00636 }
00637
00638
00640
00641 bool CTBodbcStatement::AllocIfNull()
00642 {
00643 RETCODE i_rc;
00644
00645 if (m_hstmt != 0) return true;
00646
00647 i_rc = SQLAllocStmt (m_dbc.m_hdbc, &m_hstmt);
00648 m_dbc.TraceODBCCall("CTBodbcStatement::Alloc->SQLAllocStmt",i_rc);
00649
00650 if (i_rc != SQL_SUCCESS) {
00651 PrintError(cerr);
00652 return false;
00653 }
00654 return true;
00655 }
00656
00657
00658
00659 #if (defined(CTB__OutLine) || defined(CTBodbcStatement__OutLine))
00660 #define inline
00661 #include "CTBodbcStatement.icc"
00662 #undef inline
00663 #endif
