/*
Disconnect from the DBMS.
*/
int
cllDisconnect(icatSessionStruct *icss) {
RETCODE stat;
HDBC myHdbc;
int i;
myHdbc = icss->connectPtr;
i = cllCheckPending("", 1, icss->databaseType);
if (i==1) {
i = cllExecSqlNoResult(icss, "commit"); /* auto commit any
pending SQLs, including
the Audit ones */
/* Nothing to do if it fails */
}
stat = SQLDisconnect(myHdbc);
if (stat != SQL_SUCCESS) {
rodsLog(LOG_ERROR, "cllDisconnect: SQLDisconnect failed: %d", stat);
return(-1);
}
stat = SQLFreeConnect(myHdbc);
if (stat != SQL_SUCCESS) {
rodsLog(LOG_ERROR, "cllDisconnect: SQLFreeConnect failed: %d", stat);
return(-2);
}
icss->connectPtr = myHdbc;
return(0);
}
int cmlExecuteNoAnswerSql( char *sql,
icatSessionStruct *icss)
{
int i;
i = cllExecSqlNoResult(icss, sql);
if (i) {
if (i <= CAT_ENV_ERR) return(i); /* already an iRODS error code */
return(CAT_SQL_ERR);
}
return(0);
}
/*
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);
}
}
/*
Connect to the DBMS for database-resource/db-objects access.
*/
int
cllConnectDbr(icatSessionStruct *icss, char *odbcEntryName) {
RETCODE stat;
RETCODE stat2;
SQLCHAR buffer[SQL_MAX_MESSAGE_LENGTH + 1];
SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
SQLINTEGER sqlcode;
SQLSMALLINT length;
HDBC myHdbc;
stat = SQLAllocConnect(icss->environPtr,
&myHdbc);
if (stat != SQL_SUCCESS) {
rodsLog(LOG_ERROR, "cllConnect: SQLAllocConnect failed: %d, stat");
return (-1);
}
stat = SQLSetConnectOption(myHdbc,
SQL_AUTOCOMMIT, SQL_AUTOCOMMIT_OFF);
if (stat != SQL_SUCCESS) {
rodsLog(LOG_ERROR, "cllConnect: SQLSetConnectOption failed: %d", stat);
return (-1);
}
stat = SQLConnect(myHdbc, (unsigned char *)odbcEntryName, SQL_NTS,
(unsigned char *)icss->databaseUsername, SQL_NTS,
(unsigned char *)icss->databasePassword, SQL_NTS);
if (stat != SQL_SUCCESS) {
rodsLog(LOG_ERROR, "cllConnect: SQLConnect failed: %d", stat);
rodsLog(LOG_ERROR,
"cllConnect: SQLConnect failed:odbcEntry=%s,user=%s,pass=%s\n",
odbcEntryName,icss->databaseUsername, icss->databasePassword);
while (SQLError(icss->environPtr,myHdbc , 0, sqlstate, &sqlcode, buffer,
SQL_MAX_MESSAGE_LENGTH + 1, &length) == SQL_SUCCESS) {
rodsLog(LOG_ERROR, "cllConnect: SQLSTATE: %s\n", sqlstate);
rodsLog(LOG_ERROR, "cllConnect: Native Error Code: %ld\n", sqlcode);
rodsLog(LOG_ERROR, "cllConnect: %s \n", buffer);
}
stat2 = SQLDisconnect(myHdbc);
stat2 = SQLFreeConnect(myHdbc);
return (-1);
}
icss->connectPtr=myHdbc;
if (icss->databaseType == DB_TYPE_MYSQL) {
/*
MySQL must be running in ANSI mode (or at least in PIPES_AS_CONCAT
mode) to be able to understand Postgres SQL. STRICT_TRANS_TABLES
must be st too, otherwise inserting NULL into NOT NULL column does
not produce error.
*/
cllExecSqlNoResult ( icss, "SET SESSION autocommit=0" ) ;
cllExecSqlNoResult ( icss,
"SET SESSION sql_mode='ANSI,STRICT_TRANS_TABLES'" );
}
return(0);
}
/*
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;
}
}