From: | "Ideriha, Takeshi" <ideriha(dot)takeshi(at)jp(dot)fujitsu(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | DECLARE STATEMENT setting up a connection in ECPG |
Date: | 2016-11-15 00:26:49 |
Message-ID: | 4E72940DA2BF16479384A86D54D0988A4D80D3C9@G01JPEXMBKW04 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
Making developing ECPG application more efficiently and improve portability,
I'd like to suggest DECLARE STATEMENT support in ECPG.
This DECLARE STATEMENT is one of the statement that lets users declare an identifier pointing a connection.
This identifier will be used in other embedded dynamic SQL statement
such as PREPARE, EXECUTE, DECLARE CURSOR and so on.
(Oracle implements this.)
https://docs.oracle.com/cd/B10501_01/appdev.920/a42525/apf.htm#declare_stmt
Under the current system, a user must use the AT clause in every SQL statements
when executing the dynamic SQL at non-default connection.
https://www.postgresql.org/docs/current/static/ecpg-connect.html
When a user needs to connect to a non-default connection,
AT clause can be used in DECLARE STATEMENT once and need not to be in every dynamic SQL statements.
This helps a user with making ECPG application easily and efficiently
without explicitly designating a connection for each SQL statement.
Moreover, writing code without designating connection explicitly
improves portability.
[Use-case]
It is very useful when the data needed for a report, business decision is spread across several data sources,
and one application needs to connect multiple database server.
Especially these days, multiple database servers are easily set up without taking time and cost
because virtualization such as docker and microservices are in fashion.
This trend leads to be growing importance of this handy connection switching function.
[Interface]
The syntax for the DECLARE STATEMENT in ECPG is as following:
EXEC SQL [ AT connection-name ] DECLARE statement-name STATEMENT
, where "statement-name" is an SQL identifier
and "connection name" points to the connection which will be used to execute the dynamic SQL statements.
[Example]
EXEC SQL AT con1 DECLARE sql_stmt STATEMENT
EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt
EXEC SQL PREPARE sql_stmt FROM :dyn_string
[System Design Plan]
To support above functionality, ecpg precompiler should support:
- To understand the DECLARE STATEMENT syntax
- Translate the DECLARE STATEMENT into a new function with parameters.
These parameters carry the information like connection_name and statement_name.
- The function is a new function defined in the ECPG library.
Following functions are going to be modified:
- ECPGprepare
- ECPGdeallocate
- ECPGdescribe
- ECPGdo
But I think there is room for discussing modifying ECPGdo,
because it's a very common function that will map many SQL statement
including SELECT, INSERT, EXECTUTE, CURSOR and so on.
It seems to me there is no discussion on this topic.
But if exists, would you let me know?
Regards.
Ideriha Takeshi,
Fujitsu (Fujitsu Enterprise Postgres )
From | Date | Subject | |
---|---|---|---|
Next Message | Tomas Vondra | 2016-11-15 00:44:35 | Re: PATCH: two slab-like memory allocators |
Previous Message | Michael Paquier | 2016-11-14 22:52:06 | Re: Password identifiers, protocol aging and SCRAM protocol |