[firebird-support] Parametrized queries and execute statement - dynamic parameter list

[liviuslivius liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
 
the real question is in point 4
 
tables
CREATE TABLE TABLEX
(FIELD_ID INTEGER
);
 
CREATE TABLE TABLEY
(
ID INTEGER,
AAA INTEGER
);
 
CREATE TABLE TABLEZ
(
ID INTEGER,
BBB INTEGER
);
 
few thinks:
1. If i do something like this
SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC(T.FIELD_ID) P) FROM TABLEX T
 
and TABLEX have 100 records then
 
procedure like
 
SET TERM ^ ;
ALTER PROCEDURE MY_PROC_CALC(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
BEGIN
  SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID INTO :SUM_AAA;
  SUSPEND;
END^
SET TERM ; ^
 
prepare statement "SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID" only once? for 
whole
"SELECT MY_PROC_CALC(T.FIELD_ID) FROM TABLEX T"
or it prepare query 100 times for every record?
I do not see entry in MON$STATEMENTS during execution - then i do not know how 
this work
 
2. But if i change proc to use execute statement
 
SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
BEGIN
  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=' || :ID;
  EXECUTE STATEMENT VAR_SQL INTO :SUM_AAA;
  SUSPEND;
END^
SET TERM ; ^
 
and run query 
SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC(T.FIELD_ID) P) FROM TABLEX T
 
then it prepare query for every record - not ok :(
and in MON$STATEMENTS it occure many times
 
3. when i change proc to use execute statement with parameters
 
SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
BEGIN
  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID';
  EXECUTE STATEMENT (VAR_SQL) (ID := :ID) INTO :SUM_AAA;
  SUSPEND;
END^
SET TERM ; ^
 
and run query
SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM 
TABLEX T
 
than select is prepared only once - ok :)
 
4. but how to change this sample code to use paramaters and do not prepare 
statement on every record?
 
SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
DECLARE VARIABLE VAR_BBB INTEGER;
DECLARE VARIABLE VAR_NR INTEGER;
BEGIN
  VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID ';
  VAR_NR = 1;
  FOR SELECT 
  BBB 
  FROM 
  TABLEZ 
  WHERE ID=:ID
  INTO :VAR_BBB
  DO
    BEGIN  
   /* DO SOME CALCULATION ON VAR_BBB */
   
   VAR_SQL = VAR_SQL || ' OR ID=:ID' || VAR_NR; 
        How to add here parameter to list of parameters for below execute 
statement?
    END
 
  EXECUTE STATEMENT (VAR_SQL) (ID := :ID,  ID1, ID2, ID3 ...) <----- How to 
specify parameters dynamically?
  INTO :SUM_AAA;
  
  SUSPEND;
END^
SET TERM ; ^
 
 
Is this somehow possible? Or should i add this to the Firebird bug tracker as 
new feature request?
This is important from performance point of view.
 
 
regards,
Karol Bieniaszewski