just another standards question

Now the one question: Informix and Sybase have three different
settings for indicator variables:

-1 means value is null
0 means value is okay

0 means value is okay, but string was truncated to n bytes

I tried Oracle but apparently they just use 0 and -1. Does the
standard say anything about this?

BTW is this SQL standard on-line anywhere? A WWW page maybe?

I had found a draft of the SQL92 standard on-line somewhere many months
ago; don't remember where. It is ~1.6MB uncompressed, probably much
smaller compressed; do you want me to send you a copy?

I believe that the official standard is available in print only, since
it is a cost-covering operation of the standards organization.

Anyway, it looks like Informix and Sybase have it right:

4.18.3 Indicator parameters

An indicator parameter is an integer parameter that is specified
immediately following another parameter. Its primary use is to
indicate whether the value that the other parameter assumes or
supplies is a null value. An indicator parameter cannot immediately
follow another indicator parameter.

The other use for indicator parameters is to indicate whether
string data truncation occurred during a transfer between a host
program and an SQL-implementation in parameters or host variables.
If a non-null string value is transferred and the length of the
target data item is sufficient to accept the entire source data
item, then the indicator parameter or variable is set to 0 to in-
dicate that truncation did not occur. However, if the length of
the target data item is insufficient, then the indicator parame-
ter or variable is set to the length of the source data item (in
characters or bits, as appropriate) to indicate that truncation
occurred and to indicate the original length in characters or bits,
as appropriate, of the source.
...

- Tom

Thomas G. Lockhart writes:

I had found a draft of the SQL92 standard on-line somewhere many months
ago; don't remember where. It is ~1.6MB uncompressed, probably much
smaller compressed; do you want me to send you a copy?

Yes, please.

Anyway, it looks like Informix and Sybase have it right:

4.18.3 Indicator parameters

An indicator parameter is an integer parameter that is specified
immediately following another parameter. Its primary use is to
indicate whether the value that the other parameter assumes or
supplies is a null value. An indicator parameter cannot immediately
follow another indicator parameter.

Okay, that works completely.

The other use for indicator parameters is to indicate whether
string data truncation occurred during a transfer between a host
program and an SQL-implementation in parameters or host variables.
If a non-null string value is transferred and the length of the
target data item is sufficient to accept the entire source data
item, then the indicator parameter or variable is set to 0 to in-
dicate that truncation did not occur. However, if the length of
the target data item is insufficient, then the indicator parame-
ter or variable is set to the length of the source data item (in
characters or bits, as appropriate) to indicate that truncation
occurred and to indicate the original length in characters or bits,
as appropriate, of the source.

So I have to set the indicator also during writes? That is if I insert
string "foobar" into char(3), do I have to set the indicator to 3?

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