An SQL query to return an UPDATE for the results?

[Dr. Hawkins]

On Wed, Aug 7, 2013 at 9:05 PM, Peter Haworth <pete at lcsql.com> wrote:
> 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:
>> 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.

It's a single SELECT, but after doing that from the remote/file
database, I haven't found any way other than 350 UPDATE statements to
put in in the in-memory SQLite database.

I make changes on the in-memory database, and have a lurking process
that watches for lack of keystrokes and synchronizes periodically.  So
it will find a few rows with their  "changed" column set from a single
SELECT, which data then immediately gets written to the remote.

I can loop through each line of the remote, but I'm thinking that it
would be cleaner to do my query in a way that fills out the strings,
so that rather than a few rows of "A<t>B<t>C" I get "UPDATE aTable SET
valA='A', valB='B', valC='C' WHERE key='thisKey';" all ready to wrap
in BEGIN/END and submit.

And I'm thinking that I could replace any tab&tab with tab&"NULL"&tab
before the insertion into postgres issue.

All in all, the maintainability and my being able to see things
clearly is the most important--the time to do the looping seems small
compared to the lagtime on the remote query.


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

I found this with postgres queries; the later lines get padded to the
same number of
items with the results of the earlier items!

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

 Correct.  If two people are entering data, and enter different values, one is
wrong.  That level of user error is beyond anything I can solve . . .
(A disclaimer to "fire your dumbest employee" ?  :)   )



>> > 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 if I have five columns that *could* be NULL, that's 32 combinations of
SELECT, or 10 SELECTs that get reassembled . . . and then an ugly assembly
of each row.

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

Oh, I definitely want it protected--but it seems that I'm protecting against
someone who already has full access to the internal network, including
the program.

When writing user-entered data (or calculated data; for that matter),
 I use a preSQL() function to wrap text in quotes, escape quotes, etc.
 (But the current issue is straight db to db).

thanks again

-- 
Dr. Richard E. Hawkins, Esq.
(702) 508-8462