Introduction

The department runs an SQL database server (mysql) that you can use for development and testing of database projects. The name of the host machine running the service is “stusql.dcs.shef.ac.uk”.

There are two types of databases available, 'team' and 'personal'. Both types are functionally identical, but there are these differences:

For most coursework, you will use team databases. You will be allocated to a team and given the database details by your module lecturer or demonstrator. Remember that your lecturer has the password for all team databases, and may choose to inspect it at any time. If you forget the password approach your lecturer.

As well as any team databases you use, you may also require a personal database for individual project work. Please request one by Contacting Support', and when it is set up you will be able to collect the details from https://www.dcs.shef.ac.uk/cgi-intranet/campus/dcsmysql.pl. You will need to login to this page using your existing ITS username and password.

Team databases are created in advance, and details are delivered by your lecturer. Note you cannot change any of the database passwords, sorry!

The data files of the databases are not directly available to you, and you will only be able to access them via the database server. You can access the server in a variety of ways, including: from Java programs using a JDBC driver; from Perl programs using the DBI and DBD::MySQL modules; from PHP; and from command-line utilities in Windows or Unix. Note: The MySQL server is only available from computers on the campus network, so if you're trying to access it from outside the university you'll have to use VPN (www.shef.ac.uk/it-services/vpn).

Documentation is available online at http://dev.mysql.com/doc/. Please note that you must CLOSE your database connections to the mysql server, otherwise the server runs out of connections, and no user will be able to make new connections. You will see messages e.g. 'too many connections'. If you do not understand how to do this, please see your lecturer.

Examples

These examples have usually been produced by the technical support group, and not your lecturers. By all means read and try them, but be aware:

• the software used may not (or no longer) be available on your system. In many cases we have used 'standard public-domain' software, so you may be able to install the same software yourself. Remember the software installed on the ITS managed systems is whatever has been requested by your lecturers;
• the example may not (or no longer) be the best solution;
• your lecturer may give you alternate or better examples of solutions;
• your primary contact for help with teaching software should be your lecturers and demonstrators, not the support group;

Access via JDBC

To use JDBC you need to load a mysql driver, which may well be an additional software installation. In this example the “mm.mysql” driver has been installed.

Class.forName("org.gjt.mm.mysql.Driver").newInstance();

and then open a connection,

String DB="jdbc:mysql://stusql.dcs.shef.ac.uk/dbname?user=dbuser&password=dbpassword";

e.g.

String DB="jdbc:mysql://stusql.dcs.shef.ac.uk/george?user=george&password=????";
Conn = DriverManager.getConnection(DB);

To run programs you need to specify the 'mysql' class library. Notice that you do not need this classpath to compile the program, since the driver is only dynamically loaded at run time. Access via MySQL command-line clients

In this example we make use of the MySQL command-line client. Note that you cannot run these by clicking, you will need to start a command line-processor first - on Unix use a terminal program, and on windows start a 'command prompt'.

The general format of these commands is

% command -hstusql.dcs.shef.ac.uk -udbuser -p dbname

Note that the '-p' option simply says 'prompt me for the password' on the next line - you are not specifying the password on the command line! A typical command for a team database for 'team123' is:

% mysql -hstusql.dcs.shef.ac.uk -uteam123 -p team123

Enter password: *enter the password given to you by your lecturer* Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 3320 to server version: 5.0.77

In the example above, notice that you can check which version of mysql the server is running by reading the welcome information when you connect - in this case the server is version 5.0.77.

You can use the mysqldump command to transfer a copy of your database to and from machines at home, e.g. for a personal databse for user 'george':

% mysqldump -hstusql.dcs.shef.ac.uk -ugeorge --add-drop-table -p george > mydb.txt

Enter password: …take the file mydb.txt home, work on it, …sleep, eat, then bring it back and reload onto DCS server.

% mysql -hstusql.dcs.shef.ac.uk -ugeorge -p george < mydb.txt

Access via PHP

<? 
mysql_pconnect("stusql.dcs.shef.ac.uk","dbusername","dbpassword") or die("Unable to connect to 
SQL server");
mysql_select_db("dbname") or die("Unable to select database");
?>