SQLPreparedCommand

Executing SQL queries directly has a few disadvantages. If you must execute a query more than once using different query parameters, the database server parses the query every time you invoke it. The server must also create the same query execution plan every time you run the SQL statement. On top of that you must create the SQL statement dynamically. That is, you must manipulate a string containing the partial SQL statement and add the actual query parameters to it. This is cumbersome and can also lead to dangerous security holes due to SQL injections.

To prevent all this, BI# supports the SQLPreparedCommand data type:

#define EngineVersion 3.0
#define RuntimeVersion 3.0

void PreparedCommandDemo(string connectionString)
{
  SQLConnection sqlconnection = SQLCreateConnection(connectionString);
  SQLPreparedCommand preparedCommand = SQLCreatePreparedCommand(
  sqlconnection,
  "SELECT * from [users] WHERE [name] = @name"
  );
  SQLBindStringParameter(preparedCommand, "@name", "Tom");
  SQLData queryResult = SQLExecuteQuery(preparedCommand);
  foreach (SQLDataRow row in queryResult)
  { 
    WriteLine(SQLDataRowGetString(row, "name")); 
  }
  SQLReleaseCommand(preparedCommand);
}

Creating a SQLPreparedCommand object is similar to creating a regular SQL statement. The only difference is that you can use placeholders for parameters. So instead of using a concrete name in the statement's WHERE clause, you can use the placeholder @name. Then you can use SQLBindStringParameter to bind the placeholder to an actual value such as "Tom". After you have bound all parameters, you can use the SQLPreparedCommand like an SQL statement.

Note: The SQLPreparedCommand object is temporarily disabled for an Oracle database. Therefore, OLAPCreatePreparedCommand throws an exception when used with the OracleConnection connections.

The SQLPreparedCommand data type is forwarded to the Application Engine Service Worker, so commands of the SQLPreparedCommand data type can be used in full client even if it runs outside the Infor cloud.

To release an SQLPreparedCommand object, you must call the SQLReleaseCommand() function.