An SQL query to return an UPDATE for the results?

[Dr. Hawkins]

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.

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.


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

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


> 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