Hello,

I have been looking at Hibernate for a few days and it looks fantastic. I was wondering if there was a way to leverage the SQL name binding for straight SQL and stored procedure calls.

Some of the problems with JDBC that hibernate has already solved include:
* Named statements: better to specified SQL in a separate (xml) file than to embedded it in java code (...just like hibernate).
* Named parameter bindings: The ? is error prone and hard to use. JDBC does not have any good validation (type, order, number of bindings, etc) or useful messages about bindings. It is much better to use named bindings (...just like hibernate).
* Bind types: JDBC does not provide a simple way of binding a set of parameters.
* Connection management, pooling, etc (...just like hibernate).

I was wondering if it was possible to use the above features from Hibernate in standard SQL and stored procedure queries. This may also address many of the concerns and feature requests about stored procedures. For example, if you need to do a really complex query, use sql or a stored procedure, iterate through the resultset and then call the hibernate load() using the retrieved data.

Gavin

Hi Gavin.




Have you looked at session.createSQLQuery(), which was recently added by Max Andersen to 2.1 beta?

Hello,

I have had a quick look at it and it looks like what I need. However, I am not sure if I can use it with a callable statement (as opposed to a prepeared statement). This is needed for stored procedures. Also, I am not sure if it is limited to select (will do more research on it) and how results are returned.

Thanks for the advice.

Gavin

yes, theres no support for CallableStatement yet.


Max, do you wanna put some thought into that?

I'm mapping a schema that contains various in-line functions
to convert blobs into VARCHAR2s. Will I be able to use
createSQLQuery to retrieve the VARCHAR2s from the blob
values?

e.g:
select SCHEMA.GETSPECIALFILE(t.key) from schema.specialobject t


If session.createSQLQuery doesn't solve my problem, would it be crazy
to try and modify the HQL parsing objects to make this happen? It
really doesn't look all that complicated. I can can execute the above
SQL in a plain vanilla old Statement.

Sure ;)

I'm not the big CallableStatement guy ;), but if someone could be so kind to give me a small example on a schema and a simple stored procedure (for Oracle) and the jdbc you would do for it - then I would love to look into it .....my main "issue" is how we should specify that it should use CallableStatement et.al. instead of it's current strategy.

Someone else is also currently trying to write a CustomPersister that utilizes CallableStatement etc. I love to hear how he is coming along and if he consider supplying a patch for us so we can get a good usecase for having it in core hibernate.....

And as always patches are welcome ;)

The current sql support does not yet support "scalar" selects which this probably require. But you should be able to load it if you provide all the columns needed to get the specialobject.

/max

Hi,

I have been writing a proof of concept CustomPersister that would use Stored Procedures to talk to my oracle database. I'm using an object model that has inheritance and one to many associations.

I've extended it to use Stored Procs for the insert, update, and delete. I'm still working on getting all aspects of the load extended. I've found the laod to be a lillte more trickier, because I don't want to replace everything that the hibernate persister does (it does quite a lot, and i want to keep mosty of that ), but just replace the places where it generated sql with calls to the appropriate stored procs.

I'm currently working on getting the load methods in one to many relationships to call stored procedures.

Once I figure that out, I guess i'll be more clear on how the hibernate framework can be extended (in a more generic way) so that plugging in the callable statements become very easy. (and may be provide a patch)

Jaideep

Sounds great - don't hesitate to ask ;) (kinda busy right now, but i'll do my best )

I'm sorry, maybe I misunderstand (am far from relational or DB
expert), I should've chosen clear names. specialobject is just a
table. The function just returns one row or one column (stored as
blob) of another table as a decoded VARCHAR2. What do you
mean by "should be able to load it if you provide all the columns needed to get the specialobject?" Provide all the columns and load it how if
there is no "scalar" select support that this requires?

The current createSQLQuery() method only support loading mapped objects. It cannot return a list of Object[] for e.g. "select f.name, f.age from foo as f".

So, if you just want to execute a simple sql query (as the one you list) i'll suggest you go use the connection directly. Eg. by getting it via sessions connection() method.

hi max,

I' working on extending the Loader class to use stored procedures instead of the dynamically gerenarted sql statements.

I 've writtem a stored procedure that returns a result set, in which the column names are the same as the database table columns specified in the mapping xml. But, the loaders 'hydrate' method fails because it looks for columns with aliased names which are sometimes different from the database columns names.

Why does hibernate have to use aliases?
Is there a way to make it not use the aliases, but just the database column names?

thanks

Jaideep

Hello,

Could you please send you StoredProcedure loader / persister.

Thanks

Ditto,
I am in need of a way to map the resultsets I get back when calling a stored procedure into a custom Hibernate 'detail' object.

I also have written one of these, although (see below) it's not as advanced as yours by far as yet, and given you are working on one I'm not sure I should bother to continue :)

I've got round the column aliasing (for now) by slapping anyone who uses a long column name, thus making the aliases == the column names. This will break for inheritance I believe but our model doesn't call for it.

I've handled insert/update/delete/load-by-id.

Wrt to handling more advanced queries I have the option of requiring the user to call stored procedures which return the set of matching IDs and load the persistent objects via load() individually (which isn't too bad as we are code-generating all of this type of this atm anyway) or to enhance the Persister. [ Is there anything, other than a performance hit, that I should be aware of when doing this? ]

At this time I've no idea how to tie into the Session.find/iterate methods - that's a job for tomorrow. How have you done it?

Are you able to make your Persister & related classes available or is it under some license / IP which restricts that?

Offtopic: to answer the "why use stored procedures" question from other threads - because the security nazi's are not granting direct SQL access.