• 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
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

SQL Functions in Hibernate

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

Is there a way for me to replicate the built-in DB2 functions when using Hibernate?

I need to trim the left and right spaces, and then convert the field in where-clause to upper case for comparison purposes. Cut down to the simplest form, my query presently looks like:



The error trace is:


I am in the process of determining if this is a syntax issue or something deeper. In the mean time, I'd appreciate any feedback regarding the availability of any neat way of accomplishing my task.

Thanks,
Saket
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Full disclosure - I know SQL but not Hibernate so view any suggestions in this light.

I'm pretty sure UPPER is ANSI standard SQL, but LTRIM and RTRIM aren't, so does replacing ltrim and rterim with a single TRIM work?

Also, a couple of (really) ignorant questions about Hibernate:

When doing the setParameter don't you need to specify the datatype?

countOpCodeQry.setParameter( "newOpCode", newOpCode, Hibernate.INTEGER );

instead of

countOpCodeQry.setParameter( "newOpCode", newOpCode );

Also do you need to worry about using native DB2, the Hibernate Documentation seems to indicate the syntax you have used supports native SQL.

http://www.hibernate.org/hib_docs/v3/reference/en/html/querysql.html

The concern I have is the way you are passing in a bind variable value.

The examlples I have looked at suggest using

countOpCodeQry.setInteger( "newOpCode", newOpCode );

for an Int or

countOpCodeQry.setString( "newOpCode", newOpCode );

for a string.
 
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi


did you try "trim" ?
In the DB2Dialect i found this:

so it should do exactly what you want ?


if you use setParameter() then hibernate will do some guessing based on the parameter:

Bind a value to a named query parameter, guessing the Hibernate type from the class of the given object




if you use createQuery then you need to write your query in HQL. If you use createSQLQUery then you will use SQL and can use any SQL that the underlying DB supports. But you might bind your application to this specific RDBMS loosing some of the advantages that hibernate gives you.

pascal
 
Can't .... do .... plaid .... So I did this tiny ad instead:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic