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
