Copying records between 2 different databases

Scriptcase 8 Discussion
#1

I have a need to copy data between MySQL and SQLite databases. I’m wondering what would be the best approach. I was thinking about using SC macro “sc_change"Connection”. Read record from database1 then switch connection and save this same record into database2. Not sure how well this would work and how efficient this would be. Of course on the way I might need some data manipulation (filtering, inserting some data in the fields, etc.). Because of the data manipulation I cannot copy those records blindly, records which need to be copied must go through some procedure (which is not important here for the question). I also need to do the TIMESTAMP() field comparison. The Synchronization will be done initially one way only but late on it will be bi-directional.

I wonder if anybody has done something like that or if there is any other better solution.

ARTHUR

#2

I think, you must create a new connection and use it when you Insert data with the macro .SC_EXEC_SQL
First create your query with the conection for default. with a WHILE you insert the data to new new connection using the macro…

#3

SC_EXEC_SQL(“YOUR INSERT”, “YOUR_NEW_CONNECTION”);

#4

I neeed to do MySQL <-> SQLite sync and I also need to do MySQL <-> MySQL sync

well, the question is:

  • shall I do it record by record or in chunks using arrays
  • shall I switch connections on every READ/WRITE (how effective AND secure this would be)
#5

Try this product, it makes what you are doing a piece of cake.

https://www.sqlmaestro.com/products/sql_anywhere/datasync/

#6

Thanks Dr Tim - I have seen it before. The challenge is that I need this functionality integrated into the App so 1- it is not visible 2- not sby the end user (to manipulate) 3-I can distribute it with the App. If there is some kind of liberary or file I could run unattended it would work but I doubt. There is also another issue to run this on the hosting (shared) server.

#7

With INSERT … SELECT, you can quickly insert many rows into a table from one or many tables.

INSERT INTO orders (customer_cust_id, orderdatetime, message, taxrate, shippingprice)
SELECT ‘1’, NOW(), null, taxrate, shippingprice FROM customer
WHERE cust_id=‘1’;

https://dev.mysql.com/doc/refman/5.5/en/insert-select.html

#8

Dr.Tim - do you use SQLMAESTRO ? Can it run in unattended mode ?
My idea (at least at this moment) is to Sync SQLite database with MySQL server, but the SQLite would reside 1- on the desktop PC 2-on a mobile device
so I am looking for simple and efficient way of doing this. Of course I’m talking about one database on the server and multiple databases on the clients.