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 ) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
