Book HomeMySQL and mSQLSearch this book

21.3. Msql.pm API

use Msql

use Msql;
This must be declared in every Perl program that uses the Msql.pm module.
Msql::connect

$db = Msql->connect;
$db = Msql->connect($host);
$db = Msql->connect($host, $database);

Establishes a connection between your Perl program and the Msql server. There are three versions of the function. With no arguments, a connection is made to the Msql Unix socket on the local host with no database defined. This is the most efficient connection. If one scalar argument is present, that argument is taken to be the hostname or IP address of the mSQL server. A connection is then made to that server with no database set. If two scalar arguments are present, the first is taken to be the host of the mSQL server and the second is the name of the desired database. The program then makes a connection to the given server and selects the given database. The value returned is a reference to an object called the "database handle." All communication with the database server itself takes places through this object. If the connection fails for any of the above cases, undef is returned and the error is placed in $Msql::db_errstr.

Example

use Msql;

# Connect to the localhost Unix socket
my $db = Msql->connect;

# Or...
# Connect to host 'www.myserver.com' with no database defined
my $db = Msql->connect('www.myserver.com');

# Or...
# Connect to host 'www.myserver.com' and select database 'mydata'
my $db = Msql->connect('www.myserver.com','mydata');
Msql::createdb

$result = $db->createdb($database);
Msql::createdb takes as its argument the name of a database to create. It then sends the creation request to the mSQL server. The command is sent as the same user running the CGI program. Thus, to work in a CGI program, the program must be run as a user with the right to create a new database. The function returns -1 on failure and on success.

Example

use Msql;
my $db = Msql->connect;
my $my_new_database = 'mynewdata';
my $result = $db->createdb($my_new_database);
die "Database was not created!" if $result == -1;
print "$my_new_database has been created.\n";
Msql::database

$database = $db->database;
Msql::database returns the name of the current database as a scalar. The function returns undef if no database has been selected.

Example

use Msql;
my $db = Msql->connect('www.myserver.com','mydata');

my $database = $db->database;

print "This should say 'mydata': $database\n";
Msql::dropdb

$result = $db->dropdb($database);
Msql::dropdb takes as its argument the name of a database to destroy. It then sends the destruction request to the mSQL server. The command is sent as the same user running the CGI program. Thus, to work in a CGI program, the program must be run as a user with the right to destroy the database. The function returns -1 on failure and on success. This function does not ask for any confirmation and the results are permanent. Thus, this function should be used with the most extreme caution.

Example

use Msql;
my $db = Msql->connect;
my $result = $db->dropdb('mydata');
die "Command failed!" if result == -1;
print "'mydata' is now gone forever.\n";
Msql::errmsg

$error = $db->errmsg;
Msql::errmsg returns the last error encountered by your session with the mSQL server. The value remains until the next error occurs, at which time it is replaced. If no error has occurred during your session, the function returns undef.

Example

use Msql;
my $db = Msql->connect;
...
my $error = $db->errmsg;
warn("This is your most recent mSQL error: $error");
Msql::getsequenceinfo

($step, $value) = $db->getsequenceinfo($table);
Msql::getsequenceinfo takes the name of a table as its argument. It returns the step and value of the sequence defined on the table, if any. If there is no sequence defined on the given table, an undefined value undef is returned and an error is placed in Msql::errmsg.

Example

use Msql;
my $db = Msql->connect;
my ($step, $value) = $db->getsequenceinfo('mytable');
die "There is no sequence on mytable" if not $step;
print "mystep has a sequence with a value of $value and a step of $step\n";
Msql::host

$host = $db->host;
Msql::host returns the hostname of the database server as a scalar. There is no guarantee that the function will return the canonical name of the server or even a fully qualified domain name. In fact, although not documented, it appears that Msql::host returns the same string given to the server in the Msql::connect call. This is true even to the point that Msql::host returns undef if you use the no-argument form of Msql::connect.

Example

use Msql;
my $db = Msql->connect('www.myserver.com');

my $host = $db->host;

print "You'll probably see 'www.myserver.com': $host\n";
Msql::listdbs

@databases = $db->listdbs;
Msql::listdbs returns an array of the databases available on the server. If there are no databases on the server, it returns an empty array.

Example

use Msql;
my $db = Msql->connect;

my @databases = $db->listdbs;
print "Available databases:\n\n" . join("\n",@databases);

$fields = $db->listfields($table);
Msql::listfields takes as an argument the name of a table in the current database. It returns a reference to an object which contains the names of all of the fields, as well as some other information. This reference is known as a statement handle. You can access the information in a statement handle using any of the following functions: Msql::Statement::as_string, Msql::Statement::listindices (mSQL 2.0 only), Msql::Statement::numfields, Msql::Statement::table, Msql::Statement::name, Msql::Statement::type, Msql::Statement::isnotnull, Msql::Statement::isprikey, Msql::Statement::isnum, and Msql::Statement::length. If the table does not exist, the function returns an undefined value undef, and the error is placed in Msql::errmsg. See
Reference 21.21 for a technique that makes this function somewhat obsolete.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');

my $fields = $db->listfields('mytable');
warn ("Problem with 'mytable': " . $db->errmsg) if (not $fields);
# $fields is now a reference to all of the fields in the table 'mytable'.
print "mytable contains the following fields:\n";
print $fields->as_string;
Msql::listindex

@index_handles = $db->listindex($table,$index);
Msql::listindex accepts the names of a table and the name of an index as its arguments and returns an array of statement handles containing information about each of the indices. Although this function is documented as returning an array of statement handles, we can find no case where more than one statement handle would be returned. Therefore it is probably safe to treat this function as returning a scalar statement handle. The statement handle is of the same style as a statement handle returned by Msql::query and can be accessed by the same functions. If the index does not exist, an undefined value undef is returned. The table of data returned about the index has one column, which has the title "Index". The first row is the type of index, which in mSQL 2.0 is always "avl". The other rows are the names of the fields that comprise the index. This function is valid only with mSQL 2.0 or greater database servers.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $mytable_fields = $db->listfields('mytable');
my @indices = $mytable_fields->listindices;
# I now know the names of all of the indices.

foreach (@indices) {
   my $index_info_handle = $db->listindex('mytable',$_);
   my (@index_info) = $index_info_handle->fetchcol(0);
   my $type_of_index = shift(@index_info);
   # $type_of_index now contains the type of the index (probably 'avl')
   # and @index_info now contains a list of the fields in the index.
Msql::listtables

@tables = $db->listtables;
Msql::listtables returns an array of the tables available in the current database. If the database has no tables, the function returns an empty array.

Example

use Msql;
my $db = Msql->connect;

my @tables = $db->listtables;
my $database = $db->database;
print "$database has the following tables:\n\n"
				. join("\n",@tables);
Msql::query

$query_output = $db->query($sql_statement);
Msql::query is the most important and most frequently used function in the Msql.pm API. It is through this function that you actually send the SQL queries to the database server. The function takes a scalar string containing an SQL query as an argument. If the query is a SELECT statement, the function returns a statement handle containing the results of the query. Otherwise, the function returns the number of rows that were affected by the query. The statement handle can be accessed by the same functions listed for Msql::listfields (except for Msql::Statement::listindices) as well as the following: Msql::Statement::fetchrow, Msql::Statement::fetchcol, Msql::Statement::fetchhash, Msql::Statement::numrows, Msql::Statement::maxlength, and Msql::Statement::dataseek. If the query is unsuccessful for any reason, an undefined value undef is returned and the error is placed in Msql::errmsg. Each statement handle contains the output of a separate query. Therefore, you can send as many queries as your system can handle and then deal with each of the statement handles at your leisure.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');

my $query1 = "SELECT * FROM mytable";
my $query2 = "SELECT name, date FROM myothertable WHERE name LIKE 'Bob%'";
my $query3 = "UPDATE myothertable SET name='Bob' WHERE name='Joe'";

my $mytable_output = $db->query($query1);
my $myothertable_output = $db->query($query2);
my $myothertable_input = $db->query($query3);

# $mytable_output contains the results of the query on 'mytable'
# $myothertable_output contains the results of the query on 'myothertable'
print "The update on 'myothertable' affected $myothertable_input names\n";
$Msql::QUIET

The $Msql::QUIET variable, when true, turns off error reporting when the -' option is used in Perl. Otherwise, all MsqlPerl errors will be automatically sent to STDERR. The variable can be reset at any time. The -w error reporting feature is so useful that setting $Msql::QUIET is not recommended.

Example

use Msql;

# Turn off error reporting. This has an effect only if the script is being
# run with '-w'.
$Msql::QUIET = 1;

# Do noisy section...

# Turn error reporting back on.
$Msql::QUIET = undef;
Msql::quote

$quoted_string = $db->quote($string);
$truncated_quoted_string = $db->quote($string,$length);
Msql::quote takes as its argument a scalar string. It returns the same string quoted so that it is safe for insertion into a CHAR or TEXT field in the database. More specifically, it surrounds the string with single quotes, and uses backslashes to escape any single quotes already in the string. If a second argument is present, the result is truncated to be that many characters long.

Example

use Msql;
my $db = Msql->connect;

my $string = "This is a field's value";

my $qstring = $db->quote($string);

print qq%This now says "'This is a field\\'s value'" : $qstring\n%;
Msql::selectdb

$db->selectdb($database);
Msql::selectdb selects a database from the database server. If the selection fails, the error is placed in Msql::errmsg. The only effective way to test for the success of this function is to examine the value of $db->database and compare it to the database to which you intended to connect. You may switch databases at any time during your program.

Example

use Msql;
my $db = Msql->connect;

$db->selectdb('mydata');
# The database is now 'mydata'
if ($db->database ne 'mydata') {
				warn('AWOOOGA! The database wasn't properly selected!');
}
...
$db->selectdb('myotherdata');
# The database is now 'myotherdata'
Msql::shutdown

$result = $db->shutdown;
Msql::shutdown sends a shutdown command to the mSQL server. The command is sent as the user is running the program. Thus, to work in a CGI program, the program must be run as a user with the right to shutdown the database. The function returns -1 on failure and on success.

Example

use Msql;
my $db = Msql->connect;
# Time to shutdown the database...
my $result = $db->shutdown;
die "Command failed!" if $result == -1;
print "The server has been stopped.\n";
Msql::Statement::as_string

$formatted_table = $statement_handle->as_string;
Msql::Statement::as_string returns the data contained in the statement handle in a neatly formatted ASCII table. The table is similar to the ones used by the msql monitor. The pmsql program supplied with the Msql.pm module uses this function to generate its tables.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $query = "SELECT * FROM mytable";
my $mytable_output = $db->query($query);

print "My Table:\n", $mytable_output->as_string;
# This prints the entire table in a fashion much cleaner than the
# Msql::Statement::fetchhash example.
Msql::Statement::dataseek

$statement_handle->dataseek($row_number);
Msql::Statement::dataseek takes the number of a row as its argument. The function resets the data so that the next call to Msql::Statement::fetchrow or Msql::Statement::fetchhash will return the information in that row. If a row number is supplied that is beyond the range of the table, the pointer is placed at the end of the table so that the next access will return an undefined value undef. The first row of the table is row number 0.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $query = "SELECT name, date FROM myothertable";
my $myothertable_output = $db->query($query);

my @names = $myothertable_output->fetchcol(0);
my @dates = $myothertable_output->fetchcol(1);
# The pointer is now at the end of the table.

$myothertable_output->dataseek(0);
# The pointer is now reset to the beginning of the table.

print "This is the first row of data: ", $myothertable_output->fetchrow, "\n".
Msql::Statement::fetchcol

@column_of_data = $statement_handle->fetchcol($column_number);
Msql::Statement::fetchcol takes the number of a column as its argument and returns an array of all of the values in that column. Multiple calls return all columns in the same order, so that all of the values with a certain element number refer to the same row. The first output column is numbered 0. To perform this call, the module must read the entire table. Therefore, if you want to continue examining the table after using this function you have to reset the data using Msql::Statement::dataseek. An undefined value is returned. The function returns undef if an invalid column number is provided.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $query = "SELECT name, date FROM myothertable WHERE name LIKE 'Bob%'";
my $myothertable_output = $db->query($query);

my @names = $myothertable_output->fetchcol(0);
# @names now contains all of the names.
my @dates = $myothertable_output->fetchcol(1);
# @dates now contains all of the dates.
for (0..$#names) {
   print "Row $_: $names[$_], $dates[$_]\n";
}
Msql::Statement::fetchhash

%hash = $statement_handle->fetchhash;
Msql::Statement::fetchhash ow of the statement handle as an associative array (or hash). The keys of the hash are the names of the fields and the values are the data values for the current row. Each successive call to the function returns the next row of data. When there is no more data, the function returns an undefined value undef.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $query = "SELECT * FROM mytable";
my $mytable_output = $db->query($query);
my %first_data_row = $mytable_output->fetchhash;

my @fields = keys %first_data_row;
# @fields now contains all of the field names. Therefore there is never really
# any need to use Msql::listfields, since we can get that information along
# with a lot more through the statement handle returned from Msql::query.

my (%data_row);
print join("", @fields), "\n';
print "-"x70;
print join("", values(%first_data_row);
print join("", values(%data_row)) while %data_row = $mytable_output->fetchhash;
# This prints a complete dump of the table. (Albeit in a very misaligned format.
Msql::Statement::fetchrow

@row_of_data = $statement_handle->fetchrow;
Msql::Statement::fetchrow returns the next row of data from a statement handle generated by Msql::query. Each successive call to Msql::Statement::fetchrow returns the next row of data. When there is no more data, the function returns an undefined value undef. The elements in the resultant array are in the order specified in the original query. If the query was of the form SELECT * FROM . . ., the elements are ordered in the same sequence that the fields were defined in the table.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $query1 = "SELECT * FROM mytable";
my $query2 = "SELECT name, date FROM myothertable WHERE name LIKE 'Bob%'";
my $mytable_output = $db->query($query1);
my $myothertable_output = $db->query($query2);
my $i = 0;

# This will keep reading the rows of data until there
# are no more left.
while (my(@mytable_rows)=$mytable_output->fetchrow) {
   print "Row ".$i++.": ".join(', ',@mytable_rows)."\n";
   # Unless I know something about the structure of 'mytable'
   # I have no idea how many elements are in @mytable_rows or
   # what order they are in.
}

my ($name, $date);

# This is the first row of data from $myothertable_output.
($name, $date) = $myothertable_output->fetchrow;
# This is the next row...
($name, $date) = $myothertable_output->fetchrow;
# And the next...
my @name_and_date = $myothertable_output->fetchrow;
# etc...
Msql::Statement::isnotnull

@not_null  = $statement_handle->isnotnull;
Msql::Statement::isnotnull returns a list of boolean values indicating if each of the columns of data contained in the statement handle have been defined as `NOT NULL'. When called in a scalar context the function returns a reference to an array.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select * from mydata");
my @names = $output->name;

my @not_null = $output->isnotnull;
for (0..$#not_null) {
				print "$names[$_] is not null\n" if $not_null[$_];
}
Msql::Statement::isnum

@numbers  = $statement_handle->isnum;
Msql::Statement::isnum returns a list of boolean values indicating if each of the columns of data contained in the statement handle is a numerical value. When called in a scalar context, the function returns a reference to an array. Numerical values include types, such as `INT' and `REAL', but do not include a `CHAR' or `TEXT' field that contains numbers.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select name, date from myothertable");

print "Name is a number" if $output->isnum->[0];
print "Date is a number" if $output->isnum->[1];
Msql::Statement::isprikey

@primary_key  = $statement_handle->isprikey;
Msql::Statement::isprikey returns a list of boolean values indicating if each of the columns of data contained in the statement handle is a primary key. When called in a scalar context, the function returns a reference to an array. This function will always return a list of 0's when connected to a mSQL 2 server because mSQL 2 does not use primary keys. However, this function is useful with mSQL 1 servers, which do implement primary keys.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select * from mytable");

my @prikeys = $output->isprikey;
my $number_of_prikeys = scalar @prikeys;
print "There are $number_of_prikeys primary keys in this statement handle. ".
      "There are at least this many different tables in the query as each table".
      "can have only one primary key.\n";
Msql::Statement::length

@lengths  = $statement_handle->length;
Msql::Statement::length returns a list of the maximum possible length of each of the columns of data contained in the statement handle. These values are the maximums defined when the table was created. When called in a scalar context, the function returns a reference to an array.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select * from mytable");
my @types = $output->type;

my @lengths = $output->length;
for (0..$#types) {
   if ($types[$_] == &Msql::CHAR_TYPE and $lengths[$_] > 1000000) {
      print "You've got one mighty big CHAR field in that table!\";
   }
}
Msql::Statement::listindices

@indices = $statement_handle->listindices;
Msql::Statement::listindices returns the indices associated with any of the fields found in the statement handle. Because the function looks for field names directly, it is useful only with names returned from Msql::listfields. If no indices are found, an undefined value undef is returned. This function can only be used with mSQL 2.0, or greater, database servers.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $mytable_fields = $db->listfields('mytable');

my @indices = $mytable_fields->listindices;
print "'mytable' contains these indices: " . join(", ",@indices) . "\n";
Msql::Statement::maxlength

@max_lengths  = $statement_handle->maxlength;
Msql::Statement::maxlength returns a list of the actual maximum length of each field contained in the table. When called in a scalar context, the function returns a reference to an array. Since the mSQL server does not provide this information directly, this function is implemented by reading the entire table and searching for the maximum value of each field. Thus, with mSQL this function can be resource heavy when used with queries that return large amounts of information.

Example

use Msql;
$db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query('select name, date from myothertable');

print "The longest name is " . $ouput->maxlength->[0] . " characters long.\n";
Msql::Statement::name

@column_names  = $statement_handle->name;
Msql::Statement::name returns the names of the columns of data contained in the statement handle. When called in a scalar context the function returns a reference to an array. As with Msql::Statement::table, the scalar value of this list (as opposed to the value of the function when called in a scalar context) is identical to the value of Msql::Statement::numfields.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select * from mytable");

my @column_names = $output->names;
# @column_names is now a list of the columns in 'mytable'
Msql::Statement::numfields

$number_of_fields = $statement_handle->numfields;
Msql::Statement::numfields returns the number of fields contained in a single row of the output stored in the statement handle. All output has at least one field, so this function will return a positive integer for any defined statement handle.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select * from mytable");

my $numfields = $output->numfields;
my $numrows = $output->numrows;
print "There are $numfields field in each row of 'mytable'\n";
print "And there are $numrows rows of data. Thus, 'mytable'\n";
print "contains " . ($numfields*$numrows) . " cells of data.\n";
Msql::Statement::numrows

$number_of_rows = $statement_handle->numrows;
Msql::Statement::numrows returns the number of rows contained in the statement handle. If run on a statement handle that cannot contain any rows, such as one returned by Msql::listfields, the function returns the string `N/A.' If the statement handle could contain rows but does not, such as one returned by a SELECT that does not match any fields, the function returns 0.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select * from mytable");

my $numrows = $output->numrows;
print "There are $numrows rows of data in 'mytable'\n";
Msql::Statement::table

@tables  = $statement_handle->table;
Msql::Statement::table returns a list of the tables associated with each of the columns of data contained in the statement handle. When called in a scalar context, the function returns a reference to an array. (See Msql::Statement::isnum for an example of how to use the array reference.) One entry is present for each column of data even if only one table was used in the query. As a side effect, the scalar value of the array returned by $statement_handle->table is the same value as $statement_handle->numfields.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query('select myothertable.name, myothertable.date,
   mythirdtable.name from myothertable, mythirdtable where myothertable.name
   = mythirdtable.name');

my @tables = $output->table;
# @tables now contains ('myothertable', 'myothertable', 'mythirdtable')
Msql::Statement::type

@column_types  = $statement_handle->type;
Msql::Statement::type returns the types of the columns of data contained in the statement handle. When called in a scalar context the function returns a reference to an array. The pure value of this array is not of much use to most users (in the current implementation it is a list of integers). Rather, the values can be compared to the built-in values defined in Msql.pm such as &Msql::CHAR_TYPE and &Msql::INT_TYPE. One method of accessing this data is to build an array matching readable names to the predefined types. This method was demonstrated in
Chapter 10, "Perl". Another method is demonstrated below.

Example

use Msql;
my $db = Msql->connect;
$db->selectdb('mydata');
my $output = $db->query("select name, date from myothertable");

my ($name_type, $date_type) = $output->type;
for ($name_type) {
   $_ eq &Msql::CHAR_TYPE and do { print 'name is a CHAR'; last; }
   $_ eq &Msql::INT_TYPE and do { print 'name is an INT'; last; }
   # etc...
}
# repeat for $date_type
Msql::sock

$sock = $db->sock;
Msql::sock returns a scalar containing the number of the socket used to connect with the mSQL server. This is generally useful only for real nuts and bolts programming.

Example

use Msql;
my $db = Msql->connect;

my $sock = $db->sock;

print "I am connected on socket $sock.\n";
Msql::*_TYPE

Msql.pm provides the following defined functions
that correspond to the mSQL datatypes:

&Msql::CHAR_TYPE

&Msql::INT_TYPE

&Msql::REAL_TYPE

&Msql::IDENT_TYPE

&Msql::TEXT_TYPE

&Msql::IDX_TYPE

&Msql::NULL_TYPE

&Msql::DATE_TYPE

&Msql::UINT_TYPE

&Msql::MONEY_TYPE

&Msql::TIME_TYPE

&Msql::SYSVAR_TYPE

Example

use Msql;

%types = (
				'CHAR' => &Msql::CHAR_TYPE,
				'INT' => &Msql::INT_TYPE,
				'REAL' => &Msql::REAL_TYPE,
				'SYSVAR' => &Msql::SYSVAR_TYPE,
				'TIME' => &Msql::TIME_TYPE,
				'MONEY' => &Msql::MONEY_TYPE,
				'UINT' => &Msql::UINT_TYPE,
				'TEXT' => &Msql::TEXT_TYPE,
				'NULL' => &Msql::NULL_TYPE,
				'DATE' => &Msql::DATE_TYPE,
				'IDENT' => &Msql::IDENT_TYPE,
				'IDX' => &Msql::IDX_TYPE,
);
# $types{'CHAR'} is now an easily accessible alias for 
# &Msql::CHAR_TYPE. Having the values in %types gives you access to all of the
# handy hash functions such as keys() and values().
$Msql::VERSION

The $Msql::VERSION variable contains the version
of the Msql.pm module.

Example

use Msql;

print "You are using Msql.pm version $Msql::VERSION.\n";


Library Navigation Links

Copyright © 2001 O'Reilly & Associates. All rights reserved.

This HTML Help has been published using the chm2web software.