On 14-Jun-99 Michael Meskes wrote:
> On Sun, Jun 13, 1999 at 07:24:52PM -0500, Brent Waldrop wrote:
>> Does anyone know where this is thorough esql\c documentation. I am trying to
> 
> Unfortunately there is no more than the man page and the sgml file
> distributed with PostgreSQL. I just didn't find the time to write more.
> 
>> compile previous examples i used with informix and they will not work for
>> example when i do
>> EXEC SQL declare democursor cursor for
>>  select fname,lname
>>  into :fname, :lname
>>  from employee
>> where lname < :oneline;
>> ---it bombs at the line with "into" in it saying there is a parse error
>> and when i do this EXEC SQL fetch democursor;
>> --it tells me that there is another parse error on this line
>> is there any better documenation for esql/c than whats available in the
>> programming manual so that i can figure out what is going on?
> 
> To the best of my knowledge this is incorrect syntax. For instance Oracle
> says:
> 
>    The SELECT statement associated with the cursor cannot include an INTO
>    clause. Rather, the INTO clause and list of output host variables are
>    part of the FETCH statement.
> 
> I cannot find the text in the standard right now, but there also only FETCH
> is listed with INTO.
> 
> Please correct me if I'm wrong.

        I don't know if this makes you wrong, but Informix supports
an INTO clause in a cursor declaration. It's one of the things I had to
change while porting some applications from Informix ESQL/C to ECPG.
Here's what Informix says about the INTO clause in their training
materials for ESQL/C:

        You can use an INTO clause in the SELECT or use it in the
        FETCH; you must use it in one or the other.

Right or wrong, it's definitely a part of their syntax. What's more, 
their parser does not flag this clause as being not compliant with ANSI
SQL, as some of their other improvisations are.

        This isn't a criticism BTW, just an observation. There are 
enough other differences between these two embedded SQL implementations
that anyone who supports both will have to keep separate source files
anyway (at least for anything that's not a trivial application). So
much for standards.


----------------------------------
Date: 15-Jun-99  Time: 10:45:53

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
----------------------------------

On Tue, Jun 15, 1999 at 11:19:24AM -0700, Craig Orsinger wrote:
>         I don't know if this makes you wrong, but Informix supports
> an INTO clause in a cursor declaration. It's one of the things I had to
> change while porting some applications from Informix ESQL/C to ECPG.
> Here's what Informix says about the INTO clause in their training
> materials for ESQL/C:

So you think we should add this? I have no idea at the moment if it causes
problems with the parser but I will add this to my todo list.

Michael

-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

> > I don't know if this makes you wrong, but Informix supports
> > an INTO clause in a cursor declaration. It's one of the things I had to
> > change while porting some applications from Informix ESQL/C to ECPG.
> > Here's what Informix says about the INTO clause in their training
> > materials for ESQL/C:
> So you think we should add this? I have no idea at the moment if it causes
> problems with the parser but I will add this to my todo list.

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.

                  - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California
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.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

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
----------------------------------


On Wed, Jun 16, 1999 at 02:33:06PM -0700, Craig Orsinger wrote:
> 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 
> ...

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.

>         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.

> 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 we only have FREE for prepared statements not for cursors. I cnnot
remember reading anything about freeing cursor in SQL92.

> 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.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!



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@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
----------------------------------

On Thu, Jun 17, 1999 at 11:58:14AM -0700, Craig Orsinger wrote:
> EXEC SQL DECLARE CURSOR <cursor id> FOR <select statement> ;
> EXEC SQL FETCH <cursor id> INTO <variable(s)> ;

Same with ORACLE.

>         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)> ;

That's what SQL-92 wants.

>         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

The standard wants to see IN. Simply omitting it wouldn't even work with our
parser. It creates a shift/reduce conflict. Of course we could fix that but
I doubt adding a non-standard feature is worth that effort.

>         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).

The FETCH command is completely handled by the backend. Everything that
works via psql should work via ecpg.

>         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?

Try ecpg -t. Without the '-t' ecpg automatically starts a transaction via
issuing the BEGIN command.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

On 18-Jun-99 Michael Meskes wrote:
>>         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
> 
> The standard wants to see IN. Simply omitting it wouldn't even work with our
> parser. It creates a shift/reduce conflict. Of course we could fix that but
> I doubt adding a non-standard feature is worth that effort.

        Well, it sounds like a pretty popular non-standard, since 
both Informix and Oracle use it. Nevertheless, I'd have to agree 
with you, if it's a lot of trouble. PostgreSQL cursors most closely 
resemble what Informix calls "scroll cursors", with the added ability 
to fetch more than one row  at a time. In addition to using different 
keywords for scrolling direction ("PREVIOUS/NEXT" vs. 
"FORWARD/BACKWARD"), PostgreSQL does not support  the ABSOLUTE, FIRST, 
or LAST directions, and the RELATIVE  clause does not have the same 
syntax (see next paragraph for an explanation). For more high-perfor-
mance applications than mine, there is likely to be little resemblence 
between the embedded SQL for these two databases. If the "IN" was made
optional, I could write DECLARE/OPEN/FETCH sequences that were 
compatible, but I would not be making the best use of either DBMS.

        Here's the difference in the RELATIVE clause:

FETCH RELATIVE 2 [IN] cursor ;

        In Informix, this fetches the current row, then skips two
rows. In psql, this fetches the next two rows, then skips to the
first row after. I don't know what the SQL92 spec says, but to me, 
the way Informix behaves makes more sense. After all, what good is
a RELATIVE clause if not to modify how many rows you skip? IOW, 
there needs to be a numerical field after "RELATIVE" for it to do
any good. I'm referring to how psql 6.4.2 behaves, BTW.

        Practically speaking, if there's no agreement among these 
embedded SQL implementations as to how to do preprocessor directives
like "EXEC SQL IFDEF", then the only alternative if one wants to 
maintain a single source file for each function is to use sed, yacc, 
perl or some other macro language to generate the appropriate
embedded SQL source. Are things like IF, IFDEF, ELSE, etc., defined 
in the SQL92 standard?

----------------------------------
Date: 21-Jun-99  Time: 10:09:24

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
----------------------------------

> On 18-Jun-99 Michael Meskes wrote:
> >>         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
> > 
> > The standard wants to see IN. Simply omitting it wouldn't even work with our
> > parser. It creates a shift/reduce conflict. Of course we could fix that but
> > I doubt adding a non-standard feature is worth that effort.

Excuse me -- what's wrong with shift/reduce conflicts? I have over a
hundred of those in one of my applications.

--Gene

On Mon, Jun 21, 1999 at 08:46:39PM -0400, selkovjr@mcs.anl.gov wrote:
> Excuse me -- what's wrong with shift/reduce conflicts? I have over a
> hundred of those in one of my applications.

Having a shift/reduce conflict means that you can find a statement that is
incorrectly parsed. The system will either do a shift (so the statement is
parsed via alternative 1) or do a reduce (parsing alternative 2) but what
you have in mind is certainly only one of these alternatives.

Michael
-- 
Michael Meskes                         | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz    | Go Rhein Fire!
Tel.: (+49) 2431/72651                 | Use Debian GNU/Linux!
Email: Michael.Meskes@gmx.net          | Use PostgreSQL!

Michael Meskes wrote:

> Having a shift/reduce conflict means that you can find a statement that is
> incorrectly parsed. 

A shift/reduce conflict means that you can either complete or continue
to parse an expression (if .. then .. else is a famous example). You
will always have this situation when both and expression and its
leading subexpression are valid inputs. There is nothing wrong with
it: any non-trivial grammar will have shift/reduce conflict. Bison is
written to always shift, i. e. attempt to complete valid partial
expressions.

> The system will either do a shift (so the statement is
> parsed via alternative 1) or do a reduce (parsing alternative 2) but what
> you have in mind is certainly only one of these alternatives.

This is what reduce/reduce conflicts are like. They occur when two
entirely different rules can be reduced based on the same sequence of
tokens. Bison aborts and exits when it sees a reduce/reduce conflict,
so you don't have to worry about parts of your grammar not being used.

Tomas Lockhart wrote:

> If it is the kind of shift/reduce conflict reported by yacc/bison
> (rather than a less severe clarification printed in the bison "-v"
> log file) then it means that there is a significant part of your
> grammar which can *never* be reached! The parser will always choose
> one of the two possible paths in the conflict, and will never choose
> the other.  Not good if you actually wanted to use the full
> language.

Its sounds more like a mid-rule action in a clause with a shift/reduce
conflict. Inserting an action in the middle of a clause may leave some
of the rules non-functional. There are suggestions on how to fix
that, as well as other conflicts, in the bison manual.
(http://www2.informatik.uni-halle.de/lehre/sprakt/bison.html)

Most conflicts can be fixed by adding intermediate rules. When that
appears difficult, I re-write the input to add artificial tokens
before passing it to the parser.

--Gene

> Excuse me -- what's wrong with shift/reduce conflicts? I have over a
> hundred of those in one of my applications.

If it is the kind of shift/reduce conflict reported by yacc/bison
(rather than a less severe clarification printed in the bison "-v" log
file) then it means that there is a significant part of your grammar
which can *never* be reached! The parser will always choose one of the
two possible paths in the conflict, and will never choose the other.
Not good if you actually wanted to use the full language.

                    - Thomas

-- 
Thomas Lockhart				lockhart@alumni.caltech.edu
South Pasadena, California