Thread: ESQL/C no indicator variables ./. error -213
Hello, 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. Comments? 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!
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:433]: ECPGdebug: set to 1 [6485] [18.06.2020 08:26:38:433]: ECPGdebug: proc argv0 is embedded [6485] [18.06.2020 08:26:38:433]: ECPGconnect: opening database sisis on localhost port 5432 with options application_name=SunRiseDBCALL V7.1 (pid=6485) for user sisis [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 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!
Attachment
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_execute on line 38: query: select tstchar25 , tstint from dbctes
t where tstint = 1; with 0 parameter(s) on connection sisis
[1549069]: ecpg_execute on line 38: using PQexec
[1549069]: ecpg_process_output on line 38: correctly got 1 tuples with 2 fields
[1549069]: ecpg_get_data on line 38: RESULT: offset: 8
0; array: no
[1549069]: ecpg_get_data on line 38: RESULT: 1 offset: 4; array: no
[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