00001
00002
00003
00004
00005
00006
00007
00008
00009
00010
00011
00012
00013
00014
00015
00016
00017 #include "icatLowLevelOracle.h"
00018 int _cllFreeStatementColumns(icatSessionStruct *icss, int statementNumber);
00019
00020 int cllBindVarCount=0;
00021 char *cllBindVars[MAX_BIND_VARS];
00022 int cllBindVarCountPrev=0;
00023
00024 char bindName[MAX_BIND_VARS*5]="";
00025
00026 char testName[]=":1";
00027 char testBindVar[]="a";
00028
00029
00030 #define MAX_TOKEN 256
00031
00032 #define TMP_STR_LEN 1040
00033
00034 #include <stdio.h>
00035 #include <pwd.h>
00036
00037 static OCIError *p_err;
00038
00039 OCIBind *p_bind[MAX_BIND_VARS];
00040
00041 char errbuf[100];
00042 int errcode;
00043 text oraErrorMsg[250];
00044
00045
00046
00047
00048 int
00049 logOraError(int level, OCIError *errhp, sword status)
00050 {
00051 sb4 errcode;
00052 int errorVal=-1;
00053 if (status == OCI_SUCCESS) return(0);
00054 switch (status) {
00055 case OCI_SUCCESS_WITH_INFO:
00056 rodsLog(level,"OCI_SUCCESS_WITH_INFO");
00057 OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
00058 oraErrorMsg, (ub4) sizeof(oraErrorMsg),
00059 (ub4) OCI_HTYPE_ERROR);
00060 rodsLog(level, "Error - %s\n", oraErrorMsg);
00061 errorVal=0;
00062 break;
00063 case OCI_NEED_DATA:
00064 rodsLog(level, "OCI_NEED_DATA");
00065 break;
00066 case OCI_NO_DATA:
00067 rodsLog(level, "OCI_NO_DATA");
00068 errorVal=0;
00069 break;
00070 case OCI_ERROR:
00071 OCIErrorGet ((dvoid *) errhp, (ub4) 1, (text *) NULL, &errcode,
00072 oraErrorMsg, (ub4) sizeof(oraErrorMsg),
00073 (ub4) OCI_HTYPE_ERROR);
00074 rodsLog(level, "OCI_Error: %s", oraErrorMsg);
00075 if (strstr((char *)oraErrorMsg, "unique constraint") != 0) {
00076 errorVal = CATALOG_ALREADY_HAS_ITEM_BY_THAT_NAME;
00077 }
00078 break;
00079 case OCI_INVALID_HANDLE:
00080 rodsLog(level, "OCI_INVALID_HANDLE\n");
00081 break;
00082 case OCI_STILL_EXECUTING:
00083 rodsLog(level, "OCI_STILL_EXECUTING\n");
00084 break;
00085 case OCI_CONTINUE:
00086 rodsLog(level, "OCI_CONTINUE\n");
00087 break;
00088 default:
00089 rodsLog(level, "Unknown OCI status - %d", status);
00090 break;
00091 }
00092 return(errorVal);
00093 }
00094
00095 int
00096 cllGetLastErrorMessage(char *msg, int maxChars) {
00097 strncpy(msg, (char *)&oraErrorMsg, maxChars);
00098 return(0);
00099 }
00100
00101
00102
00103
00104 int
00105 cllOpenEnv(icatSessionStruct *icss) {
00106 int stat;
00107
00108 OCIEnv *p_env;
00109 OCISvcCtx *p_svc;
00110
00111 stat = OCIEnvCreate((OCIEnv **)&p_env,
00112 (ub4)OCI_DEFAULT,
00113 (dvoid *)0, (dvoid * (*)(dvoid *, size_t))0,
00114 (dvoid * (*)(dvoid *, dvoid *, size_t))0,
00115 (void (*)(dvoid *, dvoid *))0,
00116 (size_t)0, (dvoid **)0);
00117 if (stat != OCI_SUCCESS) {
00118 rodsLog(LOG_ERROR, "cllOpenEnv: OCIEnvInit failed");
00119 return(CAT_ENV_ERR);
00120 }
00121
00122
00123 stat = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_err, OCI_HTYPE_ERROR,
00124 (size_t) 0, (dvoid **) 0);
00125
00126 if (stat != OCI_SUCCESS) {
00127 rodsLog(LOG_ERROR, "cllOpenEnv: OCIHandleAlloc failed");
00128 return(CAT_ENV_ERR);
00129 }
00130
00131 stat = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_svc, OCI_HTYPE_SVCCTX,
00132 (size_t) 0, (dvoid **) 0);
00133
00134 if (stat != OCI_SUCCESS) {
00135 rodsLog(LOG_ERROR, "cllOpenEnv: OCIHandleAlloc failed");
00136 return(CAT_ENV_ERR);
00137 }
00138
00139 icss->connectPtr=p_svc;
00140 icss->environPtr=p_env;
00141 return(0);
00142 }
00143
00144
00145
00146
00147 int
00148 cllCloseEnv(icatSessionStruct *icss) {
00149 OCIEnv *p_env;
00150 OCISvcCtx *p_svc;
00151 sword stat;
00152
00153
00154
00155
00156 p_svc = (OCISvcCtx *) icss->connectPtr;
00157 p_env = (OCIEnv *) icss->environPtr;
00158
00159 stat = OCIHandleFree((dvoid *) p_svc, OCI_HTYPE_SVCCTX);
00160
00161 stat = OCIHandleFree((dvoid *) p_err, OCI_HTYPE_ERROR);
00162
00163 icss->connectPtr=0;
00164 return(0);
00165 }
00166
00167
00168
00169
00170 int
00171 cllConnect(icatSessionStruct *icss) {
00172 int stat;
00173 OCIEnv *p_env;
00174 OCISvcCtx *p_svc;
00175
00176 char userName[110];
00177 char databaseName[110];
00178 char *cp1, *cp2;
00179 int i, atFound;
00180
00181 p_svc = (OCISvcCtx *)icss->connectPtr;
00182 p_env = (OCIEnv *)icss->environPtr;
00183
00184 atFound=0;
00185 userName[0]='\0';
00186 databaseName[0]='\0';
00187 cp1=userName;
00188 cp2=icss->databaseUsername;
00189 for(i=0;i<100;i++) {
00190 if (*cp2=='@') {
00191 atFound=1;
00192 *cp1='\0';
00193 cp1=databaseName;
00194 cp2++;
00195 }
00196 else {
00197 if (*cp2=='\0') {
00198 *cp1='\0';
00199 break;
00200 }
00201 else {
00202 *cp1++=*cp2++;
00203 }
00204 }
00205 }
00206 if (atFound==0) {
00207 rodsLog(LOG_ERROR, "no @ in the database user name");
00208 return(CAT_INVALID_ARGUMENT);
00209 }
00210
00211 #if 0
00212 stat = OCILogon(p_env, p_err, &p_svc, icss->databaseUsername,
00213 strlen(icss->databaseUsername),
00214 icss->databasePassword,
00215 strlen(icss->databasePassword),
00216 ORACLE_DATABASE_NAME, strlen(ORACLE_DATABASE_NAME));
00217 #endif
00218 stat = OCILogon(p_env, p_err, &p_svc, (OraText *)userName,
00219 strlen(userName),
00220 (OraText *)icss->databasePassword,
00221 strlen(icss->databasePassword),
00222 (OraText *)databaseName, strlen(databaseName));
00223
00224 if (stat != OCI_SUCCESS) {
00225 rodsLog(LOG_ERROR, "cllConnect: OCILogon failed: %d", stat);
00226 logOraError(LOG_ERROR, p_err, stat);
00227 return(CAT_CONNECT_ERR);
00228 }
00229
00230 icss->connectPtr=p_svc;
00231 return(0);
00232 }
00233
00234
00235
00236
00237 int
00238 cllConnectRda(icatSessionStruct *icss) {
00239 int stat;
00240 OCIEnv *p_env;
00241 OCISvcCtx *p_svc;
00242
00243 char userName[110];
00244 char databaseName[110];
00245 char *cp1, *cp2;
00246 int i, atFound;
00247
00248 p_svc = (OCISvcCtx *)icss->connectPtr;
00249 p_env = (OCIEnv *)icss->environPtr;
00250
00251 atFound=0;
00252 userName[0]='\0';
00253 databaseName[0]='\0';
00254 cp1=userName;
00255 cp2=icss->databaseUsername;
00256 for(i=0;i<100;i++) {
00257 if (*cp2=='@') {
00258 atFound=1;
00259 *cp1='\0';
00260 cp1=databaseName;
00261 cp2++;
00262 }
00263 else {
00264 if (*cp2=='\0') {
00265 *cp1='\0';
00266 break;
00267 }
00268 else {
00269 *cp1++=*cp2++;
00270 }
00271 }
00272 }
00273 if (atFound==0) {
00274 rodsLog(LOG_ERROR, "no @ in the database user name");
00275 return(CAT_INVALID_ARGUMENT);
00276 }
00277
00278 stat = OCILogon(p_env, p_err, &p_svc, (OraText *)userName,
00279 strlen(userName),
00280 (OraText *)icss->databasePassword,
00281 strlen(icss->databasePassword),
00282 (OraText *)databaseName, strlen(databaseName));
00283
00284 if (stat != OCI_SUCCESS) {
00285 rodsLog(LOG_ERROR, "cllConnectRda: OCILogon failed: %d", stat);
00286 logOraError(LOG_ERROR, p_err, stat);
00287 return(CAT_CONNECT_ERR);
00288 }
00289
00290 icss->connectPtr=p_svc;
00291 return(0);
00292 }
00293
00294
00295
00296
00297 int
00298 cllConnectDbr(icatSessionStruct *icss, char *unused) {
00299 int stat;
00300 OCIEnv *p_env;
00301 OCISvcCtx *p_svc;
00302
00303 char userName[110];
00304 char databaseName[110];
00305 char *cp1, *cp2;
00306 int i, atFound;
00307
00308 p_svc = (OCISvcCtx *)icss->connectPtr;
00309 p_env = (OCIEnv *)icss->environPtr;
00310
00311 atFound=0;
00312 userName[0]='\0';
00313 databaseName[0]='\0';
00314 cp1=userName;
00315 cp2=icss->databaseUsername;
00316 for(i=0;i<100;i++) {
00317 if (*cp2=='@') {
00318 atFound=1;
00319 *cp1='\0';
00320 cp1=databaseName;
00321 cp2++;
00322 }
00323 else {
00324 if (*cp2=='\0') {
00325 *cp1='\0';
00326 break;
00327 }
00328 else {
00329 *cp1++=*cp2++;
00330 }
00331 }
00332 }
00333 if (atFound==0) {
00334 rodsLog(LOG_ERROR, "no @ in the database user name");
00335 return(CAT_INVALID_ARGUMENT);
00336 }
00337
00338 stat = OCILogon(p_env, p_err, &p_svc, (OraText *)userName,
00339 strlen(userName),
00340 (OraText *)icss->databasePassword,
00341 strlen(icss->databasePassword),
00342 (OraText *)databaseName, strlen(databaseName));
00343
00344 if (stat != OCI_SUCCESS) {
00345 rodsLog(LOG_ERROR, "cllConnectRda: OCILogon failed: %d", stat);
00346 logOraError(LOG_ERROR, p_err, stat);
00347 return(CAT_CONNECT_ERR);
00348 }
00349
00350 icss->connectPtr=p_svc;
00351 return(0);
00352 }
00353
00354
00355
00356
00357 int
00358 cllDisconnect(icatSessionStruct *icss) {
00359 sword stat;
00360 OCISvcCtx *p_svc;
00361
00362 p_svc = (OCISvcCtx *)icss->connectPtr;
00363
00364 stat = OCILogoff(p_svc, p_err);
00365 if (stat != OCI_SUCCESS) {
00366 rodsLog(LOG_ERROR, "cllDisconnect: OCILogoff failed: %d", stat);
00367 return(CAT_DISCONNECT_ERR);
00368 }
00369
00370 return(0);
00371 }
00372
00373
00374
00375 int
00376 convertSqlToOra(char *sql, char *sqlOut) {
00377 char *cp1, *cp2, *cpEnd;
00378 int i, hundreds, tens, ones;
00379 cp1=sql;
00380 cp2=sqlOut;
00381 cpEnd=cp2+MAX_SQL_SIZE-2;
00382 i=1;
00383 while (*cp1!='\0') {
00384 if (*cp1 != '?') *cp2++=*cp1++;
00385 else {
00386 *cp2++=':';
00387
00388 tens = i / 10 ;
00389 ones = i % 10;
00390 hundreds = i / 100;
00391 if (hundreds > 0) {
00392 tens = (i - (hundreds*100)) / 10;
00393 *cp2++=hundreds+'0';
00394 }
00395 if (hundreds>0 || tens>0) {
00396 *cp2++=tens+'0';
00397 }
00398 *cp2++=ones+'0';
00399 cp1++;
00400 i++;
00401 }
00402 if (cp2 > cpEnd) return(-1);
00403 }
00404 *cp2 = '\0';
00405 return(0);
00406 }
00407
00408
00409
00410
00411 void
00412 logTheBindVariables(int level)
00413 {
00414 int i;
00415 char tmpStr[TMP_STR_LEN+2];
00416 for (i=0;i<cllBindVarCountPrev;i++) {
00417 snprintf(tmpStr, TMP_STR_LEN, "bindVar[%d]=:%s:", i+1, cllBindVars[i]);
00418 if (level == 0) {
00419 rodsLogSql(tmpStr);
00420 }
00421 else {
00422 rodsLog(level, tmpStr);
00423 }
00424 }
00425 }
00426
00427
00428
00429
00430 int
00431 bindTheVariables(OCIStmt *p_statement, const char *sql) {
00432 int myBindVarCount;
00433 int stat;
00434 int i;
00435
00436 for (i=0;i<MAX_BIND_VARS;i++) {
00437 p_bind[i]=NULL;
00438 }
00439 if (bindName[0]=='\0') {
00440
00441
00442
00443 for (i=0;i<MAX_BIND_VARS;i++) {
00444 snprintf(&bindName[i*5], 5, ":%d", i+1);
00445 }
00446 }
00447
00448 myBindVarCount = cllBindVarCount;
00449 cllBindVarCountPrev=cllBindVarCount;
00450 cllBindVarCount = 0;
00451
00452 if (myBindVarCount > 0) {
00453 if (myBindVarCount > MAX_BIND_VARS) return(CAT_INVALID_ARGUMENT);
00454 for (i=0;i<myBindVarCount;i++) {
00455 int len, len2;
00456 len = strlen((char*)&bindName[i*5]);
00457 len2 = strlen(cllBindVars[i])+1;
00458 stat = OCIBindByName(p_statement, &p_bind[i], p_err,
00459 (OraText *)&bindName[i*5],
00460 len,
00461 (dvoid *)cllBindVars[i],
00462 len2,
00463 SQLT_STR,
00464 0,0,0,0,0,
00465 OCI_DEFAULT);
00466
00467 if (stat != OCI_SUCCESS) {
00468 rodsLog(LOG_ERROR, "cllExecNoResult: OCIBindByName failed: %d",
00469 stat);
00470 rodsLog(LOG_ERROR, "sql:%s", sql);
00471 logOraError(LOG_ERROR, p_err, stat);
00472 return(CAT_OCI_ERROR);
00473 }
00474 }
00475 }
00476 return(0);
00477 }
00478
00479 int
00480 logExecuteStatus(int stat, char *sql, char *funcName) {
00481 char * status;
00482 int stat2;
00483 status = "UNKNOWN";
00484 if (stat == OCI_SUCCESS) status= "SUCCESS";
00485 if (stat == OCI_SUCCESS_WITH_INFO) status="SUCCESS_WITH_INFO";
00486 if (stat == OCI_NO_DATA) status="NO_DATA";
00487 if (stat == OCI_ERROR) status="SQL_ERROR";
00488 if (stat == OCI_INVALID_HANDLE) status="HANDLE_ERROR";
00489 rodsLogSqlResult(status);
00490
00491 if (stat == OCI_SUCCESS ||
00492 stat == OCI_SUCCESS_WITH_INFO ||
00493 stat == OCI_NO_DATA) {
00494 return(0);
00495 }
00496 else {
00497 logTheBindVariables(LOG_ERROR);
00498 rodsLog(LOG_ERROR,
00499 "%s OCIStmtExecute error: %d, sql:%s",
00500 funcName, stat, sql);
00501 stat2 = logOraError(LOG_ERROR, p_err, stat);
00502 return(stat2);
00503 }
00504 }
00505
00506
00507
00508
00509
00510
00511 int
00512 cllExecSqlNoResult(icatSessionStruct *icss, const char *sqlInput)
00513 {
00514
00515 int stat, stat2, stat3;
00516 OCIEnv *p_env;
00517 OCISvcCtx *p_svc;
00518 static OCIStmt *p_statement;
00519 char sql[MAX_SQL_SIZE];
00520 ub4 rows_affected;
00521 ub4 *pUb4;
00522
00523 stat = convertSqlToOra(sqlInput, sql);
00524 if (stat!=0) {
00525 rodsLog(LOG_ERROR, "cllExecSqlNoResult: SQL too long");
00526 return(CAT_OCI_ERROR);
00527 }
00528
00529 p_svc = (OCISvcCtx *)icss->connectPtr;
00530 p_env = (OCIEnv *)icss->environPtr;
00531
00532 if (strcmp(sql, "commit") == 0) {
00533 rodsLogSql(sql);
00534 stat = OCITransCommit(p_svc, p_err, (ub4) OCI_DEFAULT);
00535 stat2 = logExecuteStatus(stat, sql, "cllExecSqlNoResult");
00536 if (stat != OCI_SUCCESS) {
00537 rodsLog(LOG_ERROR, "cllExecSqlNoResult: OCITransCommit failed: %d",
00538 stat);
00539 logOraError(LOG_ERROR, p_err, stat);
00540 return(CAT_OCI_ERROR);
00541 }
00542 return(0);
00543 }
00544
00545 if (strcmp(sql, "rollback") == 0) {
00546 rodsLogSql(sql);
00547 stat = OCITransRollback(p_svc, p_err, (ub4) OCI_DEFAULT);
00548 stat2 = logExecuteStatus(stat, sql, "cllExecSqlNoResult");
00549 if (stat != OCI_SUCCESS) {
00550 rodsLog(LOG_ERROR, "cllExecSqlNoResult: OCITransRollback failed: %d",
00551 stat);
00552 logOraError(LOG_ERROR, p_err, stat);
00553 return(CAT_OCI_ERROR);
00554 }
00555 return(0);
00556 }
00557
00558
00559
00560 stat = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_statement,
00561 OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
00562 if (stat != OCI_SUCCESS) {
00563 rodsLog(LOG_ERROR, "cllExecSqlNoResult: OCIHandleAlloc failed: %d",stat);
00564 logOraError(LOG_ERROR, p_err, stat);
00565 return(CAT_OCI_ERROR);
00566 }
00567
00568
00569 stat = OCIStmtPrepare(p_statement, p_err, (OraText *)sql,
00570 (ub4) strlen(sql),
00571 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
00572 if (stat != OCI_SUCCESS) {
00573 rodsLog(LOG_ERROR, "cllExecSqlNoResult: OCIStmtPrepare failed: %d",stat);
00574 rodsLog(LOG_ERROR, sql);
00575 logOraError(LOG_ERROR, p_err, stat);
00576 return(CAT_OCI_ERROR);
00577 }
00578
00579 if (bindTheVariables(p_statement, sql) != 0) {
00580 logTheBindVariables(LOG_ERROR);
00581 return(CAT_OCI_ERROR);
00582 }
00583 logTheBindVariables(0);
00584 rodsLogSql(sql);
00585
00586
00587 stat = OCIStmtExecute(p_svc, p_statement, p_err, (ub4) 1, (ub4) 0,
00588 (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
00589 OCI_DEFAULT);
00590 stat2 = logExecuteStatus(stat, sql, "cllExecSqlNoResult");
00591 if (stat == OCI_NO_DATA) {
00592 return(CAT_SUCCESS_BUT_WITH_NO_INFO);
00593 }
00594
00595
00596
00597 pUb4 = (ub4*) malloc(sizeof(rows_affected));
00598 *pUb4=0;
00599 rows_affected = 0;
00600
00601 if (stat == OCI_ERROR) {
00602 rodsLog(LOG_ERROR, "cllExecSqlNoResult: OCIStmtExecute failed: %d",stat);
00603 logOraError(LOG_ERROR, p_err, stat);
00604 free(pUb4);
00605 if (stat2==CATALOG_ALREADY_HAS_ITEM_BY_THAT_NAME) return(stat2);
00606 return(CAT_OCI_ERROR);
00607 }
00608
00609 stat3 = OCIAttrGet((dvoid *)p_statement, OCI_HTYPE_STMT, pUb4, 0,
00610 OCI_ATTR_ROW_COUNT, (OCIError *) p_err );
00611 rows_affected=*pUb4;
00612 free(pUb4);
00613
00614 stat = OCIHandleFree( (dvoid *)p_statement, OCI_HTYPE_STMT);
00615
00616
00617 if ( stat3 != OCI_SUCCESS ) {
00618 rodsLog(LOG_ERROR, "cllExecSqlNoResult: OCIAttrGet failed: %d",stat);
00619 return(stat3);
00620 }
00621
00622
00623 if (rows_affected == 0) {
00624 return(CAT_SUCCESS_BUT_WITH_NO_INFO);
00625 }
00626
00627 return(stat2);
00628
00629 }
00630
00631
00632
00633
00634
00635 int
00636 cllGetRow(icatSessionStruct *icss, int statementNumber) {
00637 static OCIStmt *p_statement;
00638 int nCols, stat;
00639
00640 icatStmtStrct *myStatement;
00641
00642 myStatement=icss->stmtPtr[statementNumber];
00643 nCols = myStatement->numOfCols;
00644 p_statement = (OCIStmt *)myStatement->stmtPtr;
00645
00646 stat = OCIStmtFetch(p_statement, p_err, (ub4)1, (ub2)0,
00647 (ub4) OCI_DEFAULT);
00648 if (stat != OCI_SUCCESS && stat != OCI_NO_DATA) {
00649 logOraError(LOG_ERROR, p_err, stat);
00650 _cllFreeStatementColumns(icss,statementNumber);
00651 myStatement->numOfCols=0;
00652 rodsLog(LOG_ERROR, "cllGetRow: Fetch failed: %d", stat);
00653 return(-1);
00654 }
00655 if (stat == OCI_SUCCESS) {
00656 return(0);
00657 }
00658 _cllFreeStatementColumns(icss,statementNumber);
00659 myStatement->numOfCols=0;
00660 return(0);
00661 }
00662
00663
00664
00665
00666
00667
00668
00669 int
00670 cllExecSqlWithResult(icatSessionStruct *icss, int *stmtNum, char *sql) {
00671 OCIEnv *p_env;
00672 OCISvcCtx *p_svc;
00673 static OCIStmt *p_statement;
00674 static OCIDefine *p_dfn = (OCIDefine *) 0;
00675 int stat, stat2, i, j;
00676 char *cptr;
00677 char sqlConverted[MAX_SQL_SIZE];
00678
00679 icatStmtStrct *myStatement;
00680 int statementNumber;
00681
00682 int counter;
00683 OCIParam *mypard = (OCIParam *) 0;
00684 ub2 dtype;
00685 ub2 col_width;
00686 ub4 char_semantics;
00687 OraText *colName;
00688
00689 static int columnLength[MAX_TOKEN];
00690 static sb2 indicator[MAX_TOKEN];
00691
00692 p_svc = (OCISvcCtx *)icss->connectPtr;
00693 p_env = (OCIEnv *)icss->environPtr;
00694
00695 i = convertSqlToOra(sql, sqlConverted);
00696 if (i !=0) {
00697 rodsLog(LOG_ERROR, "cllExecSqlWithResult: SQL too long");
00698 return(CAT_OCI_ERROR);
00699 }
00700
00701
00702 stat = OCIHandleAlloc( (dvoid *) p_env, (dvoid **) &p_statement,
00703 OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0);
00704 if (stat != OCI_SUCCESS) {
00705 rodsLog(LOG_ERROR, "cllExecSqlWithResult: OCIHandleAlloc failed: %d",
00706 stat);
00707 logOraError(LOG_ERROR, p_err, stat);
00708 return(CAT_OCI_ERROR);
00709 }
00710
00711
00712 statementNumber=-1;
00713 for (i=0;i<MAX_NUM_OF_CONCURRENT_STMTS && statementNumber<0;i++) {
00714 if (icss->stmtPtr[i]==0) {
00715 statementNumber=i;
00716 }
00717 }
00718 if (statementNumber<0) {
00719 rodsLog(LOG_ERROR,
00720 "cllExecSqlWithResult: too many concurrent statements");
00721 return(-2);
00722 }
00723
00724 myStatement = (icatStmtStrct *)malloc(sizeof(icatStmtStrct));
00725 icss->stmtPtr[statementNumber]=myStatement;
00726 myStatement->numOfCols=0;
00727
00728 myStatement->stmtPtr = p_statement;
00729
00730
00731 stat = OCIStmtPrepare(p_statement, p_err, (OraText *)sqlConverted,
00732 (ub4) strlen(sqlConverted),
00733 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);
00734 if (stat != OCI_SUCCESS) {
00735 rodsLog(LOG_ERROR, "cllExecSqlWithResult: OCIStmtPrepare failed: %d",stat);
00736 rodsLog(LOG_ERROR, sqlConverted);
00737 logOraError(LOG_ERROR, p_err, stat);
00738 return(CAT_OCI_ERROR);
00739 }
00740
00741 if (bindTheVariables(p_statement, sqlConverted) != 0) {
00742 logTheBindVariables(LOG_ERROR);
00743 return(CAT_OCI_ERROR);
00744 }
00745
00746 logTheBindVariables(0);
00747 rodsLogSql(sqlConverted);
00748
00749
00750 stat = OCIStmtExecute(p_svc, p_statement, p_err, (ub4) 0, (ub4) 0,
00751 (CONST OCISnapshot *) NULL, (OCISnapshot *) NULL,
00752 OCI_DEFAULT);
00753
00754 stat2 = logExecuteStatus(stat, sqlConverted, "cllExecSqlWithResult");
00755
00756 if (stat2) {
00757 return(stat2);
00758 }
00759
00760 *stmtNum = statementNumber;
00761
00762
00763
00764
00765 counter = 1;
00766 stat = OCIParamGet((dvoid *)p_statement, OCI_HTYPE_STMT, p_err,
00767 (dvoid **)&mypard, (ub4) counter);
00768
00769
00770
00771
00772 while (stat == OCI_SUCCESS) {
00773
00774 stat = OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
00775 (dvoid*) &dtype,(ub4 *) 0, (ub4) OCI_ATTR_DATA_TYPE,
00776 (OCIError *) p_err );
00777 if (stat != OCI_SUCCESS) {
00778 rodsLog(LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
00779 stat);
00780 logOraError(LOG_ERROR, p_err, stat);
00781 return(CAT_OCI_ERROR);
00782 }
00783
00784
00785 char_semantics = 0;
00786 stat = OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
00787 (dvoid*) &char_semantics,(ub4 *) 0,
00788 (ub4) OCI_ATTR_CHAR_USED,
00789 (OCIError *) p_err );
00790 if (stat != OCI_SUCCESS) {
00791 rodsLog(LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
00792 stat);
00793 logOraError(LOG_ERROR, p_err, stat);
00794 return(CAT_OCI_ERROR);
00795 }
00796
00797
00798 col_width = 0;
00799 if (char_semantics) {
00800 stat = OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
00801 (dvoid*) &col_width, (ub4 *) 0,
00802 (ub4) OCI_ATTR_CHAR_SIZE,
00803 (OCIError *) p_err );
00804 }
00805 else {
00806 stat = OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
00807 (dvoid*) &col_width,(ub4 *) 0,
00808 (ub4) OCI_ATTR_DATA_SIZE,
00809 (OCIError *) p_err);
00810 }
00811 if (stat != OCI_SUCCESS) {
00812 rodsLog(LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
00813 stat);
00814 logOraError(LOG_ERROR, p_err, stat);
00815 return(CAT_OCI_ERROR);
00816 }
00817
00818
00819 stat = OCIAttrGet((dvoid*) mypard, (ub4) OCI_DTYPE_PARAM,
00820 &colName,(ub4 *) 0,
00821 (ub4) OCI_ATTR_NAME,
00822 (OCIError *) p_err );
00823 if (stat != OCI_SUCCESS) {
00824 rodsLog(LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
00825 stat);
00826 logOraError(LOG_ERROR, p_err, stat);
00827 return(CAT_OCI_ERROR);
00828 }
00829
00830 columnLength[counter]=col_width;
00831
00832 i = counter-1;
00833 columnLength[i]=col_width;
00834
00835 if (strlen((char *)colName)>col_width) columnLength[i]=strlen((char*)colName);
00836
00837 myStatement->resultColName[i] = (char *)malloc((int)columnLength[i]+2);
00838 strncpy(myStatement->resultColName[i],
00839 (char *)colName, columnLength[i]);
00840
00841
00842
00843
00844
00845 if (strcmp (myStatement->resultColName[i], "RESC_NETRESC_DEF_PATH")==0) {
00846 strncpy(myStatement->resultColName[i],
00847 "RESC_NET", columnLength[i]);
00848 }
00849
00850 if (strcmp (myStatement->resultColName[i],
00851 "USER_DISTIN_NAMEUSER_INFO")==0) {
00852 strncpy(myStatement->resultColName[i],
00853 "USER_DISTIN_NAME", columnLength[i]);
00854 }
00855
00856
00857 cptr = (char*)myStatement->resultColName[i];
00858 for (j=0;j<columnLength[i];j++) {
00859 if (*cptr=='\0') break;
00860 if (*cptr==':') break;
00861 if (*cptr >= 'A' && *cptr<='Z') *cptr+=((int)'a'-(int)'A');
00862 cptr++;
00863 }
00864
00865 myStatement->resultValue[i] = (char *)malloc((int)columnLength[i]+2);
00866 strcpy((char *)myStatement->resultValue[i],"");
00867
00868 stat = OCIDefineByPos(p_statement, &p_dfn, p_err, counter,
00869 (dvoid *) myStatement->resultValue[i],
00870 (sword) columnLength[i], SQLT_STR,
00871 (dvoid *)&indicator[i],
00872 (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
00873 if (stat != OCI_SUCCESS) {
00874 rodsLog(LOG_ERROR, "cllExecSqlWithResult: OCIDefineByPos failed: %d",stat);
00875 logOraError(LOG_ERROR, p_err, stat);
00876 return(CAT_OCI_ERROR);
00877 }
00878
00879
00880
00881 counter++;
00882 stat = OCIParamGet((dvoid *)p_statement, OCI_HTYPE_STMT, p_err,
00883 (dvoid **)&mypard, (ub4) counter);
00884 }
00885
00886
00887 if (counter==1) {
00888 rodsLog(LOG_ERROR, "cllExecSqlWithResult: SQLNumResultCols failed: %d",
00889 stat);
00890 return(-2);
00891 }
00892 myStatement->numOfCols=counter-1;
00893
00894 return(0);
00895 }
00896
00897
00898
00899
00900
00901 int
00902 cllExecSqlWithResultBV(
00903 icatSessionStruct *icss,
00904 int *stmtNum, char *sql,
00905 const char *bindVar1,
00906 const char *bindVar2,
00907 const char *bindVar3,
00908 const char *bindVar4,
00909 const char *bindVar5,
00910 const char *bindVar6) {
00911
00912 int done;
00913 done=0;
00914 if (bindVar1 != NULL && strlen(bindVar1) > 0) {
00915 cllBindVars[cllBindVarCount++]=bindVar1;
00916 } else {
00917 done=1;
00918 }
00919 if (!done && bindVar2 != NULL && strlen(bindVar2) > 0) {
00920 cllBindVars[cllBindVarCount++]=bindVar2;
00921 } else {
00922 done=1;
00923 }
00924 if (!done && bindVar3 != NULL && strlen(bindVar3) > 0) {
00925 cllBindVars[cllBindVarCount++]=bindVar3;
00926 } else {
00927 done=1;
00928 }
00929 if (!done && bindVar4 != NULL && strlen(bindVar4) > 0) {
00930 cllBindVars[cllBindVarCount++]=bindVar4;
00931 } else {
00932 done=1;
00933 }
00934 if (!done && bindVar5 != NULL && strlen(bindVar5) > 0) {
00935 cllBindVars[cllBindVarCount++]=bindVar5;
00936 } else {
00937 done=1;
00938 }
00939 if (!done && bindVar6 != NULL && strlen(bindVar6) > 0) {
00940 cllBindVars[cllBindVarCount++]=bindVar6;
00941 } else {
00942 done=1;
00943 }
00944 return(cllExecSqlWithResult(icss, stmtNum, sql));
00945 }
00946
00947
00948
00949
00950
00951 int
00952 cllNextValueString(char *itemName, char *outString, int maxSize) {
00953 snprintf(outString, maxSize, "%s.nextval", itemName);
00954 return 0;
00955 }
00956
00957 int
00958 cllCurrentValueString(char *itemName, char *outString, int maxSize) {
00959 snprintf(outString, maxSize, "%s.currval", itemName);
00960 return 0;
00961 }
00962
00963
00964
00965
00966
00967 int
00968 cllGetRowCount(icatSessionStruct *icss, int statementNumber) {
00969 int i, stat;
00970
00971 icatStmtStrct *myStatement;
00972 OCIStmt *p_statement;
00973 OCIParam *p_param = (OCIParam *) 0;
00974 ub4 rowCount;
00975 ub4 *pUb4;
00976
00977 void * vptr;
00978 vptr = alignDouble((void *)&rowCount);
00979
00980
00981
00982 pUb4 = (ub4 *)calloc(sizeof(rowCount));
00983
00984 myStatement=icss->stmtPtr[statementNumber];
00985 p_statement = (OCIStmt *)myStatement->stmtPtr;
00986 stat = OCIParamGet((dvoid *)p_statement, OCI_HTYPE_STMT, p_err,
00987 (dvoid **)&p_param, (ub4) 1);
00988 if (stat == OCI_SUCCESS) {
00989 stat = OCIAttrGet((dvoid*) p_param, (ub4) OCI_DTYPE_PARAM,
00990 pUb4,(ub4 *) 0, (ub4) OCI_ATTR_ROW_COUNT,
00991 (OCIError *) p_err );
00992 if (stat != OCI_SUCCESS) {
00993 rodsLog(LOG_ERROR, "cllGetRowCount: OCIAttrGet failed: %d",stat);
00994 logOraError(LOG_ERROR, p_err, stat);
00995 free(pUb4);
00996 return(CAT_OCI_ERROR);
00997 }
00998 } else {
00999 rodsLog( LOG_ERROR, "cllGetRowCount :: OCIParamGet failed." );
01000 return CAT_OCI_ERROR;
01001 }
01002 rowCount = *pUb4;
01003 i=rowCount;
01004 free(pUb4);
01005 return(rowCount);
01006 }
01007
01008
01009
01010
01011
01012 int
01013 cllFreeStatement(icatSessionStruct *icss, int statementNumber) {
01014 int stat;
01015 int i;
01016 OCIEnv *p_env;
01017
01018 icatStmtStrct *myStatement;
01019 OCIStmt *p_statement;
01020
01021 p_env = (OCIEnv *)icss->environPtr;
01022
01023 myStatement=icss->stmtPtr[statementNumber];
01024 if (myStatement==NULL) {
01025 return(0);
01026 }
01027 p_statement = (OCIStmt *)myStatement->stmtPtr;
01028
01029 for (i=0;i<myStatement->numOfCols;i++) {
01030 free(myStatement->resultValue[i]);
01031 free(myStatement->resultColName[i]);
01032 }
01033
01034 if (p_statement != NULL) {
01035 stat = OCIHandleFree(
01036 (dvoid *)p_statement,
01037 OCI_HTYPE_STMT);
01038
01039 if (stat != OCI_SUCCESS) {
01040 rodsLog(LOG_ERROR, "cllFreeStatement: OCIHandleFree failed: %d",
01041 stat);
01042 logOraError(LOG_ERROR, p_err, stat);
01043 return(CAT_OCI_ERROR);
01044 }
01045 }
01046
01047 free(myStatement);
01048 icss->stmtPtr[statementNumber]=0;
01049
01050 return (0);
01051 }
01052
01053
01054
01055
01056
01057 int
01058 _cllFreeStatementColumns(icatSessionStruct *icss, int statementNumber) {
01059 int i;
01060
01061 icatStmtStrct *myStatement;
01062
01063 myStatement=icss->stmtPtr[statementNumber];
01064
01065 for (i=0;i<myStatement->numOfCols;i++) {
01066 free(myStatement->resultValue[i]);
01067 free(myStatement->resultColName[i]);
01068 }
01069 return (0);
01070 }
01071
01072
01073
01074
01075
01076 int cllTest(char *userArg, char *pwArg) {
01077 int i;
01078 int j, k;
01079 int OK;
01080 int stmt;
01081 int numOfCols;
01082 char userName[500];
01083 int numRows;
01084
01085 struct passwd *ppasswd;
01086 icatSessionStruct icss;
01087
01088 icss.stmtPtr[0]=0;
01089 rodsLogSqlReq(1);
01090 OK=1;
01091 i = cllOpenEnv(&icss);
01092 if (i != 0) OK=0;
01093
01094 if (userArg==0 || *userArg=='\0') {
01095 ppasswd = getpwuid(getuid());
01096 strcpy(userName,ppasswd->pw_name);
01097 }
01098 else {
01099 strncpy(userName, userArg, 500);
01100 }
01101 printf("userName=%s\n",userName);
01102 printf("password=%s\n",pwArg);
01103
01104 strncpy(icss.databaseUsername,userName, DB_USERNAME_LEN);
01105 if (pwArg==0 || *pwArg=='\0') {
01106 strcpy(icss.databasePassword,"");
01107 }
01108 else {
01109 strncpy(icss.databasePassword,pwArg,DB_PASSWORD_LEN);
01110 }
01111
01112 i = cllConnect(&icss);
01113 if (i != 0) exit(-1);
01114
01115 i = cllExecSqlNoResult(&icss,"drop table test");
01116
01117 i = cllExecSqlNoResult(&icss,"create table test (i integer, a2345678901234567890123456789j integer, a varchar(50) )" );
01118 if (i != 0 && i !=CAT_SUCCESS_BUT_WITH_NO_INFO) OK=0;
01119
01120 i = cllExecSqlNoResult(&icss,
01121 "insert into test values ('1', '2', 'asdfas')");
01122 if (i != 0) OK=0;
01123
01124 i = cllExecSqlNoResult(&icss, "commit");
01125 if (i != 0) OK=0;
01126
01127 i = cllExecSqlNoResult(&icss, "insert into test values (2, 3, 'a')");
01128 if (i != 0) OK=0;
01129
01130 i = cllExecSqlNoResult(&icss, "commit");
01131 if (i != 0) OK=0;
01132
01133 i = cllExecSqlNoResult(&icss, "bad sql");
01134 if (i == 0) OK=0;
01135
01136 i = cllExecSqlNoResult(&icss, "delete from test where i = '1'");
01137 if (i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO) OK=0;
01138
01139 i = cllExecSqlNoResult(&icss, "commit");
01140 if (i != 0) OK=0;
01141
01142 i = cllExecSqlWithResult(&icss, &stmt, "select * from test where a = 'a'");
01143 if (i != 0) OK=0;
01144
01145 if (i == 0) {
01146 numOfCols = 1;
01147 for (j=0;j<10 && numOfCols>0;j++) {
01148 i = cllGetRow(&icss, stmt);
01149 if (i != 0) {
01150 OK=0;
01151 break;
01152 }
01153 else {
01154 numOfCols = icss.stmtPtr[stmt]->numOfCols;
01155 if (numOfCols == 0) {
01156 printf("No more rows returned\n");
01157 i = cllFreeStatement(&icss,stmt);
01158 }
01159 else {
01160 for (k=0; k<numOfCols || k < icss.stmtPtr[stmt]->numOfCols; k++){
01161 printf("resultValue[%d]=%s\n",k,
01162 icss.stmtPtr[stmt]->resultValue[k]);
01163 }
01164 }
01165 }
01166 }
01167 }
01168
01169 cllBindVars[cllBindVarCount++]="a";
01170 i = cllExecSqlWithResult(&icss, &stmt,
01171 "select * from test where a = ?");
01172 if (i != 0) OK=0;
01173
01174 numRows=0;
01175 if (i == 0) {
01176 numOfCols = 1;
01177 for (j=0;j<10 && numOfCols>0;j++) {
01178 i = cllGetRow(&icss, stmt);
01179 if (i != 0) {
01180 OK=0;
01181 }
01182 else {
01183 numOfCols = icss.stmtPtr[stmt]->numOfCols;
01184 if (numOfCols == 0) {
01185 printf("No more rows returned\n");
01186 i = cllFreeStatement(&icss,stmt);
01187 }
01188 else {
01189 numRows++;
01190 for (k=0; k<numOfCols || k<icss.stmtPtr[stmt]->numOfCols; k++){
01191 printf("resultValue[%d]=%s\n",k,
01192 icss.stmtPtr[stmt]->resultValue[k]);
01193 }
01194 }
01195 }
01196 }
01197 }
01198
01199 if (numRows != 1) {
01200 printf("Error: Did not return 1 row, %d instead\n", numRows);
01201 OK=0;
01202 }
01203
01204 i = cllExecSqlNoResult(&icss,"drop table test");
01205 if (i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO) OK=0;
01206
01207 i = cllExecSqlNoResult(&icss, "commit");
01208 if (i != 0) OK=0;
01209
01210 i = cllDisconnect(&icss);
01211 if (i != 0) OK=0;
01212
01213 i = cllCloseEnv(&icss);
01214 if (i != 0) OK=0;
01215
01216 if (OK) {
01217 printf("The tests all completed normally\n");
01218 return(0);
01219 }
01220 else {
01221 printf("One or more tests DID NOT complete normally\n");
01222 return(-1);
01223 }
01224 }