Postgres Pro
Downloads
Home   >   mailing lists

Thread: just another standards question

just another standards question

From
Michael Meskes
Date:
I'm about finished with indicator variables. They seem to work well with
output variables as well as with input variables in insert statements.

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?

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

Re: [HACKERS] just another standards question

From
"Thomas G. Lockhart"
Date:
> 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

Re: [HACKERS] just another standards question

From
Michael Meskes
Date:
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