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
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
/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */ EXEC [dbo].[getItemCodeName]
/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */ --Select {UNIT}.* --from {UNIT} EXEC [AxDB30SpEU].[dbo].[getItemCodeName]
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:
Great Albertas, it works!
Now let me add a little fix in order to execute stored procedures in Azure SQL.
If you try to test with:
/* %LogicalDatabase%=GetLogicalDatabase({UNIT}) */
--Select {UNIT}.*
--from {UNIT}
EXEC [AxDB30SpEU].[dbo].[getItemCodeName]
You will receive this error:
"Database returned the following error: Error in advanced query getItemCodeName: Reference to database and/or server name in 'AxDB30SpEU.dbo.getItemCodeName' is not supported in this version of SQL Server".
But if you comment the external database name it works!
EXEC /*[AxDB30SpEU]*/.[dbo].[getItemCodeName]
So, thanks for the tip.
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?
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({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
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.
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,
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.
Hi Pereira,
If SQL Server can, why can not for other DBMS?
thank you and regards
Gede wrote:
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.
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 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,
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.
Thank you very much Sir,
Bye-bye hacking and must be back to the right track.
best regards.
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.
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 - Thanks! I will take a look at it!
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.
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/
I am opening a new tread for this.
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.
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.
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.
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
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
Do you have a step by step example on how to execute an oracle stored procedure ( not a function ) returning a value ?
Thanks
BC
https://www.outsystems.com/forums/discussion/21999/how-to-call-oracle-stored-procedure-with-outputs-using-advance-query-sql/
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.
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
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.
Hi there,Today I'm developing an app in outsystems and I get problems to use Stored Procedure. (SQL Server)The problem is Outsystems does not find my Stored Procedure.Actually I have the connection to the external database and the entities into my app.So, I want to know if someone knows what can I do to fix the problem?
This app is going to be in different environments.PD: If I write the name of the database before the [dbo].[Store_Procedure] Outsystems execute the Stored Procedure [DB_Name].[dbo].[Store_Procedure] but I don't wanna do that because this app is going to be in different Environments so the name of database is different not dev.
Hi Rui,
Here is my issue, I am able to reach the backend database through intergration studio and in a c# extension i put together. In order to leverage low code I want to use advaned query and the hint
/* %LogicalDatabase%=GetLogicalDatabase({XXXXX}) */
where XXX is the Database connection named in Service Center as in step one I believe yours is EXT_DB under the Name field. Should your example above been set to /* %LogicalDatabase%=GetLogicalDatabase({EXT_DB}) */
Either way when I call the stored procedure through the advanced query I get stored procedure not found. WHen I add the databasename as test.dbo.storedprocedure I get database test not found.
Again in the c# extension it works in a non low code approach.
My guess is I am using admin credentials to reach an Azure Sql server db and I may need to create another login account under the test instance for the hint to work. Not sure that will solve the issue.
Just wanted to check with you thanks
We are trying to call a stored procedure for one of our client . Stored procedure is created in external SQL Server DB, which accepts 2 input parameter and provides around 250+ output in one record.
Procedure is created to accept a unique identifier of a customer and as part of Output provides all the related information of the customer.
While calling the stored procedures from the Advance SQL in server action we are able to store the output of the stored procedure in a Structure variable with 11 attributes, once i am creating the 12th attribute in Outsystem, it is displaying an error.
Where as till 11 attributes stored procedure works.
Below is the screenshot of the stored procedure call from advanced sql
Below is the output when the stored procedure is run keeping 11 attributes as part of the strucutre
Also we have noticed that the query output which is returned from the stored procedure, order of the output is not same as the order of the output declared in the stored procedure.
Any info on this issue will be really helpful.
Thanks and regards,
Debraj