Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:
3.2.1
Name and version of the database you are using:
Oracle 10g, MySQL 5.1

Problem Description:

We use oracle’s connect by queries to get all the children of a given node. Now we are trying to make our application database independent by trying to push all the database specific code (connect by, analytical function etc.) to stored procedures and make them return result sets.
Hibernate’s documentation says to use the below syntax in the hibernate-mapping file to call a stored procedure using named query.
But the syntax is only true for Oracle database in my case. I tried using the same syntax for MySQL database and it throws exceptions. When I change the syntax to everyting works fine in MySQL.

My questions are:
1) Is there a way I can make my application database independent?
2) Is there a different approach I need to take to attain database independence?

I might ask some more question as I remember.

Thanks

Don't know where you got the idea that stored procedures would make your application database-independent, because it does the exact opposite. The key is to either abstract away database-specific calls and syntax by using an ORM (which I assume you know of since you are here), or make the JDBC calls totally generic (e.g. ISO99) and live with the performance degradation.

Thanks for replying so quick.

When i mean database independent, it means to make our java application code database independent. The reason i choose stored procedures is because it will be like abstraction layer to the application. I will have two version of stored procedures one in Oracle and one in MySQL and take full advantage of that particular database (e.g. analytical functions in Oracle) in the stored procedure. I don't want any performance degradations.

Thanks again.

As you have already found out, calls to stored procedures are highly dependent on the database platform, as the calls have little to no standards across vendors. Now, you would be stuck with abstracting the stored procedure calls to an interface and implementing the interface for each database platform. Ick!

Could you please explain more clearly when you mean "The key is to either abstract away database-specific calls and syntax by using an ORM"

Thank you.