SQL syntax error

am 11.04.2004 08:14:32 von amy parness

well I solved all the var problems but now I am getting

BD::mysql::st execute failed: You have an error in your SQL syntax near
'between '04-02-09' AND '04-04-04' AND hour(timedatain) between 6 AND 12' at
line 1 at livesensorreadings5.pl line 185.

but it works perfectly in the MYSQL terminal without the checkbox. Can I not
say $checkboxmornValue eq "on"? Or is it an error in my SQL syntax?

if ($checkboxmornValue eq "on")
{ # morning is between >6 and <12
$SqlStatement = "SELECT avg(readings) from data";
$SqlStatement .= "WHERE timedatain between '$datestart'
AND '$dateend' AND hour(timedatain) between 6 AND 12";
$sth = $dbh->prepare($SqlStatement);
$sth->execute();
while (@row_array = $sth->fetchrow_array())
{ $morning = $row_array[0];
print "$morning
\n";
}
$sth->finish();
}

Thanks. This is my first Perl project.

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=gcdmp-msql-mysql-modules@m .gmane.org

Re: SQL syntax error

am 11.04.2004 14:03:21 von Ulrich Borchers

On 11 Apr 2004 at 2:14, amy parness wrote:

[...]
> $SqlStatement =3D "SELECT avg(readings) from data";
> $SqlStatement .=3D "WHERE timedatain between '$datestart'

There is no whitespace between "data" and "WHERE".
Your query looks like this: "... from dataWHERE ..."

Uli

--
Ulrich Borchers
Brandenberger Str.18
41065 Mönchengladbach
Tel. (0 21 61) 17 58 83
Mobil (0 179) 72 66 112

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: SQL syntax error

am 11.04.2004 14:03:21 von Ulrich Borchers

Re: SQL syntax error

am 11.04.2004 14:34:09 von Jamie McCarthy

amp318@nyu.edu (amy parness) writes:

> well I solved all the var problems but now I am getting
>=20
> BD::mysql::st execute failed: You have an error in your SQL
> syntax near 'between '04-02-09' AND '04-04-04' AND
> hour(timedatain) between 6 AND 12' at line 1 at
> livesensorreadings5.pl line 185.
>=20
> but it works perfectly in the MYSQL terminal without the
> checkbox.

No, something _else_ works perfectly. :)

> Can I not say $checkboxmornValue eq "on"? Or is it an error in
> my SQL syntax?

It's an error in your SQL syntax.

> Thanks. This is my first Perl project.

Cool. One of the tools that's going to help you debug is to print
data. When you're stuck and can't figure out what's wrong, usually
the problem is that you, the human, need more information. No need
to give up -- and no need to email a mailing list, which should be
one of the last steps before giving up! :) You have plenty more
tricks in your debugging toolkit.

In this case, you believe something about your program that isn't
true, so you need to debug in a way that will resolve that.=20
Printing data as your program runs will probably do the trick.
Even data that you are 100% sure is X can still be Y -- print its
value while the program runs, and find out!

I assume, since you have something about a checkbox, that this
code is running on a webserver. One way to use debug prints, then,
is to print them as HTML comments:

print "\n\n";

and then go looking for the information using View Source in your
browser. That will probably work in this case, since your Perl
script isn't actually crashing, it's just failing.

Another way is to print debug info to STDERR:

print STDERR scalar(localtime) . " SqlStatement=3D'$SqlStatement'\n";

and then keep a "tail -f whatever.error_log" running in a window.
(Where the STDERR output goes probably depends on what webserver
you're running. Check its documentation and configuration files.)

Good luck!
--=20
Jamie McCarthy
http://mccarthy.vg/
jamie@mccarthy.vg

--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=3Dgcdmp-msql-mysql-modules @m.gmane.org

Re: SQL syntax error

am 11.04.2004 14:34:09 von Jamie McCarthy