MySQL Chapter Seven (The MySQL DBD perl API)

Documentation Version: 0.95
MySQL Version: 3.20.29

The MySQL DBD Perl Driver

(At present this is only a slightly warmed over version of the standard POD style documentation run through pod2html. -mke)

SUMMARY:

use DBI;
$dbh=DBI->connect($host,$database,$user,'mysql'[,$password]);
# or
$dbh = DBI->connect( '', '', '', 'mysql',
{ hostname => "localhost",
port     => 3333,
database => "test",
username => "myname",
password => "mypassword",
});

EXAMPLE:

#!/usr/bin/perl

use DBI;

$drh = DBI->install_driver('mysql');
$dbh=$drh->connect('','test','','') or die "Error
 connecting to database";

$dbh->do("DROP TABLE foo");
$dbh->do("CREATE TABLE foo (Field_1 VARCHAR(60))");

$count = 1;

while ($count < 500) {
$sth = $dbh->prepare("INSERT INTO foo VALUES('XXXYYYZZZ')");
$sth->execute;
$sth->finish;
# It is VERY important to call finish, otherwise
# you will loose the memory.
print "$count\n" unless $count % 10;
$count = $count + 1;
}

DBD::mysql is a database driver for the mysql database system. It is a port by Michael 'Monty' Widenius of Alligator Descartes' DBD::msql, and so closely resembles that driver in both features and limitations. You must have the DBI module installed before you can use DBD::mysql.

$host can be a simple hostname (``db.domain.com'') or a hostname and port (``db.domain.com:3333'').

$database is the name of the database to connect to.

$user is the username to connect as (leave this blank to connect as the username corresponding to the process' effective uid). $password is optional and should only be specified for accounts that have non blank passwords.

The hash keys which DBD::mysql uses are:

hostname database username password port

Values in the hash override values passed as earlier arguments. A TCP port number passed in the hash overrides a TCP port number passed in the hostname. You can enable debugging by setting the environment variable MYSQL_DEBUG to 'd:t:O,filename' (where filename is the file to log to). This will give a dbug log in filename if libmysql is compiled with DBUG support. (See the mysql documentation for more details on DBUG). The following tags are supported: TABLE TYPE IS_PRI_KEY IS_NOT_NULL LENGTH MAXLENGTH IS_KEY IS_BLOB IS_NUM To get the value of an AUTO_INCREMENT field after an INSERT, use: $id = $dbh->func("_InsertID");Because of limitations in how Perl deals with numeric values you will have problems using numbers larger than the size of a signed LONG. (2147483647). This can occur when using MySQL's unsigned LONG (DOUBLE) or LONGLONG (BIGINT) data types. Perl stores returned values as strings, but will automatically convert them to numbers when you use the values in a numeric context. This will cause them to be "truncated" to 2147483647 since Perl uses a signed LONG value to store such numbers. You can use the following work arounds. First of all, always treat values that may be large as strings. As long as you do this they can be displayed and re-inserted into a database without incident. The same goes for inserting new values into tables. If you set a variable called $tmpvar to be equal to "4147483647" and then INSERT it into your database you should be fine. Note that the ""'s are important here. If you need to do calculations involving large numbers you should do them via the initial SELECT. Escaping Binary Strings In order to insert binary data into the database you will have to escape certain characters. You may use the following method. $mystring = $dbh->quote($rawstring); AUTHOR Msqlperl is copyright (C)1997 Alligator Descartes. Modified to Msqlperl by Michael 'Monty' Widenius. All such changes are in public domain. The pod documentation, that was the basis for this chapter and _InsertID by Nathan Torkington. SEE ALSO DBI(3) for information on the use of DBD/DBI. http://www.hermetica.com/technologia/perl/DBI/ for DBI information, http://www.tcx.se/ for mysql information, http://www.hughes.com.au/for msql information.