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]