Обсуждение: ODBC3, VB, cursortype, bug and transactions

I installed ODBC3. After some tests I have some questions: (excuse my poor english)

With this code:

    Set rstCustomers = New ADODB.Recordset
    rstCustomers.CursorType = adOpenKeyset
    rstCustomers.LockType = adLockOptimistic
    rstCustomers.Open "SELECT * FROM customers", cnnGPro

    rstCustomers.AddNew
    rstCustomers.Fields("name").Value = "Joe"
    rstCustomers.Fields("age").Value = 20
    rstCustomers.Update

    nID = rstCustomers.Fields("id_customer").Value

I can get my id_customer, but if I change the cursor type to adOpenDynamic, I won't get.

I think that I found a bug:

    Set rstCustomers = New ADODB.Recordset
    rstCustomers.CursorType = adOpenKeyset
    rstCustomers.LockType = adLockOptimistic

    cnnGPro.BeginTrans

    rstCustomers.Open "SELECT * FROM customer WHERE substr(upper(name), 1, 1) = 'M'", cnnGPro

    Debug.Print rstCustomers.RecordCount

    Do While Not rstCustomers.EOF
        rstCustomers.Delete
        rstCustomers.MoveNext
        Debug.Print rstCustomers.RecordCount
    Loop

    rstCustomers.Close

    cnnGPro.RollbackTrans

After I deleted the last record I get a error (80040e23) at rstCustomers.MoveNext.
Prior after the last delete I get:

    ? rstCustomers.EOF, rstCustomers.BOF
    False         False

After the error at rstCustomers.MoveNext, I get:

    ? rstCustomers.EOF, rstCustomers.BOF
    True          False

If I change the cursor type to adOpenDynamic no error will happen.
So if I need my id_customer I have to use adOpenKeyset and if I need to delete a record I have to use adOpenDynamic.

More one, how can I know if a transaction is serializable?
I tried

   cnnGPro.Execute "SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL SERIALIZABLE", , adCmdText +
adExecuteNoRecords  
   cnnGPro.Execute "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE", , adCmdText
   cnnGPro.IsolationLevel = adXactSerializable

but this last one fail when I did a begintrans.
Where can I confirm that this isolation is active?


Last,

   cnnGPro.Execute "BEGIN TRANSACTION", , adCmdText
   cnnGPro.Execute "COMMIT TRANSACTION", , adCmdText
   cnnGPro.Execute "ROLLBACK TRANSACTION", , adCmdText

doesn't work.


Marcelo Cid

Marcelo Cid wrote:
>
> I installed ODBC3. After some tests I have some questions: (excuse my poor english)
>
> I think that I found a bug:

OK I would examine it.

> More one, how can I know if a transaction is serializable?
> I tried

Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/

regards,
Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

Marcelo Cid wrote:
>
> I installed ODBC3. After some tests I have some questions: (excuse my poor english)
>
> With this code:
>
>     Set rstCustomers = New ADODB.Recordset
>     rstCustomers.CursorType = adOpenKeyset
>     rstCustomers.LockType = adLockOptimistic
>     rstCustomers.Open "SELECT * FROM customers", cnnGPro
>
>     rstCustomers.AddNew
>     rstCustomers.Fields("name").Value = "Joe"
>     rstCustomers.Fields("age").Value = 20
>     rstCustomers.Update
>
>     nID = rstCustomers.Fields("id_customer").Value
>
> I can get my id_customer, but if I change the cursor type to adOpenDynamic, I won't get.

Dynamic cursors aren't supported by the driver.
Maybe you have to set the CursurLocation property
to adUseClinent if you need dynamic cursors.
Keyset-driven cursors aren't implemented yet either.
ADO seems to use updatable static cursors instead.
As for keyset driven cursors I hope I would be
able to support it in the near future.

> I think that I found a bug:
>
>     Set rstCustomers = New ADODB.Recordset
>     rstCustomers.CursorType = adOpenKeyset
>     rstCustomers.LockType = adLockOptimistic
>
>     cnnGPro.BeginTrans
>
>     rstCustomers.Open "SELECT * FROM customer WHERE substr(upper(name), 1, 1) = 'M'", cnnGPro
>
>     Debug.Print rstCustomers.RecordCount
>
>     Do While Not rstCustomers.EOF
>         rstCustomers.Delete
>         rstCustomers.MoveNext
>         Debug.Print rstCustomers.RecordCount
>     Loop
>
>     rstCustomers.Close
>
>     cnnGPro.RollbackTrans
>
> After I deleted the last record I get a error (80040e23) at rstCustomers.MoveNext.
> Prior after the last delete I get:
>
>     ? rstCustomers.EOF, rstCustomers.BOF
>     False         False
>
> After the error at rstCustomers.MoveNext, I get:
>
>     ? rstCustomers.EOF, rstCustomers.BOF
>     True          False
>
> If I change the cursor type to adOpenDynamic no error will happen.
> So if I need my id_customer I have to use adOpenKeyset and if I need to delete a record I have to use adOpenDynamic.

Probably I fixed the bug.
Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/.

Hiroshi Inoue
    http://w2422.nsk.ne.jp/~inoue/

>Probably I fixed the bug.
>Please try the snapshot dll at http://w2422.nsk.ne.jp/~inoue/.

Fine. My program works now and I can get recordcount properly. It works with psqlodbc30.dll (with and without the
multibyteversion), but not with psqlodbc30w.dll. I think that it wasn't updated yet. 

Marcelo Cid