SQLPreparedCommand

Executing SQL queries directly has a few disadvantages. If you have to execute a query more than once using different query parameters, the database server has to parse the query every time you invoke it. Also the server has to create the same query execution plan every time you run the SQL statement. On top of that you have to create the SQL statement dynamically, that is you have to 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")); 
  }

}

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.