aboutsummaryrefslogtreecommitdiff
path: root/doc/src
diff options
context:
space:
mode:
authorMichael Meskes <meskes@postgresql.org>2019-02-16 10:55:17 +0100
committerMichael Meskes <meskes@postgresql.org>2019-02-16 11:05:54 +0100
commitbd7c95f0c1a38becffceb3ea7234d57167f6d4bf (patch)
tree8ab862d314ec1e241ef0b90fc42edf3f6f5e4be8 /doc/src
parent02a6a54ecd6632f974b1b4eebfb2373363431084 (diff)
downloadpostgresql-bd7c95f0c1a38becffceb3ea7234d57167f6d4bf.tar.gz
postgresql-bd7c95f0c1a38becffceb3ea7234d57167f6d4bf.zip
Add DECLARE STATEMENT support to ECPG.
DECLARE STATEMENT is a statement that lets users declare an identifier pointing at a connection. This identifier will be used in other embedded dynamic SQL statement such as PREPARE, EXECUTE, DECLARE CURSOR and so on. When connecting to a non-default connection, the AT clause can be used in a DECLARE STATEMENT once and is no longer needed in every dynamic SQL statement. This makes ECPG applications easier and more efficient. Moreover, writing code without designating connection explicitly improves portability. Authors: Ideriha-san ("Ideriha, Takeshi" <ideriha.takeshi@jp.fujitsu.com>) Kuroda-san ("Kuroda, Hayato" <kuroda.hayato@jp.fujitsu.com>) Discussion: https://postgr.es/m4E72940DA2BF16479384A86D54D0988A565669DF@G01JPEXMBKW04
Diffstat (limited to 'doc/src')
-rw-r--r--doc/src/sgml/ecpg.sgml192
1 files changed, 191 insertions, 1 deletions
diff --git a/doc/src/sgml/ecpg.sgml b/doc/src/sgml/ecpg.sgml
index fac45400b03..d2319c8b323 100644
--- a/doc/src/sgml/ecpg.sgml
+++ b/doc/src/sgml/ecpg.sgml
@@ -236,7 +236,7 @@ EXEC SQL CONNECT TO :target USER :user USING :passwd;
SQL statements in embedded SQL programs are by default executed on
the current connection, that is, the most recently opened one. If
an application needs to manage multiple connections, then there are
- two ways to handle this.
+ three ways to handle this.
</para>
<para>
@@ -310,6 +310,17 @@ current=testdb2 (should be testdb2)
current=testdb1 (should be testdb1)
</screen>
</para>
+
+ <para>
+ The third option is to declare a sql identifier linked to
+ the connection, for example:
+<programlisting>
+EXEC SQL AT <replaceable>connection-name</replaceable> DECLARE <replaceable>statement-name</replaceable> STATEMENT;
+EXEC SQL PREPARE <replaceable>statement-name</replaceable> FROM :<replaceable>dyn-string</replaceable>;
+</programlisting>
+ Once you link a sql identifier to a connection, you execute a dynamic SQL
+ without AT clause.
+ </para>
</sect2>
<sect2 id="ecpg-disconnect">
@@ -6766,6 +6777,185 @@ EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
</refsect1>
</refentry>
+ <refentry id="ecpg-sql-declare-statement">
+ <refnamediv>
+ <refname>DECLARE STATEMENT</refname>
+ <refpurpose>declares SQL statement identifier associated with connection</refpurpose>
+ </refnamediv>
+
+ <refsynopsisdiv>
+<synopsis>
+EXEC SQL [ AT <replaceable class="parameter">connection_name</replaceable> ] DECLARE <replaceable class="parameter">statement_name</replaceable> STATEMENT
+</synopsis>
+ </refsynopsisdiv>
+
+ <refsect1>
+ <title>Description</title>
+
+ <para>
+ <command>DECLARE STATEMENT</command> declares SQL statement identifier.
+ SQL statement identifier is associated with connection.
+ </para>
+
+ <para>
+ <command>DELARE CURSOR</command> with a SQL statement identifier can be written before PREPARE.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Parameters</title>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">connection_name</replaceable></term>
+ <listitem>
+ <para>
+ A database connection name established by the <command>CONNECT</command> command.
+ </para>
+ <para>
+ If AT clause is omitted, an SQL statement identifier is associated with the DEFAULT connection.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+
+ <variablelist>
+ <varlistentry>
+ <term><replaceable class="parameter">statement_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a SQL statement identifier, either as an SQL identifier or a host variable.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </refsect1>
+
+ <refsect1>
+ <title>Notes</title>
+ <para>
+ AT clause can be used at other dynamic SQL statements. The following table
+ gives the connected database when AT clause is used at DECLARE STATEMENT
+ and other dynamic statements.
+ </para>
+ <table tocentry="1" id="ecpg-declare-statement-table">
+ <title>Scenario</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>
+ Using Scenario
+ </entry>
+ <entry>
+ Declare Statement
+ </entry>
+ <entry>
+ Other Dynamic Statements
+ </entry>
+ <entry>
+ Executed Database
+ </entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry>
+ 1
+ </entry>
+ <entry>
+ Without AT clause
+ </entry>
+ <entry>
+ Without AT clause
+ </entry>
+ <entry>
+ Default connection
+ </entry>
+ </row>
+ <row>
+ <entry>
+ 2
+ </entry>
+ <entry>
+ Using AT clause connecting at con1
+ </entry>
+ <entry>
+ Without AT clause
+ </entry>
+ <entry>
+ con1
+ </entry>
+ </row>
+ <row>
+ <entry>
+ 3
+ </entry>
+ <entry>
+ Using AT clause connecting at con1
+ </entry>
+ <entry>
+ Using AT clause connecting at con2
+ </entry>
+ <entry>
+ con1
+ </entry>
+ </row>
+ <row>
+ <entry>
+ 4
+ </entry>
+ <entry>
+ Without AT clause
+ </entry>
+ <entry>
+ Using AT clause connecting at con2
+ </entry>
+ <entry>
+ con2
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ <para>
+ In scenario 4, DECLARE STATEMENT will be ignored.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
+<programlisting>
+EXEC SQL CONNECT TO postgres AS con1;
+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;
+EXEC SQL OPEN cursor_name;
+EXEC SQL FETCH cursor_name INTO :column1;
+EXEC SQL CLOSE cursor_name;
+</programlisting>
+ </refsect1>
+
+ <refsect1>
+ <title>Compatibility</title>
+
+ <para>
+ <command>DECLARE STATEMENT</command> is a PostgreSQL extension of the SQL standard,
+ but can be used in Oracle and DB2.
+ </para>
+ </refsect1>
+
+ <refsect1>
+ <title>See Also</title>
+
+ <simplelist type="inline">
+ <member><xref linkend="ecpg-sql-connect"/></member>
+ <member><xref linkend="ecpg-sql-declare"/></member>
+ <member><xref linkend="ecpg-sql-open"/></member>
+ </simplelist>
+ </refsect1>
+ </refentry>
+
<refentry id="ecpg-sql-describe">
<refnamediv>
<refname>DESCRIBE</refname>