SQL lite adding records

[JB]

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 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
>> 
> _______________________________________________
> 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
>