Failed to connect using JDBC to SqlAnywhere 17

[former_SQLA_member1695006]

I try to connect to SqlAnywhere 17 demoDB using a tool called DbSchema. The tool is using JDBC connection.

For this I started SQL Central, then I also did dbsrv17.exe -x tcpip -n server1 to start the Tcp/IP service ( this is in the tool Help ).

Then I try to connect as DBA/sql but I get 'failed login'. I tried also creating an user in SQL Central and connect using it, but connectivity still fails. The connection is done like this:

final Properties properties = new Properties(); properties.put( "user","DBA"); properties.put( "password","sql"); driver.connect( "jdbc:sybase:Tds:localhost:2638/demo?LITERAL_PARAMS=true", properties );

The error is java.sql.SQLException: JZ00L: Login fehlgeschlagen. �berpr�fen Sie SQLWarnings zu diesem Fehler, um die Ursache zu sehen. translated Login failed. Check SQLWarnings about this error to see the cause. I don't know how to get the SQLWarnings.

Any suggestion on what should I check ?

[Breck_Carter]

Please show how you specify the user id and password. Use copy and paste rather than re-typing. Please show us the exact text of the error message.

[former_SQLA_member1695006]

The connection is done like this:

final Properties properties = new Properties(); properties.put( "user","DBA"); properties.put( "password","sql"); driver.connect( "jdbc:sybase:Tds:localhost:2638/demo?LITERAL_PARAMS=true", properties );

The error is java.sql.SQLException: JZ00L: Login fehlgeschlagen. �berpr�fen Sie SQLWarnings zu diesem Fehler, um die Ursache zu sehen. translated Login failed. Check SQLWarnings about this error to see the cause. I don't know how to get the SQLWarnings.

[Breck_Carter]

It seems that DbSchema defaults to using the old slow jconnect JDBC driver jconn4d.jar rather than the new fast SQL Anywhere JDBC driver sajdbc4.jar

For more information about the two drivers see https://help.sap.com/viewer/98ad9ec940e2465695685d98e308dff5/17.0/en-US/3bd02ce86c5f101482b78476939f...

You may need to install the SAP Java Development Kit (JDK) to get jconn4d.jar. For more information ( good luck! you'll need it 🙂 see https://wiki.scn.sap.com/wiki/display/SQLANY/Downloading+the+jConnect+driver

sajdbc4.jar comes with SQL Anywhere, but you may have to tell DbSchema how to use it (see the Manage Drivers button).

I got stuck on the funky DbSchema - Manage Drivers - +New - "JDBC URL with tags" field.

I tried these wild guesses...

JDBC URL with tags  jdbc:sqlanywhere:<host>:<port>/<db>?LITERAL_PARAMS=true
Edit the JDBC URL   jdbc:sqlanywhere:localhost:2638/demo?LITERAL_PARAMS=true

FYI the driver is located here C:\\Program Files\\SQL Anywhere 17\\Java\\sajdbc4.jar

Got this far...

java.lang.UnsatisfiedLinkError: no dbjdbc17 in java.library.path: 
[c:\\program files\\common files\\i4j_jres\\14.0.1\\bin, 
C:\\Windows\\System32, C:\\Windows, C:\\Windows\\System32\\wbem, 
C:\\Windows\\System32\\WindowsPowerShell\\v1.0\, 
C:\\Windows\\Microsoft.NET\\Framework\\v2.0.50727, C:\\Windows\\System32, 
C:\\Windows, C:\\Windows\\System32\\wbem, 
C:\\Windows\\System32\\WindowsPowerShell\\v1.0\, 
C:\\Windows\\System32\\OpenSSH\\]
  at java.base/java.lang.ClassLoader.loadLibrary(ClassLoader.java:2680)
  at java.base/java.lang.Runtime.loadLibrary0(Runtime.java:807)
  at java.base/java.lang.System.loadLibrary(System.java:1907)
  at sap.jdbc4.sqlanywhere.IDriver.try_load(IDriver.java:451)
  at sap.jdbc4.sqlanywhere.IDriver.<clinit>(IDriver.java:413)
  at java.base/java.lang.Class.forName0(Native Method)
  at java.base/java.lang.Class.forName(Class.java:427)
  at com.wisecoders.dbs.rdbms.driver.model.a.a(DriverClassLoader.java:141)
  at com.wisecoders.dbs.rdbms.driver.model.a.b(DriverClassLoader.java:107)
  at com.wisecoders.dbs.rdbms.driver.model.a.a(DriverClassLoader.java:85)
  at com.wisecoders.dbs.rdbms.driver.model.a.a(DriverClassLoader.java:64)
  at com.wisecoders.dbs.rdbms.driver.model.a.<init>(DriverClassLoader.java:34)
  at com.wisecoders.dbs.rdbms.driver.model.RdbmsDriverManager.i(RdbmsDriverManager.java:131)
  at com.wisecoders.dbs.dialogs.system.FxUploadDriverTask.succeeded(FxUploadDriverTask.java:78)
  at com.wisecoders.dbs.rdbms.driver.fx.FxDriversDialog$4.succeeded(FxDriversDialog.java:244)
  at com.wisecoders.dbs.sys.fx.Dialog$.showDialog(Dialog$.java:196)
  at com.wisecoders.dbs.sys.fx.Dialog$.showDialog(Dialog$.java:168)
  at com.wisecoders.dbs.rdbms.driver.fx.FxDriversDialog.<init>(FxDriversDialog.java:112)
  at com.wisecoders.dbs.rdbms.connect.fx.FxConnectorEditor.addDriver(FxConnectorEditor.java:785)
  at com.wisecoders.dbs.sys.Rx.a(Rx.java:148)
  at com.wisecoders.dbs.sys.fx.Dialog$.showDialog(Dialog$.java:196)
  at com.wisecoders.dbs.sys.fx.ButtonDialog$.isDialogResultOk(ButtonDialog$.java:20)
  at com.wisecoders.dbs.sys.fx.ButtonDialog$.editingOk(ButtonDialog$.java:26)
  at com.wisecoders.dbs.project.fx.frame.FxFrame.newProjectFromDb(FxFrame.java:297)
  at com.wisecoders.dbs.project.fx.frame.FxWelcome.newProjectAction(FxWelcome.java:98)
  at com.wisecoders.dbs.sys.Rx.a(Rx.java:148)
  ...

Maybe the DbSchema folks can help.

[chris_keating]

I am unconvinced that DbSchema supports SQL Anywhere. This tool incorrect supplies only the jar file and misses a number of native libraries that must be the same version as the jar. Copying your SQLA install sajdbc4.jar to the c:\\Users\\<user>.DbSchema\\drivers\\SQLAnywhere and adding the following into DbSchema.vmoptions (in the root of the install) -Djava.library.path=<path_to_sqla_binaries>. This allows you to connect but then it incorrectly attempts to treat tables as databases (or schemas) resulting in exceptions. There are also exceptions after a successful connection with using jConnect (I had no issues with making a connection to the demo DB - although it is assuming that you are connecting to ASE so modifications to the URL may be needed in a SA context). If I understand the exception, it also appears to be tripping up trying to deal with schemas.

[former_SQLA_member1695006]

Great, thank you! This works for me as well with the same issues. I wrote to DbSchema team and they will check the exception right this days. I will write again here when is fixed.

[former_SQLA_member1695006]

Thank you as well for checking this. Same issue, I will write back when I have an update from DbSchema.

[Breck_Carter]

> unconvinced that DbSchema supports SQL Anywhere

That may be true, but the inverse may also be true; i.e., you can make SQL Anywhere support DbSchema 🙂

[chris_keating]

I have found a workaround. The issue appears to be that the DBSchema tool is executing DatabaseMetaData.getTables method supplying the 'catalog' as a parameter to this method. SQL Anywhere does not understand that since there is technically not a catalog. We accept NULL and an empty string and throw a driver not capable error otherwise. For ODBC connections, we can suppress that by setting the "Prevent driver not capable errors". If you create a DSN for you connection, you can take advantage of this by connecting using a DNS i.e., the URL "jdbc:sqlanywhere:dsn=SQL Anywhere 17 Demo". I will investigate if it is possible to pass this option to JDBC directly.

[chris_keating]

If you are using the SQLA JDBC driver to connect, you can use the PreventNotCapable connection option to ignore the driver not capable error. Here is an example URL:

jdbc:sqlanywhere:eng=demo17;uid=dba;pwd=sql;PreventNotCapable=YES

[chris_keating]

A fix has been made to address "Driver not capable" errors reported accessing SQL Anywhere metadata information when supplying catalog information, i.e., the following JDBC call:

   ResultSet tables = metaData.getTables( conn.getCatalog(), "GROUPO", "%", types );

The error will continue to be reported if the database name of the connection does not match the supplied catalog.

This has been addressed in Engineering Case #822551 in SQL Anywhere 17.0 Build 6194 and will be available in an upcoming patch.

[former_SQLA_member1694918]

Same issue in linux and mac. In windows connect normally 😞

Has anyone already solved this problem?

[chris_keating]

Can you start a new thread and provide the details of the issue (including any errors or exceptions) that you are seeing in Linux and Mac.

[former_SQLA_member1694918]

Thank you for response.

New thread: https://sqlanywhere-forum.sap.com/questions/38477/exception-no-dbjdbc17-in-javalibrarypath-in-mac-an...

[former_SQLA_member1694972]

copy the dbjdbc17.dll found in your SQL Anywhere\\Bin64 directory into the same directory that you copied the sajdbc4.jar and try it again.