Solved

java stored procedure

Posted on 2004-08-13
16
312 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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
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 will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

760 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

19 Experts available now in Live!

Get 1:1 Help Now