Import/Associate by IDs & BLOBS

I have successfully linked JChem 4 XL to my oracle table. This database link allows me to import all table data except structures. When I try to import structures using the "import by IDs" or "Associate by IDs", I get the following error:

An error occured: Unable to cast object of type 'oracle.sql.BLOB' to type 'System.Byte[]'

The structures are stored in the Oracle DB in MOL format inside a BLOB variable so it seems that there is a problem with the conversion from the binary object to the needed text object.

Is there a workaround to help with this? 

Accessibility, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a


Anonymously Hosted DynamicMethods Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null


ChemAxon.IKVM.aloe.1.0, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.automaton.1.11.8, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.calculations, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.common, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.concurrent, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.core, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.core.calculations, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.icons, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io.cdx, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io.inchi, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io.mdl, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io.mrv, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io.peptide, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io.skc, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.io.smiles, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.jchem.connection, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.license, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.marvin.app, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.marvin.gui, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.marvin.services.gui, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.marvin.utils, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.naming, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.structurechecker.api, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.structurechecker.marvin, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.com.chemaxon.version, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.forms.1.1.0, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.freehep.graphics2d.2.2.1, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.guava.16.0.1, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.jacob.1.17, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.jni.inchi.0.7, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.mysql.connector.java.5.1.29, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.ojdbc6.11.2.0.4, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.poi.scratchpad.3.11.beta2.20140914, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.IKVM.sqljdbc4.4.0, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.NET, Version=15.2.900.2323, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.NET.Base, Version=15.2.900.2323, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.NET.IKVM, Version=15.2.900.2323, Culture=neutral, PublicKeyToken=d25ce09139c726a9


ChemAxon.NET.Windows.Forms, Version=15.2.900.2323, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.AWT.WinForms, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Beans, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Core, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Jdbc, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Management, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Media, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Remoting, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Security, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.SwingAWT, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Text, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.Util, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.XML.API, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.OpenJDK.XML.Parse, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.Runtime, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


IKVM.Runtime.JNI, Version=0.42.1.2004, Culture=neutral, PublicKeyToken=d25ce09139c726a9


Interop.CXNOLEClientLib, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


Interop.JChemExcelCOMShimLib, Version=15.2.900.2335, Culture=neutral, PublicKeyToken=d25ce09139c726a9


Interop.MarvinOLE, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


JChemExcel, Version=15.2.900.2335, Culture=neutral, PublicKeyToken=d25ce09139c726a9


JChemExcel.Base, Version=15.2.900.2335, Culture=neutral, PublicKeyToken=d25ce09139c726a9


JChemExcel.Excel, Version=15.2.900.2335, Culture=neutral, PublicKeyToken=d25ce09139c726a9


JChemExcel.IKVM, Version=15.2.900.2335, Culture=neutral, PublicKeyToken=d25ce09139c726a9


JChemExcel.View.Windows.Forms, Version=15.2.900.2335, Culture=neutral, PublicKeyToken=d25ce09139c726a9


JChemExt, Version=15.2.900.2197, Culture=neutral, PublicKeyToken=d25ce09139c726a9


JChemOffice, Version=15.2.900.2334, Culture=neutral, PublicKeyToken=d25ce09139c726a9

Hi Josh,

if you have the blob in a local table, do you experience the same problem? (just wanted to exclude database link issue)

Viktor

Hi Josh,

http://www.chemaxon.com/jchem4office/userguide/selecting_columns_for_import.html

can you please try out if you explicitly specify the BLOB column as structure column to get it coverted locally in JC4XL rather in JChem?

Thanks,

Viktor

Hi Victor.

For clarification, here is my situation: The data is stored in a table under a different user's hierarchy (call it Table_A). Since I can't directly access other users' tables using associate by ID, I created a view (call it View_B) under my own hierarchy that is simply a mirror of Table_A:

Create view VIEW_B AS Select * from OtherUser.Table_A

 I can see and use View_B through JC4XL "Associate by ID" and "Import ID" so this is good progress!

Here are the details of trying to import both data columns and structure columns using each of JC4XL import buttons:

"Import": I cannot import any columns using this button. It doesn't matter if I target View_B or Table_A or whether I specify "Is structure column" as true or false  -- in all cases I can get past the query and column tabs, but then once I click "next" I get the following error:

Exception: java.sql.SQLSyntaxErrorException
Message: ORA-00942: table or view does not exist
StackTrace:    at ChemAxon.NET.IKVM.Sql.JDBCAdoNetDataProvider`1.ExecuteNonQuery(DbConnection connection, String sql)
   at ChemAxon.NET.IKVM.Sql.JDBCAdoNetDataProviderWithConnection`1.ExecuteNonQuery(String sql)
   at ChemAxon.NET.IKVM.Cartridge.JChem.JChemCartridgeSearchSession.†††
†††‡Š(Object , Object )
   at ChemAxon.NET.IKVM.Cartridge.JChem.JChemCartridgeSearchSession.TrySetSessionPassword()
   at ChemAxon.NET.IKVM.Cartridge.JChem.JChemCartridgeSearchSession.†††
†††‡Šˆ(Object )
   at ChemAxon.NET.IKVM.Cartridge.JChem.JChemCartridgeSearchSession.SetSessionPassword()
   at ChemAxon.NET.IKVM.Cartridge.JChem.JChemCartridgeSearchSession.†††
†††‡‰Ž(Object )
   at ChemAxon.NET.IKVM.Cartridge.JChem.JChemCartridgeSearchSession.Execute()
   at ChemAxon.NET.Base.Presenters.BackgroundWorkerPresenter.†††
†††Œ‘›(Object )
   at ChemAxon.NET.Base.Presenters.BackgroundWorkerPresenter.BackgroundWorker_DoWork(Object sender, DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

Possibly we have an issue if the molecules are stored in BLOB, rather than CLOB.

Register an issue for that and try to fix it in the next upcoming releases (on weekly bases).

Josh, quick question, if you change the data provider for Excel, it can be done in Options. Do you get the same BLOB to byte[] error?

Further questions:

Do you use JChem Manager to store the data in BLOB field, so is this table an official JChem table?

Or have you stored molecules simply in BLOB with a different tool, so this is not a JChem system.

BTW I have registerred a task to reproduce the same environment with MOL files in BLOB.

Hi Victor,

RE: trying other drivers: Microsoft driver throws an error (obviously since I have an Oracle DB). Oracle ODP interestingly doesn't throw an error, but doesn't appear to sucessfully connect since no tables or views are shown after initiating the connection in the "Import" interface. Devart driver gives the license error discussed in previous posts. Oracle.jdbc works as described in previous posts.

Re. the use of JChemmanager: I'm not certain in the BLOB column is created and maintained by JChemManager since a third party does our database management. This DB does have the ChemAxon cartridge installed and there are numerous JCHEM, JC & IDX named tables under the same hierarchy. Is there a way to check if the Structure table I am referencing was created by JChem manager? If so I can forward the question on to our external  DB manager.

Thanks.

--Josh

Hi Josh,

it would be a very important information from your database schema to know if the table is a JChem base table. How do we need to reproduce the environment.....

The easiest way if you ask your DB admin to give infromation that your BLOB table name is in the JChem Properties table as an entry, if the tale is there and registered,

the JChem system knows about it and it knows that blob column is a structure column.

As far as I remember the CLOB is suported by JChemExcel more, so would it mean an alternative or temp workaround for you to store the molecules in CLOB?

Viktor

Hi Victor,

I asked about the table and it is a JChem base table with appropriate structure indexes created.

I also asked my DB guy if there was a way to construct the View in such a way to convert the BLOB to a CLOB, i.e.:

CREATE VIEW VIEW_B AS
SELECT CMPD_ID, MAGIC_BLOB2CLOB_COVERTFUNC(STRUCTURE, "UTF8")
FROM TABLE_A

I haven't received a response yet, but if you know of such a magic function, please let me know.

Thanks for all your help Victor!

--Josh

My DB guy installed a PL/SQL procedure on the database to convert BLOB to CLOB. So I updated my VIEW_B to include an extra column which is the CLOB conversion.

The new behaviors are as follows:

"Import" still doesn't work. Same error as before. it may be that having any BLOB in the table causes this to fail

"Import by IDs": used to work for all non-structure columns - now doesn't work for any column. it adds the headers to the spreadsheet, but no longer fills in any data.

"Associate by IDs": This now works!  Since this is the main functionality I am interested it, I'm happy for now. I think I may go back later and remove the BLOB from the view to see if I can get either of the other two features to work.

Best,

--Josh

Great news Josh,

we will dig into the deep and check what happened to the BLOB handling in the meantime.

To be honest our users rarely use BLOBS since MOL, MRV, CXSMILES are the most popular data types

they can all go into CLOB rather than into BLOB. Test case will be added or repaired.

Viktor

Hi Josh,

I have fixed the BLOB issue in the this week release, please check:

https://www.chemaxon.com/jchem4excel/changes/changes.html#V113

Viktor