Problem retrieving value from stored procedure

Classic Servoy
1

I have the following code which calls a stored procedure which creates a new record and then returns the id of the new record. Unfortunately, although the DB is returning the correct value, when my Servoy code runs, the new record is created but the return value is read as either null or blank.

Here is my servoy code:

var iArray = new Array(1); 
var argArray = new Array(1); 
iArray[0] = 1; 
argArray[0] = java.sql.Types.INTEGER;
 	var ds = databaseManager.executeStoredProcedure(currentcontroller.getServerName(),'{call spAddPanel(?)}',argArray,iArray,0)
ds.rowIndex = 1; 
var  newRec = ds[1] 
plugins.dialogs.showInfoDialog(  "newRec",  newRec)

Here is my stored procedure code:

CREATE PROCEDURE DBA.spAddPanel(@newID integer output)
as
declare @newID integer
begin
insert into panels( id_projects,name_long) values( 0,‘Unknown’)
select @newID = max(id_panels) from panels where id_projects = 0
end

I also tried writing the same thing as a function but I got the same result.

create function DBA.spAddPanel2()
returns integer as
declare @newID integer
begin
insert into panels( id_projects,name_long) values( 0,‘Unknown’)
select @newID = max(id_panels) from panels where id_projects = 0
return @newID
end

If I run this function outside of servoy I do get the right value returned.

I also tried having the stored procedure call the function and then just return the value the function returned to it – but again got the same result.

Create PROCEDURE DBA.spAddPanel(@newID integer output)
as
declare @newID integer
begin
set @newID=spAddPanel2()
end

What am I doing wrong???

2

without testing youre sp just jet
What about setting the maxReturnRows to 1 instead of 0?

Can you look into the ds variable after you call executeStoredProcedure? to see what it reall holds?

3

OK. Its working now.

Changing the 0 to 1 didn’t do anything, but when I looked at the value of ds I saw that the DB was returning an error message which told me what to fix.

Thanks for your help!