[soci-users] error in parse sql code

Post by Bruno Zerbo
hi,
I'm working with an app that makes some query to a postgres db.
I tried the following query with pgadmin and it works while thought the c++
code I have a runtime error.
using namespace soci;
Edge edge;
soci::session sql(postgresql, "hostaddr=127.0.0.1 user=postgres
password=aurali port=5432 dbname=routing");
sql << "SELECT gid, source, target, the_geom, name,ST_Distance(the_geom,
ST_GeometryFromText('POINT(15.1118 37.5667)', 4326)) AS dist FROM ways WHERE
the_geom && setsrid('BOX3D(15.0118 37.4667, 15.2118 37,6667)'::box3d, 4326)
ORDER BY dist LIMIT 1"
, into(edge.gid), into(edge.source),
into(edge.target), into(edge.the_geom),
into(edge.name);

I can confirm this is a valid PostgreSQL/PostGIS query.

Post by Bruno Zerbo
"Wt: fatal error: ERROR: syntax error at or near ""$1""
LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118 37,6667)'::$1, 4326) ...
the error is given in place of $1 that in the query is ::box3d. Why? there
was this traslation?

I haven't tested your query in action, but I suppose there may be conflict
between casting operators and how SOCI parses query to handle
placeholders bound by name:

http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name

Post by Bruno Zerbo
Maybe is the double colons that gives the problem? How
I can fix? How to pass a colon in the sql string?

Can you try the alternative [1], SQL conformant, syntax for the cast:

SetSRID(CAST('BOX3D(15.0118 37.4667, 15.2118 37,6667)' AS box3d), 4326)

[1] http://www.postgresql.org/docs/9.1/static/sql-expressions.html#SQL-SYNTAX-TYPE-CASTS

Best regards,

--
Mateusz Loskot, http://mateusz.loskot.net

Vadim Zeitlin

2013-04-06 13:31:53 UTC

On Sat, 6 Apr 2013 14:02:09 +0100 Mateusz Loskot <***@loskot.net> wrote:

ML> > the application gives my the following error:
ML> >
ML> > "Wt: fatal error: ERROR: syntax error at or near ""$1""
ML> > LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118 37,6667)'::$1, 4326) ...
ML> >
ML> > the error is given in place of $1 that in the query is ::box3d. Why? there
ML> > was this traslation?
ML>
ML> I haven't tested your query in action, but I suppose there may be conflict
ML> between casting operators and how SOCI parses query to handle
ML> placeholders bound by name:
ML>
ML> http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name

FWIW the code in PostgreSQL backend clearly tries to detect the cast
operators, there are even several cases for this there.

ML> > Maybe is the double colons that gives the problem? How
ML> > I can fix? How to pass a colon in the sql string?
ML>
ML> Can you try the alternative [1], SQL conformant, syntax for the cast:
ML>

Bruno Zerbo

2013-04-06 15:55:23 UTC

I tried with the escape beckslash \ (that is \:\:box3d) but the error was
the same.
Then I tried to change the cast syntax as suggested by Mateusz and the
query worked.
Could be a good idea to add an escape strategy like that one of the
backslash (\) for parsing the colons.
Do I add the feature request on github?

thanks for the replies,
Bruno

Post by Vadim Zeitlin
ML> >
ML> > "Wt: fatal error: ERROR: syntax error at or near ""$1""
ML> > LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118 37,6667)'::$1, 4326) ...
ML> >
ML> > the error is given in place of $1 that in the query is ::box3d. Why? there
ML> > was this traslation?
ML>
ML> I haven't tested your query in action, but I suppose there may be conflict
ML> between casting operators and how SOCI parses query to handle
ML>
ML> http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name
FWIW the code in PostgreSQL backend clearly tries to detect the cast
operators, there are even several cases for this there.
ML> > Maybe is the double colons that gives the problem? How
ML> > I can fix? How to pass a colon in the sql string?
ML>
ML>
ML> SetSRID(CAST('BOX3D(15.0118 37.4667, 15.2118 37,6667)' AS box3d), 4326)

Mateusz Loskot

2013-04-06 16:55:06 UTC

Post by Bruno Zerbo
I tried with the escape beckslash \ (that is \:\:box3d) but the error was
the same.
Then I tried to change the cast syntax as suggested by Mateusz and the query
worked.

Good. You've got workaround for the time being.

Post by Bruno Zerbo
Could be a good idea to add an escape strategy like that one of the
backslash (\) for parsing the colons.
Do I add the feature request on github?

As SOCI declares it supports PostgreSQL-style typecast,
I'd rather say, it's a bug.
So, yes, please open issue at GitHub.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

p***@mailme.dk

2013-04-08 04:19:24 UTC

Hi Bruno

What version of soci are you using ?

It looks a bit like the Postgres regression that went into 3.2.0

See commit c8ad2a884 for details. It should be fixed in 3.2.1.

Poul

Post by Bruno Zerbo
I tried with the escape beckslash \ (that is \:\:box3d) but the error was
the same.
Then I tried to change the cast syntax as suggested by Mateusz and the
query worked.
Could be a good idea to add an escape strategy like that one of the
backslash (\) for parsing the colons.
Do I add the feature request on github?
thanks for the replies,
Bruno

Post by Vadim Zeitlin
ML> >
ML> > "Wt: fatal error: ERROR: syntax error at or near ""$1""
ML> > LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118 37,6667)'::$1, 4326) ...
ML> >
ML> > the error is given in place of $1 that in the query is ::box3d.
Why?
there
ML> > was this traslation?
ML>
ML> I haven't tested your query in action, but I suppose there may be conflict
ML> between casting operators and how SOCI parses query to handle
ML>
ML> http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name
FWIW the code in PostgreSQL backend clearly tries to detect the cast
operators, there are even several cases for this there.
ML> > Maybe is the double colons that gives the problem? How
ML> > I can fix? How to pass a colon in the sql string?
ML>
ML>
ML> SetSRID(CAST('BOX3D(15.0118 37.4667, 15.2118 37,6667)' AS box3d), 4326)

Bruno Zerbo

2013-04-08 07:05:59 UTC

p***@mailme.dk

2013-04-08 08:16:55 UTC

Ok good, I take it you are on master branch. Could you please try the
hotfix/3.2.1 branch ?

Post by unknown
I use soci from git download of about 1 week ago

Post by p***@mailme.dk
Hi Bruno
What version of soci are you using ?
It looks a bit like the Postgres regression that went into 3.2.0
See commit c8ad2a884 for details. It should be fixed in 3.2.1.
Poul

Post by Bruno Zerbo
I tried with the escape beckslash \ (that is \:\:box3d) but the error

was

Post by Bruno Zerbo
the same.
Then I tried to change the cast syntax as suggested by Mateusz and the
query worked.
Could be a good idea to add an escape strategy like that one of the
backslash (\) for parsing the colons.
Do I add the feature request on github?
thanks for the replies,
Bruno

Post by Vadim Zeitlin
ML> >
ML> > "Wt: fatal error: ERROR: syntax error at or near ""$1""
ML> > LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118

37,6667)'::$1,

Post by Vadim Zeitlin
4326) ...
ML> >
ML> > the error is given in place of $1 that in the query is ::box3d.
Why?
there
ML> > was this traslation?
ML>
ML> I haven't tested your query in action, but I suppose there may be conflict
ML> between casting operators and how SOCI parses query to handle
ML>
ML> http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name
FWIW the code in PostgreSQL backend clearly tries to detect the cast
operators, there are even several cases for this there.
ML> > Maybe is the double colons that gives the problem? How
ML> > I can fix? How to pass a colon in the sql string?
ML>
ML>
ML> SetSRID(CAST('BOX3D(15.0118 37.4667, 15.2118 37,6667)' AS box3d), 4326)

Bruno Zerbo

2013-04-08 08:31:08 UTC

Bruno Zerbo

2013-04-10 19:16:09 UTC

Mateusz Loskot

2013-04-10 19:52:13 UTC

Post by unknown
Hi guys,
I tryed the hotfix/3.2.1 branch and the parse error relative of the cast
operator was fixed!
Thanks a lot.
I think that yuo can closs the bug report.

Thanks for the confirmation.
The report has been closed [1] and I'll release 3.2.1 this weekend
(I've had to postpone it a few days).

[1] https://github.com/SOCI/soci/pull/112

Best regards,

--
Mateusz Loskot, http://mateusz.loskot.net

Mateusz Loskot

2013-04-06 16:47:57 UTC

Yes, and we also have test for this PostgreSQL-style typecast:

https://github.com/SOCI/soci/blob/7941919fa632e525e127fde753cf9a0cb64545d3/src/backends/postgresql/test/test-postgresql.cpp#L427-L440

But, Bruno's query is more complex than what is tested,
so our parsing may be not reliable enough.

Best regards,

--
Mateusz Loskot, http://mateusz.loskot.net

unknown

1970-01-01 00:00:00 UTC

--e89a8f2356e1b1d2ed04d9d411ce
Content-Type: text/plain; charset=ISO-8859-1

I use soci from git download of about 1 week ago

Post by Vadim Zeitlin
ML> >
ML> > "Wt: fatal error: ERROR: syntax error at or near ""$1""
ML> > LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118 37,6667)'::$1,
4326) ...
ML> >
ML> > the error is given in place of $1 that in the query is ::box3d.
Why?
there
ML> > was this traslation?
ML>
ML> I haven't tested your query in action, but I suppose there may be
conflict
ML> between casting operators and how SOCI parses query to handle
ML>
ML> http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name
FWIW the code in PostgreSQL backend clearly tries to detect the cast
operators, there are even several cases for this there.
ML> > Maybe is the double colons that gives the problem? How
ML> > I can fix? How to pass a colon in the sql string?
ML>
ML> Can you try the alternative [1], SQL conformant, syntax for the
ML>
ML> SetSRID(CAST('BOX3D(15.0118 37.4667, 15.2118 37,6667)' AS box3d),
4326)

unknown

1970-01-01 00:00:00 UTC

--047d7b343aa8300cc104d9d5421f
Content-Type: text/plain; charset=ISO-8859-1

yes I'll try. But not today. Can you wait a couple of days?

Post by p***@mailme.dk
Ok good, I take it you are on master branch. Could you please try the
hotfix/3.2.1 branch ?

Post by unknown
I use soci from git download of about 1 week ago

Post by Bruno Zerbo
I tried with the escape beckslash \ (that is \:\:box3d) but the error

was

Post by Vadim Zeitlin
ML> >
ML> > "Wt: fatal error: ERROR: syntax error at or near ""$1""
ML> > LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118

37,6667)'::$1,

Post by Vadim Zeitlin
4326) ...
ML> >
ML> > the error is given in place of $1 that in the query is ::box3d.
Why?
there
ML> > was this traslation?
ML>
ML> I haven't tested your query in action, but I suppose there may be
conflict
ML> between casting operators and how SOCI parses query to handle
ML>
ML> http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name
FWIW the code in PostgreSQL backend clearly tries to detect the cast
operators, there are even several cases for this there.
ML> > Maybe is the double colons that gives the problem? How
ML> > I can fix? How to pass a colon in the sql string?
ML>
ML> Can you try the alternative [1], SQL conformant, syntax for the
ML>
ML> SetSRID(CAST('BOX3D(15.0118 37.4667, 15.2118 37,6667)' AS box3d),
4326)

unknown

1970-01-01 00:00:00 UTC

--f46d042ef7539fa04304da0680a9
Content-Type: text/plain; charset=ISO-8859-1

Hi guys,
I tryed the hotfix/3.2.1 branch and the parse error relative of the cast
operator was fixed!
Thanks a lot.
I think that yuo can closs the bug report.
Bye, Bruno

Post by unknown
yes I'll try. But not today. Can you wait a couple of days?
Ok good, I take it you are on master branch. Could you please try the

Post by p***@mailme.dk
hotfix/3.2.1 branch ?

Post by unknown
I use soci from git download of about 1 week ago

Post by Bruno Zerbo
I tried with the escape beckslash \ (that is \:\:box3d) but the error

was

Post by Bruno Zerbo
the same.
Then I tried to change the cast syntax as suggested by Mateusz and

the

Post by Bruno Zerbo
query worked.
Could be a good idea to add an escape strategy like that one of the
backslash (\) for parsing the colons.
Do I add the feature request on github?
thanks for the replies,
Bruno

Post by Vadim Zeitlin
On Sat, 6 Apr 2013 14:02:09 +0100 Mateusz Loskot <
ML> >
ML> > "Wt: fatal error: ERROR: syntax error at or near ""$1""
ML> > LINE 1: ...etsrid('BOX3D(15.0118 37.4667, 15.2118

37,6667)'::$1,

Post by Vadim Zeitlin
4326) ...
ML> >
ML> > the error is given in place of $1 that in the query is

::box3d.

Post by Vadim Zeitlin
Why?
there
ML> > was this traslation?
ML>
ML> I haven't tested your query in action, but I suppose there may

Post by Vadim Zeitlin
conflict
ML> between casting operators and how SOCI parses query to handle
ML>
ML> http://soci.sourceforge.net/doc/3.2.0/exchange.html#bind_name
FWIW the code in PostgreSQL backend clearly tries to detect the

cast

Post by Vadim Zeitlin
operators, there are even several cases for this there.
ML> > Maybe is the double colons that gives the problem? How
ML> > I can fix? How to pass a colon in the sql string?
ML>
ML> Can you try the alternative [1], SQL conformant, syntax for the
ML>
ML> SetSRID(CAST('BOX3D(15.0118 37.4667, 15.2118 37,6667)' AS

box3d),