Binding Input Parameters

322yangxinxing 2012-02-15

展开全文

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:

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.
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 :1, 42 to the parameter marked by :2 and null value to the parameter marked by :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:

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.
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.
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)

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

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.
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 :1, 42 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 ).
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:

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.
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";
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.