I cant find any documentation on this - can anyone give advice and example of how to use. I used the ‘move code sample’ as a template but it does not work - debug stops at that line.
This functionality is crutial for those using MS SQL db’s
Roger
Below is the content of the upcoming 2.0 documentation:
(it has been tested with MS SQL as well)
Function
databaseManager.executeStoredProcedure(String dbconnection, String sp/function, Object arguments, number outputInput , number noOfRows)
Description
This function executes a database stored procedure or function and returns results if appropiate in a resultset. It takes the following arguments:
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 and for the returning parameters you need to specify the datatype, this is achieved using two arrays: an arguments array and an outputInput array. In the arguments array you specify the contents of the parameters you pass to it and for the returning parameters you specify the datatype. In the outputInput array you specify the direction of the parameter: 1 is input, 0 is output
Example:
Consider the following function (Oracle syntax):
create or replace function foo (val1 char) return char as begin
return val1 || ‘suffix’;
end;
The function above will concatenate ‘suffix’ to its input and return that string
To call it 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);
Notes:
-Oracle does not return a resultset when a stored procedure is called but an ORA-0900 error (indicating no resultset was produced which is correct)
I tired this example but it did not run the stored procedure. Can you please edit my example and post back to me.
Roger
//Execute a stored procedure
var maxRows = 1
var proc = ‘{call spRoger(globals.oldBrandId)}’
var args = new Array()
args[0] = 3000
var iArray = new Array(1);
var argArray = new Array(1);
iArray[0] = 1;
argArray[0] = globals.oldBrandId;
var dataset = databaseManager.executeStoredProcedure(controller.getServerName(), ‘call spRoger(?)’, argArray, iArray, maxRows);
application.output(globals.oldBrandId)
can you post the contents of spRoger ?
Hi Jan,
I wanted to start off with a simple example - so I created this sp just to test it all out. I am using the table roger just to confirm that the sp is actually being executed as I can query the table after running the test.
The 1st insert will proove that the sp is being called. The 2 other inserts proove that the correct value in the input parameter has been passed in correctly from the screen form field.
Question: What if the sp has no parameters - can you please provide an example of how you would call it in the function - do you provide nulls for the args arrays etc? Can you also provide additional examples where an sp only takes input parameters, and another example where an sp also has output parameters.
CREATE PROCEDURE spRoger
@oldBrandId int
AS
insert into roger (roger) values (‘yep’)
if @oldbrandid = 7 insert into roger (roger) values (‘seven’)
if @oldbrandid = 40 insert into roger (roger) values (‘forty’)
GO
P.S. It would be good if your forthcoming documentation for this and other functions show examples covering at least the majority of different scenarios.
Thanks, Roger
Your method should look like this:
//define array with arguments to be passed to stored procedure
var args = new Array()
args[0] = 7
//define the types and direction, in this case a 0 for outgoing data
var typesArray = new Array()
typesArray[0]=0
databaseManager.executeStoredProcedure('ms_northwind', '{call spRoger(?)}',args,typesArray, 0);
NOTE that in my example my connection to ms is named ms_northwind.
Does that make sense or is not clear enough yet? We’ll work on more examples for the documentation.
Jan
Did not work - is the 1st param the name of the sql database or the server connect (sql server name)?
Tried combinations of your example but no joy.
Also provide an example where there are NO params.
Roger
the first param is the server name in servoy
the best thing to do there is to use:
controller.getServerName()
if you are ofcourse on a form that is build on the same server as youre stored procedure is in…
What doesn’t work exactly? Do you get a error message?
Or does it still just stop on one line (which one exactly, the call?)
if it doesn’t stop what does the call:
var ds = databaseManager.executeStoredProcedure(controller.getServerName(), '{call spRoger(?)}',args,typesArray, 0);
application.output(ds.getExceptionMsg())
output?
using databaseManager.executeStoredProcedure
how can i pass the stored procedure return status back to the method?
so i can inform the user if action was successful or not!
thanks for your help with the stored procedure procedures… do you by any chance have any demo solutions that use stored procedures?
or are they something that servoy users so far do not bother with very much
many thanks
Eugene
Stored procedures are database specific and all our samples are generic so we don’t have any examples at the moment. THe example I posted in this thread is a fully working example for MS SQL and you should be able to use it for any stored procedure on MS SQL.
Many thanks Jan your example in this thread did help a lot
one of the last things we are trying to figure out is getting a success or failure message back to the servoy client from sql.. any suggestions
rfleming:
using databaseManager.executeStoredProcedurehow can i pass the stored procedure return status back to the method?
so i can inform the user if action was successful or not!
thanks again
Eugene
i have successfully called a MS SQL stored procedure passing in parameters - and the stored procedure receives the parameters and i can make use of them within the stored procedure.
i have 2 remaining questions:
-
how can i pass the stored procedure output parameters back to the servoy method?
-
how can i pass the stored procedure return status back to the servoy method? this is so i can take action within the method upon success or fail status of the stored procedure?
please provide code examples for each of the above
thanks for your help on this
it seems that for the input output array for the parameters when calling a stored procedure:
0 is for an input param and not 1
i tried it the other way round but it does not work
an important point!!!
i have successfully called a MS SQL stored procedure passing in parameters - and the stored procedure receives the parameters and i can make use of them within the stored procedure.
i have 2 remaining questions:
-
how can i pass the stored procedure output parameters back to the servoy method?
-
how can i pass the stored procedure return status back to the servoy method? this is so i can take action within the method upon success or fail status of the stored procedure?
please provide code examples for each of the above
thanks for your help on this
fiddling around with this one myself. I’ve got the follwoing method currently:
var maxReturedRows = 0;
var procedure_declaration = “{call test(?)}”;
var typedInput = plugins.dialogs.showInputDialog(‘Input’,‘Specify the name of the new column:’);
var args = new Array();
args[0] = typedInput;
//define the types and direction, in this case a 0 for input data
var typesArray = new Array();
typesArray[0]=0;
var dataset = databaseManager.executeStoredProcedure(controller.getServerName(), procedure_declaration, args, typesArray, maxReturedRows);
application.output(dataset.getExceptionMsg());
And in Sybase, I got the following SP named ‘Test’:
ALTER PROCEDURE “DBA”.“test” ( IN colname varchar(20) )
BEGIN
EXECUTE IMMEDIATE
‘ALTER TABLE customers ADD ‘||colname||’ VARCHAR(20)’
END
What I’m trying to accomplise is adding a column through Servoy to a table, by letting the user specify the column name.
When I execute the SP from Sybase Central, all works fine, but when executed through Servoy by the posted method, no column is added… I’m a bit clueless… since I’m also not getting any error message
Any help appreciated…
Paul
I’ve copy pasted your code into my local Servoy it works perfect.
PS. Interesting way of dynamically changing tables.
I was quite surprized that it worked for you, Jan, so I looked into it a bit more and found the following:
I’m getting the error that the table is not empty and therefor, the column cannot be added. I asume you tried it with an empty table?
If I empty the table, I can add columns with this SP.
But the strange this is that before, I had a SP in which I tried to pass a variable columnname, but my syntax was incorrect. Because it was incorrect, the wrong SP (see code below), called through Servoy, did create a new column in the DB, whether the table was empty or not.
Wrong SP:
ALTER PROCEDURE “DBA”.“test2”( in colname varchar(20))
as
BEGIN
alter table test
add colname varchar(20)
END
Another strange thing is that the correct SP (with the EXECUTE IMMEDIATE statement) does work when started/called from within Sybase Central, even if there are records in the table…
So, I’m a bit lost… If anyone can shed some light on this, I’d appreciate it!
Paul
not sure how to get return status reply back from executeStoredProcedure calling a stored procedure to inform user in servoy!!!
have built a work around solution where results or errors produced from within the stored procedure are written to a log table - which can then be looked at after the sp call by use of a simple servoy form showing records from the table
not quite user friendly but at least the user has a way to check that the sp ran successfully
If you do the following, it’s easy (at least if this is what you mean…):
var dataset = databaseManager.executeStoredProcedure(‘example_data’, procedure_declaration, args, typesArray, maxReturedRows);
if ( !dataset.getExceptionMsg() )
{
plugins.dialogs.showInfoDialog(‘Title’, dataset.getValue(1,1),‘OK’);
}
else
{
plugins.dialogs.showInfoDialog(‘Title’, dataset.getExceptionMsg(),‘OK’);
}
this code will show the error in a dialog if there was one, else it shows the value of the first Out Parameter of the procedure.
Hope it helps…
Paul