JDBC connection with ClickHouse

emil.blohme1 · June 20, 2024, 7:59am

Integration server : version 10.15.0.0.674
webMethods adapter for JDBC: version 10.3.0.0.32

Fixes:
JDBC_10.3_Fix20
IS_10.15_Core_Fix11

I’m trying to connect to a data warehouse called ClickHouse.
I have downloaded the ClickHouse JDBC jar-file, put it under /{is_instance}/lib/jars and restarted the server.
When providing my connection details and trying to test the connection in the Administration GUI of the Integration Server I get the following error message:

Error encountered

[ART.118.5011] Adapter Runtime (Connection): Unable to create new connection test:test. [ADA.1.204] Cannot connect to the database with DataSource class “com.clickhouse.jdbc.ClickHouseDriver”. class com.clickhouse.jdbc.ClickHouseDriver cannot be cast to class javax.sql.DataSource (com.clickhouse.jdbc.ClickHouseDriver is in unnamed module of loader com.wm.app.b2b.server.PackageClassLoader @7350a8a6; javax.sql.DataSource is in module java.sql of loader ‘platform’)

Has anyone experienced this error before? Any ideas how to solve it?

Many thanks!

//Emil

Dave_Laycock1 · June 20, 2024, 6:19pm

I’ve never used that database, but as the error states, the class needs to be a subclass of javax.sql.DataSource.

Looking at the driver, you should be able to use com.clickhouse.jdbc.ClickHouseDataSource

Also driver jars should be put in {is_instance}/packages/WmJDBCAdapter/code/jars

https://documentation.softwareag.com/webmethods/adapters_estandards/Adapters/JDBC/JDBC_10-3/10-3_Adapter_for_JDBC_webhelp/index.html#page/jdbc-webhelp%2Fta-installing_single_version_jdbc_on_is.html

emil.blohme1 · June 24, 2024, 8:14am

Hi Dave!
Thanks for the reply. I tried putting the jar-file under {is_instance}/packages/WmJDBCAdapter/code/jars instead, restarting the IS and reloading the package. Unfortunately I still get the exact same error message.

Any other suggestion what could be the issue?

Many thanks!

//Emil

Dave_Laycock1 · June 24, 2024, 5:16pm

To confirm, did you also change the class to “com.clickhouse.jdbc.ClickHouseDataSource” ?

engin_arlak · June 24, 2024, 6:02pm

Your datasource class is wrong.

Try this. This seems to be accurate. You can find that datasource from the repo but not the one you entered.

github.com

ClickHouse/clickhouse-java/blob/6539f9ce54ac14a6029396d626f1f1fc068fbf29/clickhouse-jdbc/src/main/java/com/clickhouse/jdbc/ClickHouseDataSource.java#L16


      
          import com.clickhouse.jdbc.internal.ClickHouseConnectionImpl;
          import com.clickhouse.jdbc.internal.ClickHouseJdbcUrlParser;
          import com.clickhouse.jdbc.internal.ClickHouseJdbcUrlParser.ConnectionInfo;
          
          import java.io.PrintWriter;
          import java.sql.SQLException;
          import java.sql.SQLFeatureNotSupportedException;
          import java.util.Properties;
          import java.util.logging.Logger;
          
          public class ClickHouseDataSource extends JdbcWrapper implements DataSource {
              private final String url;
              private final Properties props;
          
              protected final ClickHouseDriver driver;
              protected final ConnectionInfo connInfo;
          
              protected PrintWriter printWriter;
              protected int loginTimeoutSeconds = 0;
          
              public ClickHouseDataSource(String url) throws SQLException {

emil.blohme1 · June 25, 2024, 7:47am

Thanks a lot @engin_arlak and @Dave_Laycock1. Totally forgot to update the Data source class…
Doing that I get a new error

[ADA.1.201] The JDBC DataSource class “com.clickhouse.jdbc.ClickHouseDataSource” cannot be instantiated.
com.clickhouse.jdbc.ClickHouseDataSource

and according to the JDBC User Guide this error means that the JDBC driver is not supported.

201 The JDBC DataSource class ClassName cannot be instantiated.
Cause: The instantiation of the JDBC driver’s DataSource class failed.
Response: Use a supported JDBC driver

BR,
Emil

Dave_Laycock1 · June 25, 2024, 5:37pm

That is a bit surprising. I knew ClickHouse isn’t explicitly supported by the adapter, but I hoped it would work for you.

I believe recent versions of the adapter have some “generic” JDBC support, assuming the database and driver are standards compliant. For example, I was able to do basic CRUD operations with the Firebird database and driver, which isn’t supported. It didn’t raise that connection error.

Can someone from Software Ag comment?

Holger_von_Thomsen · July 4, 2024, 2:40pm

Hi,

from where did you download the driver jar exactly?

Regards,
Holger

emil.blohme1 · July 5, 2024, 8:04am

Hello,

I downloaded the driver jar from Release Release v0.6.2 · ClickHouse/clickhouse-java · GitHub.

Best Regards,
Emil

Holger_von_Thomsen · July 5, 2024, 9:34am

Hi Emil,

assuming you are running the clickhouse-jdbc-0.6.2.jar as the Driver Jar, using the “com.clickhouse.jdbc.ClickHouseDataSource” class should work.
This jar should be working on any JVM version 8 or newer.

If nothing else helps, you should try to open an incident with SAG Support Team to clarify this.

Regards,
Holger

emil.blohme1 · July 5, 2024, 10:56am

Hello Holger!
Thanks for your response! I have tried the scenario that you are describing, but get the error:
[ADA.1.201] The JDBC DataSource class “com.clickhouse.jdbc.ClickHouseDataSource” cannot be instantiated.
com.clickhouse.jdbc.ClickHouseDataSource

I have also opened an incident describing the issue towards SAG support team and got the following response:
ClickHouse is not a database supported by our webMethods Adapter 10.3 for JDBC, so we cannot provide support on this implementation.

Your help is much appreciated!

Best Regards,
Emil

Holger_von_Thomsen · July 5, 2024, 12:10pm

Hi Emil,

eventually you can try a third party driver like the clickhouse4j driver, but this does not guarantee that this will work.

Additionally, you can try to connect via the WmDB package, which uses the Driver class directly without using the DataSource class. But this is a little bit more complicated in its usage.

You might want to consider to create an Idea in the SAG Idea Portal and ask to add ClickHouse support in a future release.

Regards,
Holger

emil.blohme1 · July 5, 2024, 12:35pm

Hello,
Thanks! Currently, I’ve created my own java service using the ClickHouse java client to connect to the database.
This works, but I prefer using the supported built in JDBC connections.

I have also created an idea in the SAG Idea portal, hopefully ClickHouse will be added as a supported JDBC driver soon

Best Regards,
Emil

system · January 1, 2025, 12:35pm

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.

Topic		Replies	Views
JDBC Adapter Not getting enabled/Connected webMethods , Integration-Server-and-ESB , Adapters-and-E-Standards	4	8882	April 2, 2021
[ADA.1.200]Error webMethods , webMethods-General , Integration-Server-and-ESB	3	1477	April 2, 2021
Cannot enable JDBC connection? webMethods , Integration-Server-and-ESB , Adapters-and-E-Standards	3	1776	April 2, 2021
JDBC Adapter Connection webMethods , Integration-Server-and-ESB , Adapters-and-E-Standards	3	1621	April 2, 2021
JDBC adapter config error webMethods , Integration-Server-and-ESB , Adapters-and-E-Standards	2	1882	April 2, 2021

JDBC connection with ClickHouse

Related topics