OrientDB class loading issue with SQL Server JTDS driver #5421

racyam · 2015-12-04T09:13:58Z

I am writing a custom etl to export data sql server to orientdb using jtds driver.

Class.forName("net.sourceforge.jtds.jdbc.Driver");
String url = "jdbc:jtds:sqlserver://127.0.0.1:1433/mydb";
Connection c = null;
try {
c = DriverManager.getConnection(url, "user", "password");
}

Exception in thread "main" com.orientechnologies.orient.core.exception.OConfigurationException: Error on creating ETL processor
at com.orientechnologies.orient.etl.OETLProcessor.parse(OETLProcessor.java:242)
at com.orientechnologies.orient.etl.OETLProcessor.parse(OETLProcessor.java:153)
at com.orientechnologies.orient.etl.OETLProcessor.parseConfigAndParameters(OETLProcessor.java:143)
at com.orientechnologies.orient.etl.OETLProcessor.main(OETLProcessor.java:107)
at DBConnection.main(DBConnection.java:71)
Caused by: com.orientechnologies.orient.core.exception.OConfigurationException: [JDBC extractor] error on connecting to JDBC url jdbc:jtds:sqlserver://127.0.0.1:1433/mydb' using user 'user' and the password provided
at com.orientechnologies.orient.etl.extractor.OJDBCExtractor.configure(OJDBCExtractor.java:75)
at com.orientechnologies.orient.etl.OETLProcessor.configureComponent(OETLProcessor.java:460)
at com.orientechnologies.orient.etl.OETLProcessor.parse(OETLProcessor.java:206)
... 4 more

if i am trying to run OETL utility, i got the similar exception.

robfrank · 2015-12-04T09:20:21Z

Did you put the jtds jar in the lib dir?

racyam · 2015-12-04T09:21:56Z

Jar is in classpath

racyam · 2015-12-04T09:23:41Z

I have placed jars in lib, but it is java program...I see similar issue if i run OETL utility

robfrank · 2015-12-04T09:28:48Z

ok, can you write down more details? Such as schema, query, maybe a chunk of the code you write.
I guess you have a BLOB field that need to be deserialized the right way, but I think it could be only managed by your own code.

racyam · 2015-12-04T09:44:45Z

config.json

{
"config": {
"log": "debug"
},
"extractor" : {
"jdbc": {
"driver": "net.sourceforge.jtds.jdbc.Driver",
"url": "jdbc:jtds:sqlserver://127.0.0.1:1433/mydb",
"userName": "user",
"userPassword": "password",
"query": "select * from Request" }
},
"transformers" : [
{ "vertex": { "class": "Jobs"} }
],
"loader" : {
"orientdb": {
"dbURL": "remote:localhost/JOBS_DB",
"dbUser": "root",
"dbPassword": "ADMIN",
"dbAutoCreate": true,
"tx": false,
"batchCommit": 1000,
"dbType": "graph"
}
}
}

Test Program:

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DBConnection {

public static void main(String a[]) throws ClassNotFoundException, SQLException {
    CCLoader cl = new CCLoader(CCLoader.class.getClassLoader());
    cl.loadClass("net.sourceforge.jtds.jdbc.Driver");
    String url = "jdbc:jtds:sqlserver://127.0.0.1:1433/mydb";

    Connection c = null;

    try {
        c = DriverManager.getConnection(url, "user", "password");

        System.out.println(c.isClosed());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (c != null) {
            c.close();
        }
    }
    com.orientechnologies.orient.etl.OETLProcessor.main(new String[]{"E:\\Development\\config.json"});
}

}

Table does not have any blob data type . It has field of type bit, int, nvarchar, datetime, varchar..

racyam · 2015-12-04T09:54:47Z

#5422 : it has issue with bit field. instead of doing "* from", i mentioned columns name and removed field of bit data type from select list. Then it looks to be working without exception

robfrank · 2015-12-04T10:11:45Z

what version of orient are you using?
So one of the field of the table Request on SQLServer is of type bit?

racyam · 2015-12-04T10:12:57Z

version 2.1.6.
yes one of the type is bit

robfrank · 2015-12-04T10:45:00Z

Ok, so as far I understand a BIT data type is a BOOLEAN in the java world.
So, it seems that microsoft JDBC maps it to BOOLEAN JDBC type:
https://msdn.microsoft.com/en-us/library/ms378878(v=sql.110).aspx

while JTDS maps it to JDBC BIT type:

http://jtds.sourceforge.net/typemap.html

I'm improving the ETL data types mapper to handle the BIT type.
Could you please try the same import using the MSDN JDBC driver instead of the JTDS one?
You can find it there: https://msdn.microsoft.com/en-us/data/aa937724.aspx

#5421 #5422

racyam · 2015-12-04T12:01:05Z

are u also looking into this issue too?
Exception in thread "main" com.orientechnologies.orient.core.exception.OConfigurationException: Error on creating ETL processor
at com.orientechnologies.orient.etl.OETLProcessor.parse(OETLProcessor.java:242)
at com.orientechnologies.orient.etl.OETLProcessor.parse(OETLProcessor.java:153)
at com.orientechnologies.orient.etl.OETLProcessor.parseConfigAndParameters(OETLProcessor.java:143)
at com.orientechnologies.orient.etl.OETLProcessor.main(OETLProcessor.java:107)
at DBConnection.main(DBConnection.java:71)
Caused by: com.orientechnologies.orient.core.exception.OConfigurationException: [JDBC extractor] error on connecting to JDBC url jdbc:jtds:sqlserver://127.0.0.1:1433/mydb' using user 'user' and the password provided
at com.orientechnologies.orient.etl.extractor.OJDBCExtractor.configure(OJDBCExtractor.java:75)
at com.orientechnologies.orient.etl.OETLProcessor.configureComponent(OETLProcessor.java:460)
at com.orientechnologies.orient.etl.OETLProcessor.parse(OETLProcessor.java:206)
... 4 more

robfrank · 2015-12-04T12:11:05Z

Well, the staktrace says :

Caused by: com.orientechnologies.orient.core.exception.OConfigurationException: [JDBC extractor] error on connecting to JDBC url jdbc:jtds:sqlserver://127.0.0.1:1433/mydb' using user 'user' and the password provided at

It seems a connection error. Has fhe conf file you gave me the same problem?
How this problem it is related to #5802?

racyam · 2015-12-04T12:14:33Z

yes, conf file has issue. you can try with OETL utility

racyam · 2015-12-04T12:22:54Z

is it correct? #5802 i don't see any such issue number

racyam · 2015-12-04T12:39:53Z

yes.. i copied it
oetl config.json
i copied config.json in bin directory

robfrank · 2015-12-04T12:47:20Z

ok, and what about the jtds jar? Did you copy it in the lib dir ?
Can you paste an ls or dir of you orientdb's lib dir?
thanks.

racyam · 2015-12-04T12:50:52Z

Directory of E:\ORIENTDB\orientdb-community-2.1.6\lib

12/04/2015 02:06 PM

.
12/04/2015 02:06 PM ..
11/23/2015 08:11 PM 62,983 activation-1.1.jar
11/23/2015 08:11 PM 445,288 antlr-2.7.7.jar
11/23/2015 08:11 PM 43,398 asm-3.2.jar
11/23/2015 08:11 PM 17,985 asm-analysis-3.2.jar
11/23/2015 08:11 PM 33,094 asm-commons-3.2.jar
11/23/2015 08:11 PM 21,878 asm-tree-3.2.jar
11/23/2015 08:11 PM 36,551 asm-util-3.2.jar
11/23/2015 08:11 PM 274,185 blueprints-core-2.6.0.jar
11/23/2015 08:11 PM 188,671 commons-beanutils-1.7.0.jar
11/23/2015 08:11 PM 206,035 commons-beanutils-core-1.8.0.jar
11/23/2015 08:11 PM 588,337 commons-collections-3.2.2.jar
11/23/2015 08:11 PM 298,829 commons-configuration-1.6.jar
11/23/2015 08:11 PM 38,795 commons-csv-1.2.jar
11/23/2015 08:11 PM 143,602 commons-digester-1.8.jar
11/23/2015 08:11 PM 261,809 commons-lang-2.4.jar
11/23/2015 08:11 PM 60,686 commons-logging-1.1.1.jar
11/23/2015 08:11 PM 116,786 concurrentlinkedhashmap-lru-1.4.1.ja
11/23/2015 08:11 PM 168,327 gremlin-groovy-2.6.0.jar
11/23/2015 08:11 PM 22,932 gremlin-java-2.6.0.jar
11/23/2015 08:11 PM 5,600,962 groovy-1.8.9.jar
11/23/2015 08:11 PM 7,284,435 hazelcast-all-3.5.3.jar
11/23/2015 08:11 PM 100,884 hibernate-jpa-2.0-api-1.0.0.Final.ja
11/23/2015 08:11 PM 1,307,968 hppc-0.6.0.jar
11/23/2015 08:11 PM 33,483 jackson-annotations-2.2.3.jar
11/23/2015 08:11 PM 192,699 jackson-core-2.2.3.jar
11/23/2015 08:11 PM 865,838 jackson-databind-2.2.3.jar
11/23/2015 08:11 PM 106,573 jansi-1.5.jar
11/23/2015 08:11 PM 659,343 javassist-3.16.1-GA.jar
11/23/2015 08:11 PM 79,832 jettison-1.3.3.jar
11/23/2015 08:11 PM 87,325 jline-0.9.94.jar
11/23/2015 08:11 PM 914,639 jna-4.0.0.jar
11/23/2015 08:11 PM 1,218,171 jna-platform-4.0.0.jar
01/13/2015 10:37 AM 317,816 jtds-1.3.1.jar
11/23/2015 08:11 PM 1,658,512 lucene-analyzers-common-4.7.0.jar
11/23/2015 08:11 PM 2,356,256 lucene-core-4.7.0.jar
11/23/2015 08:11 PM 166,336 lucene-facet-4.7.0.jar
11/23/2015 08:11 PM 36,012 lucene-memory-4.7.0.jar
11/23/2015 08:11 PM 95,780 lucene-misc-4.7.0.jar
11/23/2015 08:11 PM 211,142 lucene-queries-4.7.0.jar
11/23/2015 08:11 PM 390,054 lucene-queryparser-4.7.0.jar
11/23/2015 08:11 PM 45,729 lucene-sandbox-4.7.0.jar
11/23/2015 08:11 PM 108,362 lucene-spatial-4.7.0.jar
11/23/2015 08:11 PM 388,864 mail-1.4.jar
11/23/2015 08:11 PM 78,814 orientdb-client-2.1.6.jar
11/23/2015 08:11 PM 3,076,168 orientdb-core-2.1.6.jar
11/23/2015 08:11 PM 79,969 orientdb-distributed-2.1.6.jar
11/23/2015 08:11 PM 39,918 orientdb-enterprise-2.1.6.jar
11/23/2015 08:11 PM 153,743 orientdb-etl-2.1.6.jar
11/23/2015 08:11 PM 251,064 orientdb-graphdb-2.1.6.jar
11/23/2015 08:11 PM 47,995 orientdb-jdbc-2.1.6.jar
11/23/2015 08:11 PM 99,981 orientdb-lucene-2.1.6.jar
11/23/2015 08:11 PM 187,131 orientdb-object-2.1.6.jar
11/23/2015 08:11 PM 533,385 orientdb-server-2.1.6.jar
11/23/2015 08:11 PM 53,226 orientdb-tools-2.1.6.jar
11/23/2015 08:11 PM 149,692 pipes-2.6.0.jar
11/23/2015 08:11 PM 430,239 snappy-java-1.1.0.1.jar
11/23/2015 08:11 PM 102,177 spatial4j-0.4.1.jar
11/23/2015 08:11 PM 26,514 stax-api-1.0.1.jar

robfrank · 2015-12-04T14:32:53Z

You said :
it has issue with bit field. instead of doing "* from", i mentioned columns name and removed field of bit data type from select list. Then it looks to be working without exception
I'm sorry for so silly question, but I don't understand what it is working and what it is not.

racyam · 2015-12-04T14:47:19Z

ok... sorry, if i confuses you.

I am using JTDS to extract data from SQL server. SerializationException occurs if i include bit data type field in select statement.

I hope that it would be clear now.

robfrank · 2015-12-04T14:52:43Z

ok, as I wrote, I fix the mapping problem for BIT type. We are going to release 2.1.7 that will include the fix. It seems that the official MS driver maps BIT data type on db side to BOOLEAN data type on JDBC side. Can you try with this driver?

racyam · 2015-12-04T14:54:42Z

sure... facing some network issue. Once it fixes, I will try it.

racyam · 2015-12-04T15:38:50Z

I am not able to make connection using MS JDBC driver. I got the similar problem with JTDS but somehow i managed to make a temporary fix in my code. Not able to break it in case of MS JDBC driver.

For me, it looks like there is some issue in class loading.

Caused by: com.orientechnologies.orient.core.exception.OConfigurationException: Error on opening database: the engine 'jdbc' was not found. URL was: jdbc:jtds:sqlserver://127.0.0.1:1433/mydb. Registered engines are: [memory, remote, plocal]
at com.orientechnologies.orient.core.Orient.loadStorage(Orient.java:460)
at com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx.(ODatabaseDocumentTx.java:187)
... 12 more

Here is simple example:

I am making simple SQL SERVER db connection here.. My classpath has two jars OrientJDBC jar and JTDS JAR.

           Class.forName("net.sourceforge.jtds.jdbc.Driver");
    String url = "jdbc:jtds:sqlserver://127.0.0.1:1433/VigilEnt";

    Connection c = null;

    try {
        c = DriverManager.getConnection(url, "sa", "********");
        System.out.println(c.isClosed());
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        if (c != null) {
            c.close();
        }
    }

But gets following exception

Dec 04, 2015 1:47:11 PM com.orientechnologies.common.log.OLogManager log
INFO: OrientDB auto-config DISKCACHE=4,163MB (heap=1,979MB os=8,191MB disk=5,446MB)
com.orientechnologies.orient.core.exception.ODatabaseException: Error on opening database 'jdbc:jtds:sqlserver://127.0.0.1:1433/myDb'
at com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx.(ODatabaseDocumentTx.java:204)
at com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx.(ODatabaseDocumentTx.java:168)
at com.orientechnologies.orient.jdbc.OrientJdbcConnection.(OrientJdbcConnection.java:62)
at com.orientechnologies.orient.jdbc.OrientJdbcDriver.connect(OrientJdbcDriver.java:52)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at DBConnection.main(DBConnection.java:18)
Caused by: com.orientechnologies.orient.core.exception.OConfigurationException: Error on opening database: the engine 'jdbc' was not found. URL was: jdbc:jtds:sqlserver:/127.0.0.1:1433/VigilEnt. Registered engines are: [memory, remote, plocal]
at com.orientechnologies.orient.core.Orient.loadStorage(Orient.java:460)
at com.orientechnologies.orient.core.db.document.ODatabaseDocumentTx.(ODatabaseDocumentTx.java:187)
... 6 more

If i look at stack trace carefully, DriverManager.getConnection should invoke JTDS driver class but it invokes OrientJdbcDriver. Why?

racyam · 2015-12-10T16:21:21Z

any update over it?

robfrank · 2015-12-10T20:41:06Z

I'll check tomorrow

robfrank · 2015-12-11T09:48:47Z

I'm on that right now.

robfrank · 2015-12-11T10:57:25Z

ok, problem found and solved.
So, if you don't need the OrientDB JDBC Driver, and I think you don't need it, simply delete the jar from the orient lib: orientdb-jdbc-VERSION.jar

If you need the Orient JDBC, you need a new jar with the fix I've made. But I'm confident you don't need it.

racyam · 2015-12-11T11:38:04Z

As i am writing a custom ETL utility to get data from SQL Server to OrientDB, i need both. I reduced the code to tell you, what exactly is happening.

racyam · 2015-12-11T11:39:02Z

please re-open it. it's not solved.

robfrank · 2015-12-11T14:09:21Z

So you don't use the ETL anymore, am I right?
And you need to access to orient via its JDBC driver, as fas as I understand.
If you can access to orient without using the JDBC, drop the driver and use native methods.
If you need the driver, I fixed the code, but it will be in the next 2.1.8 release.
You can even build from source on 2.1.x branch.

racyam · 2015-12-11T14:34:48Z

yes, i don't use ETL anymore. I will download and rebuild source. thanks

luigidellaquila assigned robfrank Dec 4, 2015

luigidellaquila added the bug label Dec 4, 2015

luigidellaquila added this to the 2.1.x (next hotfix) milestone Dec 4, 2015

robfrank added the waiting reply label Dec 4, 2015

racyam mentioned this issue Dec 4, 2015

Error in Pipeline execution: com.orientechnologies.orient.core.exception.OSerializationException: Impossible serialize value of type class net.sourceforge.jtds.jdbc.ClobImpl with the ODocument binary serializer #5422

Closed