ColdFusion Muse

Connect ColdFusion JDBC to Sybase SQL Anywhere 16

This is something that might not come up often, but every once in a while we have to connect to a Sybase database. This is a built in feature in the Enterprise version of ColdFusion. However, if you have the Standard version of ColdFusion you have to manually add the JDBC jar file and build the connection string by hand. This is easy to do once you have the correct information and correct format of the connection string. Finding that correct information was nearly impossible and required a lot of trial and error.

Here's the case we had to resolve at CF Webtools. One of our clients has been using ColdFusion and Sybase for ages. For the record this is Sybase SQL Anywhere 16. For those that are not aware SAP owns Sybase thus the official name is SAP SQL Anywhere 16. For the longest time they were using ODBC connectors and older versions of ColdFusion on older Windows servers. More recently they have upgraded to ColdFusion 11 on newer Windows servers and were still trying to make the connections to Sybase via ODBC. This is a large multi-tenant operation in which there are hundreds of databases on the Sybase servers. Yes, plural servers. There are two servers that are replicated and handle failover. This means the ColdFusion Datasource connection also needs to handle failover. With ODBC failover is handled by Microsoft ODBC settings. With JDBC we had to setup failover in the JDBC connection string.

To accomplish this on ColdFusion Standard edition we went with the jConnect JDBC driver (jconn4) provided by SAP. (hint: Google jconn4 to find the download.) I could go into all the issues we had finding correct documentation, but I'll focus on the end results of the research and testing. With ColdFusion you have the option to create a datasource of type "Other". This lets you manually create a JDBC datasource using your add in JDBC jar file. The first thing you need to do is download and install the JDBC jar file. In this case the file name is jconn4.jar. Place this file in the ColdFusion "lib" folder. Because you are using ColdFusion Standard the Lib folder you want is located at ./ColdFusion11/cfusion/lib/ then restart ColdFusion.

Now that you have the jconn4.jar file installed and ColdFusion is restarted ColdFusion can access and use the jar file for making a datasource connection. In Datasources Add a new "Other" datasource and fill out the form like this. I have the connection strings below for both single server and multi-server failover. Once you submit the datasource should verify as OK.

Single Server, no failover

JDBC URL = jdbc:sybase:Tds:10.0.0.10:2638/?ServiceName=myDatabase
Driver Class = com.sybase.jdbc4.jdbc.SybDriver
Driver Name = SybaseJDBC


Multi Server with failover:

JDBC URL = jdbc:sybase:Tds:10.0.0.10:2638,10.0.0.20:2638/?ServiceName=myDatabase
Driver Class = com.sybase.jdbc4.jdbc.SybDriver
Driver Name = SybaseJDBC

This example was with ColdFusion 11 Standard edition, but it will work with ColdFusion 10 and ColdFusion 2016. If you have the Enterprise edition of ColdFusion then you have Sybase drives included already and you don't have to do these steps.The datasource setup is then identical to any other datasource setup in ColdFusion

Prior to switching from ODBC to JDBC the server was experiencing performance issues and memory would slowly increase over time to eventually crash the server. Now with JDBC the server has been performing without interruptions for well over a month with stabile memory usage. This means they've gone from unpredictable downtime to 100% uptime by switching to JDBC drivers. ODBC served it's purpose back in it's day, but JDBC is proven more stable and reliable.

CF Webtools is here to fill your needs and solve your problems. If you have a perplexing issue with ColdFusion servers, code, connections, or if you need help upgrading your VM or patching your server (or anything else) our operations group is standing by 24/7 - give us a call at 402-408-3733, or send a note to operations at cfwebtools.com.

Comments



Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.