Re: Sequence and nextval problem

Tk421 wrote:
> Hello everybody.
>
> I've got an vb aplication that uses an Access database. I'm trying to
> convert the database to postgres. The conversion was done ok, but i've
> got a little problem that i don't know how to solve. Let's see if anyone
> can help me.
>
> The conversion from access database to postgres worked fine.
> Everithing it's ok. But now, when i use my database i've found a problem
> with sequences. In the conversion, the "autonumeric" fields from access
> have been converted to sequences, everithing ok in a first view. The
> problem comes because the autonumeric fields in access always return the
> last value of the table +1, but postgres no. Postgres returns "lost" (i
> don't know how to call them) values.

Access doesn't understand server-side generated keys very well.

You need to use a Before Insert event on the form to issue a passthrough
query using Visual Basic that invokes nextval('seqname') to get the next
ID from the sequence, then fills the primary key field with the return
value. That way, when Access submits the INSERT statement it'll include
the primary key generated by Pg, so you land up with the same effect as
if you left it DEFAULT, except that Access knows what the new ID will be
and doesn't get confused when it can't find the record after inserting it.

Access 2007 has a special quirk, where it'll execute any passthrough
query (including stored procedures) invoked via Visual Basic twice if it
returns a resultset. Consequently, you must EITHER retrieve a return
value from a query OR invoke one with side effects, not both.

If you call nextval(...) and read the return value, you'll actually have
two IDs generated from the sequence, the first of which is discarded.
You probably don't care about this, but it's probably best to avoid the
problem anyway. What you need to do is fire the
SELECT nextval('seqname')
query with ReturnsRecords=False . You then invoke:
SELECT currval('seqname')
to read the generated ID. currval(...) will be called twice, but you
don't care as it has no side-effects.

I've attached some Visual Basic code that demonstrates this. It's easily
adapted to other stored procedures. In fact, I've included a sample of
another stored procedure invocation in another routine; just delete it.
form_module.txt contains an example use of the code on a form. It should
be included in the module for the form(s) you want to handle, and
adapted to use the right field name for the primary key. The other file,
passthrough.txt contains a VB module named "passthrough" that contains
the actual passthrough query handling code used by the before insert
routine in the form module.

Note that you're in for some other exciting issues if you use access
with Pg. You will need to load a set of casts and operators to ensure
that Access's odd handling of Boolean values works; you'll need to set
some specific ODBC driver options; you may want to set the
transform_null_equals option in Pg to let Access use it's horrible
"value = NULL" tests, etc. See:

http://www.postgresonline.com/journal/index.php?/archives/24-Using-MS-Access-with-PostgreSQL.html

for sample operator/cast definitions. There are some notes in the top of
the "passthrough.txt" module I attached that explain the ODBC driver
options you'll want.

--
Craig Ringer