JDBC Implementation

[cguerin]

Doesn't seem to be a full JDBC implementation -- tools that OrientDB recommends like DbVisualizer won't even load the driver. SQLWorkbench will connect to it, but gives a threading error "Database instance is not set in current thread", when any attempt (MAC OSX) is made to access the tables. Pentaho will connect to it, but chokes on attempting to set the SCHEMA setting to the database name.

What's missing that needs to be in place for common JDBC clients to be able to see the tables?

[robfrank]

Hi, on DbVisualizer the driver should be declared manually using the menu Tools->Driver.

http://www.dbvis.com/doc/8.0/doc/ug/installDriver/installDriver.html

I put the driver jar in a folder under the jdbc folder of dbviz, and then I defined the driver manually.

I'll try SQLWorkbench.

The JDBC spec isn't implemented completely, and schema navigation is quite difficult to implements because some JDBC/RDBMS concepts aren't easily mappable to OrientDB.
But we are very happy to do our duty to support new use cases of the driver. If you can understand what's going wrong with Pentaho I'll do all my best to fix it.

[cguerin]

Dbvisualizer didn't work at all with the jdbc driver. Did it work for you on Mac OS X?

I'll check Pentaho logs

Sent from my iPhone

On Mar 4, 2016, at 4:42 AM, Roberto Franchini notifications@github.com wrote:

Hi, on DbVisualizer the driver should be declared manually using the menu Tools->Driver.

http://www.dbvis.com/doc/8.0/doc/ug/installDriver/installDriver.html

I put the driver jar in a folder under the jdbc folder of dbviz, and then I defined the driver manually.

I'll try SQLWorkbench.

The JDBC spec isn't implemented completely, and schema navigation is quite difficult to implements because some JDBC/RDBMS concepts aren't easily mappable to OrientDB.
But we are very happy to do our duty to support new use cases of the driver. If you can understand what's going wrong with Pentaho I'll do all my best to fix it.

—
Reply to this email directly or view it on GitHub.

[healiseu]

OrientDB JDBC has also problems with Mathematica SQLExecute. I have made a question here.

Answer from @luigidellaquila suggests that OrientDB JDBC does not support prepareStatement at this stage !

[robfrank]

@healiseu we support prepared stmt, even on old 2.0.x :

https://github.com/orientechnologies/orientdb/blob/develop/jdbc/src/test/java/com/orientechnologies/orient/jdbc/OrientJdbcPreparedStatementTest.java

https://github.com/orientechnologies/orientdb/blob/2.0.x/jdbc/src/test/java/com/orientechnologies/orient/jdbc/OrientJdbcPreparedStatementTest.java

[robfrank]

@cguerinyes, it works for me on Mac OSX. I put the Orient JDBC jar under .dbvis/jdbc`
When you star dbViz it show an error, because the driver isn't in his catalog.
Got to Tools->diver manager and map the driver. Then you can create connections to a running server, or even to a plocal db.

[healiseu]

@robfrank I have just tested OrientDB 2.1.12 with orientdb-jdbc-2.1.12-all.jar
Same problem, it opens the connection, it can read tables information, but it cannot execute SQL statement.

Needs["JLink`"]
AddToClassPath["C:\\OrientDB2112\\jdbc\\orientdb-jdbc-2.1.12-all.jar"];
Needs["DatabaseLink`"]
conn=OpenSQLConnection[JDBC[
      "com.orientechnologies.orient.jdbc.OrientJdbcDriver",
      "jdbc:orient:remote:localhost/GratefulDeadConcerts"]]

SQLTables[conn] 

{SQLTable["OIdentity", "TableType" -> "TABLE"], 
 SQLTable["V", "TableType" -> "TABLE"], 
 SQLTable["written_by", "TableType" -> "TABLE"], 
 SQLTable["OTriggered", "TableType" -> "TABLE"], 
 SQLTable["sung_by", "TableType" -> "TABLE"], 
 SQLTable["OUser", "TableType" -> "TABLE"], 
 SQLTable["followed_by", "TableType" -> "TABLE"], 
 SQLTable["OFunction", "TableType" -> "TABLE"], 
 SQLTable["E", "TableType" -> "TABLE"], 
 SQLTable["ORIDs", "TableType" -> "TABLE"], 
 SQLTable["ORole", "TableType" -> "TABLE"], 
 SQLTable["_studio", "TableType" -> "TABLE"], 
 SQLTable["ORestricted", "TableType" -> "TABLE"], 
 SQLTable["OSchedule", "TableType" -> "TABLE"]}

SQLExecute[conn, "SELECT FROM sung_by"]

Java::excptn: A Java exception occurred: java.sql.SQLFeatureNotSupportedException
    at com.orientechnologies.orient.jdbc.OrientJdbcConnection.prepareStatement(OrientJdbcConnection.java:273)
    at com.wolfram.databaselink.SQLStatementProcessor.processSQLStatement(SQLStatementProcessor.java:50).

$Failed

Tested with Mathematica 10.3 on Windows 10 Virtual Machine

[robfrank]

You're right. I'll try to fix it on Monday. Every detailed issue report is welcome.

[healiseu]

@robfrank 👍

[robfrank]

I've implemented mode methods, hope it is enough. Available from 2.1.13

[healiseu]

@robfrank I tested JDBC with OrientDB 2.1.13 in Mathematica 10.3
to continue the example above

v13 = SQLExecute[conn, "SELECT FROM 13:0"];

v13[[1]][[1]]
"V#9:1{song_type:cover,name:HEY BODIDDLEY,type:song,performances:5,out_followed_by:[size=5],out_written_by:[size=1],out_sung_by:[size=1],in_followed_by:[size=4]} v6"

v13[[1]][[2]]
"V#9:8{in_sung_by:[size=-1],in_written_by:[size=4],name:Garcia,type:artist} v8"

This response is no good....
If you try the OrientDB HTTP API with my DBAPI package in Mathematica this is what you get

<< DBAPI`OrientDB`
SetOptions[ODBapi, db -> "GratefulDeadConcerts"];
response = ODBapi[com -> "getRecords", class -> "13:0"]
{{"result":[{"@type":"d","@rid":"#13:0","@version":1,"@class":"sung_by","out":"#9:1","in":"#9:8","@fieldTypes":"out=x,in=x"}]},200}
Normal@ODBgetDataset@response
{<|"@type" -> "d", "@rid" -> "#13:0", "@version" -> 1, "@class" -> "sung_by", "out" -> "#9:1", "in" -> "#9:8", "@fieldTypes" -> "out=x,in=x"|>}

Therefore, the "out" and "in" nodes should not be expanded, and it is better if the output is in Mathematica List of Rules format. You return strings inside a list.

You do also have a problem when you execute the following

Short@SQLExecute[conn, "SELECT FROM V"]
{{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},<<773>>,{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}}

and you still get an error when you execute

SQLExecute[conn, "SELECT FROM 9:1"]
Java::excptn: A Java exception occurred: java.lang.NullPointerException at com.orientechnologies.orient.jdbc.OrientJdbcResultSetMetaData.getColumnType(OrientJdbcResultSetMetaData.java:193)    at com.wolfram.databaselink.SQLStatementProcessor.getColumnTypes(SQLStatementProcessor.java:615) at com.wolfram.databaselink.SQLStatementProcessor.getAllResultData(SQLStatementProcessor.java:471) at com.wolfram.databaselink.SQLStatementProcessor.processSQLStatement(SQLStatementProcessor.java:291).
$Failed

I am afraid your JDBC at this stage is unreliable and the format of response you get from the execution of statements is not good.
Best luck with an improved version....

[cguerin]

Yeah, I just tested opening an OrientDB database with the 2.1.13 JDBC driver using DBVisualizer and its unfortunately unreliable at getting field names.

When you open the JDBC connection DBVisualizer can see the tables, but DBVisualizer doesn’t know what they’re named.

If you write…. Select * you have a 50/50 chance of getting any field names or columns back.

I had one table successfully give me 2 of 3 fields back, the other tables not much luck, in general if lucky got the first column

Suggestion to an OrientDB contributing programmer — try it out with DBVisualizer or Pentaho or some tool that uses JDBC and see what happens!

Sorry I don’t have time to go investigate further myself ;-(

On Mar 16, 2016, at 5:32 AM, healiseu notifications@github.com wrote:

@robfrank https://github.com/robfrank I tested JDBC with OrientDB 2.1.13 in Mathematica 10.3
to continue the example above

v13 = SQLExecute[conn, "SELECT FROM 13:0"];

v13[[1]][[1]]
"V#9:1{song_type:cover,name:HEY BODIDDLEY,type:song,performances:5,out_followed_by:[size=5],out_written_by:[size=1],out_sung_by:[size=1],in_followed_by:[size=4]} v6"

v13[[1]][[2]]
"V#9:8{in_sung_by:[size=-1],in_written_by:[size=4],name:Garcia,type:artist} v8"
This response is no good....
If you try the OrientDB HTTP API with my DBAPI package in Mathematica this is what you get

<< DBAPIOrientDB
SetOptions[ODBapi, db -> "GratefulDeadConcerts"];
response = ODBapi[com -> "getRecords", class -> "13:0"]
{{"result":[{"@type":"d","@Rid":"#13:0","@Version":1,"@Class":"sung_by","out":"#9:1","in":"#9:8","@fieldTypes":"out=x,in=x"}]},200}
Normal@ODBgetDataset@response
{<|"@type" -> "d", "@Rid" -> "#13:0", "@Version" -> 1, "@Class" -> "sung_by", "out" -> "#9:1", "in" -> "#9:8", "@fieldTypes" -> "out=x,in=x"|>}
Therefore, the "out" and "in" nodes should not be expanded, and it is better if the output is in Mathematica List of Rules format. You return strings inside a list.

You do also have a problem when you execute the following

Short@SQLExecute[conn, "SELECT FROM V"]
{{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},<<773>>,{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{},{}}
and you still get an error when you execute

SQLExecute[conn, "SELECT FROM 9:1"]
Java::excptn: A Java exception occurred: java.lang.NullPointerException at com.orientechnologies.orient.jdbc.OrientJdbcResultSetMetaData.getColumnType(OrientJdbcResultSetMetaData.java:193) at com.wolfram.databaselink.SQLStatementProcessor.getColumnTypes(SQLStatementProcessor.java:615) at com.wolfram.databaselink.SQLStatementProcessor.getAllResultData(SQLStatementProcessor.java:471) at com.wolfram.databaselink.SQLStatementProcessor.processSQLStatement(SQLStatementProcessor.java:291).
$Failed
—
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub #5797 (comment)

[robfrank]

Thank you very much. I will work to improve the JDBC in the next weeks, hoping to reach a better point of compatibility with the standard.