[Cartoweb-users] recentering syntax-error?

[bram2006]

I tried to resolve the problem by modifying the demoPlugins-project step 
by step. I did not change any code. Below is a part of the mapfile in 
which I changed the airport layer from a shape into a postgistable. This 
postgislayer can be displayed correctly, the  search on aeroportnames 
works fine, but when recentering there is a mapservererror. Mapserver 
takes the 'id_attribute_string' in the WHERE-clause but the syntax isn't 
correct. I could'nt figure out where and how mapserver is told to take 
this value. Has anyone succeeded in using a postgislayer together with 
the demoLocation-plugin? When I use the aerofacp.shp everything works fine.

I also turned magic-quotes on in php.ini, but no result. 

Bram

  LAYER
    NAME "airport"
    TYPE POINT
 #  DATA "aerofacp"
    TEMPLATE "ttt"
    CONNECTIONTYPE POSTGIS
    CONNECTION "dbname=demo_plugins host=localhost user=www-data 
password=1234 port=5432"
    DATA "the_geom from airport as foo using unique ID using SRID=-1"
    METADATA
      "exported_values" "recenter_name_string,id_attribute_string"
      "recenter_name_string" "NAM"
      "id_attribute_string" "OGC_FID|string"
      "query_returned_attributes" "NAM"
    END

errorreport:

Class : SoapFaultWrapper
Message : Mapserver error: Error in msDrawMap(): Failed to draw layer named 'airport'.
Error in prepare_database(): Error executing POSTGIS DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT OGC_FID::text,NAM::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),ID::text from airport as foo WHERE (OGC_FID = 'OGC_FID in ('42')') and (the_geom && setSRID( 'BOX3D(881945.695862477 5122071.74000373,1040695.61013752 5233196.67999627)'::BOX3D,-1) )' 
Postgresql reports the error as 'ERROR:  syntax error at or near "42" at character 180
'

....

Backtrace:

file: 222 - C:\wamp06\www\cartoweb3\coreplugins\images\server\ServerImages.php
call: ServerContext->checkMsErrors()

file: 305 - C:\wamp06\www\cartoweb3\server\Cartoserver.php
call: ServerImages->drawMainmap(Object(Images))

file: 337 - C:\wamp06\www\cartoweb3\server\Cartoserver.php
call: Cartoserver->doGetMap(Object(MapRequest))

Alexandre Saunier schreef:

> Hello,
>
> well it seems that Postgres does not appreciate the non-escaped quotes 
> in "name = 'name in ('Belgium')'".
> I assume that "Belgium" is the recentering query string or something?
>
> I don't know where this part of the SQL query is built but try to 
> addslash() the incoming querystring in your plugin. There's a built-in 
> static method available in CW for that: Utils::addslashes() => it adds 
> slashes in front of quotes if it as detected that PHP magic_quotes are 
> off.
>
> Anyway your WHERE clause is kinda weird, isn't it? There's probably 
> too many "name" in it...
>
> AS
>
> bram wrote:
>
>> Hi list,
>>
>> I used the demoLocation plugin from the demoPlugins-project in 
>> myproject, which  goes fine. But  when  I choose  an item to recenter 
>> on  a syntax error occurs.  I  can't imagine there still is a bug 
>> somewhere in a script so obviously I'm making a mistake somewhere. I 
>> tried other id_attributes_string & string/value combinations. Who 
>> knows what's going wrong?
>>
>> Part of the mapfile:
>>
>>  CONNECTIONTYPE POSTGIS
>>  CONNECTION "dbname=edit_db host=localhost user=www-data 
>> password=a123 port=5432"
>>  DATA "the_geom from (select the_geom, area, gid, name, oid from 
>> countries where name < 'C') as foo2 using unique oid using SRID=-1"
>>  METADATA
>>        "exported_values" "recenter_name_string,id_attribute_string"
>>        "recenter_name_string" "name"
>>        "id_attribute_string" "name|string"
>>        "query_returned_attributes" "name area"
>>
>>
>> Errorreport:
>>
>> Error in prepare_database(): Error executing POSTGIS DECLARE (the 
>> actual query) statement: 'DECLARE mycursor BINARY CURSOR FOR SELECT 
>> name::text,asbinary(force_collection(force_2d(the_geom)),'NDR'),oid::text 
>> from (select the_geom, area, gid, name, oid from countries where name 
>> < 'C') as foo2 WHERE (name = 'name in ('Belgium')') and (the_geom && 
>> setSRID( 'BOX3D(-2843979.38207143 -1361319.536,2888428.68507143 
>> 2651366.111)'::BOX3D,-1) )' Postgresql reports the error as 'ERROR:  
>> syntax error at or near "Belgium" at character 227
>> '
>>
>> Thanks for help!
>>
>> Bram
>>  
>> _______________________________________________
>> Cartoweb-users mailing list
>> Cartoweb-users at lists.maptools.org
>> http://lists.maptools.org/mailman/listinfo/cartoweb-users
>
>