[postgis-users] reusing MapServer query-string

[Jan Hartmann]

> Sort answer, no.
> Longer answer, there are a bunch of PHP-specific enhancements which
> would make Mapscript/Database interactions alot more efficient. For
> example, being able to hand an existing PHP connection resource to
> mapscript when it creates a map object, so save the overhead of
> constanding re-initializing the connection. For Oracle in particular,
> which has a criminally large connection overhead, this would be quite
> important. However, all these changes require both some moderate
> jimmying with the mapserver source as well as with the PHP interface, so
> it is not a 2 hour hack. :/
> Paul

Hello Paul,

Yes, I understand the problem of using PHP resources outside PHP now. After
your answer, I experimented a bit with named cursors in PostgreSQL. The idea
was that although resources cannot be passed, text strings can. PostgreSQL
cursors can be named (with the server function SSI_open_cursor()), and these
names can be passed to other applications, as text strings. The programmer's
manual explicitly say: "returning the portal (=cursor) name to the
procedure's caller provides a way of returning a rowset result". I actually
managed to create a few server-procedures within the database which created
named cursors, and fetch results from them. So from PHP I can give commands
like "select from createcursor('cursorname','<sql-statement>')". A cursor
named 'cursorname' will be created in the database for the required SQL. It
can be found and used by other server procedures (with SSI_find_cursor()),
and the results are passed back to PHP.  However, a second select on the
same name won't find this cursor anymore. If this cursor could be saved
between selects, the fundamental problem of passing resources from and to
PHP within one and the same connection would be partially solved, without
the need to delve deeply into MapServer or PHP internals. I haven't found a
way to do that, so now I am stuck.

I am new to database server programming, so I don't know if this is just a
wild idea or if it makes sense to look further. Any suggestions would be
appreciated: I would really like to reuse PostGis/MapServer queries to
compute statistics (and probably a solution would be useful for finding ways
of keeping Oracle out of criminal behavior too:) ).

Jan


Jan Hartmann
Department of Geography
University of Amsterdam
jhart at frw.uva.nl