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 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.AsDateTime:=Now; Statement.Params.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.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;