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.
Set up and test the database connection in the environment management console (Service Center)
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
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
/* %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 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
/* %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: