unable to execute mysql query in perl while connecting to remote server

Question

I have connected to one MySQL database which have been hosted in a remote server in Perl. Now I am trying to execute a select statement on a table in subject.pl file using Perl command line. The code is

#!/usr/bin/perl

use DBI;
use strict;

# Connected to mysql audit database in dev server
my $dsn = 'DBI:mysql:Driver={mysql}';
my $host = 'dev-mysql.learn.local';
my $database = 'subject';
my $user = 'testUser';
my $auth = 'testPassword';
my $dbh = DBI->connect("$dsn;host=$host;Database=$database",$user,$auth) or die            "Database connection not made: $DBI::errstr";

# Prepare query
my $sql = "SELECT
        subject_id
        ,subject_value
    FROM
        subject";
my $sth = $dbh->prepare($sql);

#Execute the statement
$sth->execute() or die "Unable to execute".$sth->errstr;

while (my @row = $sth->fetchrow_array()) {
   my ($subject_id, $subject_value ) = @row;
   print "$subject_id,$subject_value,$subject_db_field\n";
}

$sth->finish();

I am getting error at line $sth->execute() or die "Unable to execute".$sth->errstr;

The error message is Unable to execute at D:\Demo\perl_demo\subject.pl line 24.

But when I am printing the $dbh variable, its giving the result like DBI::db=HASH(0x1ca7884). So I guess the connection is establishing properly.

Please help me to fix this issue as I am completely new in Perl scripting.

you surely have connection as otherwise it would die at connect (connect.. or die ..) — mpapec
– mpapec, Commented Nov 21, 2013 at 8:23
Yes Frank, I have checked the return value of $dbh->prepare($sql). Its giving DBI::db=HASH(0x1ca74f4) — Kanhu Charan Sahu
– Kanhu Charan Sahu, Commented Nov 21, 2013 at 8:55

mpapec · Accepted Answer · 2013-11-21 08:08:23Z

1

Check for errors on prepare,

my $sth = $dbh->prepare($sql) or die $dbh->errstr;

answered Nov 21, 2013 at 8:08

mpapec

50.7k8 gold badges71 silver badges133 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Kanhu Charan Sahu Over a year ago

I have checked with pitting your code. But its not throwing any error. When I print the $sth variable, its giving result like DBI::db=HASH(0x1ca74f4)

mpapec Over a year ago

@KanhuCharanSahu it is strange that $sth->execute() or die $sth->errstr; doesn't report actual error message?

Kanhu Charan Sahu Over a year ago

is it due to the remote database?

mpapec Over a year ago

@KanhuCharanSahu no, DBI should always have error message when something fails.

mpapec Over a year ago

@KanhuCharanSahu you can set {RaiseError => 1} as fourth argument to connect

|

Collectives™ on Stack Overflow

unable to execute mysql query in perl while connecting to remote server

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related