Hello,
I tried to use sys.objects and sysobjects in SQL query in Database Reader Node. But the following error appeares “ERROR Database Reader Execute failed: Operation not yet supported”. The SQL query which I used: “SELECT TOP 10 * FROM sys.objects”. Database: MS SQL Server. Driver: sun.jdbc.odbc.JdbcOdbcDriver. When I used the same queries in Database Connector node no errors appeares, but no any data returned. So system views sys.objects and sysobjects can not be accessible via KNIME. Please advise.
Hi Simulyant,
in your case the Database Reader Node performs the following query to retrieve the column metadata from the database without retrieving all data:
select * from (SELECT TOP 10 * FROM sys.objects) where 1= 0
it might be that SQL Server does not support the TOP command in sub queries. Can you try to execute the sub query using another db client to check if that is the problem?
The Database Connector does not create a sub query. But once you use the Database Connection Reader to retrieve the data from the db the same exception should be thrown since it also uses sub queries to retrieve the data.
Tobias
Hello, Tobias,
This issue does not connected with “TOP” command. The query “SELECT * FROM sys.objects” in Database Reader produced error “ERROR Database Reader Execute failed: Operation not yet supported”. In Database Connector this query generates no errors, but resultset is empty. System view sys.objects is not empty. I can check it via Microsoft Mangement Studio. So, it seems, neither Database Reader, not Database Connector can not read from system views.
Hi all,
In order to access system views, you have to use a different syntax within the Database URL field such as “jdbx:odbc:sys”, where sys is your pre-defined database name. Within the SQL query you then just need to enter the table you want to read. I have tried the Database Connector node and it works fine. The ERROR you get, tells me that SQL query that have been entered or generated by this node is not support by this database driver. Can you please post some more details to find out what causes the actual problem. Thanks, Thomas
Hello,
I use node “Database Reader”. Database driver: sun.jdbc.odbc.JdbcOdbcDriver. Database URL: jdbc:odbc:UserTvr (UserTvr is a ODBC alias for MS SQL database). SQL Statement: SELECT * FROM sys.objects.
Console log DEBUG level: “
DEBUG NodeContainerEditPart Database Reader 0:4:4 (CONFIGURED)
DEBUG WorkflowContextMenuProvider Building up context menu…
DEBUG WorkflowContextMenuProvider selected edit part: NodeContainerEditPart( Database Reader 0:4:4 (CONFIGURED) )
DEBUG WorkflowContextMenuProvider adding open node-view action(s) to context menu…
DEBUG WorkflowContextMenuProvider adding open port-view action(s) to context menu…
DEBUG ExecuteAction Creating execution job for 1 node(s)…
DEBUG NodeContainer Database Reader 0:4:4 has new state: MARKEDFOREXEC
DEBUG NodeContainer Database Reader 0:4:4 has new state: QUEUED
DEBUG KnimeResourceNavigator state changed to EXECUTING
DEBUG NodeContainer Workflow Manager 0:4 has new state: EXECUTING
DEBUG NodeContainer Workflow Manager 0 has new state: EXECUTING
DEBUG WorkflowManager Database Reader 0:4:4 doBeforeExecute
DEBUG NodeContainer Database Reader 0:4:4 has new state: EXECUTING
DEBUG NodeContainer Workflow Manager 0:4 has new state: EXECUTING
DEBUG Database Reader Start execute
DEBUG Database Reader reset
DEBUG Database Reader clean output ports.
ERROR Database Reader Execute failed: Operation not yet supported
DEBUG Database Reader Execute failed: Operation not yet supported
java.lang.UnsupportedOperationException: Operation not yet supported
at sun.jdbc.odbc.JdbcOdbcResultSet.getNString(Unknown Source)
at org.knime.core.node.port.database.DatabaseReaderConnection$DBRowIterator.readNString(DatabaseReaderConnection.java:523)
at org.knime.core.node.port.database.DatabaseReaderConnection$DBRowIterator.next(DatabaseReaderConnection.java:380)
at org.knime.core.node.ExecutionContext.createBufferedDataTable(ExecutionContext.java:148)
at org.knime.core.node.port.database.DatabaseReaderConnection.createTable(DatabaseReaderConnection.java:174)
at org.knime.base.node.io.database.DBReaderNodeModel.execute(DBReaderNodeModel.java:79)
at org.knime.core.node.NodeModel.execute(NodeModel.java:556)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:410)
at org.knime.core.node.Node.execute(Node.java:653)
at org.knime.core.node.workflow.SingleNodeContainer.executeNode(SingleNodeContainer.java:587)
at org.knime.core.node.workflow.SingleNodeContainer.access$1(SingleNodeContainer.java:561)
at org.knime.core.node.workflow.SingleNodeContainer$1.run(SingleNodeContainer.java:446)
at org.knime.core.node.workflow.JobRunnable.run(JobRunnable.java:43)
at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.util.concurrent.FutureTask$Sync.innerRun(Unknown Source)
at java.util.concurrent.FutureTask.run(Unknown Source)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:98)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:166)
DEBUG Database Reader reset
DEBUG Database Reader clean output ports.
DEBUG Database Reader Configure succeeded. (Database Reader)
DEBUG WorkflowManager Database Reader 0:4:4 doAfterExecute - failure
DEBUG NodeContainer Database Reader 0:4:4 has new state: CONFIGURED
DEBUG KnimeResourceNavigator state changed to CONFIGURED
DEBUG NodeContainer Workflow Manager 0:4 has new state: CONFIGURED
DEBUG NodeContainer Workflow Manager 0 has new state: IDLE
DEBUG NodeContainer Workflow Manager 0:4 has new state: CONFIGURED
”
This query “SELECT * FROM sys.objects” in Microsoft SQL Server Management Studio for the same database works fine (only top 10 rows below):
name object_id principal_id schema_id parent_object_id type type_desc create_date modify_date is_ms_shipped is_published is_schema_published
sysrscols 3 NULL 4 0 S SYSTEM_TABLE 2008-10-23 14:25:41.720 2008-10-23 14:25:41.740 1 0 0
sysrowsets 5 NULL 4 0 S SYSTEM_TABLE 2008-09-14 15:29:39.540 2008-10-23 14:25:41.753 1 0 0
sysallocunits 7 NULL 4 0 S SYSTEM_TABLE 2008-09-14 15:29:39.540 2008-10-23 14:25:42.220 1 0 0
sysfiles1 8 NULL 4 0 S SYSTEM_TABLE 2000-08-06 01:29:12.717 2000-08-06 01:29:12.717 1 0 0
syspriorities 17 NULL 4 0 S SYSTEM_TABLE 2008-10-23 14:25:48.427 2008-10-23 14:25:48.440 1 0 0
sysfgfrag 19 NULL 4 0 S SYSTEM_TABLE 2008-10-23 14:25:41.373 2008-10-23 14:25:41.457 1 0 0
sysphfg 23 NULL 4 0 S SYSTEM_TABLE 2008-10-23 14:25:41.470 2008-10-23 14:25:41.477 1 0 0
sysprufiles 24 NULL 4 0 S SYSTEM_TABLE 2008-10-23 14:25:41.470 2008-10-23 14:25:41.507 1 0 0
sysftinds 25 NULL 4 0 S SYSTEM_TABLE 2008-09-14 15:29:39.913 2008-10-23 14:25:41.187 1 0 0
sysowners 27 NULL 4 0 S SYSTEM_TABLE 2008-09-14 15:29:39.900 2008-09-14 15:29:39.900 1 0 0
OS: Windows XP SP3 Rus
Eclipse: Version: 3.3.2
KNIME: 2.0.3 SDK
Java: 1.6.0_10-b33
Database: Microsoft SQL Server
Hi, thanks for these details. The problem is that we access char objects using “readNString” method (instead of getString or getBytes) which is not supported by all database driver implementations. This is really a bug which is going to be fixed in our next release. Btw, are you using the latest MS SQL driver which is sqljdbc4.jar (build 25.03.2009). Sorry, currently I don’t see any real workaround for that.
Regards, Thomas
Hello, gabrial.
Yes, I tried to use latest Microsoft JDBC driver. But it was failed cause to Windows Authentication. Our MS SQL databases uses Windows Authentication. And I can not use Windows Authentication with MS JDBC.
BR, Valery Otryvankin
Hi,
Can anyone tell me how to connect KNIME to MS SQL Server 2000?
I am struggling with it.
Regards,
Srinivas
You register the JDBC driver (sqljdbcXXX.jar) under File >> Preferences >> KNIME >> Database Driver and then use the Database Reader or Database Connector node to connect to the database. Here you need to select the driver: com.microsoft.sqlserver.jdbc.SQLServerDriver, and set the URL as: jdbc:sqlserver://<server>:<port>;databaseName=<name>.
Hi Gabriel,
thanks for your quick response but I have done this already and it doesn't work.
Generally when I use TOAD it connects to SQL Server 2000 DB via Windows Authentication. But how can I tell KNIME it should use the Windows Authentication?
Regards, Srinivas
You manually have to enter user/password in the Database nodes or adjust your credentials available on workflow level (right-click on the project). Windows authentication is currently not possible; but I guess, you can also registered a database ID in the ODBC-JDBC system settings and then just reference to the ID from KNIME using the default jdbc/odbc Java driver.
Yes, that is excatly what I have done. Thanks for your hlp and support.
Best wishes. Srinivas