DevHeads.net

UPDATE table via ODBC fails.

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

Comments

Re: UPDATE table via ODBC fails.

By Richard Broersma at 04/13/2012 - 11:22

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?

Re: UPDATE table via ODBC fails.

By Ken Benson at 04/11/2012 - 20:25

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: