Through rowsets
---------------
Key: CORE-5583
URL: http://tracker.firebirdsql.org/browse/CORE-5583
Project: Firebird Core
Issue Type: New Feature
Components: API / Client Library, Engine
Reporter: Denis
Through rowsets
Requirements:
* Through rowset must have 64-bit integer identifier
(further "ROWSET_ID") representing it;
* Manipulations with a through rowset are allowed within
the BEGIN...END block only;
* Through rowset can be used as parameter in invokes
and returns of stored procedures and/or execute blocks
by own ROWSET_ID only;
* The lifetime of through rowset depends on method
and location of the declaration;
* Through rowset (own ROWSET_ID) can be checked for NULL
or rowset can be cleared by assignment ROWSET_ID to NULL;
* Through rowset can be assigned to another through rowset
with same domain by operator "=" and in this case,
another rowset will get a copy of data and new ROWSET_ID;
Futures:
* Reducing the overhead of transfer rowsets in/from SP or EB
as opposing of transfer by SUSPEND and recursive invokes
of stored procedure;
* Using non-overlapping rowsets with same name as opposing
of temporary tables;
================================================
Defenition sintax of domain that can be used
to transaction-wide through rowset declaration:
================================================
CREATE DOMAIN <through_rowset_domain_name>
AS ROWSET (<temporary_table-like_fields_&_constraints_definition>);
================================================
Instance declaration sintax of through rowset in
stored procedure, trigger or execute block, that
can be used for transaction-wide through rowsets:
================================================
DECLARE VARIABLE <through_rowset_name> <through_rowset_domain_name>;
Example:
--------
CREATE DOMAIN SomeRowsetDomain
AS ROWSET
(
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(200) NOT NULL UNIQUE
);
SET TERM ^;
CREATE OR ALTER PROCEDURE procCalc
(
someRowset SomeRowsetDomain
)
AS
BEGIN
-- change the through rowset "someRowset"
END^
SET TERM ;^
SET TERM ^;
CREATE OR ALTER PROCEDURE procResult
(
someRowset SomeRowsetDomain
)
RETURNS
(
id INTEGER,
name VARCHAR(200)
)
AS
BEGIN
EXECUTE PROCEDURE procCalc (:someRowset);
FOR
SELECT id, name
FROM :someRowset
ORDER BY id
INTO: id, name
DO
SUSPEND;
END^
SET TERM ^;
-- through rowset initialization
EXECUTE BLOCK
(
id INTEGER NOT NULL,
name VARCHAR(200) NOT NULL
)
RETURNS
(
-- return ROWSET_ID to client
someRowset SomeRowsetDomain
)
AS
BEGIN
INSERT INTO :someRowset (id, name) VALUES (:id, :name);
END
-- Multiple execution of client's query in same transaction
-- to fill the through rowset by ROWSET_ID.
-- Client can use this to manually fill the through rowset
-- from a UI grid
EXECUTE BLOCK
(
someRowset SomeRowsetDomain NOT NULL,
id INTEGER NOT NULL,
name VARCHAR(200) NOT NULL
)
AS
BEGIN
INSERT INTO :someRowset (id, name) VALUES (:id, :name);
END
-- Obtaining the result
SELECT * FROM procResult(:someRowset)
or
EXECUTE BLOCK
(
someRowset SomeRowsetDomain
)
RETURNS
(
id INTEGER,
name VARCHAR(200)
)
AS
BEGIN
EXECUTE PROCEDURE procCalc (:someRowset);
FOR
SELECT id, name
FROM :someRowset
ORDER BY id
INTO: id, name
DO
SUSPEND;
END
================================================
Instance declaration sintax of through rowset in
stored procedure, trigger or execute block, that
can be used for Trigger/EB/SP-wide through rowsets.
In this case, through rowset can not be used as
input/output parameter and it's lifetime limited
by a runtime of SP/EB/Trigger (similar to cursor)
================================================
DECLARE VARIABLE <through_rowset_name>
AS ROWSET (<temporary_table-like_fields_&_constraints_definition>);
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
