use strict; use DBI; # Connect to the database # See footnote 1 my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_password') or die "Couldn't open database: $DBI::errstr; stopped"; # Prepare the SQL query for execution my $sth = $dbh->prepare(<execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; # Fetch each row and print it while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) { print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n"; } # Disconnect from the database $dbh->disconnect(); #### perldoc DBD::Oracle #### # Connect to the database my $dbh = DBI->connect('DBI:mysql:my_database', 'my_username', 'my_password') or die "Couldn't open database: '$DBI::errstr'; stopped"; #### my $sth = prepare(q{SELECT foo FROM bar WHERE baz='bap'}) or die "Couldn't prepare statement: $DBI::errstr; stopped"; #### $sth->execute() or die "Couldn't execute statement: $DBI::errstr; stopped"; #### # Fetch each row and print it while ( my ($field1, $field2, $field3) = $sth->fetchrow_array() ) { print STDOUT "Field 1: $field1 Field 2: $field2 Field 3: $field3\n"; } #### # Fetch each row and print it-- fetchrow_arrayref while ( my $fields = $sth->fetchrow_arrayref() ) { print STDOUT "Field 1: $fields->[0] Field 2: $fields->[1] Field 3: $fields->[2]\n"; } #### # Fetch each row and print it-- fetchrow_hashref while ( my $field_hash = $sth->fetchrow_hashref() ) { print STDOUT "Field 1: $field_hash->{'field1'} Field 2: $field_hash->{'field2'} Field 3: $field_hash->{'field3'}\n"; } #### # A bad way of doing it while (my $furniture_name = ) { chomp($furniture_name); my $sth = $dbh->prepare("SELECT price FROM furniture_prices WHERE furniture_name='$furniture_name'") or die "Couldn't prepare: '$DBI::errstr'; stopped"; $sth->execute() or die "Couldn't execute: '$DBI::errstr'"; my ($price) = $sth->fetchrow_array(); print STDOUT "Item: $furniture_name Price: $price\n"; } #### # A better way of doing it my $sth = $dbh->prepare("SELECT price FROM furniture_prices WHERE furniture_name=?") or die "Couldn't prepare: '$DBI::errstr'; stopped"; while (my $furniture_name = ) { chomp($furniture_name); $sth->execute($furniture_name) or die "Couldn't execute: '$DBI::errstr'"; my ($price) = $sth->fetchrow_array(); print STDOUT "Item: $furniture_name Price: $price\n"; } #### # One way of reading multiple rows my $sth = $dbh->prepare("SELECT furniture_name, price FROM furniture_prices WHERE furniture_type=?") or die "Couldn't prepare: '$DBI::errstr'; stopped"; while (my $furniture_type = ) { chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::errstr'"; while ( my ($furniture_name, $price) = $sth->fetchrow_array() ) { print STDOUT "Item: $furniture_name Price: $price\n"; } } #### $sth->bind_col($column, \$scalar) #### # Faster way of reading multiple rows my $sth = $dbh->prepare("SELECT furniture_name, price FROM furniture_prices WHERE furniture_type=?") or die "Couldn't prepare: '$DBI::errstr'; stopped"; while (my $furniture_type = ) { my ($furniture_name, $price); chomp($furniture_type); $sth->execute($furniture_type) or die "Couldn't execute: '$DBI::errstr'"; $sth->bind_col(1, \$furniture_name); $sth->bind_col(2, \$price); while ( $sth->fetch() ) { print STDOUT "Item: $furniture_name Price: $price\n"; } }