Hi,
I'm executing a conditional SQL statement (IF flowvar == 'string' statement1 ELSE statement2) in a loop against a SQLSERVER back-end, by using the sqljdbc4.jar driver.
The statement itself is valid, as can be seen when I copy, paste and run it in a database browser. But the Database Reader node complains about there being an incorrect syntax near the keyword 'IF' - see first exception below. But when I untick the 'skips configure' option, no exception is thrown, and the node can be executed correctly.
However, when I execute the node in a loop, the second exception below is thrown. Well it's not an exception - it's is KNIME node error. The original exception might just be a JDBC bug, but why does the node bomb out in the loop with a 'CODING PROBLEM' error?
Thanks
Gideon
ERROR1:
----------------
DEBUG DatabaseConnectionSettings Opening database connection to "jdbc:sqlserver://<server>:1433;databaseName=<database>"...
DEBUG DatabaseReaderConnection Executing SQL statement as executeQuery: SELECT * FROM (IF ('' = 'HU')
SELECT
t.thutypenr AS Natcode,
t.thuvehtype AS VehicleTypeCd
FROM
typehu t
WHERE
t.thumarket = ''
ELSE
SELECT
t.typnatcode AS natcode,
t.typvehtype AS vehicletypecd
FROM
type t
WHERE
t.typmarket = '') table_14218964 WHERE 1 = 0
WARN Database Reader com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.
DEBUG Database Reader com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.
org.knime.core.node.InvalidSettingsException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.
at org.knime.base.node.io.database.DBReaderNodeModel.configure(DBReaderNodeModel.java:233)
at org.knime.core.node.NodeModel.configure(NodeModel.java:1040)
at org.knime.core.node.NodeModel.configureModel(NodeModel.java:984)
at org.knime.core.node.Node.invokeNodeModelConfigure(Node.java:1668)
at org.knime.core.node.Node.configure(Node.java:1628)
at org.knime.core.node.workflow.SingleNodeContainer.nodeConfigure(SingleNodeContainer.java:468)
at org.knime.core.node.workflow.SingleNodeContainer.configure(SingleNodeContainer.java:379)
...
ERROR2:
----------------
2013-03-07 13:23:29,908 DEBUG KNIME-Worker-430 NodeContainer : Parallel Chunks 0:96 has new state: EXECUTING
2013-03-07 13:23:29,908 DEBUG KNIME-Worker-430 LocalNodeExecutionJob : Database Reader 0:96:99:87 Start execute
2013-03-07 13:23:29,908 DEBUG KNIME-Worker-430 DatabaseReaderConnection : Executing SQL statement as executeQuery: IF ('HU' = 'HU')
SELECT
t.thutypenr AS Natcode,
t.thuvehtype AS VehicleTypeCd
FROM
typehu t
WHERE
t.thumarket = 'HU'
ELSE
SELECT
t.typnatcode AS natcode,
t.typvehtype AS vehicletypecd
FROM
type t
WHERE
t.typmarket = 'HU'
2013-03-07 13:23:29,917 DEBUG KNIME-Worker-430 DatabaseReaderConnection : Reading meta data from database ResultSet...
2013-03-07 13:23:29,917 DEBUG KNIME-Worker-430 DatabaseReaderConnection : Parsing database ResultSet...
...
2013-03-07 13:23:31,508 DEBUG KNIME-Worker-430 Buffer : Buffer file (E:\Users\GVA\AppData\Local\Temp\2\knime_container_20130307_7929201321345263376.bin.gz) is 0.292MB in size
2013-03-07 13:23:31,509 ERROR KNIME-Worker-430 Database Reader : CODING PROBLEM DataSpec generated by configure does not match spec after execution.
2013-03-07 13:23:31,509 ERROR KNIME-Worker-430 Database Reader : DataSpec generated by configure does not match spec after execution.
2013-03-07 13:23:31,509 INFO KNIME-Worker-430 LocalNodeExecutionJob : Database Reader 0:96:99:87 End execute (1 sec)
Hi Gideon,
I could imagine that the second error is caused by the fact that you generate columns "Natode" / "VehicleTypeCd" in one branch of your query and call them "natcode" / "vehicletypecd" in the other branch. As far as I remember, KNIME table specs are case sensitive. It could also be that the database column types are not identical for both cases.
Hope this helps,
Nils
Hi Nils,
thanks for your suggestion. I changed the branches to output identical columns, but I still get the error:
org.knime.core.node.InvalidSettingsException: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.
at org.knime.base.node.io.database.DBReaderNodeModel.configure(DBReaderNodeModel.java:233)
at org.knime.core.node.NodeModel.configure(NodeModel.java:1040)
at org.knime.core.node.NodeModel.configureModel(NodeModel.java:984)
at org.knime.core.node.Node.invokeNodeModelConfigure(Node.java:1668)
at org.knime.core.node.Node.configure(Node.java:1628)
at org.knime.core.node.workflow.SingleNodeContainer.nodeConfigure(SingleNodeContainer.java:468)
at org.knime.core.node.workflow.SingleNodeContainer.configure(SingleNodeContainer.java:379)
at org.knime.core.node.workflow.WorkflowManager.configureSingleNodeContainer(WorkflowManager.java:5020)
at org.knime.core.node.workflow.WorkflowManager.configureNodeAndPortSuccessors(WorkflowManager.java:5195)
at org.knime.core.node.workflow.WorkflowManager.configureNodeAndSuccessors(WorkflowManager.java:5132)
at org.knime.core.node.workflow.WorkflowManager.loadNodeSettings(WorkflowManager.java:1403)
at org.knime.core.node.workflow.NodeContainer.applySettingsFromDialog(NodeContainer.java:932)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.doApply(WrappedNodeDialog.java:547)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.doOK(WrappedNodeDialog.java:467)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.access$6(WrappedNodeDialog.java:464)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog$6.widgetSelected(WrappedNodeDialog.java:398)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:240)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
at org.eclipse.jface.window.Window.open(Window.java:801)
at org.knime.workbench.ui.wrapper.WrappedNodeDialog.open(WrappedNodeDialog.java:187)
at org.knime.workbench.editor2.editparts.NodeContainerEditPart.openNodeDialog(NodeContainerEditPart.java:803)
at org.knime.workbench.editor2.editparts.NodeContainerEditPart.openDialog(NodeContainerEditPart.java:742)
at org.knime.workbench.editor2.editparts.NodeContainerEditPart.performRequest(NodeContainerEditPart.java:335)
at org.eclipse.gef.tools.SelectEditPartTracker.performOpen(SelectEditPartTracker.java:194)
at org.eclipse.gef.tools.SelectEditPartTracker.handleDoubleClick(SelectEditPartTracker.java:137)
at org.eclipse.gef.tools.AbstractTool.mouseDoubleClick(AbstractTool.java:1069)
at org.eclipse.gef.tools.SelectionTool.mouseDoubleClick(SelectionTool.java:527)
at org.eclipse.gef.EditDomain.mouseDoubleClick(EditDomain.java:231)
at org.eclipse.gef.ui.parts.DomainEventDispatcher.dispatchMouseDoubleClicked(DomainEventDispatcher.java:291)
at org.eclipse.draw2d.LightweightSystem$EventHandler.mouseDoubleClick(LightweightSystem.java:518)
at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:195)
at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4165)
at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3754)
at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2701)
at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2665)
at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2499)
at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:679)
at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:668)
at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
at org.knime.product.rcp.KNIMEApplication.start(KNIMEApplication.java:128)
at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:344)
at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:622)
at org.eclipse.equinox.launcher.Main.basicRun(Main.java:577)
at org.eclipse.equinox.launcher.Main.run(Main.java:1410)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'IF'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:792)
at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:689)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeQuery(SQLServerStatement.java:616)
at org.knime.core.node.port.database.DatabaseReaderConnection.getDataTableSpec(DatabaseReaderConnection.java:225)