Обсуждение: Serial columns and ADO

As far as I understand, since the driver exposes 'serial' columns
as 'AutoNumber', ADO issues 'SELECT @@IDENTITY' to retreive the new
generated values just after inserts. The driver, in turn, has to modify that
query into 'SELECT currval(...)'.

When working with PG tables from inside MS Access, it all works perfectly!
But here is a small test suite that doesn't work as expected:

----------------------------------------------------------------------------
Server side:

CREATE TABLE t1
(
  col1 serial NOT NULL,
  col2 varchar(50)
)
WITHOUT OIDS;

Client side (Visual Basic 6.0 or VBA):

Sub Test()
  Dim strSql As String
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset

  cn.Open "Provider=MSDASQL.1;Data Source=Test_pg;"

  strSql = "SELECT * FROM t1"

  rs.ActiveConnection = cn
  rs.CursorLocation = adUseClient
  rs.CursorType = adOpenStatic
  rs.LockType = adLockOptimistic
  rs.Source = strSql

  rs.Open

  rs.AddNew
  rs("col2") = "Sample string"
  rs.Update
  MsgBox rs("col1")

  rs.Close
End Sub
----------------------------------------------------------------------------

Try this and you will see '0' in the messagebox shown and the entries in
psqlodbc_xxxx.log similar to the following:

.................
conn=04973D90, query='INSERT INTO "public"."t1" ("col2") VALUES (E'Sample
string')'
conn=04973D90, query='SELECT 0'
.................

My environment:
PostgreSQL 8.1.0 backend
ODBC driver 08.02.0200 Unicode (the latest at the moment of writing) on
WinXP SP2

Any ideas?
Dmitry.

Dmitry Samokhin wrote:
> As far as I understand, since the driver exposes 'serial' columns
> as 'AutoNumber', ADO issues 'SELECT @@IDENTITY' to retreive the new
> generated values just after inserts. The driver, in turn, has to modify that
> query into 'SELECT currval(...)'.
>
> When working with PG tables from inside MS Access, it all works perfectly!
> But here is a small test suite that doesn't work as expected:

Please try the snapshot dll at
    http://www.geocities.jp/inocchichichi/psqlodbc/index.html
.

regards,
Hiroshi Inoue

Hiroshi-san,
I've tried your snapshot (8.2.0201) and found no difference in behaviour. It
sends 'SELECT 0' as before. Is it really corrected in the snapshot? Maybe I
have incorrect driver settings. Which ones affect most of all?
Dmitry.


"Hiroshi Inoue" <inoue@tpf.co.jp> wrote in message
news:4553CAC6.5020001@tpf.co.jp...
> Dmitry Samokhin wrote:
>> As far as I understand, since the driver exposes 'serial' columns as
>> 'AutoNumber', ADO issues 'SELECT @@IDENTITY' to retreive the new
>> generated values just after inserts. The driver, in turn, has to modify
>> that query into 'SELECT currval(...)'.
>>
>> When working with PG tables from inside MS Access, it all works
>> perfectly! But here is a small test suite that doesn't work as expected:
>
> Please try the snapshot dll at
> http://www.geocities.jp/inocchichichi/psqlodbc/index.html
> .
>
> regards,
> Hiroshi Inoue
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Dmitry Samokhin wrote:
> Hiroshi-san,
> I've tried your snapshot (8.2.0201) and found no difference in behaviour. It
> sends 'SELECT 0' as before.

Hmm could you send me the Mylog output ?

regards,
Hiroshi Inoue

Just sent to your e-mail found at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html

Dmitry.


"Hiroshi Inoue" <inoue@tpf.co.jp> wrote in message
news:4554463D.6000301@tpf.co.jp...
> Dmitry Samokhin wrote:
>> Hiroshi-san,
>> I've tried your snapshot (8.2.0201) and found no difference in behaviour.
>> It
>> sends 'SELECT 0' as before.
>
> Hmm could you send me the Mylog output ?
>
> regards,
> Hiroshi Inoue
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

Dmitry Samokhin wrote:
> Just sent to your e-mail found at
> http://www.geocities.jp/inocchichichi/psqlodbc/index.html

Could you try to set the Extra Opts option to 0x2 ?

regards,
Hiroshi Inoue

Hiroshi Inoue wrote:
> Dmitry Samokhin wrote:
>> Just sent to your e-mail found at
>> http://www.geocities.jp/inocchichichi/psqlodbc/index.html
>
> Could you try to set the Extra Opts option to 0x2 ?

With the current snapshot, please.
I was able to reproduce the behavior here.

regards,
Hiroshi Inoue

>> Could you try to set the Extra Opts option to 0x2 ?
>
> With the current snapshot, please.
> I was able to reproduce the behavior here.
>
> regards,
> Hiroshi Inoue

OK, now it works! Thanks for your attention.
But with Extra Opts = 0x0 it still issues 'SELECT 0' and assignes 0 to an
identity field. IMHO, if I don't want to read any server-assigned defaults
for a newly inserted row, any field not set explicitly should contain NULL
both before and after insert operation, not 0.

Regards,
Dmitry.

Dmitry Samokhin wrote:
>>> Could you try to set the Extra Opts option to 0x2 ?
>>>
>> With the current snapshot, please.
>> I was able to reproduce the behavior here.
>>
>> regards,
>> Hiroshi Inoue
>>
>
> OK, now it works! Thanks for your attention.
> But with Extra Opts = 0x0 it still issues 'SELECT 0' and assignes 0 to an
> identity field. IMHO, if I don't want to read any server-assigned defaults
> for a newly inserted row, any field not set explicitly should contain NULL
> both before and after insert operation, not 0.
>

OK try the snapshot dll.
0x2 Extra Opts option is no longer needed for the case.
Also changed to SELECT NULL in case of failure.

regards,
Hiroshi Inoue

> OK try the snapshot dll.
> 0x2 Extra Opts option is no longer needed for the case.

>
> regards,
> Hiroshi Inoue

OK, 'SELECT 0' has been swept out.

> Also changed to SELECT NULL in case of failure.
What failure do you mean?

But there's still another problem. Let our table 't1' be initially empty and
the sequence reset to 1. Consider the following ADO operations:

  rs.AddNew
  rs("col2") = "Sample string"
  rs.Update
  MsgBox rs("col1")

  rs.AddNew
  rs("col1") = 2
  rs("col2") = "Sample string"
  rs.Update
  MsgBox rs("col1")

Look, the second insert operation sets the identity field explicitly. But
the driver still lets 'SELECT currval...' to pass. As a result, my explicit
value '2' is replaced by the currval()=1. Such distortion should be avoided
anyway.

Dmitry.

Hello Dmitry,
"Dmitry Samokhin" <sdld@mail.ru> wrote:

DS> But there's still another problem. Let our table 't1' be initially
DS> empty and the sequence reset to 1. Consider the following ADO operations:
DS>
DS>   rs.AddNew
DS>   rs("col2") = "Sample string"
DS>   rs.Update
DS>   MsgBox rs("col1")
DS>
DS>   rs.AddNew
DS>   rs("col1") = 2
DS>   rs("col2") = "Sample string"
DS>   rs.Update
DS>   MsgBox rs("col1")
DS>
DS> Look, the second insert operation sets the identity field
DS> explicitly. But the driver still lets 'SELECT currval...' to pass.
DS> As a result, my explicit value '2' is replaced by the currval()=1.
DS> Such distortion should be avoided anyway.

Generally, if you declared the column type as serial, you should not set it
explicitly while inserting records. In your example, rs.Update does not
guarantee changing the cursor position to the record inserted most recently.
(At least, with DAO against MS Access database.)  IMHO, it is not the ODBC
driver to blame.
--
Sincerely,
Dmitriy Ivanov