Entry
Perl: Database: MySql: Connect: How: How access your MySql database with Perl? [Microsoft / Apache]
Dec 27th, 2004 18:44
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 27 December 2004 - 00:50 am -------------------
Perl: Database: MySql: Connect: How: How access your MySql database
with Perl? [Microsoft / Apache]
---
---
Steps: Overview:
1. In order to create database backed website you need each of the
following:
1. a web server
2. a scripting language
3. a database
The following is based on Windows / Apache / Perl / MySQL
2. Install your scripting language
1. Install Perl
1. Use ppm to install the MySQL module.
1. From the MSDOS Command Prompt type the following commands:
--- cut here: begin --------------------------------------------------
C:\> c:\perl\bin\ppm
PPM> install DBD-Mysql
Install package 'DBD-Mysql?' (y/N): y
Installing package 'DBD-Mysql'...
Bytes transferred: 24184
Installing C:\Perl\site\lib\auto\DBI\dbd_xsh.h
Installing C:\Perl\site\lib\auto\DBI\DBI.bs
Installing C:\Perl\site\lib\auto\DBI\DBI.dll
Installing C:\Perl\site\lib\auto\DBI\DBI.exp
Installing C:\Perl\site\lib\auto\DBI\DBI.lib
Installing C:\Perl\site\lib\auto\DBI\dbipport.h
Installing C:\Perl\site\lib\auto\DBI\DBIXS.h
Installing C:\Perl\site\lib\auto\DBI\dbi_sql.h
Installing C:\Perl\site\lib\auto\DBI\Driver.xst
Installing C:\Perl\site\lib\auto\DBI\Driver_xst.h
Installing C:\Perl\html\site\lib\DBI.html
Installing C:\Perl\html\site\lib\Win32\DBIODBC.html
Installing C:\Perl\html\site\lib\DBI\Changes.html
Installing C:\Perl\html\site\lib\DBI\DBD.html
Installing C:\Perl\html\site\lib\DBI\FAQ.html
Installing C:\Perl\html\site\lib\DBI\Profile.html
Installing C:\Perl\html\site\lib\DBI\ProfileData.html
Installing C:\Perl\html\site\lib\DBI\ProfileDumper.html
Installing C:\Perl\html\site\lib\DBI\ProxyServer.html
Installing C:\Perl\html\site\lib\DBI\PurePerl.html
Installing C:\Perl\html\site\lib\DBI\W32ODBC.html
Installing C:\Perl\html\site\lib\DBI\ProfileDumper\Apache.html
Installing C:\Perl\html\site\lib\DBI\DBD\Metadata.html
Installing C:\Perl\html\site\lib\DBI\Const\GetInfoReturn.html
Installing C:\Perl\html\site\lib\DBI\Const\GetInfoType.html
Installing C:\Perl\html\site\lib\DBI\Const\GetInfo\ANSI.html
Installing C:\Perl\html\site\lib\DBI\Const\GetInfo\ODBC.html
Installing C:\Perl\html\site\lib\DBD\Proxy.html
Installing C:\Perl\html\site\lib\Bundle\DBI.html
Installing C:\Perl\html\bin\dbiprof.html
Installing C:\Perl\html\bin\dbiproxy.html
Installing C:\Perl\site\lib\DBI.pm
Installing C:\Perl\site\lib\Win32\DBIODBC.pm
Installing C:\Perl\site\lib\DBI\Changes.pm
Installing C:\Perl\site\lib\DBI\DBD.pm
Installing C:\Perl\site\lib\DBI\FAQ.pm
Installing C:\Perl\site\lib\DBI\Profile.pm
Installing C:\Perl\site\lib\DBI\ProfileData.pm
Installing C:\Perl\site\lib\DBI\ProfileDumper.pm
Installing C:\Perl\site\lib\DBI\ProxyServer.pm
Installing C:\Perl\site\lib\DBI\PurePerl.pm
Installing C:\Perl\site\lib\DBI\W32ODBC.pm
Installing C:\Perl\site\lib\DBI\ProfileDumper\Apache.pm
Installing C:\Perl\site\lib\DBI\DBD\Metadata.pm
Installing C:\Perl\site\lib\DBI\Const\GetInfoReturn.pm
Installing C:\Perl\site\lib\DBI\Const\GetInfoType.pm
Installing C:\Perl\site\lib\DBI\Const\GetInfo\ANSI.pm
Installing C:\Perl\site\lib\DBI\Const\GetInfo\ODBC.pm
Installing C:\Perl\site\lib\DBD\ExampleP.pm
Installing C:\Perl\site\lib\DBD\NullP.pm
Installing C:\Perl\site\lib\DBD\Proxy.pm
Installing C:\Perl\site\lib\DBD\Sponge.pm
Installing C:\Perl\site\lib\Bundle\DBI.pm
Installing C:\Perl\bin\dbiprof
Installing C:\Perl\bin\dbiprof.bat
Installing C:\Perl\bin\dbiproxy
Installing C:\Perl\bin\dbiproxy.bat
Writing C:\Perl\site\lib\auto\DBI\.packlist
Bytes transferred: 179124
Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.bs
Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.dll
Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.exp
Installing C:\Perl\site\lib\auto\DBD\mysql\mysql.lib
Installing C:\Perl\html\site\lib\Mysql.html
Installing C:\Perl\html\site\lib\DBD\mysql.html
Installing C:\Perl\html\site\lib\DBD\mysql\INSTALL.html
Installing C:\Perl\html\site\lib\Bundle\DBD\mysql.html
Installing C:\Perl\site\lib\Mysql.pm
Installing C:\Perl\site\lib\Mysql\Statement.pm
Installing C:\Perl\site\lib\DBD\mysql.pm
Installing C:\Perl\site\lib\DBD\mysql\GetInfo.pm
Installing C:\Perl\site\lib\DBD\mysql\INSTALL.pod
Installing C:\Perl\site\lib\Bundle\DBD\mysql.pm
Writing C:\Perl\site\lib\auto\DBD\mysql\.packlist
PPM> exit
C:\> exit
--- cut here: end ----------------------------------------------------
3. Install your Web Server
1. Install Apache
4. Create scripting file which connects to your database
1. Create a file called
<your Apache directory>\cgi-bin\<your filename>
e.g.
c:\apache\cgi-bin\dddtest.htm
(thus store this file in the 'cgi-bin\' directory of your
Apache server)
--- cut here: begin --------------------------------------------------
#!C:/perl/bin/perl
use CGI qw(:standard);
use Mysql;
print header();
#Import the CGI parameters into the $Q structure
import_names();
#Connect to Andrew's MySQL server at Napier University
# ---
# To connect to your database: in general:
#
# my $dbh = DBI->connect( "dbi:mysql:database=<your databasename>;
# host=<your database computer IP address>;
# port=<your database computer port",
# "<your username>",
# "<your password>"
# );
my $dbh = DBI->connect( "dbi:mysql:database=gisq;
host=pc236nt.napier.ac.uk;
port=3306",
"scott",
"tiger"
);
# ---
# To run your SQL query: in general:
#
# my $sth = $dbh->prepare( "<your SQL query>" );
my $sth = $dbh->prepare( "SELECT DISTINCT region FROM cia" );
$sth->execute;
# ---
# to show the results of the SQL query:
print "<h1>Here are some regions</h1>\n";
while ( my ( $region ) = $sth->fetchrow_array() ) {
print "<a href='?region=$region'>$region<a><br/>\n";
}
if ( $Q::region ) {
print "<h1>Here are the countries of $Q::region</h1>";
my $sth = $dbh->prepare("SELECT name, population FROM cia where
region=?");
$sth->execute($Q::region);
while ( my ( $name, $pop ) = $sth->fetchrow_array() ) {
print "$name $pop<br/>\n";
}
}
# ---
$dbh->disconnect;
--- cut here: end ----------------------------------------------------
5. Possibly start your database
6. Start your Web server:
1. Start Apache
1. On the MSDOS command prompt type the following command
C:\apache\Apache.exe
(Note: adapt the file path to your computer)
(Note: you might have to type the full filepath, instead of
only 'apache')
6. Start your database
7. Check the result of your script
1. This is what it should look like
http://sqlzoo.napier.ac.uk/cgi-bin/cia.htm
1. The given program connects to a MySQL server running at
Napier University
2. To run this program also on your computer,
make sure your Webserver runs, then type
in your browser
http://localhost/cgi-bin/cia.htm
3. The output should look like the following in your browser:
--- cut here: begin --------------------------------------------------
Here are some regions
Africa
Antarctic Region
Arctic Region
Asia
Bosnia and Herzegovina, Europe
Central America and the Caribbean
Commonwealth of Independent States
Europe
Middle East
North America
Oceania
South America
South America, Central America and the Caribbean
Southeast Asia
World
--- cut here: end ----------------------------------------------------
4. And this is the output if you should run the script
from the MSDOS command line:
--- cut here: begin --------------------------------------------------
> perl dddtest.htm
ontent-Type: text/html; charset=ISO-8859-1
h1>Here are some regions</h1>
a href='?region=Africa'>Africa<a><br/>
a href='?region=Antarctic Region'>Antarctic Region<a><br/>
a href='?region=Arctic Region'>Arctic Region<a><br/>
a href='?region=Asia'>Asia<a><br/>
a href='?region=Bosnia and Herzegovina, Europe'>Bosnia and
Herzegovina, Europe<
><br/>
a href='?region=Central America and the Caribbean'>Central America and
the Cari
bean<a><br/>
a href='?region=Commonwealth of Independent States'>Commonwealth of
Independent
States<a><br/>
a href='?region=Europe'>Europe<a><br/>
a href='?region=Middle East'>Middle East<a><br/>
a href='?region=North America'>North America<a><br/>
a href='?region=Oceania'>Oceania<a><br/>
a href='?region=South America'>South America<a><br/>
a href='?region=South America, Central America and the
Caribbean'>South America
Central America and the Caribbean<a><br/>
a href='?region=Southeast Asia'>Southeast Asia<a><br/>
a href='?region=World'>World<a><br/>
--- cut here: end ----------------------------------------------------
---
---
Note:
Tested successfully on Microsoft Windows XP Professional,
running
-ActiveState Perl v5.6.1
-MySql v4.1
-Apache v2.0.52
I had already Apache installed on my computer, also MySql.
After typing
PPM
on the MSDOS command line,
then starting
apache.exe
Then saving the file
cia.htm
in the webserver's cgi-bin directory,
then typing
http://localhost/cgi-bin/cia.htm
in my Internet Explorer browser,
it worked immediately.
The whole setup took about 5 minutes.
Incredible.
---
---
Internet: see also:
---
[Internet: source: http://sqlzoo.net/web.htm]
---
Perl: Database: Link: Overview: Can you give an overview of links?
http://www.faqts.com/knowledge_base/view.phtml/aid/33044/fid/654
----------------------------------------------------------------------
