Re: Re: ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname

Hi,

Michael Meskes írta:
>> 1. The statement
>>
>>     UPDATE table SET fld1 = :input1
>>     WHERE CURRENT OF :curname
>>     RETURNING id + :input2;
>>
>>     is transformed into
>>
>>     UPDATE table SET fld1 = $1
>>     WHERE CURRENT OF $0
>>     RETURNING id + $2;
>>
>>     and the $0 is past $1. The current code cannot deal with such
>>     a messed up order, and scanning the original query twice is
>>     needed, once for $0 substitution, once for mapping $1, etc. to
>>     the other input variables.
>>
>
> I cannot seem to reproduce this bug. Could you please send me an example that
> makes this reproducible? Yes, I know that I have to change preproc.y to allow
> for variable cursor names but in my test case everything seems to work well and
> $0 gets replaced by the cursor name.
>
> Michael
>

sorry for the late answer. Here is a minimal patch against the
current GIT tree, so the WHERE CURRENT OF accepts
dynamic cursornames,  plus the test case that shows the problem.
The problem is caused by line 25 in the attached source, the
UPDATE statement is processed into this code:

                if (sqlca.sqlcode != 0)
                        break;
                { ECPGdo(__LINE__, 1, 1, NULL, 0, ECPGst_prepnormal,
"update t1 set t = $1  where current of $0 returning id + $2 ",
        ECPGt_char,&(new_t),(long)0,(long)1,(1)*sizeof(char),
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
        ECPGt_char,&(curname),(long)0,(long)1,(1)*sizeof(char),
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L,
        ECPGt_int,&(one),(long)1,(long)1,sizeof(int),
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EOIT,
        ECPGt_int,&(id1),(long)1,(long)1,sizeof(int),
        ECPGt_NO_INDICATOR, NULL , 0L, 0L, 0L, ECPGt_EORT);
#line 25 "where-current-of.pgc"

if (sqlca.sqlcode < 0) sqlprint();}
#line 25 "where-current-of.pgc"

Running the program needs this table:

create table t1 (id serial primary key, t text);

and a few records in it. Result of running it:

$ ./where-current-of
SQL error: there is no parameter $0 on line 25
SQL error: current transaction is aborted, commands ignored until end of
transaction block on line 27
2 0 0 'x'
SQL error: current transaction is aborted, commands ignored until end of
transaction block on line 32

In the above code, the $1, $0, $2 order is correctly mirrored
in the order of the actual parameters.

The DELETE ... WHERE CURRENT OF ... RETURNING ...
grammar wouldn't cause such problem, $0 would be the first
in this case but what do you suggest solving for UPDATE?

Best regards,
Zoltán Böszörményi

--
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
     http://www.postgresql.at/