Error while enabling JDBC Polling Notification

Prasad6 · October 1, 2010, 12:40pm

I have created a JDBC Insert Polling Notification on a table.
When I am trying to enabling I am getting the below error:
[ART.116.3038] Adapter Runtime (Notification): Error in Notification Callback:enableCallback notification jdeBbweEnterpriseOne.ManugisticsServices:JdeBbJDBCExtractNotification.
[ADA.1.316] Cannot execute the SQL statement “SELECT t1.A2Y55SN,t1.A2EDTN,t1.A2DSC1,t1.A2DSC2,t1.wm_rowid,t1.wm_sendTime FROM WMBJdeBbExtractNotifybgkkmqa t1 WHERE (wm_rowid> ? ) ORDER BY wm_rowid”. "
(S0002/208) Invalid object name ‘WMBJdeBbExtractNotifybgkkmqa’."
Invalid object name ‘WMBJdeBbExtractNotifybgkkmqa’.

I tried all the options like dropDatabaseObjects and again enabling the notification. But still I am getting the same error. I have rechecked the db username previlages, but dont see any issues. I was able to manually create the buffer table and trigger using the same db credentials.
But unable to when I try to do it by enabling the polling notification.

Please find the below server logs:
[15898]Invalid object name ‘WMBJdeBbExtractNotifybgkkmqa’.
15897 Invalid object name ‘WMBJdeBbExtractNotifybgkkmqa’."
[15896][ADA.1.316] Cannot execute the SQL statement “SELECT t1.A2Y55SN,t1.A2EDTN,t1.A2DSC1,t1.A2DSC2,t1.wm_rowid,t1.wm_sendTime FROM WMBJdeBbExtractNotifybgkkmqa t1 WHERE (wm_rowid> ? ) ORDER BY wm_rowid”. "
[15895]2010-10-01 16:07:23 EST [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ART.116.3038] Adapter Runtime (Notification): Error in Notification Callback:enableCallback notification jdeBbweEnterpriseOne.ManugisticsServices:JdeBbJDBCExtractNotification.
[15894]2010-10-01 16:07:23 EST [SCC.0126.0205T] Connection Jde_JDBC_Connection:Notification(87) of Jde_JDBC_Connection:Notification is now available
[15893]2010-10-01 16:07:23 EST [ADA.0001.0105T] Rollback local transaction.
[15892]2010-10-01 16:07:23 EST [SCC.0121.0015T] rolling back resource. xid = melsys89.cub.com.au/1284382502509 resource = Jde_JDBC_Connection:Notification
[15891]2010-10-01 16:07:23 EST [SCC.0121.0053D] Starting rollback for xid = melsys89.cub.com.au/1284382502509
[15890]2010-10-01 16:07:23 EST [ART.0116.3507D] Adapter Runtime (Notification): useConnection Connection:null for Notification:jdeBbweEnterpriseOne.ManugisticsServices:JdeBbJDBCExtractNotification UUID:ad71fb80695111df8e6ecd1f1fb42d0c.
[15889]2010-10-01 16:07:23 EST [ART.0116.3038E] Adapter Runtime (Notification): Error in Notification Callback:enableCallback notification jdeBbweEnterpriseOne.ManugisticsServices:JdeBbJDBCExtractNotification.
[15888]Invalid object name ‘WMBJdeBbExtractNotifybgkkmqa’.
15887 Invalid object name ‘WMBJdeBbExtractNotifybgkkmqa’."
[15886]2010-10-01 16:07:23 EST [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.316] Cannot execute the SQL statement “SELECT t1.A2Y55SN,t1.A2EDTN,t1.A2DSC1,t1.A2DSC2,t1.wm_rowid,t1.wm_sendTime FROM WMBJdeBbExtractNotifybgkkmqa t1 WHERE (wm_rowid> ? ) ORDER BY wm_rowid”. "
15885 Invalid object name ‘WMBJdeBbExtractNotifybgkkmqa’."
[15884]2010-10-01 16:07:23 EST [ADA.0001.0113T] Received SQLException: "
[15883]Cannot drop the table ‘WMBJdeBbExtractNotifybgkkmqa’, because it does not exist or you do not have permission.
15882 Cannot drop the table ‘WMBJdeBbExtractNotifybgkkmqa’, because it does not exist or you do not have permission."
[15881]2010-10-01 16:07:23 EST [ART.0114.1007E] Adapter Runtime: Error Logged. See Error log for details. Error: [ADA.1.316] Cannot execute the SQL statement “DROP TABLE WMBJdeBbExtractNotifybgkkmqa”. "
15880 Cannot drop the table ‘WMBJdeBbExtractNotifybgkkmqa’, because it does not exist or you do not have permission."
[15879]2010-10-01 16:07:23 EST [ADA.0001.0113T] Received SQLException: "
[15878]2010-10-01 16:07:23 EST [ADA.0001.0110T] Execute the SQL statement: “DROP TRIGGER PRODDTA.WMTJdeBbExtractNotifybgkkmqa”
[15877]2010-10-01 16:07:23 EST [ADA.0001.0110T] Execute the SQL statement: “CREATE TRIGGER WMTJdeBbExtractNotifybgkkmqa ON PRODDTA.F55MAN31 for insert as begin INSERT INTO PRODDTA.WMBJdeBbExtractNotifybgkkmqa (A2Y55SN, A2EDTN, A2DSC1, A2DSC2) SELECT inserted.A2Y55SN, inserted.A2EDTN, inserted.A2DSC1, inserted.A2DSC2 FROM inserted end”
[15876]2010-10-01 16:07:23 EST [ADA.0001.0110T] Execute the SQL statement: "CREATE TABLE PRODDTA.WMBJdeBbExtractNotifybgkkmqa (A2Y55SN char(10), A2EDTN char(22), A2DSC1 char(30) NULL, A2DSC2 char(30) NULL, wm_rowid numeric(9) identity, wm_sendTime numeric(14))"
[15875]2010-10-01 16:07:23 EST [ART.0116.3508D] Adapter Runtime (Notification): getConnection Connection:com.wm.adapter.wmjdbc.connection.JDBCConnection@463e46ab for Notification:jdeBbweEnterpriseOne.ManugisticsServices:JdeBbJDBCExtractNotification UUID:ad71fb80695111df8e6ecd1f1fb42d0c.
[15874]2010-10-01 16:07:23 EST [ADA.0001.0109T] In notification callback “enableCallBack”().
[15873]2010-10-01 16:07:23 EST [ART.0116.3500D] Adapter Runtime (Notification): enableCallback Invoked for Notification:jdeBbweEnterpriseOne.ManugisticsServices:JdeBbJDBCExtractNotification UUID:ad71fb80695111df8e6ecd1f1fb42d0c.
[15872]2010-10-01 16:07:23 EST [ART.0116.3507D] Adapter Runtime (Notification): useConnection Connection:com.wm.adapter.wmjdbc.connection.JDBCConnection@463e46ab for Notification:jdeBbweEnterpriseOne.ManugisticsServices:JdeBbJDBCExtractNotification UUID:ad71fb80695111df8e6ecd1f1fb42d0c.
[15871]2010-10-01 16:07:23 EST [ADA.0001.0103T] Begin local transaction.

I have increased the log levels but couldnt find the exact issue. As per the logs when I enable the polling notification, a buffer table and a trigger is created and after that some SQL Exception occurred.

Let me know if there is any workaround for this.

Thanks in advance.
Prasad

Craig_Gorman · August 18, 2011, 5:51am

I have the same issue. Did you solve this? We have debuged it to the fact that the create statment has the owwner path, e.g. owner.WMBJdeBbExtractNotifybgkkmqa but the select statment does not. eg. SELECT … FROM WMBJdeBbExtractNotifybgkkmqa

No Idea if this is the issue. But would apperciate your help if you have solved this.

Cheers

Lionel_Lee · September 23, 2011, 8:04am

Would you mind to provide information regarding version number of JDBC Adapter. You can get the version numeber following below step:

Login to Integration Administration page
Click on Adapters > JDBC Adapter > About

If it shows that you are using JDBC Adapter 6.5, please check if latest Fix (JDBC_6.5_Fix30) has been installed. You can get latest Fix at:
https://empower.softwareag.com/KnowledgeCenter/Product_Fixes/FixExplorer/default.asp

It is suspected relate to a fix stated in JDBC_6.5_Fix30 Readme file :

Section JDB-780 (JDBC_6.5_Fix29)
[i]Creating an insert notification with webMethods JDBC Adapter 6.5 results in truncating the data inserted into the buffer table columns associated with the JDBC Adapter insert notification.

When using webMethods JDBC Adapter 6.5 to create a JDBC Adapter insert notification, the data inserted into the buffer table columns associated with the insert notification is truncated. The issue occurs because when the JDBC Adapter insert notification points to a table in the database that has columns of type nchar, nvarchar, or longnvarchar, the adapter creates a buffer table with corresponding columns that have incorrect length.

This issue is resolved. Now the buffer table is created with the required length for all columns. The length of the buffer table columns is equal to the length of the columns in the original table associated with the created JDBC Adapter insert notification.

Note: To ensure that all existing notifications function properly after installing the fix, do the following steps for each notification that selects nchar, nvarchar, or longvarchar from the table:

In Integration Server Administrator, disable the adapter notification.
In Designer, open the adapter notification and click “reload values from the adapter” icon.
In Integration Server Administrator, enable the adapter notification.

For more information about enabling adapter notifications and reloading adapter values, see “webMethods JDBC Adapter User’s Guide”. [/i]

Apply latest fixes would help minimize scope of issues to be investigated.

Please do let me know if this Fix resolve your problem.

Dileep_Kumar · May 7, 2017, 8:45pm

Hi Team,

Am creating one adapter service by using Custom SQL Template.

But am facing below error:
Could not run ‘BufferTable’
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service defaul.adapter:BufferTable.
[ADA.1.316] Cannot execute the SQL statement “select count(*) as manifestBufCount,
(select count(1) FROM “ESBTRIG”/“PKP10P100_TRGBUF”) as pkpStopBufCount,
(select count(1) FROM “ESBTRIG”/“PKP10P101_TRGBUF”) as pkpRqstBufCount,
(select count(1) FROM “ESBTRIG”/“PKP10P_CHILD_TRGBUF”) as pkpChildBufCount
(select count(1) FROM “ESBTRIG”/“RAP010_TRGBUF”) as pkpChildBufCount
FROM “ESBTRIG”/“FRP006_TRGBUF” as manifest”. "
(42601/-104) [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO."
[SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.

Server error occurred
Launch completed: 2017-05-07 14:20:38.172

Dileep_Kumar · May 7, 2017, 8:46pm

Can anyone please help me ?

Dileep_Kumar · May 7, 2017, 8:48pm

Hi Team,

Am creating one adapter service by using Custom SQL Template.

But am facing below error:
Could not run ‘BufferTable’
com.wm.pkg.art.error.DetailedServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service defaul.adapter:BufferTable.
[ADA.1.316] Cannot execute the SQL statement “select count(*) as manifestBufCount,
(select count(1) FROM “ESBTRIG”/“PKP10P100_TRGBUF”) as pkpStopBufCount,
(select count(1) FROM “ESBTRIG”/“PKP10P101_TRGBUF”) as pkpRqstBufCount,
(select count(1) FROM “ESBTRIG”/“PKP10P_CHILD_TRGBUF”) as pkpChildBufCount
(select count(1) FROM “ESBTRIG”/“RAP010_TRGBUF”) as pkpChildBufCount
FROM “ESBTRIG”/“FRP006_TRGBUF” as manifest”. "
(42601/-104) [SQL0104] Token ( was not valid. Valid tokens: , FROM INTO."
[SQL0104] Token ( was not valid. Valid tokens: , FROM INTO.

Server error occurred
Launch completed: 2017-05-07 14:20:38.172

Gerardo_Lisboa · May 8, 2017, 10:18am

Hi,

Please check you JDBC adapter fix level and your permissions on the database (is your user able to create and delete objects?).

Best Regardss,