use Msql;

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);

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;

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);

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";

$error = $db->errmsg;

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);

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";

$host = $db->host;

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;

Example

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

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

@index_handles = $db->listindex($table,$index);

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;

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);

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";

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);

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);

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'

$result = $db->shutdown;

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";

$formatted_table = $statement_handle->as_string;

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.

$statement_handle->dataseek($row_number);

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".

@column_of_data = $statement_handle->fetchcol($column_number);

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";
}

%hash = $statement_handle->fetchhash;

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.

@row_of_data = $statement_handle->fetchrow;

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...

@not_null  = $statement_handle->isnotnull;

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;

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];

@primary_key  = $statement_handle->isprikey;

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;

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!\";
   }
}

@indices = $statement_handle->listindices;

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";

@max_lengths  = $statement_handle->maxlength;

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;

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'

$number_of_fields = $statement_handle->numfields;

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;

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;

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')

$sock = $db->sock;

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";