How to use data point for "Modify Table Only"

[BG]

Hello All,
I am trying to set up a data point that will update my data table.
I have a table that has a flag to mark which rows have been read into Mango or not.
I would like to update the flag to 1 after the data have been read so that the next time a new row is read.

The MySQL data source has this select statement:

SELECT * FROM db.staging_all where logger_id = 1 and flag = 0 order by date ASC LIMIT 1

And this gives me a good row of data.

My update statement:

UPDATE db.staging_all SET flag = 1 WHERE logger_id = 1 AND flag = 0 ORDER BY date ASC LIMIT 1;

This works in the MySQL workbench, but if I combine the 2 statements together on the data source I get an error message:

class com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'UPDATE db.staging_all SET flag = 1 WHERE logger_id = 1 AND flag = 0 ORDER BY dat' at line 2

So I think that the update statement must have to go into a data point that is set to "Modify Table Only"

But it doesn't seem to work. My update statement is saved, but I am not able to select any parameters?

I have tried this as well:

UPDATE db.staging_all SET flag = ? WHERE logger_id = ? AND flag = 0 ORDER BY date ASC LIMIT 1;

Any advice would be greatly appreciated.

Thank you

Brian

[phildunlap]

You probably would want to set up your update statement as a point set to "modify table only" I believe that is correct.

You are still able to get values from the select on the datasource, but you can set the update point to (param1, param2) from other places in the UI to perform the statement and replace the ? The data source only needs the select statement it should use to poll for values.

I am somewhat confused why you refer to the table as db.staging_all in one statement, and GRDXF.staging_all in the next. Your database name can be in the connection string, and if so you only need table names inside the data source.

I believe you should not have spaces in your update statement: flag = 1 --> flag=1

I think that you'll get more information about what specifically bothered the SQL executor in your log, or in the terminal you started Mango in.

To achieve your setting the value to 1 after successful reads, I would use a point link from one of your other SQL points to invoke the update statement.

[BG]

Hello Phil,
Thank you for your reply.

First, I was not finished editing my entry before I hit the submit button. I have since edited the initial entry. I was just trying to make the database and tables names generic.

Second. I have not worked with parameters before in MySQL. The example from the Mango help file showed ? marks so I tried that.

I will try your advice today regarding the spaces, checking the log and using point links once I read up on how to use them.

Cheers

Brian

[phildunlap]

Using ? is definitely the right way. You'll have to add the right data types for the parameters in order on the table modifying point. Parameters are one line of defense against SQL injection attacks!

Good luck!

[BG]

Hello Phil,
I just wanted to thank you. The point links worked great once I understood how to use them and the scripting. This is how I did it in case anyone else wants to know:

I have an SQL data source that needs to be read when the data comes in, not on a set interval and I need to use the data's own timestamp.

My data source uses this select statement:

SELECT * FROM staging_all where logger_id=1 and flag=0 order by date asc limit 1;

To trigger the reading of each row I set up a point link as follows:

I have a source data point that is set a a virtual sinusoidal that is going between -1 and +1 every 10 seconds.

I have a target data point set up as a modify table only point.

The point link uses the following one line script:

  if (source.value >0) return '(1)'; return '(0)';

I needed this line because the target data point was an alphanumeric data point.

My target point has this script:

  UPDATE staging_all SET flag=? WHERE logger_id=1 AND flag=0 ORDER BY date ASC LIMIT 1;

Now the data point that modifies the staging table gets a trigger (1) several times a minute and that causes my data rows to be read one at a time until they are all marked as (1).

Thank you for pointing me in the right direction.