Wednesday, December 31, 2008

FAQs – December 2008

Strange 7078 Error

The 7078 Error "The Advantage Database Server cannot authenticate the user" is a very straight forward error message. Essentially the username and password supplied for the Advantage Data Dictionary (database) were incorrect. This error is easily corrected by supplying the proper username/password combination.

However, what if you get this error when you supplied the correct username and password or stranger still when you are connecting to a directory of free tables. The answer lies in the Advantage error log (ads_err.adt) where you need to look for other error codes that occurred at approximately the same time as the 7078 error. See the example below:


In this case the important error is the 8026 "Error obtaining server drive information from server and share names". To fix this error verify that the path is correct, the path can be located in the FileName column of the error log, and that the share exists.

File Errors with Version 9.1

In version 9.1 of Advantage we changed the way that files are opened when in proprietary locking mode. In the past these tables were opened in a "deny write" mode which allowed other programs to access them in read-only mode. This could lead to index corruption since other programs could lock bytes in the file causing Advantage reads and writes to fail.

With version 9.1 tables are now opened so only the server can access them. If you still need to access the files as read-only you can set proprietary locking to be non-exclusive. This is done by setting the NONEXCLUSIVE_PROPRIIETARY_LOCKING flag in the registry (Windows) or in the Advantage configuration file (Linux and NetWare).

You can access data concurrently with Advantage and a non-Advantage application using compatibility locking when using DBF files. Advantage proprietary files (ADT) are always opened using proprietary locking.

6097 Errors

The 6097 "Bad IP address and/or bad port specified in connection path or in ADS.INI file" seems like a very strait forward error. It can usually be resolved by verifying that the correct IP address and port number have been specified. You may also need to ensure that any firewalls allow UDP traffic for the specified port. With version 8.1 or newer you can specify TCP (see this post for details) on the clients instead of UDP.

There is one other situation where you may be receiving 6097 errors. This error can be reported when you are evaluating Advantage. If the evaluation code has expired the server will not respond to any client requests and the client will report a 6097 error as described in this KB Item. This behavior has changed in version 9.1 which will now report a new error code 7046 "Evaluation Period Expired"

Windows /3GB Switch

A 32-bit operating system allocates 4GB of virtual memory address space. This is split in half with 2GB allocated to the operating system (kernel) and the other 2GB reserved for applications. This default setting can be overridden by using the /3GB switch in the Boot.ini file. This changes the virtual memory allocation allowing for 3GB of virtual memory address space for applications and the other 1GB for the operating system.

This can cause some unexpected consequences and may not improve performance. We do not recommend using this switch when running Advantage on a 32-bit Windows machine. In order to address more memory with Advantage use a 64-bit operating system and the 64-bit version of Advantage.

For more information about the /3GB switch I recommend this series of articles by Raymond Chen from the Windows team.

Friday, December 26, 2008

Advantage Configuration Forms

I have been working on updating the Advantage Visual Studio demos over the past week. With the Christmas break I am a bit further behind than I wanted to be but I was able to get a lot of work done. Take a look for the updated demos, including VS2008 solutions to be available from Code Central in early January.

In the meantime I wanted to discuss some of the improvements that I have made. The current samples were written in VS2003 so I have made an effort to update them for VS2005 and VS2008. For example, the applications use a custom application settings class instead of using the built-in classes included with VS2005 and newer. I have changed the code to use these built in settings classes.

Ensuring that demos have the proper settings can be problematic since everyone has a different environment. We can’t always rely on the demos to be installed in the same place or with a pre-determined directory structure. In an effort to make the demos more flexible and customizable I have added configuration screens. There are two versions one for basic configuration and the other for advanced configuration. I am considering combining them into a single form with a "more >>" type button but I am not sure yet.

The basic configuration form is similar to the open form dialog in Advantage Data Architect. It allows the user to specify the path, username, password, table type, character type, server type, locking mode, exclusive, read only and rights checking. When the form loads the values for each of these options is loaded from the application settings file. When the OK button is pressed the settings are saved. The basic configuration form is shown below.


  The advanced configuration dialog allows the user to specify all of the configuration options. Details about these settings can be found in the help file under the AdsConnection.ConnectionString property.


I am still doing some testing but I will get all the source code posted to Code Central as soon as it is done.

Wednesday, December 24, 2008

Geek Gifts

Blogger-TShirt I realize that it is the day before Christmas and all but I thought I would post about a few items on my list for this year. The first one is this awesome t-shirt from Successful Software. Andy Brice is also donating a portion of the sales of his shirts to charity. Check out his blog post "What do you buy a programmer for Christmas" for all the details.

Of course the latest science fiction / fantasy and in the case of this year comic book hero movies are always an excellent choice. If by some chance you haven’t gotten Iron Man yet your missing out on an excellent movie. Of course the #1 movie of the year "The Dark Knight" is on my list as well. I would have already gotten a copy but being so close to Christmas… I would be remiss if I didn’t mention Wall-E since it was a fantastic movie. Of course Pixar always makes excellent movies which appeal to both kids and adults.

iphone_home Gadgets are always on a geek’s list as well. This year there were plenty of amazing new gadgets to choose from. The iPhone released the 3G model prompting the serious geeks to liquidate their less than a year old generation one iPhones for the latest and greatest. This is a very popular device at iAnywhere and our development teams are hard at work writing code to support them.

The Blackberry Storm has also been very popular. The click screen has impressed many users who like the tactile feedback that it gives. The technology isn’t all that clever, there is one large button under the screen which produces the "click", however, the result is an excellent user experience.

Game systems are another popular gift, although I never seem to have time to play the Wii at my house. Although it may just be that I can’t drag my kids away from it long enough for my turn. I have found this system to be the best for kids since it is very interactive with the Wiimote and the games seem to be on a lower violence level. If your looking for HD and intense shooter games you should go with the Xbox 360 or Playstation 3.

I opted for some good books for one of my gifts so look for some book reviews coming in the near future. I hope that you have a very Merry Christmas and have a chance to spend time with family and friends this holiday season.

P.S. You can see the Sybase Holiday Card here.

Monday, December 22, 2008

Book Review - Peopleware

Peopleware: Productive Projects and Teams by Tom DeMarco and Timothy Lister is a book about your companies most important asset its people. It specifically focuses on people who work in software development but many of the principals apply to any employee. The book is broken up into six parts, five from the first edition and a new section added for the second edition.

The overriding theme of the book is that the technology business is more about people and less about the technology. An interesting notion for those of us who really love technology. However, it is ultimately the people who make the technology work. Therefore, the majority of the effort should be spent keeping the people happy and productive. As with many of the books I have read this year, there are way too many suggestions in this book to be implemented, in fact the authors acknowledge this in chapter 26.

It doesn’t take great presence to see that one of these measures is all your likely to pull off successfully. If you try more, you will just diffuse your efforts. The rumpus you’ll raise will be more confusing than constructive, and your colleagues and those above you in the corporate hierarchy are likely to write you off as a whiner. One changes is plenty. Even a single substantive change to the sociology of your organization will be a mammoth accomplishment.

I wanted to point out a few of my favorite parts from each section of the book. I have listed the sections below as a reference.

  • Part I: Managing the human resource
  • Part II: The office environment
  • Part III: The right people
  • Part IV: Growing productive teams
  • Part V: It’s supposed to be fun
  • Part VI: Son of Peopleware

My favorite chapter in part one is chapter two “Make a Cheeseburger, Sell a Cheeseburger” with the basic point being software development is not a production process. Production lines are efficient because they perform the same operations the same way every time and break the tasks down into small manageable pieces. Software development is not about grinding out code the same way every time, it is a creative endeavor. Perspective is also a big topic in part one, chapter three3 “Vienna waits for you” discusses work / life balance. It doesn’t hurt that they use quotes from a Billy Joel song to emphasize their point.

Part two discusses the work environment and unfortunately this is something we all have very little influence over. Noise and privacy seem to be a recurring theme throughout the chapters in this part. With a highly technical field like software development interruptions are a major productivity reducer. When someone is focused on what they are doing they are the most efficient. By interrupting them while in this “zone” it takes several minutes to get back into the “zone”. The chapters discuss reducing interruptions through the use of space, offices, turning off telephones and e-mail among many others.

Part three discusses hiring of good people. Again software development is more about who is doing the work than how the work is getting done. Managing involves getting the right people in the jobs the best coaches recruit the best players why should our industry be any different. There is so much good information in this section it is difficult to pick a few examples. However, two points that really stood out for me were that retention is very important. Many people overlook the cost and lost productivity of turnover. Second is the idea of self healing or the reduction of Methodologies. The more structured a system the less able it is to deal with changes or the ability to handle special cases. Read chapter seventeen for “the rest of the story” (with all due respect to Paul Harvey).

Part four discusses building teams and can be summed up with one word; synergy. Great teams do more together than another group of individuals. Chapter nineteen discusses IBM’s Black Team which was a team of testers who “jelled” into a highly productive team within IBM. The Black Team’s mission was to test and break software being developed at IBM. They took on their own character, taking pride in being the “bad guys” within the company. Although this section points out what is good about great (jelled) teams and gives an example (the Black Team) the authors admit they don’t know exactly how to build one. However, they do have an entire chapter on how not to create a great team called “Teamacide”. Avoiding the seven techniques in this chapter give you a greater chance of creating your great team. Several other examples and suggestions for creating these teams are also presented, so it is not all doom and gloom.

Part five debunks the notion that “work is supposed to be onerous”. Chapter twenty-four “Chaos and Order” provides some methods for finding new and challenging things to work on. Software developers are problem solvers by nature so what happens if we run out of problems to solve? Another interesting point is that sometimes you have to let some employees be “free electrons” people who have no strict job function or description, think “chief software architect”. These people are free to find their own ways to benefit the organization.

Part six is an entirely new section for the second edition which consists of eight chapters. It includes some new thoughts on “Teamacide” and on competition. I have always thought that competition between teams can be a bad thing. If people feel threatened by their peers being better or knowing more than they do it results in poor productivity. I have been in many situations where something couldn’t get done because Susan is out of the office and she is the only one who can do that. Coaching and mentoring is vital to keeping a company running and for great teams to develop, competition can greatly reduce this mentoring.

Some other topics in part six include investing in people, employees are “Human Capital”. People hate change so you must work to make change happen. Although not all change is good, see the chapter twenty-nine “Process Improvement Programs”. Chapter thirty-three “The Ultimate Management Sin Is…” provides some great insight and statistical evidence. I won’t spoil the surprise but a hint can be found in The Mythical Man Month. The final chapter was very fitting, in my opinion, it wraps it up with a story with a “what’s it all for” message. Ultimately we work to build things and what is more rewarding than building a community and seeing people grow and develop within your organization.

As you can probably tell from my long posting on this book I enjoyed it immensely. I would recommend this book to anyone who manages software developers or software developers who are looking for ways to improve their productivity. It may be as simple as getting out of those cubicles or by getting onto a team you can “jell” with.

Friday, December 19, 2008

Converting from Free Tables to a Data Dictionary

Advantage Data Dictionaries (databases) have many benefits over using free tables. These include many relational features such as; Constraints, Referential Integrity, Triggers, Views, Stored Procedures and SQL user defined functions. Data dictionaries are also required for replication between two or more servers.

DD_AddTables The best part of data dictionaries is how little effort is required to get started using them. It is extremely easy to add existing tables to a data dictionary using ARC. You simply right-click on the tables icon in the tree view and choose "Add Existing Table(s)". A file open dialog will be displayed allowing you to select all of the tables you wish to add to your data dictionary. This will also include any structural index files associated with the table.

Adding users is also a very simple process, the ADSSYS (administrator) user is added when the dictionary is created. I would recommend creating at least one additional account for normal data access functions. Once users are created assigning rights has been made easier with version 9.x because of dictionary roles. Simply assign the appropriate rights to the DB:Public group and all new users will inherit these rights since every user is automatically a member of this group. You don’t have to enforce dictionary logins or rights checking if you don’t wish to.


After the tables have been added and you need to make a slight modification to your connection string. The data path you specify must now point to the dictionary file (.add). Depending on the user access you have chosen you may need to specify a username and password in your connection. You can also prompt the user for this information and then add it to the connection string.

Once the connection to the dictionary has been established you access the data in the same manner as you do with free tables. Therefore existing code should not have to change, aside from the connection strings, to implement a data dictionary. Once you have added all your tables to the dictionary you can move on to using other features like: views, triggers, stored procedures and SQL UDFs.

Wednesday, December 17, 2008

Encrypted Free Tables and Visual Studio

I discussed working with encrypted free tables in my November FAQ, however, the topic deserves a bit more detail. The best way to work with encrypted tables with any of the Advantage supported development environments is to use a data dictionary. However, this is not always possible or even desirable.

To use encrypted tables with the Advantage .NET data provider you must specify the encryption password in the connection string and open the table using a TableDirect command type. To open an encrypted table using SQL with the Advantage .NET data provider you must use a database connection. The following code snippet demonstrates opening an encrypted free table.

   1: AdsConnection cn;
   2: AdsCommand cmd;
   3: AdsDataAdapter da;
   4: DataSet ds;
   6: string sConnect = "Data Source=C:\\Data";
   7: sConnect += ";ServerType=Remote";
   8: sConnect += ";EncryptionPassword=password";
  10: cn = new AdsConnection(sConnect);
  11: cmd = cn.CreateCommand();
  12: cmd.CommandType = CommandType.TableDirect;
  13: cmd.CommandText = "Customer";
  15: cn.Open();
  17: da = new AdsDataAdapter(cmd);
  18: ds = new DataSet();
  20: da.Fill (ds);

You can encrypt and decrypt tables using an Advantage Extended Data Reader which has EncryptTable and DecryptTable methods. To use the extended data reader you must use a CommandType of TableDirect and the ExectueExtendedReader method. To use EncryptTable and DecryptTable you must open the table exclusively by setting the Shared connection string keyword to False.

There is an example application available on the Advantage DevZone which demonstrates these methods. It is listed in Code Central under C#/VB.NET and is called Free Table Encryption Demo. It is a Visual Studio 2005 project which contains C# and VB.NET code.

Monday, December 15, 2008

The 100 Post Milestone

I have reached my 100th blog posting and I thought I would take this opportunity to be narcissistic to revisit some of the most popular postings to date. Surprisingly, the most popular post to date is “What’s in a Language?” which I posted back in June. This topic was widely discussed in the programming blog-o-sphere in May which may account for its popularity. I also found that June was the most trafficked month on my blog with the top 5 postings all in that month.

So I decided to adjust my thinking a bit and choose the postings which are consistently read as opposed to the ones that got the most hits. So without further ado here are the top 5.

  1. FAQs – June 2008
  2. The Advantage Cache System
  3. Book Review – Joel on Software
  4. FAQs – July 2008
  5. Troubleshooting Advantage Connection Issues

I was happy to see that my FAQs were being referenced regularly, since that was the intent after all. My book reviews also tend to be viewed quite often which encourages me to continue posting them as I finish books, feel free to vote against this in the comments :). With the article of connection issues rounding out the top 5, three of the postings were about resolving problems. So have no fear I will continue to post FAQs each month and I will do more posts about solving problems.

Thanks to everyone that has been reading through the first 100 posts. If your new WELCOME, glad to have you aboard. Hopefully, you have found something interesting or useful here. 

Friday, December 12, 2008

Advantage 9.1

If you subscribe to the Advantage Announcements RSS feed this may seem like old news. Version 9.1 has been available since November 21st. However, the physical product is now shipping. If you order an Advantage license and want a disk you will now receive version 9.1.

Version 9.1 is a roll up of all the service updates that have been done since version version 9.0 was released in March. For a full list of all the changes included with this release you can refer to this posting.  I would also recommend reading the effects of upgrading for additional information.

There was another small change made to the Delphi components after 9.1 was released. This applies to all Delphi clients and fixes a problem which prevented Delphi grids from going to the bottom of a static SQL result set. If you have check for updates turned on in Advantage Data Architect you should have already been prompted for the update.

Shortly after the 9.1 was posted the latest service update of 8.1 ( was posted. You can get all the details on the changes in this service update using this link.

Wednesday, December 10, 2008

The INTERNET connection type

Advantage has three connection types LOCAL (ALS), REMOTE (ADS) and INTERNET (AIS). The AIS connection type provides some enhanced security and performance when connecting over an unsecure network like the Internet. These enhancements are achieved through encryption and compression which are configured on the client and server.

There are three settings that can be configured on the server for AIS connections. They are the Internet Port, Client Timeout and Compression settings.  The specified Internet port must be opened on the firewall for users to connect. By default, Advantage communicates via UDP, however, you can configure the clients to use TCP/IP if you prefer. The client timeout is the amount of time the server will wait between communication from the client. If the client does not respond to the server within this time period the client is disconnected. Compression can improve performance over slow connections and is enabled by default for AIS connections.


Although the documentation states that you cannot connect to the server using an AIS connection type if this port is not configured, it is possible refer to this KB Item for more information.

After the server has been configured for AIS connections an Advantage Data Dictionary must be configured to allow Internet connections. This is specified on the Security tab of the dictionary properties dialog. Internet access must be enabled, a security level specified and the max number of login attempts. There are three levels of security; No Authentication, Authenticate and Authenticate & Encrypt. I recommend using Authenticate & Encrypt for the most secure connection. You can get more information about the different security levels from the help file.

Dictionary Properties Security

Once Internet access has been configured in the dictionary access must be granted to users. Only users who have been granted Internet Access rights will be able to connect using an AIS connection type.

Dictionary User General Properties

After the server and data dictionary have been configured we can configure the client. This can be done using the ADS.INI file or within the connection string depending on the client you are using. You can specify the compression settings, INTERNET_PORT and INTERNET_IP within the ini file. The internet ip and port are specified for each server the client wishes to connect to and allows for a standard UNC path to be specified (ex. \\MyServer\MyData) for the client. An example entry is below:


On a final note although you must make your AIS connection through a data dictionary free tables can still be accessed. Any free tables in the data dictionary path can be opened once the connection is established.

Monday, December 8, 2008

Book Review – The Mythical Man Month

The Mythical Man Month by Frederic P. Brooks is a collection of essays on software engineering based on his work with IBM. Professor Brooks has some excellent insight into the workings of large software engineering projects and teams. Each essay (chapter) focuses on a specific topic in the process of creating software.

This book is considered a “classic” in software engineering books. The examples and data are based on Dr. Brooks experience with the System 360 and OS/360 which were developed in the mid 1960s. It is very interesting reading about shared computer time, OS/360 was built on mainframes before the advent of the personal computer. The first printing of the book was 1975.

The most valuable essay is in chapter  2 entitled appropriately the Mythical Man Month. This chapter focuses on five reasons why software doesn’t get done on time I thought I would share two with you. First programmers are optimists we tend to overestimate our abilities to get something coded. Second that man months are interchangeable, hence adding more workers reduces the time or having more time requires fewer workers. In fact, the more people added to a project and the later people are added actually increases the time the project may take. This is due to the increased complexity of communication between team members and the time it takes to get new team members up to speed.

Chapter six provides a good discussion on how teams communicate. This is further emphasized in chapter seven which demonstrates the failure of a project to the breakdown of communication. the next several chapters highlight why it is difficult to estimate software because of its technical and artistic qualities.

One of his most interesting recommendations is to build one system to throw away which takes an entire chapter. The point being that the first design is rarely 100% correct and design flaws are easier to recognize with a prototype. By taking the time to try out a concept you may discover a better way to accomplish the task. This chapter was introduced with a quote from Franklin D. Roosevelt:

It is common sense to take a method and try it. If it fails, admit it frankly and try another. But above all, try something.

Chapter 12 discusses the use of good tools. This includes using appropriate languages and determining when to use a commercial tool as opposed to building it yourself. However, one excellent point is for developers to share the tools that they use. If you have developed a piece of code that provides a great solution to a common problem share it with your team. Chapter 13 has many practical tips on debugging programs. Unsurprisingly Dr. Brooks goes back to good design as a key piece of debugging.

Chapter 14 discusses software estimation and how to avoid making millstones out of milestones. Make sure that the milestones that are set reflect realistic goals. Chapter 15 discusses providing information to the users of your software. This is written from a tools or OS perspective so it focuses on developers but many of the principals could be translated into modern end users.

The final chapters were added for the 20th anniversary edition. Chapter 16 is a 1986 paper called “No Silver Bullet”, which discusses which asserts

There is no single development, in either technology or management technique, which by itself promises even one order-of-magnitude improvement within a decade in productivity, in reliability, in simplicity.

Chapter 17 is a 1995 paper called “No Silver Bullet Refired” which is Dr. Brooks rebuttal to the criticism and “silver bullets” proposed since the original paper.  The assertions made in the original paper have held up very well and no “silver bullets” have been found. Chapter 19 is a discussion of the original book contents after 20 years.

Chapter 18 provides an overall summary of the book and is a good place to start if you want to get a high level overview of the book contents. All of the major points for each chapter are listed in the summary. You can get more details about the points by going back to the chapter containing the points you are most interested in. This chapter also includes some updated comments on the points made in the first edition. Interestingly most of his original points have stood the test of time.

Overall the book has many interesting ideas and recommendations which have stood the test of time. I found “No Silver Bullet” very interesting and “No Silver Bullet: Refired” is an excellent update of the original paper. The book reads a bit like a text book in some of the essays, presenting the material in a dry fashion. This made the book a bit slower to read than some of the others I have read this year. However, I found the book to be worthwhile and a nice perspective on the computer industry as it was in the 1960s and 1970s.

Friday, December 5, 2008

Advantage Supported Platforms

I often get asked if Advantage supports Windows 2008 server and the answer is . . . . YES. It was tested with version 9.0 and added to the officially supported platforms. With the latest service release of Advantage 8.1 ( Windows 2008 Server is officially supported.

To clarify the supported platforms I put together the following table. I included version 7.x since many partners are still using this version. However, version 7 has reached end of life and is no longer officially supported.

Platform 7.x 8.x 9.x
Windows 2008 Server 64-bit N Y1 Y
Windows 2008 Server 32-bit N Y1 Y
Windows 2003 Server 64-bit(including R2) Y Y Y
Windows 2003 Server 32-bit(including R2) Y Y Y
Windows 2000 (Server and Professional) Y Y Y
Windows Vista (Business and Ultimate) 64-bit N N Y
Windows Vista (Business and Ultimate) 32-bit Y Y Y
Windows XP (Professional) Y Y Y
Windows NT Y Y N
Windows 9X Y N N
Linux 64-bit2 N N Y
Linux 32-bit3 Y Y Y
Netware 4.x Y Y N
Netware 5.x and greater Y Y Y

1 – Version or newer 
2 – Requires glibc 2.3.5 or greater and kernel version 2.6 or greater
3 – Requires glibc 2.3.2 or greater and kernel version 2.4 or greater

Although 32-bit applications run well in 64-bit environments I would recommend using the 64-bit version of Advantage (version 9.0 and above) when using a 64-bit operating system. However, if you are using external libraries for stored procedures and triggers they must be compiled as 64-bit DLLs.

Advantage client applications cannot run on NetWare servers and 64-bit clients are currently limited to using the Advantage Client Engine directly ( i.e. C/C++) or by using the Advantage .NET Data Provider.

I posted a list of supported IDEs in September. The official list of supported operating systems is listed here.

UPDATE: A detailed datasheet describing the supported platforms and development environments (IDEs) has been posted here.

Wednesday, December 3, 2008

FAQs – November 2008

Opening Encrypted Free Tables

To view encrypted free tables you must supply an encryption password. This can be done by using the AdsEnableEncryption API. This API sets the encryption password for the table and allows encrypted records to be decrypted and viewed. All record updates and inserts will also be encrypted when being written back to the table.

When using the Advantage .NET Data Provider encrypted free tables are handled a bit differently. The encryption password is set in the connection string using the EncryptionPassword keyword. Once the encryption password has been set all tables opened through the connection will use the same password. Tables must be opened using the CommandType.TableDirect otherwise they will not be decrypted at the client.

Table encryption is handled automatically for tables in a data dictionary. Access to the tables is controlled through user permissions.


With the release of Advantage 8.1 clients have the option of communicating with the server via TCP/IP packets instead of the default UDP over TCP/IP. This can be configured in the ADS.INI file or in the connection string. You can get more information about configuring the client to use TCP/IP in this tech-tip or in the help file.

However, just because you can do something doesn’t mean that you should. In most cases the default protocol (UDP) is the best protocol to use. UDP is less chatty than TCP since it does not use acknowledgements for every packet sent. This makes it more efficient since there is no overhead in checking to see if the packets arrived.

If you are running on an unreliable network, such as a WAN, or a network with high latency TCP/IP can be a better choice. Since TCP provides a reliable and ordered stream of data between clients. TCP may also be useful when using firewalls. Network administrators are sometimes reluctant to allow UDP traffic through their firewall.

Windows 2008 Server Support

With the latest service release of Advantage 8.1 ( Windows 2008 Server is officially supported. It has been supported in version 9.x for several months. The Advantage supported platforms page is available here.

Windows 2008 Server Small Business Edition was officially released in November. We have not had the chance to fully test this version, however, we have some customers using this version of Windows.

Advantage “Hangs” on Shutdown

When trying to shut Advantage down from the Services Applet the service does not stop and may return a message “Could not stop the Advantage Database Server service on Local Computer. Error 1053: The service did not respond to the start or control request in a timely fashion”. The most likely cause of this error is that users are still connected to Advantage. By default the server will display a dialog warning the user that there are active connections on Advantage.

If you are connected to the server running the Advantage Service remotely (i.e. Remote Desktop) you may not see this message. The best option is to ensure that all users are disconnected from Advantage prior to shutting down the service. If you cannot get the users to disconnect normally you can use the advantage Kill User API to disconnect users. This option is also available in the connected users tab of ARC.

This may not be possible in all situations, such as an automated restart, so there is another option. You can set the suppress message boxes flag to prevent Advantage from alerting the user when shutting down. This option must be used when Advantage is installed in a cluster environment. You can change this setting using the Advantage Configuration Utility on the Misc. Settings tab.

Suppress Message Boxes

Friday, November 28, 2008

Tip #20 – Getting Management Information with SQL

Advantage has several system procedures dedicated to retrieving management information. This information ranges from installation and configuration information to information about users.

One of the most useful is sp_mgGetUsageInfo which returns the current, max used, configured and rejected counts like you see in the Advantage Configuration utility. The system procedure does not take any arguments so running EXECUTE PROCEDURE sp_mgGetUsageInfo() will return the following table.

Advantage Usage Info

Other server information can be obtained using sp_mgGetActivityInfo which returns the number of operations, logged errors and up time. For information on communication statistics you can use sp_mgGetCommStats which provides information on communication to the server. To see how much memory your configuration options are using run the sp_mgGetConfigMemory system procedure.

The sp_mgGetInstallInfo system procedure provides information about the installation options. These include the registered owner, serial number, user option, version, install date, character sets (ANSI and OEM) and whether or not replication is enabled. This information can be very valuable if your application relies on a particular server version or if you need replication prior to adding publications and subscriptions to a data dictionary as part of an upgrade.

Several system procedures provide information about current users. You can get a list of connected users with the sp_mgGetConnectedUsers procedure. To get a list of users who have  a specified table or index opened use sp_mgGetTableUsers or sp_GetIndexUsers. You can also get lock information with sp_mgGetLockOwner and sp_mgGetUserLocks. You can also disconnect users with the sp_mgKillUser.

Thursday, November 27, 2008

Tip #19 – Setting Table Properties via SQL

The standard SQL syntax includes a CREATE TABLE command for creating a new table. This statement can be used on free table and dictionary connections. When connected to a dictionary the table will be created in the dictionary if the user has the proper rights. If you want to create a free table on a dictionary connection use the AS FREE TABLE command at the end of your CREATE TABLE statement. You can also specify IN DATABASE to ensure that the table is created in the data dictionary.

The CREATE TABLE syntax includes the ability to add constraints for the new fields. However, it does not include syntax for creating other field and table properties available to dictionary bound tables. These include; validation expressions, validation messages, default indexes, auto create and encryption. To set these properties you must use system procedures.

sp_ModifyFieldProperty modifies a property for a specific field within the table. This system procedure can set any of the field constraints; minimum and maximum values, default value and null valid which can also be set with a CREATE TABLE statement. This system procedure also allows you to add/modify a field comment and validation message. These properties are not part of the CREATE TABLE syntax.

The following example sets the comment/description for the Address1 field in the Address table. In this case we do not need to specify a way to handle validation since this does not affect the table data. If a validation rule such as APPEND_FAIL is specified you must also define a Fail Table so records not meeting the new criteria can be preserved.

EXECUTE PROCEDURE sp_ModifyFieldProperty('Address', 'Address1', 'COMMENT', 'Street Address', 'NO_VALIDATE', NULL)

sp_ModifyTableProperty modifies a property for the specified table. These properties include; comment, validation expression, validation message, primary key, encryption, default index, permission level, auto create and memo block size. The syntax is very similar to sp_ModifyFieldProperty with the same mechanism for handling records which do not meet the new constraints.

The following example script encrypts all the tables in the dictionary. It first gets a list of tables using the system.tables view and then sets the TABLE_ENCRYPTION property. This is similar to setting the Auto Create Property for all tables which is outlined in a knowledge base article.

-- Script to Encrypt all Tables
DECLARE @cTables CURSOR AS SELECT * FROM system.tables;
OPEN @cTables;
    EXECUTE PROCEDURE sp_ModifyTableProperty(@cTables.Name,
    -- If table is already encrypted then continue to the next table
    IF __errcode = 5098 THEN
   END IF;
CLOSE @cTables;

Wednesday, November 26, 2008

Tip #18 – Getting Table Information with SQL

As mentioned in tip #16 there are many system tables which provide schema information. For example the system.tables view returns information about the tables. It is important to note that this view only returns information about data dictionary bound (database) tables.

The system.tables view returns one row for each table in the data dictionary. It contains a lot of useful information about each table such as; the primary key, default index, encryption, validation expression and comments. The following example returns the table name and comment for all the tables in the data dictionary.

System.Tables Example

The table type is stored as an integer which correspond to to the table type constants defined in the ACE Header files. These table type constants are listed below:

  • ADS_NTX = 1  DBF with single tag index files
  • ADS_CDX = 2  DBF with compound indexes
  • ADS_ADT = 3  Advantage proprietary file format
  • ADS_VFP = 4  Visual FoxPro 9 DBF files
You can use a CASE statement to return a more meaningful description of the table type as shown below:

Show Table Type

Tuesday, November 25, 2008

Tip #17 – Set Database Properties via SQL

Data dictionary properties can be set in a variety of ways. Advantage Data Architect provides a graphical interface for changing these properties, however, sometimes they need to be changed programmatically. This can be done using an API, AdsDDSetDatabaseProperty or using a system procedure.

The sp_ModifyDatabase system procedure sets one database property at a time. To use the system procedure you specify the property using its keyword (i.e. VERSION_MAJOR) and then specify the value (i.e. 2). The example script below changes the database version and adds the date of the update to the comments field.

-- Script to UPDATE the database properties
-- Get the current comment
@sComment = (SELECT [Comment] FROM system.dictionary);
-- Add the upgrade info to the comment
@sComment = @sComment + '-- Upgraded to version 2.0 ';
-- Add the current timestamp to the comment
@sComment = @sComment + CONVERT(NOW(), SQL_CHAR);
-- Update the dictionary properties
EXECUTE PROCEDURE sp_ModifyDatabase('COMMENT', @sComment);

Monday, November 24, 2008

Tip #16 – Get Database Information via SQL

Advantage has twenty-two system views which can be used to display information about a database. These views allow you to obtain metadata for all of the objects in the dictionary. Each of these view names is preceded with “system.” and are shown below:

columns dictionary effective permissions fts functions
indexes index files links objects packages
permissions publication articles publications relations stored procedures
subscriptions tables triggers usergroup members usergroups
users views      

For this tip I will focus on system.dictionary, since it is rarely used but contains a lot of useful information. This system view contains all the properties of the dictionary itself. This includes the major and minor versions, the table encryption password (only available to adssys), whether logins are required or disabled and many other properties.

System.Dictionary Table

The system views are available to all data dictionary users, although some fields are only available to administrative users. These tables can be very useful for displaying information in your application and for administrative scripts.

Friday, November 21, 2008

Tip #15 – Creating Code Templates

Code templates, AKA code snippets, insert a code block into the SQL editor. You can define these code templates in the Editor Options which you get to by choosing SQL->Options from the menu.

ARC SQL Properties

Once these code templates have been defined you can insert them into the SQL Editor by pressing [CTRL+J]. This will bring up a menu which displays a list of the code templates you have defined. Selecting one from the list inserts the defined code at the current cursor location.

ARC SQL Code Template

Thursday, November 20, 2008

Tip #14 – Using Auto Correct

Auto correct is another great feature which is included in the ARC SQL editor. By default there are not auto correct rules defined for the editor. However you can add your own rules on the Auto correct tab on the Editor Options window.

ARC SQL Properties

I like to have my SQL keywords in all caps for readability. Therefore I added the keywords that I use most to the Auto correct options. This will automatically replace a lower case “select” with “SELECT” automatically. Remember that it will only replace when an exact match is defined. Therefore “Select” will not be replaced with “SELECT” in my case.

One additional benefit of this feature is you can define shortcuts which will be replaced with a useful function. I like to define “s*” to be replaced with “SELECT * FROM”. This gives me a quick way to select all the contents of a table.

Wednesday, November 19, 2008

Tip #13 – SQL Editor Key Assignments

There are many key assignments which provide quick access to various functions within the SQL Editor. Several keys provide navigation such as the left and right keys as well as document end [CTRL+END] and document start [CTRL+HOME]. You can also navigate through your SQL statements using bookmarks. You can assign up to 10 bookmarks (0 – 9) for each SQL file.

Beginning with ARC 9.0 several key assignments were added for debugging. These include a debug run [CTRL+F5] which runs the SQL statement in debug mode, as opposed to running [F5] the SQL statement. You can also step in [F11], step out [SHIFT+F11] and step over [F10]. You can toggle breakpoints using the F9 key.

ARC SQL Properties

All of these key assignments can be modified in the editor as well. Simply select the option you wish to change and click on the edit button. You can also add an additional key shortcut to the selected option.For example you cna change the Set Bookmark {#} options to more closely match the Goto Bookmark {#} shortcuts. Since Goto Bookmark is set to [CTRL+K] + [#] by default I changed Set Bookmark to [CTRL+SHIFT+K] + [#].

ARC SQL Key Assignment

This shortcut is a two key sequence so you need to assign two keys for the Set Bookmark {#} actions. So after choosing the first key in the sequence click Next > to assign the second key of the sequence.

Tuesday, November 18, 2008

Tip #12 –SQL Syntax Highlighting Options

The  SQL Utility automatically highlights your SQL syntax just like many other code editors. You can customize the various highlighting options to suit your own preferences. You reach the SQL Utility options by choosing SQL->Options… (the SQL menu is visible when the SQL Utility window is open).

ARC SQL Properties

In this screenshot I have changed the highlighting option for SQL Functions so they will be displayed in orange, the default is black. This makes it easier to identify the built-in SQL functions when they are used in the statement, notice the CONVERT function below.

ARC SQL Highlighting

There are highlighting options for many categories including; Reserved Words, SQL Functions, Exceptions, Comments, Strings and Datatypes. You can also change the color of the left margin and gutter background. If you change the gutter background and don’t like the new color, like I did, the default values are R: 236, G: 233 and B:216.

Monday, November 17, 2008

Tip #11 – Displaying Line Numbers in SQL Utility

There are many options for customizing the SQL Utility in ARC which can be viewed by selecting SQL->Options (the SQL menu is visible when the SQL Utility is open). In order to display line numbers you must make two changes. First select the Line numbers in gutter option under General Options. Then select Visible gutter to display the gutter in the text editor area.

ARC SQL Properties

By setting this option the line numbers will be displayed in the gutter. You may need to increase the gutter width to properly display the line numbers.

SQL Utility Line Numbers

Friday, November 14, 2008

Tip #10 – Printing Table Schema

ARC Print Menu ARC 8.0 introduced two new table schema printing templates. These templates print either a simple or detailed view of the table schema. Table schema printing options are found on the Schema menu shown on the left.

The default is to use the short report which lists all the fields in a grid format along with all the indexes. The detailed report includes the table properties, field properties and index properties for the table. My example table, which contains 10 fields and 6 indexes, generated a five page detailed report. The last used template is also displayed as a menu option as pictured.

The Schema menu appears on the Table Designer in ARC 8.x and on the main menu bar in ARC 9.x. Yesterday’s tip contains more information about the schema menu.

Simple Report FormatSimple Report Template Example

Detail Report TemplateDetailed Report Template Example

Thursday, November 13, 2008

Tip #9 – Saving Table Schema Information

Back in 2007 I wrote a tech-tip about Schema Templates, which was a new feature added to ARC 8. This allows developers to save the schema of a table into a file which can be loaded into the table designer when creating a new table. The schema template contains all of the information about the table including the field descriptions, constraints, default values, validation expressions and indexes. This can be very useful if you are creating several tables with similar schema.

Schema Menu The Schema menu is located on the Table Designer window in ARC 8.x. It has been moved to the ARC menu in version 9.0. Schema files have a default extension of arcschema (i.e. customer.arcschema). Choosing the Load… menu item allows you to choose a schema to be loaded into the Table Designer. You can save the current table schema by choosing the Save As… item. The Delete… command removes the selected schema.

You can work with table schemas in both ARC 8.x and ARC 9.x the table properties windows are shown below.

ARC 8 Table Designer Version 8 Table Designer

ARC 9 Table Designer Version 9 Table Designer

Wednesday, November 12, 2008

Tip #8 – ARC User Interface Options

Both ARC version 8 and 9 have several options for modifying the user interface. You can change some of the elements of the user interface by selecting Tools->ARC Settings, then click on the User Interface tab.


You can select from several menu bar options; Flat, Enhanced, Standard, Office 11 and XP. You can also autosize the Table Browse window. I like using alternating colors on grid rows, this is the default behavior but you can turn this off or change the color.

Limiting the amount of data displayed in grid cells essentially this constrains the size of the cells in the grid. Finally you can specify if a new instance of ARC opens when you double-click a file associated with ARC. The default is to open these files (i.e. ADD, ADT, DBF) in the current instance of ARC. However, if you want each file opened in its own ARC instance select the “Always Open Shell Files in New ARC Instance”