SDF Reader

[jrm1974]

Hi,

I am intending to use the “SDF Reader” node to extract the Mol block and additional data fields from an SDF file and insert it into an Oracle table (Oracle 12.2.0.1.0 / OracleDriver ojdbc8.jar) using the “DB Insert” node. While this works fine for the data fields, I cannot insert the Mol block into a CLOB or VARCHAR2 column. I am running into the following error:

ERROR DB Insert            0:8        Execute failed: Output type mapping is missing for column:
MOL (Molfile)

I mapped the Molfile column to CLOB, which did not solve the issue.

Any ideas, what I might be doing wrong?

Thank you for your help.

[Alice_Krebs]

Hi @jrm1974

Welcome to the forum!
Unfortunately chemistry data types are not supported in the type mapping. However we have a ticket open for this, so it will be possible at some point in the future. Your request is added.
For now, the solution would be to convert the column to string before writing it to the database.

Hope that helps, sorry I don’t have a better answer.
Best,
Alice

[jrm1974]

Hi Alice,

thank you very much for your reply. I tried coverting the MOL block to string before, but unfortunately that did not work either.

I used the node “Column Rename” and removed the type mapping from the node “DB Insert”

Doing so, I received a different error:

ERROR DB Insert 0:8 Execute failed: Output type mapping is missing for column:
MOL (Non-Native [interface org.knime.core.data.StringValue, interface org.knime.chem.types.MolValue, interface org.knime.core.data.DataValue, interface org.knime.chem.types.SdfValue, interface org.knime.core.data.RWAdapterValue, interface org.knime.core.data.AdapterValue])

The insert did not work for both CLOB and VARCHAR2 colums in Oracle 12.2.0.1.0.

Do have any ideas, what I could have done wrong?

Thanks and best regards,

Juergen

[ipazin]

Hello @jrm1974,

try to avoid using Column Rename for converting column types. As you can see you got this Non-Native type… For conversion options take a look this topic:

Column Rename node doesn't convert chemical structures to String

The Column Rename node doesn't convert molecules to String.  For example, I'm trying to convert a SMILES column (which has been previously cast as such in order to perform calculations on it) back to type String.  I select this column in 'Column Rename', keep the name identical, and select the drop down to change SmilesValue to StringValue.  It appears to work, as the output table has the String icon for this column; but in reality, it is changed to "Non-Native [interface org.knime.core.data.St…

Br,
Ivan

[jrm1974]

Hello Ivan,

using the Java snippet as suggested in the linked topic, I have now been able to insert the MOL blocks into Oracle.

Thank you for your help!

Best regards,

Jürgen

[ipazin]

Hello Jurgen!

Glad to hear that

Br,
Ivan