Home arrow MySQL arrow Page 4 - Using MyODBC to Connect to a Remote Database
MYSQL

Using MyODBC to Connect to a Remote Database


Using MySQL's support for MyODBC, you can connect to remote databases, import and export tables to and from your MySQL database and more. In this article Steve shows us exactly how to get started...

Author Info:
By: Steve Knoblock
Rating: 5 stars5 stars5 stars5 stars5 stars / 38
January 02, 2003
TABLE OF CONTENTS:
  1. · Using MyODBC to Connect to a Remote Database
  2. · Why Connect Using MyODBC?
  3. · Installing MyDOBC
  4. · MyODBC Setup
  5. · Exporting a Table to the Remote Database (contd.)
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Using MyODBC to Connect to a Remote Database - MyODBC Setup
(Page 4 of 6 )



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()."



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.



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.



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



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.



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.



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.



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.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials