SQL lite adding records

Wed Mar 25 17:12:43 EDT 2015

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 <sundown at 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 <ebeugelaar at 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 <bonnmike at 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 <sundown at 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 <pete at 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 <sundown at 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 <pete at 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 <sundown at 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 at 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 at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>