Friday 20 October 2006

MySQL in CEMS

MySQL is installed for student use on the host 'shares', a Unix server accessible only from within the UWE domain. The current version is 4.0.13.

Each student has one database, with the same name as their UNIX username. All tables must be created in this database. Login with the UNIX username and original UNIX password. Get it reset by the help desk if you can't recall what this password is.

Access
You can access the database in either of two ways:
  • Using the web client PHPMyAdmin: http://isa.cems.uwe.ac.uk/new/phpmyadmin/
    • This GUI interface allows you to browse, edit, create and modify tables. However, since we are using QSEE to generate code, this will not be so helpful although a file of SQL commands can also be executed.
  • By logging in directly to the server for example, using Putty and starting the SQL command line interpretor (CLI).
    • e.g to start: >mysql - u cjwallac -ppasssword
    • to execute a file of SQL commands use the source command : source 'filename'
Hints
  • Table names on the version of MySQL running on the host 'shares' are case sensitive because this a Unix server. Take care when developing on Windows because case is not signicant so code may work on your own machine but not when installed at UWE.
  • The SQL generated from QSEE will cause errors when loaded using PHPMyAdmin due problems with comment lines with no space after --. You must also ensure that there is a use statement at the beginning. To overcome these problems you must edit the generated code. The mysql CLI is not so fussy and thus easier to use.
  • auto_increment is not settable in QSEE so this will need to be set separately. One approach is to define such changes in a separate file of Alter Commands for example:
    • alter table photo
      modify photoid int auto_increment;

Links

No comments: