Parameters

Parameters are part of SQL statements. They are being managed with taken of IStatement interface, which allows providing of values in another than text form as a part of SQL statement. You can send binary data, date/time or binary coded floating point number as a part of SQL statement without any need to represent it in text form, mainly as it may not be always possible to do such conversion. Their main advantage is one time preparation of SQL statement saving time and speeds up an application. In the SQL statement string the parameter is marked with the '?' character.

The count of used parameters is determined as soon as an SQL statement was prepared for the execution. This information is accessible via the IStatement.ParamCount method. The Params property provides the access to the parameters' values. The parameter data type is determined from the last value assignment.

The parameter can be one of following:

• Input (Default),
• Output and
• Input/Output.

Input parameter means that it supplies value, output one receives it and input/output one does both. For string representation or binary data receiving there is buffer for 64.000 unicode characters and any longer string will be truncated. To write longer strings, send them as binary data - see the Binary Data chapter.

The ODBC drivers also use the MaxBufferSize attribute in the connection string. Some of them use it as the maximum size for the non-long data type record's attribute in bytes.

Every parameter, which supplies a value, stores it in its buffer except binary data. Such data must be supplied in the time of SQL statement execution with taken of  IDataValueEvents.BlockWrite method. For more on binary data the see appropriate chapter.

The interface encapsulating the parameter is the IParamDataValue one.

Examples how to use parameters

procedure MyObj.WriteLogEvent;
var Statement:IStatement;
   i:integer;
begin
  try
    Statement:=FDataSource.CreateStatament;
    Statement.Statement:='insert into logs (time, text) values (?, ?)';
    for i:=1 to 100 do
      begin
        Statement.Params[0].AsDateTime:=Now;
        Statement.Params[1].AsString:='Now';
        Statement.Execute;
      end;
  finally
    Statement:=nil;
  end;
end;

procedure LoadDays;
var i:integer;
begin
  Statement:='select id from days where day=?';
  for i:=0 to 6 do
    begin
      Cancel; //Needed to cancel processing on previous result set 
      Params[0].AsDateTime:=i;//No need to bother with local setting
      Execute;
      if Fetch then
        begin
          //The record has been found
        end else
          begin
            //No record found 
          end;
end;