RE: OLEDB internal queries

From: Alvaro Jose Fernandez <alvaro.fernandez_at_sivsa.com>
Date: Wed, 5 Sep 2007 10:17:05 +0200
Message-ID: <607D6181D9919041BE792D70EF2AEC48242A65@LIMENS.sivsa.int>

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.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Sep 05 2007 - 03:17:05 CDT

RE: OLEDB internal queries - last try