Skip site navigation (1) Skip section navigation (2)

ODBC3, VB, cursortype, bug and transactions

From: Marcelo Cid <marcid(at)uol(dot)com(dot)br>
To: pgsql-odbc(at)postgresql(dot)org
Cc: Marcelo Cid <marcid(at)uol(dot)com(dot)br>
Subject: ODBC3, VB, cursortype, bug and transactions
Date: 2002-04-24 17:11:02
Message-ID: 200204241711.OAA25754@william.uol.com.br (view raw or flat)
Thread:
Lists: pgsql-odbc
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

Responses

pgsql-odbc by date

Next:From: Francisco Jr.Date: 2002-04-24 21:12:05
Subject: Re: Implement a .NET Data Provider
Previous:From: Hiroshi InoueDate: 2002-04-23 08:05:31
Subject: Re: ODBC on pgsql 7.2.1 with unicode databases in Delphi

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group