Solved

MySQL Connector: Custom Query and Message Headers

  • 30 November 2023
  • 2 replies
  • 41 views

Userlevel 4
Badge +1

Hi

 

I have a complex MySQL query with several JOINs need to only fetch new data from the tables.

 

I have read https://cdn.cdata.com/help/AZH/mft/Database-AC.html#only-process-new-or-changed-records but not sure how to apply this to a Custom Query,

 

Can custom queries us data from the message header? If so I may be able to use the method James suggested at 

 

icon

Best answer by James B 30 November 2023, 20:17

View original

2 replies

Userlevel 5
Badge

A SELECT action in the database connector does not have any ability to support the evaluation of headers on a message for dynamic queries, but you may still be able to use the existing features for your complex query.

This would really depend on how complex the custom query that you were issuing is - the features to only process new changes work in one of two ways:

  1. If a datetime column is used to track dates, the connector will dynamically append a WHERE clause to the query to incorporate the date checking as a WHERE clause, like:

    BASE QUERY  WHERE ([LastModified] > @LastModifiedStart) AND ([LastModified] < @LastModifiedEnd); @LastModifiedStart='2023-11-29T15:49:10.8900-05:00'; @LastModifiedEnd='2023-11-30T13:04:24.7125-05:00'

    If the end result of this is a query that your database can parse, you should be able to use a datetime column. 
  2. If you instead use an update column this will require that the table that you select your mapping has a primary key and a writable column that is used to update the records of that table as they are selected to exclude the results from further queries.

In general, however, you may find it difficult to work with complex custom queries and additionally leverage the other designer driven features of the application. We would recommend that you make use of the child tables in your mappings to select records from multiple tables instead of creating a single complex query with multiple JOINs:

https://cdn.cdata.com/help/AZJ/mft/Database-Select.html#selecting-from-multiple-tables

If you’re not able to merge the existing features for processed changes with your custom query, there may be other options for you:

 

  • The Lookup action in the Database connectors is intended when you wish to issue a query that is qualified by some element of the input (either the headers of a message, or elements in the XML body) - we’ve made a lot of improvements in the latest releases of CData Arc : 
    https://cdn.cdata.com/help/AZJ/mft/Database-Lookup.html
  • Alternately, you could create a stored procedure in your database to build your query, return a unique result set, and get only new records since the last run. This puts the burden of maintaining that logic on the database admin, but from the application you just need to use the Stored Procedure action
Userlevel 4
Badge +1

Hi James

 

In your reply you suggested “We would recommend that you make use of the child tables in your mappings to select records from multiple tables instead of creating a single complex query with multiple JOINs”.

My join actually joins across 3 MySQL databases.

This article suggest using multiple connectors https://cdn.cdata.com/help/AZJ/mft/Database-Select.html#selecting-from-multiple-tables

Create multiple Select-configured Database connectors, assign each of them a different table, and connect them all to the same connector in the flow.

 

but I’m not sure how you do that

Reply