Going Cross-Platform Again: Using Visual FoxPro to Connect ...



Going Cross-Platform Again: Using Visual FoxPro to Connect to a MySQL Database on Linux

Bob Lee

Remember when one of FoxPro's claims to fame was "cross-platform capability"? You could write an application and, with a bit of forethought and planning, make that single code base run on DOS, Windows, Unix, and Mac platforms. Then some marketing genius subverted the term "cross-platform" to mean Windows 3.1, Windows 95, and Windows NT, and the capability of Fox to run on other platforms gradually disappeared. But happy days are here again. In this article, Bob Lee shows you how to use Visual FoxPro desktop applications to access data residing on another platform, specifically addressing the issues involved in connecting to a MySQL database running on a Linux server.

I have to come clean. I initially wrote this article to show VFP developers that creating a client/server application doesn't need to be a daunting task anymore. There are still perceived roadblocks of technology, time and learning curves, and substantial software costs—but these can all be overcome. I wanted to move from a pure desktop VFP application into client/server systems, but I kept running into roadblocks. The online Help took me deep into the quagmire of possibilities and unfamiliar syntax, none of which made any sense and would take hours of testing to see what the right fit was for power and simplicity to use. Microsoft SQL Server, while less expensive than Oracle, is still costly; and its little brother, MSDE, with its five-user maximum, has been crippled so that it's unusable for anything but learning practice and trivial applications.

But this article has turned out to be a good tutorial for learning how to connect to non-Windows-based databases as well. I've managed to kill two birds with one stone.

Why MySQL?

When I started to look into taking the next step from a VFP Web application to a true client/server desktop application, I was noticing MySQL over and over again. It seemed to be everywhere. Other databases were available, and I assume they'd work equally well, but the sheer number of installations of MySQL at ISPs and ASPs impressed me. Good documentation, many after-market books, and a healthy following of dedicated users eager to help were other factors that pushed me toward MySQL. Like anything, MySQL isn't the right solution for every database application or VFP-connected application, but it's a worthy contender. Before you spend hours with it, you should be aware of MySQL's limitations. For example, subqueries and stored procedures aren't yet supported on the MySQL server.

Background to MySQL and Linux

Linux servers are omnipresent, no matter how much Microsoft would like it to be otherwise. If you haven't come across a Linux server yet, well, where've you been hiding? The largest mainframe computer companies are all warming up to Linux. IBM, HP, Silicon Graphics, and Sun have all embraced Linux in a big way. The databases that reside on these large servers are the Big Guns of the database market: Oracle and DB2. Many of those databases are today still on Unix, but, in time, many will be migrated to Linux.

MySQL () is the world's most widely used open source database, and it's a standard package on most Linux distributions (much like Notepad is to Windows). It's available for free under the GNU General Public License (GPL). Unlike MSDE, Microsoft's "free" version of SQL Server, there's no user limit on how many users can be connected to a server (the number of users is a configuration option, and the default value is 100).

MySQL isn't the largest or the best or the least expensive or most expensive, but it does claim to be the fastest database server. The ability to make such a claim comes from the fact that MySQL is a lightweight server without some of the advanced features of the larger fault-tolerant databases. (Check out rmation/benchmarks.html for more information on MySQL benchmarks.)

It's not just a toy database, either. A recent article on the MySQL Web site noted that the U.S. Census Bureau creates and manages Web sites that serve as resources for national and local statistics for the Census, and most of these sites use the MySQL database server.

Up until recently, you could go to and sign up for a free database server of your own. The beauty of this site is that they'll host a MySQL database server for you for free. Currently, they have 10,404 active MySQL databases up and running. As of this writing, they're temporarily holding off on new accounts, but they're expecting to be back, accepting new accounts soon.

What this means to you, the VFP developer, is...

You can tap into the power of a SQL database using VFP as the front end and create client/server applications virtually for free. The SQL syntax is a standard with ANSI 92. Almost every database out there has ODBC drivers available so that VFP can connect with it.

Our clients expect us to be able to supply them with a client/server option—no, correction—they demand it. Our clients increasingly seem to expect that all applications should be able to be used from home, office, and hotel room without changes to the code. Distributed database applications, once considered to be a hallmark of enterprise-level applications, are now available for you to develop.

Getting started

Okay, it's time to get started. How about a big-picture view first? Your VFP application will send a SQL statement to MySQL via an ODBC driver, and that gets carried over your network (whether that network is a local area network or the Internet doesn't really matter) via TCP/IP to the machine running MySQL. The MySQL database processes the SQL statement and returns the results to VFP as a read-write cursor. You manipulate that cursor, and then commit your changes back to the server.

Let's look at each step individually.

Access to MySQL

The first thing you'll need is access to a MySQL database. I'm going to assume that you've got access to a Linux server running MySQL with a known IP address. Unlike SQL Server, where you have to depend on the operating system to know where the database is (how many of you have had a SQL Server "disappear" from the combo box without warning?), you just use the IP address of the Linux box to identify the MySQL server.

ODBC connections

The next thing you'll need is a means to connect VFP to MySQL. This is done through the MySQL ODBC driver. ODBC is a set of protocols that all database servers have mutually agreed upon and provide a common interface. Microsoft is now moving away from ODBC to a new interface called OLEDB, but the most mature databases in the market haven't converted to OLEDB, so stick with the tried-and-true ODBC for the time being. You can download the latest version from the MySQL Web site for free.

A read-write cursor

Yes, a read-write cursor! When you finish making changes to the data, you post the changes back to the server using standard SQL all the time. You're working with something you know and understand—the VFP cursor. SKIP and REPLACE to your heart's content on your read-write cursor. At the end, write one line of code to post your changes to the server. Done. That's it.

Reports are just as simple. Get a cursor with the data you're interested in. REPORT FORM YOUR REPORT TO PRINT. Close the cursor. That's it. Adding a record is just as simple. Get a blank record cursor, do your REPLACE statements on the cursor, and then insert your new records to the server.

Once you have it in mind that you're always working with cursors (and not the table itself, or a VFP view), remembering to put back your changes becomes as simple as a SQL version of tableupdate(.t.).

Pointers

In actuality, you do have to write your own wrappers for the connection statements (or use a commercial framework). But once you've done so, they become part of your library, and you simply use them over and over.

The second tip is to stay away from Remote Views. While they appear to be easier to set up, you're no longer in control of the actual SQL statements or the VFP buffering when Remote Views are used. Reliability and speed both suffer when you employ Remote Views.

Since SQL Servers don't have the concept of a deleted record, you might want to change the structure of your tables on the server to duplicate the "deleted" functionality of the DBF by adding a field to each table that represents the deleted flag in DBFs. I use a one-char field (like a tiny INT-type field) and fill it with a 1 or a 0 to indicate whether the record is considered deleted(). My sample code uses a field name DLD as the deleted record field for each table.

A Primary Key field is required on all MySQL tables. This is because the concept of a record number isn't native to a SQL table. Make use of the auto_inc feature of MySQL. You can define a field as an INT-type field and make it auto_inc. This much-wanted field type in VFP is native to MySQL and instantly gives you a unique record ID and PK if you need one.

MySQL on Linux is case-sensitive when it comes to table names—because Linux, like other variants of Unix, is case-sensitive. Be aware that when you write your SQL statements you must make sure you match the case for the names of the tables you're attempting to query. If you're running MySQL on Windows, the table names aren't case-sensitive

Don't go overboard with the use of the MySQL permissions functions. While they appear at first reading to be able to do everything that your application could need, I found it's much better to set up only three classes of users. SQL Table permissions can be made rather complex, but details are beyond the scope of this article. Suffice it to say that I'd suggest that you create these three: The first is you or the database administrator, the second is the regular users who can read and write to tables, and the third is the casual user who can only read, but not write. The MySQL permissions don't restrict the types of SQL statements you can issue, and thus you'd need to code separate statements to allow for different groups of users. Thus, most of that security needs to be written into your application. So don't get too hung up on using the permissions.

Since most of us started out with DBF files in a directory, it may be comforting to understand what the file structure of a MySQL database looks like. A MySQL database essentially maps to a disk directory, and the tables are files in that directory. There's one special database called "mysql" that holds the users and permissions to tables for the server that's being used. The structure of MySQL is a server application—on Windows, it's running as a service while in Linux it's called a Daemon process like an HTTP server. The service is usually started on boot-up, runs 24x7, and is only really active and takes resources when a SQL request is sent to it.

There's a command line interface to the server itself, but I for one haven't used it past getting the first user on. Then I prefer using MySQLFront or VFP to do all communication with the server. MySQL is configured like most Linux applications by modifying one text file (f), which is in a similar syntax to a Windows INI file. So, essentially, you start it up, let it run, and manage it with MySQLFront.

Making the connection

Within VFP, you have many choices on how to make your connection to MySQL, but in actuality, they all do the same thing. They all send a statement to the MySQL server, which has in it the IP address or name of the server, the username password port number, and other options that you specify.

Keeping that information in the DSN/ODBC data source record is one way that a connection can be done, but that method requires that the end user go through a lengthy setup script to configure the DNS. All together now: Blech! The next option VFP gives us is using your DBC to hold that information in something called a remote connection. You could do that if you liked, but, since I like the idea of not using local data stores of any sort, the idea of using a DBC to hold this sort of metadata seemed to me somewhat needless.

So then comes my favorite method, called SQLstringconnect(). This function sends all the necessary information to enable you to create a connection to a server. The connection is considered to be valid if the response to this function is anything but a -1—you get an integer number returned to you with a value > 0, and that value is your connection handle. In your programming, you hold onto that connection handle number just as long as you need to, and then you issue the command SQLdisconnect() passing in the connection handle as a parameter to drop your connection. I suggest that you only hold onto the connection long enough to get the result you're looking for.

Here's what the connection string might look like. I store this information in an INI file with a filename like DNS.INI and read it in using strtofile() whenever I need it. You might find that you prefer to use a DBF that holds a local meta database for your users, or perhaps an MEM file or something in XML. Any way you get it, you do need to have access to the connection string.

Here's a sample of my connection string, broken onto multiple lines so it displays correctly for this article:

DRIVER={MySql Server}

SERVER=216.XXX.XXX.13X;

PORT=3306;

UID=bob;

PWD=XXXXXX;

DATABASE=mydatabase;

option=131609

And here's what all the pieces mean:

DRIVER={MySql Server}

has to be surrounded by curly brackets because it has a space embedded in the name.

SERVER=216.XXX.XXX.13X;

This identifies the location of the MySQL server machine. The syntax can be a name instead of an IP address, but doing so is a little slower with a name because every time it has to communicate with the server, it has to do a name resolution.

PORT=3306;

The port is optional. It's usually set up by the server at port 3306. My server is running MySQL on port 1433, so leave it out of your connection string if you're communicating with a normal MySQL server.

UID=bob; PWD=XXXXXX;

These should be self-explanatory.

DATABASE=mydatabase;

It's a good idea to pass in the DATABASE name—that way, the default database is set up, and you don't have to specify on each SQL statement you make what database you're communicating with. This setting is optional.

option=131609

This option parameter number is a value consisting of multiple binary flags, much like the parameters for Visual FoxPro's MessageBox function. More specifically, it's constructed like so: Go to your ODBC control panel, and open up a MySQL DSN as shown in Figure 1. The options I'm referring to are the ones at the bottom (a bunch of check boxes). They're numbered top to bottom starting with the first column. The first option is 1, the second is 2, the third is 4, then 8, 16, 32... To set multiple options, you add their values together and pass that number. So if you want options 1, 2, and 3, you'd pass 7 (1 + 2 + 4).

[pic]

Figure 1. The MySQL ODBC driver configuration.

The options I selected, to add up to 131,609, include "Pad char to full length," "Don't optimize column width," and "Safety (check this if you have problems)"—without padding char to full length, the trim (value) of the data is returned from the server. That looks like it's a really big number, but it's simply five options that equate to the binary values of the first, fourth, fifth, tenth, and eighteenth options.

MySQL data transformation functions

As I alluded to earlier, you'll need a series of functions that allow VFP to talk with MySQL. Some of these functions convert VFP data to MySQL datatypes, while others are wrappers for typical database operations like Save and Add. I put each of these functions into a single procedure file; you could of course put them in a class library as well.

Table 1 shows a listing of the functions I've written.

Table 1. MySQL functions.

|Function |Description |

|MySQL |Wrapper to get a VFP cursor from a MySQL table (but doesn't disconnect). |

|MySQLP |Wrapper to get a VFP cursor from a MySQL table (and disconnects). |

|DtoSQL |Converts VFP data to standard data that's understood by MySQL. |

|FixSQL |Called from FixSQL. |

|MySave |Saves data from a cursor to the MySQL table. It builds the SQL statement on the fly based on the structure of |

| |the current cursor, and sends to the MySQL server a SQL command—to save any changes made to the cursor. |

|MyBlank |Creates an empty one-record VFP cursor, ready to for you to populate with data. |

|MyNew |Appends the currently selected cursor to the matching MySQL table record. This first passes in the MySQL |

| |procedure, stays connected, gets a new PK (auto-increment), updates the record, and then disconnects from the |

| |server. |

|MyError |A basic error-trapping script to write a text file with error information. |

The only difference between MySQL and MySQLP is that MySQL doesn't disconnect. This is used so you can append a record and let the MySQL server create an automatic PK for you in the table.

Rough versions of each of these functions are included in MY.PRG in the Download file for this article.

How to convert your Fox DBF data to a MySQL server

Unless you're going to populate a MySQL database from scratch, you're going to want to move your VFP data into MySQL. There are several tools out on the Web, such as DBF2MySQL and DBF2SQL, that are written to handle this task for you. Some are written as PRGs, while others are stand-alone EXEs. These are freeware projects in various stages of development and/or maintenance—do searches on "dbf2mysql" or "dbf2sql" on the Web for the latest versions. You can also use my program, DBF2SQL.PRG, included in the Download file, to get started.

How to manage your new MySQL database

Once you've got your data into MySQL format, you'll probably never have to make a change, right? I know I never have. Oh, wait, except for that one time when...

So what about MODIFY STRUCTURE? Changing column widths, adding indexes, including more fields—how's all this stuff done? The simple answer (and, yes, it took weeks of testing other applications) is to use MySQLFront, a free utility that graphically handles all those functions as well as gives you a command line to the MySQL server. I use it all the time to first generate my SQL statements and test them on the server directly, and, when the SQL statements get the desired results, I cut and paste them into the VFP code. Figure 2 shows you MySQLFront connected to one of my servers executing a SQL statement and displaying the result set. MySQLFront is available for download at anse.de/mysqlfront.

[pic]Figure 2. The MySQLFront interface allows you to maintain tables just like you can in the VFP IDE.

Conclusion

Put together these concepts, procedures, and functions, and you're ready to take the next logical step into the client/server world. Make use of the Internet and your skills using VFP as a client to MySQL, and then it's a short jump to any other SQL database. You can make great desktop applications served with data from the Internet—and you're able to connect to SQL databases on platforms other than Windows.

MySQL and the MySQL ODBC driver are available now for free—together, you end up with fewer lines of VFP code, faster applications than you'd have programming the same functions using native VFP tables, and, I've found, enhanced features and speed that could only come from working in a true client/server environment.

My love of the Fox over the years is partly due to Fox giving us the ability to distribute free runtimes for our applications. With MySQL, you can develop true, robust client/server applications without having to pay the hefty license fees that SQL Server requires. This solution—cost-free VFP runtimes and license-free MySQL—is a truly cost-effective cross-platform solution for my clients, and possibly yours as well.

Download 03LEESC.ZIP

Bob Lee is the IT manager at Wolf-Gordon. He's written Xbase solutions since 1987 using dBASE III+/dBman on DOS and Xenix/FoxPro. He's created Web-connected database applications since 1998 and MySQL applications since 2001. He's currently working on a replacement for Adobe Acrobat to create PDF files and migrate data and reports from legacy systems to more modern interfaces and electronic document storage. bob@.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download