Writing Applications That Adapt to the MySQL Server Version

9.14.1 Problem

You want to use a given feature that is only available as of a particular version of MySQL.

9.14.2 Solution

Ask the server for its version number. If the server is too old, maybe you can fall back to a workaround for the missing feature, if one exists.

9.14.3 Discussion

If you're writing an application that can perform certain functions only if the MySQL server supports the necessary underlying operations, the server version number allows you to determine whether those operations are available (or whether you need to perform some sort of workaround, assuming there is one).

To get the server version, issue a SELECT VERSION( ) statement. The result is a string that looks something like 3.23.27-gamma. In other words, it returns a string consisting of major, minor, and "teeny" version numbers, and possibly some suffix. The version string can be used as is for presentation purposes if you want to produce a status display for the user. However, for comparisons, it's simpler to work with a numberin particular, a 5-digit number in Mmmtt format, where M, mm, tt are the major, minor, and teeny version numbers. The conversion can be performed by splitting the string at the periods, stripping off from the third piece the suffix that begins with the first non-numeric character, and then joining the pieces.[4]

[4] My first attempt at the conversion algorithm was to break the version string at periods, then to strip the suffix from the third piece beginning with the - character. That algorithm failed with the release of MySQL 3.23.29a-gamma. (Stripping -gamma from 29a-gamma leaves 29a, which is not a number.)

Here's a DBI function that takes a database handle argument and returns a two-element list containing both the string and numeric forms of the server version. The code assumes that the minor and teeny version parts are less than 100 and thus no more than two digits each. That should be a valid assumption, because the source code for MySQL itself uses the same format.

sub get_server_version
my $dbh = shift;
my ($ver_str, $ver_num);
my ($major, $minor, $teeny);

 # fetch result into scalar string
 $ver_str = $dbh->selectrow_array ("SELECT VERSION( )");
 return undef unless defined ($ver_str);
 ($major, $minor, $teeny) = split (/./, $ver_str);
 $teeny =~ s/D*$//; # strip any non-numeric suffix if present
 $ver_num = $major*10000 + $minor*100 + $teeny;
 return ($ver_str, $ver_num);

To get both forms of the version information at once, call the function like this:

my ($ver_str, $ver_num) = get_server_version ($dbh);

To get just one of the values, call it as follows:

my $ver_str = (get_server_version ($dbh))[0]; # string form
my $ver_num = (get_server_version ($dbh))[1]; # numeric form

The following examples demonstrate how to use the numeric version value to check whether the server supports certain features:

my $ver_num = (get_server_version ($dbh))[1];
printf "GET_LOCK( )/RELEASE_LOCK( ): %s
", ($ver_num >= 32127 ? "yes" : "no");
printf "Functional GRANT statement: %s
", ($ver_num >= 32211 ? "yes" : "no");
printf "Temporary tables: %s
", ($ver_num >= 32302 ? "yes" : "no");
printf "Quoted identifiers: %s
", ($ver_num >= 32306 ? "yes" : "no");
printf "UNION statement: %s
", ($ver_num >= 40000 ? "yes" : "no");
printf "Subselects: %s
", ($ver_num >= 40100 ? "yes" : "no");

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References

MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net