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

Dynamic select statement from SQL table using SQL data source

Mango Automation general Discussion
Log in to reply
  • M

    Hi Everbody,

    When an event is activated, we would like to send a message to the relevant number. Phone numbers and datasource names are stored in sql table which is more than 10.000 rows. As we see that, in order to find relevant phone number, we need to create a sql datapoint for each phone number and we don't want to create 10.000 sql datapoints.

    Can we change select statement or row idenfier of sql data point dynamically??
    What is your suggestion?

    What we plan to do is in attachement.

    Cheers!
    0_1583240668787_b6db6289-b4a2-4451-b507-d29d5c3803ff-image.png
    Re: SQL Data Source - Is it possible to include a Data Point value in a query?

    0
  • MattFox

    Given what you are trying to do, I wouldn't bother doing it in Mango.
    I'd use an http server to post the info to and use that to look up the SQL server and call the message app service.
    Rather than trying to make Mango do awkward maneuvers for a simple task, perhaps some simple tasks combined would be easier... just leverage a system designed to handle it.

    I wouldn't bother fluffing with the sql datasource, I think it's a run and select data affair from what I've seen.

    0
  • terrypacker

    @Nurr

    There is the possibility to use the Email Event handler to do this. If you return "CANCEL" from the script on an email event handler it won't actually send an email. Leveraging this one could write a script to make an SQL query for the numbers and then call your global script. You can call Java code directly from the script but it will require some knowledge of Java and how to use it within the Nashorn Javascript Engine. There should be some examples of this on our forum. Basically the script needs superadmin permission and you must use the full package and class name when you reference a Java class.

    If you are game to give it a try, here is some documentation on the SQL api for Java:
    https://docs.oracle.com/javase/tutorial/jdbc/basics/processingsqlstatements.html

    As a side note Mango 4.0 will have a Message event handler that will allow using Twilio and in the future other platforms to send messages.

    1
  • MattFox

    @terrypacker said in Dynamic select statement from SQL table using SQL data source:

    As a side note Mango 4.0 will have a Message event handler that will allow using Twilio and in the future other platforms to send messages.

    I've been wanting to do this!
    Thanks Terry!

    0