• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

steps need to take for developing database independent stored procedures

 
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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..
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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?
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic