Solved

java stored procedure

Posted on 2004-08-13
16
313 Views
Last Modified: 2010-03-31
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.
0
Comment
Question by:srini_r1
16 Comments
 
LVL 15

Expert Comment

by:JakobA
ID: 11792706
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
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11793193
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
 
LVL 35

Expert Comment

by:girionis
ID: 11807909
> 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
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11809994
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
 
LVL 35

Expert Comment

by:girionis
ID: 11810247
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
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11810396
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 35

Expert Comment

by:girionis
ID: 11810586
So ow do you write a db stored procedure in Java?
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11810851
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
 
LVL 35

Expert Comment

by:girionis
ID: 11811060
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
 
LVL 7

Expert Comment

by:bvanderveen
ID: 11811209
Oracle actually has a JVM built into the db.  (Called Aurora).  Usually lags the other JVMs by a bit, though.  
0
 
LVL 35

Expert Comment

by:girionis
ID: 12042285
I'd say my answer is valid since the calling of stored procedure is the same, regardless of the underlying database.
0
 
LVL 7

Expert Comment

by:bvanderveen
ID: 12043824
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
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
ID: 12280198
PAQed - no points refunded (of 30)

Computer101
E-E Admin
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mapAB Challlenge 35 123
unix in java example 9 52
Java Server Faces parameter pass? 6 37
Why my table column Id is not passed to java object? 4 17
For customizing the look of your lightweight component and making it look opaque like it was made of plastic.  This tip assumes your component to be of rectangular shape and completely opaque.   (CODE)
Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now