SQL lite adding records

[JB]

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
>