Using CSIM SQL server

MySQL set-up
Using MySQL with MySQL-Front
Using MySQL from MS Access
Im/Exporting from/to a file
CSIM Logo WelcomeCourses
Faculty, Student, Staff
Projects and reports
Conferences, workshop and seminars
Laboratories and reasearch facilities
Information related to CSIM
Information non-related to CSIM
Address, map, phone, etc.
Search

CSIM network runs a SQL server for your benefit. This server is based on the widely used open source MySQL software.

MySQL general set-up

The MySQL server is installed on a dedicated machine called database.cs.ait.ac.th.

As a user of CSIM system, you have an account open on the MySQL server. Your account has the same user name and password as your normal Unix or samba account. When you change your Unix password, with the web page for account management, it will also change your MySQL password.

Note: if your account on CSIM system was created before the MySQL server was installed (before November 15th, 2001), you have to first go to the web page for account management and change your password once to open your MySQL account.

You can read online documentation in HTML format, there is a special section about calling MySQL from a Perl script. Usefull information can also be found on DevShed.

You have access to a database that is created for your own usage, it has the same name as your user name on Unix or samba. Among the many databases available on the server, you can only use the one that has your user name. For security reason, you can use this server only from CSIM network.

As a summary:
Server name: database.cs.ait.ac.th  
Port: 3306 (default)  
User name: your user name  
Password: your password  
Database: your user name  

CSIM server banyan.cs.ait.ac.th and our web server have the libraries installed to access MySQL server, either directly with the mysql tool or with Perl scripts. Below is an example of a MySQL session on banyan:

  banyan<on>57: mysql -h database.cs.ait.ac.th -u b00355 -p Enter password: ******* Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1102 to server version: 3.23.43-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> connect b00355 Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Connection id: 1103 Current database: b00355 mysql> select * from Alter_Battery; +-----+--------+--------------+-----------+--------------+ | mid | mid_no | battery_type | talk_time | standby_time | +-----+--------+--------------+-----------+--------------+ | 34 | 1 | 5 | 210 | 150 | | 36 | 1 | 5 | 180 | 100 | | 37 | 1 | 5 | 180 | 100 | +-----+--------+--------------+-----------+--------------+ 3 rows in set (0.00 sec) mysql> quit Bye banyan<on>58: 

Using MYsql with MySQl-Front

MySQL-Front is one of the many free clients for Windows that allow you to create and manage your database with a graphical interface.

This software does not have a fancy installation procedure, just download it and unzip it somewhere (in C:\Program Files\MySQl-Front for example). You will also have to make an entry in C:\Windows\Start Menu.

When you access MySQL-Front for the first time you have to create a new connection. Do not save the connection with your password, else any user will be able to connect to your database.

I noticed the following while using MySQL-Front:

What I especially like with MySQL-Front is the SQL requests that are displayed at the bottom of the screen with ever operation made on the database.

Using MySQL from Microsoft Access

This will come later.

Im/Exporting from/to a file

The server is a stand-alone machine, where you cannot store your files; to import data, you can use the command LOAD with the LOCAL:

LOAD DATA LOCAL INFILE 'file_name.txt' INTO TABLE tbl_name

To export files, there is not such an option. Instead you can export data from MySQL-Front if you are using Windows environment, or you can do it via a short program that will read the table reccord by reccord, and store the in a file.

  #!/usr/local/bin/perl use DBI; # module to access MySQL my $dberror=0; my $dbh = DBI->connect('DBI:mysql:webcsim;host=database.cs.ait.ac.th;port=3306' , 'csim', '********', {PrintError => 0}); # connect to MySQL server if (! defined($DBI::err)) { # no error $sth = $dbh->prepare("select * from resa_VDO"); # read all the table $rv = $sth->execute; # start the SQL request open OUT, ">outfile"; # open the output file while(@row = $sth->fetchrow_array) { # read the next reccord print OUT "$row[0]\t$row[1]\t$row[2]\t$row[3]\n"; # save to file } $dbh->disconnect; # disconnect from MySQL } 

Powered by:

CSIM home pageWMailAccount managementCSIM LibraryNetwork test toolsSearch CSIM directories
Contact us: Olivier Nicole CSIM    SET    AIT Last update: Nov 2001