MPS ODBC Module
The MPS ODBC Module is an optional software component that allows Meadows AutoPrice or DesignMerge software to communicate directly with most SQL-compliant database systems. This eliminates the need to provide an intermediate, “flat-file” data extract (although both flat-file and ODBC can be supported at the same time).
How it Works
The system has a very flexible design users construct their own SQL query statements and save them as “fragments”. Fragments can be created using any text editor, but we also provide a built-in fragment editor that lets you create, modify, and test your fragments without leaving the application.
Each SQL fragment is constructed to query a database and retrieve a discrete piece of information. The information returned by the query may be text or picture information (picture information is returned as filename or full path to an image file that exists on an available storage volume). Fragments may also be constructed using a pre-defined list of “replaceable parameters” that will be substituted with relevant data just before the query is submitted to the database. A sample query is presented below:
Select ItemPrice from <TABLE_1> where ItemNumber='<SEARCH_KEY>'
In the above sample query,< SEARCH_KEY> and <TABLE_1> are replaceable parameters. The <SEARCH_KEY> parameter will be replaced with the actual search key value, or record number (typically, a product SKU) that has been attached to the variable placeholder currently being processed. The <TABLE_1> parameter will be replaced with whatever the user has identified as the name of the database table to query (this is defined in the globals section of the Replaceable Parameters setup). The “translated” query (that is, the query that is actually submitted to the database once the replaceable parameters have been substituted by AutoPrice/DesignMerge) might look something like this:
Select ItemPrice from USPriceTable where ItemNumber='J16-084'
The above query, if successful, would return the U.S. price for the product associated with Item Number J16-084. This price would then be inserted into the document, replacing the text that had been there previously.
Lots of Links
One or more of the SQL fragments can be attached to AutoPrice/DesignMerge variable placeholders inside of the document, and there is virtually no limit to the number of placeholders that can be placed on a document page (well, there actually is a limit, but it's somewhere in the thousands - we never actually bothered to count them). When the document is updated, AutoPrice/DesignMerge will swap out the replaceable parameters used in the query, and then submit to the currently selected database the fully composed query for each placeholder. The data returned by the query will then replace the existing data inside of the document.
Easy to Debug
The ODBC Module allows you to test your queries using sample data without leaving the dialog window. In addition, if you enable the special “Interactive Mode”, the ODBC Module dialog window will be displayed each time a query is about to be submitted to the database. This effectively lets you step through an update session, verifying each and every query that is submitted. Turn off Interactive Mode and the session will continue without prompting.
Flat-File Updates Available Too
You can instantly switch between ODBC mode, and flat-file mode, with one menu selection. In flat-file mode, AutoPrice/DesignMerge queries a database extract file rather than a database system. This means that last-minute changes (such as pricing) which may be supplied from a separate business system can be incorporated quickly, without the need to modify any of the variable links inside of the document.
Works with Most Database Systems
The MPS ODBC Module requires the use of third-party software drivers, which must be licensed separately. For Macintosh systems, we support the software drivers developed by OpenLink Software, Inc. Click here for more information about obtaining the appropriate ODBC driver software for your Macintosh system.