Support for BLOB data type for CD_STRUCTURE column

User 818520b6b8

19-04-2005 09:21:21

Hi all,





I altered the table where structures ar stored and changed the column data type for CD_STRUCTURE from LONG RAW to BLOB, issuing an ALTER TABLE statement.





Afterwards I rebuilded all the indexes on this table.





And, as Oracle documentation says, everything is still working fine.





Is this supported?





I'd like you to think in using BLOBs for structures in next release of JChem as the change is not so important.





Please refer to Oracle Documentation:





http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adfnstyp.htm#435240





Migrating LONG Datatypes to LOB Datatypes





The LONG datatype can store variable-length character data containing up to two gigabytes of information, depending upon available memory. LONG columns have many of the characteristics of VARCHAR2 columns. You can use them in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.





Oracle Corporation recommends using the LONG datatype only for backward compatibility with old applications. For new applications, you should use the CLOB and NCLOB datatypes for large amounts of character data. Typically, you can change LONG data to LOBs in your tables without changing existing applications. SQL, PL/SQL, and OCI interfaces for LONG data can all work on LOB data as well.





Thanks a lot.

ChemAxon 9c0afc9aaf

19-04-2005 09:59:56

Hi,





Actually there are some differences for JDBC access, so we must specifically support LOB types in the cd_structure column.





We already support BLOB and CLOB types in JChem Base.


All of our API and programs can handle these types in the cd_structure column (from JChem 3.0.4 or later).





You can change the type in the table creation dialog of JChemManager (jcman) when the CREATE statement is displayed.





We also provide API calls to read the content of cd_structure columns in a transparent way:





http://www.jchem.com/doc/api/chemaxon/util/DatabaseTools.html#readBytes(java.sql.ResultSet,%20int)





http://www.jchem.com/doc/api/chemaxon/util/DatabaseTools.html#readBytes(java.sql.ResultSet,%20java.lang.String)





We plan to make BLOB the default type from version 3.1.





In JChem Cartridge we do not fully support accesing JChem tables with LOB columns yet.


We are planning to provide full support from version 3.1.





Best regards,





Szilard

User 818520b6b8

19-04-2005 12:39:49

When is version 3.1 expected to be available?

ChemAxon 9c0afc9aaf

20-04-2005 07:58:14

Hi,





We expect to release it in 1-2 months.





Regards,





Szilard

User f6b3d4b028

05-07-2007 19:12:02

Dear Sir/Madam,





I am from www.bindingdb.org, trying to upgrade to JChem3.2.7 (now JChem 3.0.12). I used two methods to build a new structure table, jcman and API UpdateHandler, with SD file (MDL format) or single mol (MDL format) file.





It is found that the CD_SMILES from new structure table agrees with that from the old table. Then I tried to read CD_STRUCTURE (BLOB format) from the new structure table, either with standard java API methods or with readString method from chemaxon.util.DatabaseTools. To my surprise, the MDL file (BindingDB_1_new.mol, attached) from the new structure table does not agree to the expected original MDL file (BindingDB_1.mol, attached).





Please note that the two files have the same structures, especially, first four columns and last two columns agree to each other.





Thank you for your further instructions.





All the best,





Tiqing





PS. Oracle 10.1.0.2.0 for new structure table; old table stored in Oracle 9i.





if I do "select CD_FP16 from monomer_struct where monomerid = 1",





the result for the new table is "-1.898+09", but the old table gives "-828076024". The new table was produced "UpdateHandler.createStructureTable(conHandler, structureTableName, 16, 2, 5, ", monomerid NUMBER(10,0) NOT NULL, InChI_string VARCHAR2(4000), IUPAC_name VARCHAR2(4000)");


".

ChemAxon aa7c50abf8

05-07-2007 21:07:32

Dear Tiqing,





When the two molecules are displayed in MarvinView, they appear to be identical. The only difference between the two is probably that the new one is compressed.





You can turn off compression of SDFiles and Molfiles by unchecking the "Compress MOL,..." check-box on the "Advanced" tag in JChemManager's "Options" dialog box. All subsequently imported structures will then be stored uncompressed in the cd_structure field.





Let me know if this helps.





Peter

User f6b3d4b028

10-07-2007 14:17:05

Hi Peter,





It works!





All the best,





Tiqing