TOP をテンプレートにして作成
ホーム
バックアップ
一覧
検索
最終更新
ヘルプ
ログイン
開始行
* PSQL [#h1d70072]
cf)[[Firebird 2.5 Release Notes:http://www.firebirdsql.o...
** Autonomous Transactions [#b475d9fc]
This new implementation allows a piece of code to run in ...
The new transaction is initiated with the same isolation ...
Warning
Because the autonomous transaction is independent from th...
Syntax Pattern
IN AUTONOMOUS TRANSACTION
DO
<simple statement | compound statement>
Example of Use
create table log (
logdate timestamp,
msg varchar(60)
);
create exception e_conn 'Connection rejected';
set term !;
create trigger t_conn on connect
as
begin
if (current_user = 'BAD_USER') then
begin
in autonomous transaction
do
begin
insert into log (logdate, msg) values (current_time...
end
exception e_conn;
end
end!
set term ;!
** Borrow Database Column Type for a PSQL Variable [#n9d3...
This feature extends the implementation in v.2 whereby do...
Syntax Pattern
data_type ::=
<builtin_data_type>
| <domain_name>
| TYPE OF <domain_name>
| TYPE OF COLUMN <table or view>.<column>
Note
TYPE OF COLUMN gets only the type of the column. Any cons...
Examples
CREATE TABLE PERSON (
ID INTEGER,
NAME VARCHAR(40)
);
CREATE PROCEDURE SP_INS_PERSON (
ID TYPE OF COLUMN PERSON.ID,
NAME TYPE OF COLUMN PERSON.NAME
)
AS
DECLARE VARIABLE NEW_ID TYPE OF COLUMN PERSON.ID;
BEGIN
INSERT INTO PERSON (ID, NAME)
VALUES (:ID, :NAME)
RETURNING ID INTO :NEW_ID;
END
Hidden Trap!
In v.2.5 and beyond, it is possible to alter the data typ...
This means that, for variables declared using the TYPE OF...
In short, the engine now no longer stops you from changin...
** New Extensions to EXECUTE STATEMENT [#h4b3c99a]
Unusually for our release notes, we begin this chapter wi...
[FOR] EXECUTE STATEMENT <query_text> [(<input_parameters>)]
[ON EXTERNAL [DATA SOURCE] <connection_string>]
[WITH {AUTONOMOUS | COMMON} TRANSACTION]
[AS USER <user_name>]
[PASSWORD <password>]
[ROLE <role_name>]
[WITH CALLER PRIVILEGES]
[INTO <variables>]
Note
The order of the optional clauses is not fixed so, for ex...
[ON EXTERNAL [DATA SOURCE] <connection_string>]
[WITH {AUTONOMOUS | COMMON} TRANSACTION]
[AS USER <user_name>]
[PASSWORD <password>]
[ROLE <role_name>]
[WITH CALLER PRIVILEGES]
Clauses cannot be duplicated.
*** Context Issues [#qac613d7]
If there is no ON EXTERNAL DATA SOURCE clause present, EX...
However, if <user_name> is not equal to CURRENT_USER, the...
Note
In the absence of an AS USER <user_name> clause, CURRENT_...
Authentication
Where server authentication is needed for a connection th...
On Windows, for the CURRENT_CONNECTION (i.e., no external...
If the external data source parameter is present and its ...
If the external data source parameter is present and its ...
In any other case where the PASSWORD clause is missing, o...
Transaction Behaviour
The new syntax has an optional clause for setting the app...
The behaviour for WITH COMMON TRANSACTION is as follows:
Causes any transaction in an external data source to be s...
Executes the statement inside the CURRENT_TRANSACTION; or
May use another transaction that is started internally in...
The WITH AUTONOMOUS TRANSACTION setting starts a new tran...
Inherited Access Privileges
Vladyslav Khorsun
Tracker reference CORE-1928.
By design, the original implementation of EXECUTE STATEME...
The introduction of the optional clause WITH CALLER PRIVI...
Important
The WITH CALLER PRIVILEGES option is not compatible with ...
*** External Queries from PSQL [#gafb52b5]
EXECUTE STATEMENT now supports queries against external d...
The <connection_string> Argument
The format of <connection_string> is the usual one that i...
[<host_name><protocol_delimiter>]database_path
Character Set
The connection to the external data source uses the same ...
Access Privileges
If the external data source is on another server then the...
The clause WITH CALLER PRIVILEGES is a no-op if the exter...
MORE INFORMATION REQUIRED. ROLES?
Note
Use of a two-phase transaction for the external connectio...
*** EXECUTE STATEMENT with Dynamic Parameters [#i5a7e0f4]
The new extensions provide the ability to prepare a state...
Syntax Conventions
The mechanism employs some conventions to facilitate the ...
The New Binding Operator
At this point in the implementation of the dynamic parame...
Syntax for Defining Parameters
<input_parameters> ::=
<named_parameter> | <input_parameters>, <named_para...
<named_parameter> ::=
<parameter name> := <expression>
Example for named input parameters
For example, the following block of PSQL defines both <qu...
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
/* Normal PSQL string assignment of <query_text> */
S = 'INSERT INTO TTT VALUES (:a, :b, :a)';
WHILE (N > 0) DO
BEGIN
/* Each loop execution applies both the string value
and the values to be bound to the input parameters */
EXECUTE STATEMENT (:S) (a := CURRENT_TRANSACTION, b...
WITH COMMON TRANSACTION;
N = N - 1;
END
END
Example for unnamed input parameters
A similar block using a set of unnamed input parameters i...
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
S = 'INSERT INTO TTT VALUES (?, ?, ?)';
WHILE (N > 0) DO
BEGIN
EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURREN...
N = N - 1;
END
END
Note
Observe that, if you use both <query_text> and <input_par...
EXECUTE STATEMENT (:sql) (p1 := 'abc', p2 := :second_...
*** Exception Handling [#o202f1ab]
The handling of exceptions depends on whether the ON EXTE...
ON EXTERNAL DATA SOURCE clause is present
If ON EXTERNAL DATA SOURCE clause is present, Firebird ca...
The text of the interpreted remote error contains both er...
Format of isc_eds_connection error
Template string
Execute statement error at @1 :\n@2Data source : @3
Status-vector tags
isc_eds_connection,
isc_arg_string, <failed API function name>,
isc_arg_string, <text of interpreted external error>,
isc_arg_string, <data source name>
Format of isc_eds_statement error
Template string
Execute statement error at @1 :\n@2Statement : @3\nData...
Status-vector tags
isc_eds_statement,
isc_arg_string, <failed API function name>,
isc_arg_string, <text of interpreted external error>,
isc_arg_string, <query>,
isc_arg_string, <data source name>
At PSQL level the symbols for these errors can be handled...
WHEN GDSCODE eds_statement
Note
Currently, the originating error codes are not accessible...
ON EXTERNAL DATA SOURCE clause is not present
If ON EXTERNAL DATA SOURCE clause is not present, the ori...
For example, if a dynamic statement were to raise the isc...
WHEN GDSCODE lock_conflict
*** Examples Using EXECUTE STATEMENT [#jb11c97f]
The following examples offer a sampler of ways that the E...
Test Connections and Transactions
A couple of tests you can try to compare variations in se...
Test a) :Execute this block few times in the same transac...
EXECUTE BLOCK
RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
DECLARE I INT = 0;
DECLARE N INT = 3;
DECLARE S VARCHAR(255);
BEGIN
SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A
WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
INTO :S;
WHILE (i < N) DO
BEGIN
DB = TRIM(CASE i - 3 * (I / 3)
WHEN 0 THEN '\\.\' WHEN 1 THEN 'localhost:' ELSE ''...
FOR EXECUTE STATEMENT
'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION
FROM RDB$DATABASE'
ON EXTERNAL :DB
AS USER CURRENT_USER PASSWORD 'masterkey' -- just f...
WITH COMMON TRANSACTION
INTO :CONN, :TRAN
DO SUSPEND;
i = i + 1;
END
END
Test b) : Execute this block few times in the same transa...
EXECUTE BLOCK
RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
DECLARE I INT = 0;
DECLARE N INT = 3;
DECLARE S VARCHAR(255);
BEGIN
SELECT A.MON$ATTACHMENT_NAME
FROM MON$ATTACHMENTS A
WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
INTO :S;
WHILE (i < N) DO
BEGIN
DB = TRIM(CASE i - 3 * (I / 3)
WHEN 0 THEN '\\.\'
WHEN 1 THEN 'localhost:'
ELSE '' END) || :S;
FOR EXECUTE STATEMENT
'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION FROM ...
ON EXTERNAL :DB
WITH AUTONOMOUS TRANSACTION -- note autonomous tran...
INTO :CONN, :TRAN
DO SUSPEND;
i = i + 1;
END
END
Input Evaluation Demo
Demonstrating that input expressions evaluated only once:
EXECUTE BLOCK
RETURNS (A INT, B INT, C INT)
AS
BEGIN
EXECUTE STATEMENT (
'SELECT CAST(:X AS INT),
CAST(:X AS INT),
CAST(:X AS INT)
FROM RDB$DATABASE')
(x := GEN_ID(G, 1))
INTO :A, :B, :C;
SUSPEND;
END
Insert Speed Test
Recycling our earlier examples for input parameter usage ...
RECREATE TABLE TTT (
TRAN INT,
CONN INT,
ID INT);
-- Direct inserts:
EXECUTE BLOCK AS
DECLARE N INT = 100000;
BEGIN
WHILE (N > 0) DO
BEGIN
INSERT INTO TTT VALUES (CURRENT_TRANSACTION, CURRENT_...
N = N - 1;
END
END
-- Inserts via prepared dynamic statement
-- using named input parameters:
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
S = 'INSERT INTO TTT VALUES (:a, :b, :a)';
WHILE (N > 0) DO
BEGIN
EXECUTE STATEMENT (:S)
(a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
WITH COMMON TRANSACTION;
N = N - 1;
END
END
-- Inserts via prepared dynamic statement
-- using unnamed input parameters:
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
S = 'INSERT INTO TTT VALUES (?, ?, ?)';
WHILE (N > 0) DO
BEGIN
EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_...
N = N - 1;
END
END
** Other PSQL Improvements [#l56ab7b0]
Improvements made to existing PSQL syntax include the fol...
*** Subqueries as PSQL Expressions [#bae1821d]
Previously, a subquery used as a PSQL expression would re...
var = (select ... from ...);
if ((select ... from ...) = 1) then
if (1 = any (select ... from ...)) then
if (1 in (select ... from ...)) then
Now, such potentially valid expressions are allowed, remo...
*** SQLSTATE as Context Variable [#d1fb77f3]
(v.2.5.1) SQLSTATE is made available as a PSQL context va...
最終行:
* PSQL [#h1d70072]
cf)[[Firebird 2.5 Release Notes:http://www.firebirdsql.o...
** Autonomous Transactions [#b475d9fc]
This new implementation allows a piece of code to run in ...
The new transaction is initiated with the same isolation ...
Warning
Because the autonomous transaction is independent from th...
Syntax Pattern
IN AUTONOMOUS TRANSACTION
DO
<simple statement | compound statement>
Example of Use
create table log (
logdate timestamp,
msg varchar(60)
);
create exception e_conn 'Connection rejected';
set term !;
create trigger t_conn on connect
as
begin
if (current_user = 'BAD_USER') then
begin
in autonomous transaction
do
begin
insert into log (logdate, msg) values (current_time...
end
exception e_conn;
end
end!
set term ;!
** Borrow Database Column Type for a PSQL Variable [#n9d3...
This feature extends the implementation in v.2 whereby do...
Syntax Pattern
data_type ::=
<builtin_data_type>
| <domain_name>
| TYPE OF <domain_name>
| TYPE OF COLUMN <table or view>.<column>
Note
TYPE OF COLUMN gets only the type of the column. Any cons...
Examples
CREATE TABLE PERSON (
ID INTEGER,
NAME VARCHAR(40)
);
CREATE PROCEDURE SP_INS_PERSON (
ID TYPE OF COLUMN PERSON.ID,
NAME TYPE OF COLUMN PERSON.NAME
)
AS
DECLARE VARIABLE NEW_ID TYPE OF COLUMN PERSON.ID;
BEGIN
INSERT INTO PERSON (ID, NAME)
VALUES (:ID, :NAME)
RETURNING ID INTO :NEW_ID;
END
Hidden Trap!
In v.2.5 and beyond, it is possible to alter the data typ...
This means that, for variables declared using the TYPE OF...
In short, the engine now no longer stops you from changin...
** New Extensions to EXECUTE STATEMENT [#h4b3c99a]
Unusually for our release notes, we begin this chapter wi...
[FOR] EXECUTE STATEMENT <query_text> [(<input_parameters>)]
[ON EXTERNAL [DATA SOURCE] <connection_string>]
[WITH {AUTONOMOUS | COMMON} TRANSACTION]
[AS USER <user_name>]
[PASSWORD <password>]
[ROLE <role_name>]
[WITH CALLER PRIVILEGES]
[INTO <variables>]
Note
The order of the optional clauses is not fixed so, for ex...
[ON EXTERNAL [DATA SOURCE] <connection_string>]
[WITH {AUTONOMOUS | COMMON} TRANSACTION]
[AS USER <user_name>]
[PASSWORD <password>]
[ROLE <role_name>]
[WITH CALLER PRIVILEGES]
Clauses cannot be duplicated.
*** Context Issues [#qac613d7]
If there is no ON EXTERNAL DATA SOURCE clause present, EX...
However, if <user_name> is not equal to CURRENT_USER, the...
Note
In the absence of an AS USER <user_name> clause, CURRENT_...
Authentication
Where server authentication is needed for a connection th...
On Windows, for the CURRENT_CONNECTION (i.e., no external...
If the external data source parameter is present and its ...
If the external data source parameter is present and its ...
In any other case where the PASSWORD clause is missing, o...
Transaction Behaviour
The new syntax has an optional clause for setting the app...
The behaviour for WITH COMMON TRANSACTION is as follows:
Causes any transaction in an external data source to be s...
Executes the statement inside the CURRENT_TRANSACTION; or
May use another transaction that is started internally in...
The WITH AUTONOMOUS TRANSACTION setting starts a new tran...
Inherited Access Privileges
Vladyslav Khorsun
Tracker reference CORE-1928.
By design, the original implementation of EXECUTE STATEME...
The introduction of the optional clause WITH CALLER PRIVI...
Important
The WITH CALLER PRIVILEGES option is not compatible with ...
*** External Queries from PSQL [#gafb52b5]
EXECUTE STATEMENT now supports queries against external d...
The <connection_string> Argument
The format of <connection_string> is the usual one that i...
[<host_name><protocol_delimiter>]database_path
Character Set
The connection to the external data source uses the same ...
Access Privileges
If the external data source is on another server then the...
The clause WITH CALLER PRIVILEGES is a no-op if the exter...
MORE INFORMATION REQUIRED. ROLES?
Note
Use of a two-phase transaction for the external connectio...
*** EXECUTE STATEMENT with Dynamic Parameters [#i5a7e0f4]
The new extensions provide the ability to prepare a state...
Syntax Conventions
The mechanism employs some conventions to facilitate the ...
The New Binding Operator
At this point in the implementation of the dynamic parame...
Syntax for Defining Parameters
<input_parameters> ::=
<named_parameter> | <input_parameters>, <named_para...
<named_parameter> ::=
<parameter name> := <expression>
Example for named input parameters
For example, the following block of PSQL defines both <qu...
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
/* Normal PSQL string assignment of <query_text> */
S = 'INSERT INTO TTT VALUES (:a, :b, :a)';
WHILE (N > 0) DO
BEGIN
/* Each loop execution applies both the string value
and the values to be bound to the input parameters */
EXECUTE STATEMENT (:S) (a := CURRENT_TRANSACTION, b...
WITH COMMON TRANSACTION;
N = N - 1;
END
END
Example for unnamed input parameters
A similar block using a set of unnamed input parameters i...
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
S = 'INSERT INTO TTT VALUES (?, ?, ?)';
WHILE (N > 0) DO
BEGIN
EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURREN...
N = N - 1;
END
END
Note
Observe that, if you use both <query_text> and <input_par...
EXECUTE STATEMENT (:sql) (p1 := 'abc', p2 := :second_...
*** Exception Handling [#o202f1ab]
The handling of exceptions depends on whether the ON EXTE...
ON EXTERNAL DATA SOURCE clause is present
If ON EXTERNAL DATA SOURCE clause is present, Firebird ca...
The text of the interpreted remote error contains both er...
Format of isc_eds_connection error
Template string
Execute statement error at @1 :\n@2Data source : @3
Status-vector tags
isc_eds_connection,
isc_arg_string, <failed API function name>,
isc_arg_string, <text of interpreted external error>,
isc_arg_string, <data source name>
Format of isc_eds_statement error
Template string
Execute statement error at @1 :\n@2Statement : @3\nData...
Status-vector tags
isc_eds_statement,
isc_arg_string, <failed API function name>,
isc_arg_string, <text of interpreted external error>,
isc_arg_string, <query>,
isc_arg_string, <data source name>
At PSQL level the symbols for these errors can be handled...
WHEN GDSCODE eds_statement
Note
Currently, the originating error codes are not accessible...
ON EXTERNAL DATA SOURCE clause is not present
If ON EXTERNAL DATA SOURCE clause is not present, the ori...
For example, if a dynamic statement were to raise the isc...
WHEN GDSCODE lock_conflict
*** Examples Using EXECUTE STATEMENT [#jb11c97f]
The following examples offer a sampler of ways that the E...
Test Connections and Transactions
A couple of tests you can try to compare variations in se...
Test a) :Execute this block few times in the same transac...
EXECUTE BLOCK
RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
DECLARE I INT = 0;
DECLARE N INT = 3;
DECLARE S VARCHAR(255);
BEGIN
SELECT A.MON$ATTACHMENT_NAME FROM MON$ATTACHMENTS A
WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
INTO :S;
WHILE (i < N) DO
BEGIN
DB = TRIM(CASE i - 3 * (I / 3)
WHEN 0 THEN '\\.\' WHEN 1 THEN 'localhost:' ELSE ''...
FOR EXECUTE STATEMENT
'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION
FROM RDB$DATABASE'
ON EXTERNAL :DB
AS USER CURRENT_USER PASSWORD 'masterkey' -- just f...
WITH COMMON TRANSACTION
INTO :CONN, :TRAN
DO SUSPEND;
i = i + 1;
END
END
Test b) : Execute this block few times in the same transa...
EXECUTE BLOCK
RETURNS (CONN INT, TRAN INT, DB VARCHAR(255))
AS
DECLARE I INT = 0;
DECLARE N INT = 3;
DECLARE S VARCHAR(255);
BEGIN
SELECT A.MON$ATTACHMENT_NAME
FROM MON$ATTACHMENTS A
WHERE A.MON$ATTACHMENT_ID = CURRENT_CONNECTION
INTO :S;
WHILE (i < N) DO
BEGIN
DB = TRIM(CASE i - 3 * (I / 3)
WHEN 0 THEN '\\.\'
WHEN 1 THEN 'localhost:'
ELSE '' END) || :S;
FOR EXECUTE STATEMENT
'SELECT CURRENT_CONNECTION, CURRENT_TRANSACTION FROM ...
ON EXTERNAL :DB
WITH AUTONOMOUS TRANSACTION -- note autonomous tran...
INTO :CONN, :TRAN
DO SUSPEND;
i = i + 1;
END
END
Input Evaluation Demo
Demonstrating that input expressions evaluated only once:
EXECUTE BLOCK
RETURNS (A INT, B INT, C INT)
AS
BEGIN
EXECUTE STATEMENT (
'SELECT CAST(:X AS INT),
CAST(:X AS INT),
CAST(:X AS INT)
FROM RDB$DATABASE')
(x := GEN_ID(G, 1))
INTO :A, :B, :C;
SUSPEND;
END
Insert Speed Test
Recycling our earlier examples for input parameter usage ...
RECREATE TABLE TTT (
TRAN INT,
CONN INT,
ID INT);
-- Direct inserts:
EXECUTE BLOCK AS
DECLARE N INT = 100000;
BEGIN
WHILE (N > 0) DO
BEGIN
INSERT INTO TTT VALUES (CURRENT_TRANSACTION, CURRENT_...
N = N - 1;
END
END
-- Inserts via prepared dynamic statement
-- using named input parameters:
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
S = 'INSERT INTO TTT VALUES (:a, :b, :a)';
WHILE (N > 0) DO
BEGIN
EXECUTE STATEMENT (:S)
(a := CURRENT_TRANSACTION, b := CURRENT_CONNECTION)
WITH COMMON TRANSACTION;
N = N - 1;
END
END
-- Inserts via prepared dynamic statement
-- using unnamed input parameters:
EXECUTE BLOCK AS
DECLARE S VARCHAR(255);
DECLARE N INT = 100000;
BEGIN
S = 'INSERT INTO TTT VALUES (?, ?, ?)';
WHILE (N > 0) DO
BEGIN
EXECUTE STATEMENT (:S) (CURRENT_TRANSACTION, CURRENT_...
N = N - 1;
END
END
** Other PSQL Improvements [#l56ab7b0]
Improvements made to existing PSQL syntax include the fol...
*** Subqueries as PSQL Expressions [#bae1821d]
Previously, a subquery used as a PSQL expression would re...
var = (select ... from ...);
if ((select ... from ...) = 1) then
if (1 = any (select ... from ...)) then
if (1 in (select ... from ...)) then
Now, such potentially valid expressions are allowed, remo...
*** SQLSTATE as Context Variable [#d1fb77f3]
(v.2.5.1) SQLSTATE is made available as a PSQL context va...
ページ名:
新規
名前変更
ホーム
一覧
検索
最終更新
バックアップ
ヘルプ
最終更新のRSS