SQL lite adding records

[JB]

I should mention when I said sort and find I
mean in a single table.  I did the Sample
database and can do the inner and outer
joins.  I also have Valentina and can do
just about everything they offer including
sort and find but I want to use SQL also.

John Balgenorth

On Mar 24, 2015, at 7:11 PM, JB <sundown at pacifier.com> wrote:

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