- Posts: 2
Best approach to filter data
- Charleston Olaes
- Topic Author
- Offline
- User
Less
More
12 years 4 months ago #2175
by Charleston Olaes
Best approach to filter data was created by Charleston Olaes
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?
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?
Please Log in to join the conversation.
- FlowHeater-Team
- Offline
- Admin
12 years 4 months ago - 4 months 2 weeks ago #2176
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Best approach to filter data
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.
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.
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Last edit: 4 months 2 weeks ago by FlowHeater-Team.
Please Log in to join the conversation.
- Charleston Olaes
- Topic Author
- Offline
- User
Less
More
- Posts: 2
12 years 4 months ago #2177
by Charleston Olaes
Replied by Charleston Olaes on topic Re:Best approach to filter data
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
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
Please Log in to join the conversation.
- FlowHeater-Team
- Offline
- Admin
12 years 4 months ago #2178
by FlowHeater-Team
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Replied by FlowHeater-Team on topic Re:Best approach to filter data
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
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
Best wishes
Robert Stark
Did this answer your question? We would be grateful if you provide a brief comment as feedback. It may also help others who may have encountered a similar problem.
Please Log in to join the conversation.
Time to create page: 0.289 seconds