Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

New "Through rowset" objects/classes for Input/Output Parameters, local Variables and Domains [CORE5583] #5850

Open
firebird-automations opened this issue Jul 14, 2017 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: rdb_dev (rdb_dev)

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" 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 syntax 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 (It looks like a temporary table for SP/EB/TR runtime)

DECLARE VARIABLE <through_rowset_name>
ROWSET (<temporary_table-like_fields_&_constraints_definition>);

@firebird-automations
Copy link
Collaborator Author

Modified by: rdb_dev (rdb_dev)

description: 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>);

=>

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>
ROWSET (<temporary_table-like_fields_&_constraints_definition>);

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

description: 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>
ROWSET (<temporary_table-like_fields_&_constraints_definition>);

=>

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 syntax 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>
ROWSET (<temporary_table-like_fields_&_constraints_definition>);

@firebird-automations
Copy link
Collaborator Author

Commented by: @mrotteveel

The term "through rowset" doesn't mean anything to me. What is the point of this feature, and what would the actual term be?

@firebird-automations
Copy link
Collaborator Author

Commented by: rdb_dev (rdb_dev)

> The term "through rowset" doesn't mean anything to me. What is the point of this feature, and what would the actual term be?

Mark, here "through" it's a functional characteristic of therm "rowset". Is meant, that "through rowset" similar to "temporary table", but are named sets, declared like variables, having the same scope, lifetime, and can be passed as input and output parameters for stored procedures and/or execute blocks by own ROWSET_ID. Plz, see explanatory examples.

P.S. Sorry for my English

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Based on the conversation, it seems that the issue Summary should be edited to read:
New "Rowset" objects/classes for Input/Output Parameters, local Variables and Domains

@firebird-automations
Copy link
Collaborator Author

Commented by: rdb_dev (rdb_dev)

Sean, as you think more correct...

@firebird-automations
Copy link
Collaborator Author

Modified by: Sean Leyne (seanleyne)

summary: Through rowsets => New "Rowset" objects/classes for Input/Output Parameters, local Variables and Domains

@firebird-automations
Copy link
Collaborator Author

Commented by: rdb_dev (rdb_dev)

to improve understanding

@firebird-automations
Copy link
Collaborator Author

Modified by: rdb_dev (rdb_dev)

description: 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 syntax 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>
ROWSET (<temporary_table-like_fields_&_constraints_definition>);

=>

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" 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 syntax 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 (It looks like a temporary table for SP/EB/TR runtime)

DECLARE VARIABLE <through_rowset_name>
ROWSET (<temporary_table-like_fields_&_constraints_definition>);

summary: New "Rowset" objects/classes for Input/Output Parameters, local Variables and Domains => New "Through rowset" objects/classes for Input/Output Parameters, local Variables and Domains

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

1 participant