On Wed, Aug 7, 2013 at 11:20 AM, Dr. Hawkins <[email protected]> wrote:
> On Wed, Aug 7, 2013 at 10:21 AM, Peter Haworth <[email protected]> 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 > [email protected] > Please visit this url to subscribe, unsubscribe and manage your > subscription preferences: > http://lists.runrev.com/mailman/listinfo/use-livecode > _______________________________________________ use-livecode mailing list [email protected] Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
