java.sql.SQLException: Timed out trying to establish connection

[vksrinu]

I am trying to connect to SQL Server 2012 with flag MULTISUBNETFAILOVER=TRUE

I have created the DSN and it works fine for some time and next day i will get the below erorr. I have verified the DB server and its up and working fine and able to connect to it from management studio. Once I restart the server the issue get fixed. Does any one had any idea on why this issue is happening. I am using the sqljdbc4.jar for this connection.

The root cause was that: java.sql.SQLException: Timed out trying to establish connection

[Carl Von Stetten]

Which version (Standard, Enterprise, Developer) and release (9, 10, 11) of CF are you using, and on what OS?

Also, what is the entire JDBC connect string you are using?

-Carl V.

[vksrinu]

I am using CF9 on Linux OS and trying to connect to SQL Server 2012 Alwayson.

my DSN created with build in driver always gets time out in the next day. but the DSN which i have created with the below URL, works fine.

below is the JDBC URL i am using.

jdbc:sqlserver://DBSERVER1;user=user1;password=xxxx;database=reporting;MULTISUBNETFAILOVER=TRUE

[Priyank Shrivastava.]

Hi Srinivas,

ColdFusion 9 does not support SQL Server 2012, please check the support matrix, the CF 9 was tested and verified for SQL 2012 server.

https://wwwimages2.adobe.com/content/dam/Adobe/en/products/coldfusion/pdfs/coldfusion9-support-matri...

Thanks,

Priyank

[Carl Von Stetten]

I think Priyank meant to say that CF9 was tested and verified for SQL Server 2008 and only if you are using the Adobe-provided DataDirect drivers.  If you are using Microsoft's JDBC drivers, especially the SQL Server 2012 version, you are on your own - that is not a configuration that Adobe will provide you support for.

-Carl V.

[Priyank Shrivastava.]

Ohhh sorry it was a typo... Thanks Carl. Yes it is not supported with CF 9.

Regards,

Priyank

[vksrinu]

ohh..ok its fine..

I am able to connect to the SQL Server 2012 with the Microsoft drivers but I am getting extra spaces for the all the data which i retrieve data from DB, Do you know any idea why its happening or is there any way to fix it.

i have tried to write a query of quries to trim the data but QoQ is not working and throwing the error. 

The same issue is happening with the 2008 ver too.

"Error","jrpp-30","06/27/14","07:26:52","Quotes","Query Of Queries runtime error.Unsupported SQL type java.sql.Types.UNKNOWN. The specific sequence of files included or processed is: /opt/coldfusion9/apps/www/redirectindex.cfm, line: 38 "

coldfusion.sql.imq.ImqUnsupportedSQLTypeException: Query Of Queries runtime error.

  at coldfusion.sql.imq.rttExpr.SqlToJavaMapping(rttExpr.java:848)

  at coldfusion.sql.imq.rttExprColumnref.validate(rttExprColumnref.java:197)

  at coldfusion.sql.imq.ExprColumn.validate(ExprColumn.java:153)

  at coldfusion.sql.imq.SelectColumnList.validate(SelectColumnList.java:173)

  at coldfusion.sql.imq.rttSelectExprSpec.validate(rttSelectExprSpec.java:518)

  at coldfusion.sql.imq.rttSelectStmt.validate(rttSelectStmt.java:84)

  at coldfusion.sql.imq.jdbcStatement.fetchResult(jdbcStatement.java:538)

  at coldfusion.sql.imq.jdbcStatement.execute(jdbcStatement.java:131)

  at coldfusion.sql.Executive.executeQuery(Executive.java:1239)

  at coldfusion.sql.SqlImpl.execute(SqlImpl.java:345)

  at coldfusion.tagext.sql.QueryTag.executeQuery(QueryTag.java:843)

  at coldfusion.tagext.sql.QueryTag.doEndTag(QueryTag.java:533)

  at cfingres2ecfm573751271$funcTRIMQUERY.runFunction(/opt/coldfusion9/CustomTags/ingres.cfm:38)

  at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:472)

  at coldfusion.runtime.UDFMethod$ReturnTypeFilter.invoke(UDFMethod.java:405)

  at coldfusion.runtime.UDFMethod$ArgumentCollectionFilter.invoke(UDFMethod.java:368)

  at coldfusion.filter.FunctionAccessFilter.invoke(FunctionAccessFilter.java:55)

  at coldfusion.runtime.UDFMethod.runFilterChain(UDFMethod.java:321)

  at coldfusion.runtime.UDFMethod.invoke(UDFMethod.java:220)

  at coldfusion.runtime.CfJspPage._invokeUDF(CfJspPage.java:2582)

  at cfingres2ecfm573751271.runPage(/opt/coldfusion9/CustomTags/ingres.cfm:14)

  at coldfusion.runtime.CfJspPage.invoke(CfJspPage.java:231)

  at coldfusion.filter.CFVariablesScopeFilter.invoke(CFVariablesScopeFilter.java:56)

I have verified the datatype and i see that all the basic datatypes only.

[Carl Von Stetten]

What is the actual datatype of the fields that are getting extra spaces?  Are they char(x) or nchar(x)?  If so, that is to be expected as they are fixed-width fields (automatically space padded on the right side).

I don't think Query of Query (QoQ) supports the "trim" command, which might be why you get the error above.  If the fields are char(x) or nchar(x), then you can use the SQL trim command into your initial query to remove the whitespace.

-Carl V.

[vksrinu]

yes, the fields are char(x) and nchar(x) fields.

we have many files/modules which is difficult to put trim, so we are calling custom tag and trying to trim the data.

we are passing the query to customer and executing the query in custom tag with cfquery and trying to do trim by using the QoQ as it trims the data.

[Carl Von Stetten · Accepted solution]

Again, I don't believe QofQ supports the Trim function.  In fact, the only place Trim appears on the QofQ page in Adobe docs is to indicate it is a reserved word and shouldn't be used.

As far as I can see, you only have two choices here: modify your database queries to use TRIM before running your QofQ, or change the database colums from char(x) and nchar(x) to varchar(x) and nvarchar(x).  Unless this or other applications are relying on fixed character length fields, switching to varchar(x)/nvarchar(x) shouldn't result in any data loss or adverse side effects.

-Carl V.