| Subject | Parametrized queries and execute statement - dynamic parameter list |
|---|---|
| Author | liviuslivius |
| Post date | 2015-05-26T07:01:20Z |
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