Saturday, August 18, 2012

"Pure SQL" problem with JCA Adapter


I have wasted nearly a day chasing down a ghost problem with SOA Jca DB adapter. I thought I'll make a note of it here.

The database is SQLServer,we use SQLservre JDBC4.0 driver. I use "pure SQL" query for the adapter as following:

SELECT top 1 A.TNDR_CTL_ID, B.ACCOUNTING_DT
FROM PS_CI_TNDR_CTL A
,PS_CI_DEP_CTL B
WHERE A.TNDR_SOURCE_CD = #source
AND A.DEP_CTL_ID = B.DEP_CTL_ID
AND B.ACCOUNTING_DT <= convert(datetime,#date)
and A.TNDR_CTL_ST_FLG = '10' order by B.ACCOUNTING_DT desc


However, when I ran the BPEL test, it generated a nonsense error: 

oracle.sysman.emSDK.webservices.wsdlapi.SoapTestException: Client received SOAP Fault from server : Exception occured when binding was invoked. Exception occured during invocation of JCA binding: "JCA Binding execute of Reference operation 'getTenderCtrlId' failed due to: Pure SQL Exception. Pure SQL Execute of SELECT top 1 A.TNDR_CTL_ID, B.ACCOUNTING_DT FROM PS_CI_TNDR_CTL A ,PS_CI_DEP_CTL B WHERE A.TNDR_SOURCE_CD = ? AND A.DEP_CTL_ID = B.DEP_CTL_ID AND B.ACCOUNTING_DT <= convert(datetime,?) and A.TNDR_CTL_ST_FLG = '10' order by B.ACCOUNTING_DT desc failed. Caused by java.sql.SQLException: [FMWGEN][SQLServer JDBC Driver][SQLServer]Warning: Fatal error 823 occurred at Aug 17 2012 4:29PM

I initially thought this was an issue of multiple input parameters. So I stripped my query to the minimum as:

SELECT 
A.TNDR_CTL_ID, B.ACCOUNTING_DT 
FROM 
PS_CI_TNDR_CTL A , PS_CI_DEP_CTL B


and I still get that out of range error. That's when I tested the inner join and found a working query.


The query below is logically equivalent to the original version, except this one uses "inner join". And this one works. 

SELECT top 1 PS_CI_TNDR_CTL.TNDR_CTL_ID,
  PS_CI_DEP_CTL.ACCOUNTING_DT
FROM PS_CI_TNDR_CTL
INNER JOIN PS_CI_DEP_CTL
ON PS_CI_TNDR_CTL.DEP_CTL_ID        = PS_CI_DEP_CTL.DEP_CTL_ID
AND PS_CI_DEP_CTL.ACCOUNTING_DT    <= CONVERT(DATETIME,#dt)
WHERE PS_CI_TNDR_CTL.TNDR_SOURCE_CD = #source
AND PS_CI_TNDR_CTL.TNDR_CTL_ST_FLG  = '10'
ORDER BY PS_CI_DEP_CTL.ACCOUNTING_DT DESC

Beats me!


1 comment: