Is it possible for CMX to insert data in to two different table names at the same interval?
I had an idea I wanted to try out, but I am already using seconds/minutes interval in the custom uploads.
Welcome to the Cumulus Support forum.
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.9.4 (build 1099) - 28 November 2014
(a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)
Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki
Latest Cumulus MX V3 release 3.28.6 (build 3283) - 21 March 2024
Cumulus MX V4 beta test release 4.0.0 (build 4019) - 03 April 2024
Legacy Cumulus 1 release 1.9.4 (build 1099) - 28 November 2014
(a patch is available for 1.9.4 build 1099 that extends the date range of drop-down menus to 2030)
Download the Software (Cumulus MX / Cumulus 1 and other related items) from the Wiki
SQL Custom Upload Question
Moderator: mcrossley
-
- Posts: 272
- Joined: Tue 28 May 2013 1:06 am
- Weather Station: Davis VP2 Plus
- Operating System: Raspian Buster (RPi 3b)
- Location: Tulsa, OK
Re: SQL Custom Upload Question
I don't think it would be CMX's function to do this; if you're using a custom can you string two insert statements into the same command? I don't know. Now I need to research this. Not saying this couldn't be done but it's not up to CMX I would think. It's up to MySQL and the ability to concatenate insert statements to do this.
EDIT: It appears you can't do this (concatenate insert commands) however you do it as a transaction. I'm sure that keeping the semicolons where they need to be for this could work, but I don't have enough tables to do this. Here's what I found as an example:
Code: Select all
BEGIN;
INSERT INTO users (username, password)
VALUES('test', 'test');
INSERT INTO profiles (userid, bio, homepage)
VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com');
COMMIT;
It would look something like this:
Code: Select all
BEGIN; INSERT INTO users (username, password) VALUES('test', 'test'); INSERT INTO profiles (userid, bio, homepage) VALUES(LAST_INSERT_ID(),'Hello world!', 'http://www.stackoverflow.com'); COMMIT;
-
- Posts: 22
- Joined: Mon 02 Jan 2012 4:35 pm
- Weather Station: Davis VP2
- Operating System: Debian 11
- Location: Valdisotto - Italy
- Contact:
Re: SQL Custom Upload Question
What about using mysql trigger (https://dev.mysql.com/doc/refman/5.7/en ... igger.html)?
If you use phpMyAdmin to manage your mysql database you can use "Trigger" function:
For example...
AFTER "Realtime" get updated -> INSERT new data in "Lastime" table.
(Sorry for my bad english)
If you use phpMyAdmin to manage your mysql database you can use "Trigger" function:
For example...
AFTER "Realtime" get updated -> INSERT new data in "Lastime" table.
(Sorry for my bad english)
You do not have the required permissions to view the files attached to this post.
- mcrossley
- Posts: 12756
- Joined: Thu 07 Jan 2010 9:44 pm
- Weather Station: Davis VP2/WLL
- Operating System: Bullseye Lite rPi
- Location: Wilmslow, Cheshire, UK
- Contact:
Re: SQL Custom Upload Question
You can just put two insert statements in the Custom SQL field - just make sure you put a semi-colon after the first statement and before the second.
I used to do that, I had an Insert and a Delete in the Custom SQL field.
I used to do that, I had an Insert and a Delete in the Custom SQL field.
-
- Posts: 2473
- Joined: Wed 08 Jun 2011 11:19 am
- Weather Station: Davis Vantage Pro 2 + Ecowitt
- Operating System: GNU/Linux Ubuntu 22.04 LXC
- Location: Alcaston, Shropshire, UK
- Contact:
Re: SQL Custom Upload Question
That is exactly what I do for my ExtraRealtime table that contains my grass temperature and depth temperature from my Ecowitt sensors. it does work.
-
- Posts: 1808
- Joined: Sat 17 Dec 2011 11:55 am
- Weather Station: Davis Vantage Pro2
- Operating System: Windows 11 x64
- Location: Dorset - UK
- Contact:
Re: SQL Custom Upload Question
Bingo!
Working a treat.
Thank you for the replies.