Howto use MySQL databases via ODBC
MySQL is an extremely efficient database system, and can be bought as an addition to your installation. Please contact support for further information regarding this or simply see the upgrade function in the Planet Controller.
It is possible to use a local database (example: Access-database) on your own computer as a "user interface" to a MySQL database that is located on the web server, but the setup requires some knowledge on ODBC and what possibilities your local database supports.
To connect 2 databases together, ODBC must be installed and configured on both your local machine and the server - we have already taken care of the server side, but you have to handle the client side setup.
Start by downloading the lasted version of the MyODBC for Windows at the MySQL site - there are different versions for Windows 9x and Windows NT/2000/XP, but the functionality and procedures are the same. Download "Driver Installer", not the "Source" or "Driver DLLs only".
Please notice that there are also a MyODBC for different Unix types, but the installation requires a fair bit of knowledge on Unix and ODBC - it is NOT recommended for novices.
Download MyODBC from here:
http://mysql.com/downloads/api-myodbc.html
Run the installation program by double clicking the file. Click "Next" a few times to complete the installation, after the installation has completed open up the ODBC Data sources control in the administrative control panel, click the "Add.." button.
a window will now appear, give the source a name (DSN) in the "Data Source Name" field and a description in the "Description" box.
The other important boxes are:
- Host/Server name (or IP): This is the name of the database server you are using on your web server, for example venus.dbservers.dk
- Database name: The name of the database, that we have created for you. This will often be related to your domain name, for example testdom_dk as in this case.
- User: Your user name for the database - this was supplied to you when the database was created.
- Password: The password for the database - same conditions as "User:".
Please notice that the user name, password and all other information can be found in the Planet Controller, you have to enable connections to all places or specifically to your internet connection's external IP.
Now click on "Test Data Source" to do a connection test, if there are any problems with the connection it will be revealed here. Click "OK" to complete the configuration and close the MyODBC Data Source window, then click the "OK" button to close the ODBC Data source manager.
Left is "only" to get your local database program to use the ODBC connection that we just created, in it self a simple task, but it can cause problems because of the way ODBC works. There are a few points that require some attention:
-
You can not create a new database via ODBC on the remote server, only connect to an existing database.
-
You can not open an existing database directly on the remote server - only link the tables in the MySQL database to an existing local database.
-
You can not always change the design of an existing table on the remote server - only put data in it. You can however design in the local database and export it to the remote database. When the table is exported it can no longer be changed, this is not a problem on all database types, so test as you go a long.
In rough terms the procedure to get a connection to MySQL via ODBC is the following:
-
Create a local database in Access, StarBase, Paradox, Approach or similar program. This is simply a template for your future database.
-
Create your tables and get the design as close to complete as possible - since that when they are created they can no longer be altered, but you can delete tables and export a changed version, but you loose the data in the process.
-
Export tables to your MySQL database (ODBC is right at the end of the list of export options) and close the local database.
-
Create a new local database (this will be your working database) and link to the tables in the MySQL database. You can now manipulate the data in the tables, make request, generate reports, statistics, etc. from your local database program, and any change of data will instantly be replicated to the remote database.
This is just a quick introduction to ODBC and databases that is meant to help get the basic infrastructure into focus. It is required that you get further knowledge on the design and usage of databases to get the full benefit from such systems. A good start for those interested in SQL is the site below:
- especially look at "Online resources", there you will see a line of links to tutorials, manuals, etc.