An SQL query to return an UPDATE for the results?

[Peter Haworth]

On Wed, Aug 7, 2013 at 11:20 AM, Dr. Hawkins <dochawk at gmail.com> wrote:

> On Wed, Aug 7, 2013 at 10:21 AM, Peter Haworth <pete at lcsql.com> wrote:
> > Don't know how many rows are involved but it would be much faster to
> select
> > all the rows from the memory db table with one SELECT, then build the
> > UPDATE statements using an LC script.  An wrap the whole thing in
> BEGIN/END.
>
> There are three tables. One has a half dozen columns, and ~350 rows, a
> few of which would usually be done at once (although doing all 350 on
> load has to happen, too).  The other would only use a couple of rows
> at a time (in fact, usually just one),  but there are about 50
> columns, some numeric, others text of various types, and a few
> booleans.
>
> It seems that a query that produces a string half a dozen lines that
> are already sql transactions, just needing BEGIN/END wrapper, would
> necessarily be faster than looping around on the received data.
>

Selecting 350 rows with 350 SELECT statements is always slower than
selecting all the rows with 350 SELECT statements.

>
> Right now, the various UPDATEs all get wrapped into a single
> transaction with BEGIN/END.
>
> I can't wrap the selects like that, due to the bug on the return values:
>
>     SELECT val1,val2 FROM tableA
>     SELECT val1  from tableB
>
> where tableA is all A and B is all B yields
>
>      A,A       <- this is correct
>      B, A      <- the A is left over from the prior query with more
> columns.
>
> What bug?  I have never experienced that.  Alos , putting the SELECT
> within the BEGIN/END stops anyone else updating the db at the same time,
> although sounds like that;s not an issue , at last theoretically.
>


> > As for the NULL issue, you could do two selects, one for rows where the
> > column IS NULL and one for rows where the column is NOT NULL, and format
> > the UPDATE statements accordingly.
>
> But I wouldn't know which columns were NULL until I got the results of
> the query . . .
>

Of course you wouldn't, that's the point of the separate SELECT statements.

>
> But on further review,
>
>    replace tab & tab with tab & "NULL" & tab
>
> would seem to handle the situation.
>
> > If you're sending the UPDATE statements over a network, you should use
> the
> > LC feature of supplying the values in a list of variables/an array to
> avoid
> > SQL injection issues.
>
>
> The "remote" would be a postgres server either on the same localnet,
> or wrapped with authentication over https.  It may be insufficient
> imagination, but I'm not seeing how an attacker that can inject
> doesn't already have far more serious access . . .
>
> Yes, insufficient imagination is what it is.  I would think you would want
to protect yourself from any hacks if you're dealing with bankruptcy
information.

>
> > Also, is it possible that there won't be a matching row in the
> disk/netwrk
> > database?  If so, do the UPDATE and check if the return value is not
> zero;
> > if it is zero, it means no rows were updated and you need to do an INSERT
> > instead.
>
> That won't be an issue--the remote/master will have them all in one
> case, and is the "assigning power" for the key in the other.  If a
> local creates (new debts or assets), it is going to know that it's new
> and to do an insert.
>
> > Not even gonna get into the issue os what happens if two users try to
> > update from memory at the same time, or perhaps that's not possible in
> your
> > application.
>
> Not really possible  without the user having far more serious
> problems--if your secretaries are having some kind of update war on a
> bankruptcy file, or inputting conflicting data for a client's debts,
> that's beyond my ability to solve . . . :)
>


>
> thanks
>
>
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
>
> _______________________________________________
> 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
>