Friday, May 29, 2009

Delphi Prism Screencast – Part 2

Part2 In part two of our four part series we will be demonstrating the use of the Advantage DataReader, Parameterized queries and executing stored procedures. Another panel with several controls have been added to the original form.

The entire series will be posted in the Screencasts section of the DevZone. A list of all the individual screencasts is below:

  • Part 1 – Connecting to Advantage and opening a table
  • Part 2 – Using DataReaders and calling stored procedures
  • Part 3 – Updating data
  • Part 4 – Administrative operations

Wednesday, May 27, 2009

Setting up an AIS Connection

Advantage has three connection types. Local (ALS) for the embedded server, Remote (ADS) for the client-server product and Internet (AIS) which includes encryption and compression when connecting to the client-server product. The AIS connection type is typically used when connecting across the Internet without using a VPN.

There are several configuration steps you must do to use an AIS connection type. These steps are listed below:

  1. Configure the Internet port on the Advantage Database Server
  2. Open the specified port on the firewall
    1. Advantage uses UDP/IP by default
    2. Clients can be configured to use TCP/IP see the help file for more information.
  3. Configure an Advantage Data Dictionary (ADD) for Internet access
  4. Create users with Internet access
  5. Add the INTERNET_IP and INTERNET_PORT settings on the client(s)
  6. Add a drive letter mapping for the client(s) to use

Configure the Internet Port

Use the Advantage Configuration Utility to specify which port Advantage will use when accepting AIS connections. If this port is set to 0 then connecting to the Advantage Server from the Internet is disabled. The Internet Port setting is located on the Configuration –> Communications tab.

Internet Port Configuration

Another consideration when connecting to Advantage with an AIS connection is latency. Response times across the Internet will be much slower than on a typical LAN and response times can vary depending on usage. The Advantage server will automatically disconnect clients that are not actively communicating with the server. To reduce the risk of an client connected across the Internet from getting disconnected you may need to adjust the Internet Client Timeout value on the server.

Configure Firewall Settings

Firewall configuration is specific to the firewall that you are using in your environment. Ensure that the port you specified in step one is open through the firewall for traffic in and out of the network. The default protocol is UDP/IP, however, the server can communicate via TCP/IP as well.

Clients will need to be configured to use TCP/IP communications. This can be specified in the ADS.INI file by setting the USE_TCP_IP setting to 1. Several clients also have a Communication Type option.

Configure an ADD for Internet Access

An ADD is required when connecting to Advantage using the AIS connection type. The ADD is used to authenticate the users when establishing the connection. You can still access any free tables which are in the same directory with the dictionary but I recommend that you use dictionary bound tables when possible.

You can choose from three levels of security; no authentication, authenticate and authenticate with encryption. You can also specify the maximum failed login attempts which allows the user the specified number of tries before locking them out of the dictionary. A database administrator must reset any locked out users.

Dictionary Internet Access

Create Users with Internet Access

Since the dictionary is used to authenticate users when connecting with an AIS connection type these users must exist in the dictionary. Any user who needs to use an AIS connection type will need to have Internet access enabled. This is configured on the User properties screen.

Enable Internet Access for User

Configure the Internet Address on the Client(s)

In order to connect to Advantage over the Internet from a client the client needs to know the IP address and Port to use when connecting to the server. This is specified in the ADS.INI file by specifying the INTERNET_IP and INTERNET_PORT settings for a given server name. The server name you wish to use becomes the heading for the section containing the IP and Port information.


Configure a Drive Letter Mapping

Since a client connected over the Internet cannot resolve any network shares you must either use a server-side alias or configure a drive letter mapping. The drive letter mapping is configured in the ADS.INI file under the Drives heading. The mapping must point to a share on the server name you specified when configuring the INTERNET_IP and INTERNET_PORT.


To connect the client specify the connection type to AIS or INTERNET and set the data path to the configured drive letter or server-side alias. An ADO.NET connection string would look like the following using the configuration above:

Data Source=J:\;ServerType=ADS_AIS_SERVER;User ID=Chris;Password=password;

Following these steps allows for successful connections to Advantage through the Internet.

Monday, May 25, 2009

Book Review – More Joel On Software

More Joel On Software Another collection of Joel Spolsky's blog postings in dead tree form. I have really enjoyed all of Joel's books and I have reviewed them all; here, here and here. This book is nearly 300 pages of wit and witticism from Joel. Although all of these posts AKA chapters are still available online I like having them in read anywhere form, especially since I read this book on a plane.

There are thirty six separate chapters in this book, which are further divided into nine parts. The first part Managing People contained many of the same postings that appear in Smart and Gets Things Done. Each section has some interesting information written in Joel's unique style which makes the material easy to read. I'll highlight my favorite chapter from each part.

There is one new post in section one, entitled "My first BillG Review" which is about a review of an Excel specification written by Joel by Bill Gates. I found it amazing that Bill Gates was so involved in every product in the beginning. The shear amount of material he had to go through must have been staggering.

In part two, Advice to Potential Programmers, I enjoyed every chapter. I wish I had some of this advice before getting started in computer science. The chapter "The Perils of JavaSchools" was quite interesting not only does the article quote Monty Python, but it brings up some good points on the change in curriculum of computer science. It really goes back to the debate, should programmers know C.  As you may have guessed from the chapter title Joel is firmly in the learn C camp. His assertion is that CS programs have been dumbed down to increase enrollment. A more challenging CS program identifies the people with the best abilities to become great programmers.

Part three is about program design, which was my favorite section. I found the chapter on "Building Communities with Software" the most interesting. Like Joel I think that this is an area that is rapidly expanding with very little knowledge of how it really works.

Part four on managing large projects contains three articles on why large projects (products) are so difficult. "Martian Headsets" puts the problem of interoperability into perspective with a very clever analogy. These new headsets begin with a single standard which is used by all headsets. Everything works great until a new feature is added to the standard. Now the new headsets have to have a way to still work with older devices while adding additional functionality for new devices, this may sound familiar. Joel uses these headsets to demonstrate just how complicated software, particularly an operating system, can get rather quickly.

Part five is another round of programming advice. I found all of these chapters very useful but since I am picking favorites I'll highlight "Making Wrong Code Look Wrong", which discusses naming conventions as a way to help debug code. Essentially by naming variables and functions consistently the code can be more easily debugged. Joel uses the example of passing strings in HTML. This can lead to Cross Site Scripting (XSS) therefore all strings should be HTML encoded to eliminate this vulnerability. By simply naming strings with an "us" prefix for  non-encoded (unsafe) strings it becomes apparent when the wrong type of string is passed to a function. A function should never publish an unsafe variable.

Part six contains two book forwards and and the first chapter of Smart and Gets Things Done. The introduction to Eric Sink's book "The Business of Software" contains a great story on Joel's first business.

Part seven covers running a software business. Once again each chapter contains very useful information, for Joel's take on refactoring check out "Rub a Dub Dub". I found the chapter on "Seven Steps to Remarkable Customer Service" to be the best of the bunch. Having started in Advantage Technical Support over 10 years ago I could really relate to this chapter. One of the most profound ideas he puts forth is the idea that developers should be involved in tech support, an ideal we use with Advantage. It is much easier to eliminate a common problem than to pay someone to answer the same question hundreds of times. This puts a lot of pressure on technical support since the problems that do occur are difficult to work through. I think our technical support team excels at this and have become quite remarkable.

Part eight on releasing software contains two chapters. The first is "Picking a Ship Date" and the second is "Camels and Rubber Duckies" which is about pricing, go figure. The biggest factor in determining a ship date is the number of features that you include in your product. Joel recommends ranking the features by how important they are since developers will generally choose the "fun" features. He also recommends dropping features to meet the ship date rather than making the ship date a moving target. As it turns out some of the features you drop may never have to be implemented at all.

The final part is on revising software. Joel revisits and expands on his idea of determining features in "Set Your Priorities". He proposes getting a list of features from everyone you can; developers, sales, tech support and customers. Take these suggestions to a meeting with representatives from these groups. Have them vote on the features and prioritize them removing the features that don't get many votes until you have a manageable number. Take this list and allow people to vote on the features they would most like. Everyone gets the same amount of votes and the features with the most votes win.

The bottom line: I thoroughly enjoyed this book, I like Joel's writing style and he has a great way of conveying complex information in an easily digestible form. As I said in the beginning all of the chapters in the book are still available on Joel's website making the book (blook) somewhat redundant. However, having a printed copy of the book makes it a nice reference.

Friday, May 22, 2009

Delphi Prism Screencast Series

Part1Right on the heels of the Advantage with Delphi 2009 Screencast series I have been working on the next one. Over the next four weeks I will be posting the Advantage with Delphi Prism Screencast series. 

In this four part series I will be walking through building an example application using Delphi Prism. The example application is based on the sample application found in Cary Jensen's excellent book Advantage Database Server: A Developer's Guide. Part one of the series creates a new project and demonstrates connecting to Advantage, using the AdsCommand object, AdsDataAdapter object and binding a table to a grid.

The entire series will be posted in the Screencasts section of the DevZone. A list of all the individual screencasts is below:

  • Part 1 – Connecting to Advantage and opening a table
  • Part 2 – Using DataReaders and calling stored procedures
  • Part 3 – Updating data
  • Part 4 – Administrative operations

Wednesday, May 20, 2009

Advantage Unicode Preview

The Advantage R&D team has been working on Unicode support for the next version of Advantage. In order to make this feature as robust as possible we will be releasing some technical previews of the Unicode support. The first technical preview is now available, you must register to receive the preview. Here is the message from JD Mullin:

If you have an application you would like to start modifying to use these new field types, and would like to provide feedback on our early phases of development, please send an e-mail to and include "Attn:JD" in the subject. In addition, please also specify your development environment (Delphi, ADO.NET, etc).

Unicode support will be implemented in several phases. The current technical preview has been released with local server support only. The first technical preview includes a new build of Advantage Data Architect (version 9.9) and two clients ADO.NET and the Advantage Delphi Components.

ARC Unicode Support

The technical preview includes the following features:

  • Three new field types (nchar, nvarchar, nmemo)
  • SQL scalar function support (i.e. UPPER, LOWER, …)
  • WHERE clause, ORDER BY and GROUP BY
  • Unicode parameter data
  • CAST() and CONVERT()

There are several limitations in this first technical preview the following items are not yet supported:

  • Unicode Field Names
  • Indexes on Unicode field types
  • Expression engine functions called with Unicode field types

For all the details and to participate in the program send an e-mail to ATTN:JD in the subject line.

Monday, May 18, 2009

File Names and Paths with Linux

Linux is a case-sensitive operating system which can cause problems when opening tables from a Windows or DOS based application. The path must be passed in with the same case as it is stored on the Linux file system. This isn't an issue with Windows based applications so paths and table names are usually written using mixed case during development.

There is a configuration option called LOWERCASE_ALL_PATHS for the Advantage Database Server for Linux which causes the server to change any path or table name to all lower case prior to opening the requested table. This is an integer value 0 for false and 1 for true. The default is 0 meaning that the path will be used tried exactly as it is sent to the server.

The files must be stored in the file system with all lower case names for this to work. The following script will change all of the filenames in the current directory to all lower case. Remember that the directory names must also be in all lower case as well. From a terminal window change to the directory which contains your tables and type the following command on a single line.

find -type f | while read i; do j="`echo $i | tr [[:upper:]] [[:lower:]]`"; [ "$j" != "$i" ] && { mv "$i" "$j" || echo "ERROR: $i"; } ; done

By setting LOWERCASE_ALL_PATHS to true all files created by the server will always be created in all lower case. This ensures that a newly created file will be accessible to the client(s). This applies to any Create Table, Create Index, Backup and Restore operations.

If your application supports both Windows and Linux servers you should take care when naming your files. You can use the following SAMBA settings to ensure that files copied from Windows will be written in Linux using all lower case. Add or change the following settings in the /etc/samba/smb.conf file:

case sensitive = yes
preserve case = no
short preserve case = no
default case = lower

With these settings all files copied from a Windows client to the Linux server through SAMBA will be written in all lower case. Using these settings along with LOWERCASE_ALL_PATHS should avoid any 5004, 7008, 7041 and 7077 errors due to file case issues.

Friday, May 15, 2009

Advantage with Delphi 2009 – Part 5

D2009_Part5 In the last part of our Advantage with Delphi 2009 series we add ranges and seeks to our application. Both of these operations require an active index to be used. First we create another groupbox with two input fields. These allow the user to set the top and bottom of a range.

A final groupbox is added as a find box. This will allow the users to perform a seek using the active index. Two types of seeks are demonstrated a FindNearest and FindKey.

The entire series will be posted in the Screencasts section of the DevZone. A list of all the individual screencasts is below:

  • Part 1 – Connecting and Viewing data
  • Part 2 – Inserting and Editing data
  • Part 3 – Filtering and Searching
  • Part 4 – Index Operations
  • Part 5 – Ranges and Seeks

You can also view the entire series on a single page.

Wednesday, May 13, 2009

Delphi Live 2009

Delphi Live begins today with some pre-conference sessions. The main conference begins tomorrow in San Jose. Several members of the Advantage team are attending the conference and Sybase iAnywhere is a sponsor for the event.

Joachim (Jojo) Dürr, an Advantage Systems Consultant from Germany, will be teaching a session on Database Notifications on Thursday at 10:45 AM. Jojo also wrote a book "Datenbanken mit Delphi" (Databases with Delphi). The book is written in German and you can get more information from his website.

Cary Jensen will also presenting two sessions Fundamentals of ADO.NET for Delphi Prism Developers and In-Memory DataSets Compared: TClientDataSet versus DataTable. He and Marco Cantu will also be doing a power workshop today entitled Multithreading Master Class. Additionally Cary has begun blogging and he wrote a great article on Advantage.

If you are attending the conference please stop by the Advantage booth in the expo hall.

Friday, May 8, 2009

Advantage with Delphi 2009 – Part 4

D2009_Part4 In part four of our five part series we discuss indexes. This is the longest screencast in the series ( ~ 15 min ) but it covers reading index names, setting indexes and creating indexes. A drop-down combobox is added to the form which lists the available index orders for the table. The index is set by selecting one of the index names. Choosing "Natural Order" clears any index and shows the data in the order it was added to the table.

A third form is added to the application which is used for creating a new index on the table. It demonstrates setting various index options as well as opening the table in an exclusive mode.

The entire series will be posted in the Screencasts section of the DevZone. A list of all the individual screencasts is below:

  • Part 1 – Connecting and Viewing data
  • Part 2 – Inserting and Editing data
  • Part 3 – Filtering and Searching
  • Part 4 – Index Operations
  • Part 5 – Ranges and Seeks

You can also view the entire series on a single page.

Wednesday, May 6, 2009

Windows 7 – First Impressions

Like most MSDN subscribers I spent quite a bit of time on the morning of April 30th trying to download Windows 7 RC1. After lots of refreshes and a few restarts of the download I finally got the image. I poked around with the Windows 7 beta for a little while but I never had much time to dedicate to it.

This time around I am going all out, I have installed the 64-bit version on my home machine and I have a 32-bit VM. I did some initial testing with Advantage with some mixed results. The install for both the server and ARC went smoothly with no errors. Making a remote connection to the Advantage service from the machine worked perfectly.

My next step was to try and establish a connection from a different machine. My first step was to configure the Windows Firewall to allow communication to and from Advantage. This process is much different on Windows 7 than prior versions of Windows. It took me a bit of digging to figure out how to get the firewall configured. The easiest way was to simply add the Advantage service (ADS.EXE) as an authorized application, which only took a few mouse clicks. The best part about this process is it added an exception to the firewall for both TCP and UDP traffic.

I then tried to connect from my host machine to my Windows 7 virtual machine. Discovery doesn't always work too well with virtual machines so I specified the default port to facilitate the connection. My first remote connection was successful which was great. However, I did not get a list of available tables on my free table connection. I double checked my settings making sure that I was ignoring rights and that the path was correct. This did not fix the problem.

Since I knew the path was correct and I had a connection I opened an SQL window and tried opening a table with a SELECT * FROM statement. This is when I got a 7078 error which gave me a big hint as to the problem, you may remember the FAQ about strange 7078 errors. In this case a look in the error log revealed a 8026 error which can be caused when the server cannot resolve the path correctly. The 8026 error occurred when using a share and when using server-side aliases.

Since I was using a VM for my initial testing I switched the networking mode from NAT to Bridged. After making this switch I was able to successfully open the tables. I still cannot reliably get a listing of available tables probably due to permission issues between Windows 7 and Windows XP. However, this should not effect most applications since a listing of tables is not normally required.

The success I had with these initial tests is encouraging and I have enjoyed working with Windows 7. This release candidate seems quite solid and I would expect to see a full release of Windows 7 by the end of the year. Many people are speculating that it will be released as soon as mid-summer.

Keep in mind that Windows 7 is not an officially supported platform for Advantage. For the latest information on supported platforms visit the supported platforms page.

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:

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.

Friday, May 1, 2009

Advantage With Delphi 2009 – Part 3

Advantage with Delphi 2009 - Part 3 In the next part of our Advantage with Delphi 2009 Screencast series we are adding filtering and searching capabilities to the application. First we add a new editbox so users can enter a filter condition for the table. Clicking the SET button sets the user entered information to the filter condition and the table is filtered.

Another group box is added which contains two edit boxes allowing the user to enter a field and a condition. This information is used by the LOCATE command. The TAdsTable.Locate command determines the best way to find the specified data making it more efficient than a traditional ISAM locate. For example; if an index is available the Locate method will actually perform a seek.

The entire series will be posted in the Screencasts section of the DevZone. A list of all the individual screencasts is below:

  • Part 1 – Connecting and Viewing data
  • Part 2 – Inserting and Editing data
  • Part 3 – Filtering and Searching
  • Part 4 – Index Operations
  • Part 5 – Ranges and Seeks

You can also view the entire series on a single page.