bravohe / sma-bluetooth

Automatically exported from code.google.com/p/sma-bluetooth
0 stars 0 forks source link

Query getting any result from mysqldb, no output to PVoutput.org #118

Open GoogleCodeExporter opened 9 years ago

GoogleCodeExporter commented 9 years ago
What steps will reproduce the problem?
1. Using SMA STP 8000TL
2. SMATOOL 18
3. Edited smatool.c and added (close to line 2580)

sprintf(SQLQUERY,"SELECT DATE_FORMAT(dd1.DateTime,\'%%Y%%m%%d\'), 
DATE_FORMAT(dd1.DateTime,\'%%H:%%i\'), 
ROUND((dd1.ETotalToday-dd2.EtotalToday)*1000), dd1.CurrentPower, dd1.DateTime 
FROM DayData as dd1 join DayData as dd2
on dd2.DateTime=DATE_FORMAT(dd1.DateTime,\'%%Y-%%m-%%d 00:00:00\') WHERE 
dd1.DateTime>=Date_Sub(CURDATE(),INTERVAL 13 DAY) and dd1.PVOutput IS NULL and 
dd1.CurrentPower>0 ORDER BY dd1.DateTime ASC" );

if (debug == 1) printf("%s\n",SQLQUERY);
        if (debug == 1) printf("Einde van query \n");
        DoQuery(SQLQUERY);
        if (debug ==1) printf ("Number of rows: %lu\n", (unsigned long) mysql_num_rows(res));
4. When running ./smatool -d -v > test.txt

What is the expected output? What do you see instead?

Expected to data to be exported to pvoutput.org.
Data is exported to mysql database, but it stops at the mysql query.
that is why i added "printf ("Number of rows: %lu\n", (unsigned long) 
mysql_num_rows(res));" witch results in:

Einde van query 
Number of rows: 0

So i am sure that there is a problem with my query.

here are some lines of my database they look fine:

2013-03-02 18:10:00 8000TL  2110365778  0   5730400 NULL    0000-00-00 00:00:00
2013-03-02 18:05:00 8000TL  2110365778  72  5730400 NULL    0000-00-00 00:00:00
2013-03-02 18:00:00 8000TL  2110365778  72  5730394 NULL    0000-00-00 00:00:00
2013-03-02 17:55:00 8000TL  2110365778  48  5730388 NULL    0000-00-00 00:00:00
2013-03-02 17:50:00 8000TL  2110365778  72  5730384 NULL    0000-00-00 00:00:00
2013-03-02 17:45:00 8000TL  2110365778  108 5730378 NULL    0000-00-00 00:00:00
2013-03-02 17:40:00 8000TL  2110365778  120 5730369 NULL    0000-00-00 00:00:00
2013-03-02 17:35:00 8000TL  2110365778  144 5730359 NULL    0000-00-00 00:00:00
2013-03-02 17:30:00 8000TL  2110365778  156 5730347 NULL    0000-00-00 00:00:00
2013-03-02 17:25:00 8000TL  2110365778  180 5730334 NULL    0000-00-00 00:00:00
2013-03-02 17:20:00 8000TL  2110365778  192 5730319 NULL    0000-00-00 00:00:00
2013-03-02 17:15:00 8000TL  2110365778  216 5730303 NULL    0000-00-00 00:00:00
2013-03-02 17:10:00 8000TL  2110365778  252 5730285 NULL    0000-00-00 00:00:00
2013-03-02 17:05:00 8000TL  2110365778  300 5730264 NULL    0000-00-00 00:00:00
2013-03-02 17:00:00 8000TL  2110365778  312 5730239 NULL    0000-00-00 00:00:00
2013-03-02 16:55:00 8000TL  2110365778  312 5730213 NULL    0000-00-00 00:00:00
2013-03-02 16:50:00 8000TL  2110365778  312 5730187 NULL    0000-00-00 00:00:00
2013-03-02 16:45:00 8000TL  2110365778  288 5730161 NULL    0000-00-00 00:00:00
2013-03-02 16:30:00 8000TL  2110365778  408 5730076 NULL    0000-00-00 00:00:00
2013-03-02 16:25:00 8000TL  2110365778  468 5730042 NULL    0000-00-00 00:00:00
2013-03-02 16:20:00 8000TL  2110365778  528 5730003 NULL    0000-00-00 00:00:00

I tried some other query's from other people and some old issues but didn't 
seem to work ...

Can somone help me adjust the query because i don't understand it and don't now 
what to change to get it working?

Any help is greatly appreciated!!

What version of the product are you using? On what operating system?

Smabluetooth 18

Raspberry Pi wheezy raspbian

Please provide any additional information below.

Kind regards,
Filip

Original issue reported on code.google.com by Filip.Sm...@gmail.com on 2 Mar 2013 at 8:01

GoogleCodeExporter commented 9 years ago
I tried http://code.google.com/p/sma-bluetooth/issues/detail?id=91&sort=-id 
Going to see what happens tomorrow.

Original comment by Filip.Sm...@gmail.com on 3 Mar 2013 at 12:22

GoogleCodeExporter commented 9 years ago
Seems to fix the problem ;)

Original comment by Filip.Sm...@gmail.com on 3 Mar 2013 at 9:28

GoogleCodeExporter commented 9 years ago
Ill Also post my Inv codes I used for my sma:

Inverter code SMA STP 8000TL latest firmware 2.54 (European)

Inverter 8000TL 
Code1 0x3e 
Code2 0x70 
Code3 0xf9 
Code4 0x39 
InvCode 0x80

Also I made an modification to my smatool.c

I commented out the Date error lines:

Cause it gave me trouble sometimes:

if( idate != prev_idate+300 ) {
                                            printf( "Date Error! prev=%d current=%d\n", (int)prev_idate, (int)idate );
                                            //error=1;
                                            //break;
                                         }

Next i didn't want to run a script to clean the Database of false data, so i 
made a modification that only valid data can go in my database:

if ((mysql ==1)&&(error==0)){
        /* Connect to database */
        OpenMySqlDatabase( conf.MySqlHost, conf.MySqlUser, conf.MySqlPwd, conf.MySqlDatabase );
        for( i=1; i<archdatalen; i++ ) //Start at 1 as the first record is a dummy
        {
            //convert into year earase 1970
            struct tm *tempdate = gmtime( &(archdatalist+i)->date);
            if (debug == 1) printf( "Year = %d\n", tempdate->tm_year+1900);

            //print data
            if (debug == 1) printf("CurrentPower %0.f\n",(archdatalist+i)->current_value);
            if (debug == 1) printf("Currentdate %ld\n",(archdatalist+i)->date);

            // remove wrong data 
            if ((((archdatalist+i)->current_value) < 9000) && (((archdatalist+i)->current_value) >= 0) && (tempdate->tm_year+1900 > 1970))
            {

                    sprintf(SQLQUERY,"INSERT INTO DayData ( DateTime, Inverter, Serial, CurrentPower, EtotalToday ) VALUES ( FROM_UNIXTIME(%ld)$
                    if (debug == 1) printf("%s\n",SQLQUERY);
                    if (debug == 1) printf("Valid data\n");
                    DoQuery(SQLQUERY);
             }
        }
        mysql_close(conn);
    }

enclosed is my smatool.c.

Hope it can help someone in the future!

Original comment by Filip.Sm...@gmail.com on 3 Mar 2013 at 9:51

Attachments:

GoogleCodeExporter commented 9 years ago
Also i like to notice, i get allot of date errors when i run smatool -d -v BUT 
when i run smatool -v there are almost none!

Repost also doesn't work for me like i want, pvoutput only allows 60 updates in 
one hour. repost posts everything  separated so it doesn't combine the outputs.

I fixed repost with bash script at the moment, by setting PVoutput back to NULL 
and running ./smatool again:

#!/bin/bash
#

cat << EOF | mysql --password=raspberry -u Pi1 smatool | tee -a logfile.log

SELECT * FROM DayData WHERE DateTime LIKE "2013-03-04 %%:%%:%%" ORDER BY 
DateTime DESC;
UPDATE DayData SET PVOutput = NULL WHERE  DateTime LIKE "2013-03-04 %%:%%:%%" 
ORDER BY DateTime DESC;

#SELECT * FROM DayData WHERE CurrentPower>9000;
#DELETE FROM DayData WHERE CurrentPower>9000;
#SELECT * FROM DayData WHERE CurrentPower<=0;
#DELETE FROM DayData WHERE CurrentPower<=0;
#SELECT * FROM DayData WHERE DateTime LIKE "1970-%%-%% %%:%%:%%";
#DELETE FROM DayData WHERE DateTime LIKE "1970-%%-%% %%:%%:%%";

EOF

Original comment by Filip.Sm...@gmail.com on 5 Mar 2013 at 12:12

GoogleCodeExporter commented 9 years ago
Next i changed 

sprintf(SQLQUERY,"INSERT INTO DayData ( DateTime, Inverter, Serial, 
CurrentPower, EtotalToday, PVOutput ) VALUES ( 
FROM_UNIXTIME(%ld),\'%s\',%ld,%0.f, %.3f,FROM_UNIXTIME(%ld) ) ON DUPLICATE KEY 
UPDATE DateTime=Datetime, Inverter=VALUES(Inverter), Serial=VALUES(Serial), 
CurrentPower=VALUES(CurrentPower), EtotalToday=VALUES(EtotalToday), 
PVOutput=VALUES(PVOutput)",(archdatalist+i)->date, (archdatalist+i)->inverter, 
(archdatalist+i)->serial, (archdatalist+i)->current_value, 
(archdatalist+i)->accum_value, NULL );

I set PVoutput to NULL when new data is added, in this case if data get's 
overwritten by new data it will be updated to PVoutput. This is needed when a 
date error gives an value of 0, gets posted to pvoutput and next run it will be 
updated in the DB but will not be updated in PVoutput cause the DB says that it 
is already added to PVoutput.

Original comment by Filip.Sm...@gmail.com on 5 Mar 2013 at 12:25