• 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
  • Tim Cooke
  • paul wheaton
  • Jeanne Boyarsky
  • Ron McLeod
Sheriffs:
  • Paul Clapham
  • Liutauras Vilda
  • Devaka Cooray
Saloon Keepers:
  • Tim Holloway
  • Roland Mueller
Bartenders:

is it a good solution ?

 
Ranch Hand
Posts: 416
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
hello
all the database provide the function and stored procedure to allow the programmer to encaptulate part of the application logic,such as the java stored procedure from the oracle,it said that it will provide more performance promotion.
who can tell me if such a solution is a good mechanism from the aspect of performance and design pattern?
thank
 
Sheriff
Posts: 17734
302
Mac Android IntelliJ IDE Eclipse IDE Spring Debian Java Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here's my 2 cents about stored procedures:
Pros:
- Can be more efficient, performance-wise. Depends on how complicated the DB-operations are. Also depends on how well the stored procs can be written.
Cons:
- Separates some of the application logic from the rest if you mix stored procs and dynamic SQL calls.
- The above can makes maintenance a bit more complicated/difficult especially if the stored procs are written in a different language (e.g. PL/SQL or COBOL) from the rest of the application (e.g. Java)
- Also can make debugging complicated/difficult
- May be more difficult to change on the fly.

In general, I would advise to use stored procs only when the performance gains far outweigh other factors to be considered such as maintainability and ease of debugging / changing.
If you use stored procs, insist that a contract/API be clearly defined and agreed upon for each one in order to minimize finger pointing between "application" and "database/stored proc" developers when bugs do show up.
 
Sheriff
Posts: 7001
6
Eclipse IDE Python C++ Debian Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I'd also add another downside. Using any kind of database-specific features can very easily lock you into a specific vendor's database, and even a specific version of a specific database.
This may not seem a problem while you are exploring the exciting features of a new DB release, but think about 18 months time when that release is obsolete, and you are forced to still use it, known bugs and all.
Java is platform-neutral. You can (mostly) upgrade hardware and operating systems with no problems. Stored procedures (whatever their language) are not. I would only consider using stored procedures for the same reasons I would consider using native methods.
 
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Frank Carver:
Java is platform-neutral. You can (mostly) upgrade hardware and operating systems with no problems. Stored procedures (whatever their language) are not.


Well, there are Java Stored Procedures arising. AFAIK they are rather platform neutral and can even be executed outside the RDBMS for testing and debugging purposes. I am not totally sure, though...
 
Frank Carver
Sheriff
Posts: 7001
6
Eclipse IDE Python C++ Debian Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My point is really that stored procedures are very database-specific. Different databases support different types of stored procedures, in different languages, with different features.
Using any sort of stored procedures risks locking your development in to a particular version of a particular database product from a particular vendor, and may even lock you into running it on a particular platform.
Using JDBC and standard SQL allows you to pick and choose whatever version of whetever database product from whatever vendor you like.
 
Ilja Preuss
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
From all I know it seems as if Java Stored Procedures actually *are* using JDBC and other standard Java features - they are simply normal Java code executed directly in the database server.
Java Stored Procedure links
 
Frank Carver
Sheriff
Posts: 7001
6
Eclipse IDE Python C++ Debian Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Yes, but not all databases have them, and not all databases implement them in the same way.
We all know that writing some Java code which will only run on Windows is probably a bad idea. How can writing some Java code which will only run on Oracle be seen as a good idea ?
 
Ranch Hand
Posts: 188
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Frank Carver:
Yes, but not all databases have them, and not all databases implement them in the same way.
We all know that writing some Java code which will only run on Windows is probably a bad idea. How can writing some Java code which will only run on Oracle be seen as a good idea ?


Writing codes which runs only on Oracle may not be a bad idea......as I've worked with regional asian company which openly declares "We are married to Oracle; if they die, we die too".
This is just a real world example
 
Ilja Preuss
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Frank Carver:
Yes, but not all databases have them, and not all databases implement them in the same way.
We all know that writing some Java code which will only run on Windows is probably a bad idea. How can writing some Java code which will only run on Oracle be seen as a good idea ?


It can't!
But I don't think that's specific to Stored Procedures. As long as it doesn't force you to make your code more dependent on a specific product, I can't see anything wrong in using stored procedures.
Do they force you? I am not sure...
 
Frank Carver
Sheriff
Posts: 7001
6
Eclipse IDE Python C++ Debian Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Do they force you? I am not sure...
I routinely test my home-written database applications on MySQL, PostgreSQL, Access/Jet, and Hypersonic SQL. Although I usually need a few tens of lines of code per database for optimisation and customisation, the same basic application runs unchanged on all these databases.
Although I often use Oracle for clients, I don't have Oracle set up for my own use. In the past, though, I have encountered stored-procedure code which prevented the application being run on anything except Oracle 7.3.4, though.
SQL, accessed via JDBC, runs on all the above databases. I would be astonished to hear of any stored-procedure solution which will run on all those databases. Please correct me!
 
Ilja Preuss
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Frank Carver:
Do they force you? I am not sure...
I routinely test my home-written database applications on MySQL, PostgreSQL, Access/Jet, and Hypersonic SQL. Although I usually need a few tens of lines of code per database for optimisation and customisation, the same basic application runs unchanged on all these databases.
Although I often use Oracle for clients, I don't have Oracle set up for my own use. In the past, though, I have encountered stored-procedure code which prevented the application being run on anything except Oracle 7.3.4, though.
SQL, accessed via JDBC, runs on all the above databases. I would be astonished to hear of any stored-procedure solution which will run on all those databases. Please correct me!


I am understanding Java Stored Procedures to be simply general Java code as your code mentioned above, running directly on the database server. I might be wrong, though...
 
Frank Carver
Sheriff
Posts: 7001
6
Eclipse IDE Python C++ Debian Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am understanding Java Stored Procedures to be simply general Java code as your code mentioned above, running directly on the database server
In principle, yes. But the database software has to know what to do if you ask it to run one. Imagine I issue some SQL such as "call ugh('hello', 13)" the DB must be able to locate the appropriate stored procedure, understand what language it is written in, load the object code/bytecode if necessary, instantiate any objects, pass in any appropriate parameters, run the code, extract any appropriate return parameters etc.
To do all that, the DB has to be built to understand (a) stored procedures as a general concept, and (b) how to deal with the specific language you chose to write them in. If Java stored procedures were automatically available, you'd be able to use them in old versions of Oracle as well as newer ones. (AFAIK, you can't.)
Can you imagine Microsoft building a version of their "Jet" database engine which supports Java of any sort ?
 
Ilja Preuss
author
Posts: 14112
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Frank Carver:
Can you imagine Microsoft building a version of their "Jet" database engine which supports Java of any sort ?


Uh, no... :roll:
But then, I could easily imagine an architecture allowing me to switch between stored and not-so-stored procedures. But I might be dreaming...
[ January 01, 2003: Message edited by: Ilja Preuss ]
 
Fire me boy! Cool, soothing, shameless self promotion:
New web page for Paul's Rocket Mass Heaters movies
https://coderanch.com/t/785239/web-page-Paul-Rocket-Mass
reply
    Bookmark Topic Watch Topic
  • New Topic