OGRVRT Layers not releasing DB Connections

[IMD Listuser]

Hi,

Thanks for the reply.

Im not using an ovf file. Rather, Ive included all of the parameters of
the OVF file in the CONNECTION string within the .map file so that I use
variable substitution (in a <SrcSQL/> element) with a parameter that is
passed in on the request. Here is the layer from the .map file:

  LAYER
    NAME pstn
    TYPE POINT
    TEMPLATE "VTemplate.html"
    CONNECTIONTYPE OGR
    CONNECTION "<OGRVRTDataSource><OGRVRTLayer
name='pstn'><SrcDataSource>ODBC:user/pwd at MyODBCDSN</SrcDataSource><SrcSQ
L>select top 1 * from LogTable where vid = '%vid%' order by LogTable.id
desc</SrcSQL><GeometryType>wkbPoint</GeometryType><LayerSRS>WGS84</Layer
SRS><GeometryField encoding='PointFromColumns' x='longitude_dd'
y='latitude_dd'/></OGRVRTLayer></OGRVRTDataSource>"
    STATUS DEFAULT
    CLASS
      TEXT([label])
      COLOR 255 255 0
      OUTLINECOLOR 0 0 0
      SIZE 10
      LABEL
       FORCE true
       POSITION AUTO
       TYPE TRUETYPE
       FONT Arial
       SIZE 8
       OFFSET 0 5
       COLOR 255 255 255
       OUTLINECOLOR 0 0 0
       WRAP ","
      END
    END
  END

This is one of 3 layers that use ODBC connections to a SQL Server DB.
Each request for a map thus makes 3 distinct connections to the DB to
get information to place on the map image. When I monitor the number of
connections, it increments by 3 after each request, so it seems to me
that the connections are not being released since after a period of time
I start to receive errors that other processes cannot connect to the
database. A restart is necessary to restore connectivity.

I did turn debugging for this layer and I did note a call to
msOGRLayerClose(), but as you can see it seems to include all of the
CONNECTION string. Is this expected? 

msOGRFileOpen(ODBC:user/pwd at MyODBCDSNselect top 1 * from LogTable where
vid = '020fe190-4f2c-48e4-a3a5-8bf38631f308' order by LogTable.id
descwkbPointWGS84)... OGROPen(ODBC:user/pwd at MyODBCDSNselect top 1 * from
LogTable where vid = '020fe190-4f2c-48e4-a3a5-8bf38631f308' order by
LogTable.id descwkbPointWGS84)
msOGRLayerClose(ODBC:user/pwd at MyODBCDSNselect top 1 * from LogTable
where vid = '020fe190-4f2c-48e4-a3a5-8bf38631f308' order by LogTable.id
descwkbPointWGS84). msOGRFileClose(ODBC:user/pwd at MyODBCDSNselect top 1 *
from LogTable where vid = '020fe190-4f2c-48e4-a3a5-8bf38631f308' order
by LogTable.id descwkbPointWGS84,0). 

This is a tracking application that is just using the CGI verion of
Mapserver at present. I would like to be able to use SQL server, but I
might be tempted to move to MySQL or PostGIS if it were clearly the
right thing to do, esp as regards displaying the dynamic spatial data
and its attributes on the map image. 

I eagerly await your response.

charlie

Charlie Van Dusen
charlie at im-design.net


-----Original Message-----
From: UMN MapServer Users List [mailto:MAPSERVER-USERS at LISTS.UMN.EDU] On
Behalf Of Frank Warmerdam
Sent: Friday, August 05, 2005 1:23 PM
To: MAPSERVER-USERS at LISTS.UMN.EDU
Subject: Re: [UMN_MAPSERVER-USERS] OGRVRT Layers not releasing DB
Connections

On 8/5/05, IMD Listuser <imd_listuser at comcast.net> wrote:
> Greetings,
> 
> Im using Mapserver 4.6 from hobu and have just noticed that I am 
> running out of database connections to my SQL Server database. Ive 
> looked at the docs specifying how to use the PROCESSING 
> "CLOSE_CONNECTION=DEFER" or PROCESSING "CLOSE_CONNECTION=NORMAL" items

> in the map file, but this does not seem to make a big difference in my
case.
> 
> Could the list offer any further details on how to be sure that my 
> OGRVRT/ODBC connection to SQL Server is colsing its connection when 
> completed?

Charlie, 

I assume you are using a VRT file referencing SQL Server via ODBC?  Just
with normal CGI mapserv?  

If you run mapserv (or shp2img) in debug mode, you should see messages
like the following when the VRT file is closed within MapServer:

  msOGRLayerClose(...).

With shp2img I can see these messages if I do "shp2img -all_debug 1
...". 

To be really sure that the low level ODBC connection is being shutdown
properly we would need to add some debuging output in the
CloseConnection() method in gdal/port/cpl_odbc.cpp.  

If the ODBCSession is being properly shutdown then things are out of our
control.  If not, then there is a bug in the cleanup at some level.

I would add that OGR's processing of the CLOSE_CONNECTION is different
and distinct from the RDBMS derivers.  I would suggest you avoid using
the processing directive unless you find you have a specific need.

Best regards,
-- 
---------------------------------------+--------------------------------
---------------------------------------+------
I set the clouds in motion - turn up   | Frank Warmerdam,
warmerdam at pobox.com
light and sound - activate the windows | http://pobox.com/~warmerdam
and watch the world go round - Rush    | Geospatial Programmer for Rent