Executing an SQL command

Executing an SQL command

To execute an SQL command you should do the following:

1. Create a command object and set a command text.
2. Bind input parameters.
3. Execute the command.
4. Processing output parameters and result set.

In this section we'll consider a commands like INSERT, SELECT and UPDATE that do not involve fetching data. To execute SELECT command or a stored procedures see Fetching result set and Executing stored procedures sections.

In the following example we assume that the table EMPLOYEES ( NAME CHAR(25), AGE INTEGER) exists on the database Demo.

Step 1. Creating a command object and setting a command text.

To execute a command we need two objects: SAConnection (connection object) and SACommand (command object).

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

The line above creates a command object cmd based on previously created and connected connection object Connection (for creating and connection Connection object see Connecting to databases ). Second parameter is the command text. The command text can also be provided using SACommand::setCommandText method.

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

 

Step 2. Bind input parameters.

Binding some values to the command is like a filling in the gaps in the SQL command statement (marked by :1, :2, :3, ..., :n) with actual data values. The data values can be constants or program variables of appropriate types. To associate a command object with these bind variables you can use the SACommand::operator<< as shown below:

cmd << "Tom Patt" << 30;

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

The another way to bind input variables is directly assigning a value with  SAParam object which represents a command parameter. To get appropriated parameter object by its position in SQL statement use SACommand::Param method:

cmd.Param(1).setAsString() = "Tom Patt";
cmd.Param(2).setAsLong() = 30;

More details about binding input variables see in Binding input parameters section.

 

Step 3. Executing the command.

Finally we execute the command, which results in the string being inserted into column NAME, and the number being inserted into column AGE.

cmd.Execute();

If you want to see the number of rows affected by the command execution use SACommand::RowsAffected method:

int nRows = cmd.RowsAffected();

If you want to insert another row of new data, you just have to associate the new bind values with the object and repeat the bind/execute part again:

cmd << "Nick Barry" << 37;
cmd.Execute();

 

Step 4. Processing output parameters and result set.

Processing output parameters and result set is not actual for INSERT, UPDATE and DELETE commands, that's why we discuss this questions in other sections.

For processing result set (if any) see Fetching result set.

For processing output parameters (if any) see Returning output parameters. 

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