I'm using a cursoradapter class to access my PostgreSQL database. I have seen that every time i open a cursor from my db in my PostgreSQL the value of connections increases even when i cloase my cursor. In complex procedures where i have to open a grest number of tables (in a scan operation). i reach the limit of max_connections in my PostgreSQL and i can't continue with my program. Is there a way i can close my connections where i have finisched to use it ?In POstgreSQL i have different ways to close idle connections, but if i use these commands my procedure goes in error for lost connection...

Thanks

How are you connecting? ODBC? OleDb? DE?

You need to manage the connection on your own - make sure you shut down the connection when you're done or else cache the connection and reuse it. If you're using Odbc and SQL Passthrough cache the handle and reuse it. Or SqlDisconnect() when you're done.

In some situations i use SQL Passthrough and for entire session i use the same handle to make my calls.. And in this cases i can Connect and disconnect every time i make a call... The problem is where i use CursorAdapter class. Here every time i instantiate the class and use it i set as datasource the same handle, but in pg_stat_activity table (in postgreSQL) i have a new connection for every query i make and the active connections is always increased.. my cursorAdapter class use ODBC and the connectionstring is similar to this !DATABASE!;DATABASE=!DATABASE!;SERVER=!SERVER!;PORT=!PORT!;UID=!USER!;PWD=!PASSWORD!;CA=d;A7=100;B0=255;B1=8190;BI=0;C2=;CX=1c305008b;A1=7.4;Description=projectName;ApplicationName=projectName;application_name=projectName;

Not sure - but if you pass the handle as the DataSource to the DataAdapter and use DataSourceType="ODBC" it shouldn't be creating new connections - it doesn't have the information to create a new connection.

I'm not ever using DataAdapters - way to weird of an API, but I think the data connection rules are the same. I suspect you have some other place where you're creating a connection and not closing it.

+++ Rick ---

What do you suggest to use to access data on a different db than foxpro native. SQL Passthrough? I use this last when i have to call some db functions, not to query data or to write them

Personally I use SQL Passthrough for everything - but that's a personal preference.

You should be able to use the DataAdpater as long as you make sure that you manage the SQL connection handle. Make sure you open the handle, set the DA.DataSource with it, then after you're done close the connection. There should be no reason that the DA opens other connections since it wouldn't have access to the connection string - it can only use your handle.

+++ Rick ---

I’ve faced similar issues with connection limits in PostgreSQL. One thing that really helped me was making sure to explicitly close each cursor after I’m done with it using cursor.close(). I also pay attention to closing the connection itself when I finish with my database operations. If you’re dealing with a lot of tables in your scans, using a context manager can really simplify things since it automatically closes connections and cursors for you. Plus, adjusting your connection pool settings might give you a bit more breathing room, so you don’t hit that max_connections limit as quickly.