Register new database driver - sap.jdbc4.sqlanywhere.lDriver

[USCHUKN1ME]

Hi,
how do I find the ID for a driver?

How would the URL template look like?

Secondly:
How do I use ODBC in KNIME to connect to a database based on SQL Anywhere 17?

Thx in advance

greetings

[ipazin]

Hello @USCHUKN1ME,

you don’t have to find ID of the driver. You define it yourself. However it must consist only of alphanumeric characters and underscore. URL template depends on driver you are registering. In your case this is what you need to find.

Here is link to Database guide, register your own driver section:

KNIME Database Extension Guide

Seems ODBC is not supported out of box. See here: Windows ODBC Data Sources

Br,
Ivan

[USCHUKN1ME]

Hi ipazin,

okay thx .

I registered the sap.jdbc4.sqlanywhere.lDriver and i found it in the driver selection.

Get still this error message:
ERROR Database Connector (legacy) Execute failed: Could not create connection to database: no dbjdbc17 in java.library.path.

I think i made a mistake - but where?

greetings

[ipazin]

Hello @USCHUKN1ME,

why are you using the legacy Connector? In general, if there’s no obstacles, I suggest to use new DB framework and try out DB Connector from there.

Error itself indicates you are missing library. See here:

Error connecting Java to SQL Anywhere database

Possibly same issue might occur with new connector node.

Br,
Ivan

[tobias.koetter]

Hello USCHUKN1ME,
the dialog where you registered the driver was for the standard database framework so the driver will be available in the DB Connector node but not the legacy Database Connector node. As Ivan suggested I would also recommend to use the new DB framework e.g. the DB Connector node. If you encounter any problems with the SQL code that is generated by KNIME you have much more option to tweak this via the Advanced tab.
Bye
Tobias

[USCHUKN1ME]

Hi again,

thx !

The SQl driver is not available in the DB connector ?!

ERROR Database Connector (legacy) 0:41 Execute failed: Could not create connection to database: Ungültiger ODBC-Handle

Path to dbjdbc17.dll and dbodbc17.dll

Correct?

greetings

[ipazin]

Hello @USCHUKN1ME,

is there registered JDBC anywhere driver when you go KNIME --> Databases under Preferences?

Br,
Ivan

[USCHUKN1ME]

@ipazin

s there registered JDBC anywhere driver when you go KNIME → Databases under Preferences?

[ipazin]

Hello @USCHUKN1ME,

Databases is for current framework and drivers registered there show up with DB Connector node (and other matching connector nodes) while Databases (legacy) is for legacy framework and drivers registered there show up with Database Connector (legacy) node.

Br,
Ivan

[USCHUKN1ME]

Hello again,

error message now:
ERROR DB Connector Execute failed: Native Library C:\Program Files\KNIME\plugins\org.knime.binary.jre.win32.x86_64_1.8.0.252-b09\jre\bin\dbjdbc17.dll already loaded in another classloader

[ipazin]

Hello @USCHUKN1ME,

sounds like a conflict cause you already added dll which might not be necessary with new framework. Have you tried to deleting it from above mentioned path and then connecting?

Br,
Ivan

[USCHUKN1ME]

@USCHUKN1ME

C:\Program Files\KNIME\plugins\org.knime.binary.jre.win32.x86_64_1.8.0.252-b09\jre\bin\dbjdbc17.dll

Hi ipazin,

you mean deleting the *.dll in: C:\Program Files\KNIME\plugins\org.knime.binary.jre.win32.x86_64_1.8.0.252-b09\jre\bin\ dbjdbc17.dll

Greetings

[tobias.koetter]

Hello USCHUKN1ME,
this is copied from the documentation regarding native libraries when registreing a thirdparty driver for the new database framework:
" If the JDBC driver requires native libraries e.g DLLs you need to put all of them into a single folder and then register this folder via the Add directory button in addition to the JDBC driver .jar file."
So please do not add the files to the jre folder of eclipse but create a separate folder that you add to the classpath of the driver via the Add directory button.
Bye
Tobias

[USCHUKN1ME]

Hi Tobias,

thanks for the hint. I’ve packed everything in a new folder.

Next error-message:
ERROR DB Connector 0:56 Execute failed: Invalid ODBC handle

i found a tip on this page :
https://sqlanywhere-forum.sap.com/questions/16238/error-connecting-remotely-using-sajdbc4jar

The reason both yourself and Jonathan are seeing the “Invalid ODBC Handle” message in the ‘deployed’ JDBC scenario is because while the JDBC JAR driver can find the ‘dbjdbc12.dll’ native library in the path, the native driver cannot locate the additional support DLLs that go along with this file, that are mentioned in the documentation. Specifically, the dblg[xx]12.dll file is missing - but **dbicu12.dll**, and **dbicudt12.dl**l are also required for the proper operation of the driver.

I put the path into the KNIME databse preferences:

It looks like the connection is established.

but now i got this Error:
ERROR DB Query Reader 0:57 Execute failed: [SAP][JDBC Driver][SQL Anywhere]Tabelle ‘hs_vk_beleg’ nicht gefunden

The SQL Statement is a 1:1 copy from my query in Access:

SELECT hs_vk_beleg.[belegart], hs_vk_beleg.[belegnr], hs_vk_beleg.[belegdatum], hs_vk_beleg.[lieferdatum], hs_vk_beleg.[arf_1_summe], hs_vk_beleg.[arf_2_summe]
FROM hs_vk_beleg;

[USCHUKN1ME]

I found the mistake.

The SQL statement from Access didn´t work 1:1 in KNIME. The table-name-syntax is slightly different (see also below) - “hs”.“vk_beleg”

Many thanks to everyone for the very good support.

[USCHUKN1ME]

In addition, the URL for the database access to the database which is on the server.

The SQLA JDBC url should be of the form:

jdbc:sqlanywhere:<connection_str>

Based on the DSN (line breaks after semicolons for readability)

jdbc:sqlanywhere:ServerName=hsab_isoloc_s_app02;

• dbn=hsabiso2;*
• links=TCPIP;*
• uid=;*
• pwd=*

If you are trying to autostart the database on your machine with KNIME it would look like the following (set the to the path for the database file): jdbc:sqlanywhere:ServerName=hsab_isoloc_s_app02; dbf=\HSABisotest.db dbn=hsabiso2; uid=; pwd=

Source: https://sqlanywhere-forum.sap.com/questions/36506/sql-anywhere-17-connecting-to-a-database-hsabiso2db-via-knime?page=1&focusedAnswerId=36511#36511

[ipazin]

Hello @USCHUKN1ME ,

glad you made it! And tnx for sharing additional info. Sure it will be useful to others trying to connect to SQL Anywhere.

Br,
Ivan