Knowledgebase
[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

Problem Description

A Select query works with the QODBC Support Wizard but not with the sample code below in Delphi:

begin
try
oConn := TAdoConnection.CREATE(Self);
oConn.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=QuickBooks Data;OLE DB Services=-2;';
oConn.Connected := True;

try
itmQuery := TADOQuery.Create(self);
itmQuery.Connection := oConn;
itmQuery.CursorType := ctKeyset;
itmQuery.LockType := ltOptimistic;
itmQuery.SQL.Clear;
itmQuery.SQL.Add('Select * from Item');
itmQuery.Open;
if itmQuery.eof then begin
showmessage('EOF');
end
else begin
itmQuery.first;
itmID := itmQuery.FieldValues['ListID'];
end;
finally
itmQuery.Close;
itmQuery.free;
end;

finally
oConn.Close();
oConn.Free;
end;
end;

 

Solutions

     The problem seems to happen in VB also, and the forum suggested a solution of changing the recordset cursor location. As you can see in the below code, the cursor location of the query is changed to cause server rather than the default, and it works.

try
itmQuery := TADOQuery.Create(self);
itmQuery.Connection := oConn;
itmQuery.CursorType := ctDynamic;
itmQuery.CursorLocation := clUseServer;
itmQuery.LockType := ltOptimistic;
itmQuery.SQL.Clear;
itmQuery.SQL.Add('Select * from ItemService');
itmQuery.Open;
if itmQuery.eof then begin
showmessage('EOF');;
end
else begin
showmessage('Not EOF');
end;
finally
itmQuery.Close;
itmQuery.free;
end;

Instructions

     ODBC cursor errors are common because QODBC only supports static or forward-only cursors. The workaround is always to use ForwardOnly Cursors. For example, with ADO keyset cursors, we recommend:

With rs
.CursorType = CursorTypeEnum.adOpenForwardOnly
.LockType = LockTypeEnum.adLockOptimistic
.CursorLocation = CursorLocationEnum.adUseServer
End With

     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.

ctDynamic
     Allows you to view additions, changes, and deletions by other users. The Supports method of an ADODataset indicates whether a recordset supports certain operations. It will enable all types of movement through the Recordset that don't rely on bookmarks and enable bookmarks if the provider supports them. The following statement can be used to check if the provider supports bookmarks:
if ADOTable1.Supports(coBookmark) then ...

     Choose dynamic cursors if multiple users insert, update, and delete rows in the database simultaneously.

ctKeyset
     It behaves like a dynamic cursor, except that it prevents you from seeing records that other users add and access to records that other users delete. Data change by other users will still be visible. It always supports bookmarks and allows all types of movement through the Recordset.

ctStatic
     Provides a static copy of a set of records for you to use to find data or generate reports. A static cursor behaves like the result set from a BDE Query component with its RequestLive property set to False. It always allows bookmarks and all types of movement through the Recordset. Additions, changes, or deletions by other users will not be visible.

ctForward-only
     It behaves identically to a dynamic cursor except that it allows you to scroll only forward through records. This improves performance when you need to make only a single pass through a Recordset.

Note: only ctStatic is supported if the CursorLocation property of the ADO dataset component is set to clUseClient.

Note: if the provider does not support the requested cursor type, the provider may return another cursor type. If you try to set CursorLocation to clUseServer and CursorType to ctDynamic, on an Access database, Delphi will change the CursorType to ctKeyset.

CursorLocation
     The cursor location property defines where the record set is created when it's opened — on the client or the server.

     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.
In most cases, a client-side cursor is preferred because scrolling and updates are faster and more efficient, although returning data to the client increases network traffic.

     Using the server-side cursor means retrieving only the required records and 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 perform better than the client-side cursor, especially when 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 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, MS Access doesn't support dynamic cursors; it uses keysets instead. Some data providers automatically scale the CursorType and CursorLocation properties, while others generate an error if you use an unsupported CursorType or CursorLocation.

LockType
     The LockType property tells the provider what type of locks should be placed on records during editing. Locking can prevent one user from reading data that another user is changing, and it can prevent a user from changing data that is about to be changed by another user.

    Modifying a record in an Access database locks some neighboring documents. This is because access uses the 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 modify the following few records after or before it.

    You can control row and page locking by setting the appropriate cursor lock option. In Delphi, the TADOLockType specifies the types of locks that can be used. To use a specific locking scheme, the provider and database style must support that locking scheme.

ltOptimistic
     Optimistic locking locks the record only when it's physically updated. This locking is helpful in conditions where there is only a tiny chance that a second user may edit a row in the interval between when a cursor is opened and the row is finally updated. The current values in the row are compared with the values retrieved when the row was last fetched.

ltPessimistic
     Pessimistic locking locks each record while it's being edited. This option tells ADO to get an exclusive lock on the row when the user changes any column in the record. The ADOExpress components don't directly support pessimistic record locking because ADO itself does not have any way to arbitrarily lock a given record and still supports navigating to other records.

ltReadOnly
     Read-only locking with the CursorType set to ctForwardOnly is ideal for reporting purposes. This lock is useful in conditions where your application must temporarily prevent data changes but still can allow unrestricted reading. Read-only locking does not enable data editing.

ltBatchOptimistic
     BatchOptimistic locking is used with disconnected recordsets. These recordsets are updated locally, and all modifications are sent back to the database in a batch.

(139 vote(s))
Helpful
Not helpful

Comments (2)
Rafael
20 September 2012 10:09 AM
Here is a full example that I just teetsd on my 2010 system. This macro will delete all records in a table named tblEmployees in an Access database named Database1. Again, make sure you have established a reference to the Microsoft ActiveX Data Objects 2.8 Library (or 2.6 if you are using an earlier version of Office).Sub DeleteAllRecords()Dim dbConnection As ADODB.ConnectionDim dbCommand As ADODB.CommandDim dbFileName As StringdbFileName = C:\YourFilePath\Database1.accdb Set dbConnection = New ADODB.ConnectionWith dbConnection.Provider = Microsoft.ACE.OLEDB.12.0;Data Source= dbFileName _ ;Persist Security Info=False; .Open dbFileNameEnd WithSet dbCommand = New ADODB.CommandSet dbCommand.ActiveConnection = dbConnectiondbCommand.CommandText = DELETE FROM tblEmployees dbCommand.Execute , , adCmdTextSet dbCommand = NothingSet dbConnection = NothingEnd Sub
Muisrupi
20 September 2012 01:55 PM
Hi Jason -In the above code, I used an example name of Table1 for the dstabaae table receiving data from Excel. If you want to delete all the records from a table, using Table1 as an example, the SQL statement would beDELETE FROM Table1Actually, I wrote a warning message relating to this in my book on page 313, calling attention to the required presence of the WHERE clause in the SQL because omitting it would delete every row (record) from the table. Take heed and make sure that’s what you want before you execute that line, because as I wrote, once the records are deleted they are deleted forever.Hope this answers your question.Tom
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).