Обсуждение: UPDATE table via ODBC fails.

• 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

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"

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 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

On Wed, Apr 11, 2012 at 3:01 PM, Ken Benson <ken@infowerks.com> wrote:

> 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

> 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.)

You'll find that reading the logs produced by the PostgreSQL server to
be much more informative about the miss behavior your experiencing.

Can you post the logs generated by PostgreSQL, when you attempt this
UPDATE statement?

--
Regards,
Richard Broersma Jr.