RE: OLEDB internal queries - last try
- From: "Alvaro Jose Fernandez" <alvaro.fernandez@xxxxxxxxx>
- To: "Grant Allen" <gxallen@xxxxxxxxx>
- Date: Wed, 5 Sep 2007 10:17:05 +0200
Grant,
(Due to the list limitations regarding attachments, etc. , I am sending
to the list only the body of my message . The copy that I sent to you
includes
the attachments)
Many (many) thanks for your help.
-I'm attaching a self-contained sample test case in VBScript which
reproduces the problem (Frankly, test table doesn't appears to matter)
sqlplus > create table testoledb ( c char(10)) ;
sqlplus > insert into test values ('A') ; (typo ,
that's "testoledb")
sqlplus > commit ;
-reproduced using Oracle 9.2.0.7.0 OLEDB. See the tkprof trace included
in the email.
-No, we are not calling any SP from the code.
-Conn properties: Con.Timeout=120 , Con.CursorLocation=adUseClient,
adOpenStatic + adLockOptimistic (always fixed, as in the sample).
-RDBMS is 9.2.0.6 , or 9.2.0.7.0
-registry entries for OLEDB_SERVICES:
{HKEY_CLASSES_ROOT}\CLSID\{0C7FF16C-38E3-11d0-97AB-
00C04FC2AD98}\Default: RG_SZ value: SQLOLEDB
{HKEY_CLASSES_ROOT}\CLSID\{0C7FF16C-38E3-11d0-97AB-
00C04FC2AD98}\OLEDB_SERVICES REG_DWORD value: ffffffff
(hexadecimal)
Any ideas? In some - majority - of cases, the problem apears when using
DISTINCT . But I've seen some traces which not. Tried with UNIQUE and it
*appears* to do it well - but that also means changing the code.
alvaro.
--
Dim Con
Const adUseClient = 3
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim connstr
connstr = "Provider=OraOLEDB.Oracle; Data Source=SAMIL9I; Persist
Security Info=True; User ID=HOSIXV60; Password=hosixv60"
Set Con = CreateObject("ADODB.Connection") Con.ConnectionTimeout = 120
Con.Open CSTR(connstr)
' -- adUseServer: OK (no generated queries)
Con.CursorLocation = adUseClient
Con.CommandTimeout = 600
Set RS = CreateObject("ADODB.RecordSet")
'--RS.ActiveConnection = Con
msgbox "wait ... start 10046 in the server for this conn/session , for
do the trace"
sql = "SELECT Distinct c from testoledb"
RS.Open(sql), Con, adOpenStatic, adLockOptimistic
Do while not RS.BOF and not RS.EOF
'msgbox rs(0)
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
Con.Close
Set Con = Nothing
alvaro
A couple of things might be useful in helping you with this. The
connection string used (no need to include the password or real
server/user names), the connection object's properties (a very simple
bit of VB should be able to iterate over these and print them out ...
ask your VB devs), and the OLEDB_SERVICES value for the Oracle OLE DB
provider from the registry.
In 11 years of writing c++, c#, and vb against Oracle's ODBC driver and
OLE DB provider, I never saw your symptoms ... and we had regular
religious wars about the comparative merits of server-side versus
client-side cursors.
One other thing springs to mind as well. Is your VB code calling Oracle
stored procedures, and do these stored procedures include declarations
using %TYPE or %ROWTYPE? There are hideous bugs (i.e. massively
increased sql noise and network traffic) with Oracle's implementation of
these technologies in combination ... I can elaborate if this matches
your situation.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
