midop
Macroseismic Intensity Data Online Publisher
  homepage        download        manual        examples
manual index  >   input data preparation and management  >   uploading data into mysql

Uploading data into MySql

Macroseismic data tables must be uploaded to MySql in order to be reachable by MIDOP.

This upload process is crucial: if something goes wrong your data could be affected by some conversion error, so be careful while doing it.

In order to avoid potential problems, please follow these simple rules:

If you are new to MySql and, more generally, not familiar with the concepts of a database, keep in mind that:

In a database the content of each field must be always declared in advance when you are creating the table. Changing later on the data type of a field is possible, however it may lead to a serious data loss. If for example you have a field declared as “text” and you will change the type to “numerical”, you will loose all the alphabetic characters leaving only numbers.

Below a list of the main MySql data “types”:

Type Description
VARCHAR (Length) A fixed-length field from 0 to 255 characters long.
TINYTEXT A string with a maximum length of 255 characters.
TEXT A string with a maximum length of 65,535 characters.
MEDIUMTEXT A string with a maximum length of 16,777,215 characters.
LONGTEXT A string with a maximum length of 4,294,967,295 characters.
TINYINT [Length] Range of -128 to 127 or 0 to 255 unsigned.
SMALLINT [Length] Range of -32,768 to 32,767 or 0 to 65535 unsigned.
MEDIUMINT [Length] Range of -8,388,608 to 8,388,607 or 0 to 16,777,215 unsigned.
INT [Length] Range of -2,147,483,648 to 2,147,483,647 or 0 to 4,294,967,295 unsigned.
BIGINT [Length] Range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 or 0 to 18,446,744,073,709,551,615 unsigned.
FLOAT A small number with a floating decimal point.
DOUBLE [Length, Dec] A large number with a floating decimal point.
DECIMAL [Length, Dec] A double stored as a string, allowing for a fixed decimal point.
DATE In the format of YYYY-MM-DD.
DATETIME In the format of YYYY-MM-DD HH:MM:SS.
TIMESTAMP In the format of YYYYMMDDHHMMSS; range ends in the year 2037.
TIME In the format of HH:MM:SS.

Table 5. Main MySql field data types.

In this page you may fond some tips on how to upload your data to MySQL.

You may alternatively want to search Google for tips on how to upload data to a MySQL server.

Main database creation

You may create a new database using the MIDOP MySQL manager, a built-in minimal tool that enables you explore, create and remove databases on your MySQL server.

IMPORTANT NOTE
The internal MySQL manager might cause irreversible damage to your data: please, use it with extreme care!

Alternatively you may create a database using "phpMyAdmin", an open source tool extremely reliable and complete.
Below a quick explanation on how to use it on Windows and Mac OSX in combination with pre-configured AMP stack.
If you use Linux, please, search Google on how to install phpMyAdmin on your system.

 Windows    

phpMyAdmin is a web tool written in PHP language that offers let you visually manage your MySql databases. By using it you will be able to create, modify and query both databases and tables and also upload and download your data content.

For every action requested by the user phpMyAdmin give a feedback message telling if the operation was successful or not. Below a screenshot showing a correctly created database:

 MacOS X    

If you decide to alter/change/correct your data directly in MySql using phpMyAdmin we advice you to keep trace of all of them or you’ll end up with a data set which will not correspond to your initial data, resulting in odd situation later.

Data upload via CSV text file

Data upload using an ODBC link

This section covers the procedure on how to transfer a table from Microsoft Access to MySql using an  ODBC, Open Database Connectivity (http://en.wikipedia.org/wiki/ODBC).
In order to let Access and MySql communicate directly, Windows need to know which driver must be used while transferring data; such database engine driver will transparently maps Access data into MySql and vice versa with just a couple of clicks.

 Windows    

The MySql ODBC driver is freely available from the MySql website.
Before installing it, you should determine which is the right version for your system, the 32bit or the 64bit release.

Please, have a look at the page "Installing the MySql ODBC driver on Windows".

Before continue, check if the ODBC driver is properly installed and is available to Windows.
To do so, run the correct version of the "ODBC Data Source Administrator" for your system (see "Installing ODBC on Windows") and check the "Driver" tab.
The MySql ODBC driver should appear in the list, as shown in figure:

Now that the ODBC driver is properly installed, applications that are ODBC aware such Microsoft Access, can connect to MySql.
A series of links to different remote data servers can be created; creating these links is possible using a quick configuration procedure in the "ODBC Data Source Administrator".
Once a link to a remote server is created, you can then select such link in Access, and transparently load the remote data pretty much like loading data from a local file.

Procedure to create a ODBC link to a remote data server:

  1. Turn on the both Apache and the MySql server, if not already running;
  2. To create an ODBC link to the database “midop” (as explained above), open the "ODBC Data Source Administrator".
  3. Select the "User DSN" tab anc click "Add"
  4. Select "User Data Source" and "Next"
  5. Select the MySql ODBC driver and "Next"
  6. Enter:
    - a meaningful data source name, as it will be later on used to identify the link from MS Access;
    - the remote server hostname or IP address;
    - the user name to login into the remote server (in XAMPP and WAMP the default user is "root");
    - the password (in XAMPP and WAMP leaving it empty);
    - click "test" to check if the connection can be established;
    - click "OK";

From now on you will be able to connect to this data resource from every application ODBC enabled.
You are now ready to transfer data from Access and MySql with a couple of clicks.

Please, note that this connection works in both ways: you can export but also import data tables from MySql.

In order to export a table from Access to MySql follows these steps:

   

The data transfer speed might depends on the table size and the type of connection to MySql (local, on the same computer or a remote connection).
That’s it: your table is now stored in MySql and will be available to MIDOP.

 MacOS X    



Create an OpenOffice Base file that is connected to MySql follow this procedure:


To copy a table from a OpenOffice Base to MySql database you require two different OpenOffice Base files:

As OpenOffice Base cannot manage local and linked tables within the same Base file you must follow this procedure in order to copy a table to MySql:


For more information on OpenOffice Base usage refer to:
http://wiki.services.openoffice.org/wiki/Database

Your “midop” database in the MySql server contains now your data table and the MIDOP package can now use your data.

 Linux (Ubuntu)    

In order to use OpenOffice Base with MySql tables, the ODBC (“Open Database Connectivity”, http://en.wikipedia.org/wiki/ODBC) system drivers must be installed.

In order to use OpenOffice as a front-end for your MySql database follow these steps:

The procedure for copying a table from OpenOffice Base local tables to MySql is the same as the Mac OSX procedure described above.

 

 
 top of
this page