Re: Access - ODBC - serial problem...

To state the obvious, MS Access thinks the world is MSSQL,
ODBC interface or not. At Simba (ODBC Driver kit company),
proofing drivers specifically against MS Access weirdness
was our "competitive edge". :-)

SHORT
If you don't have a unique key for MyTable (other than the identity)
but you're feeling like taking a few risks,
then the best bet I've found amounts to (pardon the T-SQL):

declare @id int
select @id = max(IDENTITYCOL) from MyTable
insert MyTable(code,...) values(20, ...)
select @id = IDENTITYCOL from MyTable
where IDENTITYCOL > @id and code = 20 ...
-- IDENTITYCOL is a T-SQL specific moniker for "the"
-- serial column of a table.
LONG
SELECT @@IDENTITY (or safer in MSSQL: SELECT SCOPE_IDENTITY() )
can only be emulated in the PG server, not the driver.
The driver will never know enough to determine what table
(hence sequence) this is the nextval for.
Note that in MSSQL, an insert can cause a trigger to fire
that inserts INTO A DIFFERENT TABLE: this will set
@@IDENTITY to the nextval for that OTHER TABLE.
SCOPE_IDENTITY() is "safer", since it deals with the last
table insertion in the current stored procedure.

So, if you want to emulate this feature, it means:
- every connection(server process) needs a qua-global nextval storage
- every procedure local context (no matter what language !?)
needs a nextval
- nextval has to be changed to update this.

How likely does that sound as a to-do?
------
In case you're wondering how MS Access could possibly survive
working with any dataservers other than MSSQL, I never thought
there was really any such intention. MS Access uses
proprietary ODBC entry points and Connection/Statement
attributes (though it's mercifully silent if it tries them
and an innocent non-MS driver errors out).

Philippe Lang wrote:
> 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(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org] De la part de Steve Jorgensen
> Envoyé : vendredi, 9. avril 2004 03:16
> À : pgsql-odbc(at)postgresql(dot)org
> 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(at)sells(dot)com] 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);
>>

>>>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?