structure caching and update of structure table

Dear Support,








I would like to know how does the structure caching work both with jchem cartridge and API.





My purpose is the following:





I would like to use whether the cartridge or a java class uploaded on oracle as stored procedure to execute structure search. But I execute several times a day, insert and update on the structure table.





I would like to know if the cache table is updated according to the operations realized on the structure table (with a trigger for example)? Or does the cache table emptied and created again from the beginning?





Actually I have a servlet running which starts with execution of a blank search to create the caching table... and every time I modify the structure table, the servlet is restarted. But I would like to use a system more stable without the use of a structure search servlet.





Thank you in advance for your answer.








Best Regards,





Jack

Salut Jacques,





The structure cache is modified according to the insertion operations, it's not created and filled again with all the entries.








You can find docs here:





http://www.chemaxon.com/jchem/doc/guide/dbconcepts/index.html#structurecache





Bye,





Robert

Quote:

I would like to use whether the cartridge or a java class uploaded on oracle as stored procedure to execute structure search.

We do not recommend using JChemSearch from Oracle's Java, as it is much slower than the Sun HotSpot JRE.


Also I think each call behaves as a separate JVM in Oracle, so the cache would be loaded from zero each and every time.


We suggest calling the cartridge functions instead.








Best regards,





Szilard

Thank you very much for your answer.

Dear support,

I'm coming back to you with a small request.

I have create da servlet that makes structure search on POST queries, with a search on initialization to load cache. Actually when the structure table is updated (new line of structures inserted), I need to restart the application server (Tomcat 5.5) to be able to find the new structures as if the structure cache hasn't been updated.

Is there something I'm doing wrong?

I'm using JChem 5.2.6 with jdk 1.6

My servlet's code can be summarized below.

I use a new JChemSearch object to each search to ensure synchronization if simultaneous call.

Thank you very much for your help.

 

Best Regards,

 

Jacques

 

edit: Import of new structures in structure table are done using JChem Tools in API, but in another JVM.

public class SSearch extends HttpServlet implements Servlet {
    
    private DataSource ds = null;

    public void doGet(HttpServletRequest req, HttpServletResponse resp)    throws ServletException, IOException {
        // no GET, only POST
    }

    public void doPost(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
        String molfile = req.getParameter("molfile");
        String file = req.getParameter("file");
        File f = new File(file);
        String chemin = f.getParent();
        File ficLog = new File(chemin+"\\search.log");
        if (ficLog.exists()) {ficLog.delete();}
        PrintWriter pw = new PrintWriter(new BufferedWriter(new FileWriter(ficLog, false)), true);
        try {
            ConnectionHandler ch = new ConnectionHandler();
            ch.setConnection(ds.getConnection());
            JChemSearch searcher = new JChemSearch();
            JChemSearchOptions searchOptions = new JChemSearchOptions();
            searcher.setConnectionHandler(ch);
            searcher.setStructureTable("STRUCTURE");
            searcher.setResultTableMode(JChemSearch.NO_RESULT_TABLE);
            searchOptions.setMaxResultCount(0);
            searcher.setInfoToStdError(true);
            searcher.setRunMode(JChemSearch.RUN_MODE_SYNCH_COMPLETE);
            searchOptions.setSearchType(JChemSearch.SUBSTRUCTURE);
            searcher.setQueryStructure(molfile);
            searcher.setSearchOptions(searchOptions);
            searcher.run();
            for(int cdId : searcher.getResults()) {
                pw.println(searcher.getResult(i));        
            }
            ch.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        pw.close();
    }
    

    private void loadCache() {
        try {
            Context initContext = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:comp/env");
            ds = (DataSource)envContext.lookup("jdbc/ssearch");
            ConnectionHandler ch = new ConnectionHandler();
            ch.setConnection(ds.getConnection());
            JChemSearch searcher = new JChemSearch();
            JChemSearchOptions searchOptions = new JChemSearchOptions();
            searcher.setConnectionHandler(ch);
            searcher.setStructureTable("STRUCTURE");
            searcher.setResultTableMode(JChemSearch.NO_RESULT_TABLE);
            searchOptions.setMaxResultCount(0);
            searcher.setInfoToStdError(true);
            searcher.setRunMode(JChemSearch.RUN_MODE_SYNCH_COMPLETE);
            searchOptions.setSearchType(JChemSearch.SUBSTRUCTURE);
            searcher.setQueryStructure(molfile);
            searcher.setSearchOptions(searchOptions);
            searcher.run();
            ch.close();
        } catch (Exception e) { e.printStackTrace(); }
    }
    
    public void destroy() {
        // database acces closing
    }

    
    public void init() throws ServletException {
        super.init();
        this.loadCache();
    }
    
}

Dear Jacques,

when you add, delete or modify a structure in a database table with the JChem API, a new record is created about it in the update log table of the modified structure table. This information is needed for cache update.

Please check if the update log table is existed (it's name is: <structure table name>_ul), and the new record is created after you inserted a new structure to the structure table.

Regards, Edvard

Dear Edvard,

Actually the table <STRUCTURE>_UL exists and contains éléments : there are update_id and update_info, and each update_info is 'Inserts' and some are 'Inserts:xxxxx' where xxxxx is a number

To import data I use the following procedure:

Does the UPDATE_ID from <STRUCTURE>_UL should be the same as the CD_ID of the <STRUCTURE> table?

If so, there are not in my case.

Do I do something wrong?

Thank you so far for your help

 

Best Regards

Jacques

UpdateHandler uh = new UpdateHandler(conh,UpdateHandler.INSERT_WITH_ID,"Structure","ID1, ID2");
// ResultSet of query : SELECT ID1, MOLFILE, ID2, ID3 FROM SDF_TABLE
while (rs.next()) {
    uh.setID(rs.getInt(1));
    uh.setStructure(rs.getString(2));
    uh.setValueForAdditionalColumn(1, rs.getDouble(3));
    uh.setValueForAdditionalColumn(2, rs.getDouble(4));
    uh.execute();
}

Hi,

It  seems you forogot to call UpdateHandler.close() after the end of the insert/update operation.

http://www.chemaxon.com/jchem/doc/api/chemaxon/jchem/db/UpdateHandler.html#close()

This call stores the update information after the updates (as the format of the stored information may differ if multiple operations were performed).

Please let as know if this helps.

Best regards,

 

Szilard

Actually the table <STRUCTURE>_UL exists

and contains éléments : there are update_id and update_info, and each

update_info is 'Inserts' and some are 'Inserts:xxxxx' where xxxxx is a

number

Update_id is identifier of the UL table, and update_info contains the type of action. The number after the action means the CD_ID of the modified molecule, if only one structure was modified. You can check it if you insert delete or update only one molecule.

Does the UPDATE_ID from <STRUCTURE>_UL should be the same as the CD_ID of the <STRUCTURE> table?

UPDATE_ID and CD_ID are not the same. UPDATE_ID is just an identifier for the UL table.

Best Regards,
Edvard

 

Dear Szilard and Edvard,

 

Thank you very much for your answers. Now I understand better how update works.

I succeeded with cache update, and it work really fine. I fill a little slow down on first structure search when charging the cache update, but everything work fine.

 

Thank you very much for your help.

I wish you all, chemaxon's team, a merry christmas and happy new year. And for those how take some days, happy holidays. All the best for christmas and new year time.

Thanks again.

Best Regards

Jacques

Dear Jacques,

a little slow down on first structure search when charging the cache update

This must be the cache update. We have plans to improve this in the near future.

We wish you a  Merry Christmas and a Happy New Year.

Best regards,

Szilard

Thank you very much Szilard.

Have nice holidays.