Perl MySQL

The Perl - MySQL Libs (libraries) are what allow you to use Perl to access your databases. If you plan on using any scripts that require MySQL database access, you will need to install these.

Installing PerlMySQL

1. Click on the install icon ( ) next to PerlMySQL under the Database section. A new window will open.
2. Click on the Terms and Conditions link at the bottom of the new window. Another new window will open.
3. Read the terms and, if you agree, click the box ( ) next to I understand and agree with the Terms And Conditions.
4. Click the Install button.
5. Click the Finish button.
6. When PerlMySQL is successfully installed, the screen will tell you the installation was successful. This screen will also give you the link to your PerlMySQL test page. You may wish to bookmark it. Otherwise, you can access this page at any time in the future by clicking on PerlMySQL under the Database section of your Site Manager.
7. Close the window.

Using PerlMySQL

DBI is a generic interface for many databases. That means that you can write a script that works with many different database engines without change. You need a DataBase Driver (DBD) defined for each database type. For MySQL, this driver is called DBD::mysql. For more information on the Perl5 DBI, please visit the DBI web page and read the documentation at http://dbi.perl.org/.

For more information on Object Oriented Programming (OOP) as defined in Perl5, see the Perl OOP page at http://language.perl.com/info/documentation.html.

NOTE: If you want to use transactions with Perl, you need to have DBD-mysql version 1.2216 or newer. Version 2.1022 or newer is recommended.

If you have the MySQL module installed, you can find information about specific MySQL functionality with one of the following command:

shell> perldoc DBD/mysql
shell> perldoc mysql

MySQL-specific Methods

The Perl methods are described in more detail in the following sections. Variables used for method return values have these meanings:

• $dbh: Database handle
• $sth: Statement handle
• $rc: Return code (often a status)
• $rv: Return value (often a row count)

Portable DBI Methods

• connect ($data_source, $username, $password) : Use the connect method to make a database connection to the data source. The $data_source value should begin with DBI:driver_name: . Example uses of connect with the DBD::mysql driver:

$dbh = DBI->connect("DBI:mysql:$database", $user, $password);
$dbh = DBI->connect("DBI:mysql:$database:$hostname",
$dbh = DBI-$user, $password);
>connect("DBI:mysql:$database:$hostname:$port",
$user, $password);

If the user name and/or password are undefined, DBI uses the values of the DBI_USER and DBI_PASS environment variables, respectively. If you don't specify a hostname, it defaults to 'localhost.' If you don't specify a port number, it defaults to the default MySQL port (3306). As of Msql-Mysql-modules Version 1.2009, the $data_source value allows certain modifiers: mysql_read_default_file=file_name. Read `file_name' as an option file. For information on option files

• mysql_read_default_group=group_name: The default group when reading an option file is normally the [client] group. By specifying the mysql_read_default_group option, the default group becomes the [group_name] group.
• mysql_compression=1: Use compressed communication between the client and server (MySQL Version 3.22.3 or later).
• mysql_socket=/path/to/socket: Specify the pathname of the Unix socket that is used to connect to the server (MySQL Version 3.21.15 or later). Multiple modifiers may be given; each must be preceded by a semicolon. For example, if you want to avoid hardcoding the user name and password into a DBI script, you can take them from the user's `~/.my.cnf' option file instead by writing your connect call like this:
  $dbh = DBI->connect("DBI:mysql:$database"
  . ";mysql_read_default_file=$ENV{HOME}/.my.cnf",
  $user, $password);

This call will read options defined for the [client] group in the option file. If you wanted to do the same thing but use options specified for the [perl] group as well, you could use this:
$dbh = DBI->connect("DBI:mysql:$database"
. ";mysql_read_default_file=$ENV{HOME}/.my.cnf"
. ";mysql_read_default_group=perl",
$user, $password);

• disconnect: The disconnect method disconnects the database handle from the database. This is typically called right before you exit from the program. Example: $rc= $dbh->disconnect;
• prepare($statement): Prepares a SQL statement for execution by the database engine and returns a statement handle ($sth) , which you can use to invoke the execute method. Typically you handle SELECT statements (and SELECT -like statements such as SHOW , DESCRIBE , and EXPLAIN ) by means of prepare and execute . Example: $sth = $dbh->prepare($statement) or die "Can't prepare $statement: $dbh->errstr\n";
• If you want to read big results to your client you can tell Perl to use mysql_use_result() with: my $sth = $dbh->prepare($statement { "mysql_use_result" => 1});
• execute: The execute method executes a prepared statement. For non- SELECT statements, execute returns the number of rows affected. If no rows are affected, execute returns "0E0" , which Perl treats as zero but regards as true. If an error occurs, execute returns undef . For SELECT statements, execute only starts the SQL query in the database; you need to use one of the fetch_* methods described here to retrieve the data. Example: $rv = $sth->execute or die "can't execute the query: $sth->errstr;
• do($statement): The do method prepares and executes a SQL statement and returns the number of rows affected. If no rows are affected, do returns "0E0" , which Perl treats as zero but regards as true. This method is generally used for non- SELECT statements that cannot be prepared in advance (due to driver limitations) or that do not need to be executed more than once (inserts, deletes, etc.). Example: $rv = $dbh->do($statement) or die "Can't execute $statement: $dbh- >errstr\n";

NOTE: Generally the 'do' statement is much faster (and is preferable) than prepare/execute for statements that don't contain parameters.

• quote($string): The quote method is used to "escape" any special characters contained in the string and to add the required outer quotation marks. Example: $sql = $dbh->quote($string)
• fetchrow_array: This method fetches the next row of data and returns it as an array of field values. Example: while(@row = $sth->fetchrow_array) {print qw($row[0]\t$row[1]\t$row[2]\n);}
• fetchrow_arrayref: This method fetches the next row of data and returns it as a reference to an array of field values. Example: while($row_ref = $sth->fetchrow_arrayref) { print qw($row_ref->[0]\t$row_ref->[1]\t$row_ref->[2]\n); }
• fetchrow_hashref: This method fetches a row of data and returns a reference to a hash table containing field name/value pairs. This method is not nearly as efficient as using array references as demonstrated above. Example: while($hash_ref = $sth->fetchrow_hashref) { print qw($hash_ref->{firstname}\t$hash_ref->{lastname}\t\$hash_ref->{title}\n); }
• fetchall_arrayref: This method is used to get all the data (rows) to be returned from the SQL statement. It returns a reference to an array of references to arrays for each row. You access or print the data by using a nested loop. Example: my $table = $sth->fetchall_arrayref or die "$sth->errstr\n"; my($i, $j); for $i ( 0 .. $#{$table} ) { for $j ( 0 .. $#{$table->[$i]} ) {print "$table->[$i][$j]\t";} print "\n";}
• Indicates that no more data will be fetched from this statement handle. You call this method to free up the statement handle and any system resources associated with it. Example: $rc = $sth->finish;
• rows: Returns the number of rows changed (updated, deleted, etc.) by the last command. This is usually used after a non- SELECT execute statement. Example: $rv = $sth->rows;
• NULLABLE: Returns a reference to an array of values that indicate whether columns may contain NULL values. The possible values for each array element are 0 or the empty string if the column cannot be NULL , 1 if it can, and 2 if the column's NULL status is unknown. Example: $null_possible = $sth->{NULLABLE};
• NUM_OF_FIELDS: This attribute indicates the number of fields returned by a SELECT or SHOW FIELDS statement. You may use this for checking whether a statement returned a result: A zero value indicates a non- SELECT statement like INSERT, DELETE , or UPDATE . Example: $nr_of_fields = $sth->{NUM_OF_FIELDS};
• data_sources($driver_name): This method returns an array containing names of databases available to the MySQL server on the host 'localhost' . Example: @dbs = DBI->data_sources("mysql");
• ChopBlanks: This attribute determines whether the fetchrow_* methods will chop leading and trailing blanks from the returned values. Example: $sth->{'ChopBlanks'} =1; trace($trace_level)
• trace($trace_level, $trace_filename): The trace method enables or disables tracing. When invoked as a DBI class method, it affects tracing for all handles. When invoked as a database or statement handle method, it affects tracing for the given handle (and any future children of the handle). Setting $trace_level to 2 provides detailed trace information. Setting $trace_level to 0 disables tracing. Trace output goes to the standard error output by default. If $trace_filename is specified, the file is opened in append mode and output for all traced handles is written to that file. Example:
  DBI->trace(2); # trace everything
  DBI->trace(2,"/tmp/dbi.out"); # trace everything to
  # /tmp/dbi.out
  $dth->trace(2); # trace this database handle
  $sth->trace(2); # trace this statement handle

You can also enable DBI tracing by setting the DBI_TRACE environment variable. Setting it to a numeric value is equivalent to calling DBI->(value) . Setting it to a pathname is equivalent to calling DBI->(2,value).

MySQL-Specific Methods

The methods shown here are MySQL-specific and not part of the DBI standard. Several of them are now deprecated: is_blob , is_key , is_num , is_pri_key , is_not_null , length , max_length , and table. Where DBI -standard alternatives exist, they are noted here:

• Inserted: If you use the AUTO_INCREMENT feature of MySQL, the new auto-incremented values will be stored here. Example: $new_id = $sth->{insertid}; OR $dbh->{'mysql_insertid'}
• is_blob: Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a BLOB . Example: $keys = $sth->{is_blob};
• is_key: Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a key. Example: $keys = $sth->{is_key};
• is_num: Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column contains numeric values. Example: $nums = $sth->{is_num};
• is_pri_key: Returns a reference to an array of boolean values; for each element of the array, a value of TRUE indicates that the respective column is a primary key. Example: $pri_keys = $sth->{is_pri_key};
• is_not_null: Returns a reference to an array of boolean values; for each element of the array, a value of FALSE indicates that this column may contain NULL values. Example: $not_nulls = $sth->{is_not_null};

NOTE: is_not_null is deprecated; it is preferable to use the NULLABLE attribute (described above), because that is a DBI standard.

length

• max_length: Each of these methods returns a reference to an array of column sizes. The length array indicates the maximum possible sizes that each column may be (as declared in the table description). The max_length array indicates the maximum sizes actually present in the result table. Example: $lengths = $sth->{length}; $max_lengths = $sth->{max_length};
• NAME: Returns a reference to an array of column names. Example: names = $sth->{NAME};
• table: Returns a reference to an array of table names. Example: $tables = $sth->{table};
• type : Returns a reference to an array of column types. Example: $types = $sth->{type};

You can use the perldoc command to get more information about DBI.

perldoc DBI
perldoc DBI::FAQ
perldoc DBD::mysql

You can also use the pod2man , pod2html , etc., tools to translate to other formats.

You can find the latest DBI information at the DBI web page: http://dbi.perl.org/.

This manual was adapted from the original from the MySQL documentation and can be found in its original form here http://www.mysql.com/doc/en/Perl.html