No Suitable Driver error found while connecting to AS400

kartik18 commented 5 years ago

Hi, I'm trying to connect AS400 with Kafka using Kafka Connect. Here are the properties that I've defined 1) worker.properties bootstrap.servers=192.168.1.249:6667 key.converter=org.apache.kafka.connect.json.JsonConverter key.converter.schemas.enable=false value.converter=org.apache.kafka.connect.json.JsonConverter value.converter.schemas.enable=false internal.key.converter=org.apache.kafka.connect.json.JsonConverter internal.value.converter=org.apache.kafka.connect.json.JsonConverter internal.key.converter.schemas.enable=false internal.value.converter.schemas.enable=false rest.port=8084 plugin.path=/home/kgumber/confluent-5.0.0/share/java/ offset.storage.file.filename=/tmp/connect.offsets

2) jdbc-connector.properties name=test-as400 connector.class=io.confluent.connect.jdbc.JdbcSourceConnector tasks.max=1 driverClassName=com.ibm.as400.access.AS400JDBCDriver connection.url=jdbc:as400://xxxxxx/AS400D60;libraries=ABCD; connection.password=xxxx connection.user=xxxxx mode=bulk query=SELECT * FROM ABCD.CCC limit 10 poll.interval.ms=60000 batch.max.rows=10000 table.types=CCC topic.prefix=test-as400-jdbc-

List under /home/kgumber/confluent-5.0.0/share/java/ confluent-common kafka kafka-connect-elasticsearch kafka-connect-hdfs kafka-connect-jdbc kafka-connect-s3 kafka-connect-storage-common kafka-rest kafka-serde-tools ksql rest-utils schema-registry

I've placed jt400.jar in kafka-connect-jdbc directory

The error I'm getting is Invalid value java.sql.SQLException: No suitable driver found for jdbc:as400://xxxxxx/AS400D60;libraries=CCC; for configuration Couldn't open connection to jdbc:as400://xxxxx/AS400D60;libraries=CCC;

avocader commented 5 years ago

Hi @kartik18,

This issue seems to be related to a JDBC URL format, specific to AS400. There is a related discussion, which suggest replacing "/" by ":" between host and database. For your configuration, you can try jdbc:as400://xxxxxx:AS400D60;libraries=ABCD;.

Thanks.

kbreidenbach commented 5 years ago

Hi @kartik18,

Did you ever manager to solve this? I'm getting the same error and have tried multiple connect string formats to no avail.

KB

forresthu commented 5 years ago

I got the same issue today. Solved it by using latest JDK8 version of jt400.jar.

cindyreview2 commented 4 years ago

I have the same issue. I followed https://rmoff.dev/fix-jdbc-driver-video , but issue still exists in confluent 5-4-1.

cindyreview2 commented 4 years ago

I got the same issue today. Solved it by using latest JDK8 version of jt400.jar.

Can you please post your URL string? The jt400.java works will in a java program when Device Driver is loaded before connecting. I tried all suggestions, but it won't work for content, but individual jdbc java program.

cindyreview2 commented 4 years ago

Is this still a configuration issue? I used the same configuration, the connector still will not setup. I am getting the timeout

sandycomp commented 4 years ago

I had same issue with MySQL on mac, copying jar to java extension solved the issue fro me. Here is path /Library/Java/Extensions/mysql-connector-java-5.1.18.jar

cindyreview2 commented 4 years ago

Is this still a configuration issue? I used the same configuration, the connector still will not setup. I am getting the timeout

We finally resolved the timeout issue specifically for DB2 ISeries AS400. In the config we added:

key.converter, value.converter, key.converter.schema.enable, value.converter.schema.enable
schema.pattern = specific library
connection.url = jdbc:as400://host;libraries=dbname.specific library;

ChhatralaNirmal commented 4 years ago

Hi @cindyreview2 - How did you resolve "No Suitable Driver " error ? Can you please post the full steps you followed ? I tried to make my connection.url as you stated above, but I'm still stuck at "No Suitable Driver found" error. Note - I'm using jt400.jar with jdk-8

cnwme commented 3 years ago

Hi all, i'm getting same issue with 6.0.0 version kafka and kafka-connect-jdbc-10.0.0.jar. using jt400.jar 10.3 & 10.4, no way it still same error : Caused by: org.apache.kafka.connect.errors.ConnectException: java.sql.SQLException: No suitable driver for jdbc:as400

any update please ? Regards Martin

cnwme commented 3 years ago

hi All i've fixed connection by adding on classpath connect-distributed the folder lib related to this kafka-connect-jdbc. Hope that will help community. regards Martin

marcelrend commented 3 years ago

I managed to get this working in Docker image confluentinc/cp-kafka-connect:5.4.1-2-ubi8 as follows:

Don't adjust the CLASSPATH (might work as well though)
Download the jt400 jar (jdk-8) and put it next to the other jdbc drivers, e.g. -v ~/jt400-10.4.jar:/usr/share/java/kafka-connect-jdbc/jt400.jar
Ensure your connection string is correct. Mine only worked using a semi-colon after the hostname. jdbc:as400://hostname;database name=lalala;ssl_connection=true;prompt=false;user=username;password=supersecret

anandkumarkj commented 3 years ago

I managed to get this working in Docker image confluentinc/cp-kafka-connect:5.4.1-2-ubi8 as follows:

Don't adjust the CLASSPATH (might work as well though)

Download the jt400 jar (jdk-8) and put it next to the other jdbc drivers, e.g. -v ~/jt400-10.4.jar:/usr/share/java/kafka-connect-jdbc/jt400.jar

Ensure your connection string is correct. Mine only worked using a semi-colon after the hostname. jdbc:as400://hostname;database name=lalala;ssl_connection=true;prompt=false;user=username;password=supersecret

Can one of you please share your connect.properties for this. Many Thanks in advance.

anandkumarkj commented 3 years ago

hi All i've fixed connection by adding on classpath connect-distributed the folder lib related to this kafka-connect-jdbc. Hope that will help community. regards Martin

Where exactly did you add the Classpath.... Is it in the connect-distributed.properties file. Can you please give more details please. Many Thanks in advance.

marcelrend commented 3 years ago

I managed to get this working in Docker image confluentinc/cp-kafka-connect:5.4.1-2-ubi8 as follows:

Don't adjust the CLASSPATH (might work as well though)

Download the jt400 jar (jdk-8) and put it next to the other jdbc drivers, e.g. -v ~/jt400-10.4.jar:/usr/share/java/kafka-connect-jdbc/jt400.jar

Ensure your connection string is correct. Mine only worked using a semi-colon after the hostname. jdbc:as400://hostname;database name=lalala;ssl_connection=true;prompt=false;user=username;password=supersecret

Can one of you please share your connect.properties for this. Many Thanks in advance.

This is the connector config I use:

config = {
    "name": "connector_name",
    "connector.class": "io.confluent.connect.jdbc.JdbcSourceConnector",
    "tasks.max": "1",
    "connection.url": jdbc_connection_string,
    "connection.attempts": 1440
    "connection.backoff.ms": 10000,
    "poll.interval.ms": "600000",
    "table.poll.interval.ms": "14400000",
    "batch.max.rows": "2000",
    "dialect.name": "Db2DatabaseDialect",
    "topic.prefix": "topic_name",
    "mode": "timestamp",
    "timestamp.column.name": "last_change_timestamp",
    "quote.sql.identifiers": "never",
    "query": "select col from table"
}

confluentinc / kafka-connect-jdbc

No Suitable Driver error found while connecting to AS400 #493