Interfacing two data bases

Applications SQL Builder
#1

I am very new to Scriptcase so my request may not be too well explained; but a bit of background:

  • I am converting a cattle breeding society database from MSAccess to a MYSQL hosted database
  • under MS Access we would ftp a csv file to our public web site (also in a MYSQL database) to enable the 7,500 cattle records to be displayed as pedigree charts
  • because we wanted backward compatibility the new MYSQL tables have replicated the data structure of the MSAccess tables
  • fortunately the MSAccess table, public website database tbales and the new MYSQl tables all have the same field names and types, except two date fields that are datetime in the new MYSQL and need to be Unix timestamp in the web site table.
  • I have got the guts of our database management system built using Scriptcase; all looking good

I now need to get data from three tables in our new MYSQL database into the public web site database. The data will go into a single table that allows display of the pedigree.

  • I have a Select statement in Scriptcase that works Ok.
  • I want to be able to “push” the data from the new MYSQL database to the public web site database
  • I have tried creating a grid app to produce a csv file - in both development and production it works sometimes and on other occasions results in an error message [LEFT][SIZE=13px]"[/SIZE][/LEFT][LEFT][SIZE=13px]Error while accessing the database [/SIZE][/LEFT][LEFT][SIZE=13px]Lost connection to MySQL server during query" I also can’t include a pipe for the column separator.

A better solution would be to empty the public website table and use the Select statement from the new database tables to insert into the public web site blank table.

Does anyone have solutions that would achieve this? The solution also needs to be able to convert two date fields that are in datetime in the new MYSQL but need to be in UNIX timestamp in the web site database table.

Hopefully someone with expertise can suggest a solution and some detailed coding.[/SIZE][/LEFT]

#2

Are you still having this problem?

I think it is better if you try to address the MySQL issue first. The error you were getting on MySQL is directly related to the amount of data you are getting from the server and most of the time, caused by connection time out. This means your query runs longer than the allowable connection timeout.

Number 1 on your checklist should be increasing the connection timeout. Try to increase connect_timeout, and max_execution_time.
Next increase the allowable packets (if the resultset is big) max_allowed_packets
Last, optimize your query. Do not use Select * if you will not be using all fields. Always select only the fields you needed to get smaller resultsets, and make use of index to make your query run faster.

Your grid app should not be total. It should be paging. Then add export results to CSV.

#3

Man thanks for your advice. I reduced the select statement as much as possible and broke the application into smaller components. Our challenge is that we are trying to emulate an existing database table that has a large number of fields. Once we have the application migrated we can break some of the tables down too.