Skip to main content

Reference

 

OutSystems

Call a Stored Procedure

To call a stored procedure you have to use some native objects specific to the language you are using (.NET or Java) that you can get by calling the RuntimePublic.Db API methods. In order to obtain them, all query objects have a method that return the language native object:

  • .NET: use method GetDriver<ClassName>  method in your C# code;
  • Java: use method getDriver<ClassName> in your Java code.

When using language internal objects (.NET or Java), make sure that you comply with:

  • The database engine's specific syntax;
  • The specific behaviors of the driver.

Example

In this example, we have a external database accessed through a connection called 'Data Warehouse' that is defined in the environment management console.

We will call a store procedure called COMPUTE_SCORE, which calculates the score performance for an employee. To do it, it receives the employee's name and the output parameter, an integer number, and returns that parameter with the value correspondent to the performance for the employee.

C#

using OutSystems.RuntimePublic.Db;

// Get a reference (dbaProvider) to the external database "Data Warehouse"
DatabaseProvider dbaProvider = DatabaseAccess.ForExternalDatabase("Data Warehouse");
 
SqlHelper sqlHelper = dbaProvider.SqlHelper;

using (RequestTransaction trans = dbaProvider.GetRequestTransaction()) {
    using (Command cmd = trans.CreateCommand("COMPUTE_SCORE")) {
        // There is no API to set the command type so you need to access the
        // underlining ADO.NET objects
        cmd.GetDriverCommand().CommandType = CommandType.StoredProcedure;
        
        // Set the employee (input) and performanceScore (output) parameters
        cmd.CreateParameter("employee", DbType.String, "John Baker");
        var performanceScore = cmd.CreateParameter("score",
                                                   DbType.Int32, DBNull.Value);
        
        // Set parameter direction on ADO.NET Data Parameter
        // The driver will fill the output parameter with a value after the query executes
        performanceScore.GetDriverParameter().Direction = ParameterDirection.Output;
        
        // Call the store procedure
        cmd.ExecuteNonQuery();
        
        // Print the store procedure's output
        Console.WriteLine(performanceScore.Value);
    }
}

Java

// Get a reference (dbaProvider) to the external database "Data Warehouse"
DatabaseProvider dbaProvider = DatabaseAccess.forExternalDatabase("Data Warehouse;");
 
SqlHelper sqlHelper = dbaProvider.getSqlHelper();
 
try {
   	RequestTransaction trans = dbaProvider.getRequestTransaction();
   	try {
         	Command cmd = trans.createCommand(String.format("BEGIN COMPUTE_SCORE(%s, %s); END;", 
         					  sqlHelper.prefixParam("employee"), sqlHelper.prefixParam("score")));
         	try {
			// Set the employee (input) and performanceScore (output) parameters
			cmd.createParameter(sqlHelper.prefixParam("employee"), Types.VARCHAR, "John Baker");
			DataParameter performanceScore = cmd.createParameter(sqlHelper.prefixParam("score"),
									     Types.INTEGER, null);
			
			// Set parameter direction on ADO Wrapper Data Parameter
			// The driver will fill the out parameter with a value after the
			// query executes.
			performanceScore.getDriverParameter().setDirection(ADOParameterDirection.Output);
			
			// Call the store procedure
			cmd.executeNonQuery();
			
			// Print the store procedure's output
			System.out.println(performanceScore.getValue());
         	} finally {
                 cmd.dispose();
         	}
   	} finally {
         	trans.dispose();
   	}  	
} catch (SQLException e) {
   	e.printStackTrace();
}
  • Was this article helpful?