Re: Getting null when call execute() on a stored proc in sybase
- From: Joe Weinstein <joeNOSPAM@xxxxxxx>
- Date: Thu, 15 Jun 2006 09:49:25 -0700
claudio wrote:
Hi,
I'm having trouble with a call to a stored procedure that I have
created in sybase.
The issue is the next: I have a stored procedure that should return a
select statement (I say should because it returns a select when I
execute from SQLAdvantage), but when I call execute(), it returns
false, and I call getUpdateCount() to see what is going on, and returns
1.
I'm using Jdk 1.4.2, jConnect5.5 and ASE Sybase 12.x
The code of the stored procedure is next:
<sp-code>
create procedure Analisis.sp_perssproyec032
@arg1 varchar(255) = null
as
declare @rut varchar(255)
select @rut=@arg1
select pe.rut_person as rut, pe.nom_nombre as nombre, pe.nom_appate
as apellido1, pe.nom_apmate as apellido2, pe.e_mail as email, 'S' as
interno
from sisper_db..sp_pers pe, sisper_db..sp_rutp rp
where rp.rut= @rut
and rp.cod_ficha = pe.cod_ficha
UNION select pe.rut, pe.nombre, pe.apellido1, pe.apellido2,
pe.email, 'N' as interno from ap_pers pe where pe.rut = @rut
return
</ sp-code>
And the java code that calls the procedure is this:
<java-method-code>
// se crea la cadena de llamada al procedimiento
String llamada = "{call Analisis.sp_perssproyec032 ( ? ) }";
try {
CallableStatement cs =
accesoBD.getConexion().prepareCall(llamada);
cs.setString(1, "10050894K");
boolean seEjecutoProcedimiento = cs.execute();
if (seEjecutoProcedimiento == false) {
count = cs.getUpdateCount();
System.out.println("El procedimiento devolvio falso con count=" +
count);
} else {
System.out.println("El procedimiento se ha ejecutado
correctamente");
ResultSet resultados = cs.getResultSet();
while (resultados.next()) {
String rut = resultados.getString(1);
String nombre = resultados.getString(2);
System.out.println("RESULTADO rut=" + rut + " nombre=" + nombre);
} // end while
} // end else
} catch (SQLException e) {
e.printStackTrace();
} finally {
// accesoBD.terminarLlamada();
accesoBD.cerrarBD();
}
</ java-method-code>
The thing that is messing my head is that when I execute the proc from
SQLAdvantage, it works. But when i run the java method it works like a
update statement.
Please help, because I'm going crazy with this thing.
Bye.
The issue is that SQLAdvantage is skipping over/collecting the update counts
and your code needs to also. A procedure may return any series of mixed
update counts and result sets. Here is some well-polished efficient code for
fully processing the returns from any PreparedStatement with any stored
procedure.
boolean getResultSet = ps.execute();
int updateCount = -1;
while (true) { // handle all in-line results from any procedure
if (getResultSet) {
ResultSet r = ps.getResultSet();
while (r.next()) {
// process result set fully before calling getMoreResults()
}
r.close();
} else {
updateCount = ps.getUpdateCount();
if (updateCount != -1) {
;// process update count if you care about it
}
}
if ((!getResultSet) && (updateCount == -1)) break; // done with loop
getResultSet = ps.getMoreResults();
}
Joe Weinstein at BEA Systems
.
- Follow-Ups:
- References:
- Prev by Date: Problem with weblogic 8.1SP5
- Next by Date: Re: Problem with weblogic 8.1SP5
- Previous by thread: Getting null when call execute() on a stored proc in sybase
- Next by thread: Re: Getting null when call execute() on a stored proc in sybase
- Index(es):
Relevant Pages
|
