/*
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 that returns a result table, and and bind the
default row. Also check and bind the global array of bind variables
(if any).
*/
int
cllExecSqlWithResult( icatSessionStruct *icss, int *stmtNum, const char *sql ) {
OCIEnv *p_env;
OCISvcCtx *p_svc;
static OCIStmt *p_statement;
static OCIDefine *p_dfn = ( OCIDefine * ) 0;
int stat, stat2, i, j;
char *cptr;
char sqlConverted[MAX_SQL_SIZE];
icatStmtStrct *myStatement;
int statementNumber;
int counter;
OCIParam *mypard = ( OCIParam * ) 0;
ub2 dtype;
ub2 col_width;
ub4 char_semantics;
OraText *colName;
static int columnLength[MAX_TOKEN];
static sb2 indicator[MAX_TOKEN];
p_svc = ( OCISvcCtx * )icss->connectPtr;
p_env = ( OCIEnv * )icss->environPtr;
i = convertSqlToOra( sql, sqlConverted );
if ( i != 0 ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: SQL too long" );
return CAT_OCI_ERROR;
}
/* Allocate SQL statement */
stat = OCIHandleAlloc( ( dvoid * ) p_env, ( dvoid ** ) &p_statement,
OCI_HTYPE_STMT, ( size_t ) 0, ( dvoid ** ) 0 );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIHandleAlloc failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* set up our statement */
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->numOfCols = 0;
myStatement->stmtPtr = p_statement;
/* Prepare SQL statement */
stat = OCIStmtPrepare( p_statement, p_err, ( OraText * )sqlConverted,
( ub4 ) strlen( sqlConverted ),
( ub4 ) OCI_NTV_SYNTAX, ( ub4 ) OCI_DEFAULT );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIStmtPrepare failed: %d", stat );
rodsLog( LOG_ERROR, sqlConverted );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
if ( bindTheVariables( p_statement, sqlConverted ) != 0 ) {
logTheBindVariables( LOG_ERROR );
return CAT_OCI_ERROR;
}
logTheBindVariables( 0 );
rodsLogSql( sqlConverted );
/* Execute statement */
stat = OCIStmtExecute( p_svc, p_statement, p_err, ( ub4 ) 0, ( ub4 ) 0,
( CONST OCISnapshot * ) NULL, ( OCISnapshot * ) NULL,
OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sqlConverted, "cllExecSqlWithResult" );
if ( stat2 ) {
return stat2;
}
*stmtNum = statementNumber; /* return index to statement handle */
/* get the number of columns and width of the columns */
/* Request a parameter descriptor for position 1 in the select-list */
counter = 1;
stat = OCIParamGet( ( dvoid * )p_statement, OCI_HTYPE_STMT, p_err,
( dvoid ** )&mypard, ( ub4 ) counter );
/* Loop only if a descriptor was successfully retrieved for
current position, starting at 1 */
while ( stat == OCI_SUCCESS ) {
/* Retrieve the datatype attribute */
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &dtype, ( ub4 * ) 0, ( ub4 ) OCI_ATTR_DATA_TYPE,
( OCIError * ) p_err );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* Retrieve the length semantics for the column */
char_semantics = 0;
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &char_semantics, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_CHAR_USED,
( OCIError * ) p_err );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* Retrieve the column width in characters */
col_width = 0;
if ( char_semantics ) {
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &col_width, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_CHAR_SIZE,
( OCIError * ) p_err );
}
else {
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
( dvoid* ) &col_width, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_DATA_SIZE,
( OCIError * ) p_err );
}
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* get the col name */
stat = OCIAttrGet( ( dvoid* ) mypard, ( ub4 ) OCI_DTYPE_PARAM,
&colName, ( ub4 * ) 0,
( ub4 ) OCI_ATTR_NAME,
( OCIError * ) p_err );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIAttrGet failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
columnLength[counter] = col_width;
i = counter - 1;
columnLength[i] = col_width;
if ( strlen( ( char * )colName ) > col_width ) {
columnLength[i] = strlen( ( char* )colName );
}
myStatement->resultColName[i] = ( char * )malloc( ( int )columnLength[i] + 2 );
strncpy( myStatement->resultColName[i],
( char * )colName, columnLength[i] );
/* Big cludge/hack, but it looks like an OCI bug when the colName
is exactly 8 bytes (or something). Anyway, when the column name
is "RESC_NETRESC_DEF_PATH" it's actually the running together of
two names, so the code below corrects it. */
if ( strcmp( myStatement->resultColName[i], "RESC_NETRESC_DEF_PATH" ) == 0 ) {
strncpy( myStatement->resultColName[i],
"RESC_NET", columnLength[i] );
}
/* Second case, second cludge/hack */
if ( strcmp( myStatement->resultColName[i],
"USER_DISTIN_NAMEUSER_INFO" ) == 0 ) {
strncpy( myStatement->resultColName[i],
"USER_DISTIN_NAME", columnLength[i] );
}
/* convert the column name to lower case to match postgres */
cptr = ( char* )myStatement->resultColName[i];
for ( j = 0; j < columnLength[i]; j++ ) {
if ( *cptr == '\0' ) {
break;
}
if ( *cptr == ':' ) {
break;
}
if ( *cptr >= 'A' && *cptr <= 'Z' ) {
*cptr += ( ( int )'a' - ( int )'A' );
}
cptr++;
}
myStatement->resultValue[i] = ( char * )malloc( ( int )columnLength[i] + 2 );
strcpy( ( char * )myStatement->resultValue[i], "" );
stat = OCIDefineByPos( p_statement, &p_dfn, p_err, counter,
( dvoid * ) myStatement->resultValue[i],
( sword ) columnLength[i], SQLT_STR,
( dvoid * )&indicator[i],
( ub2 * )0, ( ub2 * )0, OCI_DEFAULT );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: OCIDefineByPos failed: %d", stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* increment counter and get next descriptor, if there is one */
counter++;
stat = OCIParamGet( ( dvoid * )p_statement, OCI_HTYPE_STMT, p_err,
( dvoid ** )&mypard, ( ub4 ) counter );
}
if ( counter == 1 ) {
rodsLog( LOG_ERROR, "cllExecSqlWithResult: SQLNumResultCols failed: %d",
stat );
return -2;
}
myStatement->numOfCols = counter - 1;
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);
}
/*
Execute a SQL command which has no resulting table. Examples include
insert, delete, update.
*/
int
cllExecSqlNoResult( icatSessionStruct *icss, const char *sqlInput ) {
int stat, stat2, stat3;
OCIEnv *p_env;
OCISvcCtx *p_svc;
static OCIStmt *p_statement;
char sql[MAX_SQL_SIZE];
ub4 rows_affected;
ub4 *pUb4;
stat = convertSqlToOra( ( char* )sqlInput, sql );
if ( stat != 0 ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: SQL too long" );
return CAT_OCI_ERROR;
}
p_svc = ( OCISvcCtx * )icss->connectPtr;
p_env = ( OCIEnv * )icss->environPtr;
if ( strcmp( sql, "commit" ) == 0 ) {
rodsLogSql( sql );
stat = OCITransCommit( p_svc, p_err, ( ub4 ) OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sql, "cllExecSqlNoResult" );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCITransCommit failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
return 0;
}
if ( strcmp( sql, "rollback" ) == 0 ) {
rodsLogSql( sql );
stat = OCITransRollback( p_svc, p_err, ( ub4 ) OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sql, "cllExecSqlNoResult" );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCITransRollback failed: %d",
stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
return 0;
}
/* Allocate SQL statement */
stat = OCIHandleAlloc( ( dvoid * ) p_env, ( dvoid ** ) &p_statement,
OCI_HTYPE_STMT, ( size_t ) 0, ( dvoid ** ) 0 );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIHandleAlloc failed: %d", stat );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
/* Prepare SQL statement */
stat = OCIStmtPrepare( p_statement, p_err, ( OraText * )sql,
( ub4 ) strlen( sql ),
( ub4 ) OCI_NTV_SYNTAX, ( ub4 ) OCI_DEFAULT );
if ( stat != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIStmtPrepare failed: %d", stat );
rodsLog( LOG_ERROR, sql );
logOraError( LOG_ERROR, p_err, stat );
return CAT_OCI_ERROR;
}
if ( bindTheVariables( p_statement, sql ) != 0 ) {
logTheBindVariables( LOG_ERROR );
return CAT_OCI_ERROR;
}
logTheBindVariables( 0 );
rodsLogSql( sql );
/* Execute statement */
stat = OCIStmtExecute( p_svc, p_statement, p_err, ( ub4 ) 1, ( ub4 ) 0,
( CONST OCISnapshot * ) NULL, ( OCISnapshot * ) NULL,
OCI_DEFAULT );
stat2 = logExecuteStatus( stat, sql, "cllExecSqlNoResult" );
if ( stat == OCI_NO_DATA ) { /* Don't think this ever happens, but... */
return CAT_SUCCESS_BUT_WITH_NO_INFO;
}
/* malloc it so that it's aligned properly, else can get
bus errors when doing 64-bit addressing */
pUb4 = ( ub4* ) malloc( sizeof( rows_affected ) );
*pUb4 = 0;
rows_affected = 0;
if ( stat == OCI_ERROR ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIStmtExecute failed: %d", stat );
logOraError( LOG_ERROR, p_err, stat );
free( pUb4 );
if ( stat2 == CATALOG_ALREADY_HAS_ITEM_BY_THAT_NAME ) {
return stat2;
}
return CAT_OCI_ERROR;
}
stat3 = OCIAttrGet( ( dvoid * )p_statement, OCI_HTYPE_STMT, pUb4, 0,
OCI_ATTR_ROW_COUNT, ( OCIError * ) p_err );
rows_affected = *pUb4;
free( pUb4 );
stat = OCIHandleFree( ( dvoid * )p_statement, OCI_HTYPE_STMT ); /* free the
statement */
if ( stat3 != OCI_SUCCESS ) {
rodsLog( LOG_ERROR, "cllExecSqlNoResult: OCIAttrGet failed: %d", stat );
return stat3;
}
/* rodsLog(LOG_NOTICE, "cllExecSqlNoResult: OCIAttrGet, rows_affected: %d",
rows_affected); */
if ( rows_affected == 0 ) {
return CAT_SUCCESS_BUT_WITH_NO_INFO;
}
return stat2;
}