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

[julien]

Hello Karim,

As I reported in my first mail, it is the first pair of quotes like in 
*'*France*'* which induce the error.
I tried *''*France*''*, with double single quote (*'*), but it is also 
not working.

Thanks anyway for the help.

Julien

alim karim wrote:
> Perhaps you can try escaping your quotes like so:
>
> 'person's name' => 'person''s name'
>
> ak
>
> On Tue, Aug 26, 2008 at 8:16 AM, julien <julien.trolet at ifremer.fr 
> <mailto:julien.trolet at ifremer.fr>> wrote:
>
>     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
>
>
>     _______________________________________________
>     mapserver-users mailing list
>     mapserver-users at lists.osgeo.org
>     <mailto:mapserver-users at lists.osgeo.org>
>     http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
>

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20080827/4b7321ef/attachment.htm>