Optiq errors when trying to setup access via JDBC

[Dan Keeley]

Hi

I decided to try out optiq today, and had a few issues.

First of all finding all the jars required to package everything together was a pain, I guess that can be done with maven somehow but I'm not that clear on that.

Anyway once I did have the jars together ( and stopped getting class not found errors ) i decided to try and connect SquirrelSQL to the samples.  Once I did that I got the error shown below.

I also tried with PDI - it also failed at the initialization step.  It did manage to report what features the driver supported though, but not whatever calls are made to support the "explore" dialog.  Showing I'm partway there.

I do have a particularly interesting use case to try out via PDI so is this likely to be possible?  PDI setup was just the usual generic database connection etc.

Here's the errors from squirrel:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: java.lang.RuntimeException: Error instantiating JsonCustomSchema(name=SALES)

at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:232)

at java.util.concurrent.FutureTask.get(FutureTask.java:91)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)

at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:441)

at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)

at java.util.concurrent.FutureTask.run(FutureTask.java:138)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:662)

Caused by: java.lang.RuntimeException: java.lang.RuntimeException: Error instantiating JsonCustomSchema(name=SALES)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:175)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)

... 6 more

Caused by: java.lang.RuntimeException: Error instantiating JsonCustomSchema(name=SALES)

at net.hydromatic.optiq.model.ModelHandler.visit(ModelHandler.java:110)

at net.hydromatic.optiq.model.JsonCustomSchema.accept(JsonCustomSchema.java:37)

at net.hydromatic.optiq.model.ModelHandler.visit(ModelHandler.java:71)

at net.hydromatic.optiq.model.ModelHandler.<init>(ModelHandler.java:63)

at net.hydromatic.optiq.jdbc.Driver$1.onConnectionInit(Driver.java:61)

at net.hydromatic.optiq.jdbc.UnregisteredDriver.connect(UnregisteredDriver.java:127)

at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)

at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)

... 8 more

Caused by: java.lang.NullPointerException

at net.hydromatic.optiq.impl.csv.CsvSchema.initialTables(CsvSchema.java:70)

at net.hydromatic.optiq.impl.java.MapSchema.initialize(MapSchema.java:121)

at net.hydromatic.optiq.model.ModelHandler.visit(ModelHandler.java:106)

... 15 more

Thanks,

Dan

[Julian Hyde]

On Oct 22, 2013, at 9:34 AM, Dan Keeley <d.e.k...@gmail.com> wrote:

Caused by: java.lang.NullPointerException

at net.hydromatic.optiq.impl.csv.CsvSchema.initialTables(CsvSchema.java:70)

Looks like File.listFiles returned a null array, which means that your directory does not exist. Try supplying the full path.

I'll fix optiq-csv to give a better error next time.

Julian

[Dan]

OK excellent thanks, that helps - I get a little further now. I had tried a full path in the connection url but the schema json only had a relative path. 

However now squirrel causes the stack below and moans about it not being a valid JDBC 3.0 driver?  PDI Still errors too. (Can get that trace if its useful?)

java.lang.RuntimeException: todo: implement this method

at net.hydromatic.optiq.jdbc.Helper.todo(Helper.java:40)

at net.hydromatic.optiq.jdbc.OptiqDatabaseMetaData.supportsSchemasInDataManipulation(OptiqDatabaseMetaData.java:301)

at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.supportsSchemasInDataManipulation(SQLDatabaseMetaData.java:339)

at net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectInfo.generateQualifiedName(DatabaseObjectInfo.java:197)

at net.sourceforge.squirrel_sql.fw.sql.DatabaseObjectInfo.<init>(DatabaseObjectInfo.java:77)

at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTreeModel$RootNode.createDbo(ObjectTreeModel.java:381)

at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTreeModel$RootNode.<init>(ObjectTreeModel.java:376)

at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTreeModel.createRootNode(ObjectTreeModel.java:271)

at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTreeModel.<init>(ObjectTreeModel.java:87)

at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTree.<init>(ObjectTree.java:133)

at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTreePanel.createGUI(ObjectTreePanel.java:906)

at net.sourceforge.squirrel_sql.client.session.mainpanel.objecttree.ObjectTreePanel.<init>(ObjectTreePanel.java:131)

at net.sourceforge.squirrel_sql.client.session.mainpanel.ObjectTreeTab.getComponent(ObjectTreeTab.java:83)

at net.sourceforge.squirrel_sql.client.gui.session.MainPanel.addMainPanelTab(MainPanel.java:202)

at net.sourceforge.squirrel_sql.client.gui.session.MainPanel.<init>(MainPanel.java:109)

at net.sourceforge.squirrel_sql.client.gui.session.MainPanelFactory.createMainPanel(MainPanelFactory.java:27)

at net.sourceforge.squirrel_sql.client.gui.session.SessionPanel.createGUI(SessionPanel.java:466)

at net.sourceforge.squirrel_sql.client.gui.session.SessionPanel.initialize(SessionPanel.java:116)

at net.sourceforge.squirrel_sql.client.gui.session.SessionInternalFrame.createGUI(SessionInternalFrame.java:123)

at net.sourceforge.squirrel_sql.client.gui.session.SessionInternalFrame.<init>(SessionInternalFrame.java:53)

at net.sourceforge.squirrel_sql.client.gui.WindowManager.createInternalFrame(WindowManager.java:360)

at net.sourceforge.squirrel_sql.client.mainframe.action.ConnectToAliasCommand$Runner.run(ConnectToAliasCommand.java:391)

at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)

at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:666)

at java.awt.EventQueue.access$400(EventQueue.java:81)

at java.awt.EventQueue$2.run(EventQueue.java:627)

at java.awt.EventQueue$2.run(EventQueue.java:625)

at java.security.AccessController.doPrivileged(Native Method)

at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)

at java.awt.EventQueue.dispatchEvent(EventQueue.java:636)

at net.sourceforge.squirrel_sql.client.Main$1.dispatchEvent(Main.java:99)

at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)

at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)

at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:178)

at java.awt.Dialog$1.run(Dialog.java:1044)

at java.awt.Dialog$3.run(Dialog.java:1096)

at java.security.AccessController.doPrivileged(Native Method)

at java.awt.Dialog.show(Dialog.java:1094)

at javax.swing.JOptionPane.showOptionDialog(JOptionPane.java:853)

at javax.swing.JOptionPane.showMessageDialog(JOptionPane.java:650)

at javax.swing.JOptionPane.showMessageDialog(JOptionPane.java:621)

at net.sourceforge.squirrel_sql.client.session.Session$3.run(Session.java:1045)

at java.awt.event.InvocationEvent.dispatch(InvocationEvent.java:209)

at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:666)

at java.awt.EventQueue.access$400(EventQueue.java:81)

at java.awt.EventQueue$2.run(EventQueue.java:627)

at java.awt.EventQueue$2.run(EventQueue.java:625)

at java.security.AccessController.doPrivileged(Native Method)

at java.security.AccessControlContext$1.doIntersectionPrivilege(AccessControlContext.java:87)

at java.awt.EventQueue.dispatchEvent(EventQueue.java:636)

at net.sourceforge.squirrel_sql.client.Main$1.dispatchEvent(Main.java:99)

at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:269)

at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:184)

at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:174)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:169)

at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:161)

at java.awt.EventDispatchThread.run(EventDispatchThread.java:122)

--
You received this message because you are subscribed to a topic in the Google Groups "optiq-dev" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/optiq-dev/Zbvg7qEeA_Y/unsubscribe.
To unsubscribe from this group and all of its topics, send an email to optiq-dev+...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Julian Hyde]

On Oct 22, 2013, at 11:07 AM, Dan <d.e.k...@gmail.com> wrote:

OK excellent thanks, that helps - I get a little further now. I had tried a full path in the connection url but the schema json only had a relative path. 

However now squirrel causes the stack below and moans about it not being a valid JDBC 3.0 driver?  PDI Still errors too. (Can get that trace if its useful?)

java.lang.RuntimeException: todo: implement this method

at net.hydromatic.optiq.jdbc.Helper.todo(Helper.java:40)

at net.hydromatic.optiq.jdbc.OptiqDatabaseMetaData.supportsSchemasInDataManipulation(OptiqDatabaseMetaData.java:301)

This happens each time someone tries out a new JDBC client -- they call methods that have not been implemented yet, and we give errors. This method is simple enough to implement, but squirrel doubtless needs other methods.

Can you go to OptiqDatabaseMetaData.java line 301, make the method return true rather than throw, re-compile optiq, and retry squirrel. After a few iterations, squirrel should work, and then you can send me a list of methods that need to be implemented.

Julian

[Dan]

OK, right so the methods are:

supportsSchemasInDataManipulation (as above)

supportsCatalogsInDataManipulation

supportsSchemasInTableDefinitions

getCatalogSeparator

supportsCatalogsInTableDefinitions

getDefaultTransactionIsolation 

At this point we can log on. However SQL still doesnt run. So the next one is:

getTimeDateFunctions

supportsStoredProcedures

getStringFunctions  (at this point i did getNumericFunctions too )

getSQLKeywords

getCatalogTerm

getSchemaTerm

getProcedureTerm

At this point I got stuck.  It still reports an error, but there is no exception or trace any more.

Just this message:

Error: exception while executing query
SQLState:  null
ErrorCode: 0
Logged by net.sourceforge.squirrel_sql.client.session.Session at 10/23/13 9:43 AM:
The driver being used for alias 'Optiq' is not JDBC 3.0 compliant.
You should consider getting a more recent version of this driver

So; Does that mean if those above returned the right values it would now work?

Dan

[Julian Hyde]

On Oct 23, 2013, at 1:46 AM, Dan <d.e.k...@gmail.com> wrote:

OK, right so the methods are:

<snip>

Thanks. I've logged https://github.com/julianhyde/optiq/issues/65. Add more details to this case when you have them. I'll implement them shortly. 

At this point I got stuck.  It still reports an error, but there is no exception or trace any more.

Just this message:

Error: exception while executing query
SQLState:  null
ErrorCode: 0
Logged by net.sourceforge.squirrel_sql.client.session.Session at 10/23/13 9:43 AM:
The driver being used for alias 'Optiq' is not JDBC 3.0 compliant.
You should consider getting a more recent version of this driver

That might be just a warning: see http://squirrel-sql.sourceforge.net/index.php?page=faq#faq8. But I'll investigate further.

Julian

[Julian Hyde]

On Oct 23, 2013, at 9:50 AM, Julian Hyde <julia...@gmail.com> wrote:

Thanks. I've logged https://github.com/julianhyde/optiq/issues/65. Add more details to this case when you have them. I'll implement them shortly. 

I’ve fixed all of the JDBC issues I could find. Squirrel runs quite nicely now.

Julian

[Dan]

Interesting, which version of squirrel did you use?  I'm still getting one more error, when i run a select count(*) from emps query

I do notice this one wasnt in the original list; thats why im wondering about squirrel versions.  So I've downloaded 3.5.0 ( was on 3.4 ) and I tried again and it still fails.  So I can only assume it must be the way I've setup the driver that means squirrel is making additional calls for me which it doesnt for you..??

java.lang.RuntimeException: todo: implement this method

at net.hydromatic.optiq.jdbc.Helper.todo(Helper.java:40)

at net.hydromatic.optiq.jdbc.OptiqDatabaseMetaData.supportsMultipleResultSets(OptiqDatabaseMetaData.java:233)

at net.sourceforge.squirrel_sql.fw.sql.SQLDatabaseMetaData.supportsMultipleResultSets(SQLDatabaseMetaData.java:1850)

at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.processQuery(SQLExecuterTask.java:416)

at net.sourceforge.squirrel_sql.client.session.SQLExecuterTask.run(SQLExecuterTask.java:220)

at net.sourceforge.squirrel_sql.fw.util.TaskExecuter.run(TaskExecuter.java:82)

at java.lang.Thread.run(Thread.java:662)

--

[Julian Hyde]

On Oct 24, 2013, at 2:00 AM, Dan <d.e.k...@gmail.com> wrote:

Interesting, which version of squirrel did you use?  I'm still getting one more error, when i run a select count(*) from emps query

java.lang.RuntimeException: todo: implement this method

at net.hydromatic.optiq.jdbc.Helper.todo(Helper.java:40)

at net.hydromatic.optiq.jdbc.OptiqDatabaseMetaData.supportsMultipleResultSets(OptiqDatabaseMetaData.java:233)

I built squirrel from source (git clone http://git.code.sf.net/p/squirrel-sql/git squirrel-sql-git).

Looks like you’re not using the latest Optiq. You need to build from source (git clone, mvn install) to get my fixes. The supportsMultipleResultSets method is now at line 231.

Julian

[Dan]

OK, I did a pull and an install, I must have missed something somehow.

I'll get back onto this tomorrow night.

--

[Dan]

OK yes it works; I just had woes with git i think!

Thanks, amazing stuff, I'll check this out with PDI now.  (As soon as it builds)

Dan