SQL lite adding records

[Bob Sneidar]

Just to be clear, using BEGIN and COMMIT is called transactional. It’s purpose is to be able to ROLLBACK if any errors occurred. This not only includes SQL errors (which should not be happening if you control the SQL and data), but internal errors, like for instance you insert an invoice then go to update the running totals in a customer record, but find someone else has the customer record locked, so you need to roll back any inserts/updates you’ve done and alert the user. 

For simple SQL Inserts/Updates where errors are highly unlikely and you are not using SQL in a multiuser setting, it isn’t strictly necessary IMHO. There is one other benefit and that is speed. From what I gather from the discussions here, transactional processing gains some performance for a large number of sequential transactions, because the SQL engine does not have to actually do the real work of inserting/updating/indexing until you tell it to COMMIT, during which you client app goes on it’s merry way. 

Others more knowledgable than me will correct me on any points I may have wrong. 

Bob S


> On Mar 24, 2015, at 19:11 , 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