Thread [DBI] Syntaxfehler DBD::mysql::st execute failed und DBD::mysql::db selectrow_array failed (5 answers)
Opened by saschaw at 2017-01-24 15:41

saschaw
 2017-01-24 15:41
#185920 #185920
User since
2017-01-23
2 Artikel
BenutzerIn
[default_avatar]
Hallo,

ich habe ein Problem mit meinem wetter.cgi, wo ich irgendwie nicht weiterkommen. Wenn ich die Syntax mit perl -c teste, bekomme ich folgende Meldungen:
Code: (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
defined(%hash) is deprecated at wetter.cgi line 1742.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 2110.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 5935.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 6142.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 6175.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 6371.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 8699.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 8741.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 9960.
        (Maybe you should just omit the defined()?)
defined(%hash) is deprecated at wetter.cgi line 10231.
        (Maybe you should just omit the defined()?)
wetter.cgi syntax OK

Die Syntax scheint ok und die anderen Meldungen, kann man (lt. Internet) ignorieren bzw. scheinen nur Warnungen zu sein(?).
Wenn ich dann aber das wetter.cgi im Broswer aufrufe bekomme ich folgende zwei Fehlermeldungen:
1. Fehlermeldung
Code: (dl )
1
2
3
4
5
6
7
8
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 'AND sensid=1 ORDER by datetime DESC LIMIT 2' at line 1 
at /var/www/cgi-bin/wetter.cgi line 7184. 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 'AND sensid=1 
ORDER by datetime DESC LIMIT 2' at line 1 at /var/www/cgi-bin/wetter.cgi 
line 7184.

Hier der Code (Zeile 7184 ist hier Zeile 17):
Code: (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
    # Iterate over all sensorIds of one sensortype
      foreach $j ( @{${$sens}{$i}->{"sensorids"}} ){
	 $dbColName=$dbcols;
	 # The sensorids defined by the user in latest_th[] may be of the format
	 # sensorId.stationid. Here we have to split both values to get the right data
	 ($sensorId, $station)=split(/\./o, $j, 2);
	 $stationId=$station if( length($station) );
         # Only start sql query if there are dbcols to be retrieved by the sensors
	 # description and if this value is != "-" (rainsensor). The rainsensor is
	 # handled differently below
      	 if( length($dbcols) && $dbcols ne "-" ){
	    $sql="SELECT $qdbcols FROM $table WHERE stationid = $stationId AND sensid=$sensorId";
	    #$sql.=" ORDER by datetime DESC,id DESC LIMIT 2";
	    $sql.=" ORDER by datetime DESC LIMIT 2";
	    # Query database for min, max and average of sensor
	    $sth = $dbh->prepare($sql);
	    $sth->execute;
	    # Fetch the latest rows. We need two for values that encode a difference
	    # like the rain sensor
	    @res= $sth->fetchrow_array;
#	    @res1=$sth->fetchrow_array if( ${$sens}{$i}->{"type"}=~/RA/o );
	 }

Die zweite Fehlermeldung die ich beim Aufruf bekomme ist:
Code: (dl )
1
2
3
4
5
6
7
8
9
DBD::mysql::db selectrow_array 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 'AND datetime>="2017-01-22 23:00:00" AND 
LEFT(datetime,10) <="2017-01-23 22:59:59' at line 1 at /var/www/cgi-bin
/wetter.cgi line 7212. DBD::mysql::db selectrow_array 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 'AND datetime>="2017-01-22 
23:00:00" AND LEFT(datetime,10) <="2017-01-23 22:59:59' at line 1 at 
/var/www/cgi-bin/wetter.cgi line 7212.

Der Code hierzu (Zeile 7212 ist hier Zeile 19):
Code: (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
if( ${$sens}{$i}->{"datediff"} ){
	    $dbColName=${$sens}{$i}->{"dbcolName"};
	    $unitFactor=${$sens}{$i}->{"unitfactor"}->{"$dbColName"};

            $midnightDate=${$sens}{$i}->{"datediff"};  # date of day for which to calculate sum
	    $midnightTime="00:00:00";                  # starting from midnight
	    $dayEndTime="23:59:59";		       # Day end time
	    #
	    # Since the database stores dates in GMT we have to calculate what GMT time 
	    # corresponds to the local time "midnight" 
      	    ($midnightDateGMT, $midnightTimeGMT)=timeConvert($midnightDate, $midnightTime, "GMT");
	    # Now calculate what GMT time corresponds to local time "end of day"
      	    ($midnightDate, $dayEndTime)=timeConvert($midnightDate, $dayEndTime, "GMT");
    
	    # Now select data that is between midnight(GMT) and end of day(GMT)
      	    $sql="SELECT SUM($dbColName) AS \"SUM\" FROM $table WHERE stationid = $stationId AND".
	         " datetime>=\"$midnightDateGMT $midnightTimeGMT\" AND".
	         " LEFT(datetime,10) <=\"$midnightDate $dayEndTime\" AND sensid=$sensorId";
	    @res=$dbh->selectrow_array($sql);
            $tmp=$res[0] * $unitFactor;
            $tmp=int($tmp*100)/100;
    	    ${$sens}{$i}->{"sensorval"}->{"$j"}->[0]->{$dbColName}=$tmp;
	 }

Ich weiß jetzt ehrlich gesagt nicht wo der Fehler ist, habe schon Stundenlang gesucht, ohne Erfolg. Ihr seit Experten und deshalb hoffe ich, dass mir jemand helfen kann.
Gruß
Sascha
Last edited: 2017-01-24 16:01:12 +0100 (CET)

View full thread [DBI] Syntaxfehler DBD::mysql::st execute failed und DBD::mysql::db selectrow_array failed