Database Writer Oracle table from csv

KNIME Analytics Platform
1

I am a new user.
I would like to register data into an oracle database 9.2 version stayng on Windows XP server.
I have generated a chemistry flow with properties calculation. Last nodes were inserted for chemical structures column deletion ( I am going to write inside a table directlty linked with a primary key to the chemical table but not containing chem stru) and very last for CSV file production. Now a I have a trivial problem. Now How to link the CSV node generator to the database writer withouty a output?
Sorry for the silly question. Forgive me but I am a chemist not a person from IT
Bye
Carlo

:?

2

Hi Carlo,
The CSV Writer node does not have a output, because the input table is written to the file as specified in the node dialog. The output of this node would be same as the input. But you can directly connect the output of the last node (before the CSV Writer node) to the Database Writer node which will create a new table in your Oracle database. Hope this answers your question?
Cheers, Thomas

3

Thank you for your entry.
I am now able to write a new Oracle table with molecule properties.
but my aim was to update an existing Oracle table. Even clicking the append flag, I am not able to add new rows.
I was successful in generating a new table with the same Columns, datatype, size and scale of the original table.
If I try to append data to the new table, I am unsuccessful too also using the same data format as that used for generating the table copy.
Any suggestion?

4

Hi, do you get any error/warning at the Database Writer node, either as icon on the node itself or in the Console log. Can you please have look if there any and if so, can you please post (or send me) the interesting parts of the knime.log (path /workspace/.metadata/knime). To check if the Database Writer can append the same data structure, you could create a new table (don't check the append flag), and then append the same data again (check the append flag). Does this work in your scenario?
Regards, Thomas

5

ERROR Database Writer Execute failed
ERROR Database Writer Invalid column type
!ENTRY org.eclipse.core.resources 2 10035 2008-12-09 16:01:50.965
!MESSAGE A workspace crash was detected. The previous session did not exit normally.

!ENTRY org.eclipse.ui 4 4 2008-12-09 16:02:03.340
!MESSAGE Invalid Menu Extension (Path is invalid): org.eclipse.ui.edit.text.openExternalFile

!ENTRY org.eclipse.ui 4 4 2008-12-09 16:02:03.340
!MESSAGE Invalid Menu Extension (Path is invalid): converstLineDelimitersTo

!ENTRY org.eclipse.ui 4 4 2008-12-09 16:02:03.340
!MESSAGE Invalid Menu Extension (Path is invalid): org.eclipse.ui.edit.text.delimiter.macOS9

!ENTRY org.eclipse.ui 4 4 2008-12-09 16:02:03.340
!MESSAGE Invalid Menu Extension (Path is invalid): org.eclipse.ui.edit.text.delimiter.unix

!ENTRY org.eclipse.ui 4 4 2008-12-09 16:02:03.340
!MESSAGE Invalid Menu Extension (Path is invalid): org.eclipse.ui.edit.text.delimiter.windows

!ENTRY org.eclipse.ui 4 4 2008-12-09 16:02:03.340
!MESSAGE Invalid Menu Extension (Path is invalid): selectWorkingSets

!ENTRY org.eclipse.ui 4 4 2008-12-09 16:02:03.340
!MESSAGE Invalid Menu Extension (Path is invalid): org.eclipse.ui.edit.text.gotoLastEditPosition

!ENTRY org.eclipse.update.core 4 0 2008-12-09 16:02:13.684
!MESSAGE Error processing update site mirror. [Attribute name "nowrap" associated with an element type "td" must be followed by the ' = ' character.]
!STACK 0
org.xml.sax.SAXParseException: Attribute name "nowrap" associated with an element type "td" must be followed by the ' = ' character.
at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(DOMParser.java:264)
at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(DocumentBuilderImpl.java:292)
at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:146)
at org.eclipse.update.core.model.DefaultSiteParser.getMirrors(DefaultSiteParser.java:820)
at org.eclipse.update.core.model.DefaultSiteParser.processSite(DefaultSiteParser.java:505)
at org.eclipse.update.core.model.DefaultSiteParser.handleInitialState(DefaultSiteParser.java:387)
at org.eclipse.update.core.model.DefaultSiteParser.startElement(DefaultSiteParser.java:196)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(AbstractSAXParser.java:533)
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(XMLNSDocumentScannerImpl.java:330)
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl$NSContentDispatcher.scanRootElementHook(XMLNSDocumentScannerImpl.java:779)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(XMLDocumentFragmentScannerImpl.java:1794)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:368)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:834)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:764)
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:148)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1242)
at javax.xml.parsers.SAXParser.parse(SAXParser.java:375)
at org.eclipse.update.core.model.DefaultSiteParser.parse(DefaultSiteParser.java:152)
at org.eclipse.update.core.model.SiteModelFactory.parseSite(SiteModelFactory.java:81)
at org.eclipse.update.internal.core.SiteURLFactory.createSite(SiteURLFactory.java:80)
at org.eclipse.update.internal.core.InternalSiteManager.createSite(InternalSiteManager.java:334)
at org.eclipse.update.internal.core.InternalSiteManager.createSite(InternalSiteManager.java:326)
at org.eclipse.update.internal.core.InternalSiteManager.createSite(InternalSiteManager.java:291)
at org.eclipse.update.internal.core.InternalSiteManager.attemptCreateSite(InternalSiteManager.java:222)
at org.eclipse.update.internal.core.InternalSiteManager.getSite(InternalSiteManager.java:162)
at org.eclipse.update.core.SiteManager.getSite(SiteManager.java:78)
at org.eclipse.update.search.UpdateSearchRequest.searchOneSite(UpdateSearchRequest.java:447)
at org.eclipse.update.search.UpdateSearchRequest.performSearch(UpdateSearchRequest.java:294)
at org.eclipse.update.ui.UpdateJob.runUpdates(UpdateJob.java:207)
at org.eclipse.update.ui.UpdateJob.run(UpdateJob.java:168)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:58)

!ENTRY org.eclipse.update.core 4 0 2008-12-09 16:03:10.778
!MESSAGE Error processing update site mirror. [Attribute name "nowrap" associated with an element type "td" must be followed by the ' = ' character.]
!STACK 0
org.xml.sax.SAXParseException: Attribute name "nowrap" associated with an element type "td" must be followed by the ' = ' character.
at com.sun.org.apache.xerces.internal.parsers.DOMParser.parse(DOMParser.java:264)
at com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderImpl.parse(DocumentBuilderImpl.java:292)
at javax.xml.parsers.DocumentBuilder.parse(DocumentBuilder.java:146)
at org.eclipse.update.core.model.DefaultSiteParser.getMirrors(DefaultSiteParser.java:820)
at org.eclipse.update.core.model.DefaultSiteParser.processSite(DefaultSiteParser.java:505)
at org.eclipse.update.core.model.DefaultSiteParser.handleInitialState(DefaultSiteParser.java:387)
at org.eclipse.update.core.model.DefaultSiteParser.startElement(DefaultSiteParser.java:196)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.startElement(AbstractSAXParser.java:533)
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl.scanStartElement(XMLNSDocumentScannerImpl.java:330)
at com.sun.org.apache.xerces.internal.impl.XMLNSDocumentScannerImpl$NSContentDispatcher.scanRootElementHook(XMLNSDocumentScannerImpl.java:779)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl$FragmentContentDispatcher.dispatch(XMLDocumentFragmentScannerImpl.java:1794)
at com.sun.org.apache.xerces.internal.impl.XMLDocumentFragmentScannerImpl.scanDocument(XMLDocumentFragmentScannerImpl.java:368)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:834)
at com.sun.org.apache.xerces.internal.parsers.XML11Configuration.parse(XML11Configuration.java:764)
at com.sun.org.apache.xerces.internal.parsers.XMLParser.parse(XMLParser.java:148)
at com.sun.org.apache.xerces.internal.parsers.AbstractSAXParser.parse(AbstractSAXParser.java:1242)
at javax.xml.parsers.SAXParser.parse(SAXParser.java:375)
at org.eclipse.update.core.model.DefaultSiteParser.parse(DefaultSiteParser.java:152)
at org.eclipse.update.core.model.SiteModelFactory.parseSite(SiteModelFactory.java:81)
at org.eclipse.update.internal.core.SiteURLFactory.createSite(SiteURLFactory.java:80)
at org.eclipse.update.internal.core.InternalSiteManager.createSite(InternalSiteManager.java:334)
at org.eclipse.update.internal.core.InternalSiteManager.createSite(InternalSiteManager.java:326)
at org.eclipse.update.internal.core.InternalSiteManager.createSite(InternalSiteManager.java:291)
at org.eclipse.update.internal.core.InternalSiteManager.attemptCreateSite(InternalSiteManager.java:222)
at org.eclipse.update.internal.core.InternalSiteManager.getSite(InternalSiteManager.java:162)
at org.eclipse.update.core.SiteManager.getSite(SiteManager.java:78)
at org.eclipse.update.search.UpdateSearchRequest.searchOneSite(UpdateSearchRequest.java:447)
at org.eclipse.update.search.UpdateSearchRequest.performSearch(UpdateSearchRequest.java:294)
at org.eclipse.update.ui.UpdateJob.runUpdates(UpdateJob.java:207)
at org.eclipse.update.ui.UpdateJob.run(UpdateJob.java:168)
at org.eclipse.core.internal.jobs.Worker.run(Worker.java:58)
new table has been created but i was forbidden to append the same data there.
Hi carlo

6

Hi, thanks for this trace whereby only the first two lines of interest. In order to get some more detailed message, it would be helpful if you could got back into the KNIME workbench and change the log-level to DEBUG (go to: File -> Preferences -> KNIME/GUI). The ERROR message together with the stack trace (on DEBUG) will then appear in KNIME Console-Log. Thanks again, Thomas

7

gabriel wrote:
Hi, thanks for this trace whereby only the first two lines of interest. In order to get some more detailed message, it would be helpful if you could got back into the KNIME workbench and change the log-level to DEBUG (go to: File -> Preferences -> KNIME/GUI). The ERROR message together with the stack trace (on DEBUG) will then appear in KNIME Console-Log. Thanks again, Thomas

Hi: this is the lsat part of the debug messge:
ERROR Database Writer Execute failed
ERROR Database Writer Invalid column type
DEBUG Database Writer Execute failed
java.sql.SQLException: Invalid column type
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269)
at oracle.jdbc.driver.OracleStatement.get_internal_type(OracleStatement.java:6164)
at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:1316)
at org.knime.base.node.io.database.DBWriterConnection.writeData(DBWriterConnection.java:216)
at org.knime.base.node.io.database.DBWriterNodeModel.execute(DBWriterNodeModel.java:172)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:441)
at org.knime.core.node.Node.execute(Node.java:991)
at org.knime.core.node.workflow.NodeContainer$1.run(NodeContainer.java:1193)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:417)
at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:269)
at java.util.concurrent.FutureTask.run(FutureTask.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:126)
INFO Database Writer reset
DEBUG WorkflowEditor Workflow event triggered: WorkflowEvent [type=NodeConfigured;old=null;new=null;timestamp=Dec 9, 2008 5:58:47 PM]
DEBUG WorkflowRootEditPart WorkflowRoot: workflow changed, refreshing children/connections..
DEBUG WorkflowEditor Workflow event triggered: WorkflowEvent [type=NodeFinished;old=Database Writer(#16);new=Database Writer(#16);timestamp=Dec 9, 2008 5:58:47 PM]
DEBUG WorkflowRootEditPart WorkflowRoot: workflow changed, refreshing children/connections..
DEBUG WorkflowManager Workflow execution time: 4282 ms
DEBUG Buffer Closing input stream on "C:\DOCUME~1\riva.c\LOCALS~1\Temp\knime_container_20081209_38281.bin.gz", 1 remaining
DEBUG Buffer Closing input stream on "C:\DOCUME~1\riva.c\LOCALS~1\Temp\knime_container_20081209_38279.bin.gz", 1 remaining
I hope it would be helpful to you
Thanks again
Carlo

8

Hi, thanks again for your valuable help. We already have fixed this bug for the 2.0 release that will come out very soon. Regards, Thomas