Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
SQL Data Source and temporary tables
-
Hi,
My sensors send SMS messages which are stored into a MySQL table (received_messages_t).
Each message has basically: two fields (device_id+slave_id) for sensor identification, one field is timestamp, then I have "N" measurement fields (eg current, voltage etc).
My approach was to define in Mango M2M one Data Source for each sensor, and then N Data Points (one for each measurement field).
I guess a column based query is not suitable (only the first row of the result is used), so I would need a row-based query which extracts the messages sent from a sensor within a certain timeframe (eg during the last day), and puts data in 3 columns: Data Point Name, Data Point Value, Timestamp.
Thus, each message will be splitted into N rows.I have successfully tested such a script (eg with MySQL Query Browser), which uses MySQL temporary tables:
DROP TABLE if exists Dummy;
DROP TABLE if exists dum_datasource_t;
CREATE TEMPORARY TABLE Dummy ENGINE=MEMORY SELECT * FROM received_messages_t WHERE device_id=2 AND slave_id=2 AND DATE_SUB(CURDATE(),INTERVAL 1 DAY) <= message_timestamp;
CREATE TEMPORARY TABLE dum_datasource_t(Name VARCHAR(255) NOT NULL , Value VARCHAR(255) NOT NULL, Timestamp DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00')ENGINE=MEMORY;
insert into dum_datasource_t(Name, Value, Timestamp) select 'Voltage_1', Dummy.Voltage_1, Dummy.message_timestamp from Dummy;
...(other Data Points)...
insert into dum_datasource_t(Name, Value, Timestamp) select 'Voltage_N', Dummy.Voltage_N, Dummy.message_timestamp from Dummy;
select * from dum_datasource_t;Now, if I use this as Select statement in my Mango Data Source, the Statement test execution gives:
"class java.sql.SQLException: Can not issue data manipulation statements with executeQuery()."Any suggestion before trying to change Statement.executeQuery() into Statement.executeUpdate() in the source code?
Any better way to handle this SQL Data Source?
My Mango installation is using Derby, while Data Source is on a separate MySQL server, btw I do not think this is the point.Thank you in advance.
Giorgio