SQL lite adding records

[JB]

Hi Peter,

Thank you so much for the detailed info!

Each value has single quotes now but it
would be nice to do it without quotes as
adding them takes more time.

I will definitely use th BEGIN statement
and thank you for explaining it.  I tried to
use revCommitDatabase after the repeat
without the BEGIN and it was missing a
file.  Your way will be a lot better and I
will include the error checking too.

I really do not know that much about using
a database so if things look weird it is due
to me not knowing what I am doing.

I tried to go to your site and look at the stack
you have for sql database administration but
I got a 404 error.  Does it include code to sort
and find with SQL?  If so I am interested.

Thanks again!

John Balgenorth


On Mar 24, 2015, at 6:52 PM, Peter Haworth <pete at lcsql.com> wrote:

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