SQL lite adding records
pete at lcsql.com
Wed Mar 25 13:03:06 EDT 2015
Whoops! Thanks for letting me know about the 404 error - will go to fix it
SQLIteAdmin does have query/sort features but, just to be clear, it is a
standalone program so you can't see the code.
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
> > 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
> > if all worked OK, otherwise an error message. If an error occurs, issue
> > 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
> > 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
> > 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
> > 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:
More information about the Use-livecode