MySQL and mSQL

MysqlPerl

Monty Widenius, the author of MySQL, also wrote the Perl interface to MySQL, Mysql.pm. This was based on the mSQL module, Msql.pm. Thus, the interfaces of the two modules are almost identical. In fact, we recently converted an entire site from mSQL to MySQL by running “perl -e ’s/^Msql/Mysql/” *.cgi” in every directory containing a CGI. This covers 95% of the work involved. Of course, this does not give you any of the advantages of MySQL, but it is a quick and easy way to start down the road to MySQL. Mysql.pm is maintained as part of msql-mysql-modules by Jochen Wiedmann.

Note

One of the largest differences between MySQL and mSQL is the way they handle sequences. In mSQL, a sequence is defined on a table with a command like CREATE SEQUENCE on tablename. The sequence value is then read as if it were a normal table value with the command SELECT _seq fromtablename. MySQL adds the flag AUTO_INCREMENT to the primary key. Whenever a null value is inserted into this field, it is automatically incremented. Both MySQL and mSQL allow only one sequence per table. For a full discussion on sequences in MySQL and mSQL, see Chapter 6.

As an example of some of the features of Mysql.pm, let’s go back to the tests example. Now that we have subject.cgi taken care of, the next step is the table of student information. The structure of the students table is as follows:

CREATE TABLE student ( id INT NOT NULL auto_increment, first VARCHAR(50), middle VARCHAR(50), last VARCHAR(50), ext VARCHAR(50), ...