> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Wednesday, June 12, 2002 2:42 PM
> To: Dann Corbit
> Cc: Michael Meskes; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] PostGres Doubt
> 
> 
> Dann Corbit wrote:
> > I should apologize for being rather harsh about embedded SQL for
> > PostgreSQL.
> > 
> > To be fair, it does function and it certainly isn't trivial to
> > implement.  I am sure that those who have worked on this 
> project have
> > invested very many hours of blood, sweat and tears making it work.
> 
> Oh, OK.  Forget what I said earlier about you writing your 
> own database. :-)
> 
> > I actually spent a great deal of effort trying to write 
> some tools using
> > the PostgreSQL version of ECPG, and found fatal flaws that 
> threw away a
> > couple weeks of work.  I think that is why my responses were so
> > overwhelmingly negative.
> 
> I assume this is because you wrote your code assuming a feature was in
> ecpg, but it wasn't, right?

I have written lots of programs that use embedded SQL.  I have (for
instance) several ODBC drivers that use embedded SQL and C++ as part of
an ODBC driver system.  I merrily coded away some stuff to do the same
thing in PostgreSQL.  After all, I had already done it for several other
systems and they all worked just about the same and the effort was
minimal to change from one system to another.

So now, I started getting down to the details.  One global structure...
I started a major rewrite to repair it.  Then (to my abject horror) I
discovered there is no SQLCA at all.  Project abandoned (actually, just
switched to libpq and everything was OK).

Yes, you are right -- I should have checked a lot more carefully before
I dove in.  I would have avoided getting my bun in a knott.
 
> > Here is what I would like to see (consider a gentle suggestion):
> > 
> > A reentrant version of ECPG that uses SQLCA and SQLDA like 
> Oracle or Rdb
> > or DB/2 or any of the professional database systems.
> 
> I see on the TODO list under ECPG:
> 
>         o Implement SQLDA
>         o Add SQLSTATE
> 
> Are these related to your problem?  I see SQLCA in the ecpg code
> already.  Is it implemented incorrectly?  If so, I could use items to
> add to the TODO list.
> 
> You are actually the first person to complain about this, as far as I
> can remember.

I doubt if many people are using it then.  There is a NIST SQL suite
which should be run against it.  Have you heard of it?  It is a
standardization for embedded SQL [and other facets of the SQL langauge].
I think it would be very nice if the PostgreSQL team should try to
incorporte the whole thing as part of their validation suite.  The
project the uses embedded sql is in the folder /pc under the nist main
folder.  Here is an example from that project that use sqlca:

/*  EMBEDDED C (file "XOP710.PC")  */

/*   Copyright 1994, 1995 X/Open Company Limited  */

/* All rights reserved.                                          */
/*                                                               */
/* DISCLAIMER:                                                   */
/* This program was reviewed by employees of NIST for            */
/* conformance to the SQL standards.                             */
/* NIST assumes no responsibility for any party's use of         */
/* this program.                                                 */

/*   X/Open and the 'X' symbol are registered trademarks of X/Open
Company  */
/*   Limited in the UK and other countries.
*/


/*****************************************************************/
/*                                                               */
/*                  COMMENT SECTION                              */
/*                                                               */
/*  DATE 1994/05/13 EMBEDDED C LANGUAGE                          */
/*  X/Open SQL VALIDATION TEST SUITE V6.0                        */
/*                                                               */
/*  xop710.pc                                                    */
/*  WRITTEN BY: Colin O'Driscoll                                 */
/*                                                               */
/*  Acceptance of correctly placed SQLCA                         */
/*                                                               */
/*  REFERENCES                                                   */
/*        X/Open CAE SQL Specification.                          */
/*        Section 8.1.1                                          */
/*                                                               */
/*                 <embedded SQL C program>                      */
/*                                                               */
/*  DATE PROGRAM LAST CHANGED   02/11/94                         */
/*                                                               */
/*****************************************************************/

#include <stdio.h>
#include <time.h>
#include <string.h>
#include <stdlib.h>
EXEC SQL BEGIN DECLARE SECTION;

/* this line may be needed for some preprocessors */
long SQLCODE;

char SQLSTATE[6];
char uid[19];
char uidx[19];
EXEC SQL END DECLARE SECTION;
extern int AUTHID();

/* INCLUDE SQLCA placed correctly */
EXEC SQL INCLUDE sqlca;

/* variables for NOSUBCLASS() */
long norm1;
long norm2;
char ALPNUM[37];
char NORMSQ[6];

int errcnt;
/* date_time declaration */
time_t cal;
            long  errflg;

CHCKOK ()
{
SQLSTATE[5] = '\0';
printf ("SQLSTATE should be 00000; its value is %s\n", SQLSTATE);

NOSUBCLASS();
if (strncmp (NORMSQ, "00000", 5) == 0 &&
strncmp (NORMSQ, SQLSTATE, 5) != 0)
printf ("Valid implementation defined SQLSTATE accepted.\n");
}

main()
{


     strcpy(uid,"XOPEN1");
     AUTHID(uid);
strcpy(uidx,"not logged in, not");
EXEC SQL SELECT USER INTO :uidx FROM XOPEN1.ECCO;
if (strncmp(uid,uidx,6) != 0)
   {
    printf("ERROR: User %s expected.  User %s connected\n",uid,uidx);
    exit(99);
   }
errcnt = 0;
errflg = 0;
printf("X/OPEN Extensions SQL Test Suite, V6.0, Embedded C,
xop710.pc\n");
printf("59-byte ID\n");
printf("TEd Version #\n");
/* date_time print */
time (&cal);
printf ("\n Time Run:  %s\n", ctime (&cal));

strcpy(ALPNUM, "01234ABCDEFGH56789IJKLMNOPQRSTUVWXYZ");

/******************** BEGIN TEST0710 ********************/

     printf("\n                     TEST0710             \n");
     printf(" X/O,Acceptance of correctly placed SQLCA\n");
     printf(" X/OPEN SQL CAE Spec Section 8.1.1\n");
     printf("       - - - - - - - - - - - - - - - - - - -\n\n");
     printf(" ### INSERT INTO WARNING VALUES('DDDDDD',5);\n");
     printf("\n\n=================================================\n");

     EXEC SQL DELETE FROM WARNING;
/* initialise variables */
     strcpy(SQLSTATE,"x");
     sqlca.sqlcode = 5;

     EXEC SQL INSERT INTO WARNING VALUES('DDDDDD',5);

     printf("sqlca.sqlcode should be 0 \n");
     printf("sqlca.sqlcode is %ld\n", sqlca.sqlcode);

     CHCKOK();
     if ((sqlca.sqlcode != 0) && (strncmp(NORMSQ,"00000",5)  != 0))
          {
          printf ("*** Problem found in TEST STEP NUMBER 1  *** \n");
          errflg = errflg + 1;
         }

     EXEC SQL ROLLBACK WORK;
     printf("\n\n=================================================\n");

     if (errflg == 0)
     {

         EXEC SQL INSERT INTO XOPEN1.TESTREPORT
VALUES('0710','pass','PC');
         printf("\n\n  xop710.pc              *** pass ***     ");
     }

     else
     {
         EXEC SQL INSERT INTO XOPEN1.TESTREPORT
VALUES('0710','fail','PC');
          errcnt = errcnt + 1;
         printf("\n\n xop710.pc              *** fail ***     ");
     }

     printf("\n\n=================================================\n");
     printf("\n\n\n\n");
                         
     EXEC SQL COMMIT WORK;

/******************** END TEST0710 ********************/

     exit(errcnt);

}

NOSUBCLASS()
{
/* This routine replaces valid implementation deifined   */
/* subclasses with 000. This replacement equates valid   */
/* implementation-defined subclasses with the 000 value  */
/* expected by the test case; otherwise the test will    */
/* fail. After calling NOSUBCLASS, NORMSQ will be tested */
/*                              SQLSTATE will be printed */

strcpy (NORMSQ, SQLSTATE);

norm1 = 2;
/* subclass begins in position 3 of char array NORMSQ    */
for (norm2 = 13; norm2 < 37; norm2++)
/* valid subclasses begin with 5/9, I-Z, end of ALPNUM table */
    {
    if (NORMSQ[norm1] == ALPNUM[norm2])
         NORMSQ[norm1] = '0';
    }

if (strncmp (NORMSQ, SQLSTATE, 5) == 0)
   goto P213;
/* Quit if NORMSQ is unchanged. Subclass is not impl.def */
/* Changed NORMSQ means implementation-defined subclass, */
/* so proceed to zero it out, if valid (0-9, A-Z)        */

norm1 = 3;
/* examining position 4 of char array NORMSQ             */
for (norm2 = 0; norm2 < 37; norm2++)
/* valid characters are 0-9 A-Z                          */
   {
   if (NORMSQ[norm1] == ALPNUM[norm2])
       NORMSQ[norm1] = '0';
   }

norm1 = 4;
/* examining position 5 of char array NORMSQ             */
for (norm2 = 0; norm2 < 37; norm2++)
/* valid characters are 0-9 A-z */
   {
   if (NORMSQ[norm1] == ALPNUM[norm2])
       NORMSQ[norm1] = '0';
   }

/* implementation-defined subclasses are allowed for warnings */
/* (class = 01). These equate to successful completion */
/* SQLSTATE values of 00000. */
/* reference SQL-92. 4.28 SQL-transactions, paragraph 2 */

if (NORMSQ[0] == '0' && NORMSQ[1] == '1')
    NORMSQ[1] = '0';
P213:
    return;

}

Wed, 12 Jun 2002 15:01:31 -0700
"Dann Corbit" <DCorbit@connx.com>
Re: PostGres Doubt
Dann Corbit 
> > I assume this is because you wrote your code assuming a feature was in
> > ecpg, but it wasn't, right?
> 
> I have written lots of programs that use embedded SQL.  I have (for
> instance) several ODBC drivers that use embedded SQL and C++ as part of
> an ODBC driver system.  I merrily coded away some stuff to do the same
> thing in PostgreSQL.  After all, I had already done it for several other
> systems and they all worked just about the same and the effort was
> minimal to change from one system to another.
> 
> So now, I started getting down to the details.  One global structure...
> I started a major rewrite to repair it.  Then (to my abject horror) I
> discovered there is no SQLCA at all.  Project abandoned (actually, just
> switched to libpq and everything was OK).


I see SQLCA mentioned in the ecpg code.  What am I not understanding?

> > > Here is what I would like to see (consider a gentle suggestion):
> > > 
> > > A reentrant version of ECPG that uses SQLCA and SQLDA like 
> > Oracle or Rdb
> > > or DB/2 or any of the professional database systems.
> > 
> > I see on the TODO list under ECPG:
> > 
> >         o Implement SQLDA
> >         o Add SQLSTATE
> > 
> > Are these related to your problem?  I see SQLCA in the ecpg code
> > already.  Is it implemented incorrectly?  If so, I could use items to
> > add to the TODO list.
> > 
> > You are actually the first person to complain about this, as far as I
> > can remember.
> 
> I doubt if many people are using it then.  There is a NIST SQL suite
> which should be run against it.  Have you heard of it?  It is a
> standardization for embedded SQL [and other facets of the SQL langauge].
> I think it would be very nice if the PostgreSQL team should try to
> incorporte the whole thing as part of their validation suite.  The
> project the uses embedded sql is in the folder /pc under the nist main
> folder.  Here is an example from that project that use sqlca:

Oh, that seems easy.  I know Michael will know the answer.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Wed, 12 Jun 2002 18:19:57 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostGres Doubt
On Wed, Jun 12, 2002 at 06:19:57PM -0400, Bruce Momjian wrote:
> > I doubt if many people are using it then.  There is a NIST SQL suite
> > which should be run against it.  Have you heard of it?  It is a
> > standardization for embedded SQL [and other facets of the SQL langauge].
> > I think it would be very nice if the PostgreSQL team should try to
> > incorporte the whole thing as part of their validation suite.  The
> > project the uses embedded sql is in the folder /pc under the nist main
> > folder.  Here is an example from that project that use sqlca:
> 
> Oh, that seems easy.  I know Michael will know the answer.

Actually I didn't know that test suite. But I will surely look at it.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Thu, 13 Jun 2002 12:09:12 +0200
Michael Meskes <meskes@postgresql.org>
Re: PostGres Doubt
On Wed, Jun 12, 2002 at 03:01:31PM -0700, Dann Corbit wrote:
> project the uses embedded sql is in the folder /pc under the nist main
> folder.  Here is an example from that project that use sqlca:

Of course this file alone won't run very well, but I added enough stuff
and created a database to get it running and here's my result:

1) SQLSTATE does not work, which of course is not surprising.
2) It triggered one bug in parsing octal number in single quotes. The
patch was just committed.
3) I had to remove the SQLCODE definition as SQLCODE at the moment is a
#define. Maybe we should change that.
4) The SQLCA test runs through just fine.

Michael
-- 
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Mon, 17 Jun 2002 15:27:37 +0200
Michael Meskes <meskes@postgresql.org>
Re: PostGres Doubt