21.3.
Msql.pm API
This must be declared in every Perl program that uses the Msql.pm module.
$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');
$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";
$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";
$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 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");
($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 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";
@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;
@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.
@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);
$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";
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;
$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%;
$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 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[$_];
}
@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";
@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";
@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";
$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";
@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')
@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 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.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().
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";
![Previous](../gifs/txtpreva.gif) | ![Home](../gifs/txthome.gif) | ![Next](../gifs/txtnexta.gif) |
21.2.
DBI.pm API | ![Book Index](../gifs/index.gif) | 21.4.
Mysql.pm API |
Copyright © 2001 O'Reilly & Associates. All rights reserved.