Annoying database problem

[Alimonster]

Hi folks. I'm trying to write a CGI app that will grab values from MS SQL Server 6.5 (using ADO, not BDE) and spit out the results to a browser as part of a larger project (w/ Delphi 5 Pro). However, I've come across a stupid problem.

SQL Server seems to handle the recordcount differently than Oracle, which I usually use. When I execute a query that's guaranteed to give no results, it blows up! Instead of letting me check the recordcount, it immediately (at the "MyQuery.Open" line, executing a simple select statement) throws back a server error:


Exception: EOleException
Message: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record

This only happens if no record is returned.

I'd really prefer to be able to check the recordcount ("if myquery.RecordCount = 0 then DoSomething") after running the query! At present, it looks like I may have to use try..except to catch this, which seems very kludgey! Does anyone have an inkling of what's going on here? How do I accurately run a select query that may not return anything without it blowing up? What am I doing wrong here? :evil:

[TheLion]

I work with ADO a lot and I never have that problem. However I never do a record count with it. I don't know what components you use, but if you use a RecordSet to retrieve the records you can solve the problem like this:

If RS.EOF Then
// No records are returned
Else
// Do whatever you need to do with the records...


RS = ADO RecordsSet, I always create it like this this:

var RS : OleVariant;
RS := CreateOleObject('ADODB.Recordset');

I never get any errors... Do you use the Delphi components or do you call ADO using OLE?

[Alimonster]

I'm using the standard ADO stuff on the ADO tab (TADOQuery, etc.). I can't use your first suggestion because it blows up immediately without letting me do any code. For example, nothing but an error would be displayed here in a web action:

myquery.Open;
try
Response.Content := 'Buh?'; // doesn't get here!
...

The executing-of-sql-statement is blowing up things :(. I'm not sure why, though, since it behaves correctly under Oracle. Maybe it's the connection string, though I doubt it. It works fine if records exist so it's not a SQL problem - I think that SQL Server is having "different" ideas about where the end-of-file marker should be, and/or doesn't deal with recordcounts in an expected fashion, and/or is being dumb.

I can work around it though, so I'll just do that - it only means a little more care when I write my loops over the records...

[TheLion]

I worked with MS SQL 2000 servers a lot when I worked at a webhosting/design company true ASP and I never experienced any problems. When I write a Delphi application that needs database access I mostly use ADO in combination with an Access DataBase, however both databases are quite similar in use and the only difference is are the connection properties.

I never use Delphi's ADO components, I have nothing against them (although they seem to fail in this situation), but I just knew how to work with the ADO components in ASP and by creating them as OLE objects at runtime I could work in exactly the same way in Delphi as I did in ASP.

If you want I'll send you my DataBase library for Access DataBases (with some explanation), all you would have to do is create a new connection and closing procedure and you would be able to use it!

My library simply opens and queries the database using an ADODB OLE object, looking at the error you are getting the Delphi components do exactly the same!

[Michalson]

Could you post what settings you are using to open the table? (view form as text and copy the ADO control properties)

[Alimonster]

Thanks, I'd appreciate it if you could send me your code (address in profile). Off-topic, but I think the version of SQL Server (6.5) I'm using is much older than 2000 (not sure though, but it's running on a creaking server w/ NT4).

[EDIT: I googled it. 6.5 was released in April 1996]

[Alimonster]

Could you post what settings you are using to open the table? (view form as text and copy the ADO control properties)
Not at the moment, unfortunately. I'm not at work tomorrow (day off, w00t), so I'll do it on Monday. I've only a little experience with databases so it's quite likely that I've ballsed up a setting somewhere.

[wilbur989]

I used to do alot of development with ado and mssql 6.5, 7.0, and 2000. One of the things that will effect the recordcount is the cursortype and the amount of records returned by the query. I found in all of my mucking with it, that the only easy way to get a total record count from a table is with a sql statement. I.E. Select count(*) form tablename. I know this seems like it will take forever to return but it seem to return quickly enough. :D I do know that the recordcount property only will return how many records you returned via your query. It does not care how many records are in the table. hope any of this helps. :D

[Alimonster]

I was beginning to suspect that I may have to do a query to get the info. Ah well, what's life without a few difficulties ;). I'll investigate the cursor stuff too, just in case. Cheers!

[Alimonster]

w00t w00t w00t w00t w00t (http://info.borland.com/devsupport/delphi/mdac26.html). Sorted! :D

[TheLion]

I love Borland! :) They solve most of our problems! ;) *and created Delphi*