ODBC Driver 9->11, troubles with dates/NULL...

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

ODBC Driver 9->11, troubles with dates/NULL...

Marco Gaiarin

After long time, i've decided to upgrade ODBC driver from version 09.03.0100
to version 11.01.0000. A big jump, indeed.

I'm using an old application written in Centura, that with 09.03.0100
works as expected.

But with '11.01.0000' no. Throw the error:

 2019-07-22 10:04:28 CEST [19212-1] barbaradg@regproto ERRORE:  sintassi di input non valida per il tipo date: "" al carattere 524
 2019-07-22 10:04:28 CEST [19212-2] barbaradg@regproto ISTRUZIONE:  INSERT INTO PROTOCOLLI ( NUM_ORDINE, ANNO_REGIST, DATA_REGIST, SEDE, PADIGLIONE,   DATA_ARRIVO, COD_PROV, PROVENIENZA, DATA_ATTO_RIC, NUM_ATTO_RIC, ESTR_CDESC_AR, ESTR_ATTO_RIC,   NUM_ALLEGATI_AR, DATA_SPEDIZIONE, COD_IND, INDIRIZZO, ESTR_CDESC_AS, ESTR_ATTO_SPED, NUM_ALLEGATI_AS,   NUM_RIF_PROT_AR, NUM_RIF_PROT_AS, CLASS_ARCH_AR, CLASS_ARCH_AS, SPESE_POSTALI_AR, SPESE_POSTALI_AS,   COD_SETTORE_AR, COD_SETTORE_AS, COD_OPERATORE_AR, COD_OPERATORE_AS )   VALUES ( E'2163'::float8           , E'2019'::float8            , E''            , NULL      , NULL            ,   NULL            , E''         , E''               , NULL             , E''            , E''             , E''               ,   E''               , E''                , E''        , E'sometext'             , E''             , E'sometext'                , E''               ,   E''        , E''              , E''             , E''             , NULL                , NULL                , E'sometext'              , E''              , E'sometext'          , E''                 )

error are relative to the first date field, 'DATA_REGIST', where the
input is:

        E''

while the table expect a non-NULL:

               Tabella "public.protocolli"
     Colonna      |          Tipo          | Modificatori
------------------+------------------------+--------------
 num_ordine       | integer                | non null
 anno_regist      | integer                | non null
 data_regist      | date                   | non null
 sede             | integer                |
 padiglione       | integer                |
 data_arrivo      | date                   |
 cod_prov         | character varying(5)   |
 provenienza      | character varying(250) |
 data_atto_ric    | date                   |
 num_atto_ric     | character varying(20)  |
 estr_cdesc_ar    | character varying(5)   |
 estr_atto_ric    | character varying(250) |
 num_allegati_ar  | character varying(10)  |
 data_spedizione  | date                   |
 cod_ind          | character varying(5)   |
 indirizzo        | character varying(250) |
 estr_cdesc_as    | character varying(5)   |
 estr_atto_sped   | character varying(250) |
 num_allegati_as  | character varying(10)  |
 num_rif_prot_ar  | character varying(10)  |
 num_rif_prot_as  | character varying(10)  |
 class_arch_ar    | character varying(20)  |
 class_arch_as    | character varying(20)  |
 spese_postali_ar | double precision       |
 spese_postali_as | double precision       |
 cod_settore_ar   | character varying(50)  |
 cod_settore_as   | character varying(50)  |
 cod_operatore_ar | character varying(50)  |
 cod_operatore_as | character varying(50)  |
Indici:
    "protocolli_pkey" PRIMARY KEY, btree (anno_regist, num_ordine)


So, seems correct to me.


Seems that somewhere 'treat empty dates as null' is set, but... where?

Thanks.

--
dott. Marco Gaiarin        GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''          http://www.lanostrafamiglia.it/
  Polo FVG   -   Via della Bontà, 7 - 33078   -   San Vito al Tagliamento (PN)
  marco.gaiarin(at)lanostrafamiglia.it   t +39-0434-842711   f +39-0434-842797

                Dona il 5 PER MILLE a LA NOSTRA FAMIGLIA!
      http://www.lanostrafamiglia.it/index.php/it/sostienici/5x1000
        (cf 00307430132, categoria ONLUS oppure RICERCA SANITARIA)


Reply | Threaded
Open this post in threaded view
|

Re: ODBC Driver 9->11, troubles with dates/NULL...

Clemens Ladisch
Marco Gaiarin wrote:

> ERRORE:  sintassi di input non valida per il tipo date: ""
> ISTRUZIONE:  INSERT INTO PROTOCOLLI ( ... DATA_REGIST ...) VALUES ( ... E'' ... )
>
> while the table expect a non-NULL:
>
>                Tabella "public.protocolli"
>      Colonna      |          Tipo          | Modificatori
> ------------------+------------------------+--------------
> ...
>  data_regist      | date                   | non null

Why are you mentioning NULL?  This is an empty string, which is not a valid date.

Are you sure that the ODBC driver version makes a difference?
Where should a valid date value come from, if not from the application?


Regards,
Clemens


Reply | Threaded
Open this post in threaded view
|

Re: ODBC Driver 9->11, troubles with dates/NULL...

Marco Gaiarin
Mandi! Clemens Ladisch
  In chel di` si favelave...

> Why are you mentioning NULL?  This is an empty string, which is not a valid date.

Correct. but...

> Are you sure that the ODBC driver version makes a difference?

Yes. With odbc driver 09.XX works as expected.


> Where should a valid date value come from, if not from the application?

AFAIK in a subsequent query the application add the date.

--
dott. Marco Gaiarin        GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''          http://www.lanostrafamiglia.it/
  Polo FVG   -   Via della Bontà, 7 - 33078   -   San Vito al Tagliamento (PN)
  marco.gaiarin(at)lanostrafamiglia.it   t +39-0434-842711   f +39-0434-842797

                Dona il 5 PER MILLE a LA NOSTRA FAMIGLIA!
      http://www.lanostrafamiglia.it/index.php/it/sostienici/5x1000
        (cf 00307430132, categoria ONLUS oppure RICERCA SANITARIA)


Reply | Threaded
Open this post in threaded view
|

Re: ODBC Driver 9->11, troubles with dates/NULL...

Marco Gaiarin

Sorry, wrote too quickly.

> > Are you sure that the ODBC driver version makes a difference?
> Yes. With odbc driver 09.XX works as expected.

I'm not speaking 'in theory': i've rolled back driver to version 09.XX,
and application work as expected.

--
dott. Marco Gaiarin        GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''          http://www.lanostrafamiglia.it/
  Polo FVG   -   Via della Bontà, 7 - 33078   -   San Vito al Tagliamento (PN)
  marco.gaiarin(at)lanostrafamiglia.it   t +39-0434-842711   f +39-0434-842797

                Dona il 5 PER MILLE a LA NOSTRA FAMIGLIA!
      http://www.lanostrafamiglia.it/index.php/it/sostienici/5x1000
        (cf 00307430132, categoria ONLUS oppure RICERCA SANITARIA)


Reply | Threaded
Open this post in threaded view
|

Re: ODBC Driver 9->11, troubles with dates/NULL...

Marco Gaiarin
In reply to this post by Marco Gaiarin

> > Are you sure that the ODBC driver version makes a difference?
> Yes. With odbc driver 09.XX works as expected.

I have to provide some logs?

--
dott. Marco Gaiarin        GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''          http://www.lanostrafamiglia.it/
  Polo FVG   -   Via della Bontà, 7 - 33078   -   San Vito al Tagliamento (PN)
  marco.gaiarin(at)lanostrafamiglia.it   t +39-0434-842711   f +39-0434-842797

                Dona il 5 PER MILLE a LA NOSTRA FAMIGLIA!
      http://www.lanostrafamiglia.it/index.php/it/sostienici/5x1000
        (cf 00307430132, categoria ONLUS oppure RICERCA SANITARIA)


Reply | Threaded
Open this post in threaded view
|

Re: ODBC Driver 9->11, troubles with dates/NULL...

Inoue, Hiroshi-2


On 2019/07/24 18:49, Marco Gaiarin wrote:
>>> Are you sure that the ODBC driver version makes a difference?
>> Yes. With odbc driver 09.XX works as expected.
> I have to provide some logs?

Could you send me the Mylog output?

regards,
Hiroshi Inoue

---
このメールは、AVG によってウイルス チェックされています。
http://www.avg.com



Reply | Threaded
Open this post in threaded view
|

Re: ODBC Driver 9->11, troubles with dates/NULL...

Marco Gaiarin
Mandi! Inoue, Hiroshi
  In chel di` si favelave...

> Could you send me the Mylog output?

I've tried to trigger the bug and so provide the MyLog but... worked!


Could be that, simply clicking on an options of ODBC configuration (eg,
mylog) and saving it, i've fixed something? Configuration changed in a
incompatible way from 9 to 11?

Boh... sorry...

--
dott. Marco Gaiarin        GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''          http://www.lanostrafamiglia.it/
  Polo FVG   -   Via della Bontà, 7 - 33078   -   San Vito al Tagliamento (PN)
  marco.gaiarin(at)lanostrafamiglia.it   t +39-0434-842711   f +39-0434-842797

                Dona il 5 PER MILLE a LA NOSTRA FAMIGLIA!
      http://www.lanostrafamiglia.it/index.php/it/sostienici/5x1000
        (cf 00307430132, categoria ONLUS oppure RICERCA SANITARIA)