Coldfusion 2023 and Sybase JDBC 3 datasource error

[tope316]

Hello, I am trying to setup a datasource using the Sybase JDBC 3 driver that we have been using since Coldfusion 9 but I am getting an error message: "Connection verification failed for data source
java.sql.SQLException: The method com.sybase.jdbc3.jdbc.SybDatabaseMetaData.getJDBCMajorVersion() has not been completed and should not be called."

Please help me on how to resolve this issue. Thanks!

[Charlie Arehart]

Tope, and while I don't use sybase and don't KNOW your answer, I suspect your error relates to the jdbc3 jar you're using and that you need a jdbc4 version.

1) First, are you a license key for cf, or or the free trial or developer edition of cf? In those, as well as when using a cf enterprise license, Adobe provides a licensed sybase jdbc connector for you. Otherwise, yes, you need to provide one.

So unless you're using a cf Standard license, try just using the sybase driver option offered when creating a cf datasource (if none is offered, that's further confirmation you're on cf Standard. The top of the cf admin window also reports what cf edition you're running.)

If you go that route, you can remove that older sybase jar you'd implemented.

2) Finally, if you ARE running cf Standard and MUST add a jdbc driver, the it seems you need a newer one. I've looked, but I and it doesn't seem that sap offers that freely. So first, it seems you should be able to get the jar from within a folder of the installed sybase db you're connecting to. I realize that maybe on another machine, and perhaps you can't even get the people responsible for that to find it for you.

In that case, another option is to download the free trial of sybase, just to get the jar. Again, the sap makes you jump through hoops to get that trial (as does Adobe for cf's trial), but give it a shot: https://developers.sap.com/trials-downloads.html?search=jdbc

Someday that link may not work, and perhaps this will: https://www.sap.com/products/technology-platform/sybase-ase/trial.html

And finally this link used to work but doesn't as I try today: http://www.sybase.com/products/allproductsa-z/softwaredeveloperkit/jconnect

Let us know if the simpler first solution above may suit you, or if you try the updated jar route. 

[Dave Watts]

I agree with @Charlie Arehart here. Get the latest driver that your database supports! You might be able to find an open-source version if you can't use Sybase's own driver. I seem to recall a jTDS version being available.

[tope316]

Hello @Charlie Arehart  and @Dave Watts , thanks for your replies.

We are using standard version and I was able to setup the JDBC 4 connector and was able to add the datasource in Coldfusion Admin.

However, when querying the tables there is an error message: 

Error Executing Database Query.

JZ0SJ: Metadata accessor information was not found on this database. Please install the required tables as mentioned in the jConnect documentation.

By the way we are using Adaptive Server Anywhere 9 as the database server. Thanks for all your help.

[Charlie Arehart]

If you left the old jar (the one you originally had), you need to remove it from that lib folder where you'd put it. You CANNOT just rename it and leave it in place. Then restart cf and try again.

Let us know how that goes. 

[tope316]

I did not rename it. I used jconnect7 (jconn4.jar) that works on SQL Anywhere 16+

So I followed your suggestion to download the free trial of sybase, just to get the jar. It now gives a different error message: "Connection verification failed for data source: 
java.sql.SQLException: JZ00L: Login failed. Examine the SQLWarnings chained to this exception for the reason(s)."

By the way, when I use this to connect to SQL Anywhere 17 it works. It just does not work on Adaptive Server Anywhere 9.

Does this mean we need to upgrade our database server from version 9 to 17?

[Charlie Arehart]

Wow, sybase 9: that's from 2003-2013. Fwiw, even cf2016 listed only sybase 16 as supported. Of course, that was also only in Enterprise.

Have people figured how to configure such an old sybase with a "newer" cf?  Perhaps. We're not hearing from them here, but Adobe won't help--again you're on cf2016, which has been out of support for 3 years itself.

Will upgrading sybase be necessary? I can't say. It seems wise, since it's out of support for 11 years.

I appreciate that updating the db and cf will be daunting (and may seem "expensive", though "avoiding that cost" is a false economy).

Sorry to not have better news for you. Seems you have some tough choices to consider. 

[BKBK]

By the way, when I use this to connect to SQL Anywhere 17 it works. It just does not work on Adaptive Server Anywhere 9.

Does this mean we need to upgrade our database server from version 9 to 17?

By @tope316

Good to hear that Anywhere 17 works. JConnect is indeed the correct JDBC driver to use, not the SQL Anywhere JDBC 3 or SQL Anywhere JDBC 4 driver. As JConnect is a pure-Java driver, chances are high that it will be compatible with different ColdFusion versions. After all, ColdFusion is itself a Java application.

Whereas, the SQL Anywhere JDBC drivers, not being pure-Java, will depend on the Operating System. For example, the SQL Anywhere JDBC 4.0 driver depends on the Windows native component dbjdbc12.dll.

So, stay with the JConnect driver, even for Anywhere 9. The error message,

 Connection verification failed for data source: java.sql.SQLException: JZ00L: Login failed.

suggests you're on the right track. Make sure you use the correct credentials for logging into the database.

[tope316]

Hello @BKBK,

Thank you for your reply.

I double checked and I am using the correct username and password. I do not know why this is giving me this error message.

[Dave Watts]

You probably need to find a JDBC 4 driver that your database supports. That may be a problem, since your database is so old. Of course, I barely know how to spell Sybase. With Microsoft SQL Server, there are two authentication modes: "native" SQL logins and Windows authentication. I doubt the same is true for Sybase, but you should look into the authentication options there.

I have a lot of trouble recommending you upgrade your database, especially in a rush to solve your immediate problem. Can you set all this up in a test environment first?

[BKBK]

Hi @tope316 , Besides the username and password, make sure the other values you enter in the ColdFusion Administrator datasource page are valid, including port and database.

[tope316]

Hi @BKBK and @Dave Watts, 

Below is the configuration in ASA 9 that works on Coldfusion 9 using JDBC 3.

This is the Coldfusion Admin 2023 datasource configuration using JDBC 4 that does not work.

[BKBK]

@tope316 , in my suggestions I assume you will use JConnect, not JDBC. I also assume you will have replaced the Jar files accordingly, then restarted ColdFusion.

[tope316]

@BKBK, yes I am using JConnect. I added this in the Coldfusion Class Path: D:\jConnect-16_0\classes\jconn42.jar and restarted Coldfusion.

FYI, jConnect is a JDBC driver.

[tope316 · Accepted solution]

Hello Everyone,

I managed to resolve this issue.

Error Executing Database Query.

JZ0SJ: Metadata accessor information was not found on this database. Please install the required tables as mentioned in the jConnect documentation.

So for anyone that encounters this error in the future, here are the steps I did:

Adaptive Server Anywhere 9 Configuration:

1.  Execute the file: [jConnect home directory]/jConnect-7_0/sp/sql_asa.sql using Interactive SQL.
2.  Restart the DB Service.

Coldfusion 2023 Administrator Data Source Configuration:

1.  Append this parameter in the JDBC URL: ";USE_METADATA=false"
2.  For example: jdbc:sybase:Tds:127.0.0.1:59200/cf-utax;USE_METADATA=false

[BKBK]

Hello @tope316 ,

I'm glad to hear that the issue is finally resolved.

Thanks for sharing the solution.

[Dave Watts]

Please mark your own answer as correct. Thanks!