Continuation of "Ever increasing number of database connections with JDBC and ODBC drivers - MSSQL"

Recap:

@tribule , In our e-mail correspondence, I suggested the following, as a possible fix: replace multiple instantiations of the database object with just one. For example, instead of

    x1=objToken.getcnnSG().dbFunction1();
    x2=objToken.getcnnSG().dbFunction2();
    objToken.getcnnSG().dbFunction3("some sql string arg");

instantiate the database object just once, using

// the single instantiation
dbObject=objToken.getcnnSG();

This guarantees that every function will be called on the same object. Thus, following dbObject.query(), the call dbOject.ReleaseConnection() will release the connection that dbObject used.

// use the instance in subsequent calls

x1=dbObject.dbFunction1();
x2=dbObject.dbFunction2();
dbObject.dbFunction3("some sql string arg");

// also use the instance to interact with the connection
dbObject.reset()
/* Alternatively: dbObject.ReleaseConnection(); */
dbObject.setConnectString("max pool size=100");

Follow-up:

1) Use var for every variable defined in your custom functions. This ensures that resources will be released, closed or garbage-collected.

2) When the database object has finished its job (querying, interacting, releasing the connection, etc.), then kill the object itself. That is, set it to null. To do so, use, for example:

dbObject= javacast("null", 0);

The association between the database object and its constituent Statements, Connections, DSN, etc is one of composition. Therefore, when the database object becomes null, so, too, will each of its constituents.

Hi. To give context to others, from what you saw, the code in the Java database class was properly opening and closing connections, however the cfscript code that used that class may have been incorrectly coded in so far as it was not apparant if the calls to the database (to execute SQL) were properly opening and closing just one connection at a time. @BKBK  if you want to explain that better, please do 🙂

I have now made modifications to a lot of templates that had database calls, and am testing to see if the system behaves any better.  Just to reiterate, we are using this MSSQL query to check how many connections to the server are occuring. This is the query which previously has yielded more and more results - it never frees up any rows:

SELECT *
FROM master.dbo.sysprocesses(NOLOCK) 
WHERE (spid > 50) AND (status = 'sleeping') AND (program_name LIKE '%jdbc%')

I am not sure if this query should yield a larger and larger result set, or if it should get smaller (automatic data purge by MSSQL) at some point? If someone knows that, I'd appreciate your input.

I will update again soon after watching the server. I do hope, as @BKBK says, that the code may be the problem, but so far this remains unknown

Regards,

Mark