Friday, October 31, 2008

FAQs – October 2008

Clipper IP Libraries

Advantage provides support for IP communication from Clipper with a helper application called ADSDOSIP. This provides a bridge between the Clipper application and Advantage so there is a performance degradation when using it. However, it can be used to extend the life of your clipper applications in a TCP/IP only environment.

ADSDOSIP and its associated libraries; axipbcom.lib, axipcomm.lib and axipxcom.lib are included in the Clipper Install. However, it is not installed by default you must choose a custom install and ensure that IP for CA-Clipper is selected.

 Clipper Install Options

Accessing Data on a NAS

For best performance data which is accessed through Advantage Database (REMOTE) Server should reside on the machine where the Advantage service is running. However, you can access data with the Advantage Server which resides on another server. This is done using a Server-Side Alias which were introduced in Advantage version 8.0.

If the data being accessed through Advantage Database Server is not physically located on the machine running the Advantage service you may experience some performance or corruption issues. Since the data is stored remotely the server has to transfer the data across the network in order to use it. If the connection to the remote data is lost every user connected to the server will no longer be able to access their data.

Replication with Different Server Versions

Replication was introduced in version 8 of Advantage and has been enhanced in version 9. Replication is configured in a Data Dictionary by defining a publication, the tables to be replicated, and one or more subscriptions, servers to push the changes to.

The publisher pushes the changes to the subscriber(s) by making a connection and sending the appropriate commands. Essentially the publisher is a client to the subscriber(s), therefore, it consumes a user license at the subscriber and must be the same version or older than the subscriber.

This means that an 8.x server can replicate to a newer 9.x server but a 9.x server will get an error when attempting to replicate to a 8.x server. You can get more information about replication from the Replication Overview topic in the help file.

Setting Table Collation Sequence

Advantage 9 supports many new collation sequences as well as the ability to set the collation sequence per table. In prior versions the collation sequence was set on the server and was applied to all tables. If this collation sequence was changed all of the indexes would have to be rebuilt. If you attempt to open a table with an incorrect collation sequence you will receive a 5209, Collations do not match, error.

Version 9 introduced a new API call AdsOpenTable90 which includes an option for setting the collation sequence to use when opening the table. When using the TAdsTable component in Delphi the collation sequence for the table is set on the AdsCharType property.

The specified collation sequence is loaded dynamically by the server and used with the table. These collation sequences are stored in a table named adscollate which is distributed with the server. When using local server you must include this table in your application path if you need to use FoxPro collations.

Wednesday, October 29, 2008

Adding a Custom Search Engine to Google Chrome

Edgar Sherman, one of our senior technical support representatives, who manages the Advantage DevZone told me about how he added the DevZone search to Google Chrome. Chrome allows for custom searches to be added to its “one box for everything” functionality. Whenever you start typing in the address bar Chrome provides “helpful” information including search options.

Chrome Search Options

You can add your own custom search engines allowing you to do a search from the address bar. To add the Advantage DevZone Search to the Chrome address bar use the following steps.

  1. Open Google Chrome ( I used version 0.3.154.9 )
  2. Right-Click on the address bar and select “Edit search engines…”
  3. Once the window opens click “Add”
  4. Fill out the edit form

    Name:  This will be the name shown when you search from the address bar.  In addition on the default homepage or new tab page this will be what is the grey text in the search box.
    Keyword: The "keyword" you will preface your search with
    URL:  The URL to use for the search

    For my setup I used the following:
    Name: DevZone Search
    Keyword: devzone
    URL: http://devzone.advantagedatabase.com/dz/content.aspx?Key=17&SearchKeyWords=%s&tab=-1 

    Chrome Edit Search Engine

  5. Now you can type “devzone <searchitem>” to search the devzone

Chrome DevZone Search

Thanks Edgar for this great tip!

Friday, October 24, 2008

And Now This Special Report . . .

Instead of writing a technical article I thought I would cheat a bit on my posting and put up some links that I have found interesting. I admit that I probably read way too many blogs, however, most are technology related so they apply to my work. That’s my story and I’m sticking to it.

I really like Jeff Atwood’s Coding Horror. He provides a great mix of good content coupled with a self deprecating and humorous tone. His post today is one of the reasons I enjoy his blog so much, check out The World’s Most Dangerous Blog.

I read Eric Sink’s book back in June and I have read his blog for quite a while. He recently did a post on Product Parenting which is his take on product management. I found it very interesting and a good read for anyone who is interested in product management.

I have also read two of Joel Spolsky’s, the famous Joel on Software, books ( see the reviews here and here)and I also appreciate his writing style. He doesn’t post as much as he has in the past but he is still publishing articles. I enjoyed his most recent article “How Hard Could It Be?: Sins of Commissions”.

For my marketing fix I rely on Seth Godin, his blog is a mix of marketing insight and other practical advice. There are so many great thoughts on this blog that it is hard to choose just one. However, I found his post Is Effort a Myth? very interesting. His advice is to take some of your “spare time” and spend it more productively, now I just have to find a way to get two more hours in my day.

If you didn’t get a chance to attend Southwest Fox you can get some recaps from some of the organizers Doug Hennig and Rick Schumer. For the lighter side of Southwest Fox take a look at this post by Andrew MacNeill.

Of course I have to mention some posts on J.D. Mullin’s blog. J.D. attended Southwest Fox with me and he did a post on Retrofitting Client Server Access. He also published a posting written by Mark Wilkins, a senior software engineer on the Advantage R&D team, about the use of Solid State Drives (SSDs).

Hope you enjoy the links, we’ll be back to our regularly scheduled programming next week.

Wednesday, October 22, 2008

Referential Integrity and Domain Tables

In my last posting about constraints I discussed field and table level properties. Another way to implement constraints is the use of Referential Integrity. Referential integrity (RI) enforces primary key and foreign key relationships within the database. These RI rules are most commonly used to prevent the occurrence of orphaned records, records in a child table which are not linked to a record in the master table.

Orphaned records are best explained using an example. In the database you have a customer table and an order table. Each order is linked to a particular customer using the customer’s primary key value stored in a field referred to as a foreign key. If the customer’s primary key is changed then all of the orders associated with that customer are no longer associated. These orders have become orphaned.

RI rules prevent this from happening by checking for associated records when an Update or Delete occurs. For instance if a CASCADE UPDATE rule was in place all of the records in the order table that were associated with the customer whose primary key changed would be updated to the new value. Using an RESTRICT UPDATE would not allow the user to change the primary key value if there were orders associated with the customer. There are two other rules SET DEFAULT and SET NULL which change the foreign  key to the specified default value or remove the value respectively.

Rules for deleting can also be defined using the same rules although they behave slightly different. A CASCADE DELETE will delete all records in the orders table that are associated with a customer which is deleted. I would recommend avoiding use of CASCADE DELETE rules in general. A RESTRICT DELETE is a good option because it prevents the customer from being deleted if there are related records in the order table. I like the SET DEFAULT UPDATE rule since it will set the foreign key value to the default making these records easy to locate.

Another aspect of an RI rule is that a record in the child table cannot be inserted without a valid value from the parent table in the foreign key field. This ensures that these tables remain linked, validating the input into the child table. This can be a very useful aspect of RI when used in conjunction with domain tables.

A domain table contains information about a particular category typically containing a code and a description. For example a marital status table could contain the following information:

Code Status
S Single
M Married
D Divorced
W Widowed

We can now setup an RI rule relating an employee table to the marital status domain table. This will ensure that if the status code ever changes the change will be reflected in the employee table. It will also ensure that a valid value, one of the codes defined in the marital status table, is used for the marital status. This essentially creates a constraint on the valid data for that table. The RI rule uses the primary key from the marital status table (Code) and relates it to the foreign key in the employee table (MaritalStatus).

Referential Integrity Rule Diagram

With this relation in place an error (7076) is reported if the user enters a value for marital status which is not in the marital status table. You can catch this error in your application and provide a more detailed description of the error.

Relation Error

RI Rules are a powerful mechanism for ensuring logical data integrity, however, they do have a few drawbacks. First ensure that your RI rule does not prohibit legitimate data entry from occurring. Creating too many rules or rules on the wrong tables valid user input may be discarded. Second, enforcing RI rules consumes resources on the server. This can delay record inserts, updates and deletes. In general use RI for your most critical relationships. If the data must have specific values or be related for your application to function correctly then RI is an excellent solution.

Constraints provide a database level mechanism for ensuring data integrity. This becomes more important when more than one application accesses the data. By centralizing critical constraints there is less margin for error when using the data from multiple client applications.

Monday, October 20, 2008

Field and Table Constraints

When tables are added to a data dictionary a host of additional features are available (see the comparison below). Part of these additional properties are constraints which include Minimum, Maximum and Null Valid on fields. You can also specify a table level constraint using an expression.

Free Table Properties

DD_Table_Properties

Minimum and maximum values are very useful when the data must be within a certain range. NULL valid is useful for ensuring that a field contains a value, although this can also be solved by assigning a Default value for the field. Providing a useful Failed Validation Message will be helpful to your users and people trying to create their own tools for your data.

These field level constraints can solve many of your validation situations. However, there are times where you need more control over validating the data. This can be done using a validation expression, which are defined at the table/record level. You can use any valid Advantage Expression Engine function within your validation expression.

The expression can be used to validate one or more fields in the table. For example if we want to ensure that the gender field contains only “M” or “F” we can use the following expression: NOT(Gender = 'M' or Gender='F').

You can also check multiple fields using this syntax. For example to ensure that at least one Phone number is specified you could use the following expression: WorkPhone = NULL AND CellPhone = NULL. These two expressions can also be combined: (NOT(Gender = 'M' or Gender='F')) AND (WorkPhone = NULL AND CellPhone = NULL). You will need to provide a very descriptive error message or do some additional validation at the client side to ensure that the user knows what they did wrong.

Sunday, October 19, 2008

Wrapping up Southwest Fox

SWFox_Booth We spent the last four days at Southwest Fox in Phoenix Arizona. It is the only FoxPro show in North America and this is our second year attending the conference. The show packs a lot of content into the four days with every session presented twice along with vendor sessions. There was even some pre-conference sessions this year.

Doug Hennig taught a session on using FoxPro and Advantage which was really well attended. I attended Doug’s session and I thought it was very well done. It is always good to see the community opinion of Advantage. I listed some of the highlights from the session below.

  • Indexes involved in relations need to have the .NOT.DELETED() condition. Doug wrote a program which automatically adds this condition to indexes involved in relations which is very useful.
  • If you add a full text search (content) index to a VFP table it will show up as invalid when you try to open it natively in FoxPro. However, the index can be added to a second CDX file allowing Advantage to use the index and allow the table to be used natively with FoxPro as well. The second CDX will be associated with the table in the Data Dictionary. You should use the Fixed or NotMaintained option since FoxPro applications will not be updating the index. You will need to do a reindex with Advantage to update the content index.
  • FoxPro allows expressions to be used for field validation rules. Advantage has a Default Value, Maximum and Minimum values for fields. However, you can use expressions as a Record Level constraint which can provide the same functionality. You define the validation expression on the Table Properties tab of the Table Properties dialog in ARC.

J.D. Mullin also attended the conference and went to some of the sessions. You can read his thoughts on the Retrofitting Client Server session on his blog.

Friday, October 17, 2008

A New FoxPro ScreenCast

Screencast - Converting a FoxPro DBC While attending the Southwest Fox Conference I was able to finish up a screencast on converting a DBC. It has been posted up on the Advantage Developer Zone under screencasts. The screencast walks through the process of creating an Advantage Data Dictionary based on the database defined in a FoxPro Database Container (DBC).

The process is automated by a FoxPro program called DBCConvert.prg. Simply open Visual FoxPro 9, connect to the database you wish to convert and run the prg file. A new Advantage Data Dictionary will be created which points to all the tables defined in the DBC. Views and relations can also be added to the Advantage Dictionary if they are not using any FoxPro specific options. See the screencast for more details.

Monday, October 13, 2008

FoxPro Grab Bag

This week is my FoxPro week. I will be at Southwest Fox in Mesa Arizona this week. We will be talking to FoxPro developers about how Advantage can help them. I am really excited about the show since I enjoyed it last year. The FoxPro community is very strong and full of great people.

Speaking of the FoxPro community Doug Henning will be doing a session on Advantage during the Southwest Fox conference. He posted a short (3.5 min) video about using Advantage full text search from Visual FoxPro. You can get the video here.

Since I am focusing on Visual FoxPro this week I thought I would point out some resources that are available. Code Central has been updated with a Visual FoxPro category. We have added links to several Advantage tools and documents under this new category.

I have also posted an example application, written in VFP 9.0 SP2, which uses SQL Passthrough to access an Advantage Data Dictionary. It uses the Advantage ODBC driver to connect to the data dictionary which was built for the Advantage book. The example application is built just like the other example applications written for the Developer’s Guide. Many thanks to Cary and Loy for all their hard work on both revisions of the book.

This months tech-tip is about converting a Visual FoxPro DBC into an Advantage data dictionary. A FoxPro prg file is distributed with the Advantage ODBC driver which reads the DBC and creates a data dictionary which can be used from an Advantage based application to access the Visual FoxPro tables. You can read the tech-tip here.

There are also a few screencasts demonstrating using FoxPro with Advantage.

As a final note our Advantage R&D manager J.D. Mullin did an interview with Andrew MacNeill for his FoxShow. You can listen to FoxShow podcast #49 which includes Andrew’s interview with J.D here.

Friday, October 10, 2008

Speaking at VSLive!

VSLive_Vegas I will be speaking at VSLive! in Las Vegas on Tuesday October 14th. I will be doing two presentations on the Entity Framework Model. The descriptions for my sessions are below.

Introduction to the Entity Framework Model

ADO.NET 3.0 introduces the Entity Data Model (EDM) which is based on the Entity-Relationship data model. This session introduces you to this new data access method used in the next version of Visual Studio. We will discuss how to use the EDM in your future applications along with its features and benefits. The session will also include a discussion of integrating this new data access strategy into your existing ADO.NET applications.

Entity Data for Multiple Applications

One of the major strengths of the Entity Data Model (EDM) is it's ability to model data in many different ways. This is especially useful when the same data must be shared by several different applications which serve various business needs. The same tables can be represented differently for Sales and Technical support applications, for example. This session will focus on techniques for modeling data for various needs. A common database will be used as a foundation for creating several applications which serve different business needs.

I will be posting the source code and some of the content here after the conference. I hope to see you in Las Vegas!

Wednesday, October 8, 2008

Using the SQL Execution Plan

If you have identified some SQL statements which are slow or unoptimized you can use the SQL Execution Plan tool in Advantage Data Architect (ARC) to obtain more information about it. Identifying these queries can sometimes be problematic but you can use SQL Logging to help identify many of them. You can also count on your QA department, you have one right, to help find problem queries.

Once you have located those pesky problem queries the fun part begins. Engineers love to solve problems and some queries can be doozies. As entertaining as these can be it sure is nice to have some help with figuring out why the query may be slow. Although Advantage Technical Support is filled with highly qualified people may I suggest using ARC first.

You can get the execution plan for any query by putting the query in the Native SQL Window and clicking on the Show Plan button (highlighted below). This will retrieve the execution plan from the server which will be displayed graphically in ARC. You can also use the SHOW PLAN keywords before your SQL statement to retrieve the execution plan.

ShowPlan

The graphical representation of the execution plan will show all of the intermediate steps that the SQL Engine will use to process the query. Keep in mind that the query is not actually executed when retrieving the plan. When the query is executed some additional optimizations may be applied to improve query performance. Any items which are not optimized will be highlighted with a red dot. You can see two of these on the screenshot below.

SQLPlan

SQLPlanDetail By hovering over an object (i.e. TABLE SCAN) additional information about the execution step will be displayed in a popup. The popup (example on right) displays; the operation with a description, number of executions, arguments and any warnings. In this case there is a warning on the Join condition not being optimized. The data shown in this popup is also displayed in the Data window which is normally docked at the bottom of the SQL Utility window. Detailed information about each execution step is contained in the data table.

SQLPlanData

The SQL Execution plan can give you valuable information about your query and help to identify potential problems. It is a quick way to evaluate your query and determine the need for indexes.

Monday, October 6, 2008

Using Query Logging

Advantage 8 included a feature for logging all queries run on the server. This can be a very useful tool for seeing what queries are being run and identifying unoptimized or slow queries. Query logging is enabled using a system procedure called sp_EnableQueryLogging which has five parameters.

The first is the name of the table to log to, second a flag to truncate existing data, third flag for logging only unoptimized queries, fourth minimum time before logging and finally an encryption password used if you wish to encrypt the table on free connections. If you are using a dictionary connection, you must be logged on with an administrator account and the query logging table will be added to the data dictionary.

The resulting log table contains the following information about the queries; ID, Start Time, Optimized, Return Code, End Time, Run Time, Database, Connection Name, Application ID and Query. By filtering on the Optimized field you can quickly identify queries which are unoptimized. You can also sort the data by Run Time to locate queries which take a long time to run. You can see the text of the query in the Query field.

To stop logging queries use the sp_DisableQueryLogging system procedure. This procedure stops query logging on the current data dictionary or for all free table connections. You can get more information about query logging in this tech-tip.

Friday, October 3, 2008

Upgrading and Expanding Advantage Database Server

Stop Service If you are upgrading from a previous version of Advantage the 9.0 install will detect and uninstall the previous version of the server. You will be prompted to stop the service and perform the uninstall of the previous version. This functionality was added in the installer beginning with version 8.0 of Advantage.

Use pre-existing Serial Number Advantage service updates are distributed as a full install which will upgrade to the latest version of the server. Therefore there is no need to install version 9.0.0.0 before installing the latest service update, currently 9.0.0.7. If Advantage is already installed the installer will detect the currently running server and read the license information. The user is prompted to use the current information eliminating the need to enter any license information during the upgrade.

Adding users to your installed Advantage Server, also referred to as an expansion, can be done without running the installation program again. There is a utility called AdsStamp in the server directory which allows for upgrading, changing the language setting or updating the license information. Clicking the License button will bring up the License Information screen with your current license information. If you have purchased an upgrade or are adding a replication license simply put the new code in the appropriate location. Clicking the Next button brings up the Owner Information screen. After entering the owner information and startup options clicking Finish applies the new license information to the server.

AdsStamp Utility

ProdInfo_V9

Upgrading and expanding Advantage is very simple and quick process. However, it is important to ensure that no clients are using the system prior to the upgrade. Therefore, it may be best to shut down the service prior to making any changes.

Wednesday, October 1, 2008

FAQs – September 2008

Delphi Client Kits

Advantage began supporting Delphi in 1997 and released our first TDataSet Descendant with version 4.4 of Advantage Database Server. Since that time we have supported many versions of Delphi from version 3 through version 2007, with version 2009 support scheduled for this year. An early release of these components are available here. For details on supported versions take a look at this post.

Over the years the client kits have matured as the Delphi IDE has changed. With these changes it became necessary to create different install packages for the newer versions of Delphi. If you are using Delphi 6 or 7 install the Advantage TDataset Descendant. If you are using Delphi 2006 or later install the Advantage Delphi Components, which were referred to as the Advantage Data Access Components (ADAC) in the past. This install includes the TDataSet Descendant as well as the Delphi .NET Data Components.

7035 Errors

If you are using the latest version of Advantage 8.1 (8.1.0.26) or Advantage 9.0.0.0 you may be seeing several 7035 “Record not visible” in your error log. This error occurs if an application attempts to goto a record that was appended in an active transaction.

Advantage uses a Read Committed Isolation level when using transactions. Therefore only the person who added or modified the records in their transaction can see the changes. All other users cannot see the new records or changes to records until the transaction is committed. Records that have been modified within a transaction are locked until the transaction is committed or rolled back. New records are not visible until the transaction is committed and deleted if the transaction is rolled back.

This is a known issue in version 8.1.0.26 and 9.0.0.0 of Advantage. The error is being incorrectly logged in the error log which could negatively affect performance. This issue has been resolved in the latest versions of Advantage, upgrade to the latest version (8.1.0.28 or 9.0.0.7). There is a KB item available on this issue.

Using SQL with ACE APIs

Programmers who use Alaska XBase++ with Advantage need to use the ACE API in order to run SQL statements. ACE API calls are also used in C or C++ programs. In fact ACE is used by all 32bit Advantage clients, such as the TDataSet Descendant and .NET Data Provider.

You must use at least two API calls in order to run an SQL statement on the server. You must first create an SQL object on the server (AdsCreateSQLStatement). Next you can execute a statement directly (AdsExecuteSQLDirect)or prepare a statement (AdsPrepareSQL)and then execute the statement (AdsExecuteSQL). The following example demonstrates connecting to Advantage and then running a simple query using the ACE API.

   1: Int32 iConnHandle;
   2: Int32 iSQLHandle;
   3: Int32 iCursorHandle;
   4: Int32 iReturnVal;
   5:  
   6: // Connect to the server
   7: iReturnVal = AdsConnect60('C:\Data\SampleDB\SampleDB.add',2,"Adssys",NULL, ADS_DEFAULT, &iConnHandle);
   8:  
   9: // Create a SQL statement object on the server
  10: iReturnVal = AdsCreateSQLStatement(iConnHandle, iSQLHandle);
  11:  
  12: // Run the SQL statement
  13: iReturnVal = AdsExecuteSQLDirect(iSQLHandle, "SELECT * FROM MyTable", &iCursorHandle);

Note: A return value other than 0 indicates an error has occurred. For brevity I did not include error checks in the sample code.

After you get a Cursor Handle back you can manipulate the results using API calls such as AdsGetString, AdsSetString, AdsSkip, etc.

Terminal Servers and Virtual Machines

Advantage runs on both terminal servers and virtual machines. Each terminal server client will be counted as an Advantage user. The management utility will list the Terminal Client Address for all users who are connected through a terminal server session. Local server cannot be used with a terminal server and will report a 5185 “Local Connection Restricted” if it is used with a terminal server.

I have use virtualization for test environments for several years. I believe that it is a fantastic test environment and allows for a lot of flexibility when recreating problems. I still favor dedicated hardware for Advantage over virtualized servers, however, as virtualization technology improves there will be very little difference for the users. As always select the environment that best supports your application and users.