[mapserver-users] [mapserver] Java - PostGIS - Quoting issue in SQL request

[julien]

Hello,

I am developing an interface for a PostreSQL/PostGIS (v8.2) database 
using Mapserver (v 5.2.0) and Java (v6.0).
I obtained some nice maps using a basic SQL query, inserted into the 
mapfile.

But as soon as I tried to _use some simple quote *'* or *\'*_, the 
server cannot draw the layer and report the following error :

java.lang.UnknownError: msPOSTGISLayerRetrievePK(): Query error. Error executing POSTGIS statement (msPOSTGISLayerRetrievePK():select attname from pg_attribute, pg_constraint, pg_class where pg_constraint.conrelid = pg_class.oid and pg_class.oid = pg_attribute.attrelid and pg_constraint.contype = 'p' and pg_constraint.conkey[1] = pg_attribute.attnum and pg_class.relname = '/*REQUEST REMOVED*/' and pg_table_is_visible(pg_class.oid) and pg_constraint.conkey[2] is null
	edu.umn.gis.mapscript.mapscriptJNI.layerObj_draw(Native Method)
	edu.umn.gis.mapscript.layerObj.draw(layerObj.java:472)
	fr.ird.remige.bean.MapFileBean$layerItemClass.draw(MapFileBean.java:617)
	fr.ird.remige.bean.MapFileBean.draw(MapFileBean.java:1105)


REQUEST REMOVED was something like :

g_carre5 from (select distinct g_carre5 from carre 
inner join date using (id_date)
inner join country using (id_country) 
WHERE v_date BETWEEN 2000 AND 2001 AND _*l_country='France'*_
group by g_carre5) as foo

The previous request, which was working, was :

g_carre5 from (select distinct g_carre5 from carre 
inner join date using (id_date)
inner join country using (id_country) 
WHERE v_date BETWEEN 2000 AND 2001 AND _*v_country=1*_
group by g_carre5) as foo

_
Table country :_
id_country serial primary key
v_country numeric(4)
l_country varchar(12)

And first line is id_country = 1, v_country = 1, l_country = France.

One strange thing is that, when I am using a mapfile script on my 
computer (not on the server) and transform it into an image file with 
*shp2img* (shell command), both requests are working. Moreover, both 
requests are also working with SQL command, when logged into the database.

In Java, the string containing the request is : "g_carre5 from (select 
distinct g_carre5 from carre inner join date using (id_date) inner join 
country using (id_country) WHERE v_date BETWEEN 2000 AND 2001 AND 
l_country=\'France\' group by g_carre5) as foo".

I also tried to replace *\' *by *\"*, and the expected error /*column 
"France" does not exist*/ was reported, which shows that Libmapscript.so 
translated *\"* correctly.

I do not understand why there is this quoting issue. Does somebody have 
ever dealt with something like that?

Julien

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080826/e9d435ac/attachment.html