Обсуждение: [ODBC] Update

Hello,

We are using psqlodbc to update a Postgresql database from embedded sql
for cobol.
We have a strange behaviour : the first time we update, the update is
correct (sqlcode 0). I we try the same update, the system return sqlcode
= 100 and sqlstate = 2000.

postgresql.x86_64                      9.2.18-1.el7
postgresql-odbc.x86_64                 09.03.0100-2.el7
unixODBC.x86_64                        2.3.1-11.el7
under Centos 7.2 64 bits.

Here an example to simulaute an upsert :

           exec sql
            UPDATE testtable SET
                num = :testtable-num
                , libelle = :testtable-libelle:testtable-libelle-NULL
                WHERE num = :testtable-num;
           end-exec
           if sqlcode = 100 then
             exec sql
              INSERT into testtable
                (
                num
                , libelle
               )
               SELECT
                  :testtable-num
                  , :testtable-libelle:testtable-libelle-NULL
              WHERE NOT EXISTS (SELECT 1 FROM testtable WHERE num =
:testtable-num);
             end-exec
           end-if
           if sqlcode = 0 then
              exec sql commit end-exec
           end-if

If I call two times the above code with the same content of
testtable-num and testtable-libelle, the first attempt is successful and
all other attempts, even changing the content of the variables, fails.

It was working good under Centos 5.9 32 bits, and Postgres 9.2.

Any idea why ?

Kind regards,

Alain

On Fri, Jun 30, 2017 at 11:31 PM, koffi BADOH <koff10@hotmail.com> wrote:
> Is there a way to make visible a materialized view through ODBC.
> I'm using postgres 9.4  and msaccess . All views  on Postgres database  are
> visible through ODBC for access
> except   materialized views.

There has been for some time a patch on this mailing list to do that:
http://www.postgresql.org/message-id/CAB7nPqR0apHpiPAi4J2e3oR2jZ8MREyJAzxdWMjrr4h5PsRp4w@mail.gmail.com
Inoue-san, Saito-san, could you integrate that in the tree? It is not
the first time this support is requested.
--
Michael

Hi Michael,

On 2017/07/02 16:45, Michael Paquier wrote:
> On Fri, Jun 30, 2017 at 11:31 PM, koffi BADOH <koff10@hotmail.com> wrote:
>> Is there a way to make visible a materialized view through ODBC.
>> I'm using postgres 9.4  and msaccess . All views  on Postgres database  are
>> visible through ODBC for access
>> except   materialized views.
> There has been for some time a patch on this mailing list to do that:
> http://www.postgresql.org/message-id/CAB7nPqR0apHpiPAi4J2e3oR2jZ8MREyJAzxdWMjrr4h5PsRp4w@mail.gmail.com
> Inoue-san, Saito-san, could you integrate that in the tree? It is not
> the first time this support is requested.

OK I would take care of it.

regards,
Hiroshi Inoue

Hi Michael,

On 2017/07/02 16:45, Michael Paquier wrote:

On Fri, Jun 30, 2017 at 11:31 PM, koffi BADOH <koff10@hotmail.com> wrote:

Is there a way to make visible a materialized view through ODBC.
I'm using postgres 9.4  and msaccess . All views  on Postgres database  are
visible through ODBC for access
except   materialized views.

There has been for some time a patch on this mailing list to do that:
http://www.postgresql.org/message-id/CAB7nPqR0apHpiPAi4J2e3oR2jZ8MREyJAzxdWMjrr4h5PsRp4w@mail.gmail.com
Inoue-san, Saito-san, could you integrate that in the tree? It is not
the first time this support is requested.

OK I would take care of it.

On Tue, Jul 4, 2017 at 7:46 AM, Inoue, Hiroshi
<h-inoue@dream.email.ne.jp> wrote:
> Done.

Thanks, Inoue-san.
--
Michael