Skip site navigation (1) Skip section navigation (2)

Re: UPDATE table via ODBC fails.

From: Ken Benson <ken(at)infowerks(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: Re: UPDATE table via ODBC fails.
Date: 2012-04-12 00:25:02
Message-ID: 4F8620DE.5070109@infowerks.com (view raw or flat)
Thread:
Lists: pgsql-odbc
UPDATE:

This must have something to do with the multiple table involvement.

I modified my 'insert' command to include the updated field on the 
initial insert.

    INSERT INTO tmp_rxpurge (rxnum,dt_wrt,dt_lastfill,b_purge)
    SELECT "RXNUM","DATE",rf.dt_lastfill,'f'
    FROM rx
    LEFT JOIN  (SELECT "RXNUM" AS rxnum, Max("DATE")  as dt_lastfill
    FROM transactions GROUP BY "RXNUM")
                 as rf on rf.rxnum=rx."RXNUM"


Then - created three additional update steps ... they all worked via the 
ODBC connector...

 1. UPDATE tmp_rxpurge SET dt_lastact=dt_wrt
 2. UPDATE tmp_rxpurge SET dt_lastact=dt_lastfill where dt_lastfill IS
    NOT NULL and dt_lastfill > dt_lastact
 3. UPDATE tmp_rxpurge SET b_purge='t' where dt_lastact < '2010-01-01'


I'm still puzzled why the first method didn't work.

Ken

On 4/11/2012 3:01 PM, Ken Benson wrote:
> I have a fairly straightforward routine which uses an ODBC connection 
> to do 4 steps:
>
>   * DROP TABLE IF EXISTS tmp_rxpurge
>   * CREATE TABLE tmp_rxpurge (rxnum VARCHAR(300),dt_wrt
>     DATE,dt_lastfill DATE,dt_lastact DATE, b_purge BOOLEAN)
>   * INSERT INTO tmp_rxpurge (rxnum,dt_wrt,b_purge) SELECT
>     "RXNUM","DATE",'f' FROM rx
>   * UPDATE tmp_rxpurge as t SET dt_lastfill = rf.dt_lastfill FROM
>     (SELECT "RXNUM" AS rxnum, Max("DATE") as dt_lastfill FROM
>     transactions GROUP BY "RXNUM") as rf WHERE rf.rxnum=t.rxnum
>
>
> The first three commands are executed perfectly as expected.
> The update fails to do anything.
> There is no error generated.
> I have enabled 'logging' in the ODBC connector - and I don't see 
> anything out of the ordinary in the short-log. (The long-log - I have 
> no idea what all that stuff means.)
>
> If I copy that last command into a psql window - it executes - and 
> works fine.
>
> But - it will not work via the ODBC connector.
>
> What's going on?
>
> Ken

In response to

pgsql-odbc by date

Next:From: Hiroshi InoueDate: 2012-04-12 13:24:09
Subject: Re: Passing numeric Bind variables to ODBC driver convers to "Double precision"
Previous:From: Ken BensonDate: 2012-04-11 22:01:36
Subject: UPDATE table via ODBC fails.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group