Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

call stored procedure from java

Posted on 2003-12-11
6
Medium Priority
?
360 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
[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
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
This video teaches viewers about errors in exception handling.
Suggested Courses

618 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