remedial mysql???

[Mike Bonner]

If you are using revdatafromquery it wraps your query in a begin and commit
on its own.  If you have 2 queries you want to do, and can't use wheres
with and/or to do it you can still do several queries in 1 call.  To test I
just did this..

*command* queryWithTransaction

   *put* "SELECT * FROM contacts where first_name='Mike';" & cr & "SELECT *
from contacts where first_name='Carl';"  into tSql

   *get* revdatafromquery(,,1,tSql)

   *put* it

*end* queryWithTransaction

It works fine, returns all mikes and carls 1 after the other.  Only tested
on sqlite but the lc side functionality should be the same.  Of course this
is a REALLY bad example since the same thing could be accomplished with a
single query, but as a demonstration that multilines are possible, it
works.

Oh, another thing, if you do add begin and commit to the string you send
using revdatafromquery the returned error is "can't begin a transaction
within a transaction" which is how you know LC is starting and ending the
transaction for you.


If you are doing inserts with revexecutesql (or updates or deletes) then
you should be able to do multilines. I've only actually tried it with
sqlite but there are a couple methods possible (i think)

If you are looping through lots of data and inserting 1 record at a time
this can be slow due to the transaction lag at the server.

If you issue a begin transaction first, then go into the loop and send each
revexecutesql to the server in turn it will build the list waiting for a
commit. After the loop is done, you can send the commit with revexecutesql
and the inserts will be done or if there was a problem NONE of the inserts
will be done. (so you should be checking for errors being returned as
always)

I suspect that you can build the whole list on the rev side and send it as
one big chunk to the server, but I don't know what quirks you might run
into doing it that way.  (
On Sat, Aug 18, 2012 at 2:05 PM, Dr. Hawkins <dochawk at gmail.com> wrote:

> On Sat, Aug 18, 2012 at 11:28 AM, Peter Haworth <pete at lcsql.com> wrote:
>
> > You don't HAVE to remove the START/COMMIT, I was pointing out that they
> are
> > superfluous.  Leave them in if you wish.
> >
>
> OK; I thought that it was up to the db what to do without them.
>
>
> > The semicolon issue has nothing to do with what mySQL requires, it's a
> > Livecode requirement - see the dictionary.
> >
>
> I'm not seeing that in the 5.5.1 dictionary for revExecuteSQL (or
> revDataFromQuery, for that matter).  Am I looking in the wrong place?
>
> And while I'm asking such things, how can Livecode even *have* a
> requirement here--shouldn't it be just passing the command to the database?
>
>
> > Finally, if you're using revDataFromQuery to do this, I'm surprised it
> ever
> > worked.  You're not getting data from a query, you're executing SQL
> > commands so you need to use revExecuteSQL.
> >
>
> Until today, I had been sending single line commands.
>
> I'm trying to cut the lag from a remote db by batching those commands I can
> (there was really no reason for these to be individual queries in the first
> place, other than that there will only be one or two of them typically,
> save when opening a new client "file", in which case there will be
> dozens").
>
> MySQL's own examples are generally multi-line, with the begin/commit
> syntax.
>
> thanks
>
> hawk, confused
> --
> The Hawkins Law Firm
> Richard E. Hawkins, Esq.
> (702) 508-8462
> HawkinsLawFirm at gmail.com
> 3025 S. Maryland Parkway
> Suite A
> Las Vegas, NV  89109
> _______________________________________________
> 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
>