Horton

Thread

  1. standards question

    Michael Meskes <meskes@topsystem.de> — 1998-03-17T15:53:24Z

    Is there a standard that defines how to return information from an embedded
SQL statement? ORACLE uses the sqlca. In particular it uses sqlca.sqlerrd[2]
for affected tuples. Is this correct with the standards? How do other
systems do that?

Michael
--
Dr. Michael Meskes, Project-Manager    | topsystem Systemhaus GmbH
meskes@topsystem.de                    | Europark A2, Adenauerstr. 20
meskes@debian.org                      | 52146 Wuerselen
Go SF49ers! Go Rhein Fire!             | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux!                  | Fax: (+49) 2405/4670-10

  2. Re: [HACKERS] standards question

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-03-17T18:53:32Z

    > 
> Is there a standard that defines how to return information from an embedded
> SQL statement? ORACLE uses the sqlca. In particular it uses sqlca.sqlerrd[2]
> for affected tuples. Is this correct with the standards? How do other
> systems do that?

I believe this is standard for embedded SQL.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

  3. Re: [HACKERS] standards question

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-03-18T06:56:13Z

    Michael Meskes wrote:
> 
> Is there a standard that defines how to return information from an 
> embedded SQL statement? ORACLE uses the sqlca. In particular it uses 
> sqlca.sqlerrd[2] for affected tuples. Is this correct with the 
> standards? How do other systems do that?

There is a part of the SQL92 standard which deals with this. My Date &
Darwen book has a chapter on it.

                - Tom

  4. Re: [HACKERS] standards question

    Michael Meskes <meskes@topsystem.de> — 1998-03-18T08:23:33Z

    Thomas G. Lockhart writes:
> There is a part of the SQL92 standard which deals with this. My Date &
> Darwen book has a chapter on it.

Could you please enlighten me Tom? :-)

Michael

-- 
Dr. Michael Meskes, Project-Manager    | topsystem Systemhaus GmbH
meskes@topsystem.de                    | Europark A2, Adenauerstr. 20
meskes@debian.org                      | 52146 Wuerselen
Go SF49ers! Go Rhein Fire!             | Tel: (+49) 2405/4670-44
Use Debian GNU/Linux!                  | Fax: (+49) 2405/4670-10

  5. Re: [HACKERS] standards question

    Thomas Lockhart <lockhart@alumni.caltech.edu> — 1998-03-18T16:07:05Z

    > Could you please enlighten me Tom? :-)

*grin* As you could see I was trying to get out of doing work...

OK, here is a short series of extracts from the book:

Summary list of SQL statements having to do with SQL descriptor areas:
  ALLOCATE DESCRIPTOR, DEALLOCATE DESCRIPTOR
  GET DESCRIPTOR, SET DESCRIPTOR
  DESCRIBE

"SQLDA" is a useful abbreviation (for the SQL descriptor area) but it is
not an official standard term. Note clearly, moreover, that several SQL
products do already support some kind of SQLDA, and even refer to it by
that name, but those SQLDAs are typically not the same as the "SQLDA"
defined in the standard. In particular, the SQLDA in the standard is
*encapsulated* (meaning that its internal structure is not specified in
the standard and is thus *a fortiori* hidden from the user), whereas the
same is typically not true of the SQLDAs in current products.

We list below some of the more important of these components (the
retrievable and settable components of the SQL92 SQLDA):

NAME	character string
UNAMED	exact numeric with scale 0 (tgl- I'll call this int)
TYPE	int
LENGTH	int
RETURNED_LENGTH int
RETURNED_OCTET_LENGTH int
PRECISION int
SCALE	int
DATA	(depends on type, length, etc)
INDICATOR int

If the INDICATOR value is negative, the DATA value is undefined
("null"). The UNAMED component is set to 1 if the NAME value is
implementation-dependent (loosely, if the corresponding column is
unnamed).

Syntax:
  ALLOCATE DESCRIPTOR [LOCAL|GLOBAL] descriptor [WITH MAX occurences]

(descriptor is specified inside single quotes). The GLOBAL is default if
not specified.

There are several ways to populate a descriptor:

1) Specify
  INTO SQL DESCRIPTOR descriptor
in an EXECUTE statement (in which case the prepared statement must be a
single-row select).

2) Specify the same in a dynamic FETCH statement.

3) Specify
  USING SQL DESCRIPTOR descriptor
in a DESCRIBE INPUT or DESCRIBE OUTPUT statement. This is the
recommended method for the most highly generalized type of application.

4) Execute
  SET DESCRIPTOR descriptor ...

Each such statement will assign values to components within a *single
item descriptor* within the SQLDA.

...

GET DESCRIPTOR descriptor target = COUNT

where target is a parameter or host variable of type exact numeric with
scale 0 (int??).


My book has quite a bit more information on this, but much more will be
too much to type :(

                        - Tom

  6. Re: [HACKERS] standards question

    Kent S. Gordon <kgor@inetspace.com> — 1998-03-18T19:28:44Z

    >>>>> "meskes" == Michael Meskes <meskes@topsystem.de> writes:

    > Is there a standard that defines how to return information from
    > an embedded SQL statement? ORACLE uses the sqlca. In particular
    > it uses sqlca.sqlerrd[2] for affected tuples. Is this correct
    > with the standards? How do other systems do that? 

 sqlca is fairly standard in most databases.  It was the recommended method of
doing exception handling in the past.  The SQL2 (SQL/92) standard went
to using SQLSTATE variable and GET DIAGNOSISTICS statement.

    > Michael -- Dr. Michael Meskes, Project-Manager | topsystem
    > Systemhaus GmbH meskes@topsystem.de | Europark A2,
    > Adenauerstr. 20 meskes@debian.org | 52146 Wuerselen Go SF49ers!
    > Go Rhein Fire!  | Tel: (+49) 2405/4670-44 Use Debian GNU/Linux!
    > | Fax: (+49) 2405/4670-10

-- 
Kent S. Gordon
Architect
iNetSpace Co.
voice: (972)851-3494 fax:(972)702-0384 e-mail:kgor@inetspace.com