You should assign values for input variables any time you want to execute an SQL command or a stored procedure with input parameters.

In this tutorial we will be inserting rows into the following table:

CREATE TABLE EMPLOYEES(NAME CHAR(25), AGE INTEGER, COMMENTS CHAR(25))

The library provides two alternative methods for binding input parameters. Both methods yield identical results, so it is just a matter of preference which one you prefer:

Binding Long/CLob/BLob
Binding Long, BLob and CLob data can have some differences from binding other data types – for example, when using piecewise capabilities. See Handle Long/CLob/BLob for additional information.

Assigning a Value with SAParam Object

SAParam object represents a parameter object associated with a command.

Example 1 – Positional Parameters

Consider the following SQL command to insert a row into the EMPLOYEES table:

INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) VALUES (:1, :2, :3)

This SQL command has three input parameters for NAME, AGE and COMMENTS columns. Input parameters are marked by the :1, :2, :3 markers. This means we need to bind parameters by their positions:

SACommand cmd(
    &Connection, 
    _TSA("INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) VALUES (:1, :2, :3)"));
    
cmd.Param(1).setAsString() = _TSA("Roy Mann");
cmd.Param(2).setAsInt32() = 42;
cmd.Param(3).setAsNull();

cmd.Execute();
The code above performs the following simple steps:
  • First statement creates a command object cmd (for more details see Execute SQL command and Call stored procedure).
    After creating the cmd object and passing a command text to it, three SAParam objects are created automatically.
  • Next group of three statements binds input variables by assigning values to SAParam objects using positional overload of the SACommand::Param method.
    The code sets the value of "Roy Mann" to the parameter marked by :1, 42 to the parameter marked by :2 and null value to the parameter marked by :3.
  • Last statement executes the command with bound parameters and sends it to the server.
Example 2 – Named Parameters

We can use another type of command text parameter binding to insert a row into the table:

INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) VALUES (:name, :age, :comments)

This SQL command has three input parameters for NAME, AGE and COMMENTS columns. Input parameters are marked by :name, :age and :comments markers. It means we need to bind parameters by their names:

SACommand cmd(
    &Connection, 
    _TSA("INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) VALUES (:name, :age, :comments)"));
    
cmd.Param(_TSA("name")).setAsString() = _TSA("Roy Mann");
cmd.Param(_TSA("age")).setAsInt32() = 42;
cmd.Param(_TSA("comments")).setAsString() = _TSA("Manager");

cmd.Execute();
The code above is identical to the one in the previous example, except that it uses named marker bindings. It sets the value of "Roy Mann" to the parameter marked by :name, 42 to the parameter marked by :age and "Manager" to the parameter marked by :comments.

Using the stream operator <<

SACommand::operator << is a stream operator, so usually it is more convenient to use it compared to assigning SAParam objects.

Example 3 – Positional Parameters

Consider once more the following SQL command:

INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) VALUES (:1, :2, :3)

This SQL command has three input parameters for NAME, AGE and COMMENTS columns. Input parameters are marked by the :1, :2, :3 markers. This means we need to bind parameters by their positions:

SACommand cmd(
    &Connection,
    _TSA("INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) values (:1, :2, :3)"));
    
cmd << _TSA("Roy Mann") << 42 << SANull();

cmd.Execute();
The code above performs the following simple steps:
  • First statement creates a command object cmd (for more details see Execute SQL command and Call stored procedure).
    After creating the cmd object and passing a command text to it, three SAParam objects are created automatically.
  • Next statement binds input variables using SACommand::operator <<.
    The code sets the value of "Roy Mann" to the parameter marked by :1, 42 to the parameter marked by :2 and null value to the parameter marked by :3.
    The order of the values is important – the first << binds to :1, the second << to :2 and the third << to :3.
  • Last statement executes the command with bound parameters and sends it to the server.
Example 4 - Named Parameters

We can use another type of command text parameter binding to insert a row into the table:

INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) VALUES (:name, :age, :comments)

This SQL command has three input parameters for NAME, AGE and COMMENTS columns. Input parameters are marked by :name, :age and :comments markers. It means we need to bind parameters by their names:

SACommand cmd(
    &Connection, 
    _TSA("INSERT INTO EMPLOYEES (NAME, AGE, COMMENTS) VALUES (:name, :age, :comments)"));
    
cmd << SAPos(_TSA("name")) << _TSA("Roy Mann") 
    << SAPos(_TSA("age")) << 42
    << SAPos(_TSA("comments")) << _TSA("Manager");

cmd.Execute();
Because of binding parameters by their names we need to use more complex sequence of stream elements. In Example 3 we put parameter values into the stream in order of their numerical positions.
Here we need to explicitly specify the named positions of bound variables before putting values into the stream. To specify a position we used SAPos objects.
Need Help?
Send an e-mail to support@sqlapi.com if you want to ask code-level questions, if you’re stuck with a specific error or need advise on the library best practices.