SQLGrinder with Virtuoso?

[Wolfman]

I was wondering if SQLGrinder worked with Virtuoso's ODBC and/or JDBC connections to its SQL db.

http://www.openlinksw.com/virtuoso

Which driver should I install to connect to them?

thanks,
Wolfman

[michaeldupuis]

Hm, by the looks of the information on their site, their JDBC driver might be a type 3 driver, not a type 4, which is a little more complicated to get working, if it's even possible.

In order to use a type 3 driver, you'd have to find both their JDBC driver jar file, and the library it uses. The JDBC jar file AND the native library could then go into ~/Library/Java/Extensions.

You'd then have to add an entry for the driver to preferences, there is a FAQ here that describes that.

I'm not sure if this will all work, and their site information makes that hard to determine.

-- Michael

[Wolfman]

ok, you have me on a roll now... I found this on the site:

The Virtuoso Drivers for JDBC are available in "jar" file formats for the JDBC 1.x, JDBC 2.x and JDBC 3.x JDBC specifications. These are Type 4 Drivers implying that utilization is simply a case of adding the relevant "jar" file to your CLASSPATH and then providing an appropriate JDBC URL format in order to establish a JDBC session with a local or remote Virtuoso server. It is important to note that when you make a JDBC connection to a Virtuoso Server, you do also have access to Native and External Virtuoso tables. Thus, you actually have a type 4 JDBC Driver for any number of different database types that have been linked into Virtuoso.

now, forgive my ignorance, I think I can find the JDBC URL format and get that working (yeah, right), but how exactly do I:

adding the relevant "jar" file to your CLASSPATH

asuming that the jar file I want to use is:

Driver Name Java Package "jar" File Archive Default Location Java Version
virtuoso.jdbc3.Driver virtuoso.jdbc3 virtjdbc3.jar <virtuoso installation directory>\jdk13 Java 1.4

and the jar file (already) exists in /Library/Java/Extensions (which is where it should be I believe.)

where and how exactly do I add the jar to my CLASSPATH?

thanks,
Wolfman

[Wolfman]

(I take it from your reply that ODBC connectivity isn't possible? I've confirmed that I have their ODBC connection working, but I have nothing to talk to its ODBC port

[Wolfman]

I seem to have figured out the connection as far as the SQL Editor goes, but I can't get the Schema Browser to work... it appears to make the connection, but never stops the barber pole spinner... it seems to get stuck after mentioning something about building schema view...

nothing appears in the Messages window. I did notice a lot of noise in the console window, though:

2004-10-11 18:25:52.667 SQLGrinder[512] java/lang/AbstractMethodError

Stack Trace:

java.lang.AbstractMethodError

at com.apple.cocoa.application.NSTableView.reloadData(Native Method)

at BrowserWindowController.createAndSetTableStorageObjects(Unknown Source)

at BrowserWindowController.windowDidLoad(Unknown Source)

at com.apple.cocoa.application.NSWindowController.window(Native Method)

at AppDelegate.newSchemaBrowser(Unknown Source)

java.lang.NoClassDefFoundError: java/sql/ParameterMetaData

at virtuoso.jdbc3.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:904)

at virtuoso.jdbc3.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:794)

at virtuoso.jdbc3.VirtuosoDatabaseMetaData.getTables(VirtuosoDatabaseMetaData.java:1877)

at com.advenio.sqlgrinder.schema.DatabaseSchema.loadMetaData(Unknown Source)

at com.advenio.sqlgrinder.schema.DatabaseSchema.<init>(Unknown Source)

at SchemaBrowserThread.run(Unknown Source)

2004-10-11 18:25:54.539 SQLGrinder[512] java/lang/NullPointerException

Stack Trace:

java.lang.NullPointerException

at BrowserWindowController.updateAfterSetDatabaseSchema(Unknown Source)

at SGApplication.sendEvent(Unknown Source)

[michaeldupuis]

I see you've defnitely made some progress.

Do you know if their drivers require JDK 1.4, or if they will work with 1.3? The current version of SQLGrinder is limited to using JDK 1.3 currently (I may remedy this soon), so that might cause a problem, if their driver is using something internally that isn't in 1.3 (the AbstractMethodError makes me suspicious of that).

-- Michael

[Wolfman]

wow, sorry for the delay, I just popped the stack back to this problem.

you're right, I'm using 1.4 drivers. I have 1.2/1.3 drivers available, too; am I likely able to run both at once? (forgive the obvious Java newbie questions).

thanks!

[Wolfman]

I'm really, really close, but I can't seem to get SQLGrinder to talk to the right jdbc driver. Even though I defined the driver in SQLGrinder to use the 1.2/1.3 driver file -- virtuoso.jdbc2.Driver, I'm still seeing this in the console (which is calling the 1.4 driver -- virtuoso.jdbc3.Driver):

java.lang.NoClassDefFoundError: java/sql/ParameterMetaData
at virtuoso.jdbc3.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:904)
at virtuoso.jdbc3.VirtuosoConnection.prepareStatement(VirtuosoConnection.java:794)
at virtuoso.jdbc3.VirtuosoDatabaseMetaData.getTables(VirtuosoDatabaseMetaData.java:1877)
at com.advenio.sqlgrinder.schema.DatabaseSchema.loadMetaData(Unknown Source)
at com.advenio.sqlgrinder.schema.DatabaseSchema.<init>(Unknown Source)
at SchemaBrowserThread.run(Unknown Source)
2004-11-01 11:32:49.727 SQLGrinder[2401] java/lang/NullPointerException
Stack Trace:
java.lang.NullPointerException
at BrowserWindowController.updateAfterSetDatabaseSchema(Unknown Source)
at SGApplication.sendEvent(Unknown Source)

[michaeldupuis]

Hi,

As of 1.2.7, you SHOULD now be able to use the 1.4 drivers. Have you tried that yet? You said that you are pointing to a JDBC 2 driver, and that the error is a 1.4 class it cannot find, so it sounds like you haven't tried that yet.

-- Michael

[Wolfman]

that seems to work better now, I can see my schema. oddly, though, nothing gets fetched into the lower half of the schema browser when I select a table.

I checked the messages window and noticed this message, although I can't decipher it:

java.lang.NullPointerException
at com.advenio.sqlgrinder.schema.DatabaseSchema.getTableColumns(Unknown Source)
at BrowserWindowController.displayTableColumns(Unknown Source)
at BrowserWindowController.handleSelectedTabViewItem(Unknown Source)
at BrowserWindowController.browserClicked(Unknown Source)
at com.apple.cocoa.application.NSApplication.sendEvent(Native Method)
at SGApplication.sendEvent(Unknown Source)

[michaeldupuis]

That's a trace from the JDBC layer of SQLGrinder itself. It looks like the calls to getColumns isn't returning any or all of the expected information. If you send email to sqlgrinder @ advenio.com, I'll email you a java class that will dump the raw schema data. This should show if something isn't getting returned from the JDBC driver, or it there is a problem with the JDBC layer itself. While the SQLGrinder JDBC layer SHOULD be OK, there seem to be weird cases for every driver that need to be worked around.

-- Michael