On 19/08/2022 00:42, Adriano dos Santos Fernandes wrote:
On 18/08/2022 18:53, Tony Whyman wrote:
IBX for Lazaus follows the original Deplhi IBX approach of adopting PSQL
syntax for named parameters.
Can a name be used more than once, and if yes, how is its type deduction?
Adriano
Uniqueness of parameters names is not required. If you have two or
parameters with the same name in a given SQL Statement then both get set
at the same time and to the same value.
Type conversions are an issue for Firebird rather than IBX and are
handled identically to positional parameters.
The general algorithm for SQL statement execution is:
1. Parse the statement replacing named parameters with '?' placeholders
and create a name to parameter position index.
2. Call Firebird to prepare the query.
3. Set up a parameter array data structure using the Firebird input
parameter metadata, indexed by both position (integer) and name.
4. The user sets parameter values in any order. There are as many setter
methods for a parameter as types available (e.g. AsString, AsInteger,
AsFloat, AsDateTime, etc). The parameter's SQL Type is originally
derived from the Firebird metadata but may be overridden at this point
if the setter used is for a different SQL Type. For string setters, the
Pascal AnsiString type includes the codepage identifier (UTF8 is a
codepage for this purpose), and this is used to set the parameter's
character set id.
5. Immediately prior to executing the query, the input parameter
metadata is regenerated from the current SQL Types in the parameter
array, and the parameter buffer is packed using the regenerated metadata.
6. The query is executed. Any type conversion problems are reported back
by Firebird and result in an exception.
Note that batch queries are handled similarly to the above, except that
from the second row onwards, the same setter method must be used for
each parameter as was used for the first row - otherwise an exception is
raised.
A simple example (note IAttachment, etc. are Pascal interfaces (managed
types) and not the same as the firebird.pas types):
procedure DoQuery(Attachment: IAttachment);
var Transaction: ITransaction;
Statement: IStatement;
begin
Transaction :=
Attachment.StartTransaction([isc_tpb_write,isc_tpb_nowait,isc_tpb_concurrency],taRollback);
Statement := Attachment.PrepareWithNamedParameters(Transaction,'INSERT INTO
EMPLOYEE (EMP_NO, FIRST_NAME, LAST_NAME, PHONE_EXT, HIRE_DATE,' +
'DEPT_NO, JOB_CODE, JOB_GRADE, JOB_COUNTRY, SALARY) '+
'VALUES (:EMP_NO, :FIRST_NAME, :LAST_NAME, :PHONE_EXT, :HIRE_DATE,' +
':DEPT_NO, :JOB_CODE, :JOB_GRADE, :JOB_COUNTRY, :SALARY)');
with Statement.SQLParams do
begin
ByName('EMP_NO').AsInteger := 150;
ByName('LAST_NAME').AsString := 'Doe';
ByName('FIRST_NAME').AsString := 'John';
ByName('PHONE_EXT').AsString := '';
ByName('HIRE_DATE').AsString := '2015-4-1';
ByName('DEPT_NO').AsString := '600';
ByName('JOB_CODE').AsString := 'Eng';
ByName('JOB_GRADE').AsInteger := 4;
ByName('JOB_COUNTRY').AsString := 'England';
ByName('SALARY').AsFloat := 41000.89;
end;
Statement.Execute;
end;
Regards
Tony
Firebird-Devel mailing list, web interface
athttps://lists.sourceforge.net/lists/listinfo/firebird-devel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
