Обсуждение: 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
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
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 >
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. > -----Original Message----- > From: pgsql-odbc-owner@postgresql.org > [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 > > > 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 >
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
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
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?
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
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 :)
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