AssociateID not working after JChem Update

Hello all,

 

After upgrading to Excel 2016 and the latest JChem4Excel simultaneously, I notice that the AssociateID button no longer works to pull structures from my database based on IDs. I checked the options for Convert ID to structure by Database key and the options are current and the same as they have always been (when it used to work correctly). I have tested the Database "Import" and "Import by IDs" functionality and these both work as normal. The only thing that doesn't work is the AssociateIDs button -- it gives the following error:

Message: Could not establish the database connection.

Exception: System.NullReferenceException
Message: Object reference not set to an instance of an object.
StackTrace:    at ChemAxon.JChemExcel.Actions.ConvertByKeysFromDatabaseToStructureAction.ValidateSchema()
   at ChemAxon.JChemExcel.Actions.ConvertByKeysFromDatabaseToStructureAction.ExecuteAsynchron()

I checked the release docs and didn't see any references to changes in this functionality. Is there a new way this function works?

 

Thanks.

Hi,

 

could you please check if there is a specified database entity in the Options for Associate IDs function?

You can check it here: Options (in the JChem ribbon)>General>Actions>Convert To Structures By Key From Database. The connection name, entity name (table or view name), key column name and structure column name have to be specified here. The reported problem can be occurred if the these fields are empty in the Options.

Best regards,

Zsolt

Hi Zsolt,

 

As I mentioned in my original post, the Options for Associate IDs function are correctly filled out. They are filled out with exactly the same values as before the JChem upgrade when Associate IDs worked fine. Also, as I mentioned, I can connect to the database and manually pull structures from the same database view using the Import By IDs button -- so there is no problem connecting to the database. Hence my suspicion that something specific to the Associate by IDs button changed in the most recent JChem4Excel release. To confirm this behavior, I had a colleague upgrade his JChem4Excel to the most recent version (he was previously using a version from around Jan of this year). After he upgraded he is experiencing the same error that I am -- namely that his Associate By ID button no longer works even though all of his options are still set correctly and that he can manually import the structures using the Import By ID button.

Hi Josh,

Is the structure field your are using with Associate ID a BLOB type field? Because in latest released version of JChem for Excel I found this is a bug, could you, please check with CLOB type field?

BTW, the error message seems a connection issue, so please try to specify the connection again in Options. (Or if you don't have too much settings, which are different from default, you can delete Settings.xml in C:\Users\Current User\AppData\Roaming\ChemAxon\JChem for Excel folder or press Restore Defaults button in Options dialog and specifiy the connection again.)

Regards,

Anna

Hi Anna,

I am aware of the fact that BLOBs do not work. In fact, I am the one who originally found and reported this bug back when I was first setting up the "Associate by IDs" button on my computer. As a result I have been using a CLOB field for the structure and still have JChem4Excel setup to use the CLOB field.

Also, I took your advice and deleted the Settings.xml file. After re-entering all my settings the "Associate by ID" functionality still doesn't work. Interestingly, in order to enter the settings in the options dialog for the Associate by ID button, JChem4Excel has to connect to the database in order to provide selectable lists of tables/views and of table columns. This is further proof that JChem4Excel is not having any problem connecting to the database even though that is the error message provided when it fails to perform the "Associate by ID" action.

My guess is that a new bug was introduced to the Associate by ID button code in a recent update. Do the change logs indicate any recent modifications to this functionality?

I have found that if I downgrade JChem4Excel to version 16.3.2800 -- the AssociateID button works again. The 16.5.3000 does not work (I didn't test anything in between). This tells me that somewhere between these two versions a new bug was introduced.

 Hi Josh,

Thank you for your feedback. I could reproduce the problem what we will have to fix.

For a workaround please modify your Settings.xml file manually. You can find that file here:

C:\Users\{user name}\AppData\Roaming\ChemAxon\JChem for Excel\Settings.xml.

There is an XML tag "EntityType". Please change the value of that from "Table" to "View".

 

I hope it helps.

Regards,

Gergely

Hi Josh,

Thanks a lot for the tests and your comments. I hope the solution that Gergő mentioned will work in your environment as well.

The story behind the issue is the re-design of the Import By ID dialog, which was released in 16.4.11 version (see in History of Changes). Associate IDs functionality is still under re-design, connection parameters can be saved only if using the Import By ID dialog and checking in the "Restore the last search/map parameters back to the option" check-box.  In Options dialog "Entity Type" cannot be specified at the moment, this is why you received the error message. We are planning to implement a similar tree-like view as in Import by ID dialog opened by pressing the "Select Entity" button.

The other issue about using Associate IDs from BLOB type structure columns was fixed in 15.3.2 version (see in History of Changes), so we have to look around in the code, what happened since that time.

Regards,

Anna

Thanks for confirming this bug. I will stick with version 16.3 for now until a fix is published.

Also, after downgrading to 16.3 I notice that my structures disappear less frequently with this version compared to the newest version. The disappearing structures has to do with JChem4Excel not correctly handing the worksheet objects list when comments are present in other cells on the worksheet. This bug was fixed a while back, but it looks like it might have re-emerged in the more recent releases. If I am bold enough to install the latest version again, I'll see if I can get a reproducible test case.

Thanks.