Hi,
Thanks for the review. Please find my comments in-line.
On Fri, Jul 19, 2019 at 8:33 AM Kyotaro Horiguchi
<horikyota....@gmail.com> wrote:
>
> Hello.
>
>
> +ECPG: CallStmtCALLfunc_application
>
> Even though it is the default behavior, but as a written rule
> this needs the postfix "block".
>
Done.
> + $$ = cat_str(2,mm_strdup("call"),$2);
>
> Let's have proper spacing.
>
> + * Copy input arguments to the result arguments list so that all the
> + * host variables gets treated as INOUT params.
>
I've removed above comments so this is no more valid.
> This fails for the following usage:
>
> -- define procedure
> create procedure ptest2 (in x int, inout y int) language plpgsql as $$
> begin
> y := y + x;
> end;
> $$;
>
> -- in .pgc
> 14: a = 3;
> 15: r = 5;
> 16: EXEC SQL call ptest2(:a, :r);
> 21: printf("ret = %d, %d\n", a, r);
>
>
> This complains like this:
>
> > SQL error: too many arguments on line 16
> > ret = 8, 5;
>
> The result should be "3, 8". This is because the patch requests
> two return but actually retruned just one.
>
> I'm not sure how to know that previously on ecpg. Might need to
> let users append INTO <vars> clause explicitly.
>
As the ecpg connector is not aware of the param types of the procedure
that it is calling, it becomes the responsibility of end users to
ensure that only those many out variables gets created by ecpg as the
number of fields in the tuple returned by the server and for that, as
you rightly said they must use the INTO clause with CALL statement in
ecpg. Considering this approach, now with the attached v2 patch, the
CALL statement in ecpg application would be like this:
EXEC SQL CALL(:hv1, :hv2) INTO :ret1, ret2;
EXEC SQL CALL(:hv1, :hv2) INTO :ret1 :ind1, :ret2, :ind2;
In case if INTO clause is not used with the CALL statement then the
ecpg compiler would fail with a parse error: "INTO clause is required
with CALL statement"
--
With Regards,
Ashutosh Sharma
EnterpriseDB:http://www.enterprisedb.com
diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons
index cbffd50..932b7f9 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -555,3 +555,12 @@ ECPG: limit_clauseLIMITselect_limit_value','select_offset_value block
}
ECPG: SignedIconstIconst rule
| civar { $$ = $1; }
+ECPG: CallStmtCALLfunc_application block
+ {
+ mmerror(PARSE_ERROR, ET_ERROR, "INTO clause is required with CALL statement");
+ $$ = EMPTY;
+ }
+ | CALL func_application ecpg_into
+ {
+ $$ = cat_str(2,mm_strdup("call"),$2);
+ }
diff --git a/src/interfaces/ecpg/test/ecpg_schedule b/src/interfaces/ecpg/test/ecpg_schedule
index e034c5a..7e6b5c7 100644
--- a/src/interfaces/ecpg/test/ecpg_schedule
+++ b/src/interfaces/ecpg/test/ecpg_schedule
@@ -33,6 +33,7 @@ test: preproc/whenever_do_continue
test: sql/array
test: sql/binary
test: sql/bytea
+test: sql/call
test: sql/code100
test: sql/copystdout
test: sql/createtableas
diff --git a/src/interfaces/ecpg/test/expected/sql-call.c b/src/interfaces/ecpg/test/expected/sql-call.c
new file mode 100644
index 0000000..813b3b7
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-call.c
@@ -0,0 +1,264 @@
+/* Processed by ecpg (regression mode) */
+/* These include files are added by the preprocessor */
+#include <ecpglib.h>
+#include <ecpgerrno.h>
+#include <sqlca.h>
+/* End of automatic include section */
+#define ECPGdebug(X,Y) ECPGdebug((X)+100,(Y))
+
+#line 1 "call.pgc"
+#include <stdio.h>
+#include <string.h>
+
+
+#line 1 "sqlca.h"
+#ifndef POSTGRES_SQLCA_H
+#define POSTGRES_SQLCA_H
+
+#ifndef PGDLLIMPORT
+#if defined(WIN32) || defined(__CYGWIN__)
+#define PGDLLIMPORT __declspec (dllimport)
+#else
+#define PGDLLIMPORT
+#endif /* __CYGWIN__ */
+#endif /* PGDLLIMPORT */
+
+#define SQLERRMC_LEN 150
+
+#ifdef __cplusplus
+extern "C"
+{
+#endif
+
+struct sqlca_t
+{
+ char sqlcaid[8];
+ long sqlabc;
+ long sqlcode;
+ struct
+ {
+ int sqlerrml;
+ char sqlerrmc[SQLERRMC_LEN];
+ } sqlerrm;
+ char sqlerrp[8];
+ long sqlerrd[6];
+ /* Element 0: empty */
+ /* 1: OID of processed tuple if applicable */
+ /* 2: number of rows processed */
+ /* after an INSERT, UPDATE or */
+ /* DELETE statement */
+ /* 3: empty */
+ /* 4: empty */
+ /* 5: empty */
+ char sqlwarn[8];
+ /* Element 0: set to 'W' if at least one other is 'W' */
+ /* 1: if 'W' at least one character string */
+ /* value was truncated when it was */
+ /* stored into a host variable. */
+
+ /*
+ * 2: if 'W' a (hopefully) non-fatal notice occurred
+ */ /* 3: empty */
+ /* 4: empty */
+ /* 5: empty */
+ /* 6: empty */
+ /* 7: empty */
+
+ char sqlstate[5];
+};
+
+struct sqlca_t *ECPGget_sqlca(void);
+
+#ifndef POSTGRES_ECPG_INTERNAL
+#define sqlca (*ECPGget_sqlca())
+#endif
+
+#ifdef __cplusplus
+}
+#endif
+
+#endif
+
+#line 4 "call.pgc"
+
+
+#line 1 "regression.h"
+
+
+
+
+
+
+#line 5 "call.pgc"
+
+
+int
+main(void)
+{
+ /* exec sql begin declare section */
+
+
+
+
+
+#line 11 "call.pgc"
+ int hv1 = 10 ;
+
+#line 12 "call.pgc"
+ int hv2 = 20 ;
+
+#line 13 "call.pgc"
+ int ind1 = 0 ;
+
+#line 14 "call.pgc"
+ int ind2 = 0 ;
+/* exec sql end declare section */
+#line 15 "call.pgc"
+
+
+ /* exec sql whenever sqlerror do sqlprint ( ) ; */
+#line 17 "call.pgc"
+
+ { ECPGconnect(__LINE__, 0, "ecpg1_regression" , NULL, NULL , NULL, 0);
+#line 18 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 18 "call.pgc"
+;
+
+ /* Start a new transaction. */
+ { ECPGtrans(__LINE__, NULL, "begin transaction");
+#line 21 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 21 "call.pgc"
+
+
+ /* Create test tables. */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table t1 ( a int , b int )", ECPGt_EOIT, ECPGt_EORT);
+#line 24 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 24 "call.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create table t2 ( a int , b int )", ECPGt_EOIT, ECPGt_EORT);
+#line 25 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 25 "call.pgc"
+
+
+ /* Insert some data into test tables created above. */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into t1 values ( 10 , 100 ) , ( 30 , 300 ) , ( 50 , 500 )", ECPGt_EOIT, ECPGt_EORT);
+#line 28 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 28 "call.pgc"
+
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "insert into t2 values ( 20 , 200 ) , ( 40 , 400 ) , ( 60 , 600 )", ECPGt_EOIT, ECPGt_EORT);
+#line 29 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 29 "call.pgc"
+
+
+ /* Create stored procedure1 with INOUT params. */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create or replace procedure sp1 ( inout v1 int , inout v2 int ) as $$\
+ begin\
+ v1 := (select b from t1 where a = v1);\
+ v2 := (select b from t2 where a = v2);\
+ end; $$ language plpgsql", ECPGt_EOIT, ECPGt_EORT);
+#line 38 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 38 "call.pgc"
+
+
+ /* Create stored procedure2 with IN and INOUT params. */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "create or replace procedure sp2 ( in v1 int , inout v2 int ) as $$\
+ declare\
+ v3 int := v2;\
+ begin\
+ v2 := (select b from t1 where a = v1);\
+ v3 := (select b from t2 where a = v3);\
+ end; $$ language plpgsql", ECPGt_EOIT, ECPGt_EORT);
+#line 49 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 49 "call.pgc"
+
+
+ /* Call stored procedure1 and print it's output. */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "call sp1 ( $1 , $2 )",
+ ECPGt_int,&(hv1),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(hv2),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
+ ECPGt_int,&(hv1),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(hv2),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 52 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 52 "call.pgc"
+
+
+ printf("Stored procedure1 output: hv1 = %d, hv2 = %d\n", hv1, hv2);
+
+ /* Call stored procedure1 with indicator variables and print it's output. */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "call sp1 ( $1 , $2 )",
+ ECPGt_int,&(hv1),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(hv2),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
+ ECPGt_int,&(hv1),(long)1,(long)1,sizeof(int),
+ ECPGt_int,&(ind1),(long)1,(long)1,sizeof(int),
+ ECPGt_int,&(hv2),(long)1,(long)1,sizeof(int),
+ ECPGt_int,&(ind2),(long)1,(long)1,sizeof(int), ECPGt_EORT);
+#line 57 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 57 "call.pgc"
+
+
+ printf("\nStored procedure1 output with indicator variables.\n");
+ printf("Indicator variables ind1 and ind2 must hold negative values indicating that server returned NULL values this time.\n");
+ printf("\nhv1 = %d ind1 = %d, hv2 = %d ind2 = %d\n", hv1, ind1, hv2, ind2);
+
+ /* Reset the value of hv1 and hv2 before calling stored procedure sp2. */
+ hv1 = 30;
+ hv2 = 40;
+
+ /* Call stored procedure2 and print it's output. */
+ { ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "call sp2 ( $1 , $2 )",
+ ECPGt_int,&(hv1),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
+ ECPGt_int,&(hv2),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
+ ECPGt_int,&(hv2),(long)1,(long)1,sizeof(int),
+ ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
+#line 68 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 68 "call.pgc"
+
+
+ printf("\nStored procedure2 output: hv2 = %d\n", hv2);
+
+ { ECPGtrans(__LINE__, NULL, "rollback");
+#line 72 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 72 "call.pgc"
+
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 74 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 74 "call.pgc"
+
+
+ return 0;
+}
diff --git a/src/interfaces/ecpg/test/expected/sql-call.stderr b/src/interfaces/ecpg/test/expected/sql-call.stderr
new file mode 100644
index 0000000..e69de29
diff --git a/src/interfaces/ecpg/test/expected/sql-call.stdout b/src/interfaces/ecpg/test/expected/sql-call.stdout
new file mode 100644
index 0000000..3d03b90
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-call.stdout
@@ -0,0 +1,8 @@
+Stored procedure1 output: hv1 = 100, hv2 = 200
+
+Stored procedure1 output with indicator variables.
+Indicator variables ind1 and ind2 must hold negative values indicating that server returned NULL values this time.
+
+hv1 = 100 ind1 = -1, hv2 = 200 ind2 = -1
+
+Stored procedure2 output: hv2 = 300
diff --git a/src/interfaces/ecpg/test/sql/Makefile b/src/interfaces/ecpg/test/sql/Makefile
index 876ca8d..38169f4 100644
--- a/src/interfaces/ecpg/test/sql/Makefile
+++ b/src/interfaces/ecpg/test/sql/Makefile
@@ -28,7 +28,8 @@ TESTS = array array.c \
insupd insupd.c \
declare declare.c \
bytea bytea.c \
- prepareas prepareas.c
+ prepareas prepareas.c \
+ call call.c
all: $(TESTS)
diff --git a/src/interfaces/ecpg/test/sql/call.pgc b/src/interfaces/ecpg/test/sql/call.pgc
new file mode 100644
index 0000000..1b38c0b
--- /dev/null
+++ b/src/interfaces/ecpg/test/sql/call.pgc
@@ -0,0 +1,77 @@
+#include <stdio.h>
+#include <string.h>
+
+exec sql include sqlca;
+exec sql include ../regression;
+
+int
+main(void)
+{
+ exec sql begin declare section;
+ int hv1 = 10;
+ int hv2 = 20;
+ int ind1 = 0;
+ int ind2 = 0;
+ exec sql end declare section;
+
+ exec sql whenever sqlerror do sqlprint();
+ exec sql connect to REGRESSDB1;;
+
+ /* Start a new transaction. */
+ exec sql begin transaction;
+
+ /* Create test tables. */
+ exec sql create table t1(a int, b int);
+ exec sql create table t2(a int, b int);
+
+ /* Insert some data into test tables created above. */
+ exec sql insert into t1 values (10, 100), (30, 300), (50, 500);
+ exec sql insert into t2 values (20, 200), (40, 400), (60, 600);
+
+ /* Create stored procedure1 with INOUT params. */
+ exec sql create or replace procedure sp1(INOUT v1 int, INOUT v2 int)
+ as $$
+ begin
+ v1 := (select b from t1 where a = v1);
+ v2 := (select b from t2 where a = v2);
+ end; $$
+ language plpgsql;
+
+ /* Create stored procedure2 with IN and INOUT params. */
+ exec sql create or replace procedure sp2(IN v1 int, INOUT v2 int)
+ as $$
+ declare
+ v3 int := v2;
+ begin
+ v2 := (select b from t1 where a = v1);
+ v3 := (select b from t2 where a = v3);
+ end; $$
+ language plpgsql;
+
+ /* Call stored procedure1 and print it's output. */
+ exec sql call sp1(:hv1, :hv2) into :hv1, :hv2;
+
+ printf("Stored procedure1 output: hv1 = %d, hv2 = %d\n", hv1, hv2);
+
+ /* Call stored procedure1 with indicator variables and print it's output. */
+ exec sql call sp1(:hv1, :hv2) into :hv1 :ind1, :hv2 :ind2;
+
+ printf("\nStored procedure1 output with indicator variables.\n");
+ printf("Indicator variables ind1 and ind2 must hold negative values indicating that server returned NULL values this time.\n");
+ printf("\nhv1 = %d ind1 = %d, hv2 = %d ind2 = %d\n", hv1, ind1, hv2, ind2);
+
+ /* Reset the value of hv1 and hv2 before calling stored procedure sp2. */
+ hv1 = 30;
+ hv2 = 40;
+
+ /* Call stored procedure2 and print it's output. */
+ exec sql call sp2(:hv1, :hv2) into :hv2;
+
+ printf("\nStored procedure2 output: hv2 = %d\n", hv2);
+
+ exec sql rollback;
+
+ exec sql disconnect;
+
+ return 0;
+}
