Thanks for the reply and infer. Bob! Your info always helps a lot.
John Balgenorth On Mar 25, 2015, at 9:53 AM, Bob Sneidar <bobsnei...@iotecdigital.com> wrote: > 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 <sund...@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 <p...@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 <sund...@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@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@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@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@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@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
