cambridgesoft connection

Dear all,

Anyone connect trought the database nodes to cambridgesoft database (oracle)? I need to extract structures and data , but i'm no sure that is possible to do easy. Anyone can help me?

best

fab

In general it is possible to connect to an Oracle database, but I am not sure if there is something special with the cambridgesoft database. In order to set up the Database Reader, you need to register the Oracle driver in the File > Preferences > KNIME > Database Driver and configure the Database Reader node correctly. This post should give some more details on how to deal with Orcale access in KNIME: http://tech.knime.org/node/20292

Hello Fab,

I do.

See http://tech.knime.org/node/20292#comment-26547 for a post on the Database Reader node config I use.

After that, it will depend on which products your company/org uses from CambridgeSoft/PerkinElmer (CS/PKI). We have registration and I pull CDX from REGDB.STRUCTURES.BASE64_CDX.

The issue I am currently facing is to have a node translate CDX into a format that can be interpreted as a molecule. I was looking for a solution in the forum when I stumbled upon your post.

Hope this helps,

Fred

I used a database looping node to loop over the structure ID's and then used "cscartridge.convertcdx.CDXToSmiles(s.BASE64_CDX)" to convert to SMILES.  You can easily do MOL2 as well.

Hello Brad,

This seems to be exactly what I am looking for. I have been trying to implement your solution for some time now and still cannot figure out how to do so.

I have configured a Database Reader node with this SQL Statement:

select *
 from (
  select
   a.BASE64_CDX "CDX_structure",
   b.REG_NUMBER "M_ID",
   d.PROJECT_NAME "Project"
  from
   regdb.structures a,
   regdb.reg_numbers b,
   regdb.compound_project c,
   regdb.projects d
  where
   a.CPD_INTERNAL_ID = b.CPD_INTERNAL_ID and
   a.CPD_INTERNAL_ID = c.CPD_INTERNAL_ID and
   c.PROJECT_INTERNAL_ID = d.PROJECT_INTERNAL_ID
 )
where rownum < 4

Its output is now connected to a Database Looping node with CDX_structure in the Column selection and this SQL Statement:

SELECT s.base64_cdx FROM regdb.structures s WHERE s.base64_cdx IN ('#PLACE_HOLDER_DO_NOT_EDIT#')

This must not be the way to do it as I get this error:

ORA-00932: inconsistent datatypes: expected - got CLOB

Even if I managed to fix this, I still would not have a clue where I could use cscartridge.convertcdx.CDXToSmiles(s.BASE64_CDX). Is it in a separate node or somewhere in the SQL statement of the Database Looping node?

Even though I have learnt a lot on how to use Knime in a fairly limited time, it seems I still am light years away from feeling comfortable with it. Please, excuse my lack of experience with this otherwise fairly intuitive tool.

Cheers,

Fred

Hello all,

The question is now how to execute this:

cscartridge.convertcdx.CDXToSmiles(BASE64_CDX)

or, in more generic terms, how can one execute a PL/SQL function from Knime, i.e.

  • how does one call the function
  • how does one provide the parameters to the function

This may be extremely basic for most of you, so please accept my apologies for asking you a detailed reply (with specific nodes and configuration).

Thank you,

Fred

Fred,

 

Try this

select r.REG_NUMBER, cscartridge.convertcdx.CDXToSmiles(s.BASE64_CDX) as Mol2Value from REG_NUMBERS r, STRUCTURES s
where r.REG_NUMBER IN ( '#PLACE_HOLDER_DO_NOT_EDIT#' )
and s.CPD_INTERNAL_ID = r.CPD_INTERNAL_ID

 

Brad

--- SOLVED ---

Thanks a bunch Brad! I feel pretty stupid not having thought about this one but it worked.

Fred