I am in need of using the JDBC BasicNotification component and want to poll only a subset of the data on a particular table. Can you use a where statement to poll only the rows you are interested in?
As far i know it is not possible to set where condition clause in the Adapter Notificiation service.It does polls on whole table if any new rows inserts/updates/deleted.
Let see if anyone have done tricky.
HTH,
RMG
This limitation is only on the BasicNotification. The other notification types do support the ‘where’ clause.
I did see the where clause supported in the other notification types but it is also my understanding this other types of notifications all create DB tables. In our production environment our DBA’s would rather wm not create it’s own tables, thus the need to use the basic noticiation.
Hi,
Basic Notification has support for where clause in DB Adapter 4.1.1, It has been removed from JDBC 4.6 onwards.
If you are on to JDBC 4.6/6.1, following way may work for for you
- use a “Stored Procedure Notification”, and code the where clause in the Stored Procedure…
Other Option is to use
A. A scheduler which publishes a notification Event, and you can pick that event, run a select query on the table you want to poll…
B. In case of IS, schedule a Service, in the service, use a Select with the where
I did see the where clause supported in the other notification types but it is also my understanding this other types of notifications all create DB tables. In our production environment our DBA’s would rather wm not create it’s own tables, thus the need to use the basic noticiation.
Actually Few question for you:
Do you want to use Exactly Once Notification? If so, are you allowed to delete records from the table?
I am sure DBA may not want random tables created by webMethods, but they can always create one, just for webMethods, which you can use for basic notification.
Thanks for the suggestion. We have an existing legacy system that has built into it a shared notification table across many subsystems. My hope was to use this rather than have to create a separate notification table just for webMethods. I will need to delete the rows from the table once I have processed them so it sounds like I should write a separate scheduled service to read and delete rows from this table.
You could use a view on the table, which implements the where clause. The you configure the basic notification to poll on the view instead of the real table. You just need to make sure that you update the records in the real table when you process the notification document so that they do not satisfy the where condition anymore and do not show up in the view or you will handle the same record multiple times. At least this is what I guess the basic notification will do.
What does the basic notification do when you do not remove the entries from the table (or view) that is monitored? Will the same record be published again in the notification document as soon as the next scheduled poll is executed? Is it practical to work with the “Delete selected records” option unchecked?
Micheal,
Alternatively you can put a filter in the webMethods Trigger. The only problem i notice in this case is unneccesary load to Intergation Server caused by the notification which doesnt match your condition.
You can also try with Stored Procedure Notification. In this case you will write your query with where condition in the procedure itself. The output parameters returned by the Stored Procedure will be available to you through the publishable document. In this option you cant have input parameters to the procedure.
Hope this helps,
Shahul.
Stored Procedure Notification is definitely an option, but you will be forced to use cursors, if you plan to return more then one row… or you will be restricted to returning only set at a time. Another issue with stored procedure notification, is that it will generate an empty notification, if no values are being returned…
Michael,
The resistance from DBAs of Legacy Servers is something not new.
An Integration can be transparent to the users but not to an IT Application. The access or the objects that need to be created are cost of creating an integration.
There are more bleeding age products, that do not even require such notifications to be created, as they plug themselves into TLOG of db to identify changes, these products may have their own “costs”…
Life in an Integration World is always in between.
whats the difference between stored procedure and stored procedure with signature.when i use the stored procedure with signature the input and output parameters come automatically.
i have a situation where a stored proc is called and the Stored proc has few parameters which are available both in output and input( this occurs when i use stored procedure with signature).
how do i separate the input and output parameters separately?
because i need to give as input the input parameters of the Stored procedure.
Hi Avinash,
in SP with Signature is added later to JDBC Adapter thru a feature pack.
Have you tried to map them using INOUT parameter type?
Read up Feature Pack 1 Addendum for JDBC Adapter.
Hi,
I am calling a stored procedure with “stored procedure call using signature”. the Stored procedure has a few input and few output parameters. when i m trying to run the Stored procedure service it gives the following error:
com.wm.app.b2b.server.ServiceException: [ART.117.4002]
Adapter Runtime(Adapter Service) Unable to invoke adapter
service <service>
[ADA.1.316] cannot execute the SQL statement “call <procedure>(?,?,?,?,?,?,?,?,?)}”."
(17004)Invalidcolumn type"
Invalid column type
I am running the Stored procedure Service alone with giving the values.
The stored procedure with signature shows some ref_cursor stuff in the parameters.i am not sure whats that.
Kindly help me.
can i use the simple stored procedure call without signature. if yes how will i configure the ref_cursor stuffs.
Thanks and regards
Avinash
hi,
I am calling a Stored Procedure which has few input and few inout parameters, but while running the Stored procedure alone ,the Stored Procedure is getting called and the output parameters are showing NULL.
what may be the possible reason?
Thanks and Regards
Avinash