MySQL and datapoints

Jagemon

Hi,

I've managed to configure datasource with the following result AFTER the select parameters:
The first row being "id", "name", "VALUE_NUMBER" and of course the timestamp.

100 strainCounterValue -1 2013-02-18 14:26:26.0
101 testPositionMaxValueInEnd 34.894116 2013-02-18 14:26:26.0
102 testPositionMinValueInEnd 31.494789 2013-02-18 14:26:27.0
103 testPositionAmplitudeInEnd 1.699664 2013-02-18 14:26:27.0
104 testForceMaxValueInEnd -40.919274 2013-02-18 14:26:27.0
105 testForceMinValueInEnd -45.262963 2013-02-18 14:26:27.0
106 testForceAmplitudeInEnd 2.171844 2013-02-18 14:26:27.0

The question is, how do I actually configure the datapoint to fetch the value_number row and write a chart or graph of it? It is obviously a row based query but I cannot seem to get it fetch anything. It is clear that something is very wrong in my data point configuration but what is it and how should it be done exactly?

phildunlap

The SQL datasource is what I am thinking you're referring to.

If we wanted to get values from the first point, we could have the row-based query "select id, VALUE_NUMBER, timestamp from my_table where id=100" and configure a datapoint with a row-identifier of '100' and a numeric type.

Do you get point values if you try this?

Jagemon

Hi, and yes it is SQL.

Thanks for your reply. I got it sorted on in a way by fetching only tables that I need the value from (well duh) and mainly adding the correct Row identifier and Data type (numeric).
I've faced another problem now and that is as follows: The rows aren't exactly numbered so that 1 is followed by 2, then 3 etc. but the value that I will fetch only occurs every 50th row or so.

Is there any way to set it up in a way that it actually moves on to the next row that has the value that I need instead of the one I fetched before?

Here is the table as it is in Statement Test:

testForceMaxValueInStart 200.956888 2013-03-05 15:48:03.0 <------ This is row ID 450
testForceMaxValueInStart 200.955026 2013-03-05 15:54:07.0 <------ This is row ID 495
testForceMaxValueInStart 201.513819 2013-03-06 14:07:07.0 <------ This is row ID 540
testForceMaxValueInStart 200.911254 2013-03-06 15:18:38.0 <------ This is row ID 585
testForceMaxValueInStart 201.424412 2013-03-07 11:00:57.0 etc...
testForceMaxValueInStart 199.931502 2013-03-07 13:30:37.0

Of course the database is being written at the same time as I try to read the values so the row ID will keep growing. If I put on a select sentence I can only fetch the first value on row 450.

Any ideas?

Jagemon

This is the problem as it is at the moment. I cannot run SQL command with "Begin" and "Commit" statement. Update doesn't work either at all. If I got the update field to work as I want it would update the selected field with a number that is outside the original select parameter.

I think the SQL select sentence on Mango doesn't support the Begin/Commit commands which are there to run commands in order?

phildunlap

I think you're right, you cannot use Begin/Commit from the sql data source's select statement. That statement was not intended to be a means to update a table. You can do this by making a point into a 'modify table only' and writing your update statement there with a parameter set. For every ? in your statement, you'll need a parameter. Then you can set that point to:

(1,false,null,'modem')

to fill in 4 question marks. These restrictions exist to protect the database, generally.

Jagemon

@phildunlap said:

I think you're right, you cannot use Begin/Commit from the sql data source's select statement. That statement was not intended to be a means to update a table. You can do this by making a point into a 'modify table only' and writing your update statement there with a parameter set. For every ? in your statement, you'll need a parameter. Then you can set that point to:

(1,false,null,'modem')

to fill in 4 question marks. These restrictions exist to protect the database, generally.

I think that doesn't synchronize with the original select sentence so how can I make sure that the point "modify table only" doesn't modify table that hasn't been read yet.

Jagemon

@phildunlap said:

I think you're right, you cannot use Begin/Commit from the sql data source's select statement. That statement was not intended to be a means to update a table. You can do this by making a point into a 'modify table only' and writing your update statement there with a parameter set. For every ? in your statement, you'll need a parameter. Then you can set that point to:

(1,false,null,'modem')

to fill in 4 question marks. These restrictions exist to protect the database, generally.

But I dont think that synchronizes with the original select statement so how can I make sure it doesn't alter data that hasn't been added in the graph yet?

My SQL database has to be used as Column base query but it only fetches the first valid number in the database even if there are plenty of other values to be fetched (more recent ones). There seems to be no way of somehow identifying the value I got from the database and somehow ignore those?

phildunlap

What do you mean, 'how can I make sure it doesn't alter data that hasn't been added to the graph yet' ?

You can make your SQL statement more complex to select only the value you're interested in, but that will depend closely on what you're trying to do. Here's a StackOverflow post about getting the latest records: http://stackoverflow.com/questions/53670/how-to-efficiently-sql-select-newest-entries-from-a-mysql-database . I think you are correct that there is no overarching synchronization in the SQLDS, but every read/write is executed within a transaction. Is the issue that you're selecting the value into a point and setting values into that same point. Sometimes you set a value to the point but the old value is returned (and then given to the datapoint) from the select statement?

Jagemon

@phildunlap said:

What do you mean, 'how can I make sure it doesn't alter data that hasn't been added to the graph yet' ?

You can make your SQL statement more complex to select only the value you're interested in, but that will depend closely on what you're trying to do. Here's a StackOverflow post about getting the latest records: http://stackoverflow.com/questions/53670/how-to-efficiently-sql-select-newest-entries-from-a-mysql-database . I think you are correct that there is no overarching synchronization in the SQLDS, but every read/write is executed within a transaction. Is the issue that you're selecting the value into a point and setting values into that same point. Sometimes you set a value to the point but the old value is returned (and then given to the datapoint) from the select statement?

So you think I can run the "insert" command in the select statement of the SQL datasource? First I run the command which gives the first value from the table but also inserts the rowid into another table where I can fetch it from. From that point forward I can just run the select command with a "where value > rowid". With that I can make it fetch value where the ID is greater than what it was before?

Main point of the post above: Running select with insert statement is okay (and should work) on the same "Select statement" in Mango?

phildunlap

No. No data manipulation is permitted from within the select statement of the SQL data source.

Jagemon

So basically it can't be done only via SQL commands? The update statement isn't viable either.

phildunlap

I don't understand what you're trying to do well enough to say if something is or isn't possible. You could also ensure the latest inserted records with an "order by" statement.

Jagemon

@phildunlap said:

I don't understand what you're trying to do well enough to say if something is or isn't possible.

Let me try it one more time if I can be more clear this time.

I have a database which keeps growing and growing when information is being added into that particular database. Say I'm trying to find values with TestForceMaxValueInEndfrom the database. The values are being added every 50 or so rows.
I can then fetch the values with Mango by using column based query but the only value being fetched is the first row. (From the document: "It is important to recognize that that only the first row of the result set is used in a column-based query.")

Now if I try to fetch the next value from the same database I am not able to do so without somehow pointing out which row was the last one fetched. If I could insert the row id into the database to another table (say rowid) I could then use the original select sentence with a when statement (select xxxxx when id>MaxValueInEndIDin table rowid).

Someone pointed out that maybe I should use javascript to run the same command (first select, then update or delete whole row) but is that possible if the Mango prevents the SQL database alteration?

phildunlap

Well, I have a feeling there could be a better solution, but you might be able to solve this using a point link. The idea would be

Select from your database into the value point.
Create a second SQL point that sets a timestamp value to some useful place in your database.
Create a point link from the value point to the timestamp point, and use the pointlink to turn it into something like ('src Pnt Name', timestamp) to set that value into the database (via the second SQL point).
Build you select to reference that stored timestamp so that you get the right point.

It's not the most elegant solution, but it should ensure that you're getting new-er values while still ensuring you're not just getting the new-est.