• Recent
    • Tags
    • Popular
    • Register
    • Login

    Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.

    Radix IoT Website Mango 3 Documentation Website Mango 4 Documentation Website Mango 5 Documentation Website

    MySQL and datapoints

    Scheduled Pinned Locked Moved User help
    15 Posts 2 Posters 4.2k Views 1 Watching
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • J Offline
      Jagemon
      last edited by

      @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.

      1 Reply Last reply Reply Quote 0
      • J Offline
        Jagemon
        last edited by

        @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?

        1 Reply Last reply Reply Quote 0
        • phildunlapP Offline
          phildunlap
          last edited by

          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?

          1 Reply Last reply Reply Quote 0
          • J Offline
            Jagemon
            last edited by

            @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?

            1 Reply Last reply Reply Quote 0
            • phildunlapP Offline
              phildunlap
              last edited by

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

              1 Reply Last reply Reply Quote 0
              • J Offline
                Jagemon
                last edited by

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

                1 Reply Last reply Reply Quote 0
                • phildunlapP Offline
                  phildunlap
                  last edited by

                  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.

                  1 Reply Last reply Reply Quote 0
                  • J Offline
                    Jagemon
                    last edited by

                    @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?

                    1 Reply Last reply Reply Quote 0
                    • phildunlapP Offline
                      phildunlap
                      last edited by

                      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.

                      1 Reply Last reply Reply Quote 0
                      • First post
                        Last post