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

Hibernate: Call a function that has no return mapping

 
Paulo Sebasti�o
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

First of all, thank you for this wonderful forum which has solved some questions in the project I'm doing.

Here's the deal: I want to call a function in hibernate that changes the return value depending on a set of parameters. Although I have all the entities for the different values mapped in Hibernate, I only know at runtime which one to use. So, I have to define a named query return entity name in real time. Is this possible?

I know that by using an SQLQuery object you can use the method addEntity() and define in runtime how should Hibernate do the mapping. How do I do this with a named query? If not, do you think there's a solution other than changing the mapping in runtime (by rebuilding the session factory, etc)?

Thanks for your help, it's very valuable since we have a tight deadline.

Paulo
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Um, actually I will re-write what I wrote just before. So I don't get it there are rules to calling stored procedures, and the main rule is that the out parameter must be a reference cursor. To me the reference cursor can not change what type it is. If you have different types coming out of your stored procedure, I don't think that is following the rules.

My first post was to create more than one named query. one for each type, and then get the corresponding named query at runtime.

Mark
 
Paulo Sebasti�o
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Mark,

Thanks for your answer. Perhaps I didn't explain the situation too well...

The function always returns a cursor, however the number and name of the columns contained on that cursor change. Not too sure if this was what you're thinking.

The solution of creating a named query for each of the types isn't feasible, since the query can return up to 50 different "table structures" inside the cursor. Perhaps I can try creating an SQLQuery that calls the function, however such SQL code doesn't output the table from the cursor, like a normal SELECT.

Cheers,
Paulo

[ January 18, 2008: Message edited by: Paulo Sebasti�o ]
[ January 18, 2008: Message edited by: Paulo Sebasti�o ]
 
Mark Spritzler
ranger
Sheriff
Posts: 17278
6
IntelliJ IDE Mac Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yeah, you can't do that, you are breaking the #1 rule for Hibernate to be able to call a Stored Procedure.

The return value must be a reference cursor, and of only one type. a reference cursor can't change the number of columns it has, it is basically mapped one to one with its table it references.

So no, you would have to make 50+ different stored procedures for each change, and that isn't feasible either.

Mark
 
Paulo Sebasti�o
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Mark,

I've followed your suggestion of creating a different named query for each different expected resultset.

However, I'm having some problems with this. The SP call succeeds the first time, however when I call it the second time. Hibernate throws an "Invalid column name" exception and I can only get the SP working by restarting my Websphere server (i.e. starting a new Hibernate session and sessionfactory).

I guess this is the oddest error I've faced in Hibernate
[ January 21, 2008: Message edited by: Paulo Sebasti�o ]
 
Paulo Sebasti�o
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The problem was on the DB side. The SP was returning some errors.

Anyway, for further reference here's how I did counter the problem: I use the same SP that returns different structures depending on the IN params, however I've mapped the named query in the mapping files for each of the 50+ different outputs.

Of course, I didn't do this by hand Since we use Hibernate Tools for Eclipse I've made a custom template to create the sql-query for each different output.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic