[GRASS-user] db.select limitation + psql workaround

GRASS GRASS user
1

Hi,

db.select balks when the SELECT queries are sufficiently complex (experienced in GRASS6.3).

Example:

echo
"SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP BY FOO.county_name_ascii,FOO.county_id;"
| db.select

results in:
DBMI-Postgres driver error:
Cannot select:
SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP BY FOO.county_name_ascii,FOO.county_id;

For Linux/PostgresQL this can be circumvented by using a single line call to psql with the identical SQL command:

psql -c
"SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP BY FOO.county_name_ascii,FOO.county_id;"
-dmeteo_null

Best,
Peter
--
Dr. Peter Löwe
<peter.loewe@gmx.de>

Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

2

On 16/01/09 16:06, peter.loewe@gmx.de wrote:

Hi,

db.select balks when the SELECT queries are sufficiently complex (experienced in GRASS6.3).

Example:

echo "SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP BY FOO.county_name_ascii,FOO.county_id;" | db.select

results in:
DBMI-Postgres driver error:
Cannot select:
SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP BY FOO.county_name_ascii,FOO.county_id;

Could this be because of the SELECT ... INTO which in my logic is a special case of the SELECT statement which would need to be executed via db.execute as db.select only handles "pure" selections, i.e. no modifications or creations ?

Moritz

3

-

> db.select balks when the SELECT queries are sufficiently complex
(experienced in GRASS6.3).
>
> Example:
>
> echo
> "SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT
county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY
county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP
BY FOO.county_name_ascii,FOO.county_id;"
> | db.select
>
> results in:
> DBMI-Postgres driver error:
> Cannot select:
> SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT
county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY
county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP
BY FOO.county_name_ascii,FOO.county_id;

Could this be because of the SELECT ... INTO which in my logic is a
special case of the SELECT statement which would need to be executed via
db.execute as db.select only handles "pure" selections, i.e. no
modifications or creations ?

Moritz

I do agree. However, since the statement begins with the "SELECT"-string, db.execute refuses to execute it.

Peter
--
Dr. Peter Löwe
<peter.loewe@gmx.de>

Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

4

"Peter Löwe" wrote:

> > db.select balks when the SELECT queries are sufficiently complex
> (experienced in GRASS6.3).
> >
> > Example:
> >
> > echo
> > "SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT
> county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY
> county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP
> BY FOO.county_name_ascii,FOO.county_id;"
> > | db.select
> >
> > results in:
> > DBMI-Postgres driver error:
> > Cannot select:
> > SELECT county_id,county_name_ascii, max(area) INTO ovl_max FROM (SELECT
> county_id::integer,county_name_ascii,met_id,area FROM ovl_110 GROUP BY
> county_id,county_name_ascii,area,met_id ORDER BY county_name_ascii) FOO GROUP
> BY FOO.county_name_ascii,FOO.county_id;
>
> Could this be because of the SELECT ... INTO which in my logic is a
> special case of the SELECT statement which would need to be executed via
> db.execute as db.select only handles "pure" selections, i.e. no
> modifications or creations ?

Technically, SELECT INTO is an entirely different command to SELECT,
not a special case of it.

[Similarly, there is no CREATE command, only CREATE TABLE, CREATE
DATABASE, and so on.]

FWIW, the PostgreSQL documentation says that CREATE TABLE AS (which is
a different command to CREATE TABLE) is the recommended method.

I do agree. However, since the statement begins with the
"SELECT"-string, db.execute refuses to execute it.

This is a bug in db.execute, and the only simple fix is to remove the
check altogether.

Distinguishing SELECT from SELECT INTO is hard enough if you know the
exact language syntax, but db.execute has to work with all DBMI
drivers. The only realistic way to perform such a check would be to
implement it in the individual drivers.

--
Glynn Clements <glynn@gclements.plus.com>

5

On Sat, Jan 17, 2009 at 11:51 AM, Glynn Clements
<glynn@gclements.plus.com> wrote:

"Peter Löwe" wrote:

I do agree. However, since the statement begins with the
"SELECT"-string, db.execute refuses to execute it.

This is a bug in db.execute, and the only simple fix is to remove the
check altogether.

As simple as this?

Index: db/base/execute.c

--- db/base/execute.c (revision 35372)
+++ db/base/execute.c (working copy)
@@ -135,7 +135,7 @@

static int get_stmt(FILE * fd, dbString * stmt)
{
- char buf[4000], buf2[4000], buf3[7];
+ char buf[4000], buf2[4000];
     int len, row = 0;

     db_init_string(stmt);
@@ -145,11 +145,6 @@
        G_chop(buf2);
        len = strlen(buf2);

- G_strncpy(buf3, buf2, 6);
- if (G_strcasecmp(buf3, "select") == 0)
- G_fatal_error(_("Use db.select for SELECT SQL statements"));
-
- len = strlen(buf2);
        if (buf2[len - 1] == ';') { /* end of statement */
            buf2[len - 1] = 0; /* truncate ';' */
            db_append_string(stmt, buf2); /* append truncated */

If so, I suggest to do it also in GRASS 6.4.x.

Markus

6

Markus Neteler wrote:

>> I do agree. However, since the statement begins with the
>> "SELECT"-string, db.execute refuses to execute it.
>
> This is a bug in db.execute, and the only simple fix is to remove the
> check altogether.

As simple as this?

Yes.

--
Glynn Clements <glynn@gclements.plus.com>

7

On Sat, Jan 17, 2009 at 8:27 PM, Glynn Clements
<glynn@gclements.plus.com> wrote:

Markus Neteler wrote:

>> I do agree. However, since the statement begins with the
>> "SELECT"-string, db.execute refuses to execute it.
>
> This is a bug in db.execute, and the only simple fix is to remove the
> check altogether.

As simple as this?

Yes.

Done in 7.trunk, devel6 and 6.4.0svn.

Markus

8

Thanks for your work, Markus !

Peter
-------- Original-Nachricht --------

Datum: Sun, 18 Jan 2009 07:23:56 +0100
Von: Markus Neteler <neteler@osgeo.org>
An: Glynn Clements <glynn@gclements.plus.com>
CC: GRASS user list <grass-user@lists.osgeo.org>, "Peter Löwe" <peter.loewe@gmx.de>
Betreff: Re: [GRASS-user] db.select limitation + psql workaround

On Sat, Jan 17, 2009 at 8:27 PM, Glynn Clements
<glynn@gclements.plus.com> wrote:
> Markus Neteler wrote:
>
>> >> I do agree. However, since the statement begins with the
>> >> "SELECT"-string, db.execute refuses to execute it.
>> >
>> > This is a bug in db.execute, and the only simple fix is to remove the
>> > check altogether.
>>
>> As simple as this?
>
> Yes.

Done in 7.trunk, devel6 and 6.4.0svn.

Markus

--
Dr. Peter Löwe
<peter.loewe@gmx.de>

Sensationsangebot verlängert: GMX FreeDSL - Telefonanschluss + DSL
für nur 16,37 Euro/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K1308T4569a