Pulling data based on date time returns error

[xhoster]

Data is stored in MySQL as '2006-07-14 13:44:13'

Select Result from DataListing where
InputDate = "2006-07-14 13:44:13"
having count(*) > 1;

DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '"Select Result from DataListing where
InputDate = "2006-07-14 13:' at line 1 at
copiesinDATA.pl line 29.

It would seem that your Perl quoting and your mysql quoting are somehow
interfering with each other. As you haven't shown us any Perl, there
isn't much we can do for you.

BTW, using placeholders/bind variables often minimizes these quoting
problems.

Xho

 

[devon_banks]

Data is stored in MySQL as '2006-07-14 13:44:13'

Select Result from DataListing where
InputDate = "2006-07-14 13:44:13"
having count(*) > 1;

DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '"Select Result from DataListing where
InputDate = "2006-07-14 13:' at line 1 at
copiesinDATA.pl line 29.

 

[Paul Lalli]

Data is stored in MySQL as '2006-07-14 13:44:13'

Select Result from DataListing where
InputDate = "2006-07-14 13:44:13"
having count(*) > 1;

DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '"Select Result from DataListing where
InputDate = "2006-07-14 13:' at line 1 at
copiesinDATA.pl line 29.

What exactly is your Perl question? You have an SQL syntax error.
Take that SQL statement and use it in MySQL's native interphase. What
results do you get?

Paul Lalli

 

[Brian McCauley]

Data is stored in MySQL as '2006-07-14 13:44:13'

Select Result from DataListing where
InputDate = "2006-07-14 13:44:13"
having count(*) > 1;

DBD::mysql::st execute failed: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use near '"Select Result from DataListing where
InputDate = "2006-07-14 13:' at line 1 at
copiesinDATA.pl line 29.

I would guess that you have an error in your SQL syntax; check the
manual that corresponds to your MySQL server version for the right
syntax.

BTW, this has nothing to do with Perl.

Random short in the dark: standard SQL uses single not double quotes.

Random short in the dark: Your "having" clause looks odd. There's no
"group by".

 

[devon_banks]

This should have been:
Select Result,count(*) from DataListing where
InputDate = "2006-07-14 13:44:13"
group by Result
having count(*) > 1;
I finally fixed the problem
by putting
$SQL = "Select Result,count(*) from DataListing where
InputDate = "2006-07-14 13:44:13"
group by Result
having count(*) > 1";

my $sth = $dbh->prepare(qq{$SQL});
$sth->execute ();

instead of

my $sth = $dbh->prepare(qq{"Select Result,count(*) from DataListing
where
InputDate = "2006-07-14 13:44:13"
group by Result
having count(*) > 1"});
$sth->execute ();

 

[J. Gleixner]

This should have been:
Select Result,count(*) from DataListing where
InputDate = "2006-07-14 13:44:13"
group by Result
having count(*) > 1;
I finally fixed the problem
by putting
$SQL = "Select Result,count(*) from DataListing where
InputDate = "2006-07-14 13:44:13"
group by Result
having count(*) > 1";

Really?? That doesn't even compile.

my $SQL = q {
Select Result,count(*)
from DataListing
where InputDate = "2006-07-14 13:44:13"
group by Result
having count(*) > 1
};

You'd be much better off using placeholders. e.g.

my $date = '2006-07-14 13:44:13';
my $SQL = q {
Select Result,count(*)
from DataListing
where InputDate = ?
group by Result
having count(*) > 1
};

my $sth = $dbh->prepare( $SQL );
$sth->execute( $date );