Database Reference
In-Depth Information
my $dbh = DBI -> connect ( "DBI:mysql:$database:$host" , $user, $password)
|| die "Could not connect to database: " . DBI -> errstr ;
...
The first two lines start Perl and set a useful condition for reducing programming errors
(i.e., use strict ). The next line calls the DBI module. Then we create a set of vari-
ables containing values for logging into MySQL. The next statement, which is spread over
two lines, sets up a database handle ( $dbh ) that specifies the database engine ( mysql ).
We give it the login variables. The rest of the statement relates to what to do if the pro-
gram is unable to connect to MySQL. If the connection is successful, though, the program
will continue on.
Querying MySQL
Making aconnection to MySQL does little good unless an SQL statement is executed.
Any SQL statement can be executed through an API. The only restrictions are those im-
posed by the MySQL server on the user account executing the SQL statements within the
application. If the user account can execute only SELECT statements, that's all that the
application may execute. Let's look at some examples here of how to select and insert
data in MySQL through an application.
Selecting data
Continuing the previous example, let's execute a SELECT to get a list of birds from the
birds table. Let's allow the user of the Perl program to specify a common name of birds
to select, when executing it from the command line. For instance, the user might enter
Avocet to get a list of Avocet birds. We'll use a LIKE operator in the WHERE clause to al-
low for some flexibility. Here's how the code for that would look:
...
my $search_parameter = shift ;
my $sql_stmnt = "SELECT human_id,
CONCAT(name_first, SPACE(1), name_last) AS
full_name,
membership_expiration
FROM humans
WHERE name_last LIKE ?" ;
my $sth = $dbh-> prepare ($sql_stmnt);
$sth-> execute ( "%$search_parameter%" );
...
Search WWH ::




Custom Search