1. 

   maron, Member

   Hello.

   I know I can use skipheader / footer to merge result sets into one table:

   select 'dbr.resultset.options', 'skip_header_footer' | 'skip_header' | 'skip_footer' | 'json'

   Can we have something similar for charts, the reason is I'm working with temporary tables a lot, and this would remove the situation where you get Can't reopen temporary table - since we would not be using union and the statements thus separated.

   https://stackoverflow.com/questions/343402/getting-around-mysql-cant-reopen-table-error

   Here is an example that generates this error

   
   
   select 'dbr.chart','Line';
   
   select 'dbr.chart.options','chartjs';
   
   select ts, js->'$.status[0].current1' from tmp_telemetry;
   
   union select ts, 'bat0', js->'$.status[0].battery_remaining' from tmp_telemetry
   
   union select ts, 'bat1', js->'$.status[1].battery_remaining' from tmp_telemetry
   
   union select ts, 'bat2', js->'$.status[2].battery_remaining' from tmp_telemetry
   
   union select ts, 'bat3', js->'$.status[3].battery_remaining' from tmp_telemetry
   
   union select ts, 'bat4', js->'$.status[4].battery_remaining' from tmp_telemetry
   
   union select ts, 'bat5', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   

   If I could do something like this it would simplify things a lot

   
   
   select 'dbr.chart','Line';
   
   select 'dbr.chart.options','resultsets','6';  -- Include the next 6 resultsets in the chart
   
   select 'dbr.chart.options','chartjs';
   
   select ts, js->'$.status[0].current1' from tmp_telemetry;
   
   select ts, 'bat0', js->'$.status[0].battery_remaining' from tmp_telemetry;
   
   select ts, 'bat1', js->'$.status[1].battery_remaining' from tmp_telemetry;
   
   select ts, 'bat2', js->'$.status[2].battery_remaining' from tmp_telemetry;
   
   select ts, 'bat3', js->'$.status[3].battery_remaining' from tmp_telemetry;
   
   select ts, 'bat4', js->'$.status[4].battery_remaining' from tmp_telemetry;
   
   select ts, 'bat5', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   

   Best regards,
   Maron
2. 

   myDBR Team, Key Master

   Not being able to use the temp table more than once in a query in MySQL is a real nuisance. If you update to MariaDB, it no longer has this limitation.

   We can take a look if this could be supported (not just for charts but in general). It would require some bigger changes on how result sets are handled though.

   Alternatively, you can create another temp table and insert the data into that.

   create temporary table telemetry_tmp as
   
   select ts, 'bat_', js->'$.status[0].current1'  as v
   
   from tmp_telemetry;
   
   insert into telemetry_tmp select ts, 'bat0', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   insert into telemetry_tmp select ts, 'bat1', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   insert into telemetry_tmp select ts, 'bat2', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   insert into telemetry_tmp select ts, 'bat3', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   insert into telemetry_tmp select ts, 'bat4', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   insert into telemetry_tmp select ts, 'bat5', js->'$.status[5].battery_remaining' from tmp_telemetry;
   
   select 'dbr.chart','Line';
   
   select 'dbr.chart.options','chartjs';
   
   select ts, bat_, v
   
   from telemetry_tmp;

   --
   myDBR Team

Reply

You must log in to post.