Log in to AnswerLog in to Follow
2018-05-30 02-48-37
Rui Barbosa
Staff
8105Views
50Comments
How To Execute Stored Procedures
Discussion
How-to

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
16
0
19 May 2015
Hi Guys,

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

Cheers,
Guilherme
3
0
22 Jun 2015
2016-04-21 20-09-54
Joost Landgraf
mvp_badge
MVP
Hi,

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

0
0
23 Jun 2015
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.
0
0
24 Jun 2015

Ricardo Silva wrote:

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.

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 



0
0
27 Sep 2018
To execute oracle procedures i've used the Oracle Connector.

https://www.outsystems.com/forge/component/891/Oracle+connector+P9/
0
0
26 Jun 2015
2018-12-11 13-32-04
Martijn Habraken
mvp_badge
MVP
Fabian wrote:
To execute oracle procedures i've used the Oracle Connector.

https://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.

 
0
0
07 Jul 2015
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
0
0
06 Jul 2015
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
1
0
07 Jul 2015
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
7
0
07 Jul 2015
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
 
 
1
0
28 Oct 2015

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:

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



0
0
17 Apr 2018
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.


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

Best regards,
Albertas
0
0
07 Jul 2015
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.
2
0
22 Jul 2015
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.
 
 
0
0
28 Oct 2015

SamyCode wrote:

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.
 
 

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


0
0
15 Jan 2017

Hi Indra,


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


Cheers

Guilherme

0
0
15 Jan 2017

Guilherme Pereira wrote:

Hi Indra,


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


Cheers

Guilherme


thank you very much Sir.

0
0
15 Jan 2017

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

0
0
26 Jan 2017

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?

0
0
06 Apr 2017

Thomas Smith wrote:

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


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?

0
0
29 Dec 2017

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}) */

0
0
19 Apr 2018
Why isn"t there a document in Outsystems to call store procedure, function for a variety of  external databases (mysql, etc). 
0
0
21 Apr 2018
Made wrote:
Why isn"t there a document in Outsystems to call store procedure, function for a variety of  external databases (mysql, etc). 
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

0
0
21 Apr 2018
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.

0
0
22 Apr 2018
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
0
0
22 Apr 2018
Guilherme Pereira wrote:
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
Hi Pereira,
If SQL Server can, why can not for other DBMS?
thank you and regards
0
0
23 Apr 2018
Gede wrote:
Guilherme Pereira wrote:
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
Hi Pereira,
If SQL Server can, why can not for other DBMS?
thank you and regards
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

0
0
23 Apr 2018
2019-11-12 17-31-25
Justin James
mvp_badge
MVP
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
1
1
22 Apr 2018
Justin James wrote:
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
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,


0
0
23 Apr 2018
Justin James wrote:
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
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. 

1
0
07 May 2018
Thank you very much Sir,
Bye-bye hacking and must be back to the right track.
best regards.
0
0
23 Apr 2018
2019-11-12 17-31-25
Justin James
mvp_badge
MVP
Timothy May wrote:
Justin James wrote:
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
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. 

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
1
0
08 May 2018
Justin James wrote:
Timothy May wrote:
Justin James wrote:
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
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. 

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,
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

0
0
08 May 2018
2019-11-12 17-31-25
Justin James
mvp_badge
MVP
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
0
0
08 May 2018
J.Ja - Thanks! I will take a look at it!

0
0
08 May 2018

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. 

Could not assign '' to 'TimestampVersion.CmpVersion'... Index was outside the bounds of the array.




It works fine as long as I am accessing the values from resultset. 
0
0
05 Oct 2018
Shailendra Bais wrote:

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. 

Could not assign '' to 'TimestampVersion.CmpVersion'... Index was outside the bounds of the array.




It works fine as long as I am accessing the values from resultset. 
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

0
0
07 Oct 2018
2019-11-12 17-31-25
Justin James
mvp_badge
MVP
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
0
0
09 Oct 2018
I am opening a new tread for this.
0
0
09 Oct 2018
Guys, I can call a procedure that receives a parameter, this procedure performs a truncate in a table whose name goes by parameter, with you? if so how? the database is oracle, I just need to call the procedure to clear a table that has many records, but many, help me.
0
0
19 Sep 2019
For Oracle on a procedure with only input-parameters it's quit simple:
The {ExistingExternalTable} is only used to define the right database-connection for the procedure to be executed.
or, but for DDL-actions or other transaction-actions not possible:

I still think the usage of Oracle-code can be implemented quit simple and created this idea: https://www.outsystems.com/ideas/6578/use-integration-studio-to-generate-actions-on-oracle-procedures-functions-and-pa
I hope Outsystems will be implementing this.


0
0
20 Sep 2019
Eric Slikker wrote:

good night, unfortunately it still didn't work, the following error happened:
the way you asked me to make the error below
Error executing query. Error in advanced query SQL1 in teste in STAudit (/* %LogicalDatabase%=GetLogicalDatabase({TB_Produto_Descricao}) */    begin      SP_LIMPA_DADOS_NF(@TableName)    end): ORA-06550: linha 5, coluna 1:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

   := . ( % ;
I did it anyway, putting exec, execute.
with exec gives the error below
Error executing query. Error in advanced query SQL1 in teste in STAudit (/* %LogicalDatabase%=GetLogicalDatabase({TB_Produto_Descricao}) */    begin      exec SP_LIMPA_DADOS_NF(@TableName)    end): ORA-06550: linha 3, coluna 8:
PLS-00103: Encountered the symbol "SP_LIMPA_DADOS_NF" when expecting one of the following:
   := . ( @ % ;

thanks for the support.
For Oracle on a procedure with only input-parameters it's quit simple:
The {ExistingExternalTable} is only used to define the right database-connection for the procedure to be executed.
or, but for DDL-actions or other transaction-actions not possible:

I still think the usage of Oracle-code can be implemented quit simple and created this idea: https://www.outsystems.com/ideas/6578/use-integration-studio-to-generate-actions-on-oracle-procedures-functions-and-pa
I hope Outsystems will be implementing this.




0
0
20 Sep 2019
Hi Reinaldo,
it looks like your Oracle-code  isn't constructed well. In Oracle every command must end with a ';'. 
So your code in the advanced SQL must have ';' at the end of the commands:
    /* %LogicalDatabase%=GetLogicalDatabase({TB_Produto_Descricao}) */
    BEGIN
      SP_LIMPA_DADOS_NF(@TableName);
    END;

Using Exec will not work, because that's not Oracle-syntax.
If you want to use a construction to execute a command, I think the next construction will work. Make sure the expand inline is set to 'Yes'. (I am not sure, because I don't have an environment to test this).

Good luck,
Eric


0
0
21 Sep 2019
Hi, 
The construction with execute immediate probably works but it's from a security perspective not the optimal solution. I would go for the initial solution. Hide the truncate in an Oracle stored procedure and call this stored procedure. The remark about the ";" is correct. 
Beste regards,
Matthieu de Graaf

Eric Slikker wrote:
Hi Reinaldo,
it looks like your Oracle-code  isn't constructed well. In Oracle every command must end with a ';'. 
So your code in the advanced SQL must have ';' at the end of the commands:
    /* %LogicalDatabase%=GetLogicalDatabase({TB_Produto_Descricao}) */
    BEGIN
      SP_LIMPA_DADOS_NF(@TableName);
    END;

Using Exec will not work, because that's not Oracle-syntax.
If you want to use a construction to execute a command, I think the next construction will work. Make sure the expand inline is set to 'Yes'. (I am not sure, because I don't have an environment to test this).

Good luck,
Eric




0
0
23 Sep 2019
Do you have a step by step example on how to execute an oracle stored procedure ( not a function ) returning a value ?
Thanks
BC
0
0
27 Apr
https://www.outsystems.com/forums/discussion/21999/how-to-call-oracle-stored-procedure-with-outputs-using-advance-query-sql/
0
0
28 Apr
Thank you for your response Eric, That method calls for creating 2 sql nodes. Do you know if both those nodes will execute in a single oracle session ? Since Oracle package variables are set at session level, is it possible that other connections to the database access this value as well since Outsystems uses connection pooling ?
My use case is that I am trying to insert data into a non-outsystems table and return the id of the newly inserted row back. 
0
0
30 Apr
Hi Berton,
Both statements run in the same session.
In case of just an insert the standard actions on the external table can be used. Outsystems will return the generated Id by default.
Make sure the Id is defined in the right way in integration studio (I don't know exactly, but it's the checkbox on defined by external system).

Insert statements in Outsystems are generated as 'Insert into .... return Id'. 

Good luck, 
Eric
0
0
30 Apr
Thank you Eric, This insert is for a non-outsystems table, I want to trigger an operation in the database for a downstream process not related to outsystems.
0
0
30 Apr
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.