Hi All,
In the current code for ecpg, we can't use CALL statement to call
stored procedures. The attached patch adds the support for it.
With the attached patch, we can now have the following SQL statement
in ecpg application to call the stored procedures with IN or INOUT
params.
EXEC SQL CALL SP1(:hv1, :hv2);
Additionally, we can also use indicator variables along with the
arguments of stored procedure with CALL statement like shown below:
EXEC SQL CALL SP1(:hv1 :ind1, :hv2, :ind2);
The patch also adds some basic test-cases to verify if CALL statement
in ecpg can be used to call stored procedures with different type of
parameters.
Please have a look and let me know your thoughts.
Thank you.
--
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 4e30375..fb24f23 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -555,3 +555,19 @@ ECPG: limit_clauseLIMITselect_limit_value','select_offset_value block
}
ECPG: SignedIconstIconst rule
| civar { $$ = $1; }
+ECPG: CallStmtCALLfunc_application
+ {
+ struct arguments *p = argsinsert;
+
+ $$ = cat_str(2,mm_strdup("call"),$2);
+
+ /*
+ * Copy input arguments to the result arguments list so that all the
+ * host variables gets treated as INOUT params.
+ */
+ while (p != NULL)
+ {
+ add_variable_to_tail(&argsresult, p->variable, p->indicator);
+ p = p->next;
+ }
+ }
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..480c1ee
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-call.c
@@ -0,0 +1,231 @@
+/* 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 procedure 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"
+
+
+ /* 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 41 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 41 "call.pgc"
+
+
+ printf("First call to stored procedure sp1 without indicator variables.\n\n");
+ printf("sp1 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_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_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 47 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 47 "call.pgc"
+
+
+ printf("\nSecond call to stored procedure sp1 using indicator variables.\n");
+ printf("Indicator variables ind1 and ind2 must hold negative values indicating that server returned NULL values this time.\n\n");
+ printf("sp1 output: hv1 = %d ind1 = %d, hv2 = %d ind2 = %d\n", hv1, ind1, hv2, ind2);
+
+ { ECPGtrans(__LINE__, NULL, "rollback");
+#line 53 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 53 "call.pgc"
+
+
+ { ECPGdisconnect(__LINE__, "CURRENT");
+#line 55 "call.pgc"
+
+if (sqlca.sqlcode < 0) sqlprint ( );}
+#line 55 "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..391a57e
--- /dev/null
+++ b/src/interfaces/ecpg/test/expected/sql-call.stdout
@@ -0,0 +1,8 @@
+First call to stored procedure sp1 without indicator variables.
+
+sp1 output: hv1 = 100, hv2 = 200
+
+Second call to stored procedure sp1 using indicator variables.
+Indicator variables ind1 and ind2 must hold negative values indicating that server returned NULL values this time.
+
+sp1 output: hv1 = 100 ind1 = -1, hv2 = 200 ind2 = -1
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..07dc22b
--- /dev/null
+++ b/src/interfaces/ecpg/test/sql/call.pgc
@@ -0,0 +1,58 @@
+#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 procedure 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;
+
+ /* Call stored procedure1 and print it's output. */
+ exec sql call sp1(:hv1, :hv2);
+
+ printf("First call to stored procedure sp1 without indicator variables.\n\n");
+ printf("sp1 output: hv1 = %d, hv2 = %d\n", hv1, hv2);
+
+ /* Call stored procedure1 with indicator variables and print it's output. */
+ exec sql call sp1(:hv1 :ind1, :hv2 :ind2);
+
+ printf("\nSecond call to stored procedure sp1 using indicator variables.\n");
+ printf("Indicator variables ind1 and ind2 must hold negative values indicating that server returned NULL values this time.\n\n");
+ printf("sp1 output: hv1 = %d ind1 = %d, hv2 = %d ind2 = %d\n", hv1, ind1, hv2, ind2);
+
+ exec sql rollback;
+
+ exec sql disconnect;
+
+ return 0;
+}
