Skip to content

Commit ad8305a

Browse files
author
Michael Meskes
committed
Add DECLARE STATEMENT command to ECPG
This command declares a SQL identifier for a SQL statement to be used in other embedded SQL statements. The identifier is linked to a connection. Author: Hayato Kuroda <kuroda.hayato@fujitsu.com> Reviewed-by: Shawn Wang <shawn.wang.pg@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/TY2PR01MB24438A52DB04E71D0E501452F5630@TY2PR01MB2443.jpnprd01.prod.outlook.com
1 parent 37c99d3 commit ad8305a

15 files changed

+1398
-17
lines changed

‎doc/src/sgml/ecpg.sgml

+139-1
Original file line numberDiff line numberDiff line change
@@ -278,7 +278,7 @@ EXEC SQL CONNECT TO :target USER :user USING :passwd;
278278
SQL statements in embedded SQL programs are by default executed on
279279
the current connection, that is, the most recently opened one. If
280280
an application needs to manage multiple connections, then there are
281-
two ways to handle this.
281+
three ways to handle this.
282282
</para>
283283

284284
<para>
@@ -350,6 +350,46 @@ main()
350350
current=testdb3 (should be testdb3)
351351
current=testdb2 (should be testdb2)
352352
current=testdb1 (should be testdb1)
353+
</screen>
354+
</para>
355+
356+
<para>
357+
The third option is to declare sql identifier linked to
358+
the connection, for example:
359+
<programlisting>
360+
EXEC SQL AT <replaceable>connection-name</replaceable> DECLARE <replaceable>statement-name</replaceable> STATEMENT;
361+
EXEC SQL PREPARE <replaceable>statement-name</replaceable> FROM :<replaceable>dyn-string</replaceable>;
362+
</programlisting>
363+
Once you link a sql identifier to a connection, you execute a dynamic SQL
364+
without AT clause. Note that this option behaves like preprocessor directives,
365+
therefore the link is enabled only in the file.
366+
</para>
367+
<para>
368+
Here is an example program using this option:
369+
<programlisting><![CDATA[
370+
#include <stdio.h>
371+
372+
EXEC SQL BEGIN DECLARE SECTION;
373+
char dbname[128];
374+
char *dym_sql = "SELECT current_database()";
375+
EXEC SQL END DECLARE SECTION;
376+
377+
int main(){
378+
EXEC SQL CONNECT TO postgres AS con1;
379+
EXEC SQL CONNECT TO testdb AS con2;
380+
EXEC SQL AT con1 DECLARE stmt STATEMENT;
381+
EXEC SQL PREPARE stmt FROM :dym_sql;
382+
EXEC SQL EXECUTE stmt INTO :dbname;
383+
printf("%s\n", dbname);
384+
385+
EXEC SQL DISCONNECT ALL;
386+
return 0;
387+
}
388+
]]></programlisting>
389+
390+
This example would produce this output, even if the default connection is testdb:
391+
<screen>
392+
postgres
353393
</screen>
354394
</para>
355395
</sect2>
@@ -6855,6 +6895,104 @@ EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
68556895
</refsect1>
68566896
</refentry>
68576897

6898+
<refentry id="ecpg-sql-declare-statement">
6899+
<refnamediv>
6900+
<refname>DECLARE STATEMENT</refname>
6901+
<refpurpose>declare SQL statement identifier</refpurpose>
6902+
</refnamediv>
6903+
6904+
<refsynopsisdiv>
6905+
<synopsis>
6906+
EXEC SQL [ AT <replaceable class="parameter">connection_name</replaceable> ] DECLARE <replaceable class="parameter">statement_name</replaceable> STATEMENT
6907+
</synopsis>
6908+
</refsynopsisdiv>
6909+
6910+
<refsect1>
6911+
<title>Description</title>
6912+
6913+
<para>
6914+
<command>DECLARE STATEMENT</command> declares SQL statement identifier.
6915+
SQL statement identifier can be associated with the connection.
6916+
When the identifier is used by dynamic SQL statements, these SQLs are executed
6917+
by using the associated connection.
6918+
The namespace of the declaration is the precompile unit, and multiple declarations to
6919+
the same SQL statement identifier is not allowed.
6920+
6921+
Note that if the precompiler run in the Informix compatibility mode and some SQL statement
6922+
is declared, "database" can not be used as a cursor name.
6923+
</para>
6924+
</refsect1>
6925+
6926+
<refsect1>
6927+
<title>Parameters</title>
6928+
6929+
<variablelist>
6930+
<varlistentry>
6931+
<term><replaceable class="parameter">connection_name</replaceable></term>
6932+
<listitem>
6933+
<para>
6934+
A database connection name established by the <command>CONNECT</command> command.
6935+
</para>
6936+
<para>
6937+
AT clause can be omitted, but such statement has no meaning.
6938+
</para>
6939+
</listitem>
6940+
</varlistentry>
6941+
</variablelist>
6942+
6943+
<variablelist>
6944+
<varlistentry>
6945+
<term><replaceable class="parameter">statement_name</replaceable></term>
6946+
<listitem>
6947+
<para>
6948+
The name of a SQL statement identifier, either as an SQL identifier or a host variable.
6949+
</para>
6950+
</listitem>
6951+
</varlistentry>
6952+
</variablelist>
6953+
</refsect1>
6954+
6955+
<refsect1>
6956+
<title>Notes</title>
6957+
<para>
6958+
This association is valid only if the declaration is physically placed on top of a dynamic statement.
6959+
</para>
6960+
</refsect1>
6961+
6962+
<refsect1>
6963+
<title>Examples</title>
6964+
6965+
<programlisting>
6966+
EXEC SQL CONNECT TO postgres AS con1;
6967+
EXEC SQL AT con1 DECLARE sql_stmt STATEMENT;
6968+
EXEC SQL DECLARE cursor_name CURSOR FOR sql_stmt;
6969+
EXEC SQL PREPARE sql_stmt FROM :dyn_string;
6970+
EXEC SQL OPEN cursor_name;
6971+
EXEC SQL FETCH cursor_name INTO :column1;
6972+
EXEC SQL CLOSE cursor_name;
6973+
</programlisting>
6974+
</refsect1>
6975+
6976+
<refsect1>
6977+
<title>Compatibility</title>
6978+
6979+
<para>
6980+
<command>DECLARE STATEMENT</command> is a extension of the SQL standard,
6981+
but can be used in famous DBMSs.
6982+
</para>
6983+
</refsect1>
6984+
6985+
<refsect1>
6986+
<title>See Also</title>
6987+
6988+
<simplelist type="inline">
6989+
<member><xref linkend="ecpg-sql-connect"/></member>
6990+
<member><xref linkend="ecpg-sql-declare"/></member>
6991+
<member><xref linkend="ecpg-sql-open"/></member>
6992+
</simplelist>
6993+
</refsect1>
6994+
</refentry>
6995+
68586996
<refentry id="ecpg-sql-describe">
68596997
<refnamediv>
68606998
<refname>DESCRIBE</refname>

‎src/interfaces/ecpg/preproc/ecpg.addons

+76-14
Original file line numberDiff line numberDiff line change
@@ -33,6 +33,7 @@ ECPG: stmtUpdateStmt block
3333
{ output_statement($1, 1, ECPGst_prepnormal); }
3434
ECPG: stmtExecuteStmt block
3535
{
36+
check_declared_list($1.name);
3637
if ($1.type == NULL || strlen($1.type) == 0)
3738
output_statement($1.name, 1, ECPGst_execute);
3839
else
@@ -56,6 +57,7 @@ ECPG: stmtExecuteStmt block
5657
}
5758
ECPG: stmtPrepareStmt block
5859
{
60+
check_declared_list($1.name);
5961
if ($1.type == NULL)
6062
output_prepare_statement($1.name, $1.stmt);
6163
else if (strlen($1.type) == 0)
@@ -104,6 +106,10 @@ ECPG: stmtViewStmt rule
104106
whenever_action(2);
105107
free($1);
106108
}
109+
| ECPGDeclareStmt
110+
{
111+
output_simple_statement($1, 0);
112+
}
107113
| ECPGCursorStmt
108114
{
109115
output_simple_statement($1, (strncmp($1, "ECPGset_var", strlen("ECPGset_var")) == 0) ? 4 : 0);
@@ -244,14 +250,20 @@ ECPG: var_valueNumericOnly addon
244250
$1 = mm_strdup("$0");
245251
}
246252
ECPG: fetch_argscursor_name addon
247-
add_additional_variables($1, false);
253+
struct cursor *ptr = add_additional_variables($1, false);
254+
if (ptr -> connection)
255+
connection = mm_strdup(ptr -> connection);
256+
248257
if ($1[0] == ':')
249258
{
250259
free($1);
251260
$1 = mm_strdup("$0");
252261
}
253262
ECPG: fetch_argsfrom_incursor_name addon
254-
add_additional_variables($2, false);
263+
struct cursor *ptr = add_additional_variables($2, false);
264+
if (ptr -> connection)
265+
connection = mm_strdup(ptr -> connection);
266+
255267
if ($2[0] == ':')
256268
{
257269
free($2);
@@ -262,14 +274,20 @@ ECPG: fetch_argsPRIORopt_from_incursor_name addon
262274
ECPG: fetch_argsFIRST_Popt_from_incursor_name addon
263275
ECPG: fetch_argsLAST_Popt_from_incursor_name addon
264276
ECPG: fetch_argsALLopt_from_incursor_name addon
265-
add_additional_variables($3, false);
277+
struct cursor *ptr = add_additional_variables($3, false);
278+
if (ptr -> connection)
279+
connection = mm_strdup(ptr -> connection);
280+
266281
if ($3[0] == ':')
267282
{
268283
free($3);
269284
$3 = mm_strdup("$0");
270285
}
271286
ECPG: fetch_argsSignedIconstopt_from_incursor_name addon
272-
add_additional_variables($3, false);
287+
struct cursor *ptr = add_additional_variables($3, false);
288+
if (ptr -> connection)
289+
connection = mm_strdup(ptr -> connection);
290+
273291
if ($3[0] == ':')
274292
{
275293
free($3);
@@ -282,7 +300,10 @@ ECPG: fetch_argsSignedIconstopt_from_incursor_name addon
282300
}
283301
ECPG: fetch_argsFORWARDALLopt_from_incursor_name addon
284302
ECPG: fetch_argsBACKWARDALLopt_from_incursor_name addon
285-
add_additional_variables($4, false);
303+
struct cursor *ptr = add_additional_variables($4, false);
304+
if (ptr -> connection)
305+
connection = mm_strdup(ptr -> connection);
306+
286307
if ($4[0] == ':')
287308
{
288309
free($4);
@@ -292,7 +313,10 @@ ECPG: fetch_argsABSOLUTE_PSignedIconstopt_from_incursor_name addon
292313
ECPG: fetch_argsRELATIVE_PSignedIconstopt_from_incursor_name addon
293314
ECPG: fetch_argsFORWARDSignedIconstopt_from_incursor_name addon
294315
ECPG: fetch_argsBACKWARDSignedIconstopt_from_incursor_name addon
295-
add_additional_variables($4, false);
316+
struct cursor *ptr = add_additional_variables($4, false);
317+
if (ptr -> connection)
318+
connection = mm_strdup(ptr -> connection);
319+
296320
if ($4[0] == ':')
297321
{
298322
free($4);
@@ -348,6 +372,9 @@ ECPG: DeclareCursorStmtDECLAREcursor_namecursor_optionsCURSORopt_holdFORSelectSt
348372
char *comment, *c1, *c2;
349373
int (* strcmp_fn)(const char *, const char *) = (($2[0] == ':' || $2[0] == '"') ? strcmp : pg_strcasecmp);
350374

375+
if (INFORMIX_MODE && pg_strcasecmp($2, "database") == 0)
376+
mmfatal(PARSE_ERROR, "\"database\" cannot be used as cursor name in INFORMIX mode");
377+
351378
for (ptr = cur; ptr != NULL; ptr = ptr->next)
352379
{
353380
if (strcmp_fn($2, ptr->name) == 0)
@@ -388,6 +415,17 @@ ECPG: DeclareCursorStmtDECLAREcursor_namecursor_optionsCURSORopt_holdFORSelectSt
388415
ECPG: ClosePortalStmtCLOSEcursor_name block
389416
{
390417
char *cursor_marker = $2[0] == ':' ? mm_strdup("$0") : $2;
418+
struct cursor *ptr = NULL;
419+
for (ptr = cur; ptr != NULL; ptr = ptr -> next)
420+
{
421+
if (strcmp($2, ptr -> name) == 0)
422+
{
423+
if (ptr -> connection)
424+
connection = mm_strdup(ptr -> connection);
425+
426+
break;
427+
}
428+
}
391429
$$ = cat2_str(mm_strdup("close"), cursor_marker);
392430
}
393431
ECPG: opt_hold block
@@ -466,49 +504,73 @@ ECPG: FetchStmtMOVEfetch_args rule
466504
| FETCH FORWARD cursor_name opt_ecpg_fetch_into
467505
{
468506
char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
469-
add_additional_variables($3, false);
507+
struct cursor *ptr = add_additional_variables($3, false);
508+
if (ptr -> connection)
509+
connection = mm_strdup(ptr -> connection);
510+
470511
$$ = cat_str(2, mm_strdup("fetch forward"), cursor_marker);
471512
}
472513
| FETCH FORWARD from_in cursor_name opt_ecpg_fetch_into
473514
{
474515
char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
475-
add_additional_variables($4, false);
516+
struct cursor *ptr = add_additional_variables($4, false);
517+
if (ptr -> connection)
518+
connection = mm_strdup(ptr -> connection);
519+
476520
$$ = cat_str(2, mm_strdup("fetch forward from"), cursor_marker);
477521
}
478522
| FETCH BACKWARD cursor_name opt_ecpg_fetch_into
479523
{
480524
char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
481-
add_additional_variables($3, false);
525+
struct cursor *ptr = add_additional_variables($3, false);
526+
if (ptr -> connection)
527+
connection = mm_strdup(ptr -> connection);
528+
482529
$$ = cat_str(2, mm_strdup("fetch backward"), cursor_marker);
483530
}
484531
| FETCH BACKWARD from_in cursor_name opt_ecpg_fetch_into
485532
{
486533
char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
487-
add_additional_variables($4, false);
534+
struct cursor *ptr = add_additional_variables($4, false);
535+
if (ptr -> connection)
536+
connection = mm_strdup(ptr -> connection);
537+
488538
$$ = cat_str(2, mm_strdup("fetch backward from"), cursor_marker);
489539
}
490540
| MOVE FORWARD cursor_name
491541
{
492542
char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
493-
add_additional_variables($3, false);
543+
struct cursor *ptr = add_additional_variables($3, false);
544+
if (ptr -> connection)
545+
connection = mm_strdup(ptr -> connection);
546+
494547
$$ = cat_str(2, mm_strdup("move forward"), cursor_marker);
495548
}
496549
| MOVE FORWARD from_in cursor_name
497550
{
498551
char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
499-
add_additional_variables($4, false);
552+
struct cursor *ptr = add_additional_variables($4, false);
553+
if (ptr -> connection)
554+
connection = mm_strdup(ptr -> connection);
555+
500556
$$ = cat_str(2, mm_strdup("move forward from"), cursor_marker);
501557
}
502558
| MOVE BACKWARD cursor_name
503559
{
504560
char *cursor_marker = $3[0] == ':' ? mm_strdup("$0") : $3;
505-
add_additional_variables($3, false);
561+
struct cursor *ptr = add_additional_variables($3, false);
562+
if (ptr -> connection)
563+
connection = mm_strdup(ptr -> connection);
564+
506565
$$ = cat_str(2, mm_strdup("move backward"), cursor_marker);
507566
}
508567
| MOVE BACKWARD from_in cursor_name
509568
{
510569
char *cursor_marker = $4[0] == ':' ? mm_strdup("$0") : $4;
511-
add_additional_variables($4, false);
570+
struct cursor *ptr = add_additional_variables($4, false);
571+
if (ptr -> connection)
572+
connection = mm_strdup(ptr -> connection);
573+
512574
$$ = cat_str(2, mm_strdup("move backward from"), cursor_marker);
513575
}
514576
ECPG: limit_clauseLIMITselect_limit_value','select_offset_value block

‎src/interfaces/ecpg/preproc/ecpg.c

+9
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ struct _include_path *include_paths = NULL;
2828
struct cursor *cur = NULL;
2929
struct typedefs *types = NULL;
3030
struct _defines *defines = NULL;
31+
struct declared_list *g_declared_list = NULL;
3132

3233
static void
3334
help(const char *progname)
@@ -347,6 +348,7 @@ main(int argc, char *const argv[])
347348
struct cursor *ptr;
348349
struct _defines *defptr;
349350
struct typedefs *typeptr;
351+
struct declared_list *list;
350352

351353
/* remove old cursor definitions if any are still there */
352354
for (ptr = cur; ptr != NULL;)
@@ -373,6 +375,13 @@ main(int argc, char *const argv[])
373375
}
374376
cur = NULL;
375377

378+
/* remove old delared statements if any are still there */
379+
for (list = g_declared_list; list != NULL;)
380+
{
381+
struct declared_list *this = list;
382+
free(this);
383+
}
384+
376385
/* remove non-pertinent old defines as well */
377386
while (defines && !defines->pertinent)
378387
{

0 commit comments

Comments
 (0)