PHP HelpPHP Help

Online Community that helps beginners learn PHP,
and webmasters solve PHP coding problems

since 1999



Connecting to a Remote MySQL Database with MyODBC

MyODBC Basics

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 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.

Exporting

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

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.

Linking

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.

Getting Started With MyODBC

Downloading MyODBC

The MyODBC download package is available at the MySQL site http://www.mysql.com/ in the Downloads section. Scroll down to the MySQL Related Software and choose Downloads for MyODBC. (On the main site, MyODBC links are available from http://www.mysql.com/download_myodbc.html)

Tip:  for a faster download, try one of the MySQL mirror sites. Choose the mirror that is closest to you (http://www.mysql.com/mirrors.html).

MyODBC is available for the Windows 9x and NT platforms as well as various flavors of Unix (including Linux). The source code for MyODBC is included in the distribution. If you only want to use the MyODBC application, you can safely ignore the various makefiles and C code included with it in the download package. Just install the MyODBC executable program (it's easy, see below).

Caution:  MyODBC is distributed in separate versions for Windows 9x and NT because of a bug in Microsoft ODBC setup that prevents it from detecting the operating system. The current version of MyODBC is 2.5. The latest version of the MyODBC download package for Windows 9x myodbc-2.50.28-win95.zip available at http://www.mysql.com/Downloads/MyODBC/myodbc-2.50.28-win95.zip Make sure you are using the latest version containing all the updates and fixes. MyODBC archive is about 1.5K and downloads in a few mintues at 56k.

Installing MyDOBC

Once you've download MyODBC into a convenient directory on your hard disk, you can open the archive and decompress the files inside. I suggest using WinZip or other application that makes the job of extracting files easy.

You can start WinZip by double-clicking on the file in the Windows Explorer. I keep mine set to the classic version, which makes the Install button readily available. This is a cool feature that will automatically create a temporary directory, unzip the archive, extract the files and start installation by running setup.exe if it finds one. Here's what the screen looks like just before installation.

Installing MyODBC

Don't forget to tell WinZip that the installation is complete so it can close its window and clean up the files.

When you start the installation process, the first window you see will be the Microsoft ODBC Setup dialog. Click Continue.

Microsoft ODBC Setup

At this point, the Install Drivers dialog window should appear. (If you do not reach this point in the installation, something is wrong with the way Windows is setup. Please contact Microsoft support to help resolve this issue.) Select MyODBC and click OK (or just click OK).

Install ODBC Driver for MySQL

If have trouble installing the driver or are merely curious, you will find version related installation options by clicking on the Advanced button. This button brings up the Advanced Installation Options dialog window.

ODBC Advanced Installation Options

Further information about the driver version is available in the Versions dialog, by clicking on the Versions button in the Advanced Installation Options dialog.

ODBC driver version

If the MyODBC driver was successfully installed, the next dialog you see will be the Data Sources dialog. This dialog is part of the Microsoft ODBC system and not a part of MyODBC. You should see the sample data source installed by MyODBC, called sample-MySQL (MySQL) in the data sources driver window. Although you could select the MySQL entry and push the Setup button to bring up the MyODBC setup panel, we will be configuring the remote connection from within MS Access. That is so we can create a data source tailored to our project.

ODBC Data Sources

When you're done, you should see a message telling you that the new ODBC driver has been successfully installed. Click OK.

ODBC driver

Connecting to An Established Remote Database

Once you have installed the driver, we will use Access to connect to the remote database. The database must be setup for remote access. At many hosting providers, this is done only by request. Please ask your administrator or hosting provider support team about activating remote access to your MySQL database. Setting up a Data Source, choosing a ODBC driver and creating a Data Source Name (DSN) for Windows applications to reference your database can be a daunting task. There are many settings involved and many terms which you may be unfamiliar with. But broken down, the steps are relatively easy to follow and fall into place.

Click File | Get External Data | Import. Click New in the Data Source window. The MyODBC driver dialog should appear.

Creating a New Data Source

At this point you need to create a new data source. A data source is where data comes from through a ODBC connection. The Database Source Name (DSN) is the name of the database. When you create the database source, give it any name you like.

Using the Create a New Data Source Wizard

The first step is to create a new Windows data source. This allows Windows applications to connect to your ODBC database. Select User Data Source. Click Next.

Create New Data Source: Select Type

The next screen presents you with a list of available ODBC drivers you can use to create a data source. (You can have as many data sources as you need, each using any ODBC driver available). Select the MySQL driver. It will be identified as to name, version and the developer, TCX. Click Next.

Create New Data Source: Select a Driver

A purely informational screen appears, displaying the data source type and choice of ODBC driver. As the dialog says, the next step will be to optionally configure the driver-specific settings. Click Next.

MyODBC Setup

Create New Data Source: MySQL driver setup

The MyODBC driver settings panel should appear. This panel allows you to specify the Windows DSN name, the remote server settings and other compatibility settings. You may give any value for the Windows DSN that is unique (not already used by another data source) to your ODBC sources list. The default port setting should be correct for most situations. Values for Server, User, Password and Port filed do not have to be specified in the ODBC Setup screen. If do specify them here, the values will be used as the default values later when you attempt to make a connection. The values can optionally be changed at that time. (Note: the MySQL manual says "If you specify the option Read options from C:\my.cnf, the groups client and odbc will be read from the 'C:\my.cnf' file. You can use all options that are usable by mysql_options()."

TDX MySQL Driver default configuration

A quick reference to the settings.

Windows DSN Name of your database used by Windows (this can be anything you like, whatever helps you to remember and recognize the database is fine)
MySQL Host IP address or domain of host database server (obtained from your hosting provider)
MySQL Database Name this is the name of the remote database (obtained from your hosting provider or specified by you when the database was created)
User this is the user name for access to the remote database (obtained from your hosting provider)
Password this is the password used to access the remote database (obtained from your hosting provider; you must have the correct permissions level to do certain operations like creating and dropping tables. Some administrators will give you up to three user id password pairs, one for read only access (SELECT), one for read/write access (SELECT,INSERT,UPDATE), one for administrator level control (CREATE TABLE, DROP TABLE, ALTER TABLE plus all other statements).

Tip:  Tip This tutorial uses Microsoft Access for illustration. To make Access work better with MyODBC, you should consider the following steps.

  • You should have a primary key in the table.
  • You should have a timestamp in all tables you want to be able to update.
  • Only use double float fields. Access fails when comparing with single floats.
  • Set the 'Return matching rows' option field when connecting to MySQL.
  • Access on NT will report BLOB columns as OLE OBJECTS. If you want to have MEMO columns instead, you should change the column to TEXT with ALTER TABLE.
  • Access can't always handle DATE columns properly. If you have a problem with these, change the columns to DATETIME.
  • In some cases, Access may generate illegal SQL queries that MySQL can't understand. You can fix this by selecting "Query | SQL Specific | Pass-Through" from the Access menu.

Once the data source has been created, you are back in the Select Data Source dialog. Select the data source you just created from the list. Click OK. That should create a duplicate table on the remote database with all your data. You won't see anything to confirm the data has been transferred. You need to link the table to the remote database.

Importing and Exporting Tables

Importing a Table From the Remote Database

Open the Access database. On the database window (the one with the tabs), select the tables pane. Go to File, then Get External Data. Choose Import from the menu.

Importing a Table From the Remote Access Database

In the dialog, select the choice "To an External File or Database." The SaveAs dialog will appear. In the Save as type select box, choose ODBC Databases (). Click Import.

Import ODBC database

The Import Objects dialog will appear. If the table you wish to import is listed, select it. Click OK.

ODBC Import Objects

Access will begin importing the table by querying the remote database through the ODBC connection. This connection can be slow for a large table, so have a cup of coffee while Access does its work.

Importing Access Table

Exporting a Table to the Remote Database

Open the Access database. On the database window (the one with the tabs), select the tables pane. Select the table to export. Go to File, then Get External Data.

Exporting a Table to the Remote Access Database

The Export dialog will appear. If the table name is what you want, click OK. The Select Data Source dialog appears. Select the Machine Data Source tab.

Microsoft Access Save As

We will create a new data source just for your this database. See the previous section on creating a new data source. This will make it easy to connect again to the database. Click New. The Create New Data Source appears. Select User Data Source. Click Next. Select the MySQL driver from the list. Click Next. The panel will display your driver info. Click Finish.

Select the MySQL driver from the list

See the previous instructions for setting up the MyODBC driver. The MyODBC driver dialog should appear.

MyODBC export

Once the data source has been created, you are back in the Select Data Source dialog. Select the data source you just created from the list. Click OK. That should create a duplicate table on the remote database with all your data. You won't see anything to confirm the data has been transferred. You need to link the table to the remote database.

exporting ODBC Databases

To link the table, select it in the table pane. Go to File | Get External Data | Link Tables. In the Link dialog, select from the Files of type list ODBC Databases (). The Data Source Dialog will appear. From the Machine Data Source tab, select your alumni database source name again. A series of dialogs will come up. One may ask you what tables you want to link, select the alumni table. Another may ask you what column you want to uniquely identify records, use the record ID column (usually the PRIMARY KEY) if you have one.

Now, a second table will appear in the panel with the same name, but a "world" icon showing that it is remote. You can work with this table like any other, creating a report based on it or a form that you can use to update the remote table.

Caution:  It may be necessary to change some of the column names in the table you are going to export. MySQL reserves certain words for its own use. Most of the SQL keywords are reserved. For example UPDATE is a reserved word, so you can't use it as a column name. Except that function names do not clash with table or column names. For example, ABS is a valid column name. I am not completely sure about this and would appreciate any observations you have.

Select Data Source

Open a blank Access database, go to File | Get External Data | Link Tables | Select from files of type ODBC, select Machine Data Sources tab, select your database name from the list, click OK.

A Link Tables dialog should come up. You should see a list of table names. Select the table you wish to link to. Click OK.

A Select Unique Identifier dialog should come up. You should see a list, select the column that you want to represent the primary key (or columns if more than one column is required to uniquely identify a row). Click OK.

You should see a remote table in the Tables tab (the globe indicates a table linked to a remote data source). Open the table, you should see the data in your remote table.

Caution:  Microsoft Access2000 has bug affecting the export of tables to the remote database. Table names are not sent in the SQL query when a database is exported. If you just go to File | Export | Select ODBC, choose MyODBC an ODBC error will occur. There is a hotfix available for this bug, thanks to the diligence of the MySQL community and responsiveness of Microsoft. The only word I have on this issue is from user group member who communicated with a member of the Microsoft Jet development team. They replied "We have found the problem with Access 2000 and MySQL/MyODBC. The problem is indeed on the Access side of ODBC, not the driver side. The Jet team has made a fix and will release with the next scheduled update. Thank you for bringing this issue to our attention. Jim Sturms. Access Program Manager." At the time of writing a hotfix was available through Microsoft's beta test program. However, linking to existing tables in the database is unaffected. Someone using Access 97/95 can connect to the database and export the data. Once the tables are online, you should be able to link to the table and modify it as needed.

MyODBC Compatibility and Options

ODBC Compatibility

Here are a few notes on making your data compatible with ODBC. They are from the MySQL manual.

The "zero" values are special, but you can store or refer to them explicitly using the values shown in the table. You can also do this using the values '0' or 0, which are easier to write. "Zero" date or time values used through MyODBC are converted automatically to NULL in MyODBC 2.50.12 and above, because ODBC can't handle such values.

MyODBC defines BLOB values as LONGVARBINARY and TEXT values as LONGVARCHAR.

The special date '0000-00-00' can be stored and retrieved as '0000-00-00'. When using a '0000-00-00' date through MyODBC, it will automatically be converted to NULL in MyODBC 2.50.12 and above, because ODBC can't handle this kind of date.

String functions now return VARCHAR rather than CHAR and the column type is now VARCHAR for fields saved as VARCHAR. This should make the MyODBC driver better, but may break some old MySQL clients that don't handle FIELD_TYPE_VARCHAR the same way as FIELD_TYPE_CHAR.

Understanding MyODBC Options

MyODBC allows the user to specify several options affecting the behavior of the ODBC connection. I don't know what all of the options do, but will present those I have used or can find a description of. I will be updating these in the future.

MySQL Driver Configuration

Don't optimize column width.

Return matching rows.

Trace MyODBC. This option activates logging of SQL statements sent to the MySQL server. It is useful when you encounter difficulties connecting to the database. The log is written to the file "myodbc.log" on the C:\ drive. (Note that you must use MYSQL.DLL and not MYSQL2.DLL for this option to work!) Once you have generated a log file, check the queries that MyODBC sends to the MySQL server; You should be able to find the queries by searching for the string ">mysql_real_query" in the "myodbc.log" file.

Allow BIG results. SQL_BIG_RESULT can be used with GROUP BY or DISTINCT to tell the optimizer that the result set will have many rows. In this case, MySQL will directly use disk based temporary tables if needed. MySQL in this case will prefer to do a sort instead doing a temporary table with a key on the GROUP BY elements.

Don't prompt on connect. When initiating an ODBC connection, do not ask the user to manually enter username and password (and possibly other information).

Simulate ODBC 1.0. Behave like older ODBC.

Ignore # in #.table.

Use manager cursors (experimental).

Don't use setlocale.

Pad CHAR to full length.

Return table names in SQLDescribeCol.

Use compressed protocol.

Ignore space after function names.

Don't optimize column width.

Force use of named pipes.

Change BIGINT columns to INT.

No catalog (experimental).

Read options from C:\my.cnf.

Safety (use this when you have problems).

Discuss on Forum   |   More Tutorials »




Copyright © 2014 PHPHelp.com