RE: SQL statement PREPARE does not work in ECPG
Meskes-san > This looks good to me. It passes all my tests, too. > > There were two minor issues, the regression test did not run and gcc > complained about the indentation in ECPGprepare(). Both I easily fixed. Thank you so much ! > > (2) > > I found some bugs (two types). I didn't fix them and only avoid bison > > error. > > > > Type1. Bugs or intentional unsupported features. > > - EXPLAIN EXECUTE > > - CREATE TABLE AS with using clause > > ... > > Please send a patch. I'm on vacation and won't be able to spend time on > this for the next couple of weeks. I begin to fix it. It may spend a while (1 or 2 week). > > Type2. In multi-bytes encoding environment, a part of character of > > message is cut off. > > > > It may be very difficult to fix. I pretend I didn't see it for a > > while. > > ... > > Hmm, any suggestion? I think that it's better to import length_in_encoding() defined in backend/utils/mb/mbutils.c into client side. Just an idea. Regards Ryo Matsumura
Re: SQL statement PREPARE does not work in ECPG
Michael Paquier writes: > This patch seems to have little incidence on the stability, but FWIW I > am not cool with the concept of asking for objections and commit a > patch only 4 hours after-the-fact, particularly after feature freeze. FWIW, I figured it was okay since ECPG has essentially no impact on the rest of the system. The motivation for having feature freeze is to get us to concentrate on stability, but any new bugs in ECPG aren't going to affect the stability of anything else. Also, I don't think it's that hard to look at this as a bug fix rather than a new feature. The general expectation is that ECPG can parse any command the backend can --- that's why we went to all the trouble of automatically building its grammar from the backend's. So I was surprised to hear that it didn't work on some EXECUTE variants, and filling in that gap doesn't seem like a "new feature" to me. Note the lack of any documentation additions in the patch. regards, tom lane
Re: SQL statement PREPARE does not work in ECPG
> This patch seems to have little incidence on the stability, but FWIW > I > am not cool with the concept of asking for objections and commit a > patch only 4 hours after-the-fact, particularly after feature freeze. This was only done to beat the pg_indent run as Tom pointed out. I figured worse case we can revert the patch if people object. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL signature.asc Description: This is a digitally signed message part
Re: SQL statement PREPARE does not work in ECPG
On Wed, May 22, 2019 at 05:10:14AM +0200, Michael Meskes wrote: > Thanks for the heads-up Tom, pushed. > > And thanks to Matsumura-san for the patch. This patch seems to have little incidence on the stability, but FWIW I am not cool with the concept of asking for objections and commit a patch only 4 hours after-the-fact, particularly after feature freeze. -- Michael signature.asc Description: PGP signature
Re: SQL statement PREPARE does not work in ECPG
> None here. You might want to get it in in the next 12 hours or so > so you don't have to rebase over a pgindent run. Thanks for the heads-up Tom, pushed. And thanks to Matsumura-san for the patch. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
Re: SQL statement PREPARE does not work in ECPG
Michael Meskes writes: > Unless somebody objects I will commit it as soon as I have time at > hand. Given that this patch also and mostly fixes some completely > broken old logic I'm tempted to do so despite us being pretty far in > the release cycle. Any objections? None here. You might want to get it in in the next 12 hours or so so you don't have to rebase over a pgindent run. regards, tom lane
Re: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san, > (1) > I attach a new patch. Please review it. > ... This looks good to me. It passes all my tests, too. There were two minor issues, the regression test did not run and gcc complained about the indentation in ECPGprepare(). Both I easily fixed. Unless somebody objects I will commit it as soon as I have time at hand. Given that this patch also and mostly fixes some completely broken old logic I'm tempted to do so despite us being pretty far in the release cycle. Any objections? > (2) > I found some bugs (two types). I didn't fix them and only avoid bison > error. > > Type1. Bugs or intentional unsupported features. > - EXPLAIN EXECUTE > - CREATE TABLE AS with using clause > ... Please send a patch. I'm on vacation and won't be able to spend time on this for the next couple of weeks. > Type2. In multi-bytes encoding environment, a part of character of > message is cut off. > > It may be very difficult to fix. I pretend I didn't see it for a > while. > ... Hmm, any suggestion? Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san
There are two points.
(1)
I attach a new patch. Please review it.
- Preproc replaces any prepared_name to "$0" and changes it to an
input-variable
for PREARE with typelist and EXECUTE with paramlist.
$0 is replaced in ecpg_build_params().
It's enable not to change ECPGdo interface.
- Host variables can be used in paramlist of EXECUTE.
(2)
I found some bugs (two types). I didn't fix them and only avoid bison error.
Type1. Bugs or intentional unsupported features.
- EXPLAIN EXECUTE
- CREATE TABLE AS with using clause
e.g.
EXPLAIN EXECUTE st; /* It has not been supported before.*/
ExplainableStmt:
ExecuteStmt
{
- $$ = $1;
+ $$ = $1.name;/* only work arround for bison error */
}
Type2. In multi-bytes encoding environment, a part of character of message is
cut off.
It may be very difficult to fix. I pretend I didn't see it for a while.
[ecpglib/error.c]
snprintf(sqlca->sqlerrm.sqlerrmc, sizeof(sqlca->sqlerrm.sqlerrmc), "%s on
line %d", message, line);
sqlca->sqlerrm.sqlerrml = strlen(sqlca->sqlerrm.sqlerrmc);
ecpg_log("raising sqlstate %.*s (sqlcode %ld): %s\n",
(int) sizeof(sqlca->sqlstate), sqlca->sqlstate, sqlca->sqlcode,
sqlca->sqlerrm.sqlerrmc);
Regards
Ryo Matsumura
ecpg_prepare_as_v1_2.patch
Description: ecpg_prepare_as_v1_2.patch
RE: SQL statement PREPARE does not work in ECPG
Meskes-san > This look very reasonable to me. I'm completely fine with this > restriction being placed on PREPARE FROM. Thank you. I start making a new patch. Regards Ryo Matsumura
Re: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san, > I defined the following specifications. Please review it. > > * ECPG can accept any valid PREPARE AS statement. > * A char-type host variable can be used as the statement name of > PREPARE AS, > but its value is constrained by the specification of PREPARE AS. > (e.g. the name cannot include double quotation.) > * The above also allows the following. It's a bit strange but there > is no reason > for forbidding. > prepare :st(type_list) as select $1 > * ECPG can accept EXECUTE statement with expression list that is > allocated > by both PREPARE FROM and PREPARE AS under the following > constraints: > - It must not include a using-clause. > - The statement name must follow to the specification of PREPARE > AS. This look very reasonable to me. I'm completely fine with this restriction being placed on PREPARE FROM. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi, Meskes-san I'm sorry for my long blank. I restarted. Review of previous discussion: I made a patch that makes ecpglib to send "PREPARE st(typelist) AS PreparableStmt" with PQexec(), because the type resolution is difficult. I tried to merge PREPARE FROM that uses PQprepare() to the PREPARE AS. Meskes-san pointed that there may be a problem that PREPARE FROM cannot use PQexec(). Now, I noticed a problem of the merging. Therefore, I will not change the existing implementation of PREPARE FROM. The problem is: Statement name of PREPARE FROM can include double quote, because the statement name is sent by PQprepare() directly and backend doesn't parse it. In other hand, the statement name of PREPARE AS cannot include double quote, because it is embedded into query and backend parser disallows it. This is a specification of PostgreSQL's PREPARE AS. I defined the following specifications. Please review it. * ECPG can accept any valid PREPARE AS statement. * A char-type host variable can be used as the statement name of PREPARE AS, but its value is constrained by the specification of PREPARE AS. (e.g. the name cannot include double quotation.) * The above also allows the following. It's a bit strange but there is no reason for forbidding. prepare :st(type_list) as select $1 * ECPG can accept EXECUTE statement with expression list that is allocated by both PREPARE FROM and PREPARE AS under the following constraints: - It must not include a using-clause. - The statement name must follow to the specification of PREPARE AS. Regards Ryo Matsumura
Re: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san, > If we also allow the statement name including white space in PREPRARE > AS, > we have to make backend parser to scan it as IDENT. Correct, without quoting would only work when using PQprepare() et al. > I cannot think of anything. > I may notice if I try to merge. Thanks. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Meskes-san I'm sorry for my slow reply. > I agree that we have to accept a quoted statement name and your > observations are correct of course, I am merely wondering if we need > the escaped quotes in the call to the ecpg functions or the libpq > functions. The following allows to use statement name including white space not double-quoted statement name. exec sql prepare "st1 x" from "select 1"; # I don't know whether the existing ECPG allows it intentionally or not. # In honestly, I think that it's not necessary to allow it. If we also allow the statement name including white space in PREPRARE AS, we have to make backend parser to scan it as IDENT. Double-quoting is one way. There may be another way. If we want to pass double-quoted statement name to backend through libpq, preprocessor have to escape it. > > I would prefer to merge as much as possible, as I am afraid that if > > we > > do not merge the approaches, we will run into issues later. There > > was a > > reason why we added PQprepare, but I do not remember it from the > > top of > > my head. Need to check when I'm online again. > > I will also consider it. I cannot think of anything. I may notice if I try to merge. Regards Ryo Matsumura
Re: SQL statement PREPARE does not work in ECPG
Matsumura-san, > Therefore, I think that the quoting statement name is needed in > PREPARE/EXECUTE case, too. I agree that we have to accept a quoted statement name and your observations are correct of course, I am merely wondering if we need the escaped quotes in the call to the ecpg functions or the libpq functions. > > I would prefer to merge as much as possible, as I am afraid that if > > we > > do not merge the approaches, we will run into issues later. There > > was a > > reason why we added PQprepare, but I do not remember it from the > > top of > > my head. Need to check when I'm online again. > > I will also consider it. Thank you. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Meskes-san Thank you for your comment. > One question though, why is the statement name always quoted? Do we > really need that? Seems to be more of a hassle than and advantage. The following can be accepted by preproc, ecpglib, libpq, and backend in previous versions. exec sql prepare "st x" from "select 1"; exec sql execute "st x"; The above was preprocessed to the following. PQprepare(conn, "\"st x\"", "select 1"); PQexec(conn, "\"st x\""); By the way, the following also can be accepted. PQexecParams(conn, "prepare \"st x\" ( int ) as select $1", 0, NULL, NULL, NULL, NULL, 0); PQexecParams(conn, "execute \"st x\"( 1 )", 0, NULL, NULL, NULL, NULL, 0); Therefore, I think that the quoting statement name is needed in PREPARE/EXECUTE case, too. > I would prefer to merge as much as possible, as I am afraid that if we > do not merge the approaches, we will run into issues later. There was a > reason why we added PQprepare, but I do not remember it from the top of > my head. Need to check when I'm online again. I will also consider it. Regards Ryo Matsumura
Re: SQL statement PREPARE does not work in ECPG
Hi all and thank you Matsumura-san. > Excuse: > It doesn't include regression tests and pass them. > Because I must reset all expected C program of regression. > # I add an argument to ECPGdo(). Sure, let's do this at the very end. > 1. Specification > It accepts the following .pgc. > I confirmed it works well for AT clause. > All results for st1 and st2 are same. I have a similar text case and can confirm that the output is the same for both ways of preparing the statement. > 2. Behavior of ecpglib > (1) PREPARE with AS clause > Ecpglib sends the PREPARE statement to backend as is. (using > PQexec). > > (2) EXECUTE with parameter list > Ecpglib sends the EXECUTE statement as is (using PQexec), but all > host variables in > the list are converted to string-formatted and embedded into the > EXECUTE statement. > > (3) PREPARE with FROM clause (not changed) > Ecpglib sends 'P' libpq-message with statement (using PQprepare). > > (4) EXECUTE without parameter list (not changed) > Ecpglib sends 'B' libpq-message with parameters. (using > PQexecPrepared). > > > 3. Change of preprocessor > > - I add ECPGst_prepare and ECPGst_execnormal. >ECPGst_prepare is only for (1) and ECPGst_execnormal is only for > (2). ># I think the names are not good. > > - I add one argument to ECPGdo().p It's for prepared statement name. One question though, why is the statement name always quoted? Do we really need that? Seems to be more of a hassle than and advantage. > 4. > I wonder whether I should merge (3) to (1) and (4) to (4) or not. I would prefer to merge as much as possible, as I am afraid that if we do not merge the approaches, we will run into issues later. There was a reason why we added PQprepare, but I do not remember it from the top of my head. Need to check when I'm online again. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san cc: Takahashi-san, Kuroda-san, Ideriha-san I attach a new patch. Please review it. Excuse: It doesn't include regression tests and pass them. Because I must reset all expected C program of regression. # I add an argument to ECPGdo(). I explain the patch as follows: 1. Specification It accepts the following .pgc. I confirmed it works well for AT clause. All results for st1 and st2 are same. exec sql prepare st0 as select 1; exec sql prepare st1(int,int) as select $1 + 5 + $2; exec sql prepare st2 from "select ? + 5 + ?"; exec sql prepare st3(bytea) as select octet_length($1); exec sql execute st0 into :ovar; exec sql execute st1(:var1,:var2) into :ovar; exec sql execute st1(11, :var2) into :ovar; exec sql execute st2(:var1,:var2) into :ovar; exec sql execute st2(11, :var2) into :ovar; exec sql execute st1 into :ovar using :var1,:var2; exec sql execute st2 into :ovar using :var1,:var2; exec sql execute st3(:b) into :ovar; 2. Behavior of ecpglib (1) PREPARE with AS clause Ecpglib sends the PREPARE statement to backend as is. (using PQexec). (2) EXECUTE with parameter list Ecpglib sends the EXECUTE statement as is (using PQexec), but all host variables in the list are converted to string-formatted and embedded into the EXECUTE statement. (3) PREPARE with FROM clause (not changed) Ecpglib sends 'P' libpq-message with statement (using PQprepare). (4) EXECUTE without parameter list (not changed) Ecpglib sends 'B' libpq-message with parameters. (using PQexecPrepared). 3. Change of preprocessor - I add ECPGst_prepare and ECPGst_execnormal. ECPGst_prepare is only for (1) and ECPGst_execnormal is only for (2). # I think the names are not good. - I add one argument to ECPGdo(). It's for prepared statement name. 4. I wonder whether I should merge (3) to (1) and (4) to (4) or not. Regards Ryo Matsumura ecpg_prepare_as_v1_1.patch Description: ecpg_prepare_as_v1_1.patch
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san Thank you for your comment. I write three points in this mail. 1. > This also seems to be conflicting with > bd7c95f0c1a38becffceb3ea7234d57167f6d4bf. If we want to keep this > commit in for the release, I think we need to get these things fixed. I understand it. My idea is that add an argument for statement-name to ECPGdo() or add a new function that is a wrapper of ECPGdo() and has the argument. The argument is used for lookup related connection. Is it good? 2. > I wrote: > But it doesn't allow to use host variable in parameter clause of EXECUTE > statement like the following. I found a way to support host variables in parameter list of EXECUTE statement. ecpg_build_params() replace each parameter to string-formatted data that can be created by ecpg_store_input(). I will try it. 3. I found a bug in my patch. Replacing $ to @ in AS clause is not good because @ is absolute value operator. Therefore, the replacing cannot accept valid statement like the following. exec sql prepare st(int) select $1 + @1; -- It equals to "select $1 + 1" I choose $$1 unavoidably. Other character seems to be used as any operator. P.S. - PREPARE with FROM is the standard for Embedded SQL. - PREPARE with AS is not defined in the standard. - PREPARE with AS clause is PostgreSQL style. - Oracle and MySQL support only the standard. Regards Ryo Matsumura
Re: SQL statement PREPARE does not work in ECPG
Hi all, > ... > But it doesn't allow to use host variable in parameter clause of > EXECUTE statement like the following. > I'm afraid that it's not usefull. I will research the standard and > other RDBMS. > If you have some information, please adivise to me. This also seems to be conflicting with bd7c95f0c1a38becffceb3ea7234d57167f6d4bf. If we want to keep this commit in for the release, I think we need to get these things fixed. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san
Thank you for your advice.
I attach a patch.
I didn't add additional tests to regression yet.
The patch allows the following:
exec sql prepare(int) as select $1;
exec sql execute st(1) into :out;
exec sql prepare(text, text) as select $1 || $2;
exec sql execute st('aaa', 'bbb') into :out;
But it doesn't allow to use host variable in parameter clause of EXECUTE
statement like the following.
I'm afraid that it's not usefull. I will research the standard and other RDBMS.
If you have some information, please adivise to me.
exec sql begin declare section;
int var;
exec sql end declare section;
exec sql prepare(int) as select $1;
exec sql execute st(:var) into :out;
SQL error: bind message supplies 1 parameters, but prepared statement ""
requires 0
I explain about the patch.
* PREPARE FROM or PREPARE AS without type clause
It uses PQprepare(). It's not changed.
[Preprocessor output]
/* exec sql prepare st from "select ?"; */
{ ECPGprepare(__LINE__, NULL, 0, "st", "select ?");
/* exec sql prepare st as select 1; */
{ ECPGprepare(__LINE__, NULL, 0, "st", " select 1 ");
* PREPARE AS with type clause
It doesn't use PQprepare() but uses PQexecuteParams().
[Preprocessor output]
/* exec sql prepare st(text, text) as select $1 || '@2'; */
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"st\" ( text ,
text ) as select @1 || '@2'", ECPGt_EOIT, ECPGt_EORT);
$1 in as clause is replaced by preprocessor at ecpg_param rule.
@1 is replaced to $1 by ecpglib at end of ecpg_build_params().
* EXECUTE without type clause
It uses PQexecPrepared(). It's not changed.
[Preprocessor output]
/* exec sql execute st into :ovar using :var; */
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "st",
ECPGt_int,&(var),(long)1,.
* EXECUTE with parameter clause
It uses PQexecuteParams().
[Preprocessor output]
/* exec sql execute st('abcde') into :ovar_s; */
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "execute \"st\" ( 'abcde'
)", ECPGt_EOIT,
.
This approach causes the above constraint that users cannot use host variables
in parameter clause in EXECUTE statement
because ecpglib sends 'P' message with "execute \"st\" ($1)" and sends 'B' one
parameter, but backend always regards the number of parameters in EXECUTE
statement as zero.
I don't have any other idea...
Regards
Ryo Matsumura
ecpg_prepare_as_v1_0.patch
Description: ecpg_prepare_as_v1_0.patch
Re: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san,
> I must use a midrule action like the following that works as
> expected.
> I wonder how to write the replacement to ecpg.addons.
> I think it's impossible, right? Please give me some advice.
You are right, for this change you have to replace the whole rule. This
cannot be done with an addon. Please see the attached for a way to do
this, untested though.
Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer
index 1d58c778d9..be6c9d0ae9 100644
--- a/src/interfaces/ecpg/preproc/ecpg.trailer
+++ b/src/interfaces/ecpg/preproc/ecpg.trailer
@@ -1,5 +1,26 @@
/* src/interfaces/ecpg/preproc/ecpg.trailer */
+PrepareStmt: PREPARE prepared_name prep_type_clause AS
+{
+prepared_name = $2;
+prepare_type_clause = $3;
+is_in_preparable_stmt = true;
+}
+PreparableStmt
+{
+$$.name = prepared_name;
+$$.type = prepare_type_clause;
+$$.stmt = $6;
+is_in_preparable_stmt = false;
+}
+| PREPARE prepared_name FROM execstring
+ {
+$$.name = $2;
+$$.type = NULL;
+$$.stmt = $4;
+}
+ ;
+
statements: /*EMPTY*/
| statements statement
;
diff --git a/src/interfaces/ecpg/preproc/ecpg.type b/src/interfaces/ecpg/preproc/ecpg.type
index 519b737dde..1c68095274 100644
--- a/src/interfaces/ecpg/preproc/ecpg.type
+++ b/src/interfaces/ecpg/preproc/ecpg.type
@@ -145,3 +145,5 @@
%type var_type
%type action
+
+%type PrepareStmt
diff --git a/src/interfaces/ecpg/preproc/parse.pl b/src/interfaces/ecpg/preproc/parse.pl
index 6f67a5e942..cafca22f7a 100644
--- a/src/interfaces/ecpg/preproc/parse.pl
+++ b/src/interfaces/ecpg/preproc/parse.pl
@@ -64,6 +64,7 @@ my %replace_types = (
'stmtblock' => 'ignore',
'stmtmulti' => 'ignore',
'CreateAsStmt' => 'ignore',
+ 'PrepareStmt' => 'ignore',
'DeallocateStmt' => 'ignore',
'ColId' => 'ignore',
'type_function_name' => 'ignore',
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san
I must use a midrule action like the following that works as expected.
I wonder how to write the replacement to ecpg.addons.
I think it's impossible, right? Please give me some advice.
PrepareStmt:
PREPARE prepared_name prep_type_clause AS
{
prepared_name = $2;
prepare_type_clause = $3;
is_in_preparable_stmt = true;
}
PreparableStmt
{
$$.name = prepared_name;
$$.type = prepare_type_clause;
$$.stmt = $6;
is_in_preparable_stmt = false;
}
| PREPARE prepared_name FROM execstring
Regards
Ryo Matsumura
> -Original Message-
> From: Michael Meskes [mailto:mes...@postgresql.org]
> Sent: Friday, March 1, 2019 8:42 PM
> To: Matsumura, Ryo/松村 量 ; Takahashi,
> Ryohei/高橋 良平 ;
> 'pgsql-hack...@postgresql.org'
> Subject: Re: SQL statement PREPARE does not work in ECPG
>
> Hi Matsumura-san,
>
> > I will read README.parser, ecpg.addons, and *.pl to understand.
>
> Feel free to ask, when anything comes up.
>
> Michael
> --
> Michael Meskes
> Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
> Meskes at (Debian|Postgresql) dot Org
> Jabber: michael at xmpp dot meskes dot org
> VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
>
Re: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san, > I will read README.parser, ecpg.addons, and *.pl to understand. Feel free to ask, when anything comes up. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Meskes-san Thank you for your comment. > The only way to add this is by creating a replacement production for > this rule. parse.pl cannot do it itself. I will read README.parser, ecpg.addons, and *.pl to understand. > > I will use @1 instend of $$1 because the replacing is almost same as > > the existing replacing function in ecpglib. > > Is it good? > > I'd say so. I try it. Regards Ryo Matsumura
Re: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san,
> But I will probably be late because I don't understand parse.pl very
> well.
> I think that the following rule is made by parse.pl.
>
>PreparableStmt:
>SelectStmt
>{
>is_in_preparable_stmt = true; <--- I want to add it.
>$$ = $1;
> }
> | InsertStmt
> .
The only way to add this is by creating a replacement production for
this rule. parse.pl cannot do it itself.
> I will use @1 instend of $$1 because the replacing is almost same as
> the existing replacing function in ecpglib.
> Is it good?
I'd say so.
Thanks.
Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san, Takahashi-san
> If the standard allows it, we want to be able to process it.
I will try to implement it with the Idea-2 that doesn't use PQprepare() and
Takahasi-san's following idea.
> For example,
> - ECPG convert ":ID" to "$1" and "$1" in the original statement to "$$1"
> - next_insert() do not check "$$1"
> - ECPGdo() reconvert "$$1" to "$1"
But I will probably be late because I don't understand parse.pl very well.
I think that the following rule is made by parse.pl.
PreparableStmt:
SelectStmt
{
is_in_preparable_stmt = true; <--- I want to add it.
$$ = $1;
}
| InsertStmt
.
The above variable is used in ecpg.trailer.
ecpg_param: PARAM {
if(is_in_preparable_stmt)
$$ = mm_strdup(replace_dollar_to_something());
else
$$ = make_name();
} ;
I will use @1 instend of $$1 because the replacing is almost same as the
existing replacing function in ecpglib.
Is it good?
Regards
Ryo Matsumura
RE: SQL statement PREPARE does not work in ECPG
Hi Matsumura-san, Thank you for your explaining. > An important point of the route is that it calls PQprepare() and PQprepare() > needs type-Oid list. (Idea-1) If we fix for Prepare statement with AS clause > and > with parameter list to walk through the route, preprocessor must parse the > parameter list and > preprocessor or ecpglib must make type-Oid list. I think it's difficult. > Especially, I wonder if it can treat user defined type and complex structure > type. I agree. In the case of standard types, ECPG can get oids from pg_type.h. However, in the case of user defined types, ECPG needs to access pg_type table and it is overhead. Therefore, the second idea seems better. By the way, should we support prepare statement like following? (I think yes.) EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = :ID or id =$1; Current ECPG produces following code. ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int ) as \" select id from test_table where id = $1 or id = $1 \"", ECPGt_int,&(ID),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT); In this case, both ":ID" and "$1" in the original statement are converted to "$1" and ECPGdo() cannot distinguish them. Therefore, ECPG should produce different code. For example, - ECPG convert ":ID" to "$1" and "$1" in the original statement to "$$1" - next_insert() do not check "$$1" - ECPGdo() reconvert "$$1" to "$1" Regards, Ryohei Takahashi
RE: SQL statement PREPARE does not work in ECPG
Meskes-san
I made mistake.
> The checking (line-1495) is meaningless for AS clause.
> It checks if all $0 is replaced to literal and all ? is replaced to $[0-9]*
> by insert_tobeinserted(),
> but it always fails because $[0-9]* in AS clause are not replaced (and should
> not be replaced).
> I don't search if there is other similar case. It is Idea-2.
It checks if a number of variables equals a number of $* after replacing $0 and
?.
It always fails because there is no variable for $* in AS clause.
We should skip AS clause at the cheking.
Umm... The skipping seems to be not easy too.
next_insert(char *text, int pos, bool questionmarks, bool std_strings)
{
pos = get_pos_of_as_clause(text); <-- parse text in ecpglib???
for (; text[p] != '\0'; p++)
if(is_prepare_statement(stmt) && invalid_pos(pos))
break;
Regards
Ryo Matsumura
RE: SQL statement PREPARE does not work in ECPG
Meskes-san
> Did you analyze the bug? Do you know where it comes from?
At first, I show the flow of Prepare statement without AS clause and
the flow of Prepare statement with AS clause but without parameter list.
[preproc/preproc.y]
1832 | PrepareStmt
1834 if ($1.type == NULL || strlen($1.type) == 0)
1835 output_prepare_statement($1.name, $1.stmt);
[preproc/output.c]
168 output_prepare_statement(char *name, char *stmt)
169 {
170 fprintf(base_yyout, "{ ECPGprepare(__LINE__, %s, %d, ", connection ?
connection : "NULL", questionmarks);
171 output_escaped_str(name, true);
172 fputs(", ", base_yyout);
173 output_escaped_str(stmt, true);
174 fputs(");", base_yyout);
It makes the following C-program and it can work.
/* exec sql prepare st as select 1; */
ECPGprepare(__LINE__, NULL, 0, "st", " select 1 ");
/* exec sql prepare st from "select 1"; */
ECPGprepare(__LINE__, NULL, 0, "st", "select 1");
/* exec sql prepare st from "select ?"; */
ECPGprepare(__LINE__, NULL, 0, "st", "select ?");
ecpglib processes as the following:
[ecpglib/prepare.c]
174 ECPGprepare(int lineno, const char *connection_name, const bool
questionmarks,
175 const char *name, const char *variable)
199 this = ecpg_find_prepared_statement(name, con, );
200 if (this && !deallocate_one(lineno, ECPG_COMPAT_PGSQL, con, prev, this))
201 return false;
203 return prepare_common(lineno, con, name, variable);
[ecpglib/prepare.c]
115 prepare_common(int lineno, struct connection *con, const char *name, const
char *variable)
135 stmt->lineno = lineno;
136 stmt->connection = con;
137 stmt->command = ecpg_strdup(variable, lineno);
138 stmt->inlist = stmt->outlist = NULL;
141 replace_variables(&(stmt->command), lineno);
144 this->name = ecpg_strdup(name, lineno);
145 this->stmt = stmt;
148 query = PQprepare(stmt->connection->connection, name, stmt->command, 0,
NULL);
The following is log of PQtrace().
To backend> Msg P
To backend> "st"
To backend> "select $1"
To backend (2#)> 0
[6215]: prepare_common on line 21: name st; query: "select $1"
An important point of the route is that it calls PQprepare() and PQprepare()
needs type-Oid list. (Idea-1) If we fix for Prepare statement with AS clause and
with parameter list to walk through the route, preprocessor must parse the
parameter list and
preprocessor or ecpglib must make type-Oid list. I think it's difficult.
Especially, I wonder if it can treat user defined type and complex structure
type.
At second, I show the flow of Prepare statement with AS clause.
1836 else
1837 output_statement(cat_str(5, mm_strdup("prepare"), $1.name, $1.type,
mm_strdup("as"), $1.stmt), 0, ECPGst_normal);
It makes the following C-program, but it cannot work because AS clause is
double quoted.
So there is no work-around for this route.
/* exec sql prepare st(int) as select $1; */
ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"st\" ( int ) as \"
select $1 \"", ECPGt_EOIT, ECPGt_EORT);
When it runs, the following error is occured.
[5895]: raising sqlcode -202 on line 20: too few arguments on line 20
SQL error: too few arguments on line 20
The following may be expected.
ECPGdo(__LINE__, 0 , 1, NULL, 0, ECPGst_normal, "prepare st ( int ) as select
$1 ", ECPGt_EOIT, ECPGt_EORT);
Even if the above C-program is made, another error is occured.
The error is occured in the following flow.
[ecpglib/execute.c]
1196 ecpg_build_params(struct statement *stmt)
1214 var = stmt->inlist;
1215 while (var)
ecpg_store_input(var--->tobeinserted)
1393 if ((position = next_insert(stmt->command, position,
stmt->questionmarks, std_strings) + 1) == 0)
1411 if (var->type == ECPGt_char_variable)
1413 int ph_len = (stmt->command[position] == '?') ?
strlen("?") : strlen("$1");
1415 if (!insert_tobeinserted(position, ph_len, stmt, tobeinserted))
1428 else if (stmt->command[position] == '0')
1430 if (!insert_tobeinserted(position, 2, stmt, tobeinserted))
1437 else
1468 if (stmt->command[position] == '?')
1480 snprintf(tobeinserted, buffersize, "$%d", counter++);
1474 if (!(tobeinserted = (char *) ecpg_alloc(buffersize,
stmt->lineno)))
1492 var = var->next;
1493 }
1495 /* Check if there are unmatched things left. */
1496 if (next_insert(stmt->command, position, stmt->questionmarks,
std_strings) >= 0)
1497 {
1498 ecpg_raise(stmt->lineno, ECPG_TOO_FEW_ARGUMENTS,
1499ECPG_SQLSTATE_USING_CLAUSE_DOES_NOT_MATCH_PARAMETERS,
NULL);
*** The above is raised. ***
The checking (line-1495) is meaningless for AS clause.
It checks if all $0 is replaced to literal and all ? is replaced to $[0-9]* by
insert_tobeinserted(),
but it always fails because $[0-9]* in AS clause are not replaced (and
Re: SQL statement PREPARE does not work in ECPG
Takahashi-san, > It works well for my statement > > "EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where > id = $1;". > > However, since data type information is not used, it does not works > well > for prepare statements which need data type information such as > "EXEC SQL PREPARE test_prep (int, int) AS SELECT $1 + $2;". Yes, that was to be expected. This is what Matsumura-san was trying to fix. However, I'm not sure yet which of his ideas is the best. > It also works. > (Actually, I wrote "EXEC SQL EXECUTE test_prep (:i) INTO :ID;".) Ok, thanks. That means the parser has to handle the list of execute arguments differently, which in hindsight is pretty obvious. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san, > Ah right, my bad. The workaround should have been: Thank you. It works. > As for the PREPARE statement itself, could you try the attached small > patch please. It works well for my statement "EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;". However, since data type information is not used, it does not works well for prepare statements which need data type information such as "EXEC SQL PREPARE test_prep (int, int) AS SELECT $1 + $2;". It fails with "PostgreSQL error : -400[operator is not unique: unknown + unknown on line 20]". (Of course, "EXEC SQL PREPARE test_prep AS SELECT $1::int + $2::int;" works well.) > Could you please also verify for me if this works correctly if you use > a variable instead of the const? As in: > EXEC SQL BEGIN DECLARE SECTION; > int i=2; > EXEC SQL END DECLARE SECTION; > ... > EXEC SQL EXECUTE test_prep (:i); It also works. (Actually, I wrote "EXEC SQL EXECUTE test_prep (:i) INTO :ID;".) ECPG produced as follows. ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep", ECPGt_int,&(i),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_int,&(ID),(long)1,(long)1,sizeof(int), ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT); Regards, Ryohei Takahashi
Re: SQL statement PREPARE does not work in ECPG
Takahashi-san, > EXEC SQL EXECUTE test_prep (2); Could you please also verify for me if this works correctly if you use a variable instead of the const? As in: EXEC SQL BEGIN DECLARE SECTION; int i=2; EXEC SQL END DECLARE SECTION; ... EXEC SQL EXECUTE test_prep (:i); I guess the problem is that constants in this subtree are move to the output literally instead treated as parameters. Thanks. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
Re: SQL statement PREPARE does not work in ECPG
Takahashi-san,
> I tried as follows.
> ...
> Unfortunately, this does not work.
> ECPGst_execute seems good, but prepare statement is the same as my
> first post.
Ah right, my bad. The workaround should have been:
EXEC SQL PREPARE test_prep from "SELECT id from test_table where id =
$1";
EXEC SQL EXECUTE test_prep using 2;
> It fails with "PostgreSQL error : -202[too few arguments on line
> 16]".
>
> This error is caused by following source code.
> ...
> I think next_insert() should ignore "$n" in the case of SQL statement
> PREPARE.
Actually I am not so sure.
> In addition, we should fix following, right?
>
> (1)
> As Matsumura-san wrote, ECPG should not produce '"' for SQL statement
> PREPARE.
Why's that?
> (2)
> ECPG should produce argument for execute statement such as "EXEC SQL
> EXECUTE test_prep (2);"
Correct.
As for the PREPARE statement itself, could you try the attached small
patch please.
This seems to create the correct ECPGPrepare call, but I have not yet
tested it for many use cases.
Thanks.
Michael
--
Michael Meskes
Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
Meskes at (Debian|Postgresql) dot Org
Jabber: michael at xmpp dot meskes dot org
VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons
index e8052819b0..bfd76960a2 100644
--- a/src/interfaces/ecpg/preproc/ecpg.addons
+++ b/src/interfaces/ecpg/preproc/ecpg.addons
@@ -22,12 +22,7 @@ ECPG: stmtUpdateStmt block
ECPG: stmtExecuteStmt block
{ output_statement($1, 1, ECPGst_execute); }
ECPG: stmtPrepareStmt block
- {
- if ($1.type == NULL || strlen($1.type) == 0)
- output_prepare_statement($1.name, $1.stmt);
- else
- output_statement(cat_str(5, mm_strdup("prepare"), $1.name, $1.type, mm_strdup("as"), $1.stmt), 0, ECPGst_normal);
- }
+ { output_prepare_statement($1.name, $1.stmt); }
ECPG: stmtTransactionStmt block
{
fprintf(base_yyout, "{ ECPGtrans(__LINE__, %s, \"%s\");", connection ? connection : "NULL", $1);
Re: SQL statement PREPARE does not work in ECPG
Matsumura-san, > Maybe, there is no work-around. Did you analyze the bug? Do you know where it comes from? > For supporting it, there are two steps. Could you please start with explaining where you see the problem? I'm actually not sure what you are trying to do here. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi, Maybe, there is no work-around. For supporting it, there are two steps. step1. fix for PREPARE. step2. fix for EXECUTE. About step1, there are two way. I want to choose Idea-2. Idea-1. ecpglib prepares Oids of type listed in PREPARE statement for 5th argument of PQprepare(). But it's difficult because ecpg has to know Oids of type. # Just an idea, create an Oid list in parsing. Idea-2. Use ECPGdo with whole PREPARE statement. In this way, there is no problem about Oid type because backend resolves it. I think the current implementation may aim to it. If we choose Idea-2, we should make a valid SQL-command(remove double quotation) and avoid any processing about prep_type_clause and PreparableStmt except for parsing. One of such processing is the checking a number of parameters that occured the error. It may take time, but it's easier than Idea-1. Is the direction of fixing good? About step2, there is the work-arround pointed by Meskes-san. Regards Ryo Matsumura
RE: SQL statement PREPARE does not work in ECPG
Hi Meskes-san,
Thank you for your replying.
> Please try this instead:
>
> EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id
> = $1;
> EXEC SQL EXECUTE test_prep using 2;
>
> This should work.
I tried as follows.
EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;
EXEC SQL EXECUTE test_prep using 2;
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_normal, "prepare \"test_prep\" ( int
) as \" select id from test_table where id = $1 \"", ECPGt_EOIT, ECPGt_EORT);
#line 16 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 16 "test_app.pgc"
{ ECPGdo(__LINE__, 0, 1, NULL, 0, ECPGst_execute, "test_prep",
ECPGt_const,"2",(long)1,(long)1,strlen("2"),
ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT, ECPGt_EORT);
#line 17 "test_app.pgc"
if (sqlca.sqlcode < 0) error_exit ( );}
#line 17 "test_app.pgc"
Unfortunately, this does not work.
ECPGst_execute seems good, but prepare statement is the same as my first post.
It fails with "PostgreSQL error : -202[too few arguments on line 16]".
This error is caused by following source code.
/* Check if there are unmatched things left. */
if (next_insert(stmt->command, position, stmt->questionmarks,
std_strings) >= 0)
{
ecpg_raise(stmt->lineno, ECPG_TOO_FEW_ARGUMENTS,
ECPG_SQLSTATE_USING_CLAUSE_DOES_NOT_MATCH_PARAMETERS, NULL);
ecpg_free_params(stmt, false);
return false;
}
if (text[p] == '$' && isdigit((unsigned char) text[p +
1]))
{
/* this can be either a dollar quote or a
variable */
int i;
for (i = p + 1; isdigit((unsigned char)
text[i]); i++)
/* empty loop body */ ;
if (!isalpha((unsigned char) text[i]) &&
isascii((unsigned char) text[i])
&[i] != '_')
/* not dollar delimited quote */
return p;
}
I think next_insert() should ignore "$n" in the case of SQL statement PREPARE.
In addition, we should fix following, right?
(1)
As Matsumura-san wrote, ECPG should not produce '"' for SQL statement PREPARE.
(2)
ECPG should produce argument for execute statement such as "EXEC SQL EXECUTE
test_prep (2);"
Regards,
Ryohei Takahashi
Re: SQL statement PREPARE does not work in ECPG
> I think SQL statement PREPARE *without* parameter is supported, > but one with parameter is not supported (or has some fatal bugs). It surely should be supported. >> I wrote the source code as follows. >> >> >> EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1; >> EXEC SQL EXECUTE test_prep (2); >> Please try this instead: EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1; EXEC SQL EXECUTE test_prep using 2; This should work. And yes, it does look like a bug to me, or better like changes in the backend that were not synced to ecpg. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Meskes at (Debian|Postgresql) dot Org Jabber: michael at xmpp dot meskes dot org VfL Borussia! Força Barça! SF 49ers! Use Debian GNU/Linux, PostgreSQL
RE: SQL statement PREPARE does not work in ECPG
Hi
I think SQL statement PREPARE *without* parameter is supported,
but one with parameter is not supported (or has some fatal bugs).
Because route for SQL statement PREPARE (line-1837 of preproc.y) always has
output an invalid SQL statement and
there is no regression test for SQL statement PREPARE.
[preproc.y]
1832 | PrepareStmt
1833 {
1834 if ($1.type == NULL || strlen($1.type) == 0)
1835 output_prepare_statement($1.name, $1.stmt);
1836 else
1837 output_statement(cat_str(5, mm_strdup("prepare"), $1.name,
$1.type, mm_strdup("as"), $1.stmt), 0, ECPGst_normal);
1838 }
The next is log of ECPGdebug() and PQtrace() for the following statement.
exec sql prepare st(int) as select col1 from foo;
[14968]: ecpg_execute on line 17: query: prepare "st" ( int ) as " select 1 ";
with 0 parameter(s) on connection conn
To backend> Msg Q
To backend> "prepare "st" ( int ) as " select 1 ""
To backend> Msg complete, length 42
2019-02-19 06:23:30.429 UTC [14969] ERROR: syntax error at or near "" select 1
"" at character 25
2019-02-19 06:23:30.429 UTC [14969] STATEMENT: prepare "st" ( int ) as "
select 1 "
Regards
Ryo Matsumura
