Re: UPDATE statement value mutation

Richard Broersma wrote:
> I stripped the .bmp attachment since it seems emails with attachments
> aren't distributed.
>
> On Mon, Jul 5, 2010 at 3:24 PM, Richard Broersma
> <richard.broersma@gmail.com> wrote:
>> Notice the attached bitmap image.  I'm trying to change a value from
>> my MS-Access front-end from 0 to 99 using an update-able query.

Could you send me the Mylog output?

regards,
Hiroshi Inoue

>> However, when the actual update statement reaches PostgreSQL the value
>> of 99 is some how changed to 11.  Is this a bug in Access or the ODBC
>> driver?
>>
>>
>>
>> Postgresql Version:
>>                           version
>> -------------------------------------------------------------
>>  PostgreSQL 8.4.4, compiled by Visual C++ build 1400, 32-bit
>>
>>
>> ODBC driver version:
>> ------------------------------
>> [0.002]Driver Version='08.04.0200,200912260001' linking 1500 static
>> Multithread library
>> [0.003]Global Options: fetch=100, socket=4096, unknown_sizes=0,
>> max_varchar_size=255, max_longvarchar_size=8190
>> [0.003]                disable_optimizer=0, ksqo=1, unique_index=1,
>> use_declarefetch=0
>> [0.004]                text_as_longvarchar=1,
>> unknowns_as_longvarchar=0, bools_as_char=1 NAMEDATALEN=64
>> [0.005]                extra_systable_prefixes='dd_', conn_settings=''
>> conn_encoding=''
>> [0.087]    [ PostgreSQL version string = '8.4.4' ]
>> [0.087]    [ PostgreSQL version number = '8.4' ]
>> [0.090]conn=088A4008, query='select oid, typbasetype from pg_type
>> where typname = 'lo''
>> [0.099]    [ fetched 0 rows ]
>> [0.101]    [ Large Object oid = -999 ]
>> [0.102]    [ Client encoding = 'UTF8' (code = 6) ]
>>
>>
>> MS-Access 2002 (10.6771.6858) SP3
>>
>>
>> Here is the statement that reaches Postgresql.
>>
>> 2010-07-05 15:21:26 PDTLOG:  statement: BEGIN;UPDATE
>> "public"."drawings" SET "drawing_type"=11  WHERE "drawingnbr" =
>> E'E-4400-60429D-S1' AND "xmin" = 23018
>> 2010-07-05 15:21:26 PDTERROR:  insert or update on table "drawings"
>> violates foreign key constraint "drawings_drawing_type_fkey"
>> 2010-07-05 15:21:26 PDTDETAIL:  Key (drawing_type)=(11) is not present
>> in table "drawing_types".
>> 2010-07-05 15:21:26 PDTSTATEMENT:  BEGIN;UPDATE "public"."drawings"
>> SET "drawing_type"=11  WHERE "drawingnbr" = E'E-4400-60429D-S1' AND
>> "xmin" = 23018
>> 2010-07-05 15:21:26 PDTLOG:  statement: ROLLBACK
>>
>> --
>> Regards,
>> Richard Broersma Jr.
>>
>> Visit the Los Angeles PostgreSQL Users Group (LAPUG)
>> http://pugs.postgresql.org/lapug