problem with updating through OCI_Execute fun

[GIlles Maria-Sube]

Hello,

I'm writting an application do do r/w access in tables, using Oracle10.2
server installed on a Linux (Suse10.1 version for 86x64) platform and I
encounter problem to perform updating in table with OCI functions; for exemple
I want to execute the query:

update fpi set orginator='orig2' where tpcode='tp1'

This query runs perfectly when it is called from sqlplus.

The application begin to call sql_connect() as follows:

->

include <ocilib.h></ocilib.h>

ifdef DEBUG

void logg( char *mess )
{
printf( "%s", mess );
}

else

void logg( char *mess )
{
}

endif

void err_handler( OCI_Error *err ) {
char msg;

sprintf( msg, "code: ORA-%05i\nmsg: %s\nsql: %s\n",
OCI_ErrorGetOCICode(err),
OCI_ErrorGetString(err),
OCI_GetSql(OCI_ErrorGetStatement(err)) );
logg(msg);
}

int sql_connect ( OCI_Connection c, char service, char user, char passwd
)
{
/ connect to db /
OCI_Connection cn;
char msg;

if ( OCI_Initialize( err_handler, NULL, OCI_ENV_DEFAULT ) != TRUE ) {
sprintf (msg,"Error initializing the default environment for OCI\n");
logg (msg);
return ( KO );
}

cn = OCI_ConnectionCreate( service, user, passwd, OCI_SESSION_DEFAULT );
if ( cn == NULL ) {
sprintf (msg,"Error connecting to service=%s , user=%s,
passwd=%s\n\n",service, user, passwd);
logg (msg);
//logg_sql_error (msg);
return ( KO );
} else {
sprintf (msg,"connected to service \n",service);
logg (msg);
}
*c = cn;

return ( OK );
}
<-

Then it calls update_db as follows:

->
int update_db( OCI_Connection cn, char query_for_update )
{
char msg;
OCI_Statement st;
OCI_Resultset rs;
unsigned int nrow;

st = OCI_StatementCreate( cn );
if ( st == NULL ) {
sprintf (msg,"Creation statement fails: st is NULL\n");
logg( msg );
return( KO );
}

/OCI_Prepare( cn, st );/
printf("pass1, query_for_update = %s\n", query_for_update);
if ( OCI_Prepare( st, query_for_update ) != TRUE ) return( KO );
if ( OCI_Execute( st ) != TRUE ) return( KO );
rs = OCI_GetResultset( st );
if ( rs == NULL ) return( KO );
if ( OCI_FetchLast( rs ) != TRUE ) return( KO );
nrow = OCI_GetCurrentRow( rs );
sprintf (msg,"Number of fetched rows: %u\n",nrow);
logg( msg );
if ( OCI_ReleaseResultsets( st ) != TRUE ) return( KO );

return( OK );
}
<-

Execution of th application runs until OCI_Execute calling, then program stay
'bloked' in the function OCIStmtExecute(..) called
by OCI_Execute, and does no return hand...

I precise that the same problem occur in threaded mode (using connection got
from a pool); but, request to read records from table (with a select) works
perfectly.

Any suggestion?

Thank you in advance

[Vincent Rogier]

HI,

If internal OCI function OCIStmtExecute() blocks on an 'update', it means that
you must have a lock on the row you want to update.
Check that there is no other session (sql*plus, etc..) that has uncommited
work.

Vincent

[GIlles Maria-Sube]

Thanks
Works fine now!