Introduction

---

Parameters allow the same SQL statement to be used with different data values, and are placeholders for those data values. At runtime, the application prepares the query with the parameters and fills the parameter with a value before the query is executed. When the query is executed, the data values passed into the parameters are substituted for the parameter placeholder and the SQL statement is applied.

Specifying Parameters in SQL

---

Parameter markers can be used in SQL SELECT, INSERT, UPDATE, and DELETE statements in place of constants. Parameters are identified by a preceding colon (:). For example:

SELECT Last_Name, First_Name
FROM Customer
WHERE (Last_Name=:LName) AND (First_Name=:FName)

Parameters are used to pass data values to be used in WHERE clause comparisons and as update values in updating SQL statements such as UPDATE or INSERT. Parameters cannot be used to pass values for database, table, column, or index names. The following example uses the TotalParam parameter to pass the data value that needs to be assigned to the ItemsTotal column for the row with the OrderNo column equal to 1014:

UPDATE Orders
SET ItemsTotal = :TotalParam
WHERE (OrderNo = 1014)

Populating Parameters with the TDBISAMQuery Component

---

You can use the TDBISAMQuery Params property to populate the parameters in an SQL statement with data values. You may use two different methods of populating parameters using the Params property:

• By referencing each parameter by its index position in the available list of parameters


• By referencing each parameter by name using the ParamByName method

The following is an example of using the index positions of the parameters to populate the data values for an INSERT SQL statement:

begin
   with MyQuery do
      begin
      SQL.Clear;
      SQL.Add('INSERT INTO Country (Name, Capital, Population)');
      SQL.Add('VALUES (:Name, :Capital, :Population)');
      Params[0].AsString := 'Lichtenstein';
      Params[1].AsString := 'Vaduz';
      Params[2].AsInteger := 420000;
      ExecSQL;
      end;
end;

The next block of code is an example of using the TDBISAMQuery ParamByName method in order to populate the data values for a SELECT SQL statement:

begin
   with MyQuery do
      begin
      SQL.Clear;
      SQL.Add('SELECT *');
      SQL.Add('FROM Orders');
      SQL.Add('WHERE CustID = :CustID');
      ParamByName('CustID').AsFloat:=1221;
      Open;
      end;
end;

Parameters and Multiple SQL Statements

---

If you have specified multiple SQL statements, or a script, in the SQL property and wish to execute these multiple SQL statements with different parameters, you can assign an event handler to the TDBISAMQuery OnGetParams event. The OnGetParams event is fired once before the execution of each SQL statement specified in the SQL property. In the event handler you would specify the parameters in the same way as you would for a single SQL statement described above. You can also use the SQLStatementType property to find out the type of SQL statement currently being executed and the Text property to examine the current SQL statement being executed.

Preparing Parameterized Queries

---

It is usually recommended that you manually prepare parameterized queries that you intend to execute many times with different parameter values. This can result in significant performance improvements since the process of preparing a query can be time-consuming. The following is an example of inserting 3 records with different values using a manually-prepared, parameterized query:

begin
   with MyQuery do
      begin
      SQL.Clear;
      SQL.Add('INSERT INTO Customer (CustNo, Company');
      SQL.Add('VALUES (:CustNo, :Company)');
      { Manually prepare the query }
      Prepare;
      ParamByName('CustNo').AsInteger:=1000;
      ParamByName('Company').AsString:='Chocolates, Inc.';
      ExecSQL;
      ParamByName('CustNo').AsInteger:=2000;
      ParamByName('Company').AsString:='Flowers, Inc.';
      ExecSQL;
      ParamByName('CustNo').AsInteger:=3000;
      ParamByName('Company').AsString:='Candies, Inc.';
      ExecSQL;
      end;
end;