# DatabaseManager.ExecuteStoredProcedure **URL:** \ **Category:** Classic Servoy\ **Created:** [March 20, 2004, 8:40pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527 "2004-03-20T20:40:19Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [March 20, 2004, 8:40pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/1 "2004-03-20T20:40:19Z")
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 ---
**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\ **Post date:** [March 22, 2004, 1:09pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/2 "2004-03-22T13:09:43Z")
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) ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [March 25, 2004, 11:58pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/3 "2004-03-25T23:58:33Z")
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) ---
**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\ **Post date:** [March 26, 2004, 9:58am UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/4 "2004-03-26T09:58:51Z")
can you post the contents of spRoger ? ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [March 28, 2004, 11:59am UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/5 "2004-03-28T11:59:06Z")
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 ---
**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\ **Post date:** [April 1, 2004, 2:07pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/6 "2004-04-01T14:07:48Z")
Your method should look like this: ```auto //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. ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [April 1, 2004, 11:22pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/7 "2004-04-01T23:22:00Z")
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 ---
**Author:** ![jcompagner](https://yyz2.discourse-cdn.com/flex010/user_avatar/forum.servoy.com/jcompagner/32/4889_2.png) [@jcompagner](https://forum.servoy.com/u/jcompagner)\ **Post date:** [April 2, 2004, 8:25am UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/8 "2004-04-02T08:25:38Z")
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: ```auto var ds = databaseManager.executeStoredProcedure(controller.getServerName(), '{call spRoger(?)}',args,typesArray, 0); application.output(ds.getExceptionMsg()) ``` output? ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [April 25, 2004, 3:21pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/9 "2004-04-25T15:21:02Z")
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! ---
**Author:** ![megaquests](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@megaquests](https://forum.servoy.com/u/megaquests)\ **Post date:** [April 26, 2004, 2:04pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/10 "2004-04-26T14:04:27Z")
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 ---
**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\ **Post date:** [April 26, 2004, 2:16pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/11 "2004-04-26T14:16:50Z")
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. ---
**Author:** ![megaquests](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@megaquests](https://forum.servoy.com/u/megaquests)\ **Post date:** [April 26, 2004, 2:39pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/12 "2004-04-26T14:39:33Z")
Many thanks Jan your example in this thread did help a lot ![:D]( "Very Happy") 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.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 again Eugene ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [May 2, 2004, 10:59am UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/13 "2004-05-02T10:59:04Z")
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: 1. how can i pass the stored procedure output parameters back to the servoy method? 2. 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 ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [May 2, 2004, 11:04am UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/14 "2004-05-02T11:04:23Z")
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!!! ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [May 8, 2004, 2:26pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/15 "2004-05-08T14:26:40Z")
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: 1. how can i pass the stored procedure output parameters back to the servoy method? 2. 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 ---
**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\ **Post date:** [May 18, 2004, 8:22pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/16 "2004-05-18T20:22:27Z")
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 ---
**Author:** ![Jan\_Aleman](https://avatars.discourse-cdn.com/v4/letter/j/4af34b/32.png) [@Jan\_Aleman](https://forum.servoy.com/u/Jan_Aleman)\ **Post date:** [May 19, 2004, 9:49am UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/17 "2004-05-19T09:49:02Z")
I’ve copy pasted your code into my local Servoy it works perfect. PS. Interesting way of dynamically changing tables. ---
**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\ **Post date:** [May 19, 2004, 9:24pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/18 "2004-05-19T21:24:11Z")
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! ![:D]( "Very Happy") Paul ---
**Author:** ![rfleming](https://avatars.discourse-cdn.com/v4/letter/r/fbc32d/32.png) [@rfleming](https://forum.servoy.com/u/rfleming)\ **Post date:** [May 19, 2004, 11:53pm UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/19 "2004-05-19T23:53:16Z")
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 ---
**Author:** ![pbakker](https://avatars.discourse-cdn.com/v4/letter/p/a4c791/32.png) [@pbakker](https://forum.servoy.com/u/pbakker)\ **Post date:** [May 20, 2004, 8:50am UTC](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527/20 "2004-05-20T08:50:31Z")
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 [Next page](https://forum.servoy.com/t/databasemanager-executestoredprocedure/1527.md?page=2)