|Home||How To||Online Documentation||Support||Download||Order|
Executing an SQL command
To execute an SQL command you should do the following:
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.
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.
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";
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.
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;
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.