MySQL Connector: Custom Query and Message Headers

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