ODBC Access Guide: FMYiRC V1.00
From FMYiRCWiki
ODBC Access Guide: Freemed-YiRC V1.00
Contents |
Summary
This document describes how to access the Freemed-YiRC MySQL database outside of the Freemed-YiRC GUI via the ODBC standard for connecting to databases over a network. It is assumed that you have installed Freemed-YiRC using the Helper CD, or you are knowledgable enough about MySQL and networking to ensure that MySQL is using port 3306 for ODBC access, and this port is not blocked in the machines firewall.
Creating a Guest Database User
Before anyone else can connect to your database, you must create a user. By default, only the root user can access the database locally on that machine. But we want a user to be able to access the database remotely. In addition, we want to create a user that has read-only priviledges... i.e., they can't really screw anything up!
First, you'll need to log into your Freemed-YiRC server. If you're not familiar with this, please see this document: Freemed-YiRC SSH Access Guide. Log in, then you'll need to do the following:
- Become the root user, if you are not already.
- su root
- Enter root users password.
- Start MySQL in the administrative database
- mysql mysql
- Create a new user
- grant select on freemed_yirc.* to USERNAME identified by 'PASSWORD';
- Do not forget semi-colon (;) at the end of the command! Do not forget to surround PASSWORD with single-quotes (')!
- USERNAME is the name of the user you wish to use.
- It's suggested you do not use guest for security. You may use fmyirc.
- PASSWORD is the password for this new database user.
- Please use a good password, composed of both letters AND numbers.
- Remember, although this user will only be given read-only access, there will be information in your database which you would not want other people to view!!!
- flush privileges;
- Do not forget semi-colon (;) at the end of the command!
- grant select on freemed_yirc.* to USERNAME identified by 'PASSWORD';
Creating an ODBC Data Source
The next item we need to accomplish is to create an ODBC Data Source. This is done within your Operating System, as opposed to your database or reporting application. You only need to create one ODBC data source, multiple applications may be one data source. NOTE: Before you can use your database application (i.e., MS Access, MS Excel, Open Office, etc...), you MUST follow these instructions!!!
Windows XP
If you've never used ODBC before, especially with a MySQL database, then you will likely need to download and install the MySQL ODBC driver (also known as the MySQL Connector/ODBC). NOTE: DO NOT USE THE V5 or V5.1 DRIVERS! ONLY USE 3.51!
- Go to http://www.mysql.org/downloads/connector/odbc/3.51.html
- In the Windows downloads section, click on the Pick a mirror link on the line which reads Windows (x86) MSI Installer.
- YOU DO NOT NEED TO LOG IN, simply ignore these boxes and scroll down to Mirrors in: (presumably United States)
- Click on the HTTP or FTP links next to an entry until one of them allows you to download the file (mysql-connector-odbc-3.51.xx-win32.msi)
- Run the file you just downloaded.
- Choose Complete install
Moving along, we now want to create an ODBC Data Source...
- Go to Control Panel.
- Typically Start -> Settings -> Control Panel
- Start Data Sources (ODBC)
- If you are in Category view, you will choose Performance and Maintenance then Administrative Tools then Data Sources (ODBC)
- If you are in Classic view, you will choose Administrative Tools then Data Sources (ODBC)
- Click on the System DSN Tab.
- Click on Add
- Scroll down to MySQL ODBC 3.51 Driver
- Click Finish
- Fill in the following:
- Data Source Name
- Just a descriptive name for this source. You can simply use Freemed-YiRC
- You do not need to put anything in Description
- Put the IP address or hostname of your Freemed-YiRC server into Server
- Put the name of the user you created in the Creating a Guest Database User section into the User field
- Put the password you created for this user into the Password field
- Put in freemed_yirc into the Database field
- NOTE: Ensure you use an underscore (_) as opposed to a hyphen here!!!
- Click Test, it should report Success; connection was made!
- If it does not, there is a problem!
- Click OK
- Data Source Name
- Your ODBC Data Source is now created. You can now close the ODBC Data Source Administrator and move on to using your application...
Linux
ODBC on Linux requires the unixODBC package. Unfortunately, installation of this package is beyond the scope of this document. For more information, please visit The Unix ODBC Website
Once you have unixODBC installed, you will need to create an ODBC entry for your Freemed-YiRC server. You can either do this via a GUI tool, or you can edit the file /etc/odbc.ini directly... If you edit the file directly, you'll want to add the following section:
[Freemed-YiRC] Driver = MySQL DATABASE = freemed_yirc SERVER = SERVERIP UID = USERNAME PWD = PASSWORD
Where:
- SERVERIP is the IP address or hostname of your Freemed-YiRC server.
- USERNAME is the name of the user you created in the Creating a Guest Database User section
- PASSWORD is the password you created for this user
Accessing The Database From Your Application
Open Office Base (2.x - Windows & Linux)
To access your database from Open Office Base, please start it up, then do the following:
- You should start from the Welcome to the OpenOffice.org Database Wizard
- IF NOT, choose File -> New -> Database
- Choose Connect to an existing database and choose OBDC from the drop-down below that (DO NOT CHOOSE MySQL!!!)
- Click Next
- Choose Connect using ODBC
- Click Next
- Click Browse
- From the Data Source window, find your Freemed-YiRC data source. Highlight it and hit OK
- Click Finish (DO NOT CLICK NEXT!)
- Give this file a name. This will NOT save the entire database to your computer. All this does is creates a shortcut of sorts so you can access this information quicker in the future. You may call the file fmyirc or something you will remember it by.
You can now open this file. After doing so, if you click on Tables to the left and a bunch of tables show up that start with fy_, then you are now connected to your database using Open Office Base!
Open Office Calc (2.x - Windows & Linux)
NOTE: In order to access your database from Open Office Calc, you will first need to create an Open Office Base file (follow instructions above). To access your database from Open Office Calc, please start it up, then do the following:
- Click View
- Click Data Sources
- A new portion of the menu should open up. It might have a list on the left with a top entry of Bibliography. Below it should be the file name you saved it under while creating the shortcut file in the above Open Office Base step. Click the plus sign next to this.
- Click the plus sign next to Tables
You should now be able to access all the database tables in the Freemed-YiRC database! To copy any of these to your spreadsheet, simply drag and drop the table name onto your spreadsheet.
