Member Login

Username
Password
Forget Password
New Sign Up
Search Forum

Buy Support
Incidents

If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:

Click Here
If you can't login and post questions or you are having trouble viewing forum posts:
Click Here
Callback
Support

If you live in USA, UK, Canada, Australia or New Zealand, you can leave us details on your question and request us to call you back and discuss them with you personally  (charges apply).

Click Here
 
Buy Support
Incidents
If you can't find your answer in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us private or confidential information:
Click Here

Forum : Ado Item Query returns EOF in Delphi when wrong Cursor type is usedSearch Forum

Forum Home > QODBC - ODBC Driver for QuickBooks > QODBC v6 Forum

 New Topic 
 
 Post Reply 
[1]  
 Ado Item Query returns EOF in Delphi when wrong Cursor type is used 
 Author   Message 
  Richard Boyd 
  
 Group: Members 
 Posts: 8 
 Joined: 2007-08-06 
 Profile
 Posted : 2007-08-18 02:23:16

e.g. The Select * query works with other tables however the item table always returns eof as true with the item table. The query works with the VB Demo Can some please make a suggestion

Thanks - Sample code below.

There are availble items and I tried "Select * from ItemService" also.

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;

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-08-18 08:52:31

The ITEM table is a combined (read only view) list of all Item tables in QuickBooks: ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, and ItemGroup; with minimum common fields.

Inserts and updates are actually done using the ItemInventory, ItemNonInventory, ItemOtherCharge, ItemInventoryAssembly, ItemService, ItemFixedAsset, or ItemGroup tables.

I understand the Customer table doesn't have this problem, what about all these other tables?

 

  Top 
  Richard Boyd 
  
 Group: Members 
 Posts: 8 
 Joined: 2007-08-06 
 Profile
 Posted : 2007-08-21 00:48:33

Yest the problem still occures

I had already tried the ItemService, ItemInventory , ItemNonInventory and ItemOtherCharge table and the same EOF message. I should have been this more clear with my explanation.

The ItemGroup table seems to work but that is the only one. I do not see how just using the ItemGroup table can help me complete everything I need to do.

I have also tried making changes to the tquery below and still the eof message.

itmQuery.CursorType := ctOpenForwardOnly;  itmQuery.LockType := ltReadOnly;

 

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-08-21 08:42:14

I just tested EOF on another script found in: Can I get some examples of how to use QODBC via Visual Basic? and it works as expected.

 

  Top 
  Richard Boyd 
  
 Group: Members 
 Posts: 8 
 Joined: 2007-08-06 
 Profile
 Posted : 2007-08-22 06:52:02

Thanks to everyone for there help.

 I found the solution. 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 cursorlocation of the tquery is changed to clUseServer rather then the default and it works. If someone could explain why I would appriciate it. Thanks again Richard Todd Boyd

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;

 

  Top 
  Tom 
  6c3c1_sdk-qodbc.gif
 Group: Administrator 
 Posts: 5510 
 Joined: 2006-02-17 
 Profile
 Posted : 2007-08-22 08:33:29
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:

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 greatly 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, and allows all types of movement through the Recordset that don't rely on bookmarks; allows bookmarks if the provider supports them. The Supports method of an ADODataset indicates whether a recordset supports certain types of operations. 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 at the same time.

ctKeyset
Behaves like a dynamic cursor, except that it prevents you from seeing records that other users add, and prevents access to records that other users delete. Data change by other users will still be visible. It always supports bookmarks and therefore 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. Always allows bookmarks and therefore allows all types of movement through the Recordset. Additions, changes, or deletions by other users will not be visible. A static cursor behaves like the result set from a BDE Query component with its RequestLive property set to False.

ctForward-only
Behaves identically to a dynamic cursor except that it allows you to scroll only forward through records. This improves performance in situations where 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 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.

CursorLocation
The CursorLocation property defines where the recordset 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, 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 a number of 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.

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 is being changed by another user, 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 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.

ltOptimistic
Optimistic locking locks the record only when it's physically updated. This type of locking is useful in conditions where there is only a small chance that a second user may update 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 makes any change to 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 support navigating to other records.

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

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.  

 

  Top 
 New Topic 
 
 Post Reply 
[1]  

Jump to