Mysql.pm API (MySQL & mSQL)

21.4. Mysql.pm API

The Mysql.pm API is identical to the Msql API (with "Msql" replaced with "Mysql" in all places) except for the following differences.

Mysql::connect

$db = Mysql->connect($host, $database, $user, $password);

In addition to the three connect methods that are identical to Msql::connect, Mysql::connect provides a fourth method that requires an additional password argument. The first argument is the hostname or IP address of the MySQL server. If undef is passed as this argument, the module connects to the Unix-style socket on the localhost. The second argument is the name of the initial database to select. This can always be changed later with Mysql::selectdb. You may also supply undef as the second argument to select no initial database. The third argument is the username of the user connecting to the database. To successfully connect, the username must exist in the MySQL access tables. The final argument is the password of the user.

Example

use Mysql;

$db = Mysql->connect(undef,'mydata','webuser','super_secret_squirrel');
# The database handle is now connected to the local MySQL server using the
# database 'mydata'. The user name 'webuser' was used to connect who had
# the password 'super_secret_squirrel'.

Mysql::errno

$error_number = $db->errno;

Mysql::errno returns the error number of the last error. This error number corresponds to the error message returned from Msql::errmsg.

Example

use Mysql;
my $db = Mysql->connect(undef,'mydata','webuser','super_secret_squirrel');

# There is a parse error in this query...
my $output = $db->query('SELECT * from mydata');

if (not $output) {
				print "Error " . $output->errno . ": " . $output->errmsg . "\n";
}

Mysql::FIELD_TYPE_*

In addition to the Mysql::TYPE_* datatype
functions that are identical to the Msql::TYPE_*
functions, Mysql.pm provides these
extra datatype functions:

&Mysql::FIELD_TYPE_BLOB

&Mysql::FIELD_TYPE_CHAR

&Mysql::FIELD_TYPE_DECIMAL

&Mysql::FIELD_TYPE_DATE

&Mysql::FIELD_TYPE_DATETIME

&Mysql::FIELD_TYPE_DOUBLE

&Mysql::FIELD_TYPE_FLOAT

&Mysql::FIELD_TYPE_INT24

&Mysql::FIELD_TYPE_LONGLONG

&Mysql::FIELD_TYPE_LONG_BLOB

&Mysql::FIELD_TYPE_LONG

&Mysql::FIELD_TYPE_MEDIUM_BLOB

&Mysql::FIELD_TYPE_NULL

&Mysql::FIELD_TYPE_SHORT

&Mysql::FIELD_TYPE_STRING

&Mysql::FIELD_TYPE_TIME

&Mysql::FIELD_TYPE_TIMESTAMP

&Mysql::FIELD_TYPE_TINY_BLOB

&Mysql::FIELD_TYPE_VAR_STRING

Example

use Mysql;
my $db = Mysql->connect(undef,'mydata');
my $output = $db->query("SELECT name, data from myothertable");

if ($output->type->[0] = &Mysql::FIELD_TYPE_STRING) {
				print "Name is a STRING.\n";
}

Mysql::Statement::affectedrows

$number_of_affected_rows  = $statement_handle->affectedrows;

Msql::Statement::affectedrows returns the number of rows that were affected by the query. This function is useful since Mysql.pm returns a statement handle even on non-SELECT statements.

Example

use Mysql;
my $db = Mysql->connect(undef,'mydata');

my $output = $db->query("UPDATE mytable set name='bob' where name='joe'");
print $output->affectedrows . " rows were updated.\n";

Mysql::Statement::info

$info  = $sth->info;

Mysql::Statement::info returns extra results from certain queries that do not have specialized functions in Mysql.pm, such as ALTER TABLE and LOAD DATA INFILE. For example, when using LOAD DATA INFILE, Mysql::Statement::info returns the number of records inserted, the number deleted, the number skipped and the number of unparsable entries.

Example

use Mysql;
$db = Mysql->connect(undef,'mydata');

my $output = $db->query("LOAD DATA INFILE 'mydata.dat' INTO TABLE mytable");

my $info = $output->info($output);
print "LOAD DATA result: $info\n";

Mysql::Statement::insertid

$new_id  = $statement_handle->insertid;

Mysql::Statement::insertid returns the current value of the auto_increment field (if there is one) in the table. If there is no auto_increment field in the table, the function returns an undefined value undef.

Example

use Mysql;
my $db = Mysql->connect(undef,'mydata');

my $output = $db->query(
   "INSERT into mytable (id, name, date) VALUES ('','bob','today')";

my $new_id = $output->insertid;
print "Bob was entered with an ID number of $new_id.\n";

Mysql::Statement::isblob

@blobs  = $statement_handle->isblob;

Mysql::Statement::isblob returns a list of boolean values indicating if the fields contained in the statement handle are of a BLOB type. If called in a scalar context, the function returns a reference to an array.

Example

use Mysql;
$db = Mysql->connect(undef,'mydata');

my $output = $db->query('SELECT name, data from myothertable');

if ($output->isblob->[0]) {
				print "Name is a BLOB.\n";
} else {
				print "Name is not a BLOB.\n";
}

Mysql::query

Unlike Msql.pm, Mysql.pm returns a statement handle even with non-SELECT queries, such as INSERT, UPDATE, and DELETE.

Example

use Mysql;
my $db = Mysql->connect(undef,'mydata');

my $output = $db->query("UPDATE mytable set name='joe' where name='bob'");
# $output is a statement handle.