I have come up with a requirement i.e. We have Oracle Student Systems Applications, Whenever students adds the unit course to their Enrollments one of the base table gets inserted with the student details and the units the students has selected. As soon as the record is inserted into Oracle Students System Applications Base tables an database trigger fires and the same record is inserted into a Oracle custom table so that WM can picks the data and do the necessary data conversion and write it to a file.
I know how to write the data picked by WM to a file. I was wondering How can I pick the data that was inserted by database trigger into Oracle Custom table.
Can anybody let me know how to initiate the process? Can anybody let me know the steps to make this happen?
You could use either an insert notification which would publish notification documents when rows were inserted into your custom table. If you plan to use your custom table as a webMethods buffer table, then you could use a basic notification to poll the custom table deleting rows when notification docs have been successfully published.
Both notification types are described in detail in the JDBC Adapter User’s Guide found in the \IntegrationServer\packages\WmJDBCAdapter\doc folder.
When ever a row inserted in the DB tables,create a JDBCNotification Adapterservice and login to JDBCAdapter admin page and click on pollingnotifications then you should see the created service there and enable it and schedule an interval for polling the DB table.
So if any things updates on the table the polling notification will trigger and publishes the document what ever you have created in the JDBCAdapterNotification service you have to subscribe the published document using subscribe service it will return the results insert/updated data and loop the results and writeToFile.
As mark suggested above the userguide will help you for creating notification/making use of WM buffer tables etc…
As you know I am learning WM. I am just trying take the data that’s inserted in a source table from SQL*PLUS and insert into the destination table not to a file right now. I just want to give a shot a see.
a.) I created an test package. I created the JDBCNotification
AdapterService and pollingnotifications for every 15 sceonds.
I have the insertAdapterMonitorPublishDocument and I have the
InsertAdapterMonitor.
b.) I have an JDBC Adapter Service (InsertUnitsAssignments) which
inserts into the destination table.
c.) I have created an trigger and I have given:
1.) The Document types and filters as “PorikaPractice.InsertNotification:insertMonitorPublishDocument” and I don’t have any filters right now.
2.) Service as:
PorikaPractice.InsertNotification:InsertUnitAssessments
3.) I don’t have any conditions.
d.) I have a sub service and I gave the “InsertNotification:insertMonitorPublishDocument” as input to my
JDBC Adapter Service (InsertUnitsAssignments) and I insert a record into the source table and after 15 seconds I can see an record inserted into the destination table but without any values, all null values.
I am not able to understand why only Null values are getting inserted?
I just ran the JDBC Adapter service seperately from WM,which inserts the records into the destination table without any problems.
Since I am getting the data in the form of document from Broker whenever the the trigger fires on insert, Do I need to convert the document byte/stream etc… and map it to the insert adapter service?
Those messages are normal when your mapping from fields which don’t contain data. Add a pub.flow:tracePipeline statement to your triggered flow service to see what information is being passed. You should see the contents of the published document type in the console or server log.
You might need to map data retrieved from your database notification to get it in the right format for your subsequent insert, but that’s completely dependent on your data. No way to tell from here.
I was able to debug and solve the problem. Thank you so much for your help. Now I am ready for my next task. I learned it and now I am going to implement it.
hi rmg,
i have very much similar situation ,but i am unable to create the insert adapter notification, the following errors are displayed when i try to schedule the polling of this notification on IS
Error in Notification Callback:enableCallback notification
Cannot execute the SQL statement "CREATE TABLE WMBbcagefc ---------
Can’t start a cloned connection while in manual transaction mode
can u tell me why am i getting these errors,
its unable to create the buffer table .and other errors as u can see them above.
thanks
sri