[GRASS-user] db.execute: SELECT STATEMENT

[Brad Douglas]

On Tue, 2007-01-30 at 20:00 +0100, Jarek Jasiewicz wrote:
> Wolf Bergenheim napisał(a):
> > Markus Neteler wrote:
> >> Right - which is confusing since the module says:
> >> "db.execute  - Execute any SQL statement"
> >> apparently wrong? Or a bug?
> >
> > This is a case of programing technique showing in the UI. Normally in 
> > programming with databases, there are two kind of queries you can give 
> > to a database. All (well almost, there might be some exception) DB 
> > APIs give the programmer 2 functions execute and select. The select 
> > function usually return a dataset - the response to the query, while 
> > the execute just returns a success flag, since this function is used 
> > to execute statements like UPDATE, DELETE, INSERT etc. A good UI would 
> > hide this distinction based on the query.
> >
> >> For now, just use db.select instead.
> >> But we should solve this confusion.
> >>> I add, that combination can be execute in pgadmin without problem so 
> >>> statements are good
> >> It seems to be a (mysterious to me) limitation in db.execute.
> >
> > So either fix db.execute to call a select function instead, or even 
> > db.select if it gets a SELECT statement to execute? Otherwise fix 
> > documentation, meaning that the manual and help should say "...EXECUTE 
> > any non-SELECT statement. Use db.select to execute SELECT statements." 
> > Or something like that.
> >
> > --Wolf
> >
> But what about this (sample test query):
> 
> echo "UPDATE testpoint_2 SET test=(SELECT max(cat) FROM testpoint_1) 
> WHERE cat IN (SELECT cat FROM testpoint_3 WHERE x>10)" | db.execute
> 
> it works! AFAIK such quries are treated as both SELECT and UPDATE query!

FYI, that's an update with an inner query.  It's still considered an
update query by SQL standards, no matter how complex it may be.

Select statements are not considered executable in the database world,
but I agree with Wolf that the documentation needs clarification as this
may not be common knowledge.


-- 
Brad Douglas <rez touchofmadness com>                    KB8UYR/6
Address: 37.493,-121.924 / WGS84    National Map Corps #TNMC-3785