TOPIC: Best approach to filter data

I have an ODBC database that is supposed to get and insert data to a remote mySql server for a website. While it currently does work, the amount of data makes the script run for about 15 hours. What can I do to make it more efficient? Can anyone point me in the right direction?

I tried using the ignore existing records but the primary key on both database tables are different and I cannot change their structure.

ODBC [READ]
number (PK)
qty
inv_date

MySql [WRITE]
ID (PK autoincrement)
number
quantity
date

Is it possible to do a select * ODBC where number > $mysqlNumber ?

Thanks.. all I need is a nudge in the right direction.

PS: I also have a batch script running 5 .FHD sequentially. Does flow heater allow reading and writing from multiple databases?

Hi Charleston,

With version 2.2.1 it is possible to use dynamical SQL statements including parameters . Please have a look to the previous support forum question: Append Only New Records Here you can find also a brief example.

Your second question: By now FlowHeater can only handle one database connection per each side. This feature is planned in a further version.

Cool, thanks for that. I just reading through the documentation and looking at the examples.

So I need to drag and drop the SQL function and Query mySql database for the data I want. Store that in a parameter, configure the READ adapter and add a condition to the query with $parameter$?

Just want to make sure. Thanks again

Yes, you have to run a query on the WRITE side like this.

Select max(number) from your_mysql_table

This result you have to store in a parameter e.g. $LAST_NUMBER$. Note: You have to configure the SQL Heater to run before starts the definition.

This Parameter $LAST_NUMBER$ you can use on the READ (ODBC) side to build a dynamical SQL statement like this

Select * from your_odbc_table where number > $MAX_NUMBER$

That’s all