Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

SQL Functions in Hibernate

 
Saket Barve
Ranch Hand
Posts: 229
  • Mark post as helpful
  • send pies
  • 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
 
Chris Hendy
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
  • 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.
 
pascal betz
Ranch Hand
Posts: 547
  • Mark post as helpful
  • send pies
  • 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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic