Data From MySQL to SQLite

[Peter Haworth]

On Thu, May 23, 2013 at 2:31 PM, Devin Asay <devin_asay at byu.edu> wrote:

> Thanks, I'll give that a try. Any idea if this might work for records
> containing binary data? I have one table with around 74 MB of data that I
> need to transfer over.


Hi Devin,
Not knowing much about your data but allowing for as many gotchas as I can
think like binary data and the need to delimit single/double quotes, etc, I
think I'd be inclined to do this with revQueryDatabase to get a db cursor
and using the "variableslist" format for revExecuteSQL, although it's
easier to use an array that a load of variables.

It will make the script a bit more complicated but I think you would run
into less problems that way.

You could have a generic handler that might look something like this
(Untested) with ptable being the name of the table to be loaded and pselect
being the select for the source table.


command loadTable ptable,pselect

local tID,tArray,tNumRecs,tColNames,tValues,tColCount

put revQueryDatabase(mySQLDBID,pselect) into tID
if tID is not an integer then
   answer error ......
   exit loadTable
end if

put revNumberOfRecords(tID) into tNumRecs
put revDatabaseColumnCount(tID) into tColCount

put empty into tArray
put "(" into tValues
repeat with x=1 to tColCount
   put ":" & x & comma after tValues
end repeat
put ")" into char -1 of tValues

revExecuteSQL SQLiteDBID,"BEGIN"

put zero into tColNumber
repeat tNumRecs times
   repeat tColCount times
      add 1 to tColNumber
      get revDatabaseColumnNumbered(tID, tColNumber,tArray[tColNumber])
   end repeat
   revExecuteSQL SQLiteDBID,"INSERT INTO <tablename> VALUES" &&
tValues,"tArray"
   revMoveToNextRecord(tID)
end repeat
revExecuteSQL SQLiteDBID,"COMMIT"

end loadTable

More error checking needed of course, along with a ROLLBACK if any db stuff
fails.


Pete
lcSQL Software <http://www.lcsql.com>