SQL lite adding records

Wed Mar 25 18:59:46 EDT 2015

I saw the SQLMagic seminar at RR 13. It was very impressive. The beginner can dive into databases immediately. Even experts can benifit as well as the beginner. Experts can set up data entry screens with complex schemas in a flash.

Ralph DiMola
IT Director
Evergreen Information Services
rdimola at evergreeninfo.net

<div>-------- Original message --------</div><div>From: Peter Haworth <pete at lcsql.com> </div><div>Date:03/25/2015  17:12  (GMT-05:00) </div><div>To: How to use LiveCode <use-livecode at lists.runrev.com> </div><div>Subject: Re: SQL lite adding records </div><div>
</div>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
>
_______________________________________________
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