Using a structure within Crystal Reports XI

At DSM we are using jChem integrated within an COM/ASP application. As of request of the customer there is need for presenting data within a reporting tool. The standard application is Crystal Reports XI for presenting a report.





Currently we are using the following versions:


- jChem: 3.0.11


- marvin: 4.0.4


- database SQL2005





Linking the blob within the structure table present an empty image.





Is there a easy way to present Structures out of the database?

Hi Roy,





The cd_structure column in a JChem structure table store the structures in their original format (e.g. SMILES, MolFile, RDF, etc.)





Normally you can use the Marvin Applet to display these structures, but this is not a web page, or convert them to pictures on the fly via our Java API but it is not very easy to access Java code from your environment.





If you happen to have a cartridge license too you can use


jc_molconvertb


to create images with your current JChem version.





The best solution would be to upgrade to our latest version, as from 5.0 image generation is available from Chemical Terms expressions.


You can assign Chemical Terms expressions to additional database columns. The calculated values (or the pictures in this case) are automatically updated whenever a structure is inserted or updated.


Then you can simply pass the binary data from this column to the report tool as an image.





First upgrade your JChem version





Additional steps for an existing table:





1. Add a new BLOB column with any DB tool to store the picture


2. Assign a Chemical Terms expression to the column via

Code:

jcman m <table> --add-ctcolcfg <cols-exprs>

Where <cols-exprs> acooring to jcman help is:





"a semicolon separated list of pairs of


column names and Chemical Terms expressions.


Each pair specifies that the values of the given


column should be automatically calculated using


the given Chemical Terms expression.


In each pair, the column name and the


Chemical Terms expression is separated with


an equal sign ('=')."





Example:

Code:

jcman m strtable --add-ctcolcfg "images=molImage('jpeg')"

3. The images in the new column are ready to be used





Defining a Chemical Terms column for a new table at table creation is much simpler in the JChemManager GUI.





(In the Instant JChem GUI adding such columns even to existing tables is very intuitive).





Please also see:





http://www.chemaxon.com/jchem/doc/user/EvaluatorTables.html#molimagedesc





Image format options:





http://www.chemaxon.com/marvin/help/formats/images-doc.html





General description of Chemical Terms:





http://www.chemaxon.com/jchem/doc/user/ChemicalTerms.html





Best regards,





Szilard

Szilard wrote:

Hi Roy, The cd_structure column in a JChem structure table store the structures in their original format (e.g. SMILES, MolFile, RDF, etc.) Normally you can use the Marvin Applet to display these structures, but this is not a web page, or convert them to pictures on the fly via our Java API but it is not very easy to access Java code from your environment. If you happen to have a cartridge license too you can use jc_molconvertb to create images with your current JChem version. The best solution would be to upgrade to our latest version, as from 5.0 image generation is available from Chemical Terms expressions. You can assign Chemical Terms expressions to additional database columns. The calculated values (or the pictures in this case) are automatically updated whenever a structure is inserted or updated. Then you can simply pass the binary data from this column to the report tool as an image. First upgrade your JChem version Additional steps for an existing table: 1. Add a new BLOB column with any DB tool to store the picture 2. Assign a Chemical Terms expression to the column via Code: jcman m <table> --add-ctcolcfg <cols-exprs> Where <cols-exprs> acooring to jcman help is: "a semicolon separated list of pairs of column names and Chemical Terms expressions. Each pair specifies that the values of the given column should be automatically calculated using the given Chemical Terms expression. In each pair, the column name and the Chemical Terms expression is separated with an equal sign ('=')." Example: Code: jcman m strtable --add-ctcolcfg "images=molImage('jpeg')" 3. The images in the new column are ready to be used Defining a Chemical Terms column for a new table at table creation is much simpler in the JChemManager GUI. (In the Instant JChem GUI adding such columns even to existing tables is very intuitive). Please also see: http://www.chemaxon.com/jchem/doc/user/EvaluatorTables.html#molimagedesc Image format options: http://www.chemaxon.com/marvin/help/formats/images-doc.html General description of Chemical Terms: http://www.chemaxon.com/jchem/doc/user/ChemicalTerms.html Best regards, Szilard

Thanks for the response. I already made an upgrade to the new version.


While running jcman the cd_sortable_formula column was added. After the addition the application resulted in a error:





java.lang.AbstractMethodError





com.microsoft.jdbc.base.baseDatabaseMetaData.getDatabaseMajorVersion()I





cmd-output:





While running JChemManager the following error occured:


java.lang.AbstractMethodError: com.microsoft.jdbc.base.BaseDatabaseMetaData.getDatabaseMajorVersion()I


at chemaxon.jchem.db.Updater.getTablesWithWrongSortableFormulaColumn(Updater.java:187)


at chemaxon.jchem.db.Updater.getUpdateInfoForUpdatingSortableFormulaColumn(Updater.java:206)


at chemaxon.jchem.db.Updater.getNextUpdateInfo(Updater.java:107)


at chemaxon.jchem.gui.Connector.go(Connector.java:95)


at chemaxon.jchem.gui.JChemFrame.connect(JChemFrame.java:582)


at chemaxon.jchem.JChemManager.init(JChemManager.java:89)


at chemaxon.jchem.JChemManager.main(JChemManager.java:106)





Software version used:


* java version "1.5.0_15"


Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0_15-b04)


Java HotSpot(TM) Client VM (build 1.5.0_15-b04, mixed mode, sharing)


* Microsoft SQL 2005 JDBC 1.2


* jChem 5.0.05 (latest windows version)





All environment variables and path entries are declared.





Please advise.





Thx.

Hi,





There are two solutions for the problem.





1. Please use a newer, 1.4 compatible JDBC driver.


(by the time your JDBC driver was written this method did not exist)





2. We have modified our code so there will be no exception even for older drivers. The new JChem version (5.0.6) will be released in a couple of days and will contain the fix.





Best regards,





Szilard

Szilard wrote:

Hi, There are two solutions for the problem. 1. Please use a newer, 1.4 compatible JDBC driver. (by the time your JDBC driver was written this method did not exist) 2. We have modified our code so there will be no exception even for older drivers. The new JChem version (5.0.6) will be released in a couple of days and will contain the fix. Best regards, Szilard

Thanks for the response.





ad 1) I have used the jDTS driver 1.2.2 and it seems to work with no problems at all.





ad 2) maybe not necessary.

Since this topic relates to JChem, I move it to the JChem forum.