Solved

java stored procedure

Posted on 2004-08-13
16
320 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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
 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.

628 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