SQL lite adding records

Peter Haworth pete at lcsql.com
Tue Mar 24 21:52:38 EDT 2015


Hi John,
A few ideas for you.

You should issue a BEGIN statement right before your repeat statement and
move your revCommitDatabase to right after the end repeat.  Better
performance and guarantees db integrity.

For some reason I don't understand, LC does not have a revxxx statement to
issue a BEGIN so you will need to use revExecuteSQL gConID,"BEGIN" right
before the repeat statement.

Within the repeat, don't forget to check if revExecuteSQL resulted in any
errors by checking "the result" right after it - it will be a numeric value
if all worked OK, otherwise an error message.  If an error occurs, issue a
revRollBackDatabase command and exit the repeat.  That will return your
database to a consistent state before any of your INSERTs happened.

I'm a little confused by your INSERT statement.  You name 4 columns in the
table but only supply 2 values, maybe tLine has comma separated values in
it? If so, you will need to enclose each value in single quotes if they are
not numeric.

Also, if recID is the primary key, no need to include it in the list of
columns to be updated or supply a value for it, SQL will take care of that
for you.

Hope that helps,

On Tue, Mar 24, 2015 at 5:04 PM JB <sundown at pacifier.com> wrote:

> I am using MySql on a local host and
> creating a database then adding records.
> After creating the table I was using the
> handler below to add records.  I was not
> using the revCommitDatabase gConID
> statement after each record and some
> times my database would be missing
> one or two records.  After including the
> revCommitDatabase gConID statement
> it appears to be including them all.  Is
> this the proper and fastest way to add
> records in a sql lite database or should
> I be using something else like a query
> to update instead?
>
> on fAddFiles
>    set the itemDelimiter to tab
>    put fld id 5246 of cd id 5187 into tList
>    repeat for each line tLine in tList
>       put "INSERT INTO tFiles(recID,field2, field3, field4) VALUES(null,"
> & tLine & ")" into sqTable
>       revExecuteSQL gConID,sqTable
>       revCommitDatabase gConID
>    end repeat
>    fRefresh
>    gConIDClose
> end fAddFiles
>
> Any information or ideas are helpful.
>
> John Balgenorth
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>



More information about the use-livecode mailing list