DECLARE STATEMENT setting up a connection in ECPG

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 )

Responses

Browse pgsql-hackers by date

  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