Apoc.load.jdbc problem querying advantage database

apoc

(Paul Drangeid) #1

Problem is LIKELY the quite old implementation that SAP is using in their jdbc driver, but unfortunately I think I'm stuck with it.

Using Advantage JDBC Driver 11.1

driver loads ok, and I am querying the database, but I suspect APOC is using a much newer JAVA method than this old JDBC driver supports. Following is my query attempt and the resulting error message:

CALL apoc.load.driver("com.extendedsystems.jdbc.advantage.ADSDriver")
call apoc.load.jdbc('jdbc:extendedsystems:advantage://commitcrm:6262;catalog=//commitcrm.mysite.com/CommitCRM/Db','SELECT source FROM Tickets WHERE source is not null') YIELD row
return row

Neo.ClientError.Procedure.ProcedureCallFailed

Neo.ClientError.Procedure.ProcedureCallFailed: Failed to invoke procedure apoc.load.jdbc: Caused by: java.lang.AbstractMethodError: Method com/extendedsystems/jdbc/advantage/ADSResultSet.isClosed()Z is abstract

Any suggestions, or am I stuck because of this legacy JDBC driver?


(Michael Hunger) #2

Hmm seems this JDBC driver doesn't implement some APIs.
A little bit hard to fix but I can have a look.

Can you test this one? https://www.dropbox.com/s/ebyuwwn3dhesufq/apoc-3.4.0.3-test-jdbc.jar?dl=0


(Paul Drangeid) #3

That did the trick!

I can do some further testing... This will allow me to do realtime queries to that platform without waiting for our nightly exports into our SQL database.

Thank you.


(Michael Hunger) #4

Should be in the latest release 3.4.0.4


(Paul Drangeid) #5

It looks like some small changes in functionality.

In the TEST version I was using, when I queried DATE fields, the returned values were automatically converted into the EPOCH:
1396571128137
1414964763000
1414964763000

In the new version the value being returned is the original STRING from the advantage DB:
"2014-11-02T15:46:03"
"2014-11-02T15:46:03"
"2014-11-02T15:46:03"

Which is fine, I CAN go add conversion routines into my scripts, but the auto-conversion was actually quite convenient! I'm not sure what was the intended output.

Thanks!


(Paul Drangeid) #6

I can confirm this now. I discovered this because I was replacing an older dev box (3.4.7) running your test 3.4.0.3 test version of APOC that you compiled for me to fix ADVANTAGE JDBC (thank-you by the way!).

And I build a new test box on 3.4.9 and I put the new 3.4.0.3 APOC in, and that's where the change occurs.
I'm pretty sure that the Advantage database is storiing the date in the string format, and I suspect the test version of 3.4.0.3 was auto-converting to the EPOCH in ms for the date field. (which is actually quite helpful!), while the new 3.4.0.3 version is probably returning the queried field unconverted -- which I am guessing was actually the desired behavior. Maybe I was enjoying the benefits of an unintended bug!

I can't verify for sure, because I don't have a browser/explorer tool handy to look through the original Advantage database.

Thanks Michael! Please let me know if I can help test or validate anything. For now I'm going to continue using the 3.4.0.3 test version because it affords me to be lazy for a few days, and not go fix my CYPHER to add the conversion process after the JDBC query.


(Michael Hunger) #7

Please try the recently released 3.4.0.4 version.


(Paul Drangeid) #8

I think this happened again in a recent update to apoc. I'm again getting unconverted (original STRING formatted) dates returned, and the previous APOC was converting these to EPOC automatically.

I can certainly change my code to convert these back to EPOC using apoc.date.parse, but not sure what should be the expected behavior.
My test machine is running APOC 3.5.0.1 and running on Neo4j 3.5.2

edit -- I was going to test to see if going back to APOC 3.4.0.4 would fix it, but I can't get neo4j 3.5.2 to start with apoc 3.4.0.4 plugin...

Thanks!


(Michael Hunger) #9

Did you see the config change which is also in the docs:

https://neo4j-contrib.github.io/neo4j-apoc-procedures/#_load_jdbc_format_date


(Paul Drangeid) #10

Ah - that's new... I think my issue is actually that the test version you sent me (to work-around those "old-as-dirt" api methods that the advantage jdbc driver uses) gave me an added bonus feature (it auto-converted the datetime format into the epoch before returning the value). Which was handy, but it seems I can just as easily do this now with datetime(row.CREATEDATE).epochMillis, so that's a pretty simple change in my code.

Thanks again for the follow-up Michael!