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

[tribule]

Hi,

This is a continuation of the thread: https://community.adobe.com/t5/coldfusion/ever-increasing-number-of-database-connections-with-jdbc-a...

For some reason I cannot post to that thread any longer, so am creating this one to carry on.

[tribule]

Yep, corrected. 

[BKBK]

@tribule :I tried JNDI earlier, which uses TomCat's DBCP connection pooling, but I have not tried JNDI with the latest changes to the open/close dbObject. 

You may indeed set up connection pooling by means of ColdFusion's Tomcat engine. However, I suspect that that is equivalent functionality to setting up a datasource in the ColdFusion Administrator.

I had assumed that since CF had a maximum connections and 'maintain connections' option, that is was doing some kind of connection cache/pool?

My guess is, Coldfusion in fact does, using Tomcat. You will find below a proof-of-concept which I lifted from the Tomcat site

https://tomcat.apache.org/tomcat-9.0-doc/jdbc-pool.html 

and converted to CFML. The question is whether/how we can programmatically integrate such connection pooling with your Java code. 

<cfscript>

datasource=createobject("java","org.apache.tomcat.jdbc.pool.DataSource").init();
p = createobject("java","org.apache.tomcat.jdbc.pool.PoolProperties").init();


p.setUrl("jdbc:mysql://localhost:3306/mysql");
p.setDriverClassName("com.mysql.jdbc.Driver");
p.setUsername("root");
p.setPassword("myMySQLPassword");
p.setJmxEnabled(true);
p.setTestWhileIdle(false);
p.setTestOnBorrow(true);
p.setValidationQuery("SELECT 1");
p.setTestOnReturn(false);
p.setValidationInterval(30000);
p.setTimeBetweenEvictionRunsMillis(30000);
p.setMaxActive(100);
p.setInitialSize(10);
p.setMaxWait(10000);
p.setRemoveAbandonedTimeout(60);
p.setMinEvictableIdleTimeMillis(30000);
p.setMinIdle(10);
p.setLogAbandoned(true);
p.setRemoveAbandoned(true);
p.setJdbcInterceptors("org.apache.tomcat.jdbc.pool.interceptor.ConnectionState;" &
                      "org.apache.tomcat.jdbc.pool.interceptor.StatementFinalizer");

datasource.setPoolProperties(p);


try {
	con = datasource.getConnection();
	st = con.createStatement();
	rs = st.executeQuery("select * from user");

 	cnt = 1;
	while (rs.next()) {
		writeoutput(cnt & ". Host:" & rs.getString("Host") &
			    " User:" & rs.getString("User") & " Authentication_string:" & 
    			    rs.getString("Authentication_string") & "<br>");
		cnt++;
	}
	rs.close();
	st.close();
} finally {
	if (!isNull(con)) {
		con.close();
	}
}
</cfscript>

[tribule]

Yes, using DBCP I recall.

[tribule]

Thanks for the code. I could, conceivably, change all code that has a SQL/database open to use your new code. That would be a lot of work my side, but it's another approach. If I was to do that, where would I put the preamble for the createobject() and all of its attributes? Would I put that once at the very top of the page that needed a database connection somewhere? I don't want to repeat that code if I can help it.

[BKBK]

Yes, you could indeed replace your current database code with the Tomcat JDBC Pool code. The replacement will only be necessary up to and including the code that establishes a database connection.

The datasource/p Tomcat objects defined above will then replace the AcbDatabaseObject we've been talking about, that is, token.getcnnSG(). You're right in avoiding code repetition. Especially here, as we are wary of excessive connections.

[tribule]

The JNDI settings already in context.xml so do I actually need the preamble at all? Don't I just need to start with:

datasource=createobject("java","org.apache.tomcat.jdbc.pool.DataSource").init();

and then just:

con = datasource.getConnection();
st = con.createStatement();
rs = st.executeQuery("select * from user");

Advice appreciated.

[BKBK]

Yes, the JNDI settings in /cfusion/runtime/conf/context.xml and /WEB-INF/web.xml amount to the property ("p") settings in the above code. However, I would then create a new datasource, of type JNDI, in the ColdFusion Administrator and use the new datasource-name throughout the application.

[tribule]

Ok thanks.

[tribule]

Ok quick update, I have modified all of the templates on our system with the new code. Connections continue to rise (as reported in master.dbo.sysprocesses). I will watch to see how far they rise.

[tribule]

Update: we are at nearly 6000 connections and the server has been up nearly 2 hours.

[BKBK]

Hi @tribule 

I wished to respond to your last post, but couldn't find it. Did you delete it?

In any case, I have the e-mail. 

> ...we have two lots of code. One if the cfscript variant which uses the 3rd party

> Java API, and then we have normal CFQUERY tags in other places.

You're on CF2018, so this is not a problem. There is, in recent ColdFusion versions, a script equivalent to practically every ColdFusion tag. For example, queryExecute().

> In the cfscript code, above, the data source is not actually referred to by name,
> so how does it know which data source it is to use?

If you register the datasource via JNDI and via the ColdFusion Administrator, then the name will be that of the datasource in the Administrator. If you register the datasource via JNDI but create the datasource using code, then the name will be the one that you assign in the code. 

Suppose that the datasource name in your JNDI resource element is defined by

<Resource name="jdbc/myMSSQL"...>

Then, in the ColdFusion Administrator or in the Java code, you will define the datasource name as 

java:comp/env/jdbc/myMSSQL

See https://tomcat.apache.org/tomcat-8.0-doc/jndi-datasource-examples-howto.html 

> When you say "use the new datasource-name throughout the application"

> do you mean via CFQUERY?

Yes. Or via the cfscript alternative, queryExecute

[tribule]

Thanks for the clarification, I'll may come back to this approach if all else fails.

[tribule]

Hi again, quick question. If I use the in-line queryExecute() with my JNDI datasource embedded, I take it that it does all of the open/close for the connection as if I had used the longer code version? Will that handle the exceptions too, or do I need to code that in extra? I just want to make sure that any queries I use are properly coded to take account of problem SQL queries and that connections cannot be left in an open state afterwards etc.

[BKBK]

If you register the datasource as JNDI type in the ColdFusion Administrator, then use <cfquery> or, equivalently, queryExecute(), ColdFusion will handle the open/maintain/reuse/close connection operations. Alternatively, you can roll out your own connection code. You would then use something similar to the try/catch/finally section in the code I gave earlier.

[BKBK]

A packet size of 8000 bytes is fine.