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

Lets say we want to insert some rows into the table EMPLOYEES(NAME CHAR(25), AGE INTEGER, COMMENT CHAR(25)). In the following examples we'll consider the different methods to write the appropriated command text and bind input variables:

Assigning a value with SAParam object

SAParam object represents a parameter object associated with a command.

Example 1

Lets execute the next SQL command to insert a row into the table:

insert into EMPLOYEES (NAME, AGE, COMMENT) values (:1, :2, :3)

This command has three input parameters to set values to NAME, AGE and COMMENT columns.  In that example input parameters are marked by :1, :2, :3. It means we will identify a parameter by its position.

To create and execute the command we should do the following:

  1. Create a command object cmd (for more details see Executing an SQL command and Executing stored procedures):

    SACommand cmd(&Connection, "insert into EMPLOYEES (NAME, AGE, COMMENT) values (:1, :2, :3)");

    After creating cmd object and passing a command text three SAParam objects are created automatically.

  2. Bind input variables by assigning values to SAParam objects.

    Because of identifying parameters by their positions we should call SACommand::Param method with the next syntax:

    cmd.Param(1).setAsString() = "Roy Mann";
    cmd.Param(2).setAsLong() = 42;
    cmd.Param(3).setAsNull();

    The lines above set the value "Roy Mann" to the parameter marked by :142 to the parameter marked by :2 and null value to the parameter marked by :3.

  3. Execute the command:

    cmd.Execute();

Example 2

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

insert into EMPLOYEES (NAME, AGE, COMMENT) values (:name, :age, :comment)

This command has three input parameters to set values to NAME, AGE and COMMENT columns. In that example input parameters are marked by :name, :age, :comment. It means we will identify a parameter by its name .

To create and execute the command we should do the following:

  1. Create a command object cmd (for more details see Executing an SQL command and Executing stored procedures):

    SACommand cmd(&Connection, "insert into EMPLOYEES (NAME, AGE, COMMENT) values (:name, :age, :comment)");

    After creating cmdobject three SAParam objects are created automatically.

  2. Bind input variables by assigning a value to SAParam object.

    Because of identifying parameters by their names we should call SACommand::Param method with the next syntax:

    cmd.Param("name").setAsString() = "Roy Mann";
    cmd.Param("age").setAsLong() = 42;
    cmd.Param("comment").setAsString() = "Manager";

    The lines above sets the value "Roy Mann" to the parameter marked by :name , 42 to the parameter marked by :age and "Manager" to the parameter marked by :comment.

  3. Execute the command:

    cmd.Execute();
Using the stream operator<<

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

Example 3

Let's execute the next SQL command to insert a row into the table:

insert into EMPLOYEES (NAME, AGE, COMMENT) values (:1, :2, :3)

This command has three input parameters to set values for NAME, AGE and COMMENT columns. In that example input parameters are marked by :1, :2, :3. It means we will identify a parameter by its position.

To create and execute the command we should do the following:

  1. Create a command object cmd (for more details see Executing an SQL command and Executing stored procedures):

    SACommand cmd(&Connection, "insert into employees (name, age, comment) values (:1, :2, :3)");

    After creating cmd object three SAParam objects are created automatically but in contrast to the previous examples we will not use them explicitly.

  2. Bind input variables by using SACommand::operator<<.

    Because of identifying parameters by their positions we can put the values into a stream:

    cmd << "Roy Mann" << 42 << SANull();

    The line above sets the value "Roy Mann" to the parameter marked by :142 to the parameter marked by :2 and null value to the parameter marked by :3.

    The order that you associate the values is important (i.e. the first '<<' associates a bind with :1, the second '<<' with :2, the third '<<' with :3).

  3. Execute the command:

    cmd.Execute();

Example 4

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

insert into EMPLOYEES (NAME, AGE, COMMENT) values (:name, :age, :comment)

This command has three input parameters to set values to NAME, AGE and COMMENT columns. In that example input parameters are marked by :name, :age, :comment. It means we will identify a parameter by its name.

To create and execute the command we should do the following:

  1. Create a command object cmd (for more details see Executing an SQL command and Executing stored procedures):

    SACommand cmd(&Connection, "insert into employees (name, age, comment) values (:name, :age, :comment)");

    After creating cmd object three SAParam objects are created automatically, but we will not use them explicitly.

  2. Bind input variables by putting values into a stream.

    Because of identifying parameters by their names we should use more complex sequence of stream elements. In Example 3 we put into a stream only parameters values according to their positions (numbers). Now we should explicitly specify the position of bind variable before putting a value into a stream. To specify a position use SAPos object:

    cmd << SAPos("name") << "Roy Mann" << SAPos("age") << 42 << SAPos("comment") << "Manager";

  3. Execute the command:

    cmd.Execute();

Binding Long, BLob and CLob data can have some differences from binding other data types. See Working with Long or Lob(CLob, BLob) Data to get more information.

If you haven't found the answer to your questions or have some problems using the Library, please, send an e-mail to howto@sqlapi.com

Last modified: 19 October 2019
webmaster@sqlapi.com