Re: Problem with x64 Oracle OLE DB Provider, MS ADO, server side forward cursor

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Wed, 4 Mar 2009 14:50:13 -0800 (PST)
Message-ID: <d61fbbb8-87e8-4b35-a152-5f5924490639_at_r34g2000vba.googlegroups.com>

On Mar 4, 4:40 pm, PBarnes <pbarnes1..._at_gmail.com> wrote:
> With 64 bit Oracle 10 or 11 client, using MS ADO with a server side
> forward only cursor to run a simple query against a single table, the
> result Recordset does not contain all rows. In fact the number of
> records I can see is equal to the Recordset.CacheSize. It works as
> expected when runnning in 32 bit app.
>
> The client is Vista x64 and I have installed both 32 bit and 64 bit
> versions of Oracle Client, and get same results with latest Oracle 10
> and 11 clients. I have tried against a 32 bit server running Oracle
> 9.2.0.1.0 and another 32 bit server running 10.2.0.1.0, both with
> identical results.
>
> I have tried this with ADO 2.5, 2.6, 2.7, and 6.0, again with same
> results.
>
> Here is a VBA script that demonstrates:
> ----------------------------------------------
> cs = "Provider=OraOLEDB.Oracle.1;Password=(edit);Persist Security
> Info=True;User ID=(edit);Data Source=(edit);Extended Properties="""""
> qs = "SELECT * FROM MYTABLE"
>
> Set rs = CreateObject("ADODB.Recordset")
> rs.CacheSize = 10
> rs.CursorLocation = 2 ' adUseServer
> rs.CursorType = 0 ' adOpenForwardOnly
> rs.Open qs, cs, 0, 1 ' 0=adOpenForwardOnly, 1=adLockReadOnly
>
> cnt = 0
> rs.MoveFirst ' same with or without this
> While Not rs.EOF
> cnt = cnt + 1
> rs.MoveNext
> Wend
> MsgBox CStr(cnt)
> -----------------------------------------------------
> When I run this with 32 bit version of wscript.exe ( in %WINDIR%
> \SysWow64) it displays the correct number of records (200 in one
> table, 12000 in another I tried.) When I run with 64 bit version of
> wscript.exe (in %WINDIR%\system32) it always displays 10 records and
> reaches EOF. If I change the cache size to N, I get N records.
>
> Has anyone else seen this? Does anyone have any ideas how to fix or
> workaround this issue?

This looks like an interesting problem. You are using an implicit connection on the recordset, and I usually use a client side connection. Try this to see if it works for you: Dim strSQL
Dim strUsername
Dim strPassword
Dim strDatabase
Dim snpData
Dim dbDatabase
Dom cnt

Set snpData = CreateObject("ADODB.Recordset") Set dbDatabase = CreateObject("ADODB.Connection")

strUsername = "user-here"
strPassword = "password-here"
strDatabase = "database-here"

dbDatabase.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=" & strDatabase & ";User ID=" & strUsername & ";Password=" & strPassword & ";FetchSize=100;"
dbDatabase.Open
dbDatabase.CursorLocation = adUseClient

strSQL = "SELECT * FROM MYTABLE"

snpData.Open strSQL, dbDatabase

cnt = 0
If snpData.State = 1 Then

    Do While Not(snpData.EOF)

        cnt = cnt + 1
        snpData.MoveNext

    Loop
End If

MsgBox CStr(cnt)

Note that the FetchSize=100 in the connection string would be equivalent to snpData.CacheSize = 100 - almost the same any way (a 10046 trace will show why they are not the same).

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Mar 04 2009 - 16:50:13 CST