Обсуждение: DECLARE STATEMENT setting up a connection in ECPG

Поиск
Список
Период
Сортировка

DECLARE STATEMENT setting up a connection in ECPG

От
"Ideriha, Takeshi"
Дата:
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 )




Re: DECLARE STATEMENT setting up a connection in ECPG

От
Michael Meskes
Дата:
> [System Design Plan]
> To support above functionality, ecpg precompiler should support:
>  - To understand the DECLARE STATEMENT syntax 

Already does, albeit as a noop.

>  - 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.

Why's that? I haven't checked if the standard says anything about this and my
memory might be wrong, but isn't "DECLARE STATEMENT" supposed to be purely
declarative, i.e. not executed at run time?  

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL



Re: DECLARE STATEMENT setting up a connection in ECPG

От
"Ideriha, Takeshi"
Дата:
Thank you for your comment.

Friday, November 18, 2016 4:45 AM Michael Meskes wrote :
> >  - 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.
> 
> Why's that? I haven't checked if the standard says anything about this and my
> memory might be wrong, but isn't "DECLARE STATEMENT" supposed to be purely
> declarative, i.e. not executed at run time?

My lack of explanation caused the confusion, sorry.
Your point is true.
"DECLARE STATEMENT" is declarative ,not function in .pgc file.

I wanted to say that in order to use the connection pointed 
by the DECLARE STATEMENT some functions like ECPGdo() would be modified or
new function would be added under the directory ecpglib/.

This modification or new function will be used to get the connection by statement_name.

Regards, 
Ideriha, Takeshi
Fujitsu

Re: DECLARE STATEMENT setting up a connection in ECPG

От
Michael Meskes
Дата:
> I wanted to say that in order to use the connection pointed 
> by the DECLARE STATEMENT some functions like ECPGdo() would be
> modified or
> new function would be added under the directory ecpglib/.
> 
> This modification or new function will be used to get the connection
> by statement_name.

Ah, now I understand. Thank you for your explanation.

I'm looking forward to seeing your patch.

Michael
-- 
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL