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