Thread: The way Access/ODBC does updates to records

Hi,

When I link postgreSQL tables to access, and try to modify a record, I
found that odbc sends a query to postgresql containing a lot of fields
in the WHERE clause. For example:

I have a table names with fields id, name, surname, address.
When I modify an existing record changing the name from Davide to
Daniele, I expect that odbc sends a query like this:
UPDATE names SET name='Daniele' WHERE id=1;
or, in the case it looks for contamporary changes:
UPDATE names SET name='Daniele' WHERE id=1 AND name='Davide';

Instead I see it uses:
UPDATE names SET name='Daniele' WHERE id=1 AND name='Davide' AND
surname='Romanini' AND address='My address';

It wouldn't be a problem, but with some datatypes (dates, doubles for
example) and sometimes with triggers that change data before updates,
it has problems like the "Another user is changing the same data".

I want to know if there's a way to force access (or odbc) to use only
primary key fields in the WHERE clause for un UPDATE (there aren't
reasons to use other fields).

Thanks, Romaz
--
Davide Romanini

Try using row versioning.
hth,
- Stuart

> -----Original Message-----
> From: Davide Romanini [mailto:romaz@libero.it]
> Sent: 17 March 2003 19:16
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] The way Access/ODBC does updates to records
>
>
> Hi,
>
> When I link postgreSQL tables to access, and try to modify a
> record, I
> found that odbc sends a query to postgresql containing a lot
> of fields
> in the WHERE clause. For example:
>
> I have a table names with fields id, name, surname, address.
> When I modify an existing record changing the name from Davide to
> Daniele, I expect that odbc sends a query like this:
> UPDATE names SET name='Daniele' WHERE id=1;
> or, in the case it looks for contamporary changes:
> UPDATE names SET name='Daniele' WHERE id=1 AND name='Davide';
>
> Instead I see it uses:
> UPDATE names SET name='Daniele' WHERE id=1 AND name='Davide' AND
> surname='Romanini' AND address='My address';
>
> It wouldn't be a problem, but with some datatypes (dates, doubles for
> example) and sometimes with triggers that change data before updates,
> it has problems like the "Another user is changing the same data".
>
> I want to know if there's a way to force access (or odbc) to use only
> primary key fields in the WHERE clause for un UPDATE (there aren't
> reasons to use other fields).
>
> Thanks, Romaz
> --
> Davide Romanini
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

DISCLAIMER:The information in this message is confidential and may be legally privileged. It is intended solely for the addressee.  Access to this message by anyone else is unauthorised.  If you are not the intended recipient, any disclosure, copying, or distribution of the message, or any action or omission taken by you in reliance on it, is prohibited and may be unlawful.  Please immediately contact the sender if you have received this message in error. Thank you.