On May 23, 2013, at 5:33 PM, Peter Haworth wrote:
> On Thu, May 23, 2013 at 2:31 PM, Devin Asay <devin_a...@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
_______________________________________________
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode
