Thread: SQL statement PREPARE does not work in ECPG

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

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

> 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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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:meskes@postgresql.org]
> Sent: Friday, March 1, 2019 8:42 PM
> To: Matsumura, Ryo/松村 量 <matsumura.ryo@jp.fujitsu.com>; Takahashi,
> Ryohei/高橋 良平 <r.takahashi_2@jp.fujitsu.com>;
> 'pgsql-hackers@postgresql.org' <pgsql-hackers@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
> 

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

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
thenumber of parameters in EXECUTE statement as zero.
 
I don't have any other idea...


Regards
Ryo Matsumura

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

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

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

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

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

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

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

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

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

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

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

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

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

Michael Meskes <meskes@postgresql.org> 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

> 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

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

> 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

Michael Paquier <michael@paquier.xyz> 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

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