Hi all,
I am trying to run multiple SQL Statements in a single esbdb file and some of the multiple statements are coded to take input parameters from the input xml file.
When I run, I always face this error.
********************
Caused by: com.sonicsw.esb.service.rdbms.script.DatabaseScriptEngineException: Failed to execute prepared statement: 'insert into `transaction` values(0,?,'CREDIT',?,'2008-10-13');insert into `transaction` values(0,?,'DEBIT',?,'2008-10-13');update account set acc_debit=acc_debit? where acc_id = ?;update account set acc_credit=acc_credit? where acc_id = ?;
'.
********************
I am certain that the XPath mapping I am using is correct as the same statements run perfectly fine when I run them individually; it's only when I try to run the statements in bunch that such error appears.
The ? seems to appear in place of the mapped parameter.
I am using Sonic V7.6 and MySQL database.
Thanks.
My question seems too vague,
Let me make it more clear,
I am dealing with MS SQL Server 2005 database,
When I try to run commit or rollback statement alone using an esbdb file, I run into the error mentioned above
(again)
*************
Caused by: com.sonicsw.esb.service.rdbms.script.DatabaseScriptEngineException: Failed to execute prepared statement: 'rollback;
'.
*************
I guess this is because SQL Server requires begin transaction and rollback with the same session connection, When I run rollback alone the earlier begin transaction statements have nothing to do with this rollback statement.
But when I try to have more than one statement within the same esbdb file, I run into the error I mentioned above,
Any help in the matter would be appreciable,
Thank you
Is it possible to see the entire esbdb file you are using?
Hi Mark,
Thanks for your interest,
I have attached the images and corresponding to the esbdb process file herewith,
The problem is that the process is unable to map the input parameters (though while debugging the esb variables seem to contain the values). The question marks appear in place of the actual values.
The extended error message is ::
com.sonicsw.esb.service.rdbms.DatabaseServiceException: Service call failed.
at com.sonicsw.esb.service.rdbms.DatabaseService.serviceConnection(DatabaseService.java:816)
at com.sonicsw.esb.service.rdbms.DatabaseService.service(DatabaseService.java:707)
at com.sonicsw.xqimpl.service.debug.DebugServiceInterceptor.intercept(DebugServiceInterceptor.java:118)
at com.sonicsw.xqimpl.service.XQServiceChain$XQInterceptorServiceWrapper.intercept(XQServiceChain.java:481)
at com.sonicsw.xqimpl.service.XQServiceChain$XQInterceptorServiceWrapper.service(XQServiceChain.java:470)
at com.sonicsw.xqimpl.service.XQServiceChain.service(XQServiceChain.java:151)
at com.sonicsw.xqimpl.service.ServiceMessageHandler.callService(ServiceMessageHandler.java:413)
at com.sonicsw.xqimpl.service.ServiceMessageHandler.handleMessage(ServiceMessageHandler.java:182)
at com.sonicsw.xqimpl.service.ProcessMessageHandler.doHandleMessage(ProcessMessageHandler.java:308)
at com.sonicsw.xqimpl.service.ProcessMessageHandler.handleMessage(ProcessMessageHandler.java:90)
at com.sonicsw.xqimpl.service.XQDispatcher.onMessage(XQDispatcher.java:422)
at com.sonicsw.xqimpl.endpoint.container.EndpointContextContainer.onMessage(EndpointContextContainer.java:84)
at com.sonicsw.xq.connector.jms.JMSEndpoint$JMSEndpointListener.onMessage(JMSEndpoint.java:570)
at progress.message.jimpl.Session.deliver(Session.java:2998)
at progress.message.jimpl.Session.run(Session.java:2390)
at progress.message.jimpl.Session$SessionThread.run(Session.java:2775)
Caused by: com.sonicsw.esb.service.rdbms.script.DatabaseScriptEngineException: Failed to execute prepared statement: 'insert into `transaction` values(0,?,'DEBIT',?,concat(concat(curdate(), ' '),curtime()));
'.
at com.sonicsw.esb.service.rdbms.script.DatabaseScriptCommand.executePreparedStatement(DatabaseScriptCommand.java:326)
at com.sonicsw.esb.service.rdbms.script.DatabaseScriptCommand.execute(DatabaseScriptCommand.java:271)
at com.sonicsw.xqimpl.script.Command.executeScriptCommand(Command.java:319)
at com.sonicsw.xqimpl.script.Command.execute(Command.java:275)
at com.sonicsw.xqimpl.script.ScriptEngine.executeCommand(ScriptEngine.java:66)
at com.sonicsw.esb.service.rdbms.DatabaseService.serviceConnection(DatabaseService.java:771)
... 15 more
Thank you.
I have trouble processing the esbdb, here are the details.
I use MS SQL 2008
My SQL is as follows.
select * from tableName
where FieldName = SUBSTRING(CONVERT(VARCHAR(10), @VariableName, 120), 1, 10);
The value I supplied is.
VariableName = '2010-05-17 14:21:42.000-05:00'
Regards,
Swaroop Kunduru.
hello all!
Try to remove ";" at the end of your statements.
Regards
Chan Jeremy