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