Perl and MiniSQL

In the last issue of the informant, we went through all of the mysql-perl functions available with the Msql / Mysql module available from CPAN (http://search.cpan.org/search?module=Mysql). Following the format, we will compare the perl mSQL functions to their PHP equivalents.

Before we begin, to test out these functions, you have to have the module installed. You must also have mSQL installed as well, which is available at http://www.hughes.com.au/. Just like MySQL, mSQL is free for you to use. The only difference between the two really is that each of them has a few extra features in them, but this is the case for most SQL servers.

Anyways, this isn't a tutorial on mSQL, but on the functions perl has to the mSQL database server, so enough of this jargon.

mSQL Functions

Remember that, before you use any of these functions, you need the line

use Msql;

in your script, above the use of the functions. Usually I deal with this at the top of the script.

Connecting to the mSQL server

PHP has:
msql_connect("hostname");

Perl has:
$db = Msql->connect("hostname");

As you can see, the variable $db is the pointer to your database connection after establishing it. The arguments in connect are optional, and you can just use Msql->connect if you are using the default host.

Selecting your Database

PHP has:
msql_select_db("database_name", $link_id);

Perl has:
$db->selectdb($database);

Here we specify the database we will be accessing. The $db scalar is being used here as it is the pointer to our mSQL connection.

Database,Table and Column Information

PHP has:
$dbs = msql_list_dbs();

This is traversed with

$result = msql_list_dbs($link_id);
$i = 0;
while ($i < msql_num_rows ($result)) {
$db_names[$i] = msql_dbname($result, $i);
echo $db_names[$i] . "<BR>";
$i++;
}

Perl has:
@dbs = $db->listdbs;

Just like the MySQL function, this is traversed easily as well.

@dbs = $db->listdbs;
foreach $database (@dbs) {
print $database."\n<BR>";
}

PHP has:
msql_list_tables(string database);

Traversed by:

$result = msql_list_tables("database");
$i = 0;
while ($i < msql_numrows($result)) {
$tb_names[$i] = msql_tablename($result, $i);
echo $tb_names[$i] . "<BR>";
$i++; }

Perl has:
@tables = $db->listtables;

Similar to the database listing function, this one can simply be traversed by:

@tables = $db->listtables;


foreach $table (@tables) {
print $table."\n<BR>";
}

PHP has: msql_list_fields("database", "tablename");

Perl has:
$fields = $db->listfields($table);

Here we grab the column information about our table specified. The $fields pointer can now be used with $fields->name; for the names of the columns. This part is exactly as you would see it for MySQL functions, give or take a few

If you only want a single name though, say, for the second column, you would just have to write $second_column = $fields->name->[1]. Why did I put in [1]? You have to remember it's in array context, so the first column would be found in [0], second in [1], third in [2], etc.

@array = $fields->type; for an array of the datatypes @array = $fields->length; for the maximum lengths of all the datatypes in the table (in bytes).

Some of the extra functions include:

• @array = $fields->isnotnull; which returns an array of boolean values checking if the column has is not null set.
• @array = $fields->isprikey; returns an array of boolean values checking if the column is a key in the database.
• @array = $fields->isnum; returns an array of boolean values checking if the column is numeric.

I think you get the idea though..... this function probably won't be used to often, but, if it is, it can be handy with this large inventory of functions.

mSQL Queries »