Oracle SQL Developer has the functionality to not only Oracle databases. For MS SQL and SAP ASE (Sybase ASE) connections it uses the jTDS JDBC driver. Unfortunately the program refuses to work with ASE 16.0 even though the database is very similar to ASE 15.7. This article describes how to force the SQL Developer to connect to ASE 16.0.
TL;DR: Take me straight to the downloads and give me a working jTDS library.
1. How Oracle SQL Developer sees SAP ASE 16.0:
When you connect from SQL Developer to ASE databases everything seems OK when the database is in version 15.0 or 15.7 (and may be others as well). Unfortunately connecting to ASE 16.0 causes unexpected results to appear:
I have tested many versions of ASE 16.0 and the problem appears in all of them.
The results above come from currently available Oracle SQL Developer – version 17.2.0.188 (build 188.1159). So, we are testing the newest available today SQL Developer.
The cause of the results above is incomprehensible for me.
I would understand if Oracle said that ASE 16.0 is not supported and showed an error message. But it’s not the case. The first available public version of ASE 16.0 appeared in March 2014. Today, more than 3 years later, SQL Developer is still not able to connect to the database.
Does the fault lay in SQL Developer? Or maybe in the jTDS driver?
2. Analysis
After turning tracing on the database it appeared, that the client (of the JDBC driver) is calling different SQL queris when SQL Devloper is connected to ASE 15.7 than when it is connected to ASE 16.0. Taking those statements outside and calling the SQL code in command line client (isql) showed no difference in the results… almost no difference.
The only difference is the database version returned by the driver to the client.
Let’s find out what happens when jTDS would tell SQL Developer that it is connected to ASE version some version of 15, lets say… 15.90 and not 16.0. I have chosen the version 15.90, as it would be a newer version than 15.70. And it looks nice too. Let’s change the source code of the jTDS driver in the following methods of class JtdsDatabaseMetaData:
Method getDatabaseProductVersion:
1 2 3 4 5 6 7 8 9 10 11 12 13 | /** * Returns the version of this database product. * * @return database version * @throws SQLException if a database-access error occurs. */ public String getDatabaseProductVersion() throws SQLException { if (serverType == Driver.SYBASE && connection.getDatabaseMajorVersion() == 16) { System.out.println("getDatabaseProductVersion: 15.90"); return "15.90"; } return connection.getDatabaseProductVersion(); } |
Method getDatabaseMajorVersion:
1 2 3 4 5 6 7 8 9 10 11 12 13 | /** * Returns the version of this database product. * * @return database version * @throws SQLException if a database-access error occurs. */ public String getDatabaseProductVersion() throws SQLException { if (serverType == Driver.SYBASE && connection.getDatabaseMajorVersion() == 16) { System.out.println("getDatabaseProductVersion: 15.90"); return "15.90"; } return connection.getDatabaseProductVersion(); } |
Method getDatabaseMinorVersion:
1 2 3 4 5 6 7 8 9 10 11 | /** * Returns the database minor version. */ public int getDatabaseMinorVersion() throws SQLException { if (serverType == Driver.SYBASE && connection.getDatabaseMajorVersion() == 16) { System.out.println("getDatabaseMinorVersion: 90"); return 90; } return connection.getDatabaseMinorVersion(); } |
Looking at the modified versions of those methods it is obvious what I have changed in the code.
3. Results
What was a surprise for me the SQL Developer now sees the metadata correctly when it is connected to ASE 16.0:
Expanding the object list also seems to work:
Actually that’s all. Nothing more is to be done here.
Does it work? It seems not to explode the first minute you try to use it. If you have patience, please feel to test it and send me some feedback.
4. Source code
The latest version of jTDS is currently 1.3.1. For the last 4 years (since 2013) there was no update in the source repository. Actually the last stable build was released before ASE 16.0 GA was available for download.
I don’t want to modify the whole source code of the library since the patch is only needed for Oracle SQL Developer when connecting to ASE 16.0. If you want to use the patch – download it from GitHub and compile the code yourself:
unzip jtds-1.3.1-src.zip patch -p1 < jtds-ora-syb16.patch ant dist
When the compilation finishes, the binary would appear in build/jtds-1.3.1.jar
5. TLDR: The modified version of jtds.jar
For all those who don’t want to compile the jTDS library from the source I have prepared a binary of the library. I have compiled it using OpenJDK 1.7.
But, beware! Downloading (and running) binaries from somebody you don’t know is not a best idea. I encourage you to download the sources and compile the binaries by yourself.
079a83e661527528501c6c1fdcee0633 jtds-1.3.1-ase16.jar
32687cac2563aa661da6fa9f378f46c2 jtds-1.3.1-ase16.jar.sig
Disclaimer: The modified version of jTDS presented here has not been tested in any kind of production environment. Actually, there have not been made any (beside basic) tests at all. Please test the modified library before connecting to any production database. Unpredictable results may appear. Remember, that you are doing it at your own risk. But if you have tested it, please give me some feedback!