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.

<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