java stored procedure

Hello All,
I have done some example of java stored procedure with oracle data base, now i want to do the same example with the  postgres data base.
i want to know what steps i have to follow for this.

thanks in advance.
srini_r1Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Computer101Connect With a Mentor Commented:
PAQed - no points refunded (of 30)

Computer101
E-E Admin
0
 
JakobACommented:
This is not a Java matter, It is about the database and how it implements the SQL language.  So read the postgres manual and find out how stored peocedures are handeled there. then you just use Java to make sure the nessesarry SQL commands get sent.

regards JakobA
0
 
bvanderveenCommented:
I don't think any other database has java stored procedures.   Oracle is the only one I am aware of.  You can use JDBC to accomplish the same thing outside of the database, though.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
girionisCommented:
> i want to know what steps i have to follow for this.

As far as Java is concerned the concept for loading up and handling a stored procedure should be *exactly* the same. All that changes is the driver to connect to the datatabase and the database's url.
0
 
bvanderveenCommented:
Could you clarify this?  Do you mean a stored procedure written in java, or calling a database stored procedure (in that db's implementation of a procedural language) from java?

Bill
0
 
girionisCommented:
I mean calling a db stored procedure. A stored procedure is not written in Java but in SQL and it resides on the server. Java simply calls it by obtaining a connection to the database and by passing any parameters the sp might be expecting.
0
 
bvanderveenCommented:
I was asking the questioner.  Oracle will permit you to load a strored procedure written in Java into the database.

>> A stored procedure is not written in Java but in SQL and it resides on the server.

Not really.  A stored procedure is a procedural-language program (that can usually embed SQL or otherwise interact with the database) that can be stored in the database.  In Oracle, you write in PL/SQL (or java, as noted above).  In SQL Server, Transact-SQL.  SQL itself is a set-oriented language, not a procedural language.  For instance, executing a query, looping through the records, following a decision tree based on the columns in those records are not something that is done in SQL, but some other, procedural language.
0
 
girionisCommented:
So ow do you write a db stored procedure in Java?
0
 
bvanderveenCommented:
For Oracle,

1. Write a java class.  The methods you can call directly have to be static.
2. Use loadjava utility (or surround class declaration with CREATE OR REPLACE JAVA..., but loadjava is my preferred method).
3. Write a PL/SQL wrapper like this:
         CREATE OR REPLACE PACKAGE RHAPP_JAVA_UTIL AUTHID CURRENT_USER AS
             FUNCTION runHost(p1 IN VARCHAR2, p2 IN VARCHAR2)
                  RETURN NUMBER;
         END   RHAPP_JAVA_UTIL;

     CREATE OR REPLACE PACKAGE BODY RHAPP_JAVA_UTIL AS
         FUNCTION runHost(p1 IN VARCHAR2, p2 IN VARCHAR2)
          RETURN NUMBER AS LANGUAGE JAVA
                NAME 'app.rbc.util.RunHost.runHost(java.lang.String, java.lang.String) return int';

    END RHAPP_JAVA_UTIL;

You can then call this function from PLSQL (or SQL if it is in the right format).  You can also use Oracle JDeveloper to do loadjava and generate your wrapper automatically.
0
 
girionisCommented:
So you actually write a java class that you somehow convert into SQL or a language that is understood by Oracle. I didn't know you could do that.
0
 
bvanderveenCommented:
Oracle actually has a JVM built into the db.  (Called Aurora).  Usually lags the other JVMs by a bit, though.  
0
 
girionisCommented:
I'd say my answer is valid since the calling of stored procedure is the same, regardless of the underlying database.
0
 
bvanderveenCommented:
His question seemed to be asking how to create a db stored procedure in Java in Postgres, like you can do in oracle.  I answered that you can't, which is correct.

We asked for clarification, and he didn't respond, so I'd say split between me an gironis.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.