• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

steps need to take for developing database independent stored procedures

 
palla jayakrishna
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author & internet detective
Marshal
Posts: 34860
369
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
palla jayakrishna
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic