Solved

call stored procedure from java

Posted on 2003-12-11
6
351 Views
Last Modified: 2010-03-31
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
}




0
Comment
Question by:dannysh
6 Comments
 

Author Comment

by:dannysh
ID: 9921105
BTW what is Cursor type ?
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 9923702
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.
0
 
LVL 1

Expert Comment

by:matt_law
ID: 9923761
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 ;)
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 4

Expert Comment

by:tapasvi
ID: 9926305
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
0
 
LVL 4

Accepted Solution

by:
tapasvi earned 50 total points
ID: 9926315
sorry ..made one mistake here

should be 3 output params (as there are four question marks)

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

0
 

Author Comment

by:dannysh
ID: 9936871
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
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

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…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the “for” loop and how it works in Java. By comparing it to the while loop learned before, viewers can make the transition easily. You will learn about the formatting of the for loop as we write a program that prints even numbers…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…

803 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