[mapserver-users] dynamic sql mapserver

[Fawcett, David]

Another debugging suggestion.  Eliminate the html page from your testing and just make a direct GET call to MapServer through a URL, passing your value in that way.  
e.g. 
http://localhost/cgi-bin/mapserv.exe?map=/ms4w/apps/myApp/myMap.map&mode=map&myDataVar=select+*+from+myTable  


I would change the var name from 'sql'.  I don't know if it is a reserved word, but it could be somewhere.  

Also, if your query is mostly static and the only part that changes is a value in the where clause, I would write most of the query out explicitly in the map file and only pass in that part that changes.  

David.


-----Original Message-----
From: mapserver-users-bounces at lists.osgeo.org [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of Pietro Giannini
Sent: Thursday, October 09, 2008 6:48 AM
To: mapserver-users at lists.osgeo.org
Subject: Re: [mapserver-users] dynamic sql mapserver


Hi Mark.

I try...

1st one: add "using SRID=4326" to the layer query;
2nd one: try to send - with some javascript - the entire query as variable, not only the subquery, i.e. :

LAYER
    ...
    DATA %sql%
    ...
END
on the mapfile and

<script language='javascript'>
    function sendquery(subquery){
        // code to complete the query string
        document.forms['mainform'].sql.value = "the_geom from (" + subquery + ") as myquery using unique gid using SRID=4326";
        document.forms['mainform'].submit();
    }
</script>
<form name='mainform' ... >
    <input type='hidden' name='sql' value='[sql]'>
    <textarea name= 'subquery' rows=3 cols=100></textarea>
    <input type=button value='send' onclick="javascript:sendquery(document.forms['mainquery'].subquery.value);"
</form>
on the html template.

let us know, ciao

....................................................pg




-- 
Pietro Giannini
Bytewise srl - Area GIS
41°50'38.58"N 12°29'13.39"E



On Gio, Ottobre 9, 2008 12:00, mark balman wrote:
> Hi all
>
> I am trying to figure out the way to create dynamic sql using 
> mapserver (using the basic demo application under MS4W) and 
> postgres/postgis. I have created various queries that work ok as
> follows:
>
> LAYER
>  CONNECTIONTYPE postgis
>  NAME "qdistance"
>  CONNECTION "user="" password="" dbname="test" host=localhost" DATA 
> "the_geom from (select * from kbapoly where 
> st_distance(the_geom,GeomFromText('Point(1 0)',4326)) <6) as myquery 
> using SRID=4326 using unique gid"  STATUS OFF
>  TYPE POLYGON
>  TEMPLATE "templates/kbas.html"
>   CLASS
>   COLOR 255 0 0
>
> What I would like to be able to do is pass user defined variables e.g. 
> latitude, longitude and distance somehow. I have read various 
> mapserver/postgis workshop docs by Paul Ramsey and others but cannot 
> get anything to work. I have defined the Layer thus:
>
> LAYER
>  CONNECTIONTYPE postgis
>  NAME "qdistance"
>  CONNECTION "user="" password="" dbname="test" host=localhost"  DATA 
> "the_geom from (%sql%) as myquery using unique gid" STATUS OFF  TYPE 
> POLYGON  TEMPLATE "templates/kbas.html"
>   CLASS
>   COLOR 255 0 0
>
> Within the basic.html page I have added the following:
>
> <p><b>Query: </b><br>
>         <textarea name="sql" rows=3 cols=100>[sql]</textarea>
>         <p>
>
> When I try adding the sql statement to the text box
>
> select * from kbapoly where st_distance(the_geom,GeomFromText('Point(1
> 0)',4326)) <6
>
> I keep getting the following message:
>
> msDrawMap(): Image handling error. Failed to draw layer named 
> 'qdistance'. prepare_database(): Query error. Error executing POSTGIS 
> DECLARE (the actual query) statement: 'DECLARE mycursor BINARY CURSOR 
> FOR SELECT 
> asbinary(force_collection(force_2d(the_geom)),'NDR'),gid::text
> from (%sql%) as myquery WHERE the_geom &&
> setSRID('BOX3D(-179.699499165275 -120.200333889816,180.300500834725
> 119.599332220367)'::BOX3D, find_srid('','(%sql%) as
> myquery','the_geom') )' Postgresql reports the error as 'ERROR: syntax
> error at or near "%" LINE 1:
> ...ection(force_2d(the_geom)),'NDR'),gid::text from (%sql%) as ... ^ '
> More Help: Error with POSTGIS data variable. You specified 'check your
> .map file'. Standard ways of specifiying are : (1) 'geometry_column
> from geometry_table' (2) 'geometry_column from (sub query) as foo
> using unique column name using SRID=srid#' Make sure you put in the
> 'using unique column name' and 'using SRID=#' clauses in. For more
> help, please see http://postgis.refractions.net/documentation/
> Mappostgis.c - version of Jan 23/2004.
>
> Can anyone provide me with some pointers?
>
> Many thanks
>
> Mark
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org 
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>


_______________________________________________
mapserver-users mailing list
mapserver-users at lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users