Microsoft .Net Provider does not return all Rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

We are experiencing a problem in using the Microsoft .Net Data Provider Fo
Oracle. For certain more complex SQL queries it does not return the correc
number of rows. The behaviour can take two forms
1 the DataReader returns no rows back to the calling program, on th
database one fetch has been done and a fraction of the expected rows ha
been processed
2 The reader returns rows but the amount of rows is less than total returne
if the SQL is run directly, on the database a number of fetches have bee
done

In both cases the SQL is valid and can be executed from SQL+, so I am happ
that there is not a problem with the data itself or the database

We tried this provider as we wanted to stay with the Oracle 8.1.7 client. W
are currently deploying on .Net 1.0 Framework using MDAC 2.7

Has anyone else had any problems with this data provider

Example SQL that does wor

SELECT B.F01_143,A.F00_025,A.F00_026 FROM SEG_01 B, SEG_00 A WHERE A.F00_00
= B.F01_14

Example SQL that doesn't wor

SELECT B.F01_143,A.F00_025,A.F00_026 FROM SEG_01 B, SEG_00 A WHERE A.F00_00
= B.F01_145 AND ( ( 1 = ( SELECT COUNT( F00_030 ) FROM SEG_01 B1, SEG_00 A
WHERE A1.F00_002 = B1.F01_145 AND B1.F01_003 = B.F01_003 GROUP B
B1.F01_003) )

Example Code showing basic connection object

Imports SDO = system.Data.OracleClien

mConnection = New SDO.OracleConnection(mConnectionString
mySQL = TextBox1.Tex

mConnection.Open(

mCommand = New SDO.OracleCommand(mySQL, mConnection
mCommand.CommandType = CommandType.Tex

Dim startTime As Date = No
aRdr = mCommand.ExecuteReader(
While aRdr.Rea
PrintLine(theFile, aRdr.Item(0).ToString
linecount +=
If linecount > limit The
Exit Whil
End I
End Whil
aRdr.Close(
Dim stopTime As Date = No
Label2.Text = Now.ToLocalTime + " It took "
CStr(DifferenceInSeconds) + " seconds and returned " + CStr(linecount)
"rows

mConnection.Close(
 
Hi,

Are you sure that this stuff isn't causing loop to abort?

If linecount > limit Then
 
DelC:

I've used this provider extensively and haven't noticed this problem. Just
to test a few things...use the same query but use a DataAdapter to fill a
datatable and verify DataTable.Rows.Count. I expect the number will match
SQL+ if the query is the same. Next, take out all the code in your While
dr.Read loop and just debug.writeline (i) and then i++. I expect it will
be the same here too. I'm not sure what Limit is, but since you don't know
what the rowcount of a reader is without walking through it (or sending a
Count(*) query with the exact same where clause prior to executing your
reader and assuming the row count hasn't changed in the interim)..it's quite
likely that you are knocking yourself out of the loop with the Exit While
like Miha pointed out.

I can't say for sure that the Oracle provider is bug free, but I've used it
a ton and never seen this, and if it were a bug this serious, I think
there'd be a lot more talk of it.

HTH,

Bill
 
I will try the DataAdapter angle to see if it behaves any differently
However when I have this problem with some examples it doesn't even go into the loop as the first dr.read returns false even though I know the query should return values
 
Are you using Framework 1.1? If so, what happens if you use
DataReader.HasRows?
DelC said:
I will try the DataAdapter angle to see if it behaves any differently.
However when I have this problem with some examples it doesn't even go
Click to expand...
into the loop as the first dr.read returns false even though I know the
query should return values.
 
Apologies if this is a duplicate post as I got an error message when I submitted the previous post.

If I Use 1.1 in my Dev environment then dr.HasRows will be false for the SQL that gives me a problem.
 
Back
Top