Solved

call stored procedure from java

Posted on 2003-12-11
6
357 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports Licensing Questions 4 80
Fast way to search item into Java Array (Rhino compatible) 2 45
netbeans can not run self signed app 8 34
Java pass by reference 3 69
Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
In this post we will learn different types of Android Layout and some basics of an Android App.
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…
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…
Suggested Courses

740 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