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 costbecause virtualization
suchas 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_stmtEXEC SQL PREPARE
sql_stmtFROM :dyn_string
[System Design Plan]
To support above functionality, ecpg precompiler should support:- To understand the DECLARE STATEMENT syntax -
Translatethe DECLARE STATEMENT into a new function with parameters. These parameters carry the information like
connection_nameand statement_name. - The function is a new function defined in the ECPG library.
Following functions are going to be modified: - ECPGprepare - ECPGdeallocate - ECPGdescribe - ECPGdoBut I think there
isroom 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 )