It would be nice to have a good stack with all of the power and tricks using SQL with Livecode.
I would even pay for it. Any power users want to make a few dollars? If you do I personally thinks it should be code examples instead of some hidden library etc. John Balgenorth On Mar 25, 2015, at 1:26 PM, Erik Beugelaar <ebeugel...@gmail.com> wrote: > I am not surprised. A lot of developers do not know the power and tricks of > presenting and performing data via SQL language. > > Mike Bonner <bonnm...@gmail.com> wrote: > >> Part of the slowdown when inserting a large number of rows one at a time is >> the opening and closing of the transaction file. If you want to see this >> in action, you can set a loop up to do repeated single row inserts, then >> watch in your file browser next to the db file as the transaction file >> appears, disappears, appears, disappears, over and over and over. There is >> a bunch of overhead with creating and removing this file for each insert in >> sqlite. By using begin/commit, the transaction file is only created once, >> and the speedup is tremendous. >> >> On Wed, Mar 25, 2015 at 12:53 PM, JB <sund...@pacifier.com> wrote: >> >>> Okay, thanks! I have been looking >>> a SQLYoga and see it has search, >>> sort and find. >>> >>> It seems to use arrays but with using >>> large amounts of data in a data grid >>> you need to use a cursor. >>> >>> SQLYoga does provide the ability to >>> use cursors but since it is using the >>> arrays is that using a lot of memory? >>> >>> John Balgenorth >>> >>> >>> On Mar 25, 2015, at 10:03 AM, Peter Haworth <p...@lcsql.com> wrote: >>> >>>> Whoops! Thanks for letting me know about the 404 error - will go to fix >>> it >>>> right now. >>>> >>>> SQLIteAdmin does have query/sort features but, just to be clear, it is a >>>> standalone program so you can't see the code. >>>> >>>> Pete >>>> >>>> On Tue, Mar 24, 2015 at 7:14 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 maili > _______________________________________________ > 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
