How To Execute Stored Procedures

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

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

/* %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
Hi Guys,

The code for this tutorial released on forge on: http://www.outsystems.com/forge/component/974/how-to-execute-stored-procedures/.

Cheers,
Guilherme
Hi,

This works find for sqlserver I presume, but oracle not so much?

Nope, not so much.

For Oracle you're better off using the RuntimePublic.Db API in an extension and wrap each stored procedure in an Extension Action.
To execute oracle procedures i've used the Oracle Connector.

http://www.outsystems.com/forge/component/891/Oracle+connector+P9/
Hi,

I followed setup, but I got an error "Database returned the following error:
Error in advanced query SQL1: Could not find stored procedure 'GETITEM CODENAME'."
I guess that problem is that In my case Outsystems DB and External DB are in different SQL servers. Does it work in such case and how to setup it?

Best regards,
Albertas
Fabian wrote:
To execute oracle procedures i've used the Oracle Connector.

http://www.outsystems.com/forge/component/891/Oracle+connector+P9/
 The problem with the Oracle Connector is that it's only suitable for the .NET stack.

 
Albertas Repecka wrote:
I followed setup, but I got an error "Database returned the following error:
Error in advanced query SQL1: Could not find stored procedure 'GETITEM CODENAME'."
I guess that problem is that In my case Outsystems DB and External DB are in different SQL servers. Does it work in such case and how to setup it?
 
 Yes, it should work fine.

Make ure you have:
  • 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. 
Cheers
Hi,

I found out. For run application is enough to write
/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
EXEC [dbo].[getItemCodeName]

For testing in SQL tool have to be commented select from any table from external DB and explicity written external DB name in call procedure)
/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
--Select {UNIT}.*
--from {UNIT}
EXEC [AxDB30SpEU].[dbo].[getItemCodeName]
P.S.
So my problem was that I didn't try to run because test in SQL tool failed

Best regards
Albertas
By the way...

In the advanced query the -- can not be used as a comment...
Try to use the /* and */ or just remove the code you want to comment.


Hi,
 
You right.
But if comment -- starts from first column it works as comment.

Best regards,
Albertas
For anyone else struggling with this, /* %LogicalDatabase%=GetLogicalDatabase({Customer}) */
Customer in this example is the name of the referenced entity in your eSpace, not the database connection.
You save my life. Damn!! Thank you!!

Albertas Repecka wrote:
Hi,

I found out. For run application is enough to write
/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
EXEC [dbo].[getItemCodeName]

For testing in SQL tool have to be commented select from any table from external DB and explicity written external DB name in call procedure)
/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
--Select {UNIT}.*
--from {UNIT}
EXEC [AxDB30SpEU].[dbo].[getItemCodeName]
P.S.
So my problem was that I didn't try to run because test in SQL tool failed

Best regards
Albertas
 
 
And this was a very nice aclaration also. Thank you!

Steven Schultz wrote:
For anyone else struggling with this, /* %LogicalDatabase%=GetLogicalDatabase({Customer}) */
Customer in this example is the name of the referenced entity in your eSpace, not the database connection.