Hi there!
I originally thought I was going mad, but now I think there's a bug in the 10g Oracle OleDB Provider. I can repeat this at will on my systems:
* Windows XP SP2 (with all the latest Windows Update Patches)
* Oracle Client (including OleDB Provider) v10.2.0.1.0
* MDAC 2.8 SP1 (with Windows XP SP2)
or
* Windows 2000 Workstation (with all the latest Windows Update Patches)
* Oracle Client (including OleDB Provider) v10.2.0.1.0
* MDAC 2.7
Here's all I have to do!
1) Create a table with some data in Oracle (have tested this against 8i, 9i and 10g databases):
CONN your_username/your_password@your_tns_service_name
CREATE TABLE OLEDB_TEST
(
RECORD_ID NUMBER(5) NOT NULL,
RECORD_DESC VARCHAR2(50) NOT NULL
)
/
INSERT INTO OLEDB_TEST VALUES(0 , '0 is OK!');
INSERT INTO OLEDB_TEST VALUES(1 , '1 is OK!');
INSERT INTO OLEDB_TEST VALUES(10 , '10 is wrong - comes out as 0');
INSERT INTO OLEDB_TEST VALUES(12 , '12 is OK');
INSERT INTO OLEDB_TEST VALUES(100 , '100 is wrong - comes out as 0');
INSERT INTO OLEDB_TEST VALUES(120 , '120 is wrong - comes out as 20');
INSERT INTO OLEDB_TEST VALUES(1200 , '1200 is wrong - comes out as 0');
INSERT INTO OLEDB_TEST VALUES(1230 , '1230 is wrong - comes out as 230');
INSERT INTO OLEDB_TEST VALUES(12300, '12300 is wrong - comes out as 300');
INSERT INTO OLEDB_TEST VALUES(12340, '12340 is wrong - comes out as 2340');
COMMIT;
2) Run some VBScript (which you can do in VB6, ASP or however else you want to add it in):
' Constants for the connection string
Const strUSER = "your_username" ' e.g. SCOTT
Const strPASSWORD = "your_password" ' e.g. TIGER
Const strTNS_SERVICE_NAME = "your_tns_service_name" ' e.g. ORCL
Dim objConnection
Dim objRecordset
' make sure we have a temp directory to write to, and that any existing file is deleted
On Error Resume Next
MkDir "C:\temp\"
Kill "c:\temp\oledb_test.txt"
On Error GoTo 0
' create the ADO connection
Set objConnection = CreateObject("ADODB.Connection")
' set the connection string
' Using the Microsoft OLEDB Provider for Oracle works fine and dandy!
'objConnection.ConnectionString = "Provider=MSDAORA.1;User ID=" & strUSER & ";Password=" & strPASSWORD & ";Persist Security Info=True;Data Source=" & strTNS_SERVICE_NAME
' But using Oracle Provider for OLEDB causes us problems!!
objConnection.ConnectionString = "Provider=OraOLEDB.Oracle.1;User ID=" & strUSER & ";Password=" & strPASSWORD & ";Persist Security Info=True;PLSQLRSet=1;Data Source=" & strTNS_SERVICE_NAME
' set the cursor to open on the client
objConnection.CursorLocation = 3 ' adUseClient
' open the connection
objConnection.Open
' create the ADO recordset object
Set objRecordset = CreateObject("ADODB.Recordset")
' set its active connection
Set objRecordset.ActiveConnection = objConnection
' open it up, pulling in the data from the oledb connection
objRecordset.Open "SELECT * FROM OLEDB_TEST"
' disconnect the recordset
Set objRecordset.ActiveConnection = Nothing
' persist the recordset's data to XML
objRecordset.Save "C:\temp\oledb_test.txt", 1 ' adPersistXML
' clean up our objects
objRecordset.Close
Set objRecordset = Nothing
objConnection.Close
Set objConnection = Nothing
3) The resulting file in c:\temp\oledb_test.txt is as follows:
<xml xmlns:s='uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882'
xmlns:dt='uuid:C2F41010-65B3-11d1-A29F-00AA00C14882'
xmlns:rs='urn:schemas-microsoft-com:rowset'
xmlns:z='#RowsetSchema'>
<s:Schema id='RowsetSchema'>
<s:ElementType name='row' content='eltOnly'>
<s:AttributeType name='RECORD_ID' rs:number='1' rs:writeunknown='true'>
<s:datatype dt:type='number' rs:dbtype='numeric' dt:maxLength='19' rs:scale='0' rs:precision='5' rs:fixedlength='true'
rs:maybenull='false'/>
</s:AttributeType>
<s:AttributeType name='RECORD_DESC' rs:number='2' rs:writeunknown='true'>
<s:datatype dt:type='string' rs:dbtype='str' dt:maxLength='50' rs:maybenull='false'/>
</s:AttributeType>
<s:extends type='rs:rowbase'/>
</s:ElementType>
</s:Schema>
<rs:data>
<z:row RECORD_ID='0' RECORD_DESC='0 is OK!'/>
<z:row RECORD_ID='1' RECORD_DESC='1 is OK!'/>
<z:row RECORD_ID='0' RECORD_DESC='10 is wrong - comes out as 0'/>
<z:row RECORD_ID='12' RECORD_DESC='12 is OK'/>
<z:row RECORD_ID='0' RECORD_DESC='100 is wrong - comes out as 0'/>
<z:row RECORD_ID='20' RECORD_DESC='120 is wrong - comes out as 20'/>
<z:row RECORD_ID='0' RECORD_DESC='1200 is wrong - comes out as 0'/>
<z:row RECORD_ID='230' RECORD_DESC='1230 is wrong - comes out as 230'/>
<z:row RECORD_ID='300' RECORD_DESC='12300 is wrong - comes out as 300'/>
<z:row RECORD_ID='2340' RECORD_DESC='12340 is wrong - comes out as 2340'/>
</rs:data>
</xml>
Note that the RECORD_ID field is incorrect!! Somewhere along the line, for every 0 at the end of the RECORD_ID (when the RECORD_ID is not 0), a digit is removed from the beginning of the RECORD_ID!
This is KILLING me!!!
You will notice that there is a line of code that is commented out which uses the Microsoft OLE DB Provider for Oracle instead. This works fine and dandy. The trouble is I can't use it, as I need functionality in the Oracle provider that the MS provider doesn't have! OUCH!
Can anyone confirm that they get the same problems, or is there something else in my system that is messing things up??
Many thanks for any help!
James
Message was edited by:
JAMES_2JS
