Re: [INTERFACES] esql\c documentation - Mailing list pgsql-interfaces

On 16-Jun-99 Michael Meskes wrote:
> On Wed, Jun 16, 1999 at 02:18:13PM +0000, Thomas Lockhart wrote:
>> I'd suggest *not* adding this non-SQL92 feature. afaik it adds no
>> functionality, so why bother messing with it? btw, OpenIngres1.1 did
>> not have this syntax either.
> 
> I agree. My only reason for maybe adding it was that I wasn't sure about
> SQL92. But if it definitely is not in SQL92 I think we shouldn't bother. But
> then we have some other compatibility hacks too.
       Informix can do this either way, according to their documentation.
I'd say it's not that useful, either. There are other things that I'd 
prefer were done for compatibility with ESQL/C before this one. I can't
find my notes on the subject, so I'll post a list later of things I've 
found that are incompatible between the two. Some are easy to fix, some
are probably not. 
       While we're on the subject of cursors and FETCH, Informix does
cursors rather differently from the way PostgreSQL does. There are three
different types defined: non-scroll, SCROLL (SELECT), and FOR UPDATE. The
FETCH statement has no IN/OUT clause, since ESQL/C seems to be able to
remember what type of cursor each one is. What makes this a porting 
challenge is that the ESQL/C parser will not accept and IN/OUT clause if
it is there. SCROLL cursors, OTOH, have a position clause, which 
controls the direction the cursor is moved for the next fetch. Valid
values in this field included FIRST/LAST/CURRENT/ABSOLUTE #/NEXT/PRIOR.
This is why I was saying there are lots of incompatibilities in this area.
Since Informix' is the only other embedded SQL I've worked with, I don't
know how common this syntax is.
       Here the syntax rules of each:

FETCH [position] <cursor id> [INTO variables]
DECLARE <cursor id> SCROLL CURSOR [WITH HOLD] FOR <select statement>
DECLARE <cursor id> CURSOR FOR <select statement>
DECLARE <cursor id> CURSOR FOR <select statement> FOR UPDATE [OF <col. list>]
       Non-scrolling cursors can also be used for inserts. I've looked
at the keyword list in the 6.4.2 ECPG source, and did not see a few of
the keywords I've mentioned, including FIRST and LAST.
       Informix also requires a FREE statement be used for each cursor.
This is done right after the CLOSE <cursor id> statement. Its syntax:

FREE <cursor id>
       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.
There are also ways to avoid freeing cursors, if necessary. The easiest
is to declare it the first time the module is called, then just keep
track of the fact that you've already declared it (using a flag, 
presumably). How well would that work in ECPG? [BTW, ESQL/C does flag
the FREE <cursor id> statement as being non-compliant with ANSI SQL.]
       I think that the best thing you guys could do for the sake
of compatibility issues like this is to get all the EXEC SQL preprocessor
statements implemented, especially the IF, IFDEF, IFNDEF, ELSE,
and ELSEIF statements, and allow simple arithmetic expressions in macros.
Then I could do something of the form:

EXEC SQL IFDEF INFORMIX       <Informix ESQL/C stuff>
EXEC SQL ELSEIF POSTGRESQL       <PostgreSQL ECPG stuff>
EXEC SQL ELSE       "Umm, don't you want to define a database type?"
EXEC SQL ENDIF
       By "simple arithmetic expressions in macros", I mean something
like this:

EXEC SQL BEGIN DECLARE SECTION ;  char field_a[FIELD_A_LEN+1] ;
EXEC SQL END DECLARE SECTION ;
       Otherwise, I have to define a field length macro and a string
length macro, with obvious code maintenance problems resulting.
       Given that the basic designs of Informix and PostgreSQL are
different, I think there are always going to be some incompatilibilities,
and this is the best way I can think of to resolve some of them. Since
they'd also be handy for general purposes, I'd think this would be a
better way for you to help us with these kinds of issues.
       Sorry for rattling on, but this seems like a complex subject
to me.

----------------------------------
Date: 16-Jun-99  Time: 12:58:40

Craig Orsinger                  (email: <orsingerc@epg.lewis.army.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
----------------------------------