Hi,

first off, I'd like to say thanks for a great tool. Been using it for a few months now and it's been great. This is the first time I've had to post to the forum (thanks to the unusally good documentation), so I thought I'd show my appreciation.

Ok, the problem. Here's two HQL statements I've using:

private static final String REFSEQ_GENE_NAME_HQL = "select taggedprimerpair from TaggedPrimerPair as taggedprimerpair "+
"where taggedprimerpair.primerPair.target.accession in (select refseq.accession from RefSeq as refseq where "+
"contains(refseq.locus.name.name, 'SYN('||:param||', molecularBiology)')>0)";

private static final String GENBANK_GENE_NAME_HQL = "select taggedPrimerPair from TaggedPrimerPair as taggedPrimerPair "+
"where taggedPrimerPair.primerPair.target.accession in (select elements(locus.genBankAccession) from "+
"Locus as locus inner join locus.name as geneName where contains(geneName.name, 'SYN('||:param||', molecularBiology)')>0)";

As you can see they both use the SYN function in Oracle. The first works just as I'd expect, demonstrating that the SYN('|| .. construct works (I use it all over actually). However the second dosen't, in a strange way. If I replace the '||:param||' by an explict string it works fine. If I leave the param construct in it dosen't work: no errors, no debug messages, the call just never returns. Here's the debug output (which looks fine to me when I view the constructed prepared statement).

DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] - instantiated session factory
DEBUG [com.clontech.dataaccess.qzyme.HibernateQzymeDAO] - Entering getTaggedDesignPairs with key = genBankGeneName and param = apoptosis
DEBUG [net.sf.hibernate.impl.SessionImpl] - opened session
DEBUG [net.sf.hibernate.impl.SessionImpl] - find: select taggedPrimerPair from TaggedPrimerPair as taggedPrimerPair where taggedPrimerPair.primerPair.target.accession in (select elements(locus.genBankAccession) from Locus as locus inner join locus.name as geneName where contains(geneName.name, 'SYN('||:param||', molecularBiology)')>0)
DEBUG [net.sf.hibernate.impl.SessionImpl] - parameters: apoptosis
DEBUG [net.sf.hibernate.hql.QueryTranslator] - compiling query
DEBUG [net.sf.hibernate.hql.QueryTranslator] - compiling query
DEBUG [net.sf.hibernate.hql.QueryTranslator] - HQL: select locus.genBankAccession.elements from com.clontech.om.Locus as locus inner join locus.name as geneName where contains ( geneName.name , 'SYN(' | | :param | | ', molecularBiology)' ) > 0
DEBUG [net.sf.hibernate.hql.QueryTranslator] - SQL: select genbanka5_.gbacc from admin.locus_main locus3_, admin.gene_name genename4_, admin.gbaccnum_ll genbanka5_ where locus3_.gene_name_id=genename4_.ID and locus3_.locusid=genbanka5_.locusid_f and ((contains(genename4_.gene_name , 'SYN('||?||', molecularBiology)')>0 ))
DEBUG [net.sf.hibernate.impl.SessionImpl] - flushing session
DEBUG [net.sf.hibernate.impl.SessionImpl] - Flushing entities and processing referenced collections
DEBUG [net.sf.hibernate.impl.SessionImpl] - Processing unreferenced collections
DEBUG [net.sf.hibernate.impl.SessionImpl] - Scheduling collection removes/(re)creates/updates
DEBUG [net.sf.hibernate.impl.SessionImpl] - Flushed: 0 insertions, 0 updates, 0 deletions to 0 objects
DEBUG [net.sf.hibernate.impl.SessionImpl] - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
DEBUG [net.sf.hibernate.impl.SessionImpl] - Dont need to execute flush
DEBUG [net.sf.hibernate.hql.QueryTranslator] - HQL: select taggedPrimerPair from com.clontech.om.TaggedPrimerPair as taggedPrimerPair where taggedPrimerPair.primerPair.target.accession in (select elements(locus.genBankAccession) from com.clontech.om.Locus as locus inner join locus.name as geneName where contains(geneName.name, 'SYN('||:param||', molecularBiology)')>0)
DEBUG [net.sf.hibernate.hql.QueryTranslator] - SQL: select taggedpr0_.ID as ID, taggedpr0_.catalog_no as catalog_no, taggedpr0_.syteline_no as syteline3_, taggedpr0_.design_pair_id as design_p4_, taggedpr0_.oligo_tag_id as oligo_ta5_ from dzyna.tagged_design_pair taggedpr0_, dzyna.design_pair qzymepri1_, dzyna.gene qzymetar2_ where (qzymetar2_.gb_acc in(select genbanka5_.gbacc from admin.locus_main locus3_, admin.gene_name genename4_, admin.gbaccnum_ll genbanka5_ where locus3_.gene_name_id=genename4_.ID and locus3_.locusid=genbanka5_.locusid_f and ((contains(genename4_.gene_name , 'SYN('||?||', molecularBiology)')>0 ))) and taggedpr0_.design_pair_id=qzymepri1_.ID and qzymepri1_.gene_id=qzymetar2_.ID)
DEBUG [net.sf.hibernate.impl.BatcherImpl] - about to open: 0 open PreparedStatements, 0 open ResultSets
DEBUG [net.sf.hibernate.connection.DriverManagerConnectionProvider] - total checked-out connections: 0
DEBUG [net.sf.hibernate.connection.DriverManagerConnectionProvider] - using pooled JDBC connection, pool size: 0
DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] - prepared statement get: select taggedpr0_.ID as ID, taggedpr0_.catalog_no as catalog_no, taggedpr0_.syteline_no as syteline3_, taggedpr0_.design_pair_id as design_p4_, taggedpr0_.oligo_tag_id as oligo_ta5_ from dzyna.tagged_design_pair taggedpr0_, dzyna.design_pair qzymepri1_, dzyna.gene qzymetar2_ where (qzymetar2_.gb_acc in(select genbanka5_.gbacc from admin.locus_main locus3_, admin.gene_name genename4_, admin.gbaccnum_ll genbanka5_ where locus3_.gene_name_id=genename4_.ID and locus3_.locusid=genbanka5_.locusid_f and ((contains(genename4_.gene_name , 'SYN('||?||', molecularBiology)')>0 ))) and taggedpr0_.design_pair_id=qzymepri1_.ID and qzymepri1_.gene_id=qzymetar2_.ID)

Hibernate: select taggedpr0_.ID as ID, taggedpr0_.catalog_no as catalog_no, taggedpr0_.syteline_no as syteline3_, taggedpr0_.design_pair_id as design_p4_, taggedpr0_.oligo_tag_id as oligo_ta5_ from dzyna.tagged_design_pair taggedpr0_, dzyna.design_pair qzymepri1_, dzyna.gene qzymetar2_ where (qzymetar2_.gb_acc in(select genbanka5_.gbacc from admin.locus_main locus3_, admin.gene_name genename4_, admin.gbaccnum_ll genbanka5_ where locus3_.gene_name_id=genename4_.ID and locus3_.locusid=genbanka5_.locusid_f and ((contains(genename4_.gene_name , 'SYN('||?||', molecularBiology)')>0 ))) and taggedpr0_.design_pair_id=qzymepri1_.ID and qzymepri1_.gene_id=qzymetar2_.ID)

DEBUG [net.sf.hibernate.impl.SessionFactoryImpl] - preparing statement
DEBUG [net.sf.hibernate.type.StringType] - binding 'apoptosis' to parameter: 1


If anyone has any insight here, it'd be very gratefully recieved. I've been scratching my head over this for a while now,

thanks

Jonny

you can try to run resulting query using plain JDBC...

Yeah, I just played around with the generated prepared statement using plain JDBC and I get the same behaviour. I can't see anything wrong with the PS looking at it, it's what I would have written by hand, so it dosen't look like a problem with the generation. Looks like some strange Oracle bug.