Re: Problem with x64 Oracle OLE DB Provider, MS ADO, server side forward cursor
From: PBarnes <pbarnes1970_at_gmail.com>
Date: Tue, 10 Mar 2009 08:08:04 -0700 (PDT)
Message-ID: <33793372-56bd-4349-b4ff-43e2301949f0_at_x29g2000prf.googlegroups.com>
On Mar 5, 9:57 am, PBarnes <pbarnes1..._at_gmail.com> wrote:
> On Mar 4, 5:00 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Mar 4, 5:50 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > > 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.
>
> > Small correction (must set the CursorLocation first before opening):
> > dbDatabase.CursorLocation = adUseClient
> > dbDatabase.Open
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Charles, thank you for your post. Your script works correctly on x64,
> as does mine if I use a client side cursor, adUseClient. However your
> version behaves the same as mine with a server side forward only
> cursor. My application needs to support server side forward only
> cursors since client side cursors are much slower for a single pass
> thru a large result set.
Date: Tue, 10 Mar 2009 08:08:04 -0700 (PDT)
Message-ID: <33793372-56bd-4349-b4ff-43e2301949f0_at_x29g2000prf.googlegroups.com>
On Mar 5, 9:57 am, PBarnes <pbarnes1..._at_gmail.com> wrote:
> On Mar 4, 5:00 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
>
>
>
>
> > On Mar 4, 5:50 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
>
> > > 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.
>
> > Small correction (must set the CursorLocation first before opening):
> > dbDatabase.CursorLocation = adUseClient
> > dbDatabase.Open
>
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
>
> Charles, thank you for your post. Your script works correctly on x64,
> as does mine if I use a client side cursor, adUseClient. However your
> version behaves the same as mine with a server side forward only
> cursor. My application needs to support server side forward only
> cursors since client side cursors are much slower for a single pass
> thru a large result set.
Oracle Support has confirmed that this is a known bug with their x64 OLE DB Provider (all versions.) Their bug number is 6623430. I'll try to update this thread when I get more information on when a fix will be available. Received on Tue Mar 10 2009 - 10:08:04 CDT