[mapserver-users] PostGIS problems in nightly

[Stephen Woodbridge]

Håvard,

Try:

trim(both from direction)

which does the same thing as:

trim(both ' ' from direction)

this looks like it might be an unescaped ' and in single quotes string.

you could also try:

trim(both \' \' from direction)

or

trim(both \\' \\' from direction)

and report if either of these work.

This would help narrow down the issue and maybe give you a work around.

-Steve W

Håvard Tveite wrote:
> I want to report a small problem I faced with a PostGIS
> connection with the latest nightly build.
> 
> With the latest build I have problems with one of my
> PostGIS connections.
> The problem goes away when I switch back to the nightly
> build from March 4th 2008.  Other PostGIS connections
> seem to work fine on both builds.
> 
> The mapserver_log reports:
> ******************************************************
> Fri May  9 17:08:42 2008,2747,128.39.236.206,okart,0,-19379.849812 
> 26500.000000 19379.849812 57500.000000,-1.000000 -1.000000,o-kartpoint 
> o-kartpolygon o-kartline ,msDrawMap(): Image handling error. Failed to 
> draw layer named 'o-kartpoint'. <br>
> prepare_database(): Query error. Error declaring cursor: ERROR:  current 
> transaction is aborted, commands ignored until end of transaction block
> 
> With query string: DECLARE mycursor BINARY CURSOR FOR SELECT 
> "symbol"::text,"direction"::text,asbinary(force_collection(force_2d(the_geom)),'XDR'),OID::text 
> from (select oid, symbol, trim(both ' ' from direction) as direction, 
> the_geom from okartpoint order by sequence) as okart WHERE the_geom && 
> setSRID('BOX3D(-19379.8498122653 26500,19379.8498122653 57500)'::BOX3D, 
> -1 )
>  <br>
> 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 = 
> '(select oid, symbol, trim(both ' ' from direction) as direction, 
> the_geom from okartpoint order by sequence) as okart' and 
> pg_table_is_visible(pg_class.oid) and pg_constraint.conkey[2] is null <br>
> ******************************************************
> 
> PostgreSQL log:
> ******************************************************
> 2008-05-09 17:08:42 MET DST okart guest 128.39.175.213ERROR:  syntax 
> error at or near "' from direction) as direction, the_geom from 
> okartpoint order by sequence) as okart'" at character 283
> ******************************************************
> 
> MapServer version 5.1-dev OUTPUT=GIF OUTPUT=PNG OUTPUT=JPEG OUTPUT=WBMP 
> OUTPUT=PDF OUTPUT=SVG SUPPORTS=PROJ SUPPORTS=AGG SUPPORTS=FREETYPE 
> SUPPORTS=ICONV SUPPORTS=WMS_SERVER SUPPORTS=WMS_CLIENT 
> SUPPORTS=WFS_SERVER SUPPORTS=WFS_CLIENT SUPPORTS=WCS_SERVER INPUT=EPPL7 
> INPUT=SDE INPUT=POSTGIS INPUT=OGR INPUT=GDAL INPUT=SHAPEFILE
> 
> PostgreSQL 8.1.5, POSTGIS="1.3.2" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 
> 4.5.0, 22 Oct 2006" USE_STATS
> I am on Solaris 2.7
> 
> If I remove the trim statement ("trim(both ' ' from direction)
> as direction"), and replace it with "direction" (untrimmed) there
> is no error situation in the latest nightly.
> 
> Is this a bug?
> 
> 
> Regards,
>