how use Cartridge jc_insert function store additional column

Dear all:


       I want to store additional column ,but I don't know how to set up.I according to the developer's guide,but cannot set up,because i will to store the additional column not sdf


The additional column is custom of structure table , I want to set a value then store structure table, I don't know how to use the options parameter.Who can help me solve this problem? Thanks a lot.





Allen

Dear Allan,





You can use the userDefColMap option of jc_insert to map a tag in the SDFile:





http://www.chemaxon.com/jchem/doc/guide/cartridge/cartapi.html#jc_insert





. If you want to add a value which is not in the SDFile,





1. Insert first the SDFile with jc_insert,





2. use a regular UPDATE command in conjuction with the cd_id value of the newly inserted structure:





update mytable set mycolumn = myvalue where cd_id = which_has_been_returned_by_jc_insert








Peter

Dear Peter,


Thanks for your help again,

I'm attempting to use the userDefColMap in a jc_insert and am getting an error.  This is an example of the last param in the insert.  

    'flags:c haltOnBadFormat:y userDefColMap:INTERNALCODE=CODE'

'CODE' is a field in the table that contains the structures.  INTERNALCODE is a field in the SDF.  If I remove the userDefColMap column mappings the structure gets inserted.  If I leave the mappings in then I get the following SQL error 

ORA-29532: Java call terminated by uncaught Java exception: java.rmi.ServerException: RemoteException occurred in server thread; nested exception is: 
    java.rmi.RemoteException
ORA-06512: at "JCHEMCART.JCHEM_TABLE_PKG", line 30
ORA-06512: at "JCHEMCART.JCHEM_TABLE_PKG", line 19

In the servers logs I get this error.  Any idea what I'm doing wrong?

SEVERE: null
java.lang.NullPointerException
    at chemaxon.jchem.interop.core.InteropMiscellanious.getXColNamesAndValues(InteropMiscellanious.java:322)
    at chemaxon.jchem.cartridge.rmi.impl.DmlImpl.fillResponseData(DmlImpl.java:114)
    at chemaxon.jchem.cartridge.rmi.impl.DmlImpl.createResponseData(DmlImpl.java:71)
    at chemaxon.jchem.cartridge.rmi.impl.DmlImpl.createInsertData(DmlImpl.java:51)
    at sun.reflect.GeneratedMethodAccessor13.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

Thanks,

Paul

Paul,

Please, could you execute the following SQL statement and post its output:

select jchem_core_pkg.getenvironment from dual;

?

Thanks

Peter

Peter,

"Oracle environment:
Oracle Database 10g Release 10.2.0.4.0 - Production
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0    Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

JChem Server environment:
Java VM vendor: Sun Microsystems Inc.
Java version: 1.6.0_17
Java VM version: 14.3-b01
JChem version: 5.2.6
JChem Index version: 5020400
JDBC driver version: 11.1.0.7.0-Production
"

Thanks, Paul

Paul,

Please, could you provide a more complete example of the problematic jc_insert SQL statements?

Thanks

Peter

The following is a more complete example of the SQL. The "molstructure" is a CLOB which contains the SDF.  If I remove the "userDefColMap:INTERNALCODE=CODE" the insert works fine.

declare
  IDs CD_ID_ARRAY := CD_ID_ARRAY();
  Begin
IDs :=
jchem_table_pkg.jc_insert( 
 'SELECT molstructure from someTable where structureId=132' 
, 'jchemcart.structure' 
, 'jchemcart.jchemproperties' 
, 'true' 
, 'true' 
, 'flags:c haltOnBadFormat:y userDefColMap:INTERNALCODE=CODE' 
);
End;

I've tried several SDF and even tried changing the format of some of them (SDF seems to be an unforgiving format) to see if that helped but it didn't seem to make a difference.  The following is one example of an SDF I tried:

  -CPSS-  0804941117

 13 14  0  0  0  0  0  0  0  0  0
    0.8400   -0.1600    0.0000 N   0  0     0  0  0  0  0  0
    1.4800    0.4300    0.0000 N   0  0     0  0  0  0  0  0
    0.0900    0.2700    0.0000 N   0  0     0  0  0  0  0  0
    1.1100    1.2100    0.0000 C   0  0     0  0  0  0  0  0
    0.2700    1.1200    0.0000 C   0  0     0  0  0  0  0  0
    0.8400   -1.0300    0.0000 C   0  0     0  0  0  0  0  0
    1.5300    1.9900    0.0000 C   0  0     0  0  0  0  0  0
    1.0700    2.7400    0.0000 Cl  0  0     0  0  0  0  0  0
    1.5900   -1.4600    0.0000 C   0  0     0  0  0  0  0  0
    0.0800   -1.4600    0.0000 C   0  0     0  0  0  0  0  0
    1.5900   -2.3300    0.0000 C   0  0     0  0  0  0  0  0
    0.0700   -2.3200    0.0000 C   0  0     0  0  0  0  0  0
    0.8400   -2.7600    0.0000 C   0  0     0  0  0  0  0  0
  2  1  1  0  2  0  0
  3  1  1  0  2  0  0
  4  2  2  0  2  0  0
  5  3  2  0  2  0  0
  6  1  1  0  2  0  0
  7  4  1  0  2  0  0
  8  7  1  0  2  0  0
  9  6  1  0  1  0  0
 10  6  2  0  1  0  0
 11  9  2  0  1  0  0
 12 10  1  0  1  0  0
 13 12  2  0  1  0  0
  4  5  1  0  2  0  0
 13 11  1  0  1  0  0
>  <INTERNALCODE>
304

$$$$

Thanks again,

Paul

Paul,

This is a bug in JChem Cartridge. It will be fixed in version 5.8.

Workaround: Use the actual structure to insert as first parameter instead of a SELECT statement.

Peter

Peter,

I replaced the select clause in the jc_insert and used the SDF (CLOB) as the first param like you suggested.  That works fine.  Thanks for your help.

Hi,

JChem 5.8.0 has been released with a fix for this problem.

Peter