Re: PostGres Doubt - Mailing list pgsql-hackers

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

}