cancel
Showing results for 
Search instead for 
Did you mean: 

Error in Sender JDBC Communication Channel _SQL database

Former Member
0 Kudos

Hi Experts ,

Scenario : JDBC -> PI-> ABAP Proxy Asynch scenario

XI/PI version:

I am facing problems with the JDBC communication channel in PI. I have a sender JDBC communication channel which is trying to execute  a distributed query in a SQL Server 2008 database. My query looks like:

SELECT

* from openquery (PIUS,



'SELECT b.batchid, b.starttime, b.endtime, b.product, b.procedure,



(SELECT m.name FROM pimodule..pimodule m WHERE uid = b.moduleuid) AS "unit" FROM piunitbatch b



WHERE b.moduleuid IN (SELECT uid FROM pimoduleh WHERE path = ''\FN\Rec-FHG\Tank\'') AND b.starttime > ''*-30d'' AND b.endtime IS NULL'

)

Query works fine in SQL 2005 or 2008 server studio , but executed the same in JDBC channel throws error in Chanel Monitoring:

Database-level error reported by JDBC driver while
executing statement ''SELECT b.batchid, b.starttime, b.endtime, b.product,
b.procedure, (SELECT m.name FROM pimodule..pimodule m WHERE uid = b.moduleuid)
AS "unit" FROM piunitbatch b  WHERE b.moduleuid IN (SELECT uid FROM pimoduleh
WHERE path = ''\FN\Rec-FHG\Tank\'') AND b.starttime > ''*-30d'' AND b.endtime
IS NULL')'. The JDBC driver returned the following error message:
'com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near 'SELECT
b.batchid, b.starttime, b.endtime, b.product, b.procedure, (SELECT m.name FROM
pimodule..pimodule m WHERE uid = b.moduleui'.'. For details, contact your
database server vendor.

I have enabled the option: Database Auto Commit-Enabled

JDBC Driver : com.microsoft.sqlserver.jdbc.SQLServerDriver

jdbc:sqlserver://PIDKAF.dknz.nzcorp.net;databasename=AutoStand;selectMethod =cursor

tried changing the query and also contacted DB colleague .This query is executed successfully in SQL SErver .

Appreciate your input to sort this issue,

Best regards,

Varalakshmi SB

Accepted Solutions (0)

Answers (1)

Answers (1)

stefan_grube
Active Contributor
0 Kudos

Try to put the whole SQL statement in SQL studio in one line, then copy and paste it to JDBC sender adapter.

Be aware that the update statement needs to have exactly the same where clause. Maybe you use a stored procedure for the select.

Former Member
0 Kudos

HI  Stefan ,

Thank you for your information.

Apparently checked replacing the data credteria from :''*-30d''  to ''GETDATE()-30'' .

The new query worked in SAP PI replacing after data  criteria s replaced  from ''*-30d''  to ''GETDATE()-30''


New sql query:

SELECT * from openquery (PIUS,'SELECT b.batchid, b.starttime, b.endtime, b.product, b.procedure,(SELECT m.name FROM pimodule..pimodule m WHERE uid = b.moduleuid) AS "unit" FROM piunitbatch b WHERE b.moduleuid IN (SELECT uid FROM pimoduleh WHERE path = ''\FN\Rec-FHG\Tank\'') AND b.starttime > ''GETDATE()-30'' AND b.endtime IS NULL')

From business, the SAP PI should poll for every one hour and ideally should delete existing entries in SAP and then add the result of this query so that new entries are updated in SAP.

So, every hour: delete data in SAP, insert new data with this query

delete syntax of sql  :DELETE FROM table_name but my query is open query.

could you provide me input on this delet query and to insert the same query to pass new entries to SAP.

Thanks,

Varal.

Former Member
0 Kudos

Hi Experts ,

Can someone help me with  in executing the deleting existing query and reinserting the new entries to SAP in JBDC  sender channel .(I have expalined business details in above reply)

I checkde few blogs for delete query but the SQL query which i have is a open query

This is my SQL query :

SELECT * from openquery (PIUS,'SELECT b.batchid, b.starttime, b.endtime, b.product, b.procedure,(SELECT m.name FROM pimodule..pimodule m WHERE uid = b.moduleuid) AS "unit" FROM piunitbatch b WHERE b.moduleuid IN (SELECT uid FROM pimoduleh WHERE path = ''\FN\Rec-FHG\Tank\'') AND b.starttime > ''GETDATE()-30'' AND b.endtime IS NULL')