empty recordset returned instead of runtime error

[Remco Blok]

Hello,

I have a problem that ado returns an empty recordset
instead of raising a runtime error in the open method on
the recordset. Here's a VB6 code example:

Dim objC As New ADODB.Connection
Dim objRS As New ADODB.Recordset
objC.Open "Provider=MSDAORA.1;Data Source=bla;User
ID=bla;Password=bla"
objRS.Source = "select to_date
('2003/30/30:00:00:00','yyyy/mm/dd:hh24:mi:ss') from dual"
objRS.ActiveConnection = objC
objRS.Open
MsgBox objRS.EOF
MsgBox objRS.Fields(0).Value

The first message box confirms EOF = True. But when I try
objRS.Fields(0).Value I do not get an "Either BOF or EOF
is True, or the current record has been deleted. Requested
operation requires a current record." error, but I get the
actual runtime error which should have been raised in the
open method on the recordset: "ORA-01843: not a valid
month".

I have tried both the Microsoft OLEDB provider for Oracle
MSDAORA.1 and the Oracle OLEDB provider for Oracle
OraOLEDB.Oracle. I have tried ado 2.5 and ado 2.7. I have
also tried a VBScript version of the code running in the
Windows Scripting Host and an IIS ASP page. All show the
same strange behaviour which points to ADO.

Last week I saw the same strange behaviour in another
database where ADO would not raise an "ORA-01652: unable
to extend temp segment by 128 in tablespace TEMP" error in
the open method on the recordset, but simply returned an
empty recordset. I only found out what the error was by
running the same query in SQLPLUS and TOAD, which do not
use ADO.

Has anybody seen this strange behaviour and is there a fix?

Kind regards,

Remco

[Victor Koch]

objRS.Open
if objRS.BOF=true and objRS.EOF=true then
MsgBox "the record is empty, BOF=" & objRS.BOF & " EOF=" & objRS.EOF
else
MsgBox objRS.Fields(0).Value
endif

--
Víctor Koch From Argentina.

"Remco Blok" <remco...@logicacmg.com> escribió en el mensaje
news:0a5201c3a2cd$bce426d0$a601...@phx.gbl...

[Remco Blok]

Thanks for your reply Victor.
Perhaps though it wasn't clear exactly what my problem is.
It is not that I don't know how to check for an empty
recordset, but my problem is that a runtime error should
have been raised by ADO while it is not. Therefore I get
an empty recordset without knowing there was actually an
error. Seems like a bug in ADO to me.
Kind regards,

Remco

[AA]

Which calendar has 30 months?

>.
>

[Remco Blok]

None. It was done on purpose to make the query fail with
the error "ORA-01843: not a valid
month". The only problem being that ADO does not give this
error, but returns an empty recordset.
Sorry for all the confusion.

Kind Regards,

Remco

[anon...@discussions.microsoft.com]

Further to that note: I had only created that query so
that other people may be able to reproduce the problem.
The actual query that showed the problem in the first
place is a lot more complex, but this simplified query
shows exactly my problem. Can you reproduce my problem?
You can only find out by trying I think.

Kind regards,

Remco

>.
>

[dulal oza]

Yo remco,

is 2003/30/30 a typo ??? if not the date format is
incorrect... months should be between 1-12

>.
>

[Remco Blok]

Sorry, I should have clarified this:

It was done on purpose to make the query fail with the
error "ORA-01843: not a valid month". The only problem
being that ADO does not give this error, but returns an
empty recordset.

I had only created that query so that other people may be
able to reproduce the problem. The actual query that
showed the problem in the first place is a lot more

complex and only applies to our database. ADO should have
raised the error because somebody had entered a date
incorrectly in the database. But instead it returned an
empty recordset. This simplified query shows exactly my
problem and should run on all oracle databases. Can you
reproduce my problem?

Kind Regards,

Remco

[Stephen Howe]

>objRS.Open

This is not good. Go and have a look at Microsofts documentation on what it
means if you fail to provide RecordSet Open() with rest of the parameters.

If you fail to provide the arguments, particularly the 5th argument, ADO has
to deduce what Source is. A slight performance loss.
I mean it could be Stored Procedure, SQL command, Table, what? Who knows
what ADO thinks Source is? It is conceivable that ADO might guess wrongly.

Open() is the most important call of a Recordset. Depending on what the
arguments are, it completely controls how various properties behave.
Therefore make sure you specify all arguments and leave nothing to chance so
you get exactly the type of recordset you want. Don't ask for more
functionality than you need. If you don't plan to update your Recordset,
specify a read-only cursor.

Did you want a client-sided or server-side cursor location?
Did you want a read-only locktype?
Did you want a forward only cursor (the fastest cursor but very limited
functionality: RecordCount will be -1)?

I would try client-sided,static cursor,readonly with adCmdText as the hint
and see what you get.

Hope this helps

Stephen Howe

[Remco Blok]

Thanks for your reply Stephen,

Unfortunately specifying a client-side static cursor, read
only and adCmdText with the recordset open method did not
make a difference. I'll try re-installing mdac
Regards,

Remco

>.
>

[anon...@discussions.microsoft.com]

As a workaround I've put additional error trapping in
place, making ue of the strange fact that ado raises the
original runtime error when you try .Fields(0).Value on
the empty recordset. Perhaps everybody should do this,
because when ado returns an empty recordset you may never
know if this was correct or if a runtime error occured
without being raised by ado.

Dim objC
Dim objRS
Dim sDescription, sSource, iNumber
Dim vDummy
Set objC = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")

objC.Open "Provider=MSDAORA.1;Data Source=bla;User
ID=bla;Password=bla"
objRS.Source = "select to_date
('2003/30/30:00:00:00','yyyy/mm/dd:hh24:mi:ss') from dual"
objRS.ActiveConnection = objC

On Error Resume Next
objRS.Open
iNumber = Err.Number
sSource = Err.Source
sDescription = Err.Description
On Error GoTo 0
If iNumber<>0 Then
Err.Raise iNumber,sSource,sDescription
End If
On Error Resume Next
vDummy = objRS.Fields(0).Value
iNumber = Err.Number
sSource = Err.Source
sDescription = Err.Description
On Error GoTo 0
If iNumber <> 0 And iNumber <> adErrNoCurrentRecord Then
Err.Raise iNumber,sSource,sDescription
End If

[Jerry III]

Remco, this is not an ADO issue. ADO does not provide any database engine,
it simply provides a somewhat standard way to access a database. This seems
to be a bug (or maybe it is an expected behavior, who knows) when using
Oracle DB engine - you said the provider does not make a difference, so it
seems to be in the actual DB engine. I think you should ask in an Oracle ng
or try searching their support site. Or maybe it's a setting of the server,
I've never used Oracle but from one point of view it might make sense not to
throw an error to cancel the whole query when the problem is in a conversion
of a single field...

Jerry

"Remco Blok" <remco...@logicacmg.com> wrote in message
news:0a5201c3a2cd$bce426d0$a601...@phx.gbl...

[im.j...@gmail.com]

Did you solve this problem?
I have the same problem :S and I don´t know how to solve