Using MyODBC to Connect to
a Remote Database
One of the impressive features of MySQL is its full support for ODBC (Open DataBase Connectivity). The source for the ODBC driver and interface are available for those interested in furthering open source development or customizing ODBC access. All ODBC 2.5 functions and many others are supported. You may use Microsoft Access to connect to your MySQL server, which this tutorial is based on. MyODBC is the ODBC driver for the MySQL database server produced by TCX Data-Consult in Sweden. MySQL provides support for ODBC by means of the MyODBC program.
Why Connect Using MyODBC?
With MyODBC you may:
- Connect to a remote database server from anywhere you have access to a desktop application, such as Microsoft Access.
- Export a database to the remote server.
- Import a database from the remote server.
- Link a local database to a remote database.
It's useful to export a database when you are first populating a remote or online database. If you have an existing database that you want to put online, exporting it from Access to MySQL through an ODBC connection greatly simplifies the process. It allows even novices to get their database to the online server without requiring any knowledge of Unix shell commands or utility applications. It's important to note that exporting is a static process transferring a table from the local to the remote server once each time. There is usually no feedback beyond a progress indicator. When you export a table, SQL statements are sent to the remote SQL server to create the table (CREATE TABLE) and to insert the data being transferred (INSERT). If the table has already been created, a CREATE TABLE statement is not issued and the new information is transferred using (UPDATE).
Importing a table from a remote database can be very useful when you want to create a report from information stored in an online database server. By importing a table into Access, you can generate charts or reports from data gathered online easily. It's important to note that importing is also a static process transferring a table from the remote to the local server once each time. There is usually no feedback beyond a progress indicator.
When you link a local table to a remote table, you allow desktop users to make changes to the remote table through the graphical user interface of Access. For example, if a table row is added or modified through an Access form attached to a particular table linked to the remote table, the remote table will be updated to include the changes. This can help novices to maintain online database information in an easy to use and familiar environment. One of the benefits of linking tables is that any changes made by the user to the local database are also made to the remote database. This makes the whole processing of coordinate remote and local databases more visible.
All these tasks can be accomplished through use of the MySQL command line monitor from the Unix shell, but only for the initiated. Sometimes experts may be more comfortable working from the command line, issuing SQL queries to affect the remote database but for many people (clients, office workers) ODBC is the easy way to get their data to the remote server or produce nicely formatted charts and reports from online data. MyODBC is an important item in the developer's toolkit. It allows you to offer clients an easy and flexible interface to their online database through an application and platform they are familiar with. For the web applications developer, allowing users to update their online database from the desktop can be a time-saver. Data entry and database management may be accomplished from the desktop, while a PHP script generates pages dynamically based on the content stored in database tables.
Note: Microsoft Access is not available for the Macintosh. However, it is possible to exchange or share data in the following ways:
- You may save the Access data from tables, queries, and all or selected portions of datasheets to file formats that can be opened by Office 98 for the Macintosh applications.
- Share data from a network server using Microsoft FoxPro for Macintosh users and Microsoft Access for PC users.
- Share data using ODBC drivers.
|Pages: 1 2 3 4 5||Next: Getting Started With MyODBC »|