Forum Discussion

Prabaharan's avatar
Prabaharan
Contributor
2 years ago

SOAPUI JDBC error while creating a table view

Hi,

I'm doing a database automation using JDBC request in soapui open source. I'm able to connect to DB2, but getting error while running the below query.

WITH AggregatedData AS (
SELECT
cont_id AS partyID, COUNT(DISTINCT admin_client_id) AS count_admin_client_id
FROM
contequiv WHERE cont_id IN (
SELECT
cont_id
FROM
contequiv GROUP BY cont_id HAVING COUNT(cont_id) > 1

MINUS

SELECT target_cont_id
FROM
inactivecontlink WHERE link_reason_tp_cd = 1 )  GROUP BY  cont_id
),
IndividualData AS (
SELECT cont_id AS partyID, admin_client_id AS sourceID
FROM contequiv WHERE
cont_id IN (
SELECT
cont_id FROM contequiv
GROUP BY cont_id
HAVING
COUNT(cont_id) > 1

 

MINUS

 

SELECT
target_cont_id
FROM
inactivecontlink
WHERE
link_reason_tp_cd = 1
)
)
SELECT
AggregatedData.partyID,
AggregatedData.count_admin_client_id,
LISTAGG(IndividualData.sourceID, ', ') WITHIN GROUP (ORDER BY IndividualData.sourceID) AS sourceIDs
FROM
AggregatedData
JOIN
IndividualData
ON
AggregatedData.partyID = IndividualData.partyID
GROUP BY
AggregatedData.partyID, AggregatedData.count_admin_client_id;

 

 

The above query is working when tried from different DB client but not in soapui JDBC request.

 

Getting below error 

"Error getting response; com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;ount_admin_client_id;END-OF-STATEMENT, DRIVER=4.25.13"

 

Appreciate if anyone can help me in resolving the issue.

 

Thanks in advance.

 

  • Hello Prabaharan

     

    Since SoapUI and DBVisualizer are probably using different jdbc drivers, you could try to download a recent DB2 jdbc driver and put it in the SoapUI 'lib' folder (readme.txt in 'bin\ext' says otherwise that i don't agree with however).  It might just be some simple syntax or character code that is causing the issue...  maybe remove the last ';' (semi-colon) from the sql and see what happens.  If you copied/pasted the query from someplace that adjusted the character codes that could be an issue (copy and paste to Notepad then copy that and paste into your SoapUI query...  Notepad usually does good job at normalizing character set)...  in otherwords, there might be any number of things that could go wrong that will only be corrected with a little googleing and trial and error.  Maybe identifying the jdbc driver that DBVisualizer uses and replace it in SoapUI.  just my 2 cents...  🙂

     

    Regards,

    Todd

  • Hi nmrao ,

    I tried the same query in DBVisualizer and able to see the result. Below snippet for your reference.

    Same when tried from SOAP UI getting below error

    Can you help me on this?

     

    Thanks

     

     

    • TNeuschwanger's avatar
      TNeuschwanger
      Champion Level 1

      Hello Prabaharan

       

      Since SoapUI and DBVisualizer are probably using different jdbc drivers, you could try to download a recent DB2 jdbc driver and put it in the SoapUI 'lib' folder (readme.txt in 'bin\ext' says otherwise that i don't agree with however).  It might just be some simple syntax or character code that is causing the issue...  maybe remove the last ';' (semi-colon) from the sql and see what happens.  If you copied/pasted the query from someplace that adjusted the character codes that could be an issue (copy and paste to Notepad then copy that and paste into your SoapUI query...  Notepad usually does good job at normalizing character set)...  in otherwords, there might be any number of things that could go wrong that will only be corrected with a little googleing and trial and error.  Maybe identifying the jdbc driver that DBVisualizer uses and replace it in SoapUI.  just my 2 cents...  🙂

       

      Regards,

      Todd

  • Hi Todd,

     

    Tried removing ';' and it worked in Soapui.

     

    Regards,

    Prabaharan