• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

getResultsList() returning nothing through OracleTopLink..works fine in Hibernate

 
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi everyone,

I am newbie to ORM and trying to run a simple select statement to fetch data by comparing first name using OracleTOPlink. I have tried following different options

1.em.createQuery("select o from FProfile as o where o.firstName='"+firstname+"'").getResultList(); //no luck
2. //return em.createQuery("select o from FProfile as o where o.firstName= ':firstname'").setParameter("firstname",firstname).getResultList();//no luck
3. em.createQuery("select object(o) from FProfile as o where o.firstName='"+firstname+"'").getResultList();//no luck
4. em.createQuery("select o from FProfile o where o.firstName like :firstname").setParameter("firstname",'%'+firstname+'%').getResultList();

The only option that works is 4.

However, if I remove the % sign, it stops working..and returns nothing.

Can someone explain me why is this happening?

The following query works wonderfully fine in Hibernate

em.createQuery("select o from Profile as o where o.firstName='"+firstname+"'

If I do the same thing through Oracle TopLink it fails.

The following query also fails in toplink.
em.createQuery("select o from FProfile o where o.firstName =:firstname").setParameter("firstname",'%'+firstname+'%').getResultList();

If i remove % from above query, then it returns nothing, but as soon as I put LIKE operator instead of = query works.

I would like to understand the difference...as to why do I have to put '%' sign and LIKE operator and do setParameters to make it work in Oracle Toplink.

Let me know if I am doing something wrong.

Thanks in advance.
Regards,
Chintan.
 
Ranch Hand
Posts: 553
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My guess would be your field is a CHAR field, not a VARCHAR and TopLink uses parameter binding (parameterized SQL) by default, where as Hibernate does not (dynamic SQL). Your database would seem to have an issue with binding comparisons to CHAR fields, because the fields are space padded so comparisons (and other operations) can fail.

You can disable binding in TopLink through the persistence.xml property, "eclipselink.jdbc.bind-parameters"="false".

You could also disable binding for just the query.

In general I would recommend you use VARCHAR not CHAR for variable length strings. If you space padded the string to the size of the CHAR column it would also work with binding on.
 
Chintan B Shah
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi James,

Thanks a lot for reply. I apologize that I did not post the table structure but yes you are right..the fields that I am querying on are CHAR(100).

Unfortunately, I wont be able to change DB table structure now(as I will have to change code too)..probably its bad on our side to design it that way.

However,I would like to try the options that you have mentioned

1. You can disable binding in TopLink through the persistence.xml property, "eclipselink.jdbc.bind-parameters"="false".

I am posting my persistence.xml here..As its Netbeans oriented..I could not find the parameter that you have mentioned.

<?xml version="1.0" encoding="UTF-8"?>
<persistence version="1.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd">
<persistence-unit name="OracleAndMySQL-ejbPU2" transaction-type="JTA">
<jta-data-source>OracleDS</jta-data-source>
<properties/>
</persistence-unit>
<persistence-unit name="OracleAndMySQL-ejbPU" transaction-type="JTA">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<jta-data-source>Synchronization</jta-data-source>
<properties/>
</persistence-unit>
</persistence>

2. You could also disable binding for just the query.
-->How can I do this?

3. If you space padded the string to the size of the CHAR column it would also work with binding on.
-->I will try this..but this basically means, I have to get string length and set it to 100 dynamically(if not).

Here is the table structure and I am querying on state, firstname, lastname, city, country and zipcode.

SQL> desc f_profile;
Name Null? Type
----------------------------------------- -------- ----------------

USER_ID NOT NULL NUMBER
FIRST_NAME NOT NULL CHAR(100)
LAST_NAME NOT NULL CHAR(100)
EMAIL NOT NULL VARCHAR2(4000)
ADDRESS NOT NULL VARCHAR2(4000)
CITY NOT NULL CHAR(100)
STATE NOT NULL CHAR(100)
ZIPCODE NOT NULL NUMBER
COUNTRY NOT NULL CHAR(100)
PASSWORD NOT NULL VARCHAR2(20)

Thanks a lot for your response. Not sure why DB person kept it CHAR instead of VARCHAR2.


Regards,
Chintan.
 
Chintan B Shah
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for reply James.
Your reply was extremely helpful.

I was able to work out the following option.

3. If you space padded the string to the size of the CHAR column it would also work with binding on.
-->I will try this..but this basically means, I have to get string length and set it to 100 dynamically(if not).


Thanks once again.

Regards,
Chintan.
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
For the first option, you can replace your <properties/> tag with the below, in your persistence.xml.



For second option,


[ October 22, 2008: Message edited by: Adeel Ansari ]
 
Chintan B Shah
Ranch Hand
Posts: 83
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Aneel,

I will try that.

Regards,
Chintan.
 
We've gotta get close enough to that helmet to pull the choke on it's engine and flood his mind! Or, we could just read this tiny ad:
Smokeless wood heat with a rocket mass heater
https://woodheat.net
reply
    Bookmark Topic Watch Topic
  • New Topic