Friday, May 30, 2008

TechEd for Developers

TechEd_MeetMeThere_dev_120x90 I will be attending TechEd Developer next week in Orlando Florida. I will be in the Sybase iAnywhere booth Tuesday through Friday. I will be discussing using Advantage with Visual Studio and Visual FoxPro. If you are attending TechEd please drop by I would love to talk to you about Advantage or any other programming topics.

We will be featuring the following products during the show:

Come see us in Booth #1044, hope to see you there.

Wednesday, May 28, 2008

Advantage and Virtual Machines

Yes, Advantage works in a virtual machine. In fact I did all my Advantage beta testing using VirtualPC. This allowed me to do tests on the latest builds while still having the latest release on my actual machine. However, just because you can do something should you? Let's examine some of the requirements for virtualization.

One of the major requirements for virtual machines is processor power. Many systems can run even the most demanding services and still only utilize 15% of the total processing power. Most applications are not designed to take advantage of multiple processor systems see Jeff Atwood's post Choosing Dual or Quad Core. Virtualization software uses these under used cores and unused CPU clock cycles to run additional machines on the same hardware. So having those nice quad core processors becomes very handy for virtualization.

Even though you may have plenty of processing power virtual machines require lots of memory. You have to have enough to run your host OS and each of the Virtual Machines. Remember, a 32bit OS can only address 4GB of ram some of which will not be available, (Ian Griffiths breaks down the details here) my system reports 3454MB. So for a system running several VMs you should consider using a 64bit OS.

Next you need to choose your software. Microsoft and VMWare are the leading companies producing virtual machine software. There are consumer solutions such as VMWare Workstation or Microsoft VirtualPC2007 both of which are free. These products work great for testing purposes and I have used both when re-creating customer issues, beta testing and keeping development environments separate.

If you are going to use virtual machines in a production environment you should move up to a server based product such as VMWare Server or Microsoft VirtualServer. These products are more robust and designed to run more efficiently and provide network services.

Assuming that you have the proper hardware performance becomes the next big question. Just how does a VM compare to a physical machine. In many cases the performance nearly equals that of a physical machine.VMWare has an excellent blog dedicated to performance. It has information, charts, graphs and links to many tests that have been conducted using their products. I could not find quite as much information about Microsoft Virtual Server although there are many postings about improving performance.

As I mentioned before I love VMs for testing purposes. It is wonderful to have a "sandbox" that you can do all kinds of experiments and simply clean it all up and start all over again. I don't generally recommend using VMs in production environments because they are just not as robust as a physical machine.

Having said that there are times when it makes sense to use them. A Web hosting company can provide "machines" for many customers which are in fact virtual machines running on shared hardware. In this case it provides a greater level of security since each customer's environment is separate making it more secure. This also saves the hosting company a lot of money on hardware while maximizing the use of the hardware they do purchase.

Virtualization has come a long way and it is quite mature, it has been available since the early 1990s. As hardware continues to get faster and cheaper it will be difficult for software to utilize the full potential of the platform. Virtualization allows for efficient use of hardware resources potentially eliminating the need for multiple servers. As with most software your results may vary and nothing is a good substitute for a good test lab to work out any kinks before you go live.

Tuesday, May 27, 2008

Using Advantage Online Backup from Visual Studio

With the release of Advantage 8.0 a new Online (hot) Backup feature was added. This functionality allows you to create a backup of your data while the data is in use. It creates a snapshot of the data and copies it to the specified location. You can get a detailed overview of Online Backup in the Advantage Help File.

Backups can be performed on free tables as well as data dictionaries. To backup a data dictionary you must have the proper permissions. For Advantage 8.x you must connect as ADSSYS, Advantage 9.0 includes new database roles which allow you to grant backup privileges to other users. Online backups are only available when using the Advantage Database (Remote) Server not the Advantage Local Server.

There are several ways to perform an Online Backup including; Advantage Data Architect (ARC), command line tool and a system procedure. ARC provides a graphical interface for creating a backup of your data. It is accessed by right-clicking on a connection in the repository and choosing backup from the context menu. The ARC backup utility also generates the proper SQL for calling the backup system procedure and a command line syntax for the adsbackup utility. The adsbackup utility is a command line tool for creating backups. It can be added to the Windows Scheduler to provide backups on a scheduled basis.

To perform backups programmatically you must use the sp_BackupDatabase or sp_BackupFreeTables system procedures. You must connect to the server using the ADSSYS account (version 8.x) or a member of the DB:Backup group (version 9). Then execute the stored procedure using an EXECUTE PROCEDURE SQL command. The procedure will return a resultset containing any errors and warnings from the backup. If an empty resultset is returned then no errors occurred during the backup.

There is a simple Visual Studio project available on Code Central which demonstrates how to call these procedures and view the results. The example allows the user to select a data and backup path. There is an Advanced tab which allows them to specify additional options. A screenshot of the main form is below.

Application Screen Shot

The code for setting up the syntax for sp_BackupDatabase or sp_BackupFreeTables is very straight forward. To get the results of the backup you can use either a DataReader or a DataAdapter. I choose to use a DataAdapter since it can be used with the DataGridView control to display the results. The DataReader is forward only and would involve writing more code to display the results. Remember to set the command timeout to 0 (no timeout) prior to executing the command otherwise the operation will timeout in 30 seconds. Examples of the command text and execution of the command are below.

   1: // Setup the command based on the table type
   2: if (rbDataDictionary.Checked)
   3: {
   4:     cmAds.CommandText = "EXECUTE PROCEDURE sp_BackupDatabase('" +
   5:                         txtBackupPath.Text + "', '" +
   6:                         sOptions + "')";
   7: }
   8: else
   9: {
  10:     cmAds.CommandText = "EXECUTE PROCEDURE sp_BackupFreeTables('" +
  11:                         txtDataPath.Text + "', '" + 
  12:                         txtSourceMask.Text + "', '" +
  13:                         txtBackupPath.Text + "', '" +
  14:                         sOptions + "', '" +
  15:                         txtPassword.Text + "')";
  16: }
   1: // Run the backup command
   2: try
   3: {
   4:     daAds = new AdsDataAdapter(cmAds);
   5:  
   6:     // Put the results of the backup into a dataset
   7:     dsResults = new DataSet();
   8:     daAds.Fill(dsResults);
   9: }
  10: catch (AdsException aex)
  11: {
  12:     MessageBox.Show("Error: " + aex.Message);
  13:     lblStatus.Text = "Advantage Error";
  14:     ShowButtons(true);
  15:     return;
  16: }

Finally we will check to see if there are any rows were returned by the system procedure. If there are then we will give the user an option to view the results.

   1: // If the dataset contains rows an error has occurred during backup
   2: if (dsResults.Tables[0].Rows.Count > 0)
   3: {
   4:     lblStatus.Text = "Backup completed with errors";
   5:     btnViewErrors.Visible = true;
   6: }
   7: else
   8:     lblStatus.Text = "Backup completed successfully";

The example application also provides restore functionality and can be downloaded from Code Central on the Advantage DevZone. The project is called "Backup Example" and can be found under tools Tools or C#/VB.NET categories.

Friday, May 23, 2008

Exporting to XML

This months tech-tip is about exporting Advantage data to an XML file. It describes an Advantage Extended Procedure (AEP) which will export the table to a specified file. Fortunately both Visual Studio and Delphi have libraries which provide this functionality.

The example AEP only contains one function called TableToXML. I want to expand on the example a bit and add a second function called StmtToXML which will accept a generic SQL statement and export the resultset. The mechanism is the same since we will once again use the DataSet (VS) to write the XML file.

Since we are allowing the user to input their own SQL statement we must consider the possibility of an SQL Injection Attack. This is where someone sends in a string which may run multiple statements. To thwart this possibility we simply have to check for a ";" and reject any statement which includes one.

   1: // Check for SQL injection
   2: if (sStatement.Contains(";"))
   3: {
   4:     IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();
   5:     oErrCommand.CommandText = "INSERT INTO __error VALUES( 1001, 
   6:                               'SQL scripts are invalid for this function' )";
   7:     oErrCommand.ExecuteNonQuery();
   8: }

Next we need to make sure that the SQL statement will return rows. Therefore only a SELECT or an EXECUTE PROCEDURE statement will be allowed. Data Manipulation Language (DML) statements such as INSERT, UPDATE, DELETE should be rejected by the AEP. So our code will do a simple check of the statement for the proper syntax. If the statement begins with SELECT or EXECUTE PROCEDURE then run the statement and export the results. Otherwise return an error.

   1: // Ensure the SQL statement is a SELECT or EXECUTE PROCEDURE statement
   2: if (sStatement.Substring(0, 6).ToUpper() == "SELECT" ||
   3:     sStatement.Substring(0, 17).ToUpper() == "EXECUTE PROCEDURE")
   4: {
   5:     // Run specified SQL statement
   6:     oAdapter = new Advantage.Data.Provider.AdsDataAdapter(sStatement, oStateInfo.DataConn);
   7:     oAdapter.Fill(oDataSet);
   8:  
   9:     // Export the result to XML
  10:     oDataSet.WriteXml(sDestination.Trim() + "\\" + sFileName.Trim() + ".xml", XmlWriteMode.WriteSchema);
  11: }
  12: else
  13: {
  14:     IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();
  15:     oErrCommand.CommandText = "INSERT INTO __error VALUES( 1002, 'SQL statements must start with SELECT or EXECUTE PROCEDURE' )";
  16:     oErrCommand.ExecuteNonQuery();
  17: }

Now that the code is finished the Stored Procedure needs to be added to the dictionary. StmtToXML takes three input paramters; statement, filepath and XMLfilename. A screenshot of the Stored Procedure configuration is below.

StmtToXML configuration

The last step is to test our new Stored Procedure. Use an EXECUTE PROCEDURE call to invoke the new procedure.

EXECUTE PROCEDURE sp_StmtToXML( 'SELECT * FROM Table', 'C:\XML', 'Test1' )

The Visual Studio Source code for this Stored Procedure can be downloaded from the Advantage DevZone in Code Central. The project is named "Exporting  Advantage Data as XML" and is filed under C#/VB.NET Stored Procedures. Version 1.1 includes updated Visual Studio code, the Delphi sample has not been updated.

Wednesday, May 21, 2008

Advantage TDataSet Version Switcher

The Advantage 9.0 TDataSet Descendant ships with a new utility called the Advantage TDataSet Version Switcher, referred to here as Switcher. This utility makes it easier to manage multiple versions of the Advantage TDataSet. This allows developers to maintain older versions of code along with newer versions on the same development machine.

The Switcher detects the version of the TDataSet you have installed and gives you a list of versions to switch to. By checking the Back Up Existing BPL Files a backup of all the relevant files will be backed up to C:\Documents and Settings\All Users\Application Data\Advantage\TDataSet_Backup\Version x.xx\ (WinXP) or C:\ProgramData\Advantage\TDataSet_Backup\ (Vista)

switcher

Switching versions is as simple as simple as choosing the version you want to switch to and clicking the Switch button. The current version is automatically detected along with your version of Delphi.

You can add custom configurations if you want to keep incremental builds of the TDataSet installed. For example you could create a custom version for 8.1.0.18 and 8.1.0.26. Keep in mind that releases will always be put in their own directory however, service updates will overwrite current files. If you have switched the version in the past with the backup option the previous versions will still be available in the backup directory. Otherwise you will have to have specific directories if you wish to have more than one 8.1.x version installed on your system.

The Switch utility is installed with the Advantage Delphi TDataSet components (Delphi 3-7) and the Advantage Delphi Components (Delphi 2006 and newer). It is installed in C:\Program Files\Advantage x.x\TDataSet\Switch by default.

Monday, May 19, 2008

Book Review: Don't Make Me Think

Don't Make Me ThinkDon't Make Me Think by Steve Krug is one of the most popular Web design books ever written. It is a very good book which discusses the elements of a good Web site. It stresses the need for usability testing and provides the information in a concise and entertaining fashion.

The book is relatively short (197 pages) by design. Many of the chapters are only a few pages and I was able to read it in a couple of sittings. The material is presented using real world examples and a bit of humor. The illustrations and cartoons are very amusing and reinforce his point exceptionally well.

The major principal is of course "Don't Make Me Think", in other words your Web site should be intuitive enough that users will be able to navigate around with very little thought. Buttons and links should be noticeable, navigation should be easy to use, road maps and road signs should be prominent. Essentially the user should be able to get to the information they need with little effort and always be able to get back if they go in the wrong direction.

He dedicates almost half the book (72 of 197 pages) to navigation and the home page. Navigation is critical because you want your users to be able to find what they are looking for and even things they didn't know they were looking for. Navigation should exist on every page and remain consistent throughout your site. The homepage is the most important page on your site since almost everyone will arrive there and make a decision as to how long they will stay. The most important function of the homepage is to show the users what the Web site, and your company, does.

Steve Krug is a big believer in usability testing a point Joel Spolsky also emphasized in his User Interface Design book. User testing should be done early and often he even gives many tips on making it more affordable. Usability testing is excellent feedback for the design team since it is difficult to develop personas for Web users. Unlike applications which target a specific audience or task the Web is much more fluid. Everyone uses the Web in different ways making it much more difficult to please everyone.

One of my favorite parts of the book was the end of chapter 9 where he describes what to do with the test results. It may seem obvious but he gives a very complete outline of how to "triage" the results and get the most benefit from the tests.

Overall, I found the book very interesting and would benefit any developer. Although the book is focused on Web design many of the principals can be applied to application development. Of course I am sure I will spend the next few weeks analyzing my favorite Web sites.

Friday, May 16, 2008

Avoiding SQL Injection Attacks

SQL Injection exploits a security vulnerability which can exist in the database access layer. This can occur when user input is not checked for an escape character or is not strongly typed.

The best way to prevent SQL Injection is through the use of parameterized queries. Parameters are a place holder in the SQL statement which are populated with a value. This value is then used in the comparison. Consider the following:

sStmt = "SELECT * FROM Members WHERE userid = '" + sUserID + "'";

The value of the sUserID variable will become part of the SQL statement. A user could potentially use a string such as "Mike' OR  'a' = 'a". This would generate the following statement:

SELECT * FROM Members WHERE userid = 'Mike' OR 'a' = 'a'

This query would return all the user information since 'a' = 'a' will always evaluate to true. Additional SQL statements could also be inserted in a similar fashion. For example the user could enter "Mike';DROP TABLE Members;" resulting in the following statement:

SELECT * FROM Members WHERE userid = 'Mike';DROP TABLE Members;

Using a parameter ensures that the value provided will be used for comparison. As opposed to being sent to the server to be processed as an SQL command. Replacing the above examples with the following statement removes the injection threat.

SELECT * FROM Members WHERE userid = :username

Any input with additional SQL commands would be processed as a string to compare to the userids in the table most likely resulting in no match. However, parameters can not be used with every SQL statement. They can only be used in the where clause so other measures must be taken if the user must input a table name for example.

An easy way to check for an injection attack in this case is to search the string for a ";" in the body of the statement. With Visual Studio this can be done using the Contains function of your string variable (see below).

   1: // Check for SQL injection
   2: if (sStatement.Contains(";"))
   3: {
   4:     IDbCommand oErrCommand = oStateInfo.DataConn.CreateCommand();
   5:     oErrCommand.CommandText = "INSERT INTO __error VALUES( 1001, 'SQL scripts are invlid for this function' )";
   6:     oErrCommand.ExecuteNonQuery();
   7: }

Other options include providing appropriate values from a pick list to prevent injection. You can also limit the length of the value or strongly type the value. If you need a dollar amount for example use a double instead of a string variable. In the following example dSalesGoal is a double and will cause an error if a string value is used.

sStmt = "SELECT Name, TotalSales FROM Employee WHERE TotalSales > " + dSalesGoal;

Although SQL Injection attacks are rare it is a good idea to put in measures to prevent them. Especially in web based applications.

Wednesday, May 14, 2008

Working with Date/Time Values

Referencing dates in SQL statements can sometimes be problematic especially when your application is used in many countries. Here in the US we use the MM/DD/YYYY format most of the time. The UK uses DD/MM/YYYY which can cause your SQL statements to fail if you are expecting the US format.

The easiest way around this problem is to use the ANSI date format in your SQL statements (YYYY-MM-DD). Advantage will always respect this format regardless of the format configured on the machine. Advantage will accept the date format defined on the machine and the ANSI format by default.

The date format Advantage uses can be changed using the AdsSetDateFormat which takes a string value for the format. The string must contain two or more occurrences of the letters D (day), M (month) and Y (year). Thus the date could be set with any of the following strings

  • DD/MM/YYYY
  • MM/DD/YY
  • DD.MM.YY
  • YYYY-MM-DD

Having the date format correct avoids many problems but there are times when you want to get the current date or manipulate the date. Fortunately Advantage has many Date/Time Functions built-in to the SQL Engine. Some of the commonly used ones are below

  • CURDATE() or CURRENT_DATE() returns the current date
  • CURTIME() or CURRENT_TIME([precision]) returns the current time
  • CURRENT_TIMESTAMP([precision]) returns the current date and time
  • TIMESTAMPADD(interval, int, timestamp) increments the timestamp by the specified value for the given interval (i.e. Months, Days, Hours, Seconds)
  • TIMESTAMPDIFF(interval, timestamp1, timestamp2) returns the difference between two timestamp in the specified interval

As a final note with the release of version 8.0 a new field type ModTime was added for ADT tables. This is a timestamp value which is automatically updated whenever a record is updated. It is very useful when auditing changes or as a quick lookup for recently changed items.

Monday, May 12, 2008

Improving Concurrency

Database scalability and performance is a concern of almost every database developer. This becomes increasingly important as your application gets used by more an more concurrent users.

It is important to note that scalability is not always linear (see Glenn Paulley's article "The myth of linear scalability"). How an operation is performed can be just as important as how fast your network and server are. For example using a table to store primary keys is commonly used but can cause scalability issues.

Lets examine a typical implementation of this concept. The user needs to get the value they will use as their key, then increment the value so the next user gets a unique value. To ensure that the next value is unique only one user can be reading the value at any given time so the following sequence is followed.

  1. Find the proper record
  2. Lock the record
  3. Read the record
  4. Increment the record
  5. Return the result to the client
  6. Unlock the record

This sequence requires 6 calls between the client and server and is normally executed very quickly. As the number of users increases, however, several clients may be requesting a lock on the same record. These lock requests will get processed by the server which can delay the client who owns the lock from unlocking the record. Essentially the server is busy telling all the other users the status of the record while the user who has the record locked waits to unlock it.

This type of situation may not occur for a small number of users since each request is processed quickly. As an example let's say the database server serializes all requests and processes them in turn. Each request takes 10ms to complete. This would make our primary key operation take 60ms to complete (6 requests at 10ms each). If 20 additional users issue lock requests immediately after the lock was granted to one user an additional 200ms would pass before the first user could perform the next step. If the clients issued lock retries in a tight loop this would add even more time to the procedure.

Of course, in the real world these simple operations can take far less time and the server is capable of processing multiple requests simultaneously. However, the fact remains that many calls to the same record can delay further operations on that record. This can be reduced significantly by moving the operation into a stored procedure. Since the stored procedure is run entirely on the server, thus a single client request, the lock owner holds onto a worker thread until the entire operation is completed. The thread does not have to wait while other clients are trying to lock the same record it can continue on to the next step. Thus there is no delay in completing the process and returning the result to the client.

You will still have to have some type of mechanism within your stored procedure to retry if the lock fails. However, the time it takes to obtain the key value from the table should remain relatively constant regardless of how many users are attempting the same operation at a time.

Monday, May 5, 2008

Book Review: User Interface Design for Programmers

User Interface Design for Programmers[2]I read Joe Spolsky's book "User Interface Design for Programmers" over the weekend. It is an excellent book for programmers by a programmer about making your programs more useable. I think it is a must read for all developers. The writing style is very entertaining and easy to read.

Joel works on his central axiom for user interface design "A user interface is well designed when the program behaves exactly how the user thought it would". No small task to be sure. As software developers we tend to design our applications to work the way we do not realizing that we are not a typical end-user.

There are several ways to help us design a better UI. The first is the idea of "personas" which are fictional users. I first read about personas in Alan Coopers book "The inmates are running the asylum" which is an excellent book describing the need for good UI design. By using a persona when designing the application we focus more in on how the application will be used which ultimately makes the software more usable.

Another very useful tool is the use of usability testing. Joel has an entire chapter which discusses usability testing and how it can be best used. I found two points, in particular, to be very useful. First usability tests do not have to involve a lot of people. In fact, most usability problems can be discovered by a group of about 6 testers. Second, it is very beneficial for software engineers to actually watch a typical user use their software. What seems obvious to you as the developer may not always be obvious to a secretary, accountant or sales person.

The book is less than 200 pages and I found it very easy to read. Joel uses many examples from programs and operating systems from the early 2000s which I found very familiar. He has a great sense of humor and a very self-deprecating style which I enjoyed. I found the information very useful and I hope to incorporate his ideas into future projects.