Support for CLOB in cartridge?

Although am aware that it is recommended to use BLOB datatype to handle the formats like SD/MOL, visualising BLOB datatypes is a bit hard on our side.





Wondering if there is a way of handling, searching over and coverting to CLOB types in the JChem Cartridge OR is this something which is being considered for future versions?





Thanks





Madhu

Certainly, we are prepared to consider implementing anything useful to our users.





Please, could you elaborate on how CLOB would make your life easier?





Thanks





Peter

We register compounds to Oracle using normal table creation using SQL(not using jchemmanager) and index them using jchem index. At the moment we support writing, reading and visualising CLOB datatype but not BLOB. We are not sure of extending this support to BLOB type.


So using jchem operators on SDF/MOL data from our end turned out to be tricky.

JChem Cartridge will most probably support the CLOB type for structure columns, but I do not know just when. I have to do some research on what it takes to implement this support and based on the costs we will have to discuss the schedule internally.





I will get back to you on this thread, when I have the schedule (or more information on a possible schedule).





Cheers,


Peter

Hi,





I have searched the forum but cant seem to reach a conclusion as to whether the Cartridge supports the creation of an index for searching a CLOB column of an existing (not JChem) table? Obviously you support BLOB columns but do you support CLOB columns as well?





If this is not supported when might it be?





Thanks,





James

Hi,





This feature is not yet available, but is planned with no established target release yet.





Peter





PS:


As far as I can tell CLOB support implementation can be divided into two steps: 1.) "syntactic support": operators and functions accepting CLOB type with US-ASCII character encoding only 2.) support for any character encoding supported by Oracle.





Step 1.) appears to be relatively simple and is probably enough for SMILES users. Step 2.) may be necessary for MDL users with non-US-ASCII (multibyte, wide-character, etc) encoding and requires more thought, research and experimentation. For example: for most operations I have to pass strings from Oracle to Java, then from Java back to Oracle -- and Java may not support all encodings supported by Oracle (or worse: encodings supposed to be same in Oracle and in Java, might actually differ at random points). An approach for step 2.) could be to initially support only a Unicode variant supported by both Oracle and Java -- but this may not be good enough for certain users.

Hi Peter,





Thanks for the update on the status of this feature request. One thing that I am wondering is doesn't JChem Base already have to handle the same issue of interacting between CLOB and Java as I am sure I read in the forum and documentation that the cd_structure column of a JChem table can be of type BLOB or CLOB.





Therefore could the cartridge "borrow" the Java <-> CLOB solution from JChem base?





Thanks,





James

Hi James,





When a structure is inserted into an jc_idxtype-indexed structure table, the string representation of the structure is passed from Oracle to Tomcat as part of an HTTP POST request for standardization and fingerprint generation, etc. The generated information is sent back to Oracle as the reply to this HTTP request. The encoding for the HTTP transport is not solved in JChem Base. Currently, JChem Cartridge uses US-ASCII everywhere between Tomcat and Oracle.





Regards,


Peter

Peter,





Ah OK I see the problem you face. In that case please add a vote from me for addition of support for CLOB columns to the cartridge sooner rather than later.





The reason is I have a situation where I am trying to interface with an existing table containing structures in a CLOB column, I cant mandate that the type be changed to BLOB and I need to access the table through standard SQL.





Thanks,





James

JChem 3.2.5 has been released with CLOB support included.