Re: Problems with variable cursorname in ecpg

Hi,

Michael Meskes írta:
> Hi,
>
> I did some more testing on ecpg and found that allowing variables as cursor
> names seems to produce more problems than I anticipated. But then maybe it's
> just some missing checks to throw out error messages. Anyway, I attach a small
> test program that, from my understanding, should work, but dosn't. Could
> somebody with access to embedded SQL precompilers from other DBMSes please try
> if this test case works with them?
>

I have modified your code a little to be able to compile with ESQL/C.
Attached both the embedded SQL and the processed source.

> The problem we seem to have right now comes from the original logic in ecpg
> moving the declare cursor statement to the position of the open cursor
> statemend at compile time. With the cursor name being unique this never has
> been a problem. However, with variables as cursor names, this uniqueness need
> not hold anymore. If it does, i.e. each cursor gets its own variable, all is
> well, but if not, it doesn't work correctly at all times.
>

This was what I found some time ago when the same issue, i.e.
two DECLAREs for the same cursor name in IF/ELSE appeared
for different queries:

The standard says (SQL:2008, section 14.1 <declare cursor>,
Syntax Rules):

"
Syntax Rules
1) If a <declare cursor> is contained in an
<SQL-client module definition> M, then:
a) The <cursor name> shall not be equivalent to the
<cursor name> of any other <declare cursor> or
<dynamic declare cursor> in M.
b) The scope of the <cursor name> is M with the exception
of any <SQL schema statement> contained in M.
c) Any <host parameter name> contained in the <cursor specification>
shall be defined in a <host parameter declaration> in the
<externally-invoked procedure> that contains an <open statement>
that specifies the <cursor name> and is contained in the scope of
that <cursor name>.
"

The standard text doesn't say a word about DECLARE has to imply
a function call, ESQL/C does call a function, ECPG doesn't.
Also, in the same section, under General Rules:

"
General Rules
1) A cursor declaration descriptor CDD is created. CDD includes
indications that:
a) The kind of cursor is a standing cursor.
b) The provenance of the cursor is an indication of the SQL-client
module whose <SQL-client module definition> contains the
<declare cursor>.
c) The name of the cursor is the <cursor name>.
d) The cursor's origin is the <cursor specification> contained in
the <declare cursor>.
e) The cursor's declared properties are as determined by the
<cursor properties>.
"

This says "A cursor declaration descriptor CDD is created." - it doesn't
say where, and ECPG treats it as internal descriptor (as opposed to
a runtime descriptor in the processed C code as done by ESQL/C)
and currently it uses it to enforce the rules about cursors in embedded
SQL programs, like (in section 21.1 <embedded SQL host program>):

"
14) A <declare cursor> that is contained in an <embedded SQL host program>
shall precede in the text of that <embedded SQL host program> any
SQL-statement that references the <cursor name> of the <declare cursor>.

15) A <dynamic declare cursor> that is contained in an
<embedded SQL host program> shall precede in the text of that
<embedded SQL host program> any SQL-statement that references the
<cursor name> of the <dynamic declare cursor>.
"

and the paragraph 1)a) cited above in "Syntax Rules" of section 14.1.

The above was described as "the DECLARE statement is declarative"
by You, the ECPG maintainer when I fixed another bug that was reported
by our client. If you remember, the bug was that Informix resets
SQLCA upon executing DECLARE, and PostgreSQL didn't do it because
DECLARE wasn't calling any function, it only have set up the
internal descriptor for the cursor. You accepted a fix for this
for the Informix compatible mode of ECPG but not for the native mode,
to keep the declarative nature of DECLARE. But this didn't change
the fact that DECLARE still doesn't involve any function call that
uses name of the cursor.

The interpretation of the standard in the above way (DECLARE is declarative,
not functional) leads to the situation where the ECPG transformation cannot
know the cursor's real name during runtime (ECPG is not a VM after all),
only at transformation time. This means that with a dynamic cursorname
the only thing it can check and match is the ":variablename" cursorname
so OPEN, FETCH and CLOSE will all mandatorily have to use the same
variable as was used in the DECLARE statement.

BTW, the declarative nature of the DECLARE statement means that
it (a DECLARE statement) can appear outside of any functions
in ECPG's native mode and ESQL/C's failure in (or interpretation of)
conforming to the standard treats it as an error.

The uniqueness problem can only be solved with modifying
the runtime library to keep track of the cursor names in the client.
It would ruin the declarative nature of DECLARE but would increase
compatibility with Informix, and we would also need to implement
correct "FREE cursorname" behaviour, too. Which would also bring
the consequence that the ECPG client library would need to
forbid cursors and prepared statements with the same name as
"FREE" can also free cursors and prepared statements.

But there's a workaround that is usable under ECPG.
One of the cursors can be put into a different source file,
and different statements for cursors (DECLARE, OPEN, FETCH,
CLOSE) can now be put into different functions. You can
even have different cursornames passed into the same
DECLARE using different statements with different number of
input parameters and different output structure and have
it all work using named SQL or SQLDA descriptors.

I think the current behaviour is the best we could achieve
while keeping close standard conformance.

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

> BTW I can modify the test case so it works fine, but ecpg will still throw an
> error message, which is not a good situation either.
>
> Michael
>
> ------------------------------------------------------------------------
>
>

--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics

----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/