Questions on executeStoredProcedure

Classic Servoy
1

I have a bunch of stored procedure’s that create temporary tables, select and manipulate data from a bunch of different tables and insert the values into the temporary table, then return the temporary table with a select statement. I would like to have this returned as a dataset in Servoy but I haven’t been able to get it to work with executeStoredProcedure. I am using Sybase ASE. Below is an example of what I am trying to do.

Stored Procedure Definition

CREATE PROCEDURE MyTestProc
AS

create table #MyTempTable
(
test_string varchar(32) null
)

insert #MyTempTable values('Test1')
insert #MyTempTable values('Test2')

select test_string from #MyTempTable

Call from Servoy

//Execute a stored procedure.
var maxReturnedRows = 100;
var procedure_declaration = '{? = Call MyTestProc}';
var args = new Array()
//args[0] = java.sql.Types.VARCHAR;
//args[1] = java.sql.Types.NUMERIC;
//define the types and direction, in this case a 0 for input data
var typesArray = new Array();
//typesArray[0]=1;
//typesArray[1]=1;
var dataset = plugins.rawSQL.executeStoredProcedure("mydb", procedure_declaration, args, typesArray,maxReturnedRows);

I have got it to return a value by defining and using output paramaters but it only returns a value for a single row.

Any help on this is greatly appreciated,
Jason

2

Jason,

What happens if you do ‘Call MyTestProc’ in stead of ‘{? = Call MyTestProc}’ ?
The plugins.rawSQL.executeStoredProcedure() call returns a dataset if the sproc call returns a resultset.

Rob

3

Rob,
Thanks for the reply.

I tried calling the proc with ‘Call MyTestProc’ and it returned the error of “JSDataSet:exception:com.servoy.j2db.persistence.RepositoryException: com.sybase.jdbc3.jdbc.SybSQLException: Stored procedure ‘Call’ not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).
Stored procedure ‘Call’ not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).”.

I then tried with ‘MyTestProc’ and ‘Execute MyTestProc’ both resulting in empty dataset’s “DATASET: JSDataSet:size:0,selectedRow:-1” when I was expecting a dataset containg ‘Test1’,‘Test2’

Thanks,
Jason

4

Jason,

The way the plugin currently works is that the first statement is returned.
If it is a select, the result will be in the resulting dataset.

If you do any update/create/etc first you won’t receive the selected rows.

I have changed this in Servoy 6 (beta 2), in stead of the first select result, the last select result will be returned in the data set.
Your example returns the expected 2 rows.

As a workaround until then you could use 2 stored procedures, 1 to calculate the data and store it in a temp table and another one to select the result an do a cleanup.

Rob