Getting a Connection to the Database in an Extension [DEPRECATED]

  
DISCLAIMER: The methods and APIs depicted in this post (and the supporting extension) are NOT a public and supported API and are thus subject to change at any time at the sole discretion of OutSystems.

For OutSystems Platfom 9, you should use the methods in the RuntimePublic API, as the ones depicted in this post are deprecated.
 
----

If you want to run a stored procedure, the correct way to do it is using an Integration Studio Extension to perform these actions. In order to do so you'll need to obtain a connection to the database. In this post I'll explain you how you can do it with proper integration with the Agile Platform.


Basically, you need to ask the Agile Platform's Transaction Manager for a transaction. You have two kinds of transactions you can ask for:
  • Private Transaction - This is a brand new transaction obtained using one of the Agile Platform's connection strings
  • Main Transaction - This is the transaction being used by the Agile Platform runtime

You do this by calling the following methods, respectively
  • DBTransactionManager.Current.GetMainTransaction()  [.NET]  - DbTransactionManager.getCurrent().getMainTransaction() [Java]
  • DBTransactionManager.Current.GetPrivateTransaction() [.NET] - DbTransactionManager.getCurrent().getPrivateTransaction() [Java]
Both these methods have an alternative counterpart taking an extra string parameter so  you are able to get transactions to access External Database Connections, for example, to get the Main Transaction for  DB Connection named "xpto" you'd call DBTransactionManager.Current.GetMainTransaction("xpto").


So, when should you use each kind of transaction?

Since you can only have one active reader per transaction for SQL Server, and the Agile Platform uses READ UNCOMMITTED isolation level in that Database Engine, you should use a Private Transaction for reading data in SQL Server. In Oracle, due to the READ COMMITTED isolation level you should always use the Main Transaction, unless you have good reason not to.

If you wish your changes to the database to be integrated into the request (have it committed if the request succeeds, rolled back if there is an abort transaction) you need to perform all your data affecting (updates, inserts) statements in the Main Transaction.

All private transactions you obtain need to be either commited or rolled back inside your extension action. If you obtain the Main Transaction, you should take care to release it before the end of your action, otherwise the Agile Platform will be unable to use it.

In attachment you can find an extension with .NET and Java code to exemplify accessing database.
Thanks Ricardo, I've got a need for this in the next bit of my project, and I was wondering how / whether it could be done - perfect timing! I'll let you know how I get on with it! Kind regards, Andrew.
Important notes on the usage of this API:

In order to avoid runtime errors and connection leaks you must always use the DBTransactionManager methods to commit, rollback or release transactions, and should never use the standard transaction API for the effect. This means that private transactions need to have one of the DBTransactionManager.Current.CommitTransaction or DBTransactionManager.Current.RollbackTransaction called on it before the end of your extension action. A main transaction must have DBTransactioManager.Current.ReleaseTransaction called on it before the end of the extension action. We suggest this is done using a try ... finally pattern as shown on the example extension.

Also note that the query / statement itself cannot COMMIT or ROLLBACK the transaction. These operations must ALWAYS be done using the OutSystems API for it.

Since this hinders the general maintainability of the application using an extension to execute a query is not recommended, as you should use a simple query or an advanced query for the purpose. If you really must perform a query inside an extension, you must remember to close the OSResultSet reader. For this purpose we suggest the use of a using clause in .NET or a try ... finally pattern in JAVA, as shown in the example extension.
Hi everyone

I have recently found this component, which seems a simpler approach to this topic:
http://www.outsystems.com/forge/component/392/oracle-connector/

Cheers, 
Acácio
Hello,

I am also using the same component as Acácio is using and it is very easy to implement.

Regards,
Sagar Nannaware
Hello,
 
I’m trying to create a SP call.
 
DbTransaction trans = DBTransactionManager.getCurrent().getMainTransaction(inParamDBConnection);
            
       try
        {
            DbCommand cmd = DBHelper.createCommand(trans, "begin CALC_MYARVAL(INparam1, INparam2); end;");
           
            DBHelper.AddParameter(cmd, "INparam1", DbType.Int32, inParamOfferteNr);
            DBHelper.AddParameter(cmd, "INparam2", DbType.Int32, inParamOfferteRegel);
            //IDbDataParameter outParam = DBHelper.AddParameter(cmd, "OutParam", DbType.String);
            //outParam.Direction = ParameterDirection.Output;
            //you can execute commands (UPDATE, INSERT, ETC) this way
            DBHelper.executeNonQuery(cmd);
        }
 
The question is what do I put in place of the DbType.Int32? Or where can I find the DbTypes in the API? We are using the Java Stack.
 
Kind regards,
Niek Oosterbeek.
You should use java.sql.Types enumeration to fill in values of that particular parameter. For example, if you are using an integer parameter, you would pass java.sql.Types.INTEGER to DbHelper.addParameter() .
and what about returning something from the stored procedure and fill in a outsystems variable?
I would like to note that as of the OutSystems Platform 9, the API depicted here is deprecated.

You should use the Runtime Public API for the purpose of accessing the database.
Hi, 

I'm using this api in an extension and when i call this code line :
  • DbTransaction trans = DBTransactionManager.getCurrent().getMainTransaction(<dbconnection>)
I always get an error of ClassDefNotFound, can you help understand why?

Thanks in advance,

Hugo
Hi, I would like to create tables and triggers inside an Extension. How could I accomplish this?