Re: [INTERFACES] esql\c documentation

On 17-Jun-99 Michael Meskes wrote:

>What do you mean with IN/OUT? Please try your syntax directly via psql to
>see whether this is an ECPG problem. DECLARE/FETCH commands are handled by
>the backend.

Here's how Informix ESQL/C does it:

EXEC SQL DECLARE CURSOR <cursor id> FOR <select statement> ;
EXEC SQL FETCH <cursor id> INTO <variable(s)> ;

Here's how ECPG does the same thing:

EXEC SQL DECLARE CURSOR <cursor id> FOR <select statemtent> ;
EXEC SQL FETCH IN <cursor id> INTO <variable(s)> ;

[In the discussion that follows, I refer to the versions
of ECPG by the PostgreSQL version they are included with.
Sorry, but I can't keep up with all these version numbers.]

It doesn't work (in 6.4.2 and earlier, at least) without that 'IN'.
In checking through the test source in the 6.5 version (directory
<source root>/src/interfaces/ecpg/test), I notice that the
'IN' is still included in all the FETCH statements. I don't know why I
mentioned 'OUT' as being another possible keyword, perhaps I assumed that
this was a direction clause. I can't find an example of a "FETCH OUT"
statement. As I mentioned before, Informix won't parse this statement if
'IN' is included in the statement. From my perspective, if the 'IN' was
made optional in ECPG, that would resolve this difference.

As you suggested, I checked the syntax help in psql for the
"FETCH" command. Here's the result:

Command: fetch
Description: retrieve tuples from a cursor
Syntax:
FETCH [FORWARD|BACKWARD] [number|ALL] [IN cursorname];

So it would appear that PostgreSQL allows a FETCH command without
a cursor, and the "IN" clause identifies a string as a cursor id ???
This is substantially different from the way cursors and fetches are
done in ESQL/C. For one thing, I can't find any way of doing more
than one row at a time (assuming that's what the "[number|ALL]" clause
is about).

BTW, I'm still using version 6.4.2. I just have the source
for 6.5 untarred in a source directory here.

>> EXEC SQL BEGIN DECLARE SECTION ;
>> char field_a[FIELD_A_LEN+1] ;
>> EXEC SQL END DECLARE SECTION ;
>
> Should work. You have to EXEC SQL DEFINE FIELD_A_LEN 1 before.

It didn't work on the 6.4.2 version. If it works in 6.5, great!

>> I've found that replacing the FREE <cursor id> with a COMMIT
>> statement works pretty well for me, since Informix offers the ability
>> to not use transactions - a feature my applications don't require.
>
>You can do the same with postgresql, i.e. each statement is its own
>transaction.

Hmmm. I don't explicitely enable transactions, but the only
way that something seems to get done is by having that COMMIT statement
at the end of the function. Do I have to turn off transaction processing
explicitely?

----------------------------------
Date: 17-Jun-99 Time: 10:35:48

Craig Orsinger (email: <orsingerc(at)epg(dot)lewis(dot)army(dot)mil>)
Logicon RDA
Bldg. 8B28 "Just another megalomaniac with ideas above his
6th & F Streets station. The Universe is full of them."
Ft. Lewis, WA 98433 - The Doctor
----------------------------------