/* with bind-variables */
int cmlGetFirstRowFromSqlBV (char *sql,
char *arg1, char *arg2, char *arg3, char *arg4,
int *statement,
icatSessionStruct *icss)
{
int i, stmtNum, ii;
*statement=0;
i = cllExecSqlWithResultBV(icss, &stmtNum, sql,
arg1,arg2,arg3,arg4,0,0);
if (i != 0) {
if (i <= CAT_ENV_ERR) return(i); /* already an iRODS error code */
return (CAT_SQL_ERR);
}
i = cllGetRow(icss,stmtNum);
if (i != 0) {
ii = cllFreeStatement(icss,stmtNum);
return(CAT_GET_ROW_ERR);
}
if (icss->stmtPtr[stmtNum]->numOfCols == 0) {
i = cllFreeStatement(icss,stmtNum);
return(CAT_NO_ROWS_FOUND);
}
*statement = stmtNum;
return(0);
}
/*
Like cmlGetOneRowFromSql but uses bind variable array (via
cllExecSqlWithResult).
*/
static
int cmlGetOneRowFromSqlV3 (char *sql,
char *cVal[],
int cValSize[],
int numOfCols,
icatSessionStruct *icss)
{
int i,j, stmtNum, ii;
i = cllExecSqlWithResult(icss, &stmtNum, sql);
if (i != 0) {
if (i <= CAT_ENV_ERR) return(i); /* already an iRODS error code */
return (CAT_SQL_ERR);
}
i = cllGetRow(icss,stmtNum);
if (i != 0) {
ii = cllFreeStatement(icss,stmtNum);
return(CAT_GET_ROW_ERR);
}
if (icss->stmtPtr[stmtNum]->numOfCols == 0) {
ii = cllFreeStatement(icss,stmtNum);
return(CAT_NO_ROWS_FOUND);
}
for (j = 0; j < numOfCols && j < icss->stmtPtr[stmtNum]->numOfCols ; j++ )
rstrcpy(cVal[j],icss->stmtPtr[stmtNum]->resultValue[j],cValSize[j]);
i = cllFreeStatement(icss,stmtNum);
return(j);
}
/* like cmlGetOneRowFromSql but cVal uses space from query
and then caller frees it later (via cmlFreeStatement).
This is simplier for the caller, in some cases. */
int cmlGetOneRowFromSqlV2 (char *sql,
char *cVal[],
int maxCols,
char *bindVar1,
char *bindVar2,
icatSessionStruct *icss)
{
int i,j, stmtNum, ii;
i = cllExecSqlWithResultBV(icss, &stmtNum, sql,
bindVar1, bindVar2,0,0,0,0);
if (i != 0) {
if (i <= CAT_ENV_ERR) return(i); /* already an iRODS error code */
return (CAT_SQL_ERR);
}
i = cllGetRow(icss,stmtNum);
if (i != 0) {
ii = cllFreeStatement(icss,stmtNum);
return(CAT_GET_ROW_ERR);
}
if (icss->stmtPtr[stmtNum]->numOfCols == 0)
return(CAT_NO_ROWS_FOUND);
for (j = 0; j < maxCols && j < icss->stmtPtr[stmtNum]->numOfCols ; j++ )
cVal[j] = icss->stmtPtr[stmtNum]->resultValue[j];
return(stmtNum); /* 0 or positive is the statement number */
}
int cmlGetFirstRowFromSql (char *sql,
int *statement,
int skipCount,
icatSessionStruct *icss)
{
int i, stmtNum, ii;
#ifdef ORA_ICAT
int j;
#endif
*statement=0;
i = cllExecSqlWithResult(icss, &stmtNum, sql);
if (i != 0) {
if (i <= CAT_ENV_ERR) return(i); /* already an iRODS error code */
return (CAT_SQL_ERR);
}
#ifdef ORA_ICAT
if (skipCount > 0) {
for (j=0;j<skipCount;j++) {
i = cllGetRow(icss,stmtNum);
if (i != 0) {
ii = cllFreeStatement(icss,stmtNum);
return(CAT_GET_ROW_ERR);
}
if (icss->stmtPtr[stmtNum]->numOfCols == 0) {
i = cllFreeStatement(icss,stmtNum);
return(CAT_NO_ROWS_FOUND);
}
}
}
#endif
i = cllGetRow(icss,stmtNum);
if (i != 0) {
ii = cllFreeStatement(icss,stmtNum);
return(CAT_GET_ROW_ERR);
}
if (icss->stmtPtr[stmtNum]->numOfCols == 0) {
i = cllFreeStatement(icss,stmtNum);
return(CAT_NO_ROWS_FOUND);
}
*statement = stmtNum;
return(0);
}
int cmlGetNextRowFromStatement (int stmtNum,
icatSessionStruct *icss)
{
int i, ii;
i = cllGetRow(icss,stmtNum);
if (i != 0) {
ii = cllFreeStatement(icss,stmtNum);
return(CAT_GET_ROW_ERR);
}
if (icss->stmtPtr[stmtNum]->numOfCols == 0) {
i = cllFreeStatement(icss,stmtNum);
return(CAT_NO_ROWS_FOUND);
}
return(0);
}
int cmlGetMultiRowStringValuesFromSql (char *sql,
char *returnedStrings,
int maxStringLen,
int maxNumberOfStringsToGet,
char *bindVar1,
char *bindVar2,
icatSessionStruct *icss) {
int i,j, stmtNum, ii;
int tsg; /* total strings gotten */
char *pString;
if (maxNumberOfStringsToGet <= 0) return(CAT_INVALID_ARGUMENT);
i = cllExecSqlWithResultBV(icss, &stmtNum, sql,
bindVar1,bindVar2,0,0,0,0);
if (i != 0) {
if (i <= CAT_ENV_ERR) return(i); /* already an iRODS error code */
return (CAT_SQL_ERR);
}
tsg = 0;
pString = returnedStrings;
for (;;) {
i = cllGetRow(icss,stmtNum);
if (i != 0) {
ii = cllFreeStatement(icss,stmtNum);
if (tsg > 0) return(tsg);
return(CAT_GET_ROW_ERR);
}
if (icss->stmtPtr[stmtNum]->numOfCols == 0) {
ii = cllFreeStatement(icss,stmtNum);
if (tsg > 0) return(tsg);
return(CAT_NO_ROWS_FOUND);
}
for (j = 0; j < icss->stmtPtr[stmtNum]->numOfCols;j++) {
rstrcpy(pString, icss->stmtPtr[stmtNum]->resultValue[j],
maxStringLen);
tsg++;
pString+=maxStringLen;
if (tsg >= maxNumberOfStringsToGet) {
i = cllFreeStatement(icss,stmtNum);
return(tsg);
}
}
}
}
/*
A few tests to verify basic functionality (including talking with
the database via ODBC).
*/
int cllTest(char *userArg, char *pwArg) {
int i;
int j, k;
int OK;
int stmt;
int numOfCols;
char userName[500];
int ival;
struct passwd *ppasswd;
icatSessionStruct icss;
icss.stmtPtr[0]=0;
icss.databaseType = DB_TYPE_POSTGRES;
#ifdef MY_ICAT
icss.databaseType = DB_TYPE_MYSQL;
#endif
rodsLogSqlReq(1);
OK=1;
i = cllOpenEnv(&icss);
if (i != 0) OK=0;
if (userArg==0 || *userArg=='\0') {
ppasswd = getpwuid(getuid()); /* get user passwd entry */
strcpy(userName,ppasswd->pw_name); /* get user name */
}
else {
strncpy(userName, userArg, 500);
}
printf("userName=%s\n",userName);
printf("password=%s\n",pwArg);
strncpy(icss.databaseUsername,userName, DB_USERNAME_LEN);
if (pwArg==0 || *pwArg=='\0') {
strcpy(icss.databasePassword,"");
}
else {
strncpy(icss.databasePassword,pwArg, DB_PASSWORD_LEN);
}
i = cllConnect(&icss);
if (i != 0) exit(-1);
i = cllExecSqlNoResult(&icss,"create table test (i integer, j integer, a varchar(32))");
if (i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO) OK=0;
i = cllExecSqlNoResult(&icss, "insert into test values (2, 3, 'a')");
if (i != 0) OK=0;
i = cllExecSqlNoResult(&icss, "commit");
if (i != 0) OK=0;
i = cllExecSqlNoResult(&icss, "bad sql");
if (i == 0) OK=0; /* should fail, if not it's not OK */
i = cllExecSqlNoResult(&icss, "rollback"); /* close the bad transaction*/
i = cllExecSqlNoResult(&icss, "delete from test where i = 1");
if (i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO) OK=0;
i = cllExecSqlNoResult(&icss, "commit");
if (i != 0) OK=0;
i = cllExecSqlWithResultBV(&icss, &stmt,
"select * from test where a = ?",
"a",0 ,0,0,0,0);
if (i != 0) OK=0;
if (i == 0) {
numOfCols = 1;
for (j=0;j<10 && numOfCols>0;j++) {
i = cllGetRow(&icss, stmt);
if (i != 0) {
OK=0;
}
else {
numOfCols = icss.stmtPtr[stmt]->numOfCols;
if (numOfCols == 0) {
printf("No more rows returned\n");
i = cllFreeStatement(&icss,stmt);
}
else {
for (k=0; k<numOfCols || k < icss.stmtPtr[stmt]->numOfCols; k++){
printf("resultValue[%d]=%s\n",k,
icss.stmtPtr[stmt]->resultValue[k]);
}
}
}
}
}
ival=2;
i = cllExecSqlWithResultBV(&icss, &stmt,
"select * from test where i = ?",
"2",0,0,0,0,0);
if (i != 0) OK=0;
if (i == 0) {
numOfCols = 1;
for (j=0;j<10 && numOfCols>0;j++) {
i = cllGetRow(&icss, stmt);
if (i != 0) {
OK=0;
}
else {
numOfCols = icss.stmtPtr[stmt]->numOfCols;
if (numOfCols == 0) {
printf("No more rows returned\n");
i = cllFreeStatement(&icss,stmt);
}
else {
for (k=0; k<numOfCols || k < icss.stmtPtr[stmt]->numOfCols; k++){
printf("resultValue[%d]=%s\n",k,
icss.stmtPtr[stmt]->resultValue[k]);
}
}
}
}
}
i = cllExecSqlNoResult(&icss,"drop table test;");
if (i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO) OK=0;
i = cllExecSqlNoResult(&icss, "commit");
if (i != 0) OK=0;
i = cllDisconnect(&icss);
if (i != 0) OK=0;
i = cllCloseEnv(&icss);
if (i != 0) OK=0;
if (OK) {
printf("The tests all completed normally\n");
return(0);
}
else {
printf("One or more tests DID NOT complete normally\n");
return(-1);
}
}
/*
A few tests to verify basic functionality (including talking with
the database via ODBC).
*/
int cllTest( const char *userArg, const char *pwArg ) {
int i;
int j, k;
int OK;
int stmt;
int numOfCols;
char userName[500];
int numRows;
struct passwd *ppasswd;
icatSessionStruct icss;
icss.stmtPtr[0] = 0;
rodsLogSqlReq( 1 );
OK = 1;
i = cllOpenEnv( &icss );
if ( i != 0 ) {
OK = 0;
}
if ( userArg == 0 || *userArg == '\0' ) {
ppasswd = getpwuid( getuid() ); /* get user passwd entry */
strcpy( userName, ppasswd->pw_name ); /* get user name */
}
else {
strncpy( userName, userArg, 500 );
}
printf( "userName=%s\n", userName );
printf( "password=%s\n", pwArg );
strncpy( icss.databaseUsername, userName, DB_USERNAME_LEN );
if ( pwArg == 0 || *pwArg == '\0' ) {
strcpy( icss.databasePassword, "" );
}
else {
strncpy( icss.databasePassword, pwArg, DB_PASSWORD_LEN );
}
i = cllConnect( &icss );
if ( i != 0 ) {
exit( -1 );
}
i = cllExecSqlNoResult( &icss, "drop table test" );
i = cllExecSqlNoResult( &icss, "create table test (i integer, a2345678901234567890123456789j integer, a varchar(50) )" );
if ( i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO ) {
OK = 0;
}
i = cllExecSqlNoResult( &icss,
"insert into test values ('1', '2', 'asdfas')" );
if ( i != 0 ) {
OK = 0;
}
i = cllExecSqlNoResult( &icss, "commit" );
if ( i != 0 ) {
OK = 0;
}
i = cllExecSqlNoResult( &icss, "insert into test values (2, 3, 'a')" );
if ( i != 0 ) {
OK = 0;
}
i = cllExecSqlNoResult( &icss, "commit" );
if ( i != 0 ) {
OK = 0;
}
i = cllExecSqlNoResult( &icss, "bad sql" );
if ( i == 0 ) {
OK = 0; /* should fail, if not it's not OK */
}
i = cllExecSqlNoResult( &icss, "delete from test where i = '1'" );
if ( i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO ) {
OK = 0;
}
i = cllExecSqlNoResult( &icss, "commit" );
if ( i != 0 ) {
OK = 0;
}
i = cllExecSqlWithResult( &icss, &stmt, "select * from test where a = 'a'" );
if ( i != 0 ) {
OK = 0;
}
if ( i == 0 ) {
numOfCols = 1;
for ( j = 0; j < 10 && numOfCols > 0; j++ ) {
i = cllGetRow( &icss, stmt );
if ( i != 0 ) {
OK = 0;
break;
}
else {
numOfCols = icss.stmtPtr[stmt]->numOfCols;
if ( numOfCols == 0 ) {
printf( "No more rows returned\n" );
i = cllFreeStatement( &icss, stmt );
}
else {
for ( k = 0; k < numOfCols || k < icss.stmtPtr[stmt]->numOfCols; k++ ) {
printf( "resultValue[%d]=%s\n", k,
icss.stmtPtr[stmt]->resultValue[k] );
}
}
}
}
}
cllBindVars[cllBindVarCount++] = "a";
i = cllExecSqlWithResult( &icss, &stmt,
"select * from test where a = ?" );
if ( i != 0 ) {
OK = 0;
}
numRows = 0;
if ( i == 0 ) {
numOfCols = 1;
for ( j = 0; j < 10 && numOfCols > 0; j++ ) {
i = cllGetRow( &icss, stmt );
if ( i != 0 ) {
OK = 0;
}
else {
numOfCols = icss.stmtPtr[stmt]->numOfCols;
if ( numOfCols == 0 ) {
printf( "No more rows returned\n" );
i = cllFreeStatement( &icss, stmt );
}
else {
numRows++;
for ( k = 0; k < numOfCols || k < icss.stmtPtr[stmt]->numOfCols; k++ ) {
printf( "resultValue[%d]=%s\n", k,
icss.stmtPtr[stmt]->resultValue[k] );
}
}
}
}
}
if ( numRows != 1 ) {
printf( "Error: Did not return 1 row, %d instead\n", numRows );
OK = 0;
}
i = cllExecSqlNoResult( &icss, "drop table test" );
if ( i != 0 && i != CAT_SUCCESS_BUT_WITH_NO_INFO ) {
OK = 0;
}
i = cllExecSqlNoResult( &icss, "commit" );
if ( i != 0 ) {
OK = 0;
}
i = cllDisconnect( &icss );
if ( i != 0 ) {
OK = 0;
}
i = cllCloseEnv( &icss );
if ( i != 0 ) {
OK = 0;
}
if ( OK ) {
printf( "The tests all completed normally\n" );
return 0;
}
else {
printf( "One or more tests DID NOT complete normally\n" );
return -1;
}
}