Skip to main content

Reference

 

OutSystems

Insert Data in the Application Database

Use the RuntimePublic API to insert data in the application database following this pattern:

  1. Retrieve a DatabaseProvider from the DatabaseAccess instance;
  2. Use the provider to create a transaction;
  3. Use the transaction to create a Command;
  4. Use the Command to create command parameters;
  5. Execute the Command;
  6. Retrieve the Command results.

Example

In this example, we add a record to the USEDITEM table that is in the application database.

C#

using OutSystems.RuntimePublic.Db;

// Retrieve the DatabaseProvider for the current application
DatabaseProvider dbaProvider = DatabaseAccess.ForRunningApplication();

// We will use this later to handle query parameters and escape identifiers
SqlHelper sqlHelper = dbaProvider.SqlHelper;

// Here we use the database transaction that is used to handle the current HTTP request
// You can also use a separate transaction
using (RequestTransaction requestTransaction = dbaProvider.GetRequestTransaction()) {
    using (Command cmd = requestTransaction.CreateCommand(
        string.Format("INSERT INTO USEDITEM ({0}, age) VALUES ({1}, {2})",
                        // DESC is a keyword in SQL Server you need to escape it before using it as an
                        // identifier
                        sqlHelper.Escape("DESC"),
                        // Parameter names need to be prefixed so they can be properly replaced by
                        // their respective values. Each engine has its own parameter prefix so you can
                        // use this function to make your SQL database agnostic
                        sqlHelper.PrefixParam("desc"), sqlHelper.PrefixParam("age")))) {
        cmd.CreateParameter(sqlHelper.PrefixParam("desc"), DbType.String, "Car");
        cmd.CreateParameter(sqlHelper.PrefixParam("age"), DbType.Int32, 3);
        cmd.ExecuteNonQuery();
    }
}

Java

try {
   	// Retrieve the DatabaseProvider for the current application
   	DatabaseProvider dbaProvider = DatabaseAccess.forRunningApplication();
   	// We will use this later to handle query parameters and escape identifiers
   	SqlHelper sqlHelper = dbaProvider.getSqlHelper();
   	// Here we use the database transaction that is used to handle the current HTTP request
      // You can also use a separate transaction
   	RequestTransaction requestTransaction = dbaProvider.getRequestTransaction();
   	try {
         	Command cmd = requestTransaction.createCommand(
                	String.format("INSERT INTO USEDITEM (%, age) VALUES (%, %)",
                                 // LOOP is a keyword in Oracle, you need to escape it before using
                                 // it as an identifier
                       	         sqlHelper.escape("LOOP"),
                                 // Parameter names need to be prefixed so they can be properly 
                                 // replaced by their respective values. Each engine has its own 
                                 // parameter prefix so you can use this function to make
                                 // your SQL database agnostic
                       	         sqlHelper.prefixParam("loop"), sqlHelper.prefixParam("age")));
         	try {
                	cmd.createParameter(sqlHelper.prefixParam("loop"), Types.VARCHAR, "Car");
                	cmd.createParameter(sqlHelper.prefixParam("age"), Types.INTEGER, 3);
                	cmd.executeNonQuery();
         	} finally {
                	cmd.dispose();
         	}
     	} finally {
         	requestTransaction.dispose();
   	}
} catch (SQLException e) {
   	e.printStackTrace();
}
  • Was this article helpful?