Драйвер SQL History

spectrekr

~~@Haus:~~

Была у меня идея через messagebox выполнять произвольные sql запросы.
Было бы неплохо, или в драйвер javascript включить модули для основных видов баз. А то при обновлении драйвера модули не включенные в стандартную поставку удаляются.

Haus

~~@Bluefox:~~

~~@Haus:~~

~~@RDim:~~

Думаю вопрос про то как в javascript работать с mysql. Да именно из скрипта управления отоплением написанного на javascript, в момент выполнения скрипта. Сами данные в базу пишет каждые пять минут драйве owfs. Если есть такая возможность примерчик очень помог бы. :roll:
Была у меня идея через messagebox выполнять произвольные sql запросы. `
Bluefox, подымаю заново вопрос

Драйвер SQL History, db type MySQL

Как из базы MySQL в javascript вытащить среднее значение температуры за последние 30 минут, 5 минут ?

Haba

вот как вариант http://forum.iobroker.net/viewtopic.php … 618#p23618

Haus

~~@Haba:~~

вот как вариант http://forum.iobroker.net/viewtopic.php … 618#p23618 `
Да пытаюсь, пока вышло подключится трудно без знаний javascript

Haba

Наверное больше не js, а правильно сформировать SQL запрос в базу, чтобы получить искомое

SELECT AVG('имя столбца в таблице с показаниями') FROM 'имя таблицы'

WHERE 'имя столбца даты и времени' >= date_sub(now(), INTERVAL 1 HOUR);

попробуй так

jaffa

Из за чего ошибка?

sql-0 2016-04-17 04:10:21 error Cannot select SELECT id, type FROM iobroker.datapoints WHERE name='megad.0.p7_Свет_1';: Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_

sql-0 2016-04-17 04:10:21 warn Cannot get index of "megad.0.p7_Свет_1": Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '='

instalator

~~@jaffa:~~

Из за чего ошибка?

sql-0 2016-04-17 04:10:21 error Cannot select SELECT id, type FROM iobroker.datapoints WHERE name='megad.0.p7_Свет_1';: Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_

sql-0 2016-04-17 04:10:21 warn Cannot get index of "megad.0.p7_Свет_1": Error: ER_CANT_AGGREGATE_2COLLATIONS: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '=' `
http://stackoverflow.com/questions/1008 … ysql-error

Haus

Почему меняя host: 'localhost' на удалённый host: '192.168.1.10' все равно стучит в localhost ?

var mysql = require('mysql');
var connection = mysql.createConnection({
    //host: 'localhost',
    host: '192.168.1.10',
    user: 'root',
    password: '********',
    database: 'demo',
    socketPath: '/var/run/mysqld/mysqld.sock'
});

//создаем подключение к базе
connection.connect(function(err) {
    if (err) {
        log('error connecting: ' + err.stack);
        return;
    }
    log('connected as id ' + connection.threadId);
});

function start () {
    connection.query('SELECT (dev_value) AS cur_temp FROM tmp_2 WHERE ContID=6 ORDER BY dev_date DESC LIMIT 3', function(err, res, fields) {
        if (err) throw err;
        log('cur_temp: ' + (res[0].cur_temp).toFixed(2));
        //setState('Heat.cur_temp', (res[0].cur_temp).toFixed(2));
    });
}
setInterval ( function () {
    start ();
},30000 );

PS. закомментировал socketPath: '/var/run/mysqld/mysqld.sock' стал отрабатывать host: '192.168.1.10'.

Lstt

Сегодня решил попробовать node.js 4.4.3, получаю в логе:

sql.0	2016-04-27 09:29:07	error	sql.0 TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:28:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:28:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:27:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:27:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:26:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:26:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
admin-1	2016-04-27 09:25:56	info	successful connection to socket.io from ::ffff:XXX.YYY.169.102
sql-0	2016-04-27 09:25:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:25:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function

Bluefox

~~@Lstt:~~

Сегодня решил попробовать node.js 4.4.3, получаю в логе:

sql.0	2016-04-27 09:29:07	error	sql.0 TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:28:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:28:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:27:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:27:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:26:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:26:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
admin-1	2016-04-27 09:25:56	info	successful connection to socket.io from ::ffff:XXX.YYY.169.102
sql-0	2016-04-27 09:25:37	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function
sql-0	2016-04-27 09:25:07	error	TypeError: SQL[(clients[adapter.config.dbtype].name + "Pool")] is not a function 

```` `

А какой dbtype у тебя?

Lstt

Самый простой, без заморочек, SQLite3. Всё остальное, пока для меня сложно..

Bluefox

~~@Lstt:~~

Самый простой, без заморочек, SQLite3. Всё остальное, пока для меня сложно.. `
К счастью я смог воспроизвести ошибку.

При смене версий ноды нужно пересобирать все бинарные пакеты. И sqlite это один из них.

Лечится просто:

cd ...iobrokerDirectory
iobroker stop sql
cd node_modules/iobroker.sql
npm update --production
cd ..\..
iobroker start sql

electric

Система armbian плата cubietruck.

Ошибка при установки драйвера:
1238_pic_198.jpg

spectrekr

Это не ошибка, если подождать подольше, то он начинает компилировать базу sqlite, процесс очень затягивается, может несколько минут длится.

electric

~~@spectrekr:~~

Это не ошибка, если подождать подольше, то он начинает компилировать базу sqlite, процесс очень затягивается, может несколько минут длится. `
Спасибо! Да, подождал, установилось…

instalator

Настроил сегодня MySQL .

В логе есть такие ошибки:

! ````
sql.0 2016-11-20 20:37:19.752 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(93, 1479649029728, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:19.410 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(74, 1479649029399, ''''''hdmi1'''''', 0, 2, 0);: Error: ER_PARSE_ERROR: 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 'hdmi1'''''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:37:19.188 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(108, 1479649029176, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:16.136 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(133, 1479649026114, ''''''*pow=off '''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 ''''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:13.316 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(71, 1479649023305, ''''''cool'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'cool'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:07.716 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(149, 1479649017635, ''''''OK'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'OK'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:07.248 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(113, 1479649017231, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:02.142 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(105, 1479649012081, ''''''*pow=off '''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 ''''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:37:00.944 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(80, 1479649010933, ''''''0B'''''', 0, 3, 0);: Error: ER_PARSE_ERROR: 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 '0B'''''', 0, 3, 0)' at line 1
sql.0 2016-11-20 20:36:59.489 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(50, 1479649009472, ''''''off'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:36:59.245 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479649009233, ''''''01R240G3B0'''''', 0, 2, 0);: Error: ER_PARSE_ERROR: 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 '01R240G3B0'''''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:36:58.558 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(118, 1479649008542, ''''''phone'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'phone'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:36:57.851 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479649007841, ''''''min'''''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'min'''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:36:35.858 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(46, 1479648985848, ''myhome-RS232,myhome-NRF,myhome-Bathroom,myhome-kitchen,myhome-bedroom,myhome-Mother,myhome-Conditioner,myhome-BENQ,myhome-Guestroom,myhome-Audio_Amplifier,myhome-Lighting'', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'myhome-RS232,myhome-NRF,myhome-Bathroom,myhome-kitchen,myhome-bedroom,myhome-Mot' at line 1
mqtt.0 2016-11-20 20:35:25.866 info mqtt.0 Client [phone] closed
mqtt.0 2016-11-20 20:35:25.858 info mqtt.0 Client [phone] disconnected
sql.0 2016-11-20 20:35:19.719 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(93, 1479648909707, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:19.411 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(74, 1479648909397, ''''hdmi1'''', 0, 2, 0);: Error: ER_PARSE_ERROR: 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 'hdmi1'''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:35:19.186 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(108, 1479648909174, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:16.122 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(133, 1479648906111, ''''*pow=off '''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 ''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:13.312 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(71, 1479648903301, ''''cool'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'cool'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:07.642 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(149, 1479648897631, ''''OK'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'OK'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:07.220 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(113, 1479648897209, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:02.090 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(105, 1479648892079, ''''*pow=off '''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 ''''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:35:00.945 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(80, 1479648890930, ''''0B'''', 0, 3, 0);: Error: ER_PARSE_ERROR: 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 '0B'''', 0, 3, 0)' at line 1
sql.0 2016-11-20 20:34:59.475 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(50, 1479648889463, ''''off'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'off'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:34:59.136 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479648889124, ''''01R240G3B0'''', 0, 2, 0);: Error: ER_PARSE_ERROR: 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 '01R240G3B0'''', 0, 2, 0)' at line 1
sql.0 2016-11-20 20:34:58.556 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(118, 1479648888540, ''''phone'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'phone'''', 1, 1, 0)' at line 1
sql.0 2016-11-20 20:34:57.850 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479648887839, ''''min'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'min'''', 1, 1, 0)' at line 1
mqtt.0 2016-11-20 20:34:49.108 info mqtt.0 Client [phone] connected

Каждый раз при новой ошибке в переменной добавляются кавычки.
[266_______.png](/assets/uploads/files/266_______.png)
[266______.png](/assets/uploads/files/266______.png)

Bluefox

Включи дебаг и посмотри как выглядят команды

instalator

~~@Bluefox:~~

Включи дебаг и посмотри как выглядят команды `

! ````
sql.0 2016-11-20 21:07:17.589 error Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650837572, ''01R240G3B0'', 0, 4, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the m
inMem 2016-11-20 21:07:17.576 debug message * kodi.1.playing_time val=06-37-17, ack=true, ts=1479650837485, q=0, from=system.adapter.kodi.1, lc=1479650837485
sql.0 2016-11-20 21:07:17.573 debug INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650837572, ''01R240G3B0'', 0, 4, 0);


>! ````
sql.0	2016-11-20 21:08:37.649	error	Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650917641, ''''''01R240G3B0'''''', 0, 4, 0);: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; che
sql.0	2016-11-20 21:08:37.642	debug	INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(36, 1479650917641, ''''''01R240G3B0'''''', 0, 4, 0);
sql.0	2016-11-20 21:08:37.641	debug	timed-relog mqtt.0.myhome.test.test, value='''''01R240G3B0''''', lastLogTime=1479650907639, ts=1479650917641

Bluefox

Поменяй строку 28 в lib/mysql.js на

    if (db === "ts_string") state.val = "'" + state.val.replace(/'/g, '\\\'') + "'";

Лучше?

instalator

~~@Bluefox:~~

Поменяй строку 28 в lib/mysql.js на
    if (db === "ts_string") state.val = "'" + state.val.replace(/'/g, '\\\'') + "'";
Лучше? `
Меньше но проскакивает:

! ````
sql.0 2016-11-21 19:17:18.410 debug sql.0 INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479730628288, ''''min'''', 1, 1, 0);
sql.0 2016-11-21 19:17:18.574 error sql.0 Cannot insert INSERT INTO iobroker.ts_string (id, ts, val, ack, _from, q) VALUES(143, 1479730628288, ''''min'''', 1, 1, 0);: Error: ER_PARSE_ERROR: 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 'min'''', 1, 1, 0)' at line 1


PS. Не меньбше, так же.

Но не на все объекты ругается

NEWS

Драйвер SQL History

Support us

421
Online

31.6k
Users

79.6k
Topics

1.3m
Posts

NEWS

Драйвер SQL History

Support us

421Online

31.6kUsers

79.6kTopics

1.3mPosts

421
Online

31.6k
Users

79.6k
Topics

1.3m
Posts