Views from an Advantage Evangelist

Who Does the Testing

2010-02-05T07:00:00.000-07:00

As I discussed in my last post there are many types of testing, but who should be doing the testing? This depends on what type of test is being conducted. Unit tests, regression tests and integration tests can be automated in many cases. They were probably written by the developers during development or by the QA department.

Automated tests are extremely good at demonstrating that the features and functionality work correctly. They can also be used to measure performance. By always running the same tests regressions can be identified quickly and we can be assured that existing features continue to work as new features are added.

However, there are many tests that simply cannot be automated, especially if your application contains a lot of UI elements. Sure there are programs that click buttons in a specific sequence and can "observe" the results. But, this isn't the same as a person interacting with the product. When a real person is clicking the buttons they an also evaluate how good the workflow is, how easy it is to move through the steps of the task and most importantly provide qualitative feedback.

These types of testers are a rare find. A good QA person needs to be someone who enjoys working on repetitive tasks or at the very least configuring several complex environments. The tester needs to be able to evaluate not only that the software is performing correctly but that it is doing the job in a usable way. Joel Spolsky has some advice on choosing testers.

If your program has a significant user interface you should include some Usability Testing into your schedule. These tests should be conducted in a semi-controlled environment with little direction. A user should be given a task to complete with the software and the session should be observed and/or recorded. This allows the developer(s) to see how easy their interface is to use. It can also identify places where additional error handling or explanation is necessary.

These kinds of tests should not be conducted by the engineer who built the interface. Let's face it, the interface that I build may make perfect sense to me because I know what it is supposed to do and how it does it. Using the form may not be as obvious to someone else working with the product. A sequence of actions to perform a task that is quite natural to me may be frustrating for many users. Jeff Atwood made a similar point in his article "Open Source Software, Self Service Software" when discussing self-service checkout.

There are certain rituals to using the self-service checkout machines. And we know that. We programmers fundamentally grok the hoops that the self-service checkout machines make customers jump through. They are, after all, devices designed by our fellow programmers. Every item has to be scanned, then carefully and individually placed in the bagging area which doubles as a scale to verify the item was moved there. One at time. In strict sequence. Repeated exactly the same every time. We live this system every day; it's completely natural for a programmer. But it isn't natural for average people. I've seen plenty of customers in front of me struggle with self-service checkout machines, puzzled by the workings of this mysterious device that seems so painfully obvious to a programmer. I get frustrated to the point that I almost want to rush over and help them myself. Which would defeat the purpose of a.. self-service device.

I related to this example right away. It seems very natural to me to do things step by step since that is the way we have to write programs. Most people don't break tasks down into these small chunks. A good tester can identify where these steps seem onerous which allows the developers to streamline the task.

Finally if you need some convincing on hiring testers take a look at Top Five (Wrong) Reasons You Don't Have Testers.

The Power of Testing

2010-02-03T07:00:00.000-07:00

Testing is an often dreaded but very necessary part of the development process. It can be a tedious task but it can really pay off in the long run. I think this (de)Motivational poster sums it up nicely.

I realize that there isn't time to test everything. Like all programming tasks they need to be added into the schedule. Therefore, you need to choose the tests that best suit your situation. Here are a few examples.

Unit Testing
Regression Testing
Integration Testing
Usability Testing

Unit testing was brought to the forefront with the concept of Test-driven Development (TTD). TTD encourages developers to write a unit test first then create the method. This ensures that every method has a test written for it. If you are trying to apply this to an existing project it can be a very large task. For new projects there are many tools available which which help automate the process.

Creating unit tests for every function can be a time consuming process and needs to be balanced with generating the code that actually does the work. This can be further complicated since you need a positive and negative test in many cases. You need to ensure your function returns the correct results for valid input as well as proper handling of invalid input. Achieving the right amount of unit tests can be a somewhat daunting task.

Two types of tests which are almost always used are regression and integration tests. Regression testing ensures that changes to the code don't break any existing functionality. This is a step up from unit level tests in that it demonstrates that all of the units work together properly. Testing of new features is generally referred to as Integration Testing since it tests the addition of new features with the old.

When you are developing a program that will be used by people frequently Usability Testing is a valuable tool. Many people think that this can be a very time consuming and expensive type of testing to do. However, it doesn't have to be. You can conduct informal usability tests by simply grabbing someone in the hall and asking them to look at a form or web page that you just completed. If they can use the form then your on the right track.

Other simple usability tests can be conducted in a few hours using screen sharing software and a little help from your customers. This allows your developers to watch a user work with the program and identify good and bad aspects of the interface. I believe this kind of feedback is invaluable, it will lead to improvements and is well worth the time investment.

There are many other categories of tests that can be run and ultimately the level of tests you perform is going to depend on many factors. Although testing is very important delivering a product to market is just as important. Do enough testing to ensure that your product will work correctly and effectively then get it out to your best testers; your customers.

FAQs – January 2010

2010-02-01T07:00:00.000-07:00

Client Communication Errors

Clients can get disconnected from Advantage in many ways. The network connection could fail, the client could loose power or the user could simply close the program abnormally. If the server detects that the client has been disconnected it will log a 7020 error.

However, there are some communication errors that can generate an error on the server. In rare cases a 9094 error, which will also generate a crash dump file, can be caused by a client communication failure. This should be resolved in version 9.10.0.20 which added a new error code 7213 which does not generate a crash dump file. This error is logged when the client gets a temporary or non-fatal communication error when sending a request to the server.

Two knowledge base articles that address this issue have been posted: 7213 Error Logged in the Advantage Error Log and Error 9094 Logged and Dump File Generated

"Empty" Date Fields in VFP Tables

When a date field is empty in a Visual FoxPro (VFP) table the Advantage ODBC Driver will return 12/30/1899 as the date. This is the default return value when an Xbase field contains an empty value.

You can avoid this situation in one of two ways. VFP allows fields to be created to support NULLs. When the field is created this way Advantage will return NULL if a value has not been defined.

Alternately you can check the field for an empty value by using the EMPTY() function. This is available as both an SQL scalar function as well as a filter expression. The EMPTY() function will return true if the field contains the Xbase empty value for the given field type.

SELECT * FROM MyTable WHERE EMPTY(MyDate) = TRUE

Can a Server Replicate to Itself?

Yes, an Advantage server with replication enabled can replicate to itself. You simply have to create another database on the server and configure it as the subscriber. This could be very useful in keeping an up to the minute backup of your data. Create the second database on another drive within the server so if the primary drive failed you would have a current backup immediately available.

You could also replicate to an external drive or to a Network Attached Storage (NAS) device for an additional measure of security.

Creating a Mini-Dump for a Specific Error Code

Advantage automatically creates a crash dump file whenever a 9000 class error occurs. These files assist the R&D team in determining what is going on with the server when the error occurred. Generally this allows us to quickly determine the problem and recommend solutions.

You can also create a mini-dump when you want more information about why a specific error code is being generated. This is done by adding some additional keys to the registry. Create registry entries named MINIDUMPERROR, MINIDUMPFILE, and MINIDUMPLINE in the ADS registry location of HKLM\SYSTEM\CurrentControlSet\Services\Advantage\Configuration.

To generate the log file just find the Error Number, File Name and Error Line you wish to create the dump for in the Advantage Error Log (ADS_ERR.adt). Then do whatever operation that caused the error again. Advantage should generate a file named adsdump*.gz on the root of the C drive, or the location configured in the error log path.

For more information about this process see this knowledge base article.

The Apple iPad

2010-01-29T07:00:00.000-07:00

I am always amazed at how good a show Apple puts on. They do a spectacular job at releasing just enough information and rumor to whip everyone into a frenzy. Take the iPad announcement this week it has generated a avalanche of blog posts and tweets (twitters?) over the past two days. It ranked second on Twitter to Haiti on the 27th when it was announced. What would it be like to generate that kind of interest in your product?

So, I'll admit it I am interested in this new device. I have really enjoyed using my iPhone and I must admit the iPad looks like a really cool and fun device. Apple's multi-touch interface is the most intuitive interface that I have ever used. It is very easy to learn and it makes the device easy to use for just about everyone. It is so quick to learn how to navigate around and use the applications. I believe that this device will be very well received and Apple will sell millions of them.

The iPad is essentially a giant iPod Touch, which will be great for surfing the Web and watching movies but not so good at fitting in your pocket. The standard applications like calendar, contacts and photos have all been enhanced to make use of the larger screen size. It also adds the capability to read books. I think this is a great selling feature and it will appeal to a wide audience.

Since I already have an iPhone and a Kindle I don't see this as a necessary device for me. The iPhone is more portable, provides Internet access virtually everywhere and is my phone. I also think reading on an electronic ink screen instead of an LCD is much easier on the eyes. I really enjoy reading on my Kindle and I find it almost exactly the same as reading ink on paper.

If I didn't already have the other devices I would be much more interested in purchasing an iPad. I really like the instant on capability and it is large enough to comfortably surf the web. Books are also a great feature and I must admit the product video made the photo browser look really fun.

For all of the good/great things about the iPad I do see some drawbacks. It runs the iPhone OS which means no multi-tasking. On a device this big I would want to be able to have a few apps open at the same time. There are no cameras on the iPad, which I found surprising since this would be a perfect video chat device. It may be a bit awkward as a camera or camcorder though.

I found a couple of great articles on Gizmodo discussing the iPad. The best one "The iPad is the Gadget We Never Knew We Needed" has some great points and is well worth the read. For the downside to the iPad take a look at "8 Things That Suck About the iPad". Stephen Fry also had some great points in his article. I thought his comment to naysayers was quite good:

There are many issues you could have with the iPad. No multitasking, still no Flash. No camera, no GPS. They all fall away the minute you use it. I cannot emphasize enough this point: “Hold your judgment until you’ve spent five minutes with it”. No YouTube film, no promotional video, no keynote address, no list of features can even hint at the extraordinary feeling you get from actually using and interacting with one of these magical objects.

I would love to have a chance to use the iPad but I am sure it would make me really want to have one. I think this first generation will be good but will have its share of issues. However, I wouldn't turn one down if it was offered to me. I'm just not sure I'm ready to spend $500 on a device that has some glaring limitations. For now I would rather surf the web with my laptop at home or on my iPhone when I am mobile.

All that said I am sure Apple will sell millions of these and they are always very good at responding to their critics. I bet the next generation iPad will be a must have.

January Advantage News

2010-01-27T17:24:00.001-07:00

The January edition of the Advantage Newsletter was sent out today. If you didn't get one you can view it here or you can sign-up and get it right when it is published. You can see a complete archive of the newsletter here.

Some of the exciting things announced in the newsletter include

Advantage Database Server 10 | Pure & Simple webcast series – this three part series will introduce you to all the new features and improvements for the latest version of Advantage. This series begins in February and you can sign up here.
The next Advantage Technical Summit will be held in Boise, Idaho on April 28th and 29th. This is a great opportunity to learn more about Advantage and network with other Advantage users. More information about this event will be sent out soon.

Keep watching for more event announcements.

My 2010 Reading List

2010-01-13T07:00:00.000-07:00

As I mentioned in my first book review of the year I am going back to basics. I thought it was time to review some of the basic database theory along with solid design principals. With that in mind here are the titles that I have identified so far.

Database Stuff

User interface design and usability

Miscellaneous

A fairly ambitious list but I hope to get through them all this year. Stay tuned for book reviews as I get them read.

Book Review – Beginning Database Design

2010-01-11T07:00:00.000-07:00

I wanted to take some time and go back to some database basics so I looked for some introductory books on database design. I decided on Beginning Database Design From Novice to Professional by Clare Churcher. This is a well written book with decent examples but a bit too introductory for a database programmer. I think it would be a very good book for someone who is looking to move from spreadsheets into databases though.

The book is relatively short at 240 pages. It contains twelve chapters along with a conclusion and index. It is truly aimed at the casual computer user who has a need to track data. Most of the examples pertain to education and research. Each of the examples are well thought out and clearly demonstrate the concepts she is discussing.

I thought her approach to introducing the material was well organized. She made some excellent points in her first chapter "What can go wrong", it demonstrated the importance of good design quickly. Chapter 2 is a brief introduction into the development process. I was happy to see some discussion of use cases in this chapter. I think this is a very important step in the design and development process.

Chapter 3 examines use cases more thoroughly showing how the process of working with the users and identifying how they will use the data is a fundamental requirement for designing a good database. Chapters 4 and 5 dive into creating a data model. This includes considerations of data types and logical grouping of data. These chapters also spend a lot of time discussing data relationships.

Chapters 6 discusses looking at data as classes. This provides a good way to demonstrate when it is appropriate to create subclasses that have some common data but also include specific information of their own. Chapters 7 through 9 discuss the relational database model. This is where all of the concepts introduced in the first half of the book come together. There is a lot of discussion about primary keys, normalization and constraints. Each of these concepts include simple and effective examples.

Chapter 10 is a very quick overview of SQL queries. It demonstrates how to create simple queries to get the data out of the database. If you have worked with SQL before you won't see anything new here. Chapter 11 discusses some user interface issues. I liked how she brought the UI design back to the use cases she discussed in the beginning of the book. All of the UI examples were done in MS Access and are very simple.

The book concludes with some discussion of object-oriented databases and using data within a spreadsheet. Probably very useful for a casual computer user but not something a programmer really needs. The conclusion does a good job of summarizing the material in the book as well as offering recommendations for more information.

The bottom line: this book is well written for its intended audience casual users to possibly IT professionals. As a database programmer you will want to invest in something more focused on a programmer's prospective.

Amazon Kindle

2010-01-08T07:00:00.000-07:00

I was fortunate enough to get a Kindle 2 for Christmas this year. I guess my wife was listening when I kept talking about how cool and useful I thought the device would be. So far I haven't been disappointed it is everything I expected and I am really enjoying the device.

The best feature is how easy it is to read on the device. The electronic ink screen is almost like reading from ink and paper it doesn't strain your eyes at all. I also love the wireless delivery of books to the device. I have been using the Kindle application for my iPhone for a few months and all the books I had downloaded to my iPhone I had instant access to them on my new Kindle. It even keeps track of where I am at in books between the two devices.

Another great feature is the ability to download previews of books. This is like browsing a book in the bookstore, you usually get a couple of chapters. If you like the book there is a link to get the rest of the book downloaded directly to the device. There are also lots of free books available from Amazon. Many of them are classics but if you check you can usually find some good bargains. I was able to get Magic Kingdom for Sale, Sold! for free.

There are a few drawbacks to the device though. The form factor is nice but I think it is a bit too thin and perhaps a bit slick on the back. I felt like I might drop it the first few times I was reading. Although I got used to it I still bought a protective case which adds a bit more bulk making it easier for me to grip it.

I went with the latitude jacket from M Edge. It zips up securing the Kindle and provides some protection when traveling. It also folds completely back making it easy to hang onto the Kindle. It also has a pouch in the front that is big enough to hold the charging cable which keeps everything together.

Although the screen is easy to read you do need a good light source. It would be nice if the Kindle included some lights around the screen making it easier to read in low light. However, this would probably significantly reduce the battery life and there are many lights available.

Another slight downside is the screen refresh. Once the screen is rendered it displays in crisp clear grayscale. Some of the screen saver images are really detailed. However, there is a very noticeable flicker when turning pages and sometimes when navigating through menus.

Overall I love the device. It will be nice to be able to take several books along with me and have access to others just about everywhere. Amazon is also coming out with an SDK for the Kindle. It will be very interesting to see what kinds of applications become available.

An Exciting New Year

2010-01-04T07:00:00.000-07:00

2010 will be an exciting year for the Advantage team. We will be releasing version 10 of Advantage in the first half of the year. As you might expect we will be providing many opportunities for you to see what will be in the latest version of Advantage in the coming months.

Here is just a quick peek at what will be available

Version 10 webcast series – begins in February
Version 10 launch events – Stay tuned for more information
Version 10 Beta – First half of 2010

Along with these major events I will be doing several blog posts and tech tips when the beta is released. You can view the official list of version 10 features on the Advantage Developer Zone. You can also provide feedback and see which features the R&D team is currently working on by visiting our feedback site.

Also stay tuned for the schedule for the next Advantage Technical Summit. This is a great opportunity for you to learn more about Advantage, meet the team and network with other Advantage users.

I'm looking forward to seeing a lot of Advantage users this year.

FAQs – December 2009

2010-01-01T07:00:00.000-07:00

Advantage Data Architect Command Line

You can open a table or data dictionary using a command line argument for Advantage Data Architect. ARC will open and use the default connection settings to connect to Advantage and open the specified object. You will be prompted for your credentials if they are required when a data dictionary is specified.

Clipper with Windows 7

Although Windows 7 is not yet officially supported by Advantage, you may need to run an existing Clipper based application on a Windows 7 client. This can be done using Windows 7 XP Mode, which is a virtual Windows XP environment.

You can get detailed instructions from this knowledgebase article.

Setting NOT NULL Constraints With a CREATE TABLE Statement

You can specify constraints on data dictionary bound tables with a CREATE TABLE statement. To do this you must use the CONSTRAINT keyword when specifying the constraint. Some servers support defining constraints without using the CONSTRAINT keyword. In the case of specifying whether or not a field can be NULL there are some slight differences.

Many data modeling tools generate DDL statements to create a database based on the model. These statements may look like the following.

CREATE TABLE MyTable ( PK Integer NOT NULL, Name Char(50) NULL, HireDate Date NULL, Salary Money NULL )

This query would create a table with four fields and the PK field must contain a value. Some small modifications to the statement are required to work with Advantage. The default is to allow NULL for all fields in a table, therefore you do not need to specify the NULL. Additionally you must use the CONSTRAINT keyword when specifying the NOT NULL constraint. The following statement will create the same table in Advantage.

CREATE TABLE MyTable ( PK Integer CONSTRAINT NOT NULL, Name Char(50), HireDate Date, Salary Money )

Madison PC Users Group

2009-11-13T07:00:00.000-07:00

We visited the Madison PC Users Group meeting on Wednesday night. There was a good turnout at the meeting and we were able to discuss Advantage Database Server with them. I would like to thank Eric Selje of Salty Dog Solutions for getting us on the schedule. You can get his summary of the meeting here.

This meeting had a diverse mix of PC users and developers. We trimmed down our presentation to highlight the Advantage features and we did a lot more discussion of general database topics than usual. However, the group was very friendly and had many good questions during the presentation.

If your interested in the information that we presented to the Madison user group check out the Getting Started with Advantage screen cast we did earlier this month.

Chicago FoxPro User Group Meeting

2009-11-11T09:33:00.000-07:00

Last night we presented at the Chicago FoxPro Users and Developers Group. We got a great turnout and the meeting lasted nearly three hours. There were many questions about replication and encryption during the meeting. It was great to have such an interactive group to present to.

We did take a short break during the meeting to have some authentic Chicago style pizza. Which made for a great break during the meeting.

With everyone energized after the pizza we finished up the meeting by showing some demonstrations of our CloneDBC utility and using Advantage from FoxPro. Again we had great interaction and good questions during the demos and we were able to discuss several use cases and scenarios.

All of the attendees received a flash drive which included all of the screen casts we have produced for FoxPro developers. You can view these screencasts all together using this link. Many other screen casts about Advantage are available on the Devzone.

We will be in Madison, WI tonight for another user group meeting.

Chicago FoxPro User Group

2009-11-09T17:59:00.001-07:00

I will be presenting at the Chicago FoxPro Users and Developers Group on Tuesday night the 10th of November. We will be discussing using Advantage Database Server with FoxPro. If you are in the Chicago area feel free to drop in. THe meetings are held at Tech Nexus on the 15th floor of 200 S. Wacker Drive. The meeting runs from 5:30PM until 8:00PM.

View Larger Map

Here is the description from the Chicago FoxPro User Group Site

We have a couple of representatives of Sybase coming to Chicago to demonstrate their Fox-friendly data server -- Advantage; An alternative to pricey backends with an easy conversion route. It should be a good time -- another milestone in a long history of tech twists and turns. From their promotional material at http://www.sybase.com/products/databasemanagement/advantagedatabaseserver: * Flexible data access via either native relational SQL or direct navigational database commands. * Optimized data access for all development environments, including Delphi, Visual Studio, Visual Objects, Visual Basic and more. * Zero administration, easy to install and manage — does not require a database administrator, eliminating high administration costs. * Complete referential integrity support including primary/foreign key definition and cascaded updates and deletes. * Complete server-based transaction processing eliminates database corruption, drastically reducing support costs. * Database security and encryption support. * Fully scalable from local to peer-to-peer to client/server environments — with one set of source code If you have been wondering about a Fox friendly data backend that breaks the 2 GB barrier, come check out the Sybase presentation of Advantage Database Server. First meeting is free. From then on, $10 per meeting or $75 per year. The usual time and place: Tech Nexus 200 S. Wacker Drive, Suite 1500. Tuesday Nov. 10th at 5:30pm (chip in for Pizza and brown bottle beverage.) Here’s the RSVP link: http://bit.ly/3yOzIG As we have worked to keep the FoxPro Users and Developers group a vibrant and interesting colloqium over the years, we notice that new blood comes into the group based on one meeting or subject. I remember making contact with a future employer because he came to see what was up with Crystal Reports in 2002. See you there?

FAQs October 2009

2009-11-02T07:00:00.000-07:00

DSN less connection

There are two ways to connect to an ODBC driver, using a Data Source Name (DSN) or by providing a connection string. The latter is generally referred to as a DSN Less connection. A DSN Less connection can be useful because there is no need to create a DSN on every system your application is running on.

A DSN connection string needs at least a Driver and a DataDirectory. When connecting to a data dictionary you will also have to specify a UID (Username) and PWD (Password). When working with free tables we recommend specifying the table type using the DefaultType keyword. See the example DSN connection strings below.

// Connection to an Advantage Data Dictionary
Driver=Advantage StreamlineSQL ODBC; DataDirectory=C:\Data\Demo\DemoDictionary.add; Uid=adsuser;Pwd=password;

// Connecting to a directory of Visual FoxPro tables
Driver=Advantage StreamlineSQL ODBC; DataDirectory=C:\Data\VFP ; DefaultType=FoxPro;

All of the properties for an ODBC connection are listed in the help file

Permissions Required for Modifying RI Rules

You cannot assign rights to Referential Integrity objects within the database. RI rules can only be created in the database by an administrator. Users with ALTER permissions on the tables involved in the RI rule can modify the rule. RI Rules are not visible in the dictionary without the proper permissions. RI rules are enforced for every database user there is no way to exclude users from an RI rule.

Using the AdsCommandBuilder

The Advantage .NET Data Provider includes a AdsCommandBuilder class which is responsible for creating SQL commands to update, insert and delete data for an AdsDataAdapter. Any statements that are not set directly on the AdsDataAdapter will be generated by the AdsCommandBuilder.

By default the command builder requires the table to have a primary key in order to generate the additional commands. Ideally you will have primary keys on all of the tables that you use within your application. In practice this is not always the case. If the table does not have a primary key assigned you must set the RequirePrimaryKey property to false.

The following code demonstrates using the command builder to generate INSERT, UPDATE and DELETE commands for a given AdsDataAdapter.

     oCommand.CommandText = "SELECT * FROM Customer"

    daTemp = New AdsDataAdapter(oCommand)

    dtTemp = New DataTable()

    daTemp.Fill(dtTemp)

    ' build the INSERT, UPDATE and DELETE commands

    cbAds = New AdsCommandBuilder(daTemp)

    cbADS.RequirePrimaryKey = False    ' Primary key not available on the table

    daTemp.InsertCommand = cbAds.GetInsertCommand()

    daTemp.UpdateCommand = cbAds.GetUpdateCommand()

    daTemp.DeleteCommand = cbAds.GetDeleteCommand()

Error 7156

The 7156 "Auto Index Rebuild Active" error appears in the error log it most likely occurred because the server was stopped abnormally. For performance reasons indexes are stored in cache and written to disk as necessary. If the Advantage service is stopped abnormally it is possible that all of the index updates have not been written to the index file.

To correct this situation the Advantage server will automatically rebuild any indexes that it had in cache prior to the cache. This will ensure that the indexes are proper and up to date. It will lock the tables while the index is being rebuilt so you may need to wait a few minutes before connecting clients after restarting a server that has crashed.

SWFox Final Day

2009-10-18T18:19:00.001-06:00

The final day of Southwest Fox began at included repeats of sessions from the previous days. The conference schedules each session twice to give all the attendees plenty of opportunity to get to the sessions they want to hear. I really like this strategy, I have been to some conferences where there are two sessions that I want to see scheduled at the same time. Just another one of those nice touches that make this a really fun conference to attend.

I haven't mentioned our giveaways for this year's conference yet. This year our excellent marketing department was able to get us several items to distribute from our booth. We had some great mini cars that include a spring so you can pull them back and have them zoom over any flat surface. We also had a bunch of nerf-like missiles/grenades which came in very handy for the ambush of Doug Hennig on Saturday. Finally we gave away 1GB flash drives which included a bunch of information about Advantage for FoxPro developers.

In addition to our free giveaways we also had an Amazon Kindle 2 to give away. We collected names during the conference and had Rick Schummer draw for the Kindle during the closing session. The lucky winner , was excited to take away his bran new Kindle 2 from the conference. Note that Advantage Database Server a Developer's guide is not available on the Kindle, however, it is included as a part of the 9.x help file.

I would like to thank the Southwest Fox staff for once again putting on an excellent conference. I always enjoy talking with people from the FoxPro community and this is the biggest FoxPro event in North America. I am already thinking about what we can do for next year's conference.

Southwest Fox Day Two

2009-10-17T22:01:00.000-06:00

Day two of Southwest Fox had a much lighter schedule than day one. There were only twenty nine sessions scheduled. As you can imagine attendance was down just a bit for the 8:30 am sessions but by 10 everyone was back into learning mode. The afternoon sessions were very well attended.

I presented a session on Advantage Database Server right before lunch. My presentation was similar to the presentations that I have been giving at various FoxPro user groups over the past year. My presentation highlights the capabilities of the server along with demonstrations of using CloneDBC and concurrent access to the data. There were many good questions during the session and I got a lot of positive feedback about the product.

One of the really fun parts of the conference is the family like atmosphere. On the final session of the day an attack was planned by Craig Boyd on Doug Hennig. All of the people attending Craig's session were issued Nerf guns and Sybase iAnywhere Nerf grenades. Craig then lead the charge into Doug's session. The fight was over quickly given that the only armed parties were from Craig's session. Although the scene was brutal, hundreds of Nerf darts were scattered around, order was quickly restored and the sessions continued. See the video here.

Although there is no dinner provided on the second night of the conference many of the attendees look forward to the annual trip to F1 Race Factory here in Phoenix. There is a very intense rivalry between many of the regular speakers at the conference. It is the source of a lot of trash talk on Twitter prior to the conference and many hallway challenges during the conference.

SWFox Day One

2009-10-16T22:29:00.000-06:00

Day one of Southwest Fox consisted of forty sessions that started at 8:30 am and ended at 9:30 pm. The organizers do an incredible job creating a schedule and providing lots of great content. Each session is presented twice so all the attendees have an opportunity to see all the sessions that they want to. I am impressed each year at how well everything works.

I spent most of my day talking to the attendees at our booth in the exhibit area. Since this is our third year I have been seeing a lot of familiar faces. The FoxPro community is a very tight knit group and many of the attendees have been coming to this conference for years. It has been great chatting with people that we have met at various FoxPro User Group Meetings over the past year.

I was able to break away from the booth to attend the session on Alaska Xbase++. The presentation was done by , the chief architect and president of the company. Xbase++ has big plans to support the FoxPro community by adding support for the VFP language to the Xbase++ compiler. This could become another way for FoxPro developers to work with Advantage since Alaska provides a native Advantage Data Driver.

Ken Levy presented a session on Advantage where he discussed the benefits of using Advantage with your FoxPro applications. Ken discussed the benefits of moving to client/server specifically using Advantage. He demonstrated the use of the bran new CloneDBC utility to create an Advantage data dictionary. He then showed accessing the data concurrently from a simple Visual FoxPro application and a Visual Studio application.

Ken also discussed some code samples which were produced by VFP Conversions. There are two samples one is a VFP9 application and the other is a C# application. The C# application uses the Advantage .NET Data provider to access the data concurrently with the native VFP application. You can download these samples using this link.

After the sessions we had a very nice dinner provided by the conference. During the dinner many giveaways were handed out to attendees. After dinner there was one last group of sessions for the hard core conference goers. Still one and a half days left in this year's conference.

SWFox Conference Begins

2009-10-15T22:15:00.001-06:00

The 2009 Southwest Fox conference began with a great keynote presentation. The conference is organized by Geek Events which is a group of three Visual FoxPro MVPs; Tamar Granor, Doug Hennig and Rick Schummer. Unlike most keynotes this one includes a bunch of personal touches. The conference statistics are discussed right up front. This year there are 88 attendees from 7 different countries, 19 speakers, 9 sponsors and only 2 additional staff people.

Several awards are also presented to the community during the introductions. First off the VFPX Outstanding Service award went to Cesar Chalom who contributes to the GDI Plus X and FoxCharts projects. Next up was the Ceil Silver Ambassador award which goes to an international member of the community and pays for them to come to the conference. This is the inaugural award and it went to Emerson Santon Reed from Brazil. Finally the award for most popular speaker for the 2008 conference, as determined by the attendee evaluations, was Cathy Pountney.

After all the conference business the keynote speaker Sara Ford from Microsoft was introduced. Sara is the project manager for CodePlex which is Microsoft's open source community. The title of the keynote "Towards a Stronger Open Source Ecosystem on Codeplex.com". Sara is the self-titled "Queen of Open Source at Microsoft" and she has made a lot of effort into understanding how the Open Source community works.

Sara is a high energy and dynamic speaker and she covered many topics using many stories. She discussed the process of bringing more open source awareness to Microsoft which she discussed in detail in this video. Overall I found her discussion of open source very interesting and it is great to see that Microsoft is supporting open source development. Just as interesting though was her discussion of how the CodePlex site is developed. They use agile development processes so a release is done every three weeks. This was a big change for her since she came from the Visual Studio team which has an 18 – 24 month release cycle. The entire keynote was broadcast live over UStream and you can view the recording here.

Sessions begin at 8:30 on Friday and I will be attending a few of the sessions.

Ready for SWFox

2009-10-14T07:00:00.000-06:00

Southwest Fox 2009 begins Thursday night, this is my third year at the conference. I am particularly excited to see the keynote by Sara Ford from Microsoft. I am sure she will put on a good show as well as provide some good information.

This year there will be two presentations on Advantage during the conference. Ken Levy will be doing a session on Friday at 3:30 PM and I will be doing a session at 11:30 on Saturday. The session descriptions are below.

Using Advantage Database Server with FoxPro – Ken Levy

This session is an overview with useful scenario samples of using VFP with Sybase's Advantage Database Server (Advantage), as well as the free Advantage Local Server, OLE DB Provider, ODBC Driver and .NET Data Provider. Advantage can be used with Visual FoxPro as a database system, but its unique functionality is its data dictionary wrapping and using existing DBF data. This allows .NET and other technologies to use DBF based data of VFP applications while not requiring VFP applications to be modified. Advantage will play a key role in modernizing VFP applications with DBF data into the next decade. Ken will present a series of useful scenarios of using VFP DBF data with .NET, Office, and other products and technologies by leveraging Advantage tools and products, many of which are free for both development and deployment.

Advantage Database Server Have We Met? – Chris Franz

As companies store more and more data the need to organize and retrieve this data becomes critical. Use of a client/server database system provides secure storage of large amounts of data while providing high performance. Advantage Database Server provides a unique client/server solution for Visual FoxPro development while making data conversion unnecessary.
This session will provide an overview of Advantage Database Server's key features and demonstrate those features that are unique to FoxPro. You will learn:

How to connect to Advantage from FoxPro using remote views, cursor adapters and SQL pass-through.

Concurrent access to data from a FoxPro application and an ASP.NET website

Data security through restricted access and encryption

Accessing very large DBF tables ( >2gb )

I'll be doing daily posts from the conference and I may even post to Twitter as well. The FoxPro community are very avid users of twitter so you can get a real great perspective on the conference. Just take a look at what they are saying.

What's being said about SWFox

Southwest Fox 2009

kevincully Later folks. Shutting down to head to #SWFox. 4 hours ago reply

mroof Heading to the airport... See ya'll at #swfox 3 hours ago reply

mikefeltman Uninstalling VFP from my notebook on the day I leave for #SWFOX - keep your fingers crossed for me! 3 hours ago reply

frontier2000 @MikeFeltman /uninstalling VFP/ Check your email! #SWFOX 3 hours ago reply

psherwood Trying to finish up several projects before I head out to #swfox this evening. 3 hours ago reply

visutrac #SWFox Happy Trails to all heading off to AZ for the conference ! Enjoy , it is going to be way nicer than the nearly winter weather here. 2 hours ago reply

dfapam Something must be wrong. All packed but the airport shuttle isn't due to pick me up for another hour. #SWFox 2 hours ago reply

Join the conversation

Book Review – A Guide to the SQL Standard

2009-10-12T08:00:00.000-06:00

This will be a different type of review than I normally do, because this book is more of a reference guide rather than a book you would read cover to cover. A Guide to the SQL Standard Fourth Edition by C.J. Date with Hugh Darwen is an in-depth examination of the SQL standard. It breaks down nearly every aspect of the standard with good examples.

I have used this book frequently as a reference or when I need more information about a particular aspect of SQL. The book is divided into five sections; Introduction, Some Preliminaries, Data Definition and Manipulation, Data Control and Advanced Topics.

The introduction is a very good summary of SQL and it covers many of the features of the SQL language. For anyone new to SQL chapter 2 is a must read, it is also a good review for any SQL veteran. This chapter paints the entire picture of the overall functionality of the SQL standard.

The preliminaries section is a pure reference section in my opinion. It provides lists of key words, operators and basic SQL concepts like schemas and transactions. This is a good section to refer to when you need to verify a keyword or look up a basic operator.

The data definition and manipulation section is the real heart of the book. This section contains seven chapters which cover many aspects of working with data using SQL statements. In particular chapter 10, Data Manipulation: Cursor Operations includes several examples on working with a set of data in an SQL script. Chapter 11, Table Expressions, provides an in depth discussion of table joins. This section is very useful in demonstrating techniques for retrieving data from a highly normalized relational database. Chapter 12, Conditional Expressions, provides a tremendous amount of useful information about filtering data.

The data control section provides discussions on data integrity and data security. The integrity section provides useful information about using constraints as well as referential integrity. The data security chapter covers information about the different types of operations which can be defined for database objects. This chapter includes information on the GRANT and REVOKE statements.

The advanced topics section contains detailed information about various topics. I found chapter 16, Missing Information and Nulls, to be very interesting. It is very valuable to understand what a NULL really means and the authors do a very good job demonstrating the impact of NULLs. Other chapters go more in depth about data types such as date and time values as well as character data.

The bottom line: this is a very good reference book. It provides a detailed look at the SQL standard which will give you a broader understanding of how SQL should be implemented. Keep in mind that not all database servers implement all aspects of the standard. If you are looking for a book that teaches you how to write SQL statements to retrieve your data there are better books. However, if you are looking for a better understanding of the SQL language this provides the in-depth view of how it works.

CloneDBC Screencast

2009-10-09T16:48:00.001-06:00

A new utility named CloneDBC is included with the latest Advantage OLE DB Provider (version 9.1.0.16) which replaces the DBCConvert.prg which was included in previous versions. The CloneDBC utility creates an Advantage Data Dictionary which contains references to the same tables as a FoxPro Database Container. This makes it easier for developers working with FoxPro data to access it through Advantage.

This short screencast, about 7.5 minutes, highlights the features and limitations of the utility. It also walks through the conversion of a sample DBC. You can get more information about CloneDBC in this announcement.

Translating DBC Views to Advantage

2009-10-07T07:00:00.000-06:00

You can use the CloneDBC to create an Advantage Data Dictionary (ADD) from an existing FoxPro Database Container (DBC). This tool adds the tables, views and referential integrity rules to the ADD based on the information in the DBC. However, FoxPro allows several things within their views which are not allowed by Advantage.

One of the most notable is the ability to order a view. Using an ORDER BY clause in a view is prohibited by the SQL standard and could result in poor performance. For example if you are using a view in another SQL statement that is ordered the server would have to order the data twice. The view would be ordered then the resulting join would be ordered.

The CloneDBC utility has an option to remove the ORDER BY clause from all the views that it copies from the DBC. I highly recommend using this option and then ordering your views within your application.

Another issue I ran into when cloning the Northwind sample database was the join order. The FoxPro view designer uses <prev join> when listing some of the joins that are used by the view. I found that this syntax sometimes does not translate well into Advantage. You may get errors stating that certain tables were not found. You can resolve this issue by removing and adding the joins again using your primary table as the first join. Below are two versions of the PRODUCT_SALES_FOR_1997 view. The top image shows the original view definition which gave me an "Table or alias not found : OrderDetails…" error.

In this case I deleted all of the joins and re-created them starting with the Orders to OrderDetails join. This did not change the results of the view but it did allow the view to be added to the ADD file correctly.

Another issue with cloning is support for various FoxPro functions. If you are using functions in your View I recommend checking the Supported Scalar Functions before cloning the DBC. Some of the functions I had to replace were ALLTRIM and NVL. The Advantage SQL Engine does not support ALLTRIM, however, it does support TRIM which provides the same functionality. The NVL command can be replaced with ISNULL which is supported in both FoxPro and Advantage.

Finally be aware of reserved keywords that may be used as column names or alias names. Use of these words will produce SQL syntax errors. The following example is the SQL Code for the SUMMARY_OF_SALES_BY_YEAR view.

     -- Original view statement year cannot be used as an alias without a delimiter

    SELECT YEAR(Orders.shippeddate) AS year, SUM(Order_subtotals.subtotal) AS total 

     FROM  Orders  INNER JOIN Order_Subtotals 

     ON  Orders.orderid = Order_subtotals.orderid 

    WHERE  Orders.shippeddate IS NOT NULL GROUP BY 1, 2

    -- Corrected view statement with year delimited with double quotes

    SELECT YEAR(Orders.shippeddate) AS "year", SUM(Order_subtotals.subtotal) AS total 

     FROM  Orders  INNER JOIN Order_Subtotals 

     ON  Orders.orderid = Order_subtotals.orderid 

    WHERE  Orders.shippeddate IS NOT NULL GROUP BY 1, 2

When cloning your DBC pay close attention to any reported errors after the conversion. Many times you can make a few simple changes to get the same functionality with Advantage.

CloneDBC Available with Advantage OLEDB Update

2009-10-05T07:00:00.001-06:00

With the release of Advantage 9 we added native support for the VFP 9 data types. Unfortunately we could not add support for the FoxPro database container (dbc). However, our Advantage data dictionary (ADD) provides the same functionality. With the initial release we included a program called DBCConvert which read the DBC and created an ADD which contained all the compatible features. The ADD could then be used to access the DBF data with an Advantage enabled application.

With the latest update of the Advantage OLEDB provider we have replaced the DBCConvert program with a new utility called CloneDBC, read the announcement here. This is a VFP application which simplifies the process of creating an ADD that points to the same data as an existing DBC. The utility includes a graphical interface which allows users to browse for a DBC to convert. The new utility has also been enhanced enabling it to copy more objects from the DBC than before.

The chart below highlights the differences between the original utility and the new utility.

DBCConvert.prg	CloneDBC.app
FoxPro program code, reads the DBC currently open in FoxPro	GUI based application allows for selection of DBC
Does not convert views with "ORDER BY" clause	Option to strip ORDER BY from views
Cannot create referential integrity rules if NOT DELETED() is not specified keys in the relation	Option to add NOT DELETED() to keys in relations

It is important to note that neither of these utilities make any changes to the DBC file or the tables. The utility simply reads the DBC and creates an ADD that contains all of the compatible objects. The new ADD will contain references to all the tables, views and referential integrity rules. We can not currently convert triggers or stored procedures.

You can download the latest version of the Advantage OLE DB provider from the Advantage Developer Zone.

FAQs – September 2009

2009-10-02T07:00:00.000-06:00

Local Server on a 64-bit OS

Although we introduced a 64-bit version of Advantage with version 9.0 it is only the Client-Server (Remote) Server version. The Advantage Local Server (ALS) is only available as a 32-bit version. Any application that will use ALS must be compiled as a 32-bit application. When using Visual Studio make sure you set the target platform to x86 to ensure that your application will be compiled as 32-bit.

Working with Backup Sets

Advantage online backup allows users to create a backup of their database or free tables while the tables are in use. You can then further protect the data by making a backup of this backup with your normal backup software or procedures.

Any backup created using online backup must be restored prior to using it. The restore process copies the tables back to the specified location and verifies their integrity. It also rebuilds all of the indexes to ensure they are up to date. Attempting to open the backed up tables directly will result in errors.

You can restore a backup using sp_RestoreDatbase, sp_RestoreFreeTables or the adsbackup utility with the –r option.

Error 75 on Entity Framework Model

You may receive an error when generating an ADO.NET Data Model if your primary key fields are allowed to be NULL. The error may look like the following.

Error 75: Key Part: 'Employee_Number' for the type EMPLOYEE is not valid. All parts of the key must be non nullable.

All of the fields used in primary keys or as foreign keys must be set to be non nullable. The Null Valid property for all of these fields must be set to NO.

Using the Entity Framework with Prism

Delphi Prism does not currently support the entity framework model wizard. There is no code generation for the oxygene language available for the wizard to utilize. You can manually map your tables, views and stored procedures into a model and write the code yourself. You could also generate the model using C# and access it from your Prism project.

There is some additional information about using Prism with the entity framework on Stack Overflow.

Delphi 2010 Support

2009-09-30T07:00:00.000-06:00

Embarcadero released Delphi 2010 in late August. In keeping with our commitment to supporting the latest version of Delphi we have updated the Advantage Delphi Components to support Delphi 2010.

Both version 8.1 and 9.1 have been updated. You can download the updated components on the Advantage Developer Zone. The latest versions are 8.1.0.46 and 9.1.0.15.

For the latest Advantage updates subscribe to the Advantage Announcements feed.