asked on

call stored procedure from java

Hi
I am new to working with java against the DB.

The DBA wrote the following stored procedure:

begin

config_pkg.get_app_config_xml(i_app_name => :i_app_name,
o_xml => :o_xml);
end;

remarks:
i_app_name defined as String
o_xml defined as Cursor

and I want to write a simple java code which run this procedure.
The procedure should get A string and return XML.

Assume I have already a connection to the db how will be the rest of code:

public static void main(String args[])
{
Connection conn = getConnection(...);

//WHAT SHOULD I DO HERE
}

dannysh

ASKER

BTW what is Cursor type ?

CEHJ

Please decipher that for those of use who don't understand the syntax ;-) You could, for instance have an IN parameter that is a String and an OUT parameter that is a Cursor, so you can iterate through a result.

matt_law

This should answer your question -

http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html

Its basically this -

try
{
int age = 39;
String poetName = "dylan thomas";
CallableStatement proc =
connection.prepareCall("{ call set_death_age(?, ?) }");
proc.setString(1, poetName);
proc.setInt(2, age);
cs.execute();
}
catch (SQLException e)
{
// ....
}

but the exact format of the call may vary depending on your DB.

A cursor is a reference to the current row of the result set.

If you need anymore info, what DB are you using - this will help answer your q ;)

tapasvi

Following code will help you:

but to use this code you need to make following changes:
in
call <stored proc name>(?,?,?,?)

insert your stored proc name
and number of "?" should be equls to the sum of input parameters + output parameters

my example assumes one input parameter and 3 output parameters
(return values from stored proc)

import following things:

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

----------------------------------------------

Connection con = null;
CallableStatement cs = null;
ResultSet rs = null;
try
{
con = DBConnection.getConnection();
cs = con.prepareCall("{ call <stored proc name>(?,?,?,?)}");
cs.setInt(1, Integer.parseInt("123")); //Input parameter one

//2 output param are as follows
cs.registerOutParameter(2, Types.CHAR);
cs.registerOutParameter(3, Types.CHAR);

rs = cs.executeQuery();

--------------------
hope this helps
tapasvi

ASKER CERTIFIED SOLUTION

tapasvi

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dannysh

ASKER

Hi Comment
First I want to thank you for your help.

The DB I am working against is Oracle.
Can you tell me please who do I register to Cursor output ?

10x