[QODBC-Desktop] ADO Query Returns EOF When Wrong Cursor Type is used
Posted by brad waddell on 12 March 2009 05:21 PM
Troubleshooting: ADO Query Returns EOF When Wrong Cursor Type is used
A Select query works with the QODBC Support Wizard but not with the sample code below in Delphi:
The problem seems to happen in VB also and the forum suggested asolution of changing the recordset cursor location. As you can see in the below code the cursor location of the tquery is changed to clUseServer rather then the default and it works.
ODBC cursor errors are common because QODBC only supports static or forward only type cursors. The work around is to always use ForwardOnly Cursors. For example with ADO keyset cursors we recommend:
And just in case you try switching everything to adUseServer, please be aware QODBC stored procedure calls like sp_tables only like adUseClient cursor types.
As far as Delphi is concerned, choosing the correct cursor has a direct impact on the success of your Delphi ADO-based application. ADO provides four cursor options: dynamic, keyset, forward-only and static. Since each cursor type behaves differently, you will significantly benefit from understanding the capabilities of each one.
The CursorType property specifies how you move through the recordset and whether changes made on the database are visible to the recordset after you retrieve it. Delphi wraps ADO cursor types in the TCursorType.
Choose dynamic cursors if multiple users insert, update, and delete rows in the database simultaneously.
Note: only ctStatic is supported if the CursorLocation property of the ADO dataset component is set to clUseClient.
Note: if the requested cursor type is not supported by the provider, the provider may return another cursor type. That is, if you try to set CursorLocation to clUseServer and CursorType to ctDynamic, on an Access database, Delphi will change the CursorType to ctKeyset.
The data in a client-side cursor is "inherently disconnected" from the database. ADO retrieves the results of the selection query (all rows) and copies the data to the client before you start using it (into the ADO cursor). After you make changes to your Recordset, the ADO translates those changes into an action query and submits that query to your database through the OLE DB provider. The client-side cursor behaves like a local cache.
Using the server-side cursor means retrieving only the required records, requesting more from the server as the user browses the data. Server-side cursors are useful when inserting, updating, or deleting records. This type of cursor can sometimes provide better performance than the client-side cursor, especially in situations where excessive network traffic is a problem.
You should consider several factors when choosing a cursor type: whether you're doing more data updates or just retrieving data, whether you'll be using ADO in a desktop application or in an Internet-based application, the size of your resultset, and factors determined by your data store and environment. Other factors might restrict you as well. For example, the MS Access doesn't support dynamic cursors; it uses keyset instead. Some data providers automatically scale the CursorType and CursorLocation properties, while others generate an error if you use an unsupported CursorType or CursorLocation.
Modifying a record in an Access database locks some neighboring records. This is because Access uses, so-called, page locking strategy. This means that if a user is editing a record, some other user won't be allowed to modify that record, or even to modify the next few records after or before it.
In Delphi, the TADOLockType specifies the types of locks that can be used. You can control row and page locking by setting the appropriate cursor lock option. To use a specific locking scheme, the provider and database type must support that locking scheme.