Home How To Online Documentation Support Download Order

 

 

Connecting to databases
Transactions
Executing an SQL command
Executing stored procedures
Binding input parameters
Fetching result set
Fetching multiple result sets
Optimizing Database Performance
Returning output parameters
Working with Long or Lob(CLob, BLob) Data
Working with date/time values
Error handling
Using native API
Query examples


Binding Input Parameters

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

Suppose, 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.

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 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 variant of command text to insert a row into the table:

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

This command has three input parameters to set values to NAME and AGE 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 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 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 variant of command text 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.

Problems and Questions

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