LiveCode Forums.

Posted: **Tue Jun 30, 2009 1:40 pm**

Hello

I have an .sql file (created from MySQL). This file only contains INSERT data like;

INSERT INTO ordersummary VALUES("1","1.95","57.52","Completed")
INSERT INTO ordersummary VALUES("2","1.95","57.52","Completed")
INSERT INTO ordersummary VALUES("3","1.95","57.52","Completed")
INSERT INTO ordersummary VALUES("4","1.95","57.52","Completed")

These four columns in order are;
RecordID, OrderShipping, OrderFullPrice, OrderStatus. I will NOT require Create Table etc...all will exist already.

Using RunRev database commands/functions, does anyone know how I can import the data from the .sql file into an SQLite database table of the same schema.

I have looked in the help files but cannot seem to find a method of executing this task.

Any hints and tips gratefully received!

Posted: **Tue Jun 30, 2009 7:17 pm**

Ypu coulmd use the sqlite3 command-line application through a shell call, which has a .import TABLE_NAME command (see http://www.sqlite.org/sqlite.html for more info)

The alternative is using the revExecuteSql command repeatedly to issue indivual INSERT queries.

Jan Schenkel.

Posted: **Wed Jul 01, 2009 9:10 am**

I apologies, I should perhaps clarify my requirement and explain the rationale of what I am trying to achieve.

My client, will have an online database, in MySQL. They will download an .sql file containing 'new' records on a (for example) daily basis. They wish then to add the records in the .sql file to their 'local' SQLite database, for use in a desktop application.

The idea behind this is to sychronise the Local and Remote databases and be able to use the data in the desktop application that we provide them.

Sometimes, they may not have an internet connection, so it is important that they can continue to use the application, in which case then, they will be able to use data on the local machine and carry on working.

I might be wrong of course. Is there a better way of resolving this requirement?

Andrew Paget

Posted: **Thu Jul 02, 2009 6:08 am**

Thanks for the clarficiation, the solution is "simple" then: read the .sql file line by line, executing each line using the revExecuteSql command; or use the sqlite3 command-line application to execute the .sql file in one go, using the .read FILE command.

Jan Schenkel.

Posted: **Thu Jul 02, 2009 9:01 am**

Well, I think that maybe using the command line is not an option as the customer will have to learn how to do this. I think it seems the easier option to use the revExecuteSQL command option.

Are there any examples out there that show how to do this. Essentially, I would need to allow the customer to specity the .sql file from which the data should be imported, maybe something like;

revExecuteSQL "INSERT into admindetails (SELECT * FROM file(file.sql))"

Thanks to anyone for any hints

Posted: **Thu Jul 02, 2009 9:10 pm**

If you want to use the revExecuteSql command, you'll read the file line by line, and execute the command it contains. Given that your .sql file is already a collection of SQL satements, ypou don't need to do any conversion.

Code: Select all

on mouseUp
  global gDbConnectionId
  answer file "Please select the .sql file to import"
  if the result is "Cancel" then exit mouseUp
  put URL ("file:" & it) into tSqlScript
  repeat for each line tSqlStatement in tSqlScript
    revExecuteSql gDbConnectionId, tSqlStatement
  end repeat
  answer "Done"
end mouseUp

The above is a crude example and lacks error checking; it also assumes that you've already connected to the SQLite database and stored the connection id in the global variable gDbConnectionId.
Bear in mind that you're leaving the door open for malicious SQL scripts that could nuke your database. Depending on the number of SQL statements in the file, you may also want to show some progress indication.

HTH,

Jan Schenkel.

Posted: **Sun Sep 06, 2009 7:00 pm**

I'm new to both Revolution and SQLite and still getting my bearings before I start any serious work, but for what it is worth:

Keep in mind that SQLite takes each insert as a full transaction (See http://www.sqlite.org/faq.html#q19 for more details). I think you could combine the inserts into one script, then execute the script wrapped within a single transaction, as that would allow rollback of the import if any of the rows fails to insert for whatever reason. I am assuming this would also cut down on the overhead associated with the revExecuteSql command. Of course, being a newbie, I am blissfully unaware of any size limitations revExecuteSql puts on the SQL statement provided.

Depending on size, you might want to chunk it up into managable blocks, but that does risk that part of the import is "irreversible" if one of the later rows fails to insert.

Arnoud.

Posted: **Wed Sep 09, 2009 2:46 pm**

Keep in mind that SQLite takes each insert as a full transaction (See http://www.sqlite.org/faq.html#q19 for more details). I think you could combine the inserts into one script, then execute the script wrapped within a single transaction, as that would allow rollback of the import if any of the rows fails to insert for whatever reason. [/quote]

You can control when a transaction begins and ends by using revExecuteSQL to BEGIN TRANSACTION, COMMIT TRANSACTION or ROLLBACK transaction. If I am ever performing more than one insert/update with SQLite I wrap the operation in my own BEGIN/COMMIT TRANSACTION calls. This keeps SQLite from creating the journal file on disk once for every insert/update and speeds things up dramatically.

LiveCode Forums.

Use a .sql to insert data into SQLite

Use a .sql to insert data into SQLite

Use a .sql to insert data into SQLite