ESQL/C no indicator variables ./. error -213

Matthias Apitz <guru@unixarea.de> writes:

We encountered that if our ESQL/C written servers see on SELECT or FETCH
in a row a NULL value, it will raise correctly the error -213 as written
and explained in https://www.postgresql.org/docs/11/ecpg-variables.html#ECPG-INDICATORS
We catch this error -213 and deal with in.

What we did not knew and discovered today is something very fatal: In
such a situation on a FETCH of a row of some 55 columns, the transfer of
the column elements into their hostvariables stops on first NULL value,
as here to be seen in the log:

Could you provide a self-contained test case for this? It's hard to
guess at what the problem might be.

regards, tom lane

El día Mittwoch, Juni 17, 2020 a las 01:39:53 -0400, Tom Lane escribió:

Matthias Apitz <guru@unixarea.de> writes:

We encountered that if our ESQL/C written servers see on SELECT or FETCH
in a row a NULL value, it will raise correctly the error -213 as written
and explained in https://www.postgresql.org/docs/11/ecpg-variables.html#ECPG-INDICATORS
We catch this error -213 and deal with in.

What we did not knew and discovered today is something very fatal: In
such a situation on a FETCH of a row of some 55 columns, the transfer of
the column elements into their hostvariables stops on first NULL value,
as here to be seen in the log:

Could you provide a self-contained test case for this? It's hard to
guess at what the problem might be.

Hello,

attached is a simple ESQL/C code; if it is not passing the mailing-list,
the code is also here: http://www.unixarea.de/embedded.pgc and this is
its output together with the ESQL/C error log file and SQL examples:

psql -Usisis -dsisis
psql (11.4)
Geben Sie »help« für Hilfe ein.

sisis=# \d dbctest
Tabelle »public.dbctest«
Spalte | Typ | Sortierfolge | NULL erlaubt? | Vorgabewert
-----------+---------------+--------------+---------------+-------------
tstchar25 | character(25) | | |
tstint | integer | | |
Indexe:
"i_tstint" UNIQUE, btree (tstint)

sisis=# select * from dbctest where tstint = 1;
tstchar25 | tstint
-----------+--------
| 1
(1 Zeile)

./embedded
hostvariable 'tstint' before SELECT tstchar25, tstint INTO :tstchar25, :tstint FROM dbctest: 99
hostvariable 'tstint' after SELECT tstchar25, tstint INTO :tstchar25, :tstint FROM dbctest with -213: 99

cat esqlc.6485
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed
[6485]: [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed

Thanks

matthias

--
Matthias Apitz, ✉ guru@unixarea.de, http://www.unixarea.de/ +49-176-38902045
Public GnuPG key: http://www.unixarea.de/key.pub
May, 9: Спаси́бо освободители! Thank you very much, Russian liberators!

On Wed, 2020-06-17 at 19:02 +0200, Matthias Apitz wrote:

We encountered that if our ESQL/C written servers see on SELECT or FETCH
in a row a NULL value, it will raise correctly the error -213 as written
and explained in https://www.postgresql.org/docs/11/ecpg-variables.html#ECPG-INDICATORS
We catch this error -213 and deal with in.

What we did not knew and discovered today is something very fatal: In
such a situation on a FETCH of a row of some 55 columns, the transfer of
the column elements into their hostvariables stops on first NULL value,
as here to be seen in the log:

[29217] [17.06.2020 15:49:16:499]: ecpg_execute on line 69: query: select * from acq_ffleit where bnr = $1 ; with 1 parameter(s) on connection sisis
[29217] [17.06.2020 15:49:16:500]: ecpg_execute on line 69: using PQexecParams
[29217] [17.06.2020 15:49:16:500]: ecpg_free_params on line 69: parameter 1 = 742
[29217] [17.06.2020 15:49:16:500]: ecpg_process_output on line 69: correctly got 1 tuples with 55 fields
[29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: 742 offset: 752; array: no
...
[29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: 49 offset: 752; array: no
[29217] [17.06.2020 15:49:16:500]: ecpg_get_data on line 69: RESULT: offset: 752; array: no
[29217] [17.06.2020 15:49:16:500]: raising sqlcode -213 on line 69: null value without indicator on line 69

In the above examples the transfer stopped after 47 RESULTs; the
hostvariables after this have been untouched in our program, i.e. only
part of the row has been read.

One could say, "so what, you have error -213 and you deserve it". But at
least this behaviour should be documented clearly in the above mentioned page.

I would expect, that NULL values would not be transfered in such case
but all other hostvariables yes, i.e. for me this is perhaps a bug in the
ESQL/C implementation.

I think that is as expected.

The documentation says:

To be able to pass null values to the database or retrieve null values from the database,
you need to append a second host variable specification to each host variable that contains data.
This second host variable is called the *indicator* [...]

Failure to do that causes an error (which you catch).
If a statement causes an error, you cannot rely on the state of any host valiable
that gets set by that statement.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com

Matthias Apitz <guru@unixarea.de> writes:

El día Mittwoch, Juni 17, 2020 a las 01:39:53 -0400, Tom Lane escribió:

Could you provide a self-contained test case for this? It's hard to
guess at what the problem might be.

attached is a simple ESQL/C code;
...
[6485] [18.06.2020 08:26:38:436]: ecpg_execute on line 36: query: select tstchar25 , tstint from dbctest where tstint = 1; with 0 parameter(s) on connection sisis
[6485] [18.06.2020 08:26:38:437]: ecpg_execute on line 36: using PQexec
[6485] [18.06.2020 08:26:38:437]: ecpg_process_output on line 36: correctly got 1 tuples with 2 fields
[6485] [18.06.2020 08:26:38:437]: ecpg_get_data on line 36: RESULT: offset: 80; array: no
[6485] [18.06.2020 08:26:38:437]: raising sqlcode -213 on line 36: null value without indicator on line 36
[6485] [18.06.2020 08:26:38:438]: ecpg_finish: connection sisis closed

Hmm. Trying this here, I get

$ ./embedded
hostvariable 'tstint' before SELECT tstchar25, tstint INTO :tstchar25, :tstint FROM dbctest: 99
hostvariable 'tstint' after SELECT tstchar25, tstint INTO :tstchar25, :tstint FROM dbctest with -213: 1
...
[1549069]: ecpg_finish: connection sisis closed
t where tstint = 1; with 0 parameter(s) on connection sisis
[1549069]: ecpg_finish: connection sisis closed
[1549069]: ecpg_finish: connection sisis closed
[1549069]: ecpg_finish: connection sisis closed
0; array: no
[1549069]: ecpg_finish: connection sisis closed
[1549069]: ecpg_finish: connection sisis closed

which looks like the right thing. I don't pay much attention to ECPG
development, but it seems like you must be hitting a bug that's been
fixed. Are your ecpg and libecpg up to date?

regards, tom lane

I wrote:

Hmm. Trying this here, I get
...

Oh, scratch that, I'd failed to duplicate the test case. (I had an
empty string, not NULL, as the value of the tstchar25 column.)

I concur with Laurenz's diagnosis that you need to use an indicator
variable if you want to retrieve a NULL.

regards, tom lane