• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
1

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

Enthusiast ,
Jan 20, 2021 Jan 20, 2021

Copy link to clipboard

Copied

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.

 

Views

908

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 26, 2021 Jan 26, 2021

Copy link to clipboard

Copied

Yep, corrected. 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 26, 2021 Jan 26, 2021

Copy link to clipboard

Copied

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

 

 

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 26, 2021 Jan 26, 2021

Copy link to clipboard

Copied

Yes, using DBCP I recall.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Feb 03, 2021 Feb 03, 2021

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 04, 2021 Feb 04, 2021

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Feb 05, 2021 Feb 05, 2021

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 07, 2021 Feb 07, 2021

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Feb 08, 2021 Feb 08, 2021

Copy link to clipboard

Copied

Ok thanks.

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 25, 2021 Jan 25, 2021

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Jan 25, 2021 Jan 25, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 08, 2021 Feb 08, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Feb 08, 2021 Feb 08, 2021

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Feb 09, 2021 Feb 09, 2021

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Feb 09, 2021 Feb 09, 2021

Copy link to clipboard

Copied

LATEST

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Jan 21, 2021 Jan 21, 2021

Copy link to clipboard

Copied

A packet size of 8000 bytes is fine.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation