How To Execute Stored Procedures

OutSystems platform can call existing stored procedures and functions from your existing databases using its SQL Tool

Although virtually anything one can do with a stored procedure can also be done with the Platform, in many scenarios, legacy data and logic needs to be kept instead of re-written or migrated. This is the perfect usage for the Platform capabilities to integrate with your existing databases.

When building brand new applications we do recommend using the Platform visual modeling language to code all business logic given its built-in monitoring and deployment capabilities.

OutSystems platform does not generate stored procedures nor functions as part of its data access layer nor it automates the deployment or management of existing stored procedures.

1. Database Connection

Set up and test the database connection in the environment management console (Service Center)

2. Security

Adjust your SQL server security to allow the connection user to query data and execute the stored procedures

Note: OutSystems only requires datareader and datawirter. There is no need to grant higher DB roles. See the OSRUNTIME user permissions

3. Stored Procedure returning one value or code

Use the following code in the SQL Tool (aka advanced query)

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */
EXEC @return_value = [dbo].[spINSERT_dbo_Customer]
    @FirstName,
    @LastName ,
    @PhoneNumber ,
    @eMailAddress,
    @Priority ,
    @CreateDate

SELECT @return_value 

Use the LogicalDatabase to hint the platform on what database connection to use. In this case we are setting to use the same database connection set up for the customer table

You must create a structure to hold your result set

4. Stored procedure returning a Result Set

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */
EXEC [dbo].[spSELECT_CustomerEmailByID]
    @CustomerID 

Use the Logical Database to hint the platform on what database connection to use. In this case we are saying use the same database connection set up for the customer table

You must create a structure to hold your result set

You must have a single result set for each stored procedure

Code for the Stored Procedures:
 

USE [EXT_DB]
GO
/****** Object:  StoredProcedure [dbo].[spINSERT_dbo_Customer]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spINSERT_dbo_Customer] @FirstName varchar(25), @LastName varchar(25), 
@PhoneNumber varchar(15), @EmailAddress varchar(25), @Priority int, @CreateDate datetime

AS

SET NOCOUNT ON


-- 1 - Declare variables
DECLARE @CUSTOMERID int
-- 2 - Initialize variables

-- 3 - Execute INSERT command
INSERT INTO [dbo].[Customer]
           ([FirstName]
           ,[LastName]
           ,[PhoneNumber]
           ,[EmailAddress]
           ,[Priority]
           ,[CreateDate])
     VALUES
           (@FirstName
           ,@LastName
           ,@PhoneNumber
           ,@EmailAddress
           ,@Priority
           ,@CreateDate)
           
SELECT @CUSTOMERID= @@IDENTITY

return @CUSTOMERID

USE [EXT_DB]
GO
/****** Object:  StoredProcedure [dbo].[spSELECT_CustomerEmailByID]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spSELECT_CustomerEmailByID] 
    -- Add the parameters for the stored procedure here
    @CustomerID int
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT CustomerID,EmailAddress 
        from dbo.Customer
        where customerid = @CustomerID or @CustomerID = 0
    Order by CustomerID desc
END

• Created a database connection with a user that can execute the stored procedure
• Created an extension which maps at least 1 table/view from that database connection
• Remember to use the /* %LogicalDatabase%=GetLogicalDatabase({table/view}) */ in the advanced query so it knows which database to query
• You may need to fiddle a little bit with the procedure name EXEC [dbo].[spSELECT_CustomerEmailByID] note the square brakets, the dbo, etc. 

/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
EXEC [dbo].[getItemCodeName]

/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
--Select {UNIT}.*
--from {UNIT}
EXEC [AxDB30SpEU].[dbo].[getItemCodeName]

SamyCode wrote:

How to call mysql stored procedure in java stack?thank you

Hi Indra,

For mysql you use the runtime public db api. You can find an example on the documentation here

Cheers

Guilherme

Guilherme Pereira wrote:

thank you very much Sir.

Does anyone khow how to call a stored procedure in DB/400 ?

I am on OS 10 and the Hinting (/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */) does not seem to work for Functions when testing.

I even added 

--Select {UNIT}.*
--from {UNIT}

to no avail...

 Any Ideas?

Thomas Smith wrote:

Based on my testing, functions seem to work fine for queries against tables in the same LogicalDatabase as the tables being queried, but I too am having trouble getting any stored procedures to run in my external environment. I'm sure I tested this successfully when we were evaluating the system under OS9.  It was of the reasons we chose Outsystems because of the platforms ability to do this.  

Can someone please confirm that this no longer works in 10?  Or better yet, what we need to do to make this work again?

In my case, I want to retrieve the same 5 pieces of information from x number of tables (and growing) and just thought I could pass the table name in to a stored procedure and get that information, but instead I've had to resort to creating a long manually maintained union query against all x tables to get the same information without the stored procedure.

If this is now only available through the runtime public db api for MSSQL too, then are there steps somewhere that show where this code is written and stored?  I've not written anything in C# before.  It seems more complicated than just calling the stored procedure with input parameters from the advanced SQL box...but maybe there is another CS or Java box that I'm missing some place in Service Studio?

Hi,

We were trying to solve the similar issue because of wrong name in GetLogicalDatabase.
I understood the concept now.
The following solution works for me as well.

Thanks for the help.

Albertas Repecka wrote:

/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
EXEC [dbo].[getItemCodeName]

/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
--Select {UNIT}.*
--from {UNIT}
EXEC [AxDB30SpEU].[dbo].[getItemCodeName]

For mysql:

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */

call store_procedure;

but the Customer above must be changed to the entity used in the store procedure. Sometimes, the Entity IS NOT JUST a representative of an external entity (you can fail for one entity, and just copy others):

/* %LogicalDatabase%=GetLogicalDatabase({Customer}) */

/* %LogicalDatabase%=GetLogicalDatabase({Supplier}) */

Why isn"t there a document in Outsystems to call store procedure, function for a variety of  external databases (mysql, etc). 

Made wrote:

Hi,

There is. You can find it on the knowledge base here: https://success.outsystems.com/Documentation/10/Reference/OutSystems_APIs/RuntimePublic.Db_API/Call_a_Stored_Procedure

Cheers

Guilherme

Hi Pereiea,

Thank you for quick reply. 

I mean in a Low Code way using Sql Advance widget.

Sometimes, i use the notation /*logical database* / and it works for some queries; sometimes it does not work for the same mysql external database scheme. 

1. If i create simple query like select * from {ENTITY1} and i use in the logical database {ENTiTY1} it works.

2. If a query select * from {Entity2!} where {Entity2} has relation to Entity1  it works, otherwise i need to change the wntity1 in the logical database.

3. If i create a store procedure that is simplewitg query above, it works.

4. I i create a dynamic query it does not work.. need priviledge for osrunrtime to the store procedure.

So i think it is better for Outsystems to document the low code way as access to database is very common in all application, why still must go to the high code way..using that API... low code is the main reason for us to use Outsystems as a platform.

Thank you and regards.

Hi,

The method described in this thread is not supported and for sql server only (i’m surprised it even works in some cases in mysql)

There’s no low code solution for that and you should used the official supported way using the RuntimePublicDB APi.

Cheers,

Guilherme

Calling Stored Procedures isn't "low code". It's HIGH CODE. You now have two places to maintain the logic... and one is completely outside of OutSystems (your stored procedures). OutSystems should NOT encourage this (not good) behavior by making it easy for you.

OutSystems is a pretty mature platform. If something is still "hard" or requires a lot of work, it is usually because the people designing the tool have decided that it isn't the right way to do things.

J.Ja

Guilherme Pereira wrote:

Hi Pereira,

If SQL Server can, why can not for other DBMS?

thank you and regards

Justin James wrote:

Hi Justin,

Thank you. But, I am still thinking that Outsystems is designed for the easy use (even for non programmer background). If, I read another view (from database concept), it is rather different, it is encouraged to use database store procedure for single maintenance.

If Outsystems does not take it, there is a chance that other platform will provide it. So, make it happen, Outsystems, .. :)...

regards,

Gede wrote:

Hi,

This method is not supported in any DBMS. It’s achack that work on MSSQL but not in other because esch DBMS works differently.

For other the supported way is using the RuntimePublic DB API.

Cheers,

Guilherme

Thank you very much Sir,

Bye-bye hacking and must be back to the right track.

best regards.

Justin James wrote:

Justin, There are some cases where using OutSystems Aggregates and Logic is far too slow for production use. 

It is not bad practice to use the right tool for the right job. OutSystems is absolutely great for Mobile and WebApp development. It is even great for very simple database design and limited database processing.

Oracle and SQL Server (and MySQL is slowly catching up) have really advanced execution plan features that allow all sorts of high-performance database updates. For instance, the WITH features of Oracle and SQL Server make many things possible that are simply not doable or would be many orders of magnitude slower if done in OutSystems directly. I routinely use Stored Procedures to ask the database engine to do heavy lift processing that would "kill" my OutSystems platform instance.

In my experience, using OutSystems to build sophisticated business applications, OutSystems is great for 80+% of the solution. Luckily, and this is a key reason I recommend OutSystems to my clients, you can step outside the OutSystems platform, with Integration Studio, when you need to. 

In general, OutSystems leverages a very small slice of the features of Oracle, SQL Server and MySQL - those additional features are not fluff, they are often necessary and they are getting better all the time. These features are not "replaced" or "obviated" by OutSystems. When you need those features it is entirely appropriate to leverage Integration Studio. Again, use the right tool for the right job.

One more comment regarding "HIGH CODE". I have numerous examples of reaching a point of "diminishing returns" when trying to implement a complex database process in OutSystems. I'm talking about  really complex aggregates and logic with multiple nested iterations over record sets here, I find I can accomplish all of the same process in a compact stored procedure in less than a page of code. Granted, you can't ask a "citizen developer" to write that code but it is not VOLUME  - it is the requirement to understand SQL.

So, in general, if OutSystems can do anything to make it easier for those of us who "must" use Integration Studio to leverage our databases, then we should expect OutSystems to be the best it can be in this regard. Otherwise, somebody else will come along that does. 

Timothy May wrote:

Keep in mind, I do not, nor have I ever, work for OutSystems.

I agree, use the right tool for the job.

However, in virtually every case I have encountered where someone told me they "needed" to do things this way, they were just plain wrong. They were using a stored procedure to compensate for either a knowledge/skill shortcoming (of either OutSystems itself, or databases in general), or DB design decisions.

I have a very stout skepticism when someone tells me that they "need" stored procedures to access the OutSystems database, especially with a "greenfield" OutSystems application.

If someone tells me that they "need" stored procedures dealing with an existing DB, particularly if they are talking about re-using existing stored procedures, I will be more likely to believe it, but I would still want to take a look on a case-by-case basis.

The bottom line, is that there are a number of things that the system will allow you to do, or make possible, but will not elevate it to the same level as using an Input widget or a Calendar control or whatever, because they aren't in the 80% side of the 80/20 rule, and in many cases they are edge cases even within the 20%.

Perhaps integration of stored procedures should be pushed to the level of integrating tables and views in Integration Studio, where it's "configuration, not code". A good case could be made for that, and I'd most certainly support it (because we'd be talking about an EXTERNAL DB which we already expect to be maintained separately anyways).

But the absolutely dead last thing the system should be encouraging is the writing of stored procedures against the OutSystems tables, and that is what I truly do not want to see being promoted to the same level as Aggregates or Advanced SQL.

J.Ja

Justin James wrote:

Justin,

For clarity, I was talking about accessing external databases. I didn't see where, in the thread above, folks were trying to define and call stored procedures in the native OutSystem database. With the names of the tables changing in the native OutSystems database (to support versioning), I'm mystified as to how one would even attempt to write stored procedures on the native database.

We don't use Internal OutSystem databases much as the limitations of the internal OutSystems database are too constraining for our applications. Most of our applications are large scale FinTech. The "mushy" database schema approach (mangled table names to support versioning) would be a hard sell in a supply chain audit for instance. Same with with the multi-tenant implementation of segregation by Key (Instead, you need to have separate application databases by tenant and switch among them based on user login). So we use external databases. In my last company we deployed and managed OutSystems on our own AWS stack. In my current company we are trying out the OutSystems PaaS solution combined with AWS RDS. So far, so good! 

So, between compliance requirements (PCI etc. and up) and the need for performance against really large datasets, we pretty much end up with all the usual big database design, admin and performance tweaking (partitions, clustering, sharding, etc) headaches. Like you said above "because we'd be talking about EXTERNAL DB which we already expect to be maintained separately".

I love the ER design tool in OutSystems for quick prototyping (hard to beat!). We then port the  database design using TOAD, ER/Studio or whatever the client wants so we can generate the usual set of scripts for versioning, updates and maintenance. It would be really great if there was a quick way of exporting the prototype schema into the external database. It's not a big deal with few dozen tables, but it gets old when you get to 100+ tables.

We isolate the application tables in a db module. When we create the OutSystems extension for the external db, it is easy to re-target the dependencies and away you go. If you are careful to manage your foreign keys properly, the whole process is quite painless. There were some strange issues with this process early on but version 10 works like a charm. 

I'd love to see OutSystems providing better support for Procedures and Functions when creating the extension as this would be a time saver for us. That is why I responded to your post - to make it clear there IS room for improvement and a need for improvement in these tools.

While I'm ranting, it would also be great to see direct support for optimistic locking (instead of having to hack it in the way we do now).

I'm working hard to get OutSystems adopted more widely in enterprise accounts, and all these things matter for those accounts. 

Now I am really curious how one would write stored procedures against a native OutSystems db - that would have to be a nightmare ;) I guess you would have to read the OutSystems metadata and then engage in a bunch of code generation in the SQL Server side - darn near crazy in MySQL, hard in SQL Server and somewhat annoying in Oracle. I think I just talked myself out of it ;)

Onward and upward!

Tim

Tim -

It sounds like a lot of your pain in this particular process can be solved with the View4Entity component. With some quick changes to it (or even just find/replace on the DDL it makes) it can crank out schema creation stuff you need, and even lets you export/import configurations across environments if you need it to. (And it would make writing stored procedures against the native DB much easier too)

J.Ja

J.Ja - Thanks! I will take a look at it!

Ricardo Silva wrote:

We used the solution with advanced sql extensively in our external Oracle DB OutSystems  environment (version 10) both on a .Net and Java environment. Never had any problems. Besides that you can call your stored functions by means of

select myfunction

from  table_with_only_one_record

Regards,

Matthieu 

I am using outsytems Advanced sql to call sql but this case never worked for me . Till now I could manage some how but now I really need to know how can make this work. 

My stored procedure call returns resultset but also set the total count for records to a out variable. 

I have used resultset and out parameter with success till now but I never used this combination and neither it seems working for me. We have multiple such SP as they support pagination using existing SP . 

How can access the resultset and out parameter using the single call ? 

I get following error when I tried to access the value of output parameter. 

Shailendra Bais wrote:

Hi Shailendra,

For the next time, could you please open another thread, because this is clearly another subject. 

My 2 cents for a solution:

Option A) (if you are on Oracle) Make 2 new stored procedure/function. Both call your existing stored procedure, but return either the resultset or the count. For performance reasons you could cache the value of you existing stored procedure in package variables

Option B) Make a stored procedure, which calls your existing procedure and returns the data in the form of Json. Deserialize this in OutSystems.

Matthieu

If you are using Oracle, you may need to use an extension for this. I know in the past, the Oracle install doesn't play quite right with calling stored procs in Advanced SQL.

https://www.outsystems.com/forge/Component_Versions.aspx?ProjectId=891

https://www.outsystems.com/forge/component/1305/advancequery/

J.Ja

I am opening a new tread for this.