OGRVRT Layers not releasing DB Connections

[Frank Warmerdam]

On 8/6/05, IMD Listuser <imd_listuser at comcast.net> wrote:
> 
> 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:
...
> 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).

Charlie, 

Using the direct OGR VRT information in the connection string
should be fine.  It certainly looks like mapserver is properly asking
OGR to close the connection. 

I tried some experiments with an ODBC connection via OGR VRT
to an MS Access file and it seemed to be calling SQLDisconnect()
properly for each SQLConnect() request.  I'm really not sure what else
to do at my end to try and reproduce your situation. 

I did commit changes to OGR so that with CPL_DEBUG set ON it will
report each SQLConnect and SQLDisconnect call.   One approach
to debugging might be to update to the latest GDAL/OGR from CVS 
and run it in debug to see if SQLDisconnect is being properly called.
If it is, then I am pretty much at a loss as to why connections at the
level you are monitoring them would not be properly released.

> 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.

It is hard for me to provide useful reasons to move or not move.  There
are advantages to moving into postgis but other than "lightness of
connection", I'm not sure that mysql would have any advantages over
SQL Server. 

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