/* 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);
}
int cmlGetOneRowFromSql (char *sql,
char *cVal[],
int cValSize[],
int numOfCols,
icatSessionStruct *icss)
{
int i,j, stmtNum, ii;
i = cllExecSqlWithResultBV(icss, &stmtNum, sql,
0,0,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) {
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 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);
}
}
