Monday, May 4, 2009

FAQs – April 2009

Advantage "Hangs" On Startup or Shutdown

When starting the Advantage Service it does not completely start but shows a status of "Starting" in the Services Console. This can also occur when attempting to stop the service and usually generates a Windows 2140 error as shown below.

ADS Stop Error

This can happen if Advantage displays an error message at startup on the console that is not responded to. If you are connected remotely to the server through Remote Desktop, VNC, CoPilot etc… this message may not be displayed. If you routinely connect to the machine running Advantage remotely you may want to suppress message boxes so these messages will not be displayed. This will allow Advantage to startup or shutdown without any user intervention. Any errors on startup or shutdown will be logged in the error log.

FoxPro View Designer

When creating remote views for Visual FoxPro sometimes the view designer generates an error. The error may look like the following:

Error 7200:  AQE Error:  State = 42000;   NativeError = 2115;  [iAnywhere Solutions][Advantage SQL Engine]Expected lexical element not found: identifier or expression -- Location of error in the SQL statement is: 124 [Parsing Expression (column 8 in the SELECT clause)]

This error is generally caused because a field is named one of the SQL Reserved Words used by Advantage. These keywords must be delimited with either double quotes ( " ) or brackets ( [] ). However the FoxPro view designer does not allow for delimiting field names. The view will have to be created programmatically for example:

CREATE DATABASE 'TEST.DBC'
CREATE SQL VIEW "TESTVIEW" ;
REMOTE CONNECT "ADS" ;
AS SELECT MyTable.[Key], MyTable.LastName, MyTable.FirstName FROM MyTable MyTable

Record Locking With Delphi

The Advantage TAdsTable component has the option to use Optimistic or Pessimistic (default) locking when editing records. You specify the locking mode with the AdsRecordLockingMode table option. When using Pessimistic Locking the record is locked when placed in edit mode and unlocked after a successful post or the edit is canceled. With Optimistic locking the record is not locked until after a successful post.

In both cases Advantage retrieves the latest version of the record when it is placed in edit mode. This ensures that the user has the latest data from the server. When Optimistic locking is used the CRC of the record is checked again before the post is committed. If the record has changed since the user started the edit but before the post a 5186 (AE_OLD_RECORD) error is returned and the post is canceled.

If this error is returned you can reconcile any changes automatically or allow the user to choose which fields to change. The TAdsTable component will only write to the fields that have been modified by the user, however, if the same field was changed by more than one user the last users update will be applied.

Collation Error when Connecting to VFP files

When connecting to a Visual FoxPro 9 table using an Advantage client you may receive a 5092 (Collation Sequence Mismatch) error. VFP tables allow for multiple indexes to be built using different collations. Advantage now has the ability to dynamically load collation sequences at runtime which allows for the use of multiple collations with tables.

Generally all of the indexes for the VFP table are created with the same collation sequence, however, it is not always the machine default collation sequence. In most cases the MACHINE_VFP_BIN_1252 collation sequence does not produce any errors. However, you should specify the collation sequence specified in VFP for full compatibility.

The various collations supported by Advantage are stored in the Adscollate table which is located in the server install directory. This table is also placed in the Windows\System32 directory so it can be used by Advantage clients such as the ODBC driver. You can get an overview of Advantage collation support in the help file.

No comments: