My latest product, SQLMagic, is inching slowly to a GM release. Using it, you will not need to write any Livecode scripts or issue an SQL statements for a large percentage of your SQL transactions. Email me offline if you would like to be on my SQLMagic mailing list.
On April 16th, I am presenting a Livecode sponsored webinar titled "Advanced Database Coding", more details on the Livecode web site. I will be providing several scripts as part of the webinar. Since I mentioned that, the word "advanced" means different things to different people and I'm trying to decide on what topics to address in the webinar. I suspect many of the people on this list know at least as much as I do about SQL but if anyone has suggestions for what topics to cover, I'd love to hear them. On Wed, Mar 25, 2015 at 1:40 PM JB <sund...@pacifier.com> wrote: > 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 > _______________________________________________ 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
