Views from an Advantage Evangelist

Resolutions

2012-01-06T10:37:00.000-07:00

After posting my 2012 Book Reading List I ran across an article by John Tierney called Be It Resolved. It has some great information about how to keep your resolutions and some surprising statistics that show that you can be successful in achieving you goals by following some simple strategies

Set a single clear goal
Pre-Commit
Outsource
Keep track
Don’t overreact to a lapse
Tomorrow is another taste
Reward often

So I thought I would apply these concepts to my 2012 resolutions to see if it will help me stay on track to reach my goals. One of the biggest portions is outsourcing, which I have done by publicly declaring one of my goals. My book reviews will serve as a mechanism for keeping track of my progress.

Now all I need to do is come up with those frequent rewards.

Book Reading Goals

2012-01-04T10:33:00.000-07:00

Its that time of year again where we all take an opportunity to start fresh and forget about the resolutions we didn’t accomplish last year in favor of the resolutions we make for this year. I am no exception to this cycle of making resolutions sticking to them for awhile and then letting them slide because life just got too busy.

One of my big resolutions is to do more reading. I always seem to have several books on my shelf as well as a big wish list of books that I would like to read. So this year I am setting the goal of reading one technical book every month and posting a review of each of them.

So, without further ado here is my 2012 list.

Living With Complexity Donald A Norman
Code Complete Second Edition Steve McConnell
Entity Framework 4 In Action
The Art of Unit Testing
RESTful Web Services
SQL Antipatterns
The Productive Programmer
Seven Languages in Seven Weeks
Head First Android Development
Head First C# Second Edition
The Design of Design
Thoughts on Interaction Design

SWFox Begins

2011-10-26T13:48:00.000-06:00

SWFox 2011 begins tonight with the keynote followed by a reception. This is my fifth year attending/exhibiting at this conference. As I mentioned in a previous post we will be doing two sessions on Extending Your FoxPro data to other applications.
One of the demos is a JQueryMobile website that displays the class information for SWFox. This data is stored in VFP9 dbf files and is also used by the Kokopelli scheduling application.

The demo was written using JQueryMobile and accessing the data via the Advantage Web Platform. The example will work in virtually every browser and will automatically resize for various devices such as smartphones and tablets. The data is all read-only, however, data can be modified using simple methods if desired.

You can view the demo here.

Southwest Fox 2011

2011-10-12T07:00:00.000-06:00

We will once again be attending SWFox in Phoenix Arizona which starts on October 26th. I always look forward to this conference because of the great FoxPro community, it always feels like a gathering of friends. I am always impressed with the variety of training offered and the diversity of applications that are using FoxPro.

This year we will be focusing on Extending FoxPro applications. Advantage provides easy access to FoxPro data without any conversion and allows for concurrent access ensuring that current applications will continue to run without any code changes.

I will be presenting a session titled “Mobile Access to your VFP” on Thursday October 27th at 2PM. Here is the class description.

Need to extend your current VFP application to mobile devices or another technology? We will show you how without changing you current VFP application. See how to share your VFP data with your desktop application and mobile devices at the same time, along with getting other client/server benefits. In our session we will demonstrate concurrent data access with an iPad, iPhone, Android, and a mobile web application. Your current data can now be accessed by mobile workers without having to change the application currently used by the office workers. Your VFP application's life span can grow... we can show you how to do it.

If you are going to be attending SWFox this year please come to the session. Feel free to stop by our booth we will have an amazing giveaway as always. See you in Phoenix.

Advantage Technical Summit

2011-09-26T07:00:00.000-06:00

We had another very successful technical summit in Boise last week. We focused on mobile development and our Advantage Web Api. The best feature of the Web Api is that it uses oData and JSON to transfer data which can be consumed by virtually any client. No specific Advantage client is required to send and receive data through the Web Api.

I taught classes on Mobile Web Development using JQueryMobile with oData. There is an excellent screencast done by Daniel Ryan which demonstrates our example Web Application. I also did a class on developing oData applications for iOS which includes iPods, iPhone and iPad devices. JD Mullin did a series of screencasts on using oData with iOS as well. Jed Thomet did a class on using oData from Android devices.

We also had our usual classes on using our other Advantage technologies and clients. We added a panel discussion session which gave attendees the opportunity to ask our development team general questions about Advantage and our development process.

The greatest benefit of our technical summits is the networking that goes on between Advantage users and our staff here. It is always great to hear about how you are using Advantage with your products. I hope we will see you at our next conference.

Delphi Live 2011

2011-09-19T16:22:00.000-06:00

We had the opportunity to attend Delphi Live! in San Jose California again this year. We always get to see and talk to many Delphi users that are also Advantage customers.

This year we were able to talk more about our Advantage Web Platform which provides oData and JSON access to Advantage data through the Web. This allows access from virtually any device, such as iPhones, iPads, Android phones and tablets. We have an example mobile website where you can see this technology in action.

There were many sessions on the latest release of Delphi, Delphi XE 2 at the conference. This release includes 64-bit support and Fire Monkey which is a cross-platform development mechanism. We will be updating the Advantage Delphi Components to work with this release and to include support for 64-bit applications. We are still investigating Fire Monkey support.

Limiting Users for a Database

2011-03-28T07:00:00.000-06:00

Advantage counts users based on the machine name that is connecting. This means that a single machine can have multiple connections to Advantage while only consuming a single license. There are times when you may wish to limit the number of users to a particular database. For example you may be hosting data for multiple customers at your site or in the cloud. You may need a way to limit each customer to a specific number of concurrent users. If each customer has a specific database you can accomplish this by using the sp_mgGetTableUsers system procedure when your application starts up.

The sp_mgGetTableUsers procedure takes one parameter which is the full qualified path to the table. You can provide a path to a data dictionary instead of a table to get a list of all the users connected to the database. See the example below

As you can see this list returns all connections to the specified file. You will need to count the results based on the type of users you wish to track. If you are using a traditional client/server solution where each user has their own machine name ( as shown in the example ) you need to count the number of unique UserName values. If you are hosting the application on a terminal server then you will need to count the unique TSAddress values.

If you are using Advantage version 10 or newer you can use SQL to count the connections. The example below demonstrates different types of counts.

By running a script like the one above when your application starts you can get an accurate count of the total number of users connected to the specified database. You could then compare this to the number of licenses for that database. The number of licenses could be stored encrypted in the registry, in a license file or some other method.

There are a few limitations to this approach. This technique will not prevent third party applications since the check is done by your application and not by the server. This technique will also not work for a web based application since all users will be connecting through one or more web servers.

Advantage Training

2011-03-14T07:00:00.000-06:00

The next Advantage Technical Summit has been scheduled for 18 – 20 May at the Bridgewater Mariott in New Jersey. The training will be co-located with the Sybase LPGA Match Play Championship which is held at the Hamilton Farm Golf Club. Attendees will get a wealth of information about Advantage and have an opportunity to attend the golf tournament as well.

You can get all the details here. The schedule overview is below and you can get details about all of the sessions using this link.

Wednesday, May 18th:
2pm – 5:00pm: Sessions
5:30pm – Hosted Dinner at Hamilton Golf Club with LPGA professionals

Thursday, May 19th:
8am – 2:30: Sessions
2:30 – 5:00: Sybase Match Play Championship Event
6:30 – Hosted Dinner and networking event

Friday, May 20th:
8am – Noon: Sessions
Noon – 1:30pm: Hosted Lunch at Hamilton Golf Club
1:30 – 5:00: Sybase Match Play Championship Event

I look forward to seeing you there.

Advantage in the Real World

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

Cary Jensen wrote an article about how he has been using Advantage with one of his customers. He focuses on several server-side features including replication, triggers and notifications. It provides some examples on how these features can be used to enhance an existing application. You can read the article here.

FAQs–December 2010

2011-01-03T07:00:00.000-07:00

Programmatically Getting the Type of Server

Several of the connection components ( i.e. tAdsConnection, AdsConnect ) have properties for server type and server version. However, there are times when you may need to know what type ( i.e. Windows, Linux ) of server you are connected to. The easiest way to get this information is to use sp_mgGetServerType as it returns text description as well as an enum value which represents the type of server. Beginning with version 9 two new enums were added; Windows NT 64-Bit (7) and Linux 64-bit (8). An example of the output is shown below.

You can also use the AdsMgGetServerType API call to retrieve the type of server you are connected to.

7209 Errors

The 7209 "SQL query aborted by user" error may become more common now that a SQL Timeout error has been added for all clients. This is typically seen if you give your users the ability to cancel a query through the use of a callback function. An SQL Timeout property has been a part of the OLE DB and .NET Data providers for some time. These properties are used automatically by the client to cancel a query. The default timeout is generally 30 seconds.

The default SQL Timeout property for version 10.x clients is 0, meaning no timeout. If you specify a value, in seconds, the query will be canceled if the timeout expires and you will receive a 7209 error.

Windows Installer Errors

The latest version of the Advantage .NET Data Provider requires version 4.5 of the Windows Installer. If you have an earlier version of Windows Installer you will get the error pictured. This error may occur on Windows XP machines. Windows Vista ships with version 4.0 and 4.5 is included in SP2. The latest Win7 and Windows Server 2008 R2 include version 5.0.

You can check the version of Windows Installer by typing "msiexec /?" in the run dialog or from a command prompt. You can get version 4.5 of the Windows Installer from MSDN.

Which Clients work with Enhanced Encryption

With the 10.1 release of Advantage only the ACE API and Advantage Components for Delphi include support for enhanced encryption. Additional clients will be released as they are available. Announcements will be made as additional clients are updated for enhanced security.

Connection Options for Strong Encryption

2010-12-22T07:00:00.000-07:00

Strong encryption is a new option available as an add-on that you can purchase for Advantage 10.1 and newer. This post will discuss the new connection options that are available for connecting to data that is encrypted with the strong encryption option.

The new connection options are listed below, these options are only available when using the new AdsConnect101 connection API. Currently only the Advantage Delphi Client and the ACE API implement this new functionality. Additional clients will be updated to include this new functionality soon.

FIPS
EncryptionType
DDPassword
TLSCertificate
TLSCiphers
TLSCommonName

When FIPS mode is specified the client will only open dictionary and tables that are encrypted using an approved FIPS 140-2 encryption mechanism (AES). TLS communication is also enforced when FIPS mode is specified. An error will be returned if either of these conditions is not met. FIPS mode must also be specified on the server using the FIPS configuration setting.

The EncryptionType option is used to specify the type of encryption to use when encrypting tables. This option is ignored for any table that is already associated with a data dictionary. The TLS* options are used when using TLS communications and will be discussed in a separate post.

The DDPassword is used to specify the AES encryption password when connecting to a dictionary encrypted with strong encryption. This password is required in addition to the dictionary user password. Note that the dictionary password does not allow access to the dictionary. Instead it is used to generate keys for working with the data dictionary. Access permissions are controlled by the dictionary username and password.

Dictionary encryption passwords can also be stored on the server using the SE_PASSWORDS configuration option with the remote server. Using this option allows the Advantage server to generate and store the keys necessary for opening AES encrypted data dictionaries upon startup. A password must be specified for each dictionary, for example: c:\data\sampledb\sample.add=strongpassword. This option is more efficient since the keys for accessing the encrypted data only have to be generated once and will be stored until the server is restarted. If the DDPassword option is used the keys will be generated each time the application connects which will add some overhead to the connections. This will be most noticeable with an application which connects and disconnects frequently.

The TLS* options are used to configure secure communication options. The TLSCertificate option specifies the full path to the name of the server public certificate. The TLSCiphers option specifies the allowed combination of TLS cipher suites. The TLSCommonName option specifies the “common name” of the server you are connecting to.

In my next post I will be walking through creating a TLS connection to dictionary using strong encryption.

Using Strong Encryption with Data Dictionaries

2010-12-20T07:00:00.000-07:00

Strong encryption is a new option available as an add-on that you can purchase for Advantage 10.1 and newer. This post will walk through the steps of creating a new data dictionary using the strong encryption and then converting an existing data dictionary to use strong encryption. I would recommend that you read my previous post Advantage Strong Encryption Overview or review the various encryption topics in the help file prior to reading through this post.

Creating a Strongly Encrypted Data Dictionary

When creating a data dictionary with strong encryption you must specify an additional password to encrypt the database. This password is in addition to any user passwords or a table encryption password. This password is also required when connecting to the dictionary. You also must specify the encryption type to be used. These two new options have been added to the CREATE DATABASE statement and the new AdsDDCreate101 ACE function. These options are also available when using Advantage Data Architect (ARC).

Encryption options for the dictionary and tables are located on the Security tab of the Create Database dialog in ARC. The default encryption type is the RC4 encryption which is included with Advantage. You can now also select AES128 or AES256, in order to use these encryption types you must have the OpenSSL libraries ( purchased separately ) in your search path or application directory. The OpenSSL libraries are required by both the client and the server when using strong encryption. A 7160 error will occur if the OpenSSL libraries cannot be found on the server. If the OpenSSL libraries are not found on the client a 6323 error will be returned. ARC provides a custom error message (see right) if the libraries cannot be located. Instructions for distributing the OpenSSL libraries are included with the strong encryption download. After selecting the encryption option you will need to enter an encryption password as shown below.

The encryption password can be supplied as the value for the DDPassword connection option. However, we do not recommend sending this password with every connection when using remote server. Once the server has generated the keys for accessing the encrypted database they are cached and do not need to be regenerated unless the encryption type is changed. Passwords for strongly encrypted dictionaries should be stored on the server using the SE_PASSWORDS configuration parameter. The keys for accessing the encrypted dictionary will be generated when the server starts up and therefore the client does not need to provide the DDPassword.

Changing Encryption Type of an Existing Data Dictionary

In previous versions of Advantage ( 10.0 and below ) you could only encrypt a data dictionary when it was created. There was no mechanism for encrypting the data dictionary files ( ADD, ADM, AI ) after they were created. With version 10.1 and later you can change the encryption type of the data dictionary files using the sp_SetDDEncryptionType system procedure. This procedure can also decrypt the dictionary files.

Setting the encryption type encrypts the data dictionary files and sets the encryption type for all of the tables associated with the data dictionary. Therefore, all of the tables associated with the dictionary must be decrypted before using the sp_SetDDEncryptionType procedure. After running the procedure tables can be encrypted using ARC or using the sp_EncryptTable system procedure. It is very important to note that all user passwords will be lost when you change an existing dictionary's encryption type. Data dictionaries do not store user passwords it only stores password verification, therefore passwords cannot be recovered.

The procedure takes five parameters; Path to the dictionary, AdsSys password, DDPassword used to encrypt the files, Encryption type ( RC4, AES128 or AES256 ) and whether or not to encrypt the dictionary. This procedure requires exclusive access to the data dictionary so you cannot have any active connections to the data. It is probably easiest to make a connection to the directory containing the dictionary and then running the procedure. An example of this procedure is shown below.

// Encrypt the data dictionary files with 256-bit AES encryption
EXECUTE PROCEDURE sp_SetDDEncryptionType('Sample.add', 'password', 
                                         'StrongPassword', 'AES256', TRUE)

// Remove encryption from the data dictionary
EXECUTE PROCEDURE sp_SetDDEncryptionType('Sample.add', 'password', 
                                         'StrongPassword', 'RC4', FALSE)

You can verify the type of encryption that the dictionary is using with the sp_GetSecurityInfo system procedure. This procedure returns information about the various security options configured for the current connection. A screenshot of the security configuration for the example dictionary is shown below.

In my next post I will discuss the new connection options related to strong encryption.

Lazarus Support

2010-12-10T07:00:00.001-07:00

Lazarus is the open source IDE for the FreePascal compiler, which can use many Delphi components. Below is the official description from the Free Pascal Lazarus Project:

Lazarus is the class libraries for Free Pascal that emulate Delphi. Free Pascal is a GPL'ed compiler that runs on Linux, Win32, OS/2, 68K and more. Free Pascal is designed to be able to understand and compile Delphi syntax, which is of course OOP. Lazarus is the part of the missing puzzle that will allow you to develop Delphi like programs in all of the above platforms. Unlike Java which strives to be a write once run anywhere, Lazarus and Free Pascal strives for write once compile anywhere. Since the exact same compiler is available on all of the above platforms it means you don't need to do any recoding to produce identical products for different platforms.

With the release of Advantage 10.1 support for Lazarus has been added to the Advantage Delphi Components. You will need to have Lazarus version 0.9.28.2 or later to use the components. The Lazarus components are not installed by default so you must choose a Custom Install and make sure you select the Lazarus Free Pascal option.

The TDataSet installer will not automatically add the Advantage components to Lazarus you must follow these steps to use the Advantage Components.

Open the Lazarus IDE.
Click on "Package", then "Open Package File (.lpk) ..." and browse to the adsl.lpk file in the TDataSet installation directory.
In the package window (shown below), click the "Compile" button.
When compilation is complete, click the "Install" button. When asked if you wish to rebuild Lazarus, select "Yes".
Lazarus should build a new version of the Lazarus executable and automatically start the new version of the executable.
The component palette should now contain the Advantage tab.
The Advantage components can now be used in Lazarus to access Advantage data from your application.

Once the components have been installed they can be used in an application. The components work just like they do in Delphi and are very easy to configure. Lazarus includes many of the same components as Delphi. Below is a screenshot of a simple application which displays the contents of the Customer.adt table from the example data supplied with the Advantage Delphi Components.

For additional information about using Advantage with Lazarus refer to the Advantage Help File or the Lazarus and Advantage Database Server entry in the Lazarus Wiki.

Advantage Strong Encryption Overview

2010-12-08T07:00:00.003-07:00

With the release of 10.1 a new FIPS 140-2 encryption option has been added to Advantage. You must purchase the strong encryption libraries separately, for more information contact your sales representative or visit our encryption page. You can also view the strong encryption datasheet.
The default encryption mechanism is a 160-bit RC4 encryption mechanism. This includes encrypting of all files ( tables, memos and indexes ) as well as encryption of communications. The strong encryption add-on provides a FIPS 140.2 compatible encryption mechanism. This implements both 128-bit and 256-bit AES encryption for files and Transport Layer Security (TLS) for communication.
Here is a quick summary of the new features and benefits of strong encryption.

TLS v1.0 operating over TCP/IP using RSA for key exchange with either 128-bit or 256-bit AES encryption with SHA-1 for message authentication
Tables can be encrypted using 128-bit or 256-bit AES
Improved key strength by salting and hashing keys
Ability to encrypt a data dictionary with an external password. This also allows you to change the type of encryption of an existing dictionary.
Added a FIPS mode for Advantage clients and server. This ensures that all encryption meets the FIPS 140-2 standard.

Several new stored procedures have been added to make working with encryption easier. These include functions for encrypting and decrypting tables, setting encryption types and retrieving information about the type of encryption being used. A list of these new system procedures are listed below.

sp_EncryptTable encrypts a table. When AES Encryption is specified on dictionary bound tables this procedure converts the table to strong encryption.
sp_DecryptTable decrypts tables.
sp_SetDDEncryptionType converts the type of encryption used by a data dictionary
sp_GetSecurityInfo returns information about the type of encryption being used on the connection

Next week I'll be walking through using the new strong encryption with Advantage.

Advantage 10.1 Now Available

2010-12-06T07:00:00.000-07:00

Advantage version 10.1 is now available for download on the DevZone. Here are the highlights:

Support for Delphi XE
Support for Lazarus
Unicode Full Text Search Support
Support for Copy/Paste of rows in ARC
Strong Encryption and FIPS 140-2 compliance

For a complete list of supported platforms and IDEs refer to the Advantage Supported Platforms document. Details on the new encryption options is available here. For additional details on the new features refer to the What's New in Advantage 10 and 10.1 document.

I'll have a couple of articles on the new encryption support later this week.

Geek Christmas List - 2010

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

It's the most wonderful time of the year and many sites are publishing gift guides. Of course many of these sites are doing it for purely capitalistic reasons, which I fully support. I just like to take this opportunity to talk about some of the cool gifts that I have been keeping my eye on this year.
I have been a fan of Apple products for several years, I have always liked the quality and usability they provide. So this year I have looked into getting the latest generation AppleTV. It allows access to your iTunes library, rentals and Netflix. It has HDMI output and both wired and wireless network adapters. We gave away a couple of these at the Southwest Fox show a couple of months ago. I am also very excited about this device due to the new airplay support which allows users to send content from other iOS devices directly to the AppleTV via a wireless network.
Another great Apple product is the new MacBook Air, no offense to the iPad which I also think is awesome. The new MacBook Air brings all of the great things about the iPad to a full computer. It is equipped with a Solid State Drive (SSD) which provides nearly instant on just like the iOS device. It has exceptional battery life and a full keyboard. Unlike other iOS devices it includes support for Flash providing for a full web browsing experience.
If your looking for something for your current PC I would recommend a SSD. Prices have come down significantly over last year with drives starting at under $100. Many of the reviews I have read recommend the Crucial RealSSD C300 series of drives. They have good durability and very fast data transfer rates ( reads up to 355MB/s ). The 128GB model costs about $300 and is a good size for a laptop especially if you take your current HDD and put it into an expansion bay that fits where the DVD drive resides.
I use a laptop with a docking station at work which allows me to run two external monitors. However, there are a few times when I would like to have just a bit more space. Since you cannot add an additional video card to a laptop I have been researching a USB Display Adapter. After seeing a few in action and reading several reviews I have settled on the UV Plus from EVGA. It is available online for about $50 and supports resolutions up to 1600 X 1200 at 60hz.
There are many other less practical gifts out there. For example I have wanted to get a USB Rocket Launcher for years, we have also discussed marshmallow guns. A friend of mine bought a pair of the eight bit dynamic life t-shirts for him and his wife. These shirts have hearts on them that light up as the shirts get closer together. Maybe I'll just get my wife the I <heart> my geek shirt this year.
If your looking for some more gift guides I would recommend the gift guides available on Gizmodo.

FAQs – November 2010

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

Advantage ADO.NET Designers Don't Work with Delphi Prism

The Advantage .NET Data Provider includes several wizards which enable functionality such as creating a connection using the Server Explorer, Drag and Drop onto a DataSet object and generating typed DataSets. Addins like these for Visual Studio require the full version of Visual Studio. They will not work in the Express versions or Shell versions of Visual Studio. Since Delphi Prism uses the Visual Studio shell these designers are not available to Prism users.

Delphi XE Support

The latest version of Delphi, Delphi XE, is not yet supported by the Advantage Components for Delphi. Support for Delphi XE is planned for Advantage 10.1 which is scheduled for release in December. Delphi XE support will also be included in a maintenance release of the Advantage Components for Delphi version 9.1 shortly.

For the latest information on all supported platforms and IDEs refer to the Advantage Supported Platforms document.

7075 Error with Local Server

The Advantage Local Server (ALS) is limited to 5 concurrent users and will return a 7075 error when a sixth user attempts to connect. Another user cannot connect until one of the current users disconnects. A user is each machine that is connected to ALS.

If a client is ended abnormally, i.e. CTRL-ALT-DEL, the table(s) may not be closed correctly. In this case a new user may receive the 7075 error even if fewer than five users are currently connected. You can resolve this problem by opening and closing the table from the workstation where the application abnormally terminated. This will "free" up the connection for a new user.

ARC Slow to Open a Table with Many Fields

When opening a table with many fields ARC may freeze or hang for several seconds. By default ARC uses the size of each field to provide an optimal display in the grid when the table is displayed. The more fields in the table the longer this process can take.

You can avoid this problem by changing the default settings in ARC. Choose Tools –> ARC Settings and go to the User Interface tab. From here you can limit the size of the columns using the "Limit amount of data displayed…" and "Minimum Column Width' settings. More information is available in this knowledge base article.

FAQs – October 2010

2010-11-01T08:00:00.000-06:00

Distributing Advantage Client DLLs

The Advantage client engine is required by nearly every client and must be distributed with your application. You must include the ACE32.dll with all Windows 32-bit applications with the exception of Java clients. The Advantage JDBC driver includes all of the necessary client code within the driver itself. Windows 64-bit applications require the 64-bit version of ACE which is available for both version 9 and 10. In version 10 this dll has been renamed to ACE64.dll, with version 9 it is still named ACE32.dll. The 64-bit version of the Advantage client dlls are included with the ACESDK.

You must also include the Advantage Local Server files or Advantage Communication dll for remote server applications. The Advantage Local Server dll is named ADSLOC32.dll, version 10 includes a 64-bit version ADSLOC64.dll. You can also optionally include a configuration file ADSLocal.cfg. When using the remote server you will need to include the Advantage Communication Layer AXCWS32.dll or AXCWS64.dll for 64-bit in version 10.

For information about distributing specific clients see the help file.

Triggers on DBF Tables

You can assign triggers to DBFs when they are added to an Advantage Data Dictionary. These triggers will be executed as long as the table is opened through a data dictionary connection. DBFs that are associated with a data dictionary can still be opened as a free table. When the DBF is opened as a free table any associated triggers will not be fired.

Additionally triggers may not fire on a DBF opened through a data dictionary if the table is currently opened as a free table. More information can be found in this knowledge base article.

10038 Errors On Startup

When Advantage starts up it binds to two ports, one for LAN communication and one for Internet communication. The default port for LAN use is 6262 and the Internet port is disabled by default. If Advantage cannot bind to the specified ports it may cause 10038, Attempt to send a message through a socket failed, error. This can be caused if the LAN and Internet ports are set to the same value or by a corrupt protocol stack. It can also be caused by virtual sockets created by anti-virus programs. See this Knowledge Base article for additional information.

Getting Date/Time Values from the Server

There are several ways to get current date/time values from the Advantage server. There are several date/time functions available in the SQL engine. For the current timestamp use the NOW() function, CURDATE() or CURRENT_DATE() for the current date and CURTIME or CURRENT_TIME for the current time. Many of these can also be used as default values for data dictionary bound tables.

Additionally the AdsConnection object in .NET has a ServerTime property which contains the timestamp of the server. The TAdsConnection component in Delphi has a GetServerTime method which returns a tDateTime value.

Advantage Web API with Windows Phone 7

2010-10-29T07:00:00.001-06:00

Earlier in the week I discussed using the Advantage Web API with Windows Mobile 6 (WM6) in this post I will be discussing using the API with Windows Phone 7 (WP7). The techniques are very similar for both platforms. However, there are several differences with Microsoft's latest mobile platform.

Most notably WP7 applications are written in Silverlight or XNA which are both much different than the Windows Forms model used by WM6. The Silverlight libraries do not include any functions for creating a custom certificate handler, therefore you must use a valid certificate or register your self-signed certificate on the phone. I haven't found a good way of doing this yet so for now I am doing all my testing without SSL. However, you should always use SSL in a production environment.

The mechanism for retrieving data from the Advantage Web API is quite similar to the mechanism used with WM6. For WP7 I will once again use a JSON serializer and a custom class/object to work with the data on the device. Creating the class for the deserializer to work with is very similar to the one I discussed last week. The classes will use the System.Runtime.Serialization library I used an article by Nick Harris as my starting point. The class for the customer summary is below.

[DataContract]
public partial class CustomerSummary : BaseMyContent
{
  [DataMember]
  public string CustomerID { get; set; }
  [DataMember]
  public string CustomerName { get; set; }
  [DataMember]
  public string CustomerCompany { get; set; }
}

Web requests from WP7 are done in an asyncronous manner making the code a bit more complex than making a request with WM6. Additionally Microsoft provides a JSON serializer which is found in the System.Runtime.Serialization.Json library, which can be used to populate the data classes. I'll be using this serializer instead of the JSON.NET libraries.

private void GetData(string URL)
{
  var request = WebRequest.Create(HttpUtility.HtmlEncode(URL)) as HttpWebRequest;
  request.Accept = "application/json";
  request.Headers["Authorization"] = "Basic " + 
          Convert.ToBase64String(Encoding.UTF8.GetBytes(settings.Credentials));
  request.BeginGetResponse(RequestCallback, request);
}

void RequestCallback(IAsyncResult response)
{
  var request = response.AsyncState as HttpWebRequest;
  resp = request.EndGetResponse(response);
            
  if (resp != null)
  {
    var jsonSerializer = new DataContractJsonSerializer(typeof(BaseJSONResult));

    using (var stream = resp.GetResponseStream())
    {
      customerList = jsonSerializer.ReadObject(stream) as BaseJSONResult;
    }

    if (invoiceList.Result != null)
    {
      nextInvoices = invoiceList.Result.NextLinkUri;
      this.Dispatcher.BeginInvoke(() => DataRetrieved());
    }
    else
    this.Dispatcher.BeginInvoke(() => HandleError(invoiceList.Error.ErrorMessage.Value));
  }
}

void DataRetrieved()
{
  PageTitle.Text = "Invoice List";
  listboxCustomers.ItemsSource = customerList.Result.MyContents;
}

void HandleError(string errMessage)
{
  MessageBox.Show(errMessage);
  NavigationService.GoBack();
}

Since the request is processed asyncronously we need to use an Invoker to interact with the UI, hence the this.Dispatcher.BeginInvoke calls. Now that we have the results stored in our class we can now display the data. This is done by setting the ItemsSource property of the listbox and then binding the fields to the controls within the listbox. The xaml for the listboxCustomers is below.

<grid grid.rowspan="2" margin="12,149,12,12" x:name="ContentPanel">
  <listbox margin="0,0,-12,0" x:name="MainListBox" height="607"               selectionchanged="MainListBox_SelectionChanged" itemssource="{Binding Items}">
  <listbox.itemtemplate>
    <datatemplate>
        <stackpanel margin="0,0,0,17" width="432">
          <textblock textwrapping="Wrap" text="{Binding CustomerName}" />
          <textblock margin="12,-6,12,0" textwrapping="Wrap" 
                        text="{Binding CustomerCompany}" />
        </stackpanel>
      </datatemplate>
    </listbox.itemtemplate>
  </listbox>
</grid>

Advantage Data Class

2010-10-27T07:00:00.000-06:00

As I mentioned in my first post about the Advantage Web API I talked about a class that I wrote which parsed a JSON result and created a DataTable object. This is an alternate to using specific classes to work with the results from a Web API request.

I like this method because it can generate a DataTable for any request so I don't have to create a specific class for each set of data I request. The generated DataTable is also very easy to bind to various .NET controls. Using specific classes provide a more strongly typed representation of the data and can be bound to controls with a little more effort. Defined objects can also be manipulated directly by a JSON serializer.

In order to create a DataTable we will have to parse the JSON ourselves, we cannot rely on the serializer. This is done with the following method. Note that there is a mechanism for error checking in the method.

private bool ParseJSON( string stringJSON )
{
  string sValue = string.Empty;
  dataTable = new DataTable();

  using ( JsonTextReader reader = new JsonTextReader(new StringReader(stringJSON)))
  {             
    // Move to the array of objects
    do
    {
      if (reader.TokenType == JsonToken.PropertyName 
          && reader.Value.ToString().Contains("error"))
      {
        parseError(reader);
        return false;
      }
      reader.Read();
    } while (reader.TokenType != JsonToken.StartArray);


    // Get the dataTable
    PopulateDataTable(reader);
                
    // Get the rest of the Information
    while (reader.Read())
    {
      if (reader.TokenType == JsonToken.PropertyName)
      {
        if (reader.Value.ToString().Contains("__next"))
        {
          reader.Read();
          nextURI = reader.Value.ToString();
        }

        if (reader.Value.ToString().Contains("rows_affected"))
        {
          reader.Read();
          rowsAffected = Convert.ToInt32(reader.Value);
        }

        if (reader.Value.ToString().Contains("last_autoinc"))
        {
          reader.Read();
          lastAutoinc = Convert.ToInt32(reader.Value);
        }
      }
    }
  }

  return true;
}

To create the DataTable object we examine the first object in the results array. The JSON.NET Library has a mechanism for determining the data type which we will use to create the columns in the data table. We also need to store the information we retrieve since we can only move forward through the JSON string. We will do this with a simple helper class that will store the Column Name, Data Type and Value.

private DataTable CreateDataTable(JsonReader reader)
{
  DataTable dt = new DataTable();
  string sItem = string.Empty;
  List lItems = new List();

  // Create a data row based on supplied table
  DataRow dr = dt.NewRow();

  // Put the values into the datarow
  while (reader.TokenType != JsonToken.EndObject)
  {
    try
    {
      if (reader.TokenType == JsonToken.PropertyName)
        sItem = reader.Value.ToString();
      else
      {
        switch (reader.TokenType)
        {
          case JsonToken.Boolean:
            dt.Columns.Add(sItem, Type.GetType("System.Boolean"));
            break;
          case JsonToken.Date:
            dt.Columns.Add(sItem, Type.GetType("System.DateTime"));
            break;
          case JsonToken.Float:
            dt.Columns.Add(sItem, Type.GetType("System.Double"));
            break;
          case JsonToken.Integer:
            dt.Columns.Add(sItem, Type.GetType("System.Int32"));
            break;
          case JsonToken.String:
          default:
            dt.Columns.Add(sItem, Type.GetType("System.String"));
            break;
        }
        
        // Store the column value
        lItems.Add(new DataRowItem(sItem, reader.TokenType, reader.Value.ToString()));
      }
    }
    catch (Exception ex)
    {
      Console.WriteLine(ex.Message);
    }

    reader.Read();
  } 

  // add the row to the datatable
  AddDataRow(lItems, dt);

  return dt;
}

The AddDataRow function adds the values that we stored in the DataRowItem object into a new row in the newly created DataTable.

private static bool AddDataRow(List lDataItems, DataTable dt)
{
  // Create a data row based on supplied table
  DataRow dr = dt.NewRow();

  foreach (DataRowItem di in lDataItems)
  {
    switch (di.ColumnType)
    {
      case JsonToken.Boolean:
        dr[di.ColumnName] = Convert.ToBoolean(di.ColumnValue);
        break;
      case JsonToken.Date:
        dr[di.ColumnName] = Convert.ToDateTime(di.ColumnValue);
        break;
      case JsonToken.Float:
        dr[di.ColumnName] = Convert.ToDouble(di.ColumnValue);
        break;
      case JsonToken.Integer:
        dr[di.ColumnName] = Convert.ToInt32(di.ColumnValue);
        break;
      case JsonToken.Null:
        dr[di.ColumnName] = null;
        break;
      case JsonToken.String:
        dr[di.ColumnName] = di.ColumnValue;
        break;
    }
  }

  // add the row to the datatable
  dt.Rows.Add(dr);

  return true;
}

After the first record has been read and the table has been created the rest of the records can be added without having to create a DataItem object. Therefore, there is an overloaded version of AddRow

private static bool AddDataRow(JsonReader reader, DataTable dt)
{
  string sItem = string.Empty;

  // Create a data row based on supplied table
  DataRow dr = dt.NewRow();

  // Put the values into the datarow
  while (reader.TokenType != JsonToken.EndObject)
  {
      try
      {
        if (reader.TokenType == JsonToken.PropertyName)
          sItem = reader.Value.ToString();
        else
        {
          switch (reader.TokenType)
          {
            case JsonToken.Boolean:
              dr[sItem] = Convert.ToBoolean(reader.Value);
              break;
            case JsonToken.Date:
              dr[sItem] = Convert.ToDateTime(reader.Value);
              break;
            case JsonToken.Float:
              dr[sItem] = Convert.ToDouble(reader.Value);
              break;
            case JsonToken.Integer:
              dr[sItem] = Convert.ToInt32(reader.Value);
              break;
            case JsonToken.Null:
              dr[sItem] = null;
              break;
            case JsonToken.String:
              dr[sItem] = reader.Value.ToString();
              break;
          }
        }
      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
      }

      reader.Read();
  }
  // add the row to the datatable
  dt.Rows.Add(dr);

  return true;
}

As I said in the beginning this is a bit more difficult than using pre-defined objects/classes with a JSON serializer/deserializer. However, DataTables are very easy to bind to many of the controls available in the .NET Compact Framework. The downside is that the data types may not always be correct depending on the serializer you are using.

Book Review – Database in Depth

2010-10-25T07:00:00.000-06:00

In keeping with my "Back to Basics" theme for this year I picked up a copy of Database In Depth by C.J. Date. This book includes a basic introduction into the relational database model and how it is different than many of the many implementations currently available. This book discusses the relational theory as developed by E.F. Codd and how this theory has become the basis for how data is manipulated today.

C.J. Date covers this very complex topic using many examples and discussions. I found it very enlightening and useful although it took some effort to get through the book. The material is explained and examples are given, however, sometimes I had to do the provided exercises in order to really get a firm grasp on the information. This is definitely a book that requires some effort by the reader.

The first chapter of the book is a quick introduction into the terminology used in relational theory and how some of it is different than the SQL model which gets more attention. The following four chapters go into these various types and relationships in much more detail. The next two chapters focus more on how the relational theory is applied with respect to constraints and database design. The final chapter defines the relational model and compares it to other data models.

Terminology is a major factor in this book. I tend to think of databases in a SQL sense which is slightly different than the relational sense. SQL is based on rowsets or resultsets whereas relational theory uses relvars and tuples to define data. Once I was comfortable with the differences the information was easier to digest.

Of particular note is the fact that Mr. Date does not support the implementation of nulls in databases. He makes some convincing arguments in chapter three. Essentially, it boils down to the complexity of three value logic. Because a boolean result can now return three values ( true, false or unknown ) instead of just two. I agree with his point, however, in the real world I see practical uses for allowing null.

The final chapter does a good job of wrapping up the information presented in the book as well as provides a definition of the relational model. Below are the five components of the relational model ( from chapter eight page 164 ):

An open-ended collection of scalar types, including the particular type BOOLEAN (truth values)
A relation type generator and an intended interpretation for relations of types generated thereby
Facilities for defining relation variables of such generated relation types
A relational assignment operator for assigning relation values to such relation variables
An open-ended collection of generic relational operators for deriving relation values from other relation values.

The bottom line: this book contains a lot of useful information on the foundations of modern database systems. It is highly technical and covers the topics in a very textbook manner. Although the book is not an "easy read" there is a ton of good information in the 200 pages. This isn't the kind of book you read straight through. It takes time to get through the material and it even includes many exercises used to reinforce the information.

Handling Errors with the Advantage Web API

2010-10-22T07:00:00.000-06:00

The one thing that I didn't discuss in my previous post was how to handle errors returned from an Advantage Web API request. If an error occurs when making a request the error information is packaged up as a JSON response just like a successful data request. Therefore, errors can be handled using classes just like we did with data results.

Below is a sample error response received from a request to the Web API:

{
  "error": {
    "code": "7200",
    "message": {
      "lang": "en-US",
      "value": "Error 7200:  AQE Error:  State = HY000;   NativeError = 5004;  [iAnywhere Solutions][Advantage SQL][ASA] Error 5004:  Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: SomeTable"
    }
  }
}

Instead of the results array we received with a successful request we are now getting error information. So we need to create classes for the serializer to load the information into. The error object defined in the JSON includes a code and a message object. Therefore we will need two classes to handle an error.

// processes the error tag
public class oError
{
  public string code;
  public oErrorMessage message;
}

// processes the message tag
public class oErrorMessage
{
  public string lang;
  public string value;
}

Finally we will need to add this new error object to the oDataResult class so it can be processed. This allows us to process the JSON once checking for either results or an error. You will need to set the MissingMemberHandling property to ignore otherwise an error would occur during deserialization. So our oDataResult class contains three items.

public class oDataResults  // T needs to be a list of objects
{
  public DInfo d;
  public oError error;
  public oQueryMeta __metadata;
}

Now when we make a request we can check for an error before using the list of results.

using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
  using (Stream dataStream = response.GetResponseStream())
  {
    using (StreamReader reader = new StreamReader(dataStream))
    {
      JsonSerializer serializer = new JsonSerializer();
      serializer.MissingMemberHandling = MissingMemberHandling.Ignore;

      oDataResults olist;
      olist = (oDataResults)serializer.Deserialize(
               new JsonTextReader(reader),
               typeof(oDataResults));

      // Check for any errors
      if (olist.error == null)
        dgTable.DataSource = olist.d.results;
      else
        MessageBox.Show(olist.error.message.value);
    }
  }
}

Consuming Advantage Web API Results

2010-10-20T07:00:00.000-06:00

As I discussed in my last post the Advantage Web API returns data using JSON in a format based on the oDATA Protocol Standard. In order to use this data it needs to be transformed into an object that the client can work with. Let's begin by looking at a sample of the data returned from the Advantage Web API. I Included only a single record in the sample and I included the additional information ( rows_affected and last_autoinc ) which will be returned with each request.

{
  "d": {
    "results": [
      {
        "__metadata": {
          "uri": "https://Server.localdomain:6282/adsweb/example/v1/query/select * FROM customer"
        },
        "CustID": "c50585b4-b039-4843-a092-ef00da72bb90",
        "CustNum": 10000,
        "LastName": "Martin",
        "FirstName": "Denise",
        "Gender": "F",
        "CompanyID": 446,
        "CustomerSince": "1953-10-08",
        "Address1": "16907 HERITAGE COURT",
        "Address2": null,
        "City": "Wolf Point",
        "State": "MT",
        "ZipCode": "59201",
        "CellPhone": "(555) 555-4289",
        "WorkPhone": "(555) 555-9415"
      },
    ],
    "__next": "https://Server.localdomain:6282/adsweb/example/v1/query/select%20*%20FROM%20customer?$skiptoken=21"
  },
  "__metadata": {
    "rows_affected": -1,
    "last_autoinc": 0
  }
}

The curly braces ( {} ) are used to distinguish objects within the string. The brackets ( [] ) are used to indicate arrays. Therefore the rows are defined within the results array. JSON serializers can be used to deserialize this into an object as long as the object conforms to the data provided in the string. Additionally we need to have a list of the object since the results object is an array of the rows. The object and list are defined below:

// definition of Customer fields
public class Customer
{
   public string CustID;
   public int CustNum;
   public string LastName;
   public string FirstName;
   public string Gender;
   public int CompanyID;
   public DateTime CustomerSince;
   public string Address1;
   public string Address2;
   public string City;
   public string State;
   public string ZipCode;
   public string CellPhone;
   public string WorkPhone;
}

// List of customers 
public class CustomerRows : List { }

The serializer will copy the information from the results array into the two objects defined above. The values will be assigned based on the names defined in the JSON so the types defined in the class must have exactally the same names. These classes must be defined for each table or query that you want to receive.

There are a few more classes that we need to define to handle the rest of the JSON string. We will still need classes to handle the d and the final metadata information. Unlike the classes mentioned above these classes can be reused for any JSON object. These objects are shown below:

// Processes the __metadata tag
public class oDataResults  // T needs to be a list of objects
{
   public DInfo d;
   public oQueryMeta __metadata;
}

// processes the results tag
public class DInfo
{
   public T results;
   public string __next;
}

// processes the final __metadata tag
public class oQueryMeta
{
   public int rows_affected;
   public int last_autoinc;
}

The first class ( oDataResults )determines the type that will be used when the results array is processed. The DInfo class allows the serializer to put the data into the specified type and gets the __next value. The oQueryMeta class gets the rows_affected and last_autoinc values.

With all of our classes defined all we need to do is send our request and process the results.

using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
  using (Stream dataStream = response.GetResponseStream())
  {
     using (StreamReader reader = new StreamReader(dataStream))
     {
       JsonSerializer serializer = new JsonSerializer();
       
       oDataResults oList;
       olist = (oDataResults)serializer.Deserialize(
				new JsonTextReader(reader), 
				typeof(oDataResults));
       
       // Bind the list of customer objects to a datagrid control   
       dgCustomer.DataSource = olist.d.results;
     }
  }
}

We will have more sample code available with the release of the Advantage Web API.

Advantage Web API with Windows Mobile

2010-10-18T07:00:00.002-06:00

The Advantage Web API is a framework for allowing developers to access their data from virtually anywhere on just about any device. It consists of a set of pre-built web services running on a lightweight version of Apache. The interface will provide both SQL and Table style access methods to Advantage data. Communication is secure over HTTPS using SSL and requires data dictionary authentication. The basic architecture is shown below.

Now that we have established the basic framework it is time to create a simple Windows Mobile 6 application that retrieves data from the Advantage Web API. In this case we are going to simply send a query to the server to retrieve a list of customers as shown.

The first step is to create our web request with an appropriate URL. We then need to add some header information which includes the result type ( application/JSON ) and our authentication. Once the request has been created we then execute the request. The request is sent via HTTPS ensuring that the authentication and data are transferred securely. This means that the server is using some type of certificate to provide the secure communication.

string sURI = "https://localhost:6282/adsweb/example/v1/query/";
sURI += "SELECT%20*%20FROM%20CustomerList";
HttpWebRequest request = WebRequest.Create(sURI) as HttpWebRequest;
request.Accept = "application/json";
string auth = "Basic " + Convert.ToBase64String(Encoding.ASCII.GetBytes("adssys:"));
request.Headers["Authorization"] = auth;

In my case I am using a self-signed certificate which is not recognized by the device. Initially when I made the request i received a "Could not establish a trust relationship with remote server" error message. This is because there is no default mechanism for allowing users to accept an unknown certificate. In order to resolve this error by creating a custom certificate verification handler. You can get all the details from this MSDN article.

The next step is to process the result returned by the request. The data is returned back as a JSON string that we can parse. In my case I used the JSON.NET library from CodePlex to create my own parser. The AdsDataObject stores metadata contained in the request and dynamically builds a DataTable based on the returned data. I'll provide the details on this object in a future post.

Here is the code that processes the response to the web request. Once the request has been processed the data can be shown in the data grid.

using (HttpWebResponse response = request.GetResponse() as HttpWebResponse)
{
  using (Stream dataStream = response.GetResponseStream())
  {
    using (StreamReader reader = new StreamReader(dataStream))
    {
       string json = reader.ReadToEnd();

       oData = new AdsDataObject(json);
       dgCustomer.DataSource = oData.dataTable;
    }
  }
}

By default a maximum of 20 rows are returned with each request, you can change this by using the $top parameter ( i.e. …/query/select%20*%20from%20table?$top=10). The request includes an additional parameter, named __next, which contains a URL for getting the next records. The parser/deserializer that you use will need to capture this value.

SWFox Conference 2010 Wrap-Up

2010-10-16T20:29:00.000-06:00

We wrapped up our participation at SWFox on Saturday night. We gave away our Sybase Developer's Rock t-shirts and some Advantage 10 pens. We also provided copies of the latest Advantage book to all of the FoxPro user groups who had representatives attending.

We had a second opportunity to present our class on extending FoxPro data to mobile devices on Saturday morning. Once again we had several attendees and we were able to have a good discussion of the technology and the mobile landscape. Overall the technology was well received and I got some great feedback on how it could be used in the field.

This year we gave away two of the bran new AppleTV devices. We gave one away at the dinner reception on Friday night and the other was given away after lunch on Saturday. I would like to congratulate David Bernard and Ross Taylor and I hope they enjoy their new AppleTVs.

Finally my thanks once again to all the conference organizers; Rick Schummer, Doug Hennig and Tamar Granor who always make the conference run smoothly. See you all next year.