From: Torsten Lange <tlange@gwdg.de>
To: pgsql-sql@postgresql.org
Subject: Re: Access Update error: solution.
Date: Fri, 21 May 2010 18:22:24 +0200
User-Agent: KMail/1.10.3 (Linux/2.6.27.45-0.1-default; KDE/4.1.3; x86_64; ; )
References: <ed7837818fc165ea7ffdc2efadb4ddc3.squirrel@mailbox.gwdg.de>
	<4BF64129.10604@postnewspapers.com.au>
	<8585BA53443004458E0BAA6134C5A7FB0642C43A@EGEXCMB01.oww.root.lcl>
In-Reply-To: <8585BA53443004458E0BAA6134C5A7FB0642C43A@EGEXCMB01.oww.root.lcl>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <201005211822.24575.tlange@gwdg.de>


I think I also found one useful switch. In the ODBC-driver's setting there is 
one "extra"  or so field, where 4 possible values can be entered: 0x0, 0x1, 
0x2... If I remember right with 0x2 the driver mimics an SQL-Server auto 
increment, which Access acknowledges.

However, does anyone knows good resources to learn more about triggers? Can 
also be a book to buy. I need an after insert/update trigger to update a 
PostGIS geometry column of a table after someone has typed in a x,y-location 
into x and y columns.

Thx,Torsten


Am Freitag, 21. Mai 2010 17:23:12 schrieb Little, Douglas:
> A comment on the MS access/PG problem.
>
> We experienced this problem as well and I isolated the problem to access's
> support of timestamp. PG defaults to timestamp(6),   While access only
> supports timestamp(2).
>
> When access fills the grid from the table, the values are truncated to
> ts(2).
>
> As the grid is updated, a sql update statement is prepared in the
> background. all fields except for those updated(by the grid) are used in
> the construction of the where clause. For most cases this means all of
> timestamp columns are compared.  Ie Update remote x
> Where local.ts1 = remote.ts1  and .....
> The PG timestamp is compared to the local access copy.
> Because of the truncation  no row is found in PG, and Access reports that
> someone else updated the record.
>
> We found the solution to be that all timestamp columns needed to be set to
> timestamp(0).  Once we did this the issue went away.
>
> Back to the original problem.
> Serial is just a notational convenience. When created the pg engine will
> rewrite the statement to 1) Generate a sequence, then 2) create column as
> int and a default value as nextval(seqName).   The datatype remains
> recorded as Serial, which is not a standard SQL datatype.
>
> You can manually do the same yourself - create the sequence, then assign
> the column default, or easier - create the table as serial, then change the
> datatype to int. PG will automatically create the seq and assign the
> default.  Changing the datatype will leave the default and sequence intact.
>
> Once datatype is int, Access will recognize it.
>
> Doug
>
>
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Craig Ringer Sent:
> Friday, May 21, 2010 3:16 AM
> To: Richard Broersma
> Cc: tlange@gwdg.de; pgsql-sql@postgresql.org
> Subject: Re: [SQL]
>
> On 21/05/2010 9:56 AM, Richard Broersma wrote:
> > On Thu, May 20, 2010 at 5:52 PM,<tlange@gwdg.de>  wrote:
> >> I'm new to triggers in PostgreSQL. I have to create a trigger on insert
> >> to increment a sequence to overcome MS-Access' limitation in
> >> acknowledging serial "datatype".
> >
> > Uh?  Access doesn't need to acknowledge the serial datatype.
> > At-least in recent versions auto increment is recognized by MS-Access
> > just fine (I'm guessing this is due to the Return clause which the
> > ODBC driver automatically calls).
>
> Really?
>
> I had problems with Access complaining that the object it just inserted
> had vanished, because the primary key Access had in memory (null) didn't
> match what was stored (the generated PK). I had to fetch the next value
> in the PK sequence manually and store it in Access's record before
> inserting it to work around this.
>
> I wouldn't be surprised if this actually turned out to just require some
> bizarre ODBC driver parameter change, but I never figured it out and I
> couldn't find any info about it on the 'net.
>
> For the original poster: I posted some information about this at the
> time I was working on it, so search the archives of this list for MS
> Access.
>
> I eventually ditched Access entirely as the user who was demanding the
> use of MS Access relented (phew!), so I put together a simple web-app to
> do what they wanted in a day. Hopefully I'll never need to go near ODBC
> again, because it's a truly "special" way to talk to PostgreSQL.
>
> --
> Craig Ringer
>