File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Callable Statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of EJB 3 in Action this week in the EJB and other Java EE Technologies forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Callable Statement" Watch "Callable Statement" New topic
Author

Callable Statement

Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Yeh!! somebody help ma ya.In the callable statement I used 7 IN parameter and 1 OUT parameter.I can't get the OUT parameter value.But without IN parameter it is working.I used Oracle Stored Procedures for this.
Pls somebody help me .I'm totally confused by this.


chandran..
Michael Hildner
Ranch Hand

Joined: Oct 13, 2000
Posts: 297
Let see some code. What type of return value are you expecting, how do you register the out parameter?
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Thanks Mike!
just I want to get the integer value thru OUT parameter.Here my codings.
Java coding:
import java.io.*;
import java.sql.*;
public class callstatdemo
{
public static void main(String arg[])
{
Connection con;
CallableStatement cs;
Driver dr;
String str1,str2;
int in1,in2;
double d1;
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection("jdbc dbc:raajesh","sys","yak");
if(con==null)
{
System.out.println("Connection not Established");
}
else
{
cs=con.prepareCall("{call testproduct(?,?,?,?,?,?,?,?)}");
cs.setInt(1,1);
cs.setInt(2,201);
cs.setString(3,"Raajesh");
cs.setInt(4,1);
cs.setInt(5,1);
cs.setInt(6,1);
cs.setInt(7,1);
System.out.println("After set");
cs.registerOutParameter(8,java.sql.Types.INTEGER);
cs.executeUpdate();
System.out.println("After execute");
in2=cs.getInt(8);
System.out.println(in2);
}
}
catch(Exception e)
{
System.out.println("Error Message : "+e);
}
}
}
Oracle Procedure:
Create or Replace Procedure testproduct ( Action1 Number, ProdId Number, ProdName VarChar2,ProdAmount Number, ProdStock Number, ProdPercent Number, SellPrice Number,Action IN OUT Integer )
As
V_CursorID Number;
V_NumRowsNumber;
V_UpdateTabVarChar2 ( 500 );
Begin
If ( Action1 = 1 ) Then

V_UpdateTab := 'Insert into PRODUCT
( ID, PROD_NAME, PROD_PURCHASE_AMOUNT, PROD_STOCK, PROD_PERCENT, PROD_SELLING_PRICE )
values
( '| | ProdId | | ', ''' | |ProdName | |''',' | | ProdAmount | | ',' | | ProdStock | | ',' | | ProdPercent | | ',' | | SellPrice | |' )';

Else
V_UpdateTab := 'Update PRODUCT
set
PROD_NAME = ''' | | ProdName | | ''' ,
PROD_PURCHASE_AMOUNT = ' | | ProdAmount | | ',
PROD_STOCK = ' | | ProdStock | | ',
PROD_PERCENT = ' | | ProdPercent | |',
PROD_SELLING_PRICE = ' | | SellPrice | | '
where
ID= '| | ProdId;

End If;
V_CursorID := Dbms_SQL.OPEN_CURSOR;
Dbms_SQL.Parse ( V_CursorID, V_UpdateTab, Dbms_SQL.native );
V_NumRows := Dbms_SQL.execute ( V_CursorID );

If V_NumRows = 0 And Action1 <> 1 Then
Dbms_Output.Put_Line ( 'No Matching Rows...');
End If;
Dbms_Output.Put_Line ( V_NumRows | | ' Rows Updated' );

Dbms_SQL.Close_Cursor ( V_CursorID );
If V_NumRows > 0 Then

Action := '0';
Else
Action := '1';
End If;
Exception

When Others Then
Dbms_Output.Put_Line ( 'Problem in Inserting Record...' );
Dbms_SQL.Close_Cursor ( V_CursorID );
Action := '-1';
end;
I'm totaly confused by this.
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
I notice when you are setting Action, you have quotes around it in the stored procedure. You would only do that if it were a String.
Try just Action := 0;
Dan
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi!!
Thanx for helping me.
I changed the codes.still the same errors is coming.
See i'm using Oracle8 and JDK 1.3
I want to know whether this version matches.
chandran..
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi!!
while using Oracle8 and JDK 1.3 I'm getting SQLException
saying
Nmeric or Value Error
while using Oracle8 and JDK 1.1.8 I'm getting SQLException saying

Specified Driver could not be loaded due to system error(Oracle ODBC Driver)
pls help me to trace my error.
Thankx
chandran
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi!!
Please help me ya!!. still i'm not get the result.
chandran

[This message has been edited by Raajesh Chandran (edited May 08, 2001).]
Vinay Reddy
Greenhorn

Joined: May 08, 2001
Posts: 3
Hi Rajesh
Nothing wrong with your java code.I think something wrong with your storedprocedure.You have declared the varialble in Stored procedure as 'ProdName VarChar2', is that correct? .please check your Oracle stored procedure.
Vinay
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi vinay!!
It works fine in oracle.By using another procedure I called that one and receive the return value.I'm using oracle8 and JDK1.3 in windows 95.Is there is any problem by this.
Thankx for replying me
chandran
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
I see some of your in parms are set to number and not integer. Try changing all the proc parms to integer and see if it works. A integer is supposed to be like a number, but with the driver maybe not. Remember that a number can have decimal places, not an integer. So I would give it a try.
Dan
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi Dani!!
Thankx.Still I'm getting the same Error.I'm using oracle8 JDK1.3 and windows 95.I don't know where I'm going wrong.Shall I try using Oracle8i instead of Oracle8.I think Oracle8 supports JDK 1.0 and Oracle 8i supports JDK1.2.Am I correct or not.
chandran..
Vinay Reddy
Greenhorn

Joined: May 08, 2001
Posts: 3
Hi Chandran,
While running your java program can you look into the Oracle database LOg.so that i can know what is goinng on In the Oracle database.I strongly feel that something wrong with your stored procedure.you can use oracle8 and JDK1.3 in windows 95.
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi Vinay!!
Can't Get U.without OUT parameter it works fine in both Oracle and Java.why it is not working when I include OUT parameter.
chandran
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Yeah guys!!

I got it.All my codings are correct.I just installed Oracle8i instead of Oracle8. As I said Oracle8i is supporting JDK1.3

Thankx a lot those who show interest to clear me.
chandran..
Smita Tyagi
Greenhorn

Joined: May 09, 2001
Posts: 21
Hello Rajesh,
Try by giving action as OUT only rather than IN OUT.
Smita
Raajesh Chandran
Ranch Hand

Joined: May 04, 2001
Posts: 103
Hi Smitha!!

I got it.Nothing wrong in my StoredProcedure.Only Version is problem.Only Oracle8i sends the OUT parameter to Java.We can pass values through other version.But Only Oracle8i allows U to get the OUT parameter.

Thankx for U'r suggestion
Chandran
Daniel Dunleavy
Ranch Hand

Joined: Mar 13, 2001
Posts: 276
Thanks for the update...its nice to know
Dan
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Callable Statement
 
Similar Threads
Prepared Statement Vs. Callable Statement
Callable statements
Using Boolean in Callable Statments
how to access an IN OUT parameter ?
OUT parameter