Re: Problem with x64 Oracle OLE DB Provider, MS ADO, server side forward cursor
From: PBarnes <pbarnes1970_at_gmail.com>
Date: Thu, 5 Mar 2009 06:57:18 -0800 (PST)
Message-ID: <d475e3e3-7486-4f85-9c99-bd60811ba4ee_at_n33g2000pri.googlegroups.com>
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.
Date: Thu, 5 Mar 2009 06:57:18 -0800 (PST)
Message-ID: <d475e3e3-7486-4f85-9c99-bd60811ba4ee_at_n33g2000pri.googlegroups.com>
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. Received on Thu Mar 05 2009 - 08:57:18 CST