retrieve an output param defined in a stored procedure from java code  
Index ‹ java-programmer
Author Message
bego_colomer
PostPosted: 2003-11-19 19:03:00 Top
java-programmer, retrieve an output param defined in a stored procedure from java code Hi,
I've made a java application and i use a CallableStatement to call a
stored procedure. I use the method getString() of this class to
retrieve the value of the definded output param but it retuns "" (no
value). The code of the stored procedure is

CREATE procedure pruebaICM
@pANI varchar(20),
@pTABLA varchar(20),
@pInsert varchar(500),
@pUpdate varchar(1000),
@pFLAG varchar(1),
@pResultado VARCHAR(1) OUTPUT

as
begin

DECLARE @ani varchar(20)
declare @cliente int
DECLARE @sentencia nvarchar(1000)
DECLARE @tabla nvarchar(20)
DECLARE @sentencia_where nvarchar(50)
DECLARE @sql nvarchar(1050)
SET NOCOUNT ON
set @tabla = @pTABLA
set @ani = @pANI
SELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N'
where ani = ' + @ani
SELECT @sql
exec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente
OUTPUT
SELECT @Cliente
if (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')
begin
if (@cliente = 0)
begin
set @sentencia = N'insert into ' +
@pTABLA + N' (ani) values (' + @pInsert + N')'
select @sentencia
EXEC sp_executesql @sentencia
set @pResultado = 1
SELECT @pResultado
end

if (@cliente = 1)
begin
set @sentencia = N'update ' + @pTABLA +
N' set ' + @pUpdate
set @sentencia_where = N' where ANI =
' + @pANI
set @sql = @sentencia +
@sentencia_where
select @sql
EXEC sp_executesql @sql
set @pResultado = 2
SELECT @pResultado
end

end
else if (@pFLAG = 'B') or (@pFLAG = 'Borrar')
begin

if (@cliente = 0)
begin
set @pResultado = 0
SELECT @pResultado
end
else if (@cliente = 1)
begin
set @sentencia = N'delete from '+
@pTABLA + N' where ANI = ' + @pANI
EXEC sp_executesql @sentencia
set @pResultado = 3
SELECT @pResultado
end
end
end

However, when i call this stored procedure fron a query analyzer
windows it works well, the statements
are executed correctly and i can see that the value of the aoutput
param (@pResultado) changes depends on
the sentence executed (insert, update, delete). Then, why can't i
retrieve this param from my java code?
Thans very much for helping me!!!!!!!!!!!!!

P.D: i'm using jsk1.3.1_04 and sql server 7
 
Joe Weinstein
PostPosted: 2003-11-20 1:10:00 Top
java-programmer >> retrieve an output param defined in a stored procedure from java code

Bego?a wrote:

> Hi,
> I've made a java application and i use a CallableStatement to call a
> stored procedure. I use the method getString() of this class to
> retrieve the value of the definded output param but it retuns "" (no
> value). The code of the stored procedure is

Try setting nocount on before executing the procedure. Also try
completely processing all the returns (update counts) just in case,
before trying the CallableStatement.getString():

cstmt.execute();

while (true)
{
int updateCount = cstmt.getUpdateCount();
ResultSet rs = cstmt.getResultSet();

// When there are no results or counts, we're done.
if (rs == null && updateCount == -1)
break;

// Check to see if there is a ResultSet
if (rs != null) { // you'll be surprised!
while (rs.next()) {
System.out.println("Get first col by id:" + rs.getString(1));
}
rs.close();
} // Otherwise, there will be an update count
else {
System.out.println("Update count = " + cstmt.getUpdateCount());
}
cstmt.getMoreResults();
}

// Best to retrieve status after all result sets and update counts
// have been retrieved.
System.out.println( "Output status: " + cstmt.getString(1));

Joe Weinstein at BEA

>
> CREATE procedure pruebaICM
> @pANI varchar(20),
> @pTABLA varchar(20),
> @pInsert varchar(500),
> @pUpdate varchar(1000),
> @pFLAG varchar(1),
> @pResultado VARCHAR(1) OUTPUT
>
> as
> begin
>
> DECLARE @ani varchar(20)
> declare @cliente int
> DECLARE @sentencia nvarchar(1000)
> DECLARE @tabla nvarchar(20)
> DECLARE @sentencia_where nvarchar(50)
> DECLARE @sql nvarchar(1050)
> SET NOCOUNT ON
> set @tabla = @pTABLA
> set @ani = @pANI
> SELECT @sql= N'select @cliente=count(ani) from '+ @tabla + N'
> where ani = ' + @ani
> SELECT @sql
> exec sp_executesql @sql, N'@Cliente INT OUTPUT', @Cliente
> OUTPUT
> SELECT @Cliente
> if (@pFLAG = 'A') or (@pFLAG = 'Actualizar') or (@pFLAG = 'I')
> begin
> if (@cliente = 0)
> begin
> set @sentencia = N'insert into ' +
> @pTABLA + N' (ani) values (' + @pInsert + N')'
> select @sentencia
> EXEC sp_executesql @sentencia
> set @pResultado = 1
> SELECT @pResultado
> end
>
> if (@cliente = 1)
> begin
> set @sentencia = N'update ' + @pTABLA +
> N' set ' + @pUpdate
> set @sentencia_where = N' where ANI =
> ' + @pANI
> set @sql = @sentencia +
> @sentencia_where
> select @sql
> EXEC sp_executesql @sql
> set @pResultado = 2
> SELECT @pResultado
> end
>
> end
> else if (@pFLAG = 'B') or (@pFLAG = 'Borrar')
> begin
>
> if (@cliente = 0)
> begin
> set @pResultado = 0
> SELECT @pResultado
> end
> else if (@cliente = 1)
> begin
> set @sentencia = N'delete from '+
> @pTABLA + N' where ANI = ' + @pANI
> EXEC sp_executesql @sentencia
> set @pResultado = 3
> SELECT @pResultado
> end
> end
> end
>
> However, when i call this stored procedure fron a query analyzer
> windows it works well, the statements
> are executed correctly and i can see that the value of the aoutput
> param (@pResultado) changes depends on
> the sentence executed (insert, update, delete). Then, why can't i
> retrieve this param from my java code?
> Thans very much for helping me!!!!!!!!!!!!!
>
> P.D: i'm using jsk1.3.1_04 and sql server 7

 
 
Index ‹ java-programmer