MySQL and datapoints

[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

1. Select from your database into the value point.
2. Create a second SQL point that sets a timestamp value to some useful place in your database.
3. 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).
4. 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.