Thread: Support for CALL statement in ecpg

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

Hello.

At Thu, 18 Jul 2019 16:38:54 +0530, Ashutosh Sharma <ashu.coek88@gmail.com> wrote in
<CAE9k0PkKCsbZLurU5O5V3+c1F-ANKFoKpzpMUa6LQFP9+dcJFA@mail.gmail.com>
> 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.

+ECPG: CallStmtCALLfunc_application

 Even though it is the default behavior, but as a written rule
 this needs the postfix "block".

+    $$ = 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.

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.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

Hi,

Thanks for the review. Please find my comments in-line.

On Fri, Jul 19, 2019 at 8:33 AM Kyotaro Horiguchi
<horikyota.ntt@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

I don't find this patch in any commit fest.  Seems like a good addition.
-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services