SQL statement PREPARE does not work in ECPG

classic Classic list List threaded Threaded
40 messages Options
12
Reply | Threaded
Open this post in threaded view
|

SQL statement PREPARE does not work in ECPG

Takahashi, Ryohei
Hi,


In the PostgreSQL Documentation, both ECPG PREPARE and SQL statement PREPARE can be used in ECPG [1].
However, SQL statement PREPARE does not work.

I wrote the source code as follows.

<test_app.pgc>
============================
  EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;
  EXEC SQL EXECUTE test_prep (2);
============================

PostgreSQL 11.2 ECPG produced following code.

<test_app.c>
============================
  { 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_EOIT, ECPGt_EORT);
#line 17 "test_app.pgc"

if (sqlca.sqlcode < 0) error_exit ( );}
#line 17 "test_app.pgc"
============================


There are following problems.

(1)
When I run this program, it failed with "PostgreSQL error : -202[too few arguments on line 16]".
The reason is ECPGdo has no argument though prepare statement has "$1".

(2)
I want to execute test_prep (2), but ECPGst_execute does not have argument.


Can SQL statement PREPARE be really used in ECPG?


[1] - https://www.postgresql.org/docs/11/ecpg-sql-prepare.html


Regards,
Ryohei Takahashi


Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Matsumura, Ryo
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

Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
> 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.
>> <test_app.pgc>
>> ============================
>> 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


Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Takahashi, Ryohei
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.

<test_app.pgc>
============================
  EXEC SQL PREPARE test_prep (int) AS SELECT id from test_table where id = $1;
  EXEC SQL EXECUTE test_prep using 2;
============================

<test_app.c>
============================
  { 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.


<execute.c ecpg_build_params()>
        /* 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;
        }

<execute.c next_insert()>
                        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]) &&text[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

Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Matsumura, Ryo
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
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


Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
In reply to this post by Takahashi, Ryohei
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

prepare.diff (1K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
In reply to this post by Takahashi, Ryohei
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


Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Takahashi, Ryohei
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

Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
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


Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Matsumura, Ryo
In reply to this post by Michael Meskes-3
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, &prev);
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,
1499                    ECPG_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 should not be replaced).
I don't search if there is other similar case. It is Idea-2.

What is ECPGt_char_variable?
[preproc.y]
   65 static struct ECPGtype ecpg_query = {ECPGt_char_variable, NULL, NULL, NULL, {NULL}, 0};
15333 ECPGCursorStmt:  DECLARE cursor_name cursor_options CURSOR opt_hold FOR prepared_name
15367             thisquery->type = &ecpg_query;
15381             add_variable_to_head(&(this->argsinsert), thisquery, &no_indicator);

What is $0?
  In ECPG, the followings can be specified by host variable.
  - cursor name
  - value of ALTER SYSTEM SET statement
    e.g. ALTER SYSTEM SET aaaa = $1
  - fetch counter
    e.g. FETCH ABSOLUTE count

  Basically, ECPG-preprocessor changes the host variables to $[0-9]* and adds
  variables to arguments of ECPGdo, and ecpglib calls PQexecParams(stmt, vars).
  In case of the above, they cannot be passed to vars of PQexecParams() because
  backend cannot accept them.
  So ecpg_build_params() replace $0 to literal.

Regards
Ryo Matsumura
Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Matsumura, Ryo
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
Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Takahashi, Ryohei
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


Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
Hi Takahashi-san

> 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.

The overhead wouldn't be too bad. In fact it's already done, at least
sometimes. Please check ecpg_is_type_an_array().

> By the way, should we support prepare statement like following?
> (I think yes.)

If the standard allows it, we want to be able to process it.

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

I agree. It seems that stuff really broke over the years and nobody
noticed, sigh.

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


Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Matsumura, Ryo
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
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


Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Matsumura, Ryo
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
Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
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


Reply | Threaded
Open this post in threaded view
|

RE: SQL statement PREPARE does not work in ECPG

Matsumura, Ryo
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:[hidden email]]
> Sent: Friday, March 1, 2019 8:42 PM
> To: Matsumura, Ryo/松村 量 <[hidden email]>; Takahashi,
> Ryohei/高橋 良平 <[hidden email]>;
> '[hidden email]' <[hidden email]>
> 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
>

Reply | Threaded
Open this post in threaded view
|

Re: SQL statement PREPARE does not work in ECPG

Michael Meskes-3
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

prep.diff (2K) Download Attachment
12