Integration

Hi All,

How do I go about capturing and handling a database adapter Insert error that faults with the following type of error:

file:/opt/oracle/product/10.1.3.1/OracleAS_1/bpel/domains/default/tmp/.bpel_ServiciosRed_1.10_8d669f416ffeca90179946ca781adac8.tmp/DB_INSERCION_CLIENTE.wsdl [ DB_INSERCION_CLIENTE_ptt::DB_INSERCION_CLIENTE(DB_INSERCION_CLIENTEInput_msg) ] - WSIF JCA Execute of operation 'DB_INSERCION_CLIENTE' failed due to: Pure SQL Exception.
Pure SQL Execute of insert into clientescrm(id_cliente_crm, estado) values(?, (select id_estado from estados_componentes where TEXTO_ESTADO = 'INACTIVO' AND COMPONENTE = 'CLIENTE')) failed. Caused by java.sql.SQLException: ORA-00001: unique constraint (BPEL.SYS_C009595) violated.

Basically, I want to continue the flow to continue - currently the flow aborts.

Any advise greatly appreciated.

Background info 1):

We have a flow that very infrequently receives two orders for the same clientID within the same second. The flow contains logic to first check if the ClientID already exists in a table, and to insert it (using the above) if not. When we receive two orders at the same time, both flows perform the check at the same time, and both decide that they need to insert into the database, resulting in one flow faulting with the error above.

Background info 2):

I inherited this flow from an SI firm that my company nolonger uses, and in the flow I can see BEFORE the database read, and AFTER the insert the following java embedded, but the blocking is not working, and I don't know enough about this area to figure out why:

Identificador -- this is the ClientID received by the flow
Bpelprocess -- this is the cikey/instance id for the flow

<bpelx:exec import="com.hp.spain.jazztel.pomv.modulobloqueos.*"/>
<bpelx:exec import="java.rmi.Naming"/>
<bpelx:exec name="BloqueoCliente"
language="java" version="1.5">
<![CDATA[try
{
String identificador = getVariableData("Identificador").toString();
long bpelprocess = new Long(getVariableData("Bpelprocess").toString()).longValue();

BloqueoInterfazRemota mb = (BloqueoInterfazRemota)Naming.lookup("//localhost/ModuloBloqueos");
mb.lock(identificador, bpelprocess);
}
catch(Exception e){e.printStackTrace();}]]>
</bpelx:exec>

INVOKE DB ADAPTER TO READ record
If record does not exist then
INVOKE INSERT
EndIf

<bpelx:exec name="DesbloqueoCliente"
language="java" version="1.5">
<![CDATA[try
{
String identificador = getVariableData("Identificador").toString();

BloqueoInterfazRemota mb = (BloqueoInterfazRemota)Naming.lookup("//localhost/ModuloBloqueos");
mb.unlock(identificador);
}
catch(Exception e){e.printStackTrace();}]]>
</bpelx:exec>