Getting Stored Procedure output

Classic Servoy
1

I am trying to execute a stored procedure which inserts a record into my organizations table and returns the id of the new record.

I create the procedure in Sybase using:

CREATE PROCEDURE DBA.spAddOrganization (@newID integer OUTPUT)
as
insert into organizations( name_long) 
values( 'Unknown' )
Select @newID = @@identity

My Servoy code which attempts to use it is:

var iArray = new Array(1);
var argArray = new Array(1);
iArray[0] = 1;
var ds = databaseManager.executeStoredProcedure(currentcontroller.getServerName(),'{call spAddOrganization(?)}',argArray,iArray,0)
var newRec = argArray[0]
application.output(ds.getExceptionMsg())

When that runs, I get the following output:

java.lang.ClassCastException null

What am I doing wrong?

2

please look at the sample of executeStoredProcedure()!

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 spAddOrganization(?)}’,argArray,iArray,0)
dataset.rowIndex = 1;
var theID = dataset[1]
application.output(ds.getExceptionMsg())

3

Johan,

Thanks for your help.

I want you to know that I did look carefully and extensively at the sample of executeStoredProcedure() as well as throughout the past forum posts. I try not to bother you guys till I have made a reasonable effort to find it on my own.

The problem is, your documentation is really not very good. As an example, I pasted the entire executeStoredProcedure() section below.

As you will see, there is no example of
argArray[0] = java.sql.Types.INTEGER;
which I needed. There is an example of argArray[0] = java.sql.Types.CHAR; but no explanation of how or when to use it, or what the various other Types are.

The other two lines I needed are:

dataset.rowIndex = 1;
var theID = dataset[1]

There is no mention of anything like this, nor any example at all, in the documentation.

That is why we have no choice but to bug you. There really is no way to figure it out by ourself.

Thankfully, you are the fountain if wisdom and information and for that I am greatful indeed.

Function executeStoredProcedure
Description Executes a SQL stored procedure or function; returns the resultset (if
applicable).

Syntax
databaseManager.executeStoredProcedure(String dbconnection,
String sp/function, Object arguments, number outputInput,
number noOfRows)

Parameters
dbconnection - the name of the database connection you want to execute the
function or stored procedure.
sp/function - the actual stored procedure or function you want to execute.
Use the following syntax:
For a stored procedure:
“{call proc (?,?)}”
where proc is the name of the stored procedure and each ? stands for a
parameter passed to it
for a function:
“{? = call func (?,?)}”
where func is the name of the function and each ? stands for a returning or
passing parameter
The parameters you pass to it have to be placed in an Array. This is achieved
using two arrays:
arguments array - specify the contents of the parameters you pass to it and for
the returning parameters you specify the datatype.
outputInput array - specify the direction of the parameter:
1 is input, 0 is output.

Example //the following function (Oracle syntax) will concatenate ‘suffix’ to its input
and return that string.
create or replace function foo (val1 char)
return char as
begin
return val1 || ‘suffix’;
end;
//to call the stored procedure and retrieve the result:
var iArray = new Array(2);
var argArray = new Array(2);
iArray[0] = 1;
iArray[1] = 0;
argArray[0] = java.sql.Types.CHAR;
argArray[1] = 1;
var resultset =
databaseManager.executeStoredProcedure(‘orcl’,“begin ? :=
foo(?); end;”, argArray,iArray,1);
var result = resultset.getValue(1,1);

4

Hi.

Its still not working.

I changed the code to:

var iArray = new Array(1);
var argArray = new Array(1);
iArray[0] = 1;
var ds = databaseManager.executeStoredProcedure(currentcontroller.getServerName(),'{call spAddOrganization(?)}',argArray,iArray,0)
ds.rowIndex = 1; 
var  newRec = ds[1] 
application.output(ds.getExceptionMsg())
application.output('newRec=' + newRec)

When it runs, I still get the Exception message: java.lang.ClassCastException null and the value for newRec is null (or maybe blank).

5

Hi again.

I’m still trying to get this to work.

I thought that perhaps returning the value as a dataset might be easier then trying to get the output param to work. So, I wrote a new stored procedure which returns the value I want as a dataset with one row and one column.

Unforntunately that didn’t work either.

The code I used to execute the proc and get the results is:

var iArray = new Array(0);
var argArray = new Array(0);
var ds = databaseManager.executeStoredProcedure(currentcontroller.getServerName(),'{call spAddOrganization2}',argArray,iArray,1)
var  newRec = ds.getValue(0,0)

Can you see anything wrong with this?

6

I already posted exactly what you should do why aren’t you still setting the type of the output param that you want back???

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 spAddOrganization(?)}’,argArray,iArray,0)
ds.rowIndex = 1;
var newRec = ds[1]
application.output(ds.getExceptionMsg())
application.output(‘newRec=’ + newRec)

If you use output variables you have to set the Type of the param you want it back.

7

Ok. Thanks. That worked.

Sorry I forgot to make that change the first time you told me… :oops: