Re: PostGres Doubt - Mailing list pgsql-hackers

> -----Original Message-----
> From: Bruce Momjian [mailto:pgman@candle.pha.pa.us]
> Sent: Wednesday, June 12, 2002 3:20 PM
> To: Dann Corbit
> Cc: Michael Meskes; pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] 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?

I meant to say no SQLDA (the SQLCA only has the problem of scope).
> > > > 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.

Those are precisely the missing items (along with the implementation of
SQLCA -- it should not be a global object, but rather be declared and a
new instance gets created).

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

Embedded SQL is subject to standard "X/Open DR":
http://www.opengroup.org/sib.htm

If it can pass all of the tests in the NIST validation suite, then I
think that would be a great start.

It is also an excellent test for all the other facets of SQL -- it tests
SQL/CLI (ODBC), transact SQL, etc.

Many government contracts cannot be fulfilled by products which have not
been certified to pass this suite.
http://www.opengroup.org/public/prods/drm4.htm
At least, that used to be the case.  If need be, I can supply a copy of
the test suite (I can't seem to find the download link any more).

Here are some other implementations:
Xdb:
http://www.va.pubnix.com/man/xdb/sqlref/SQLDAStructureForC_516.html

Progress:
http://www.progress.com/support/downloads/v91c_release_notes/esql_92.pdf

Sybase:
http://manuals.sybase.com/onlinebooks/group-or/org0400e/osrsp32/@Generic
__BookTextView/7062

DB/2:
http://publib.boulder.ibm.com/html/as400/v4r5/ic2924/index.htm?info/db2/
rbafymst222.htm

Microsoft SQL*Server
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/esqlfor
c/ec_6_erf_03_8ag5.asp

Informix:
http://www.informix.com/answers/english/docs/dbdk/infoshelf/esqlc/15.fm1
.html

SQL/Anywhere:
http://bonsai.ucdmc.ucdavis.edu/SQLHelp/00000268.htm

Adabas (This one for Adabas is very nice, it has a formal grammar!):
http://www.softwareag.com/adabasd/documentation/docuen/html/prceng9.htm

Oracle:
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a8986
1/pc_15ody.htm#4581

General case:
http://www.fh-sbg.ac.at/~ulamec/sql/sqlda.htm

An overview:
http://www.cs.purdue.edu/homes/mcclure/cs448/info/oraproc.ppt