Logo 
Search:

Java Forum

Ask Question   UnAnswered
Home » Forum » Java       RSS Feeds

Problem in calling stored procedure

  Asked By: Kevin    Date: Jun 24    Category: Java    Views: 982
  

I am facing a problem in calling a stored procedure from java code.

I have a stored procedure named 'update_costing_table' it requires 10
input parameters. All of these parameters are initialised to a value,
which is assigned to the variable in case no value is passed for that
parameter while calling stored procedure.

In Query Analyser I am able to successfully call the procedure with a query

" EXEC update_costing_table @costing_code='4001' "

'costing_code' is one of the 10 input parameters. Rest are assigned
values from the stored procedure initial values.

Now I want to call this procedure, in the same way, from java code.

Please note I want to pass values to parameters using parameter names
as defined in stored procedure. Not by using placeholders '?'.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Hababah Younis     Answered On: Jun 24

U can use java.sql.Statement 's .execute() method to call  stored
procedures in the RDBMS via Java...

If is it giving any error plz give the details abt the error...

more info:

java.sun.com/.../Statement.html#execute(java.lang.\
String)

 
Answer #2    Answered By: Edfu Massri     Answered On: Jun 24

I am on the look out for a way to pass parameters  to a stored
procedure using their names as defined  in the stored  procedure.

java.sql.Statement 's .execute() should be called after the parameters
are set. But the problem  lies in the previous step. I'll elaborate --

As I mentioned in previous mail following command works in Query Analyser.
exec  update_costing_table @costing_code='4001' "

In java  I can achieve above result as under --
PreparedStatement pstmt =
connection.prepareStatement("call update_costing_table (?)");
pstmt.setString(1,"4001");
pstmt.executeUpdate();

But here parameter  is passed  using column Index not by column name.
And I precisely need to do it using column name only.

 
Answer #3    Answered By: Samuel Costa     Answered On: Jun 24

As for as my concern there is no direct way of passing values  to a
PreparedStatement using STRING NAMES, the only options exists is the
way of using parameter  indexes to pass  values. For tht also u have to
remember the order of indexes...

But if u still wanted to do, you can use Double dimension Arrays /
Hashtable & use a method to retrieve the corresponding index...

Go through my example, it may help u to understand wht im saying..

import java.sql.*;
import java.util.*;

public class PSDemo
{
static Hashtable params = new Hashtable();

static
{
params.put("EMPNO",new Integer(1));
params.put("EMPNAME",new Integer(2));
params.put("EMPSAL",new Integer(3));
}

public static void main(String args[])
{
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con =
DriverManager.getConnection("jdbc:odbc:JTESTING","admin","admin");

PreparedStatement pstSave = con.prepareStatement("INSERT INTO
EMP(EMPNO,EMPNAME,EMPSAL) VALUES(?,?,?)");
pstSave.setInt(getIndex("EMPNO"),Integer.parseInt(args[0]));
pstSave.setString(getIndex("EMPNAME"),args[1]);
pstSave.setDouble(getIndex("EMPSAL"),Double.parseDouble(args[2]));
pstSave.execute();
pstSave.close();
con.close();
System.out.println("Records Saved Successfully!!");
}
catch(Exception ex)
{
System.out.println(ex.toString());
}
}

public static int getIndex(String strHeader)
{
return Integer.parseInt(params.get(strHeader).toString());
}
}

 
Didn't find what you were looking for? Find more on Problem in calling stored procedure Or get search suggestion and latest updates.




Tagged: