ADO and sequences

Hi,

I'd like to write to PG from Access2000 with ADODB.
I got it running, but I'd like to know the sequence-nr that gets used
when a new record is created.

I open a recordset with
.CursorLocation = adUseClient
.CursorType = adOpenStatic
.LockType = adLockOptimistic
then I call its AddNew method, the columns get filled and the recordset
is closed.
The primary key is a SERIAL.
Is there a way to read the pkey before I commit the record?
Access knows it's Autonumbers after AddNew when I use it's JET db-engine.

A "select currval...." after the AddNew yields
Error while executing the query;
ERROR: currval of sequence "personen_person_id_seq" is not yet defined
in this session

I'd rather not use currval anyways. I'd prefer to have the program not
know to much of the structure that sits in the db backend, like the
sequence's name in this case.

Can I get something like this :

rs.AddNew
lngID = rs!id

rs!field1 = value1
rs!field2 = value2
.....
rs.update
rs.close


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: ADO and sequences

Easiest way I've found is to read NextVal on the relevant sequence, and use
the value you get for the key field.

Since the field will be loaded, the "default" function that assigns the
number, kind of like an Access Autonumber - will *not* override the value
you manually insert.

So:
Get nextval on sequence
..addnew
recordset!idfield = thenextvalyougot




At 11:38 AM 8/22/06, Andreas wrote:
>Hi,
>
>I'd like to write to PG from Access2000 with ADODB.
>I got it running, but I'd like to know the sequence-nr that gets used when
>a new record is created.
>
>I open a recordset with
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
>then I call its AddNew method, the columns get filled and the recordset is
>closed.
>The primary key is a SERIAL.
>Is there a way to read the pkey before I commit the record?
>Access knows it's Autonumbers after AddNew when I use it's JET db-engine.
>
>A "select currval...." after the AddNew yields
>Error while executing the query;
>ERROR: currval of sequence "personen_person_id_seq" is not yet defined in
>this session
>
>I'd rather not use currval anyways. I'd prefer to have the program not
>know to much of the structure that sits in the db backend, like the
>sequence's name in this case.
>
>Can I get something like this :
>
>rs.AddNew
> lngID = rs!id
>
> rs!field1 = value1
> rs!field2 = value2
>....
>rs.update
>rs.close
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: ADO and sequences

Thanks, I did it that way but I'd like to have a workaround that doesn't
tie my application to one single RDBMS.
My initial plan was to create an adodb-connection to the current db
backend on start of my application and the rest of the program wouldn't
need to be aware what RDBMS actually manages the data in this session.
This would be postgres in my LAN and JET directly accessing MDB-files on
a notebook pc where I'd rather not always run a server in the background.


Johann schrieb:
> Easiest way I've found is to read NextVal on the relevant sequence,
> and use the value you get for the key field.
>
> Since the field will be loaded, the "default" function that assigns
> the number, kind of like an Access Autonumber - will *not* override
> the value you manually insert.
>
> So:
> Get nextval on sequence
> .addnew
> recordset!idfield = thenextvalyougot
>

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: ADO and sequences

If you want autoincrement fields to be fully portable, then you will
have to do this client side.
You can do this by creating a table where youhold the next values, and
retrieve from them. This is similar to the way PosgtreSQL sequences
work internally.
Or, one other way it to have a record where yo identify the backend
type, and depending on the backend run specific code snippets.

=20

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org=20
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Andreas
> Sent: Tuesday, August 22, 2006 4:49 PM
> To: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] ADO and sequences
>=20
>=20
> Thanks, I did it that way but I'd like to have a workaround=20
> that doesn't tie my application to one single RDBMS.
> My initial plan was to create an adodb-connection to the=20
> current db backend on start of my application and the rest of=20
> the program wouldn't need to be aware what RDBMS actually=20
> manages the data in this session.
> This would be postgres in my LAN and JET directly accessing=20
> MDB-files on a notebook pc where I'd rather not always run a=20
> server in the background.
>=20
>=20
> Johann schrieb:
> > Easiest way I've found is to read NextVal on the relevant sequence,=20
> > and use the value you get for the key field.
> >
> > Since the field will be loaded, the "default" function that assigns=20
> > the number, kind of like an Access Autonumber - will *not* override=20
> > the value you manually insert.
> >
> > So:
> > Get nextval on sequence
> > .addnew
> > recordset!idfield =3D thenextvalyougot
> >
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>=20
> http://www.postgresql.org/docs/faq
>=20

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: ADO and sequences

One way - ugly - but maybe OK:

To create a new record, do a "insert" statement with enough known unique
values that you can the re-select it, then re-select it, edit out any funny
values, and work with the recordset / present the form.

The different ways the ID # were loaded don't matter that way. Note that
defining the ID field in PostgreSQL as the primary key will prevent some
other weird ADO behavior where Access via JET knows which records to update
but PostgreSQL via ODBC doesn't.



At 04:49 PM 8/22/06, Andreas wrote:

>Thanks, I did it that way but I'd like to have a workaround that doesn't
>tie my application to one single RDBMS.
>My initial plan was to create an adodb-connection to the current db
>backend on start of my application and the rest of the program wouldn't
>need to be aware what RDBMS actually manages the data in this session.
>This would be postgres in my LAN and JET directly accessing MDB-files on a
>notebook pc where I'd rather not always run a server in the background.
>
>
>Johann schrieb:
>>Easiest way I've found is to read NextVal on the relevant sequence, and
>>use the value you get for the key field.
>>
>>Since the field will be loaded, the "default" function that assigns the
>>number, kind of like an Access Autonumber - will *not* override the value
>>you manually insert.
>>
>>So:
>>Get nextval on sequence
>>.addnew
>>recordset!idfield = thenextvalyougot
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: ADO and sequences

Andreas wrote:
> Hi,
>
> I'd like to write to PG from Access2000 with ADODB.
> I got it running, but I'd like to know the sequence-nr that gets used
> when a new record is created.
>
> I open a recordset with
> .CursorLocation = adUseClient
> .CursorType = adOpenStatic
> .LockType = adLockOptimistic
> then I call its AddNew method, the columns get filled and the recordset
> is closed.
> The primary key is a SERIAL.
> Is there a way to read the pkey before I commit the record?
> Access knows it's Autonumbers after AddNew when I use it's JET db-engine.
>
> A "select currval...." after the AddNew yields
> Error while executing the query;
> ERROR: currval of sequence "personen_person_id_seq" is not yet defined
> in this session
>
> I'd rather not use currval anyways. I'd prefer to have the program not
> know to much of the structure that sits in the db backend, like the
> sequence's name in this case.
>
> Can I get something like this :
>
> rs.AddNew
> lngID = rs!id

Try to get rs!id here is meaningless but

> rs!field1 = value1
> rs!field2 = value2
> ....
> rs.update

you can get rs!id here maybe.
Am I misunderstanding your point ?

> rs.close

regards,
Hiroshi Inoue

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: ADO and sequences

Hiroshi Inoue schrieb:
> Andreas wrote:
>> rs.AddNew
>> lngID = rs!id
> Try to get rs!id here is meaningless but
>> rs!field1 = value1
>> rs!field2 = value2
>> ....
>> rs.update
> you can get rs!id here maybe.
> Am I misunderstanding your point ?
>> rs.close

I tried this before my initial mail.
Even though in the table definition is
id serial not null,
primary key (id)
I still get rs!id = NULL after rs.addnew as well as after rs.update

Maybe your driver behaves differently to the still official 08.01.0200?

The problem affects not only the serial column but also a timestamp(0)
that defaults to NOW() and another timestamp(0) that gets set by a
trigger after every update.
All 3 debug.print as NULL in the recordset after the UPDATE.
Obviously those dynamically created values get into the table since I
see them with pgAdmin but the newly created record isn't automatically
read back into Access's adodb.recordset object.

BTW there is a difference between adUseClient and adUseServer.
With adUseServer isEmpty(r!id) shows TRUE and the automatic columns
show nothing with debug.print.
Whereas with adUseClient isEmpty(r!id) is FALSE and the 3 columns
print as NULL.

Do you have further advise?



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

http://archives.postgresql.org

Re: ADO and sequences

Andreas wrote:
>
>
> Hiroshi Inoue schrieb:
>> Andreas wrote:
>>> rs.AddNew
>>> lngID = rs!id
>> Try to get rs!id here is meaningless but
>>> rs!field1 = value1
>>> rs!field2 = value2
>>> ....
>>> rs.update
>> you can get rs!id here maybe.
>> Am I misunderstanding your point ?
>>> rs.close
>
> I tried this before my initial mail.
> Even though in the table definition is
> id serial not null,
> primary key (id)
> I still get rs!id = NULL after rs.addnew as well as after rs.update
>
> Maybe your driver behaves differently to the still official 08.01.0200?

The driver doesn't support Updatable Cursors.
Please install the 8.2.0002 version from the site
http://pgfoundry.org/projects/psqlodbc/
and turn on the Updatable Cursors option.

After that you can also use the snapshot driver at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html .
The registration is the same as the 8.2.0002 version and you
can simply replace the dll psqlodbc35w.dll.

Please note that you would be able to see the result after calling
update() not after Addnew().

regards,
Hiroshi Inoue

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

http://archives.postgresql.org

Re: ADO and sequences

Hiroshi Inoue schrieb:
>
> The driver doesn't support Updatable Cursors.
> Please install the 8.2.0002 version from the site
> http://pgfoundry.org/projects/psqlodbc/
> and turn on the Updatable Cursors option.
I'll try this one later today.

Could you give me the Updatable Cursors option as a string that I can
include in a connection string for a dsn-less connection, please?

Thanks for your assistnce :)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: ADO and sequences

Andreas wrote:
>
>
> Hiroshi Inoue schrieb:
>>
>> The driver doesn't support Updatable Cursors.
>> Please install the 8.2.0002 version from the site
>> http://pgfoundry.org/projects/psqlodbc/
>> and turn on the Updatable Cursors option.
> I'll try this one later today.
>
> Could you give me the Updatable Cursors option as a string that I can
> include in a connection string for a dsn-less connection, please?


UpdatableCursors=1
or
C4=1 (if you prefer a shorter string)
..

regards,
Hiroshi Inoue

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

http://archives.postgresql.org