Data From MySQL to SQLite

[Devin Asay]

On May 23, 2013, at 5:33 PM, Peter Haworth wrote:

> 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,

This is immensely helpful. I had worked out a way to do it using revDataFromQuery(), but that meant I had to loop through the data and create my own array, then insert into the SQLite table using INSERT in a loop. I'm going to give it a try. 

Thanks to Richard, Ruslan and Mark S. for your inputs as well. 

Devin

Devin Asay
Office of Digital Humanities
Brigham Young University