int logExecuteStatus( int stat, const char *sql, const char *funcName ) { char * status; int stat2; status = "UNKNOWN"; if ( stat == OCI_SUCCESS ) { status = "SUCCESS"; } if ( stat == OCI_SUCCESS_WITH_INFO ) { status = "SUCCESS_WITH_INFO"; } if ( stat == OCI_NO_DATA ) { status = "NO_DATA"; } if ( stat == OCI_ERROR ) { status = "SQL_ERROR"; } if ( stat == OCI_INVALID_HANDLE ) { status = "HANDLE_ERROR"; } rodsLogSqlResult( status ); if ( stat == OCI_SUCCESS || stat == OCI_SUCCESS_WITH_INFO || stat == OCI_NO_DATA ) { return 0; } else { logTheBindVariables( LOG_ERROR ); rodsLog( LOG_ERROR, "%s OCIStmtExecute error: %d, sql:%s", funcName, stat, sql ); stat2 = logOraError( LOG_ERROR, p_err, stat ); return stat2; } }
/* Execute a SQL command that returns a result table, and and bind the default row; and allow optional bind variables. */ int cllExecSqlWithResultBV(icatSessionStruct *icss, int *stmtNum, char *sql, char *bindVar1, char *bindVar2, char *bindVar3, char *bindVar4, char *bindVar5, char *bindVar6) { RETCODE stat; HDBC myHdbc; HSTMT hstmt; SQLSMALLINT numColumns; SQLCHAR colName[MAX_TOKEN]; SQLSMALLINT colType; SQLSMALLINT colNameLen; SQL_UINT_OR_ULEN precision; SQLSMALLINT scale; SQL_INT_OR_LEN displaysize; #ifndef NEW_ODBC static SQLINTEGER resultDataSize; #endif icatStmtStrct *myStatement; int i; int statementNumber; char *status; char tmpStr[TMP_STR_LEN+2]; myHdbc = icss->connectPtr; rodsLog(LOG_DEBUG1, sql); stat = SQLAllocStmt(myHdbc, &hstmt); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLAllocStmt failed: %d", stat); return(-1); } statementNumber=-1; for (i=0;i<MAX_NUM_OF_CONCURRENT_STMTS && statementNumber<0;i++) { if (icss->stmtPtr[i]==0) { statementNumber=i; } } if (statementNumber<0) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: too many concurrent statements"); return(-2); } myStatement = (icatStmtStrct *)malloc(sizeof(icatStmtStrct)); icss->stmtPtr[statementNumber]=myStatement; myStatement->stmtPtr=hstmt; if ((bindVar1 != 0 && *bindVar1 != '\0') || (bindVar2 != 0 && *bindVar2 != '\0') || (bindVar3 != 0 && *bindVar3 != '\0') || (bindVar4 != 0 && *bindVar4 != '\0')) { rodsLogSql("SQLPrepare"); stat = SQLPrepare(hstmt, (unsigned char *)sql, SQL_NTS); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLPrepare failed: %d", stat); return(-1); } if (bindVar1 != 0 && *bindVar1 != '\0') { stat = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_C_CHAR, 0, 0, bindVar1, 0, 0); snprintf(tmpStr, TMP_STR_LEN, "bindVar1=%s", bindVar1); rodsLogSql(tmpStr); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLBindParameter failed: %d", stat); return(-1); } } if (bindVar2 != 0 && *bindVar2 != '\0') { stat = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_C_CHAR, 0, 0, bindVar2, 0, 0); snprintf(tmpStr, TMP_STR_LEN, "bindVar2=%s", bindVar2); rodsLogSql(tmpStr); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLBindParameter failed: %d", stat); return(-1); } } if (bindVar3 != 0 && *bindVar3 != '\0') { stat = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_C_CHAR, 0, 0, bindVar3, 0, 0); snprintf(tmpStr, TMP_STR_LEN, "bindVar3=%s", bindVar3); rodsLogSql(tmpStr); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLBindParameter failed: %d", stat); return(-1); } } if (bindVar4 != 0 && *bindVar4 != '\0') { stat = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_C_CHAR, 0, 0, bindVar4, 0, 0); snprintf(tmpStr, TMP_STR_LEN, "bindVar4=%s", bindVar4); rodsLogSql(tmpStr); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLBindParameter failed: %d", stat); return(-1); } } if (bindVar5 != 0 && *bindVar5 != '\0') { stat = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_C_CHAR, 0, 0, bindVar5, 0, 0); snprintf(tmpStr, TMP_STR_LEN, "bindVar5=%s", bindVar5); rodsLogSql(tmpStr); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLBindParameter failed: %d", stat); return(-1); } } rodsLogSql(sql); stat = SQLExecute(hstmt); } else { rodsLogSql(sql); stat = SQLExecDirect(hstmt, (unsigned char *)sql, SQL_NTS); } status = "UNKNOWN"; if (stat == SQL_SUCCESS) status= "SUCCESS"; if (stat == SQL_SUCCESS_WITH_INFO) status="SUCCESS_WITH_INFO"; if (stat == SQL_NO_DATA_FOUND) status="NO_DATA"; if (stat == SQL_ERROR) status="SQL_ERROR"; if (stat == SQL_INVALID_HANDLE) status="HANDLE_ERROR"; rodsLogSqlResult(status); if (stat == SQL_SUCCESS || stat == SQL_SUCCESS_WITH_INFO || stat == SQL_NO_DATA_FOUND) { } else { logBindVars(LOG_NOTICE, bindVar1, bindVar2, bindVar3, bindVar4, bindVar5, bindVar6); rodsLog(LOG_NOTICE, "cllExecSqlWithResultBV: SQLExecDirect error: %d, sql:%s", stat, sql); logPsgError(LOG_NOTICE, icss->environPtr, myHdbc, hstmt, icss->databaseType); return(-1); } stat = SQLNumResultCols(hstmt, &numColumns); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLNumResultCols failed: %d", stat); return(-2); } myStatement->numOfCols=numColumns; for (i = 0; i<numColumns; i++) { stat = SQLDescribeCol(hstmt, i+1, colName, sizeof(colName), &colNameLen, &colType, &precision, &scale, NULL); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLDescribeCol failed: %d", stat); return(-3); } /* printf("colName='%s' precision=%d\n",colName, precision); */ columnLength[i]=precision; stat = SQLColAttribute(hstmt, i+1, SQL_COLUMN_DISPLAY_SIZE, NULL, 0, NULL, &displaysize); // JMC :: changed to SQLColAttribute for odbc update if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLColAttributes failed: %d", stat); return(-3); } if (displaysize > ((int)strlen((char *) colName))) { columnLength[i] = displaysize + 1; } else { columnLength[i] = strlen((char *) colName) + 1; } /* printf("columnLength[%d]=%d\n",i,columnLength[i]); */ myStatement->resultValue[i] = (char*)malloc((int)columnLength[i]); strcpy((char *)myStatement->resultValue[i],""); #if 1 // =-=-=-=-=-=-=- // JMC :: added static array to catch the result set size. this was necessary to stat = SQLBindCol(hstmt, i+1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], &resultDataSizeArray[i] ); #else #ifdef NEW_ODBC stat = SQLBindCol(hstmt, i+1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], NULL); /* The last argument could be resultDataSize (a SQLINTEGER location), which will be returned later via the SQLFetch. Since unused now, passing in NULL tells ODBC to skip it */ #else /* The old ODBC needs a non-NULL value */ stat = SQLBindCol(hstmt, i+1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], &resultDataSize); #endif #endif if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResultBV: SQLColAttributes failed: %d", stat); return(-4); } myStatement->resultColName[i] = (char*)malloc((int)columnLength[i]); strncpy(myStatement->resultColName[i], (char *)colName, columnLength[i]); } *stmtNum = statementNumber; return(0); }
/* Execute a SQL command that returns a result table, and and bind the default row. This version now uses the global array of bind variables. */ int cllExecSqlWithResult(icatSessionStruct *icss, int *stmtNum, char *sql) { RETCODE stat; HDBC myHdbc; HSTMT hstmt; SQLSMALLINT numColumns; SQLCHAR colName[MAX_TOKEN]; SQLSMALLINT colType; SQLSMALLINT colNameLen; SQL_UINT_OR_ULEN precision; SQLSMALLINT scale; SQL_INT_OR_LEN displaysize; #ifndef NEW_ODBC static SQLINTEGER resultDataSize; #endif icatStmtStrct *myStatement; int i; int statementNumber; char *status; /* In 2.2 and some versions before, this would call _cllExecSqlNoResult with "begin", similar to how cllExecSqlNoResult does. But since this function is called for 'select's, this is not needed here, and in fact causes postgres processes to be in the 'idle in transaction' state which prevents some operations (such as backup). So this was removed. */ myHdbc = icss->connectPtr; rodsLog(LOG_DEBUG1, sql); stat = SQLAllocStmt(myHdbc, &hstmt); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResult: SQLAllocStmt failed: %d", stat); return(-1); } statementNumber=-1; for (i=0;i<MAX_NUM_OF_CONCURRENT_STMTS && statementNumber<0;i++) { if (icss->stmtPtr[i]==0) { statementNumber=i; } } if (statementNumber<0) { rodsLog(LOG_ERROR, "cllExecSqlWithResult: too many concurrent statements"); return(-2); } myStatement = (icatStmtStrct *)malloc(sizeof(icatStmtStrct)); icss->stmtPtr[statementNumber]=myStatement; myStatement->stmtPtr=hstmt; if (bindTheVariables(hstmt, sql) != 0) return(-1); rodsLogSql(sql); stat = SQLExecDirect(hstmt, (unsigned char *)sql, SQL_NTS); status = "UNKNOWN"; if (stat == SQL_SUCCESS) status= "SUCCESS"; if (stat == SQL_SUCCESS_WITH_INFO) status="SUCCESS_WITH_INFO"; if (stat == SQL_NO_DATA_FOUND) status="NO_DATA"; if (stat == SQL_ERROR) status="SQL_ERROR"; if (stat == SQL_INVALID_HANDLE) status="HANDLE_ERROR"; rodsLogSqlResult(status); if (stat == SQL_SUCCESS || stat == SQL_SUCCESS_WITH_INFO || stat == SQL_NO_DATA_FOUND) { } else { logTheBindVariables(LOG_NOTICE); rodsLog(LOG_NOTICE, "cllExecSqlWithResult: SQLExecDirect error: %d, sql:%s", stat, sql); logPsgError(LOG_NOTICE, icss->environPtr, myHdbc, hstmt, icss->databaseType); return(-1); } stat = SQLNumResultCols(hstmt, &numColumns); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResult: SQLNumResultCols failed: %d", stat); return(-2); } myStatement->numOfCols=numColumns; for (i = 0; i<numColumns; i++) { stat = SQLDescribeCol(hstmt, i+1, colName, sizeof(colName), &colNameLen, &colType, &precision, &scale, NULL); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResult: SQLDescribeCol failed: %d", stat); return(-3); } /* printf("colName='%s' precision=%d\n",colName, precision); */ columnLength[i]=precision; stat = SQLColAttribute(hstmt, i+1, SQL_COLUMN_DISPLAY_SIZE, NULL, 0, NULL, &displaysize); // JMC :: fixed for odbc if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResult: SQLColAttributes failed: %d", stat); return(-3); } if (displaysize > ((int)strlen((char *) colName))) { columnLength[i] = displaysize + 1; } else { columnLength[i] = strlen((char *) colName) + 1; } /* printf("columnLength[%d]=%d\n",i,columnLength[i]); */ myStatement->resultValue[i] = (char*)malloc((int)columnLength[i]); strcpy((char *)myStatement->resultValue[i],""); #if 1 // =-=-=-=-=-=-=- // JMC :: added static array to catch the result set size. this was necessary to stat = SQLBindCol(hstmt, i+1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], &resultDataSizeArray[ i ] ); #else #if NEW_ODBC stat = SQLBindCol(hstmt, i+1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], NULL); /* The last argument could be resultDataSize (a SQLINTEGER location), which will be returned later via the SQLFetch. Since unused now, passing in NULL tells ODBC to skip it */ #else /* The old ODBC needs a non-NULL value */ stat = SQLBindCol(hstmt, i+1, SQL_C_CHAR, myStatement->resultValue[i], columnLength[i], &resultDataSize); #endif #endif if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "cllExecSqlWithResult: SQLColAttributes failed: %d", stat); return(-4); } myStatement->resultColName[i] = (char*)malloc((int)columnLength[i]); strncpy(myStatement->resultColName[i], (char *)colName, columnLength[i]); } *stmtNum = statementNumber; return(0); }
/* Execute a SQL command which has no resulting table. With optional bind variables. If option is 1, skip the bind variables. */ int _cllExecSqlNoResult(icatSessionStruct *icss, char *sql, int option) { RETCODE stat; HDBC myHdbc; HSTMT myHstmt; int result; char *status; SQL_INT_OR_LEN rowCount; #ifdef NEW_ODBC int i; #endif noResultRowCount=0; rowCount=0; myHdbc = icss->connectPtr; rodsLog(LOG_DEBUG1, sql); stat = SQLAllocStmt(myHdbc, &myHstmt); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "_cllExecSqlNoResult: SQLAllocStmt failed: %d", stat); return(-1); } #if 0 if (bindVar1 != 0 && *bindVar1 != '\0') { stat = SQLBindParameter(myHstmt, 1, SQL_PARAM_INPUT, SQL_C_SBIGINT, SQL_C_SBIGINT, 0, 0, 0, 0, 0); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "_cllExecSqlNoResult: SQLAllocStmt failed: %d", stat); return(-1); } } #endif if (option==0) { if (bindTheVariables(myHstmt, sql) != 0) return(-1); } rodsLogSql(sql); stat = SQLExecDirect(myHstmt, (unsigned char *)sql, SQL_NTS); status = "UNKNOWN"; if (stat == SQL_SUCCESS) status= "SUCCESS"; if (stat == SQL_SUCCESS_WITH_INFO) status="SUCCESS_WITH_INFO"; if (stat == SQL_NO_DATA_FOUND) status="NO_DATA"; if (stat == SQL_ERROR) status="SQL_ERROR"; if (stat == SQL_INVALID_HANDLE) status="HANDLE_ERROR"; rodsLogSqlResult(status); if (stat == SQL_SUCCESS || stat == SQL_SUCCESS_WITH_INFO || stat == SQL_NO_DATA_FOUND) { cllCheckPending(sql, 0, icss->databaseType); result = 0; if (stat == SQL_NO_DATA_FOUND) result = CAT_SUCCESS_BUT_WITH_NO_INFO; #ifdef NEW_ODBC /* ODBC says that if statement is not UPDATE, INSERT, or DELETE then SQLRowCount may return anything. So for BEGIN, COMMIT and ROLLBACK we don't want to call it but just return OK. */ if ( ! cmp_stmt(sql,"begin") && ! cmp_stmt(sql,"commit") && ! cmp_stmt(sql,"rollback") ) { /* Doesn't seem to return SQL_NO_DATA_FOUND, so check */ i = SQLRowCount (myHstmt, (SQL_INT_OR_LEN *)&rowCount); if (i) { /* error getting rowCount???, just call it no_info */ result = CAT_SUCCESS_BUT_WITH_NO_INFO; } if (rowCount==0) result = CAT_SUCCESS_BUT_WITH_NO_INFO; } #else rowCount=0; /* avoid compiler warning */ #endif } else { if (option==0) { logTheBindVariables(LOG_NOTICE); } rodsLog(LOG_NOTICE,"_cllExecSqlNoResult: SQLExecDirect error: %d sql:%s", stat, sql); result = logPsgError(LOG_NOTICE, icss->environPtr, myHdbc, myHstmt, icss->databaseType); } stat = SQLFreeStmt(myHstmt, SQL_DROP); if (stat != SQL_SUCCESS) { rodsLog(LOG_ERROR, "_cllExecSqlNoResult: SQLFreeStmt error: %d", stat); } noResultRowCount = rowCount; return(result); }