This is from quite a while ago (it's been a while since I've used MySQL) but
it should work as a simple example:
use DBI;
my $database_name = 'intra_data';
my $location = 'localhost';
my $port_num = '3306'; # This is default for mysql
# define the location of the sql server.
my $database = "DBI:mysql:$database_name:$location:$port_num";
my $db_user = "sa";
my $db_password = "sa";
# connect to the sql server.
my $dbh = DBI->connect($database,$db_user,$db_password);
my $sql_statement = "SELECT call_no FROM calltrac ORDER BY call_no DESC
LIMIT 1";
my $sth = $dbh->prepare($sql_statement);
my ($call_no);
$sth->execute() or die "Can't execute SQL statement : $dbh->errstr";
$sth->bind_columns(undef, \$call_no);
my $row;
while ($row = $sth->fetchrow_arrayref) {
print "$call_no\n";
};
That is a very simple example of a select to bring values into your script.
In this case, all we do with that value is to print one per row.
There are other examples of how to use the Prepare, execute, or do methods
in the perldoc DBI.
Not sure how your SQL is, but if you can endure a DBA diatribe, let me offer
a couple of suggestions:
1. Don't use shortcuts like: SELECT *
or INSERT INTO Table Values(
Always use explicit column lists. DBA's need to do things to databases
(like Merge replication for MS DBA's like myself - I'm sure other things for
MySQL) that may add columns to the table. That will break your code if you
do not use an explicit columnlist.
2. Learn and use ANSI join syntax - it'll make you life MUCH easier when you
do more complex things. I know of one app I deal with where correlated
subqueries are nexted 4 deep in the where clause because they apparently did
not want to use an outer join. The outer join would have been MUCH more
efficient.
Example of old syntax:
SELECT t1.Column1, t2.Column2 FROM table1 t1, table2 t2
where t1.column3 = t2.column3
and t1.column4 in ('value1', 'value2')
Better (ANSI) Syntax:
SELECT t1.Column2, t2.Column2
FROM table1 t1 INNER JOIN table2 t2 on t1.column3 = t2.column3
where t1.column4 in ('value1', 'value2')
You get the idea. Once you are familiar with it, the ANSI syntax makes the
queries much easier to write, debug, read, and if you need to do something
besides an inner join, or a cross join, it makes your life much, much
easier.
Ok, end of my DBA diatribe.
I might recommend also that you check out the DBI users group. I'm on that
one, and there are very, very slick examples of DBI programming given by
some very sharp people on that group. Just following the examples will help
you a lot.
Hope this helps,
Steve Howard
-----Original Message-----
From: Elaine -HFB- Ashton [mailto:[EMAIL PROTECTED]]
Sent: Sunday, June 17, 2001 4:32 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Need Perl/CGI/DBI/mysql Examples
[EMAIL PROTECTED] [[EMAIL PROTECTED]] quoth:
*>
*>I swear I wont be stealing anyones code for my own, all I want is just a
*>"road map" for my own educational purposes. If anyone wouldn`t mind
*>sharing or could point me in the right direction, it would be *immensely*
*>appreciated!
I was in the same predicament last fall when I wanted to build a very
simple little database driven web page. There is precious little out there
in the way of no frills db examples, however, O'Reilly publishes the code
for the DBI Perl book on their ftp site at
http://examples.oreilly.com/perldbi/ It helps to have the book to go along
with but chapter 8 is the one you'll probably find most helpful in the
examples.
http://bookmarks.cpan.org/search.cgi?cat=Database may also be helpful and
is the site I threw together after looking at the examples above.
enjoy.
e.
