CF10 MS SQL JDBC connection

[Selfish_Elephant]

Hello there,

I can't make JDBC connection work with CF10 and MS SQL 2008 Express Edition. CF MS SQL Server driver works fine. I use this string to connect

jdbc:sqlserver://127.0.0.1/MyInstance:12345;databaseName=database-name, but getting error that server is not available. I tried MS JDBC driver v3 and v4, but with the same result.

May be to use MS SQL Server driver is fine, but I had some issues with it on CF8 and CF9, like it told that query was not exists. So, I had to use JDBC driver. May be it is fixed in CF10. We will see soon, if I do not solve problem above.

Thanks,

Anton

[carl type3]

SQL Express Edition do not enable TCP protocol by default.

Run "SQL Server Configuration Manager" > SQL Server Network Configuration >

Protocols for SQL (insert your SQL instance name) > TCP/IP > Enabled. SQL Server will need restarting to apply.

HTH, Carl.

[Selfish_Elephant]

Thanks Carl, but TCP/IP is on. And I can connect with CF MS SQL Server driver just fine. JDBC is the issue.

[carl type3]

Odd since CF > Data & Services > Data Sources > Microsoft SQL Server is a JDBC driver.
What happens if you pick "other" and use following example that matches your environment.

JDBC URL:
jdbc:macromedia:sqlserver://server_name:1433;databaseName=northwind

Driver Class:
macromedia.jdbc.MacromediaDriver

Regards, Carl.

[Selfish_Elephant]

Carl, I connected to DB successfully with parameters you provided. What is the difference between this and MS JDBC driver? Any idea why MS JDBC driver is not working?

[carl type3]

This is the syntax I have used before with installed JAR, MS JDBC driver:

jdbc:sqlserver://server_name:1433;databaseName=northwind

com.microsoft.sqlserver.jdbc.SQLServerDriver

The other diver (macromedia) is  built in CF.

Cheers, Carl.

[Selfish_Elephant]

I use the same syntax. I have found that instance name should be with backslach, not forward one. In this case I get timout error. May be it is a driver issue. I put sqljdbc4.jar into CF directory. Hopefully, current driver will work fine.

Thanks again, Carl.

[carl type3]

Glad to help.

sqljdbc4.jar in ColdFusion10\cfusion\lib & restart CF10 application should work.

[Selfish_Elephant]

Still cannot make CF10 and MS SQL JDBC driver to work together. CF9 was fine. Uninstalled it and installed CF10. Again CF MS SQL driver works fine, but JDBC driver, which worked fine with CF9, gives timeout. Cannot find any solution.

[carl type3 · Accepted solution]

CF10 with this (ie default + updated):

About ColdFusion »

System Information 
Server Product  ColdFusion 
Version  ColdFusion 10,282462 
Update Level  02 
Tomcat Version  7.0.23.0 
Update Level  /C:/ColdFusion10/cfusion/lib/updates/chf10000002.jar   
JVM Details 
Java Version  1.6.0_29   

MS SQL JDBC driver fails with:

Connection verification failed for data source: NAME
java.sql.SQLException: Timed out trying to establish connection
The root cause was that: java.sql.SQLException: Timed out trying to establish connection

CF10 change Java JVM in use:

About ColdFusion »

System Information 
Server Product  ColdFusion 
Version  ColdFusion 10,282462 
Update Level  02 
Tomcat Version  7.0.23.0 
Update Level  /C:/ColdFusion10/cfusion/lib/updates/chf10000002.jar   
JVM Details 
Java Version  1.7.0_03   

Data & Services > Data Sources > MS SQL JDBC says:

data source updated successfully (ie verifies OK).

Reader beware. Why did I use Java 7? I had that Java already installed and was easy for me to flick CF10 over and use that. Adobe CF does not support Java 7 yet so it may be more advisable to use Java 6 (1.6.0_35?) tho also keep in mind Adobe CF does not officially support beyond 1.6.0_29.

HTH, Carl.

[Selfish_Elephant]

Carl, it works with JRE v1.6.0_35. Thanks again for the great help.

[carl type3]

Re Java 1.6.0_35 I guess Adobe CF support that now:
http://helpx.adobe.com/coldfusion/kb/upgrading-java-coldfusion.html

Still do not see any official support for MS SQL 2008 r2 (or 2012).

Cheers, Carl.

[carl type3]

Anton also if you follow this thread:

http://forums.adobe.com/message/4705800#4705800

SQL 2012 is not officially supported.

[Selfish_Elephant]

Hi Carl,

I'm using MS SQL 2008 R2 Express. But isn't it driver's taks to maintain connection between CF and DB?

Anyway, I think, changing java version could help. I have the exact configuration and issue you described. I'll try v1.6.0_35 later today or tomorrow and v1.7, if no success with v1.6, and post here.

Thank you,

Anton