Everyone knows at this point from reading my former blog posts how excited I tend to get over Queue based integration processes. Well, you are all in luck, because Query Publishers allow Queue based integrations to work! This feature of the Scribe application allows us to take data returned to us from a query and transform it into XML, which is then posted to our ScribeIn queue for processing. What follows is a technical review of how this works, and how you should best utilize it.
A Query Publisher requires just a few things: a connection to a database, data, and some kind of net change pattern. It's very straight forward to setup, but allows you to take advantage of some very interesting features within Scribe. Let's start off talking about the Data Source.
The Query Publisher needs to pull data from somewhere in order to produce XML. This "somewhere" can be an ODBC DSN, a native SQL connection, or an Adapter. So wherever your data resides, Scribe can generally get to it. Once you have this created (note to readers: when using ODBC DSN's please ensure they are created as System DSN's and not User DSN's) you can establish a link to that data source in the setup of your query publisher, illustrated below.
Once you have a connection, it's time to figure out how to get the data you need from there. This is the part where you must decide on a net change pattern. I'll detail two patterns here, but, there are that you could use. The idea is to only get the data that has changed since the last time the publisher ran it's query. The first method I'll cover is using a sync flag.
This method relies on a table containing a field that only the integration utilizes. If you were to look at the ScibeShadow table setup you would see this field listed as a SyncStatus field. This field does not need to be very robust, at the very least if the field remains #NULL! on inserts, then we can key off of it. We need to have control over values placed in the field. So, we would use the Update Source feature to change our key field to a value we determine, and use that in the source query to determine what records Scribe has touched. For an example of a Scribe Shadow table, please refer to the ScribeTriggers SQL script located in your Program Files\Scribe directory.
This method relies on a field that only the integration uses. You need to have complete control of the values in this field and not allow users to directly interact with this field. Ideally, you could use a SQL trigger to set the value in the sync field to I when a record is inserted and U when a record is updated.
The source query in your query publisher selects records where the value in the sync field is not equal to S. That way it will select all the records that have a value of U or I in the sync field.
Configure the update source feature in your query publisher to set the value in the sync field to S. That way, any records that are selected by the query publisher and pushed to the queue as XML will have the value of the sync field set to S, meaning that the record has been synced. That ensures the record is not processed if it has not changed since the last time the query publisher ran.
The second method does not require any extra "trimmings", and therefore is generally more universally accepted. All you need for this method is a modified timestamp on the record. In this case you would configure the query in the query publisher to only pull back records that have been modified since the last time we produced data. Simple, right?
The way we make this work is by using two System Variables. The main one is :LastRunDateTime. This variable returns the last time the publisher ran successfully and produced XML. This means we have a value that we can use in a comparison. It allows us to have a query that selects only data that has changed since the last time the query publisher ran.
We use :LastRunDateTimein combination with another variable, :ThisRunDateTime.
The clause you would want in your query would be something along the lines of "select * from table where ModifiedStamp >= :LastRunDateTime and ModifiedStamp < :ThisRunDateTime". The "<:ThisRunDateTime" part gives you a specific window of time to use in your comparison, ensuring that your query publisher does not select a record two times on two successive runs of the query, if the record happens to be updated while the query is running.
The :LastRunDateTime variable only gets updated when the publisher finds data, and creates XML from that data. If the query finds no results, the :LastRunDateTime is not changed. Also, if there is some critical error where the XML cannot be generated (MSMQ service stops for example) then the :LastRunDateTime variable does not get updated. :LastRunDateTime only gets changed when the publisher actually creates XML.
Once you have your data source set and your net change pattern defined, you are ready to publish! The last few things you must decide on are the Message Label, which should uniquely tie this XML to a queue based integration process, whether or not you require repeating nodes (such as publishing Sales Orders with multiple line items), and how often the publisher should run (every few seconds/minutes/hours). When the publisher is active it will begin running the query you defined earlier at the interval you determined and start filling the queue with XML for your Queue based integration processes.
If you would like to see videos that walk you through working with query publishers, step-by-step, check out the Scribe Inside Track service. A subscription to the Inside Track gets you access to deep dive educational videos, Web meetings, and a quarterly training newsletter.
Comments