uh.setValueForAdditionalColumn

User 818520b6b8

13-05-2005 06:28:25

Hi all,





I added 5 additional columns at structures table.





The 5th column is a CLOB and I want to stored the mol file representation of the molecule when the user performs an update, but I'm getting an error.





I'm using this code to perform the update:





uh.setValueForAdditionalColumn(5,molstr,Types.CLOB);





Where molstr is the string for the molfile.





Where is the problem?

User 818520b6b8

13-05-2005 06:45:31

Even if this column is a varchar2(4000) I get the same error, using the following code:





uh.setValueForAdditionalColumn(5,p_molstr);

User 818520b6b8

13-05-2005 07:30:30

In the end I managed to perform the update when the column is a varchar2(4000).





The problem is that this column needs to be a CLOB because we have mol files larger than 4000 characters.





Thanks.

User 818520b6b8

13-05-2005 07:54:49

I found the solution !!!!!





I need to use a Oracle Temporary CLOB.





Thanks.

User 818520b6b8

13-05-2005 08:02:29

I'm having problems with large structures.





I can only get an "a" written to the clob column.





Any ideas?

User 818520b6b8

13-05-2005 08:05:25

I'm using this code:





UpdateHandler uh = null;


CLOB clob = CLOB.createTemporary(conn,false,CLOB.DURATION_SESSION);


try


{


uh = new UpdateHandler(ch,


(p_isInsertion?


UpdateHandler.INSERT_WITH_ID :


UpdateHandler.UPDATE),


structureTableName, "CD_MOLFILE");


}


catch (Exception e)


{}





try


{


if(p_isInsertion)


uh.setValuesForFixColumns(p_id, MdlCompressor.convert(p_structure,MdlCompressor.COMPRESS));


else


uh.setValuesForFixColumns(p_id, MdlCompressor.convert(p_structure,MdlCompressor.COMPRESS));


clob.setString(1,p_molstr);


uh.setValueForAdditionalColumn(1,clob,Types.CLOB);


uh.execute();


clob.freeTemporary();

ChemAxon 9c0afc9aaf

13-05-2005 15:35:02

Hi,





Sorry for answering late, but this topic was originally posted to the Marvin related section, so I've just got a notification when it was moved here.





I will investigate this issue and get back to you soon.





Best regards,





Szilard

ChemAxon 9c0afc9aaf

15-05-2005 07:48:56

Hi,





BLOB and CLOB types are only supported for the cd_structure field yet.


(You can change the type in the table creation dialog, in fact BOB will be the default instead of LONG RAW from version 3.1).





Oracle JDBC drivers has a bug concerning the insertion of BLOB and CLOB data bigger than 4 kBytes in size.


In this case a different method is used to insert the data: first a dummy value is inserted, then it is updated with the appropriate value.


This workaround is implemented for the cd_structure field of course, you see an accidental side effect of it with your custom field ("a" is the dummy value).





We plan to support BLOB and CLOB additional columns, but I'm not sure if we can implement this in the next major release (3.1), as there are lot of other things to do.





For a temporary solution I recommend you not to specify the value of this field (it will be NULL after the insertion), and then updating the row from your own code. Of course you should not forget to take care of the mentioned JDBC bug. I can give you some hints if you are not familiar with the issue or cannot find a good description on the web.





The reason for needing such a column is also interesting.





- Do you need it because the molfile is compressed in the cd_structure column ?


- Why is this a problem? Do you use some non-ChemAxon product to display the molfile ? In that case what is the reason for not using Marvin for this ?


- Cannot you decompress the content of cd_structure on the fly for your needs ? (with MDLCompressor)


- Would an option help with which you could disable the compression ?





I have also found that the API documentation for UpdateHandler.setValueForFixColumns(...) is misleading.


("structure - Structure in compressed molfile format")


In fact, it can accept structures in the following formats: molFile, SDFile, RDFile, RXN, SMILES, MRV, JTF.


You do not need to compress the structure source , the MDL formats are automatically compressed (this cannot be disabled currently). Otherwise the structures are stored without conversion, except that the data fields are removed.


We will correct the documentation of this method.





Best regards,





Szilard

User 818520b6b8

17-05-2005 08:10:53

Thanks Szilard,





we hit the bug indeed.





What I don't understant is why Oracle JDBC 10g has still the same bug from previous JDBC versions. I'll talk with Oracle about this....





For future releases I'd think about allowing CD_STRUCTURE column to be a CLOB or a BLOB with full support of Compress/Decompress functions both from JAVA Client Side (API) anb Oracle PL/SQL side with wrappers.





About your questions:





1) Yes, we need a column with MolFile format without compression.


2) We'll talk about this on thursday. Cannot reply to this in a public forum.


3) We need the molfile format without compression from within Oracle database accessible from SQL and PLSQL, and I cannot use PL/SQL Wrappers for MDLCompressor class for structures bigger than 32Kb.


4) Yes.





Thank you very much indeed.





See you on wednesday.