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

Get dialect from session (for character escaping)

 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all,

I need to get the dialect being used in order to make some special characters escape, I need to use different escape technique depending upon the dialect.

For example, for mysql I need to escape underscore like this:

query.replaceAll("_", "\\\\_")

for Oracle and HSQL I need to do like this:

query.replaceAll("_", "\\_")

That's why I need to infer the dialect from the org.hibernate.Session, but I just can't see how to do it with the available JavaDocs.

Thanks a lot!
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It is counterintuative to try to do this, hence no method in the API.

Why do you need to escape charactes at all?
 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Because I don't want to have special characters like underscore to be read as wildcards but literal characters, ex:

I have this data:

index word
1 stop
2 stap
3 st_p

If I put in my hibernate query "select * from data where word like '%st_p%' "

I only want to get the "st_p" result and not the three of them.

Currently I'm using MySQL, so the query that gets the right result looks like "select * from data where work like '%st\\\\_p%' "

In Oracle it would be "select * from data where work like '%st\\_p%' "

That's why I want to escape especial characters according to dialect.

Thanks!


 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Would it not be much easier to use parameter binding? This way you do not have to care about database-specific escape characters in your queries.
 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you give me an example of what you mean? Please let me know how it would work for the given example.

Thanks
 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
By the way, I currently do this:

sqlQuery.setParameter(e.getKey(), e.getValue() != null ? e.getValue().replaceAll("_", "\\\\_") : null);
 
Paul Clapham
Sheriff
Posts: 21579
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And do you do that for every single query? You didn't post a query to go with that, which suggests that you do.
 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, I do that for every single query. I actually escape the parameters values when they are binded to the org.hibernate.SQLQuery.

My second post was to clarify whay it works different in Oracle and MySql.

However the first question still remains: How can I infer the dialect so I use different escaping in Oracle than MySql.

Thanks!
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmm - I'm guilty or reading your question too quickly. Now I think about it, underscore is in the ANSI standard, so a dialect will not escape it even with parameter binding. Finding the dialect is a non-starter, there is no API for this supplied by Hibernate. But finding the database is doable. You would need to interogate the database meta data and use a SQL query directly as appropriate.

I'm actually a little surprised HQL doesn't seem to include a cross-platform escape character (or some other mechanism) to escape underscore==wildcard behaviour.
 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Paul Sturrock wrote:You would need to interogate the database meta data and use a SQL query directly as appropriate.


Any sample about how to do this? I only have the Session to get that metadata.

Cheers
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can get a java.sql.Conneciton from the Session with the connection() method. From that you can get the DatabaseMetaData which should allow you to find out what database type you are on. Not perfect, but I can't think of another way of doing it (short of changing your data).
 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot, that made the trick. However the connection() method is deprecated. Is there another way to get the connection/metadata?
 
Robert Garrido
Ranch Hand
Posts: 30
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks a lot. It's fixed now.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic