SQL lite adding records

[Peter Haworth]

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
>