Re: Access - ODBC - serial problem...

Is there a Postgresql ODBC driver guru that could give us an idea for a driver 
modification? I don't know the exact job of an ODBC driver, nor how it connects 
to Postgresql, so I cannot tell if it would be possible to modify the ODBC 
driver, and do something like a "SELECT @@IDENTITY" (which is a SQL Server 2000 
call, correct).

Since Microsoft is behind both Access and SQL Server, maybe nothing can be done 
in the driver itself, and I'll have to handle my insertions by hand, through 
pl/pgsql. But really, that's a serious limitation...


-----Message d'origine-----
De : pgsql-odbc-owner@xxxxxxxxxxxxxx [mailto:pgsql-odbc-owner@xxxxxxxxxxxxxx] 
De la part de Steve Jorgensen
Envoyé : vendredi, 9. avril 2004 03:16
À : pgsql-odbc@xxxxxxxxxxxxxx
Objet : Re: [ODBC] Access - ODBC - serial problem...

Having fought with this before myself, I can tell you that this can be made to 
work, but it's not exactly ideal.  With an Access front-end, you try to make 
the back-end plug-and play.  You don't want to have to execute a pass-through 
query to be able to add a row through a bound form.  It would certainly be nice 
if the ODBC driver for PostgreSQL would work with Access like other drivers do 
(hovever that works), and allow Access to see the new Serial value after adding 
a row.

On Thursday, April 08, 2004 10:12 AM, Ray Aspeitia [SMTP:aspeitia@xxxxxxxxx] 
wrote:
>
> I haven't tried this in Access, but why not fetch the record 
> identifier first then insert the record like below. Retrieving the id 
> first makes sure that you get the right id, because you might be in a 
> pooled connection environment, and you might not get the right id 
> after the insert.
>
>
> SELECT nextval('schema.sequence_for_table_record_id') AS record_id;
>
> INSERT INTO test (record_id,code) VALUES ([retrieved record_id],20);
>
> or
>
> INSERT INTO test (record_id,code) VALUES 
> (currval('schema.sequence_for_table_record_id'),20);
>
>
> Ray A.
>
>
> >Hello,
> >
> >I have made some more tests regarding my precedent post "[ODBC] 
> >Access - ODBC - index strange bug", and in fact it is not 
> >index-related. The bug is just more visible when an index is used, 
> >apparently.
> >
> >The problem comes from the serial identifier, which is not know from 
> >the client at the time the record is being inserted.
> >
> >With the table described below, imagine I do, from the client:
> >
> >insert into test (code) VALUES (20);
> >
> >How does the client know the id that has been given to the record?
> >With ethereal, I could see Access fetches the id by doing a
> >
> >select id from test where code = 20"
> >
> >Of course, another record has the same code, and the wrong id is 
> >being fetched back. This explains what I have noticed, and that is 
> >explained below...
> >
> >Is there a solution to that, except inserting records with PL-PGSQL, 
> >which I already do in some cases, by the way...?
> >
> >Is that an Access-related problem? Does it work with SQL Server?
> >
> >
> >Thanks for your feedback...
> >
> >Philippe
> >
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)