aspose file tools*
The moose likes Object Relational Mapping and the fly likes steps need to take for developing database independent stored procedures Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "steps need to take for developing database independent stored procedures" Watch "steps need to take for developing database independent stored procedures" New topic
Author

steps need to take for developing database independent stored procedures

palla jayakrishna
Greenhorn

Joined: Oct 01, 2008
Posts: 7
Hi Guys,

can please write the steps we have to take care for writing complex stored procedures in oracle but it should work even if we change the database to MySQL .
I think if future there will be a common api for all databases which can have same syntax for all sql queries..
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30299
    
150

As of now, if you want your app to be database independent, you should avoid stored procedures. I've been reading about Java stored procedures where the languages isn't PL/SQL. I don't know how common support is now though.

I'd question if you really need a stored procedure though. Many complex queries can be written using JDBC and remain database independent.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
palla jayakrishna
Greenhorn

Joined: Oct 01, 2008
Posts: 7
HI Jeanne,

Thanks for the reply
1)in our existing application we already have some of the stored procedures written in oracle and we are planning to write some more stored procedure to parse large xml files and insert data in the oracle database which enhances the existing functionality of our core application.Also need the flexibility to migrate to mysql or db2.
i dont know may be we have to write complex queries in using JDBC and remain database independent as suggested by you avoiding stored procedure.

2)Do you have any idea about any R and D developments in progress to come up with common api which will be same syntax and same queries for all for any database.

3)As per my understanding hibernate will generate the sql queries based on the back end database so will it change the sql queries while generating queries based on the back end database.
i know how to call a stored procedure by using hibernate but i am not sure if we develop stored procedure in hibernate by using session createSQLQuery or named query and can we see the difference of generated queries by changing the database names?
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3610
    
  60

Jeanne Boyarsky wrote: I've been reading about Java stored procedures where the languages isn't PL/SQL. I don't know how common support is now though.

As far as I know, support for Java inside the database is specific to Oracle database. It therefore won't make the code more portable at all, but it might make it more manageable by a Java developer. Or easier to move existing JDBC Code with minimal effort to the database to minimize network roundtrips.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: steps need to take for developing database independent stored procedures