Callable Statement Syntax

Advertisement
I am using the callable statement to invoke a MS SQL Server stored procedure that will return a result set. I have several questions:
1) According to the Java documentation I should be using the following format for a prepareCall that will return a result paramater with 4 input paramaters:
{ ? = call SP_ABC(?, ?, ?, ?)}
In my particular case the stored procedure will return 14 pieces of data in a result set. Do I need to write code for 14 registerOutParameter fields? Is the syntax mentioned above accurate for the 14 pieces of data or do I need to repeat the question marks (?) 14 times? What shoud the syntax be for my prepareCall statement?
Advertisement

Replay

The ? in the sql syntax refer to variables passed to the procedure as either in or in/out parameters. You need to write code to register any OUT variables (including any return value).
The data returned from the SP will be returned as a ResultSet which is processed in the normal way.
So assuming that you have a procedure declared as:
create proc p @1 int, @2 int OUTPUT as ...then your code would look like this:
statement = connection.prepareCall("{?=call p (?,?}");
// register the return code
statement.registerOutParameter(1,Types.INTEGER);
// register the OUTPUT var @2
statement.registerOutParameter(3,Types.INTEGER);
// Set the input param @1
statement.setInt(2,0);
rs = statement.executeQuery();
// Get the return code
int rc = statement.getInt(1);
// Get the output param @2
int out = statement.getInt(3);
// process the result set
while(rs.next()) {...}Dave

How to return Multiple ResultSets Using Callable Statement

hi everybody, while i was working with callable statements i came across a problem of how to fetch Multiple Resultsets by means of Stored Procedures written for tables in Oracle. If any one can help me, pls do help me with a detailed explanation, and

Regarding callable statement

Hi all, I have a stored procedure which executes in sql query analyzer in 2 seconds. It executes without any error. after execution of the stored procedure i get near about 4000 records. Now i am trying to call the stored procedure using the callable

Return records from Stored Procedure to Callable Statement

Hi All, I am createing a web application to display a students score card. I have written a stored procedure in oracle that accepts the student roll number as input and returns a set of records as output containing the students scoring back to the JS

IF statement syntax in SQL script view

I need to include a "IF" condition in the "SELECT" section of my SQL script view. I tried the following syntax's but I get the error 'Incorrect SQL syntax near 'IF' 1.  IF(Revenue <> '0' AND Quantity <> '0', Revenue/Quantit

Parameter name passed in Set Callable Statement

Is it not possible to pass parameter name in the callable statement while using Oracle drivers for jk 1.4?? Thanks!Is it not possible to pass parameter name in the callable statement while using Oracle drivers for jk 1.4?? Thanks!Read other 19 answer

How to use Callable statements.

Hi. I'm just new in using JSP. I just want to ask how to call stored procedures (ex. from SQL Server) using the Callable statements. A sample code will be very helpful...... Ex. I have these parameters for my stored proc: name = "sentiments"; //

Using A callable statement in java

Hi all im trying to get results back from the database using a callable statement the problem is that it is placing / infront of single quotes.I need to get rid of this because it's not returning anything here is my code CallableStatement statementOn

High memory usage on JDBC 10.2.0.1.0 driver on Prepared/Callable Statements

We are observing high memory usage for each callable/prepared stmt, using 10.2.0.1.0 JDBC Driver. The char[] in oracle/jdbc/driver/T4CVarcharAccessor was alloted 64K to 320K and grows with usage. This problem is worse with 10.1.0.2. driver which was

Member formula IF statement syntax

Hi guys Can someone assist me with the syntax of the IF statement on a member formula. On the measure "Total Cost", i would like to apply a formula which calls value loaded to a series of other measures (m1, m2, m3, etc) such that if the Number

Caching of Prepared and Callable Statements

A Prepared Statement object represents a precompiled SQL statement. Where is this precompiled SQL statement stored, in the database? What happens when this object goes out of scope / garabage collected? Using the WebLogic App. Server's console, we ca

Callable Statement wot working properly

I have a situation where using CallableStatement only executes the stored proc intermittenly, while utilizing Statement works all the time. This didn't happen under WLS 5.1 SP 6. Does anyone else have the same problems. Also in both cases the jdbc lo

How to access the oracle database using JNDI in iplanet sp1(i have used CURSOR TYPE with CALLABLE STATEMENT)

 ya, I have restarted. Can you please tell me whether the path which i am giving is right or not in the context file? Thanks, Gana.Read other 7 answers

ArrayIndexOutOfBoundException when using Callable Statement in Weblogic 8.1

Hi all, We recently ported our application from 6.1 to 8.1. I have noticed the following exception in 8.1 (which was working fine in 6.1) java.lang.ArrayIndexOutOfBoundsException: 7 [ERROR] nfusion.admin.UpdateBureauLogin.5 131575 2003-10-15 16:21:58

Callable statement Clob problem..reading and writing

Hi, I am having problem ,,trying to read and write a clob object ... DB Server is oracle 9i and app server is WAS5.1 I am geting exception... java.sql.SQLException: Missing IN or OUT parameter at index:: pls check the code below.. Connection connecti

Syntax and format of a IF statement in formula XPath in a InfoPath field

Good day I needed assistance with IF statement syntax in a InfoPath field formula. If Field_1 = null concat(substring-before(txtComponentNo1, "."), ".", substring-before(field17, "."), ".", substring-after(field17,

Batching disparate JDBC statements

I am trying to batch execute a bunch of JDBC statements which are disparate - ie. insert , update. Is there a way to do this via Oracle's Thin JDBC driver? Someone mentioned that it might be possible using Oracle JDBC Callable statement interface to

Insert statements in jsp

I am having a problem. I would like to insert some form data into a database using jsp but I have coded it and it gives me an error insert statement syntax is incorrect here is my code <%@page import="java.sql.*"%> <html> <head>

Error --callable statment not suported

hi all, i am using mysql as database. try{      Class.forName("com.mysql.jdbc.Driver");      connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/kantest","gk","gk");      String sql = sql = "ins

MAXL Import data statement error

Can someone assist me in the maxl import data statement. In the import data statement "import database App.DB data from data_file "\\servername\\folder1\\folder1\\data.txt"; The error I get is trying to specify the syntax for the path of th