Dumb ass database question!

[srod]

Hi,

I was just testing what happens with PB's database commands when accessing a remote database (MySQL in this case) and the connection fails in the middle of retrieving the results of a Select query.

I opened the connection and issued the select query. Before using NextDatabaseRow() I slapped a message requester in to halt proceedings and then yanked my modem from the back of my machine - thereby destroying the connection.

I was mildly surprised to find that all records were still retrieved!

Obviously the DBMS has sent the results of the select query to my client 'up front' - or at the very least it sent a bunch of records via some kind of caching. The database in question is very small and so I cannot be sure if the DBMS is in the habit of sending the entire recordset or just the first x records etc?

Anyone know for sure what is happening here and whether this is the same for all remote DBMS accessed via ODBC ? I mean is there some requirement imposed by ODBC that all records are sent up front or just a few records etc? Do some ODBC drivers immediately create a separate thread, for example, to retrieve a set number of records to be cached?

Thanks.

[DTecMeister]

I know this is a bit late, but there is an odbc recordset cachesize setting. Whatever this is set to would identify how much data is stored for retrieval.

[srod]

Any reply is never too late! Thanks for that, I kind of suspected as much but haven't looked too deeply into the ODBC api's.

[Straker]

Any reply is never too late! Thanks for that, I kind of suspected as much but haven't looked too deeply into the ODBC api's.

Good! because here is a even later reply.

Databases generally do not cache results sets. It compiles the result set in its memory space based upon your select statement but then returns the entire data set to the requester, otherwise, it wouldn't be doing its job (answering the request). If I request all rows in a given table (SELECT * FROM table), then the database will send me all rows, whether or not the requesting client has enough memory to handle it. If not, the client process will choke or lock up, because the database server is not considerate enough (or knowledgeable enough) to wait and pass more data when the client is ready for more.

Basically ODBC is synchronous, not asynchronous.

So, in your example, the database returned the entire result set to your client, and NextDatabaseRow() simply cycled thru the result set already cached on your PC.

That being said, it is possible with some more sophisticated database servers (Oracle, Sybase, etc) to create CURSORS on the database server, which is basically caches result sets on the server, from which you issue FETCH to return a row at a time from the server. I believe that is what you were assuming was happening.

See http://en.wikipedia.org/wiki/Cursor_(databases)

I know it sounds confusing, but I hope this advances your understanding.

EDIT: What? are URLS now disabled on the forum?

[srod]

Interesting Wiki entry - thanks. That is very clear now.