Views from an Advantage Evangelist

Stored Procedure Enhancements

2012-05-25T07:00:00.000-06:00

For this week's Advantage Version 11 demonstration I'll be discussing the many enhancements pertaining to SQL stored procedures. There are two major changes in SQL stored procedures included in version 11. First input parameters are directly accessible within the SQL script, there is no need to open the __input table. Second stored procedures can now return varying output. The output of the stored procedure is defined by the final SELECT or EXECUTE PROCEDURE statement.

The demonstration runs approximately 7 minutes and demonstrates the difference between stored procedures written for version 10 and the new version 11 changes. You DO NOT have to rewrite you current stored procedures, version 11 simply adds another method you can use.

You can view the demonstration by clicking on the image or you can download a Windows Media version here. You can find all the Version 11 demonstrations on the DevZone under screencasts.

Advantage Web Platform Webcast

2012-05-23T07:00:00.000-06:00

Just two weeks until our next Advantage version 11 webcast. This time we will be discussing the new Advantage Web Platform which allows you to provide Advantage data to virtually any platform!

The live webcast is on Wednesday June 6th at 11:00 AM EDT. We will present all the material and be available to answer questions during the webcast. You can register right here.

Here is the full description:

Mobile Access with the Advantage Web Platform

Need mobile device access to Advantage data?
Join us for part 2 of the Advantage Database 11 webcast series. Find out how the Advantage Web Platform provides client-less access to Advantage data from mobile phones, tablets, or any platform with any development environment. Additionally you will see the new Advantage Web Administrator, which allows you to monitor the server status and make changes to configuration. This utility was built on the Advantage Web Platform allowing for secure administration of Advantage from anywhere.

Online Table Maintenance

2012-05-18T07:00:00.000-06:00

This is the first in a series of weekly demonstrations for Advantage Version 11. This weeks demonstration is on our new online maintenance features. This gives you the ability to pack, re-index or alter a table while other users have the table open.

This short demonstration ( ~8.5 minutes ) gives a brief overview of how the online maintenance works and then demonstrates a pack and alter of tables that are in use.

You can view the demonstration by clicking on the image or you can download a Windows Media version here. You can find all the Version 11 demonstrations on the DevZone under screencasts.

Advantage 11 Overview Screencast

2012-05-16T07:00:00.000-06:00

Here is your chance to get a look at Advantage Version 11. This screencast covers the major features included in version 11 with the exception of the web platform. The presentation is approximately 35 minutes long and includes three demonstrations. It was originally presented live on May 9th.

You can view the screencast by clicking on the image. We have also created an Windows Media version as well as versions for both the iPad and iPod/iPhone. For more Advantage screencasts visit the DevZone and choose the screencasts menu item.

What's New in Advantage 11 Webcast

2012-05-09T11:00:00.000-06:00

Our first webcast in our two part series of Advantage 11 was a huge success. Thanks to everyone that attended and for the fantastic questions you asked. We covered a lot of information in just over an hour.

If you missed the webcast we will be posting a recording early next week. If you were registered you will be getting an e-mail with the link to the recording. If you didn't register, I will be posting a link to the recording when it is available.

Our next Advantage 11 webcast will cover the Advantage Web Platform and will be on June 6th. Invitations will be going out soon. There will also be a link available on our homepage.

Telling a Mobile Story

2012-04-30T07:00:00.000-06:00

You may have seen my review of "The Back of the Napkin". The book is all about using pictures and visual thinking to solve problems and sell ideas. I enjoyed the book so I thought I would try to incorporate some drawing into some of my presentations. I drew a picture as I was describing why mobility is important in today’s business world. The final drawing is shown below.

Although every picture tells a story sometimes a few words help. In this case we start on the right with the extremely large computer, I believe I drew a TRS80 here, connected to a database in a client/server model. The person on the far left is a physician who is working with a patient and documenting what is being done.

The middle of the diagram shows the various ways the doctor’s work can be recorded. The orange path shows the traditional paper route the doctor writes down the diagnosis and treatment then gives the paper to the billing person for entry into the system. The yellow path represents the doctor recording the information electronically but having to print the result out to once again give to the billing person. Finally the blue path shows the data going directly to the database allowing for more accuracy and allowing the billing person to interact with patients instead of just doing data entry.

I used this story to introduce the need for mobility solutions for common applications. With the Advantage Web Platform data can now be sent and received from virtually any mobile device. Now the physician can enter information about a patient while visiting them at the hospital and the relevant information can be sent directly to the billing system at the office.

This scenario can be used in all types of industries. Perhaps you manage documents and need to allow access on a tablet. Maybe you support an industry like construction or landscaping where providing an on-site quote can win you the business. The possibilities are endless.

Even with my meager drawing skills the picture seemed to resonate with the group. I think it was relatively effective in conveying my point. If you attend any of my upcoming presentations you may just see some drawing to go along with the slides.

The Advantage Web Platform is available as part of the Advantage 11 Beta which you can download now.

Unicode Usernames and Passwords

2012-04-27T07:00:00.000-06:00

Advantage added support for Unicode in version 10. I did a few posts and a demo of this feature in 2009, just put Unicode in the search box to find relevant posts. However, Data Dictionary metadata is still stored in Character fields.
This means that usernames and passwords cannot be stored as Unicode by default. In order to use Unicode usernames and passwords you will need to convert the characters to UTF8 before storing them in the dictionary.
For Delphi 2009 and newer these Unicode strings are stored using UTF16, the default for Windows systems. These UTF16 values need to be converted to UTF8 before storing them in the data dictionary. An example of creating a user with Unicode is shown below.

procedure TForm1.btnCreateUser(Sender: TObject);
var
  FhConnection   : AdsNativeInt;
  RetVal         : Cardinal;
begin
  //First make an admin connection
  RetVal := ace.AdsConnect60('C:\Data\TestDb\TestDb.add', ADS_REMOTE_SERVER,
                   'adssys', 'password', ADS_DEFAULT, @FhConnection );

  if ( RetVal = AE_SUCCESS ) then
  begin
    // Create the user using the values from edit boxes
    RetVal := ace.AdsDDCreateUser(lHandle, nil, 
                      PAnsiChar( UTF8String( edtUsername.Text ) ),
                      PAnsiChar( UTF8String( edtPassword.Text ) ), 
                      'Unicode username and password' ) ;
    // add error checking here
  end
  else
    showmessage( 'Connection Failed: ' + IntToStr(RetVal));
end;

Once the user has been created you can then connect using the same type of casting.

procedure TForm1.btnLoginClick(Sender: TObject);
var
  FhConnection   : AdsNativeInt;
  RetVal         : Cardinal;
begin
  // Connect using the supplied username and password
  RetVal := ace.AdsConnect60('C:\Data\TestDb\TestDb.add', ADS_REMOTE_SERVER,
                   PAnsiChar( UTF8String( edtUsername.Text ) ),
                   PAnsiChar( UTF8String( edtPassword.Text ) ),
                   ADS_DEFAULT, @FhConnection );

  if ( RetVal = AE_SUCCESS ) then
    showmessage( 'Connection Successful!' );
  else
    showmessage( 'Connection Failed: ' + IntToStr(RetVal));
end;

You can do similar conversions in other languages as well. If you need code for a specific language let me know and I'll get some additional examples posted.

LAFox User Group

2012-04-23T07:00:00.000-06:00

I will be traveling down to Los Angeles this week to present at the LAFox User group Meeting. The meeting is on Wednesday April 25th at 7:30 PM. The meeting is held in Councilman Bill Rosendahl’s Westchester Office: 7166 West Manchester Ave. 90045 see the map below.

I will be discussing mobilizing your application using Advantage Database Server and our new Web Platform. I will be doing demonstrations using the beta version of Advantage 11, so this is your first chance to see the latest release of Advantage. You can get more details about the meeting by visiting the LAFox Homepage.

Book Review–Back of the Napkin

2012-01-23T06:00:00.000-07:00

I have been trying to mix up my non-fiction reading with some business type books to go along with my technology reading list. The first business book I read this year was The Back of the Napkin by Dan Roam. This book describes the use of simple pictures as a mechanism for demonstrating and solving complex business problems.

The concept was interesting and compelling but I have never been an artist. Of course, Dan had anticipated this type of response and addresses it in the first chapter. Dan categorizes people into three categories he refers to as pens.

Black pen people are eager to pick up a pen and start drawing. Yellow pen people are reluctant to draw but generally add good feedback on pictures that have been drawn. Red pen people are very reluctant to draw at all but generally add insightful dialog to pictures. There is a test to determine what type of “pen” you are. I ended up being in between yellow and red.

If you want to get a really concise summary of what the book is all about start with chapter sixteen. It gives a quick back of the napkin presentation on just what visual thinking is and how it helps get your point across.

Dan breaks down the visual thinking process into four parts; look, see, imagine and show. Looking is simply taking in the entire scene get a general impression of what your eyes are showing you. This is closely followed by seeing where we start to focus in on a particular part of what we have looked at. Next we imagine what could happen based on the things we have seen. Finally we want to show what we have seen and imagined to someone else. This process is summed up nicely in Dan’s example ( chapter 3 page 35 ).

This process shouldn’t come as a surprise. After all, we go through these steps thousands of times a day—like when we cross the street, for example. We look both ways and if we see a car nearby, we stop. If we see a car at a distance, we imagine weather we can make it across before it arrives, and if so, we show our decision by confidently striding across the street or waiting until the car has safely passed by.

The look and see phases of this process are very interdependent, in fact, they can form a loop where we go back to looking at the whole picture after we have focused (seen) on a particular piece. This can be followed by focusing on another aspect of the whole.

For the imagine phase Dan developed a series of questions to ask about the problem in order to decide how to present it. To make it easier to remember he came up with the mnemonic SQVID.

S for Simple vs. Complex
Q for Quality vs. Quantity
V for Vision vs. Execution
I for Individual Attributes vs. Comparison
D for Change ( Δ ) vs. Status Quo

Once we have asked the imagination questions it is time for us to show our idea. To do this we have to answer the time honored questions of Who, What, When, Where, How and the all important Why. Dan once again gives us visual answers for these questions.

Who/What – Portraits
How Much – Charts and Graphs
Where – Maps
When – Timeline or Gantt Chart
How – Flowchart
Why – Multivariable Plot

As you can imagine, even without a picture, the book goes much more in depth on each of these points. In fact there are plenty of pictures to back up the points on visual thinking.

The bottom line: I found this book easy to read and I enjoyed Dan’s casual writing style. The points he made are backed up with many examples and a bit of science, not to mention that they just make a lot of sense. If you are looking for a way to deliver more impact with your message you could benefit from the techniques in this book.

Book Review: Emotional Design

2012-01-09T06:00:00.000-07:00

I have long been a fan of user interface design and design in general. Having been impressed by The Design of Everyday Things, I decided to pick up a few more of Donald A. Norman’s books. Emotional Design, why we love (or hate) Everyday Things, is about how the design of things effect us emotionally. Or more accurately how we can design things to elicit the emotional response that marketers want.

Although Dr. Norman tends to have a rather dry academic writing style from time to time, he backs up his claims with many stories and examples. Overall I found this book to be very interesting and I picked up some very interesting tidbits on how design effects us on many levels. The book primarily focuses on the design of physical things rather than software specifically but many of the principals can be applied to software as well.

One of the very interesting studies presented in this book showed that attractive things work better. It brought to mind the “form follows function” premise from architecture. In the study by Noam Tractinsky ATMs were compared. All of the ATMs performed the same functions, had the same number of buttons but some were more attractive. In all of the tests users felt that the more attractive ATMs worked better than the less attractive ones.

This demonstrates how the look and feel of a device is just as important as the function of the actual device. Take the iPhone, which revolutionized the smart phone industry, its functionality isn’t completely revolutionary but its look and feel was entirely new. Smart phones have allowed for phone calls, web surfing, games and many other applications for many years but none of them did it in such an elegant way. The clean interface and simple yet sophisticated design of the iPhone made it a winner in the smart phone market.

Dr. Norman describes our reaction to the design of things into three levels; visceral, behavioral and reflective. We process the things we see and use first on a visceral level, how it looks and feels. We then explore how the item behaves or does what it was designed to do. Then we reflect on our past experiences with the item.

The book builds upon these three principals of design with many examples and stories. There is a very interesting discussion about emotional machines, think the Turing Test. This translates into the future of robots as well, think of your favorite movie robot. The design of robots that are truly useful will have many human characteristics to make interacting with them more natural.

The bottom line: This book provides a good perspective on how our designs affect the users. By providing a functional device or application that is also appealing on a variety of levels we will be more successful. I always love to hear from our customers about how easy our software is to use and how great their experience was with our products.

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.