[GRASS-user] Problem with executing some type od queries (with regular expresion parser)

GRASS GRASS user
1

Hi
Well, I thing I expect too much, but for now grass has been suprising me (positively) on cooperation GRASS - POstgreSQL

I have a query which works well in PgAdmin:

SELECT cat FROM streams_coor WHERE link::text IN(
   SELECT regexp_split_to_table(
        (SELECT branch FROM links WHERE keyid='168'), E'\\.'))

the branch looks like this:
"89.88.166.169.168"

and result are:

148
150
161
168
157

If I try to use this query in gis.m "use sql query" field with proper SQL syntax"

link::text IN(
    SELECT regexp_split_to_table((
        SELECT branch FROM links WHERE keyid='168'), E'\\.'))

do not receive any error but also any result

so where is problem?

As far as I know GRASS simply send query to database and recive list of cats as a result, so there could be the problem with parsing quotemarks and escape marks in regular expresion?

regards
Jarek

2

Hello Jarek,
please re-run Your gis.m with DEBUG=3
g.gisenv set=DEBUG=3

then redraw map and look into output pane for line:
D3/3: db_select_int()
D3/3: SQL: SELECT cat FROM ...
and post result here.
Ignore other error messages, that will popup.

Maris.

2008/10/29, Jarek Jasiewicz <jarekj@amu.edu.pl>:

Hi
Well, I thing I expect too much, but for now grass has been suprising me
(positively) on cooperation GRASS - POstgreSQL

I have a query which works well in PgAdmin:

SELECT cat FROM streams_coor WHERE link::text IN(
   SELECT regexp_split_to_table(
        (SELECT branch FROM links WHERE keyid='168'), E'\\.'))

the branch looks like this:
"89.88.166.169.168"

and result are:

148
150
161
168
157

If I try to use this query in gis.m "use sql query" field with proper
SQL syntax"

link::text IN(
    SELECT regexp_split_to_table((
        SELECT branch FROM links WHERE keyid='168'), E'\\.'))

do not receive any error but also any result

so where is problem?

As far as I know GRASS simply send query to database and recive list of
cats as a result, so there could be the problem with parsing quotemarks
and escape marks in regular expresion?

regards
Jarek
_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

3

As you wish:

D3/3: db_select_int()
D3/3: SQL: SELECT cat FROM streams_coor WHERE link::text IN(SELECT regexp_split_to_table((SELECT branch FROM links WHERE keyid='168'), E'\\.'))
D3/3: Escaped SQL: SELECT cat FROM streams_coor WHERE link::text IN(SELECT regexp_split_to_table((SELECT branch FROM links WHERE keyid='168'), E'\\\\.'))
D3/3: describe_table()

As we see, there is a problem with escape \ backslash
grass (I don't know gis.m or db_select_int()) adds backslash before.
In pgAdmin I must add escape character manually as in example in post before.

In gis.m this command should look like:

link::text IN(
    SELECT regexp_split_to_table((
        SELECT branch FROM links WHERE keyid='168'), E'\.'))

And everythik is OK. Porblem solved.

thank you very much for your hint. It was very helpful.

Jarek

Maris Nartiss pisze:

Hello Jarek,
please re-run Your gis.m with DEBUG=3
g.gisenv set=DEBUG=3

then redraw map and look into output pane for line:
D3/3: db_select_int()
D3/3: SQL: SELECT cat FROM ...
and post result here.
Ignore other error messages, that will popup.

Maris.

2008/10/29, Jarek Jasiewicz <jarekj@amu.edu.pl>:
  

Hi
Well, I thing I expect too much, but for now grass has been suprising me
(positively) on cooperation GRASS - POstgreSQL

I have a query which works well in PgAdmin:

SELECT cat FROM streams_coor WHERE link::text IN(
   SELECT regexp_split_to_table(
        (SELECT branch FROM links WHERE keyid='168'), E'\\.'))

the branch looks like this:
"89.88.166.169.168"

and result are:

148
150
161
168
157

If I try to use this query in gis.m "use sql query" field with proper
SQL syntax"

link::text IN(
    SELECT regexp_split_to_table((
        SELECT branch FROM links WHERE keyid='168'), E'\\.'))

do not receive any error but also any result

so where is problem?

As far as I know GRASS simply send query to database and recive list of
cats as a result, so there could be the problem with parsing quotemarks
and escape marks in regular expresion?

regards
Jarek
_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

4

Jarek Jasiewicz wrote:

D3/3: db_select_int()
D3/3: SQL: SELECT cat FROM streams_coor WHERE link::text IN(SELECT
regexp_split_to_table((SELECT branch FROM links WHERE keyid='168'), E'\\.'))
D3/3: Escaped SQL: SELECT cat FROM streams_coor WHERE link::text
IN(SELECT regexp_split_to_table((SELECT branch FROM links WHERE
keyid='168'), E'\\\\.'))
D3/3: describe_table()

As we see, there is a problem with escape \ backslash
grass (I don't know gis.m or db_select_int()) adds backslash before.
In pgAdmin I must add escape character manually as in example in post
before.

In gis.m this command should look like:

link::text IN(
    SELECT regexp_split_to_table((
        SELECT branch FROM links WHERE keyid='168'), E'\.'))

And everythik is OK. Porblem solved.

db/drivers/postgres/execute.c has the following:

    /* Postgres supports in addition to standard escape character ' (apostrophe) also \ (basckslash)
     * as this is not SQL standard, GRASS modules cannot work escape all \ in the text
     * because other drivers do not support this feature. For example, if a text contains
     * string \' GRASS modules escape ' by another ' and string passed to driver is \''
     * postgres takes \' as ' but second ' remains not escaped, result is error.
     * Because of this, all occurencies of \ in sql are escaped by \ */
    str = G_str_replace(db_get_string(sql), "\\", "\\\\");

[Note that "\\" is a string containing a single backslash, as C also
uses backslash as an escape character.]

IOW, the "pg" DBMI driver assumes that every backslash which it sees
is supposed to be a literal backslash, and converts it to a
double-backslash escape sequence to ensure that it is interpreted as
such.

So you can't use PostgreSQL's C-like escapes (\n etc) within SQL
syntax.

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