Hi,
I am working on a project of DB migration from Oracle to Postgres. The application is working well with Oracle. But when switch to Postgres, some SQL executionss couldn't finish and got an error like:
FATAL: DefineSavepoint: unexpected state STARTED
STATEMENT: SAVEPOINT_per_query_svp;DEALLOCATE "_PLAN04824560";RELEASE_per_query_SVP_
The below is the c++ code to execute SQL to complete the operation. From UI, it tries to add/remove some values (remove or insert some records into table). When running application, nothing happens but with the error above on DB server side. I did some search for this error, looks not too much. Any suggestions to fix this issue?
Thanks..
...................................
try {
_statement->prepareStatement( SQL_DELETE_SACTIONS_BY_SID_AND_CID );
_statement->setUnsignedInteger( 1, criteria->cid );
_statement->setUnsignedInteger( 2, criteria->sid );
_statement->execute();
for( size_t i = 0; i < ( criteria->sActions->size() ); i++ ) {
_statement->prepareStatement( SQL_INSERT_SACTION_BY_SID_AND_CID );
_statement->setUnsignedInteger( 1, criteria->cid );
_statement->setUnsignedInteger( 2, criteria->sid );
_statement->setString( 3, ( unsigned int )( ( *( criteria->sActions ) )[i].length() ), ( *( criteria->sActions ) )[i].c_str() );
if( _statement->execute() != ServiceConstants::SUCCESS ) {
return result;
}
}
result = true;
_statement->prepareStatement( "COMMIT" );
_statement->execute();
} catch( ServiceException ex ) {
DatabaseUtilities::logServiceException(
ex, "UpdateBySidAndCid" );
}
return result;
}
const char * const UpdateBySidAndCid::SQL_DELETE_SACTIONS_BY_SID_AND_CID
= "DELETE FROM tableA WHERE Cid = ? AND Sid = ?";
const char * const UpdateBySidAndCid::SQL_INSERT_SACTION_BY_SID_AND_CID
= "INSERT INTO tableA (Cid, Siid, SActionUid) SELECT ?, ?, SActionUid FROM tableB WHERE SActionName = ?";
