Data360 DQ+

 View Only
Expand all | Collapse all

Invoking external data store stored procedure from process model and returning condition code

  • 1.  Invoking external data store stored procedure from process model and returning condition code

    Posted 04-29-2020 06:42

    I want to run a DQ+ validity check on data columns in an internal data store and then pass to invoke a SP in an external data store (Sybase DB) and pass parameters to that procedure in a real-time model.  How can I achieve this within DQ+ or Data 360 platform?



  • 2.  RE: Invoking external data store stored procedure from process model and returning condition code

    Employee
    Posted 04-29-2020 14:32

    DQ+ has an "Execute Query in DB" node that will allow for customizing a SQL query to execute on the specified database. This includes calling stored procedures or functions. 

    In order to pass in variables into the SQL query, you can use DQ+'s ${column_name} notation, where column_name is an input field. The ${...} notation will tell DQ+ to search for the specified column and input its value into the SQL query. This notation can be used throughout varies DQ+ nodes and properties - it isn't limited to just SQL queries.

    Note that the SQL query will run once per record, so depending on your use case of calling stored procedures, you may need to reduce your record count to 1. 

     

    As an example, I have the following sample data being inputted into the Execute Query in DB node:

     

    Within the Execute Query in DB node, I'm connecting to a database to run a custom function named add100. This function will take an integer input and add 100 as a returned value. This is the SQL query used in the node:

    SELECT '${Username}' as "Username", add100(${AmountInput}) as "AmountOutput"

    Since I have three input records, this query will run three times. I feed in the Username field into the SQL query just so I can get it back as an output too, and I feed the AmountInput field into the function using the and store the result in the AmountOutput field. Note that Username and AmountInput use the ${...} notation to reference the DQ+ input fields.

    The output is now the same data with 100 added to the amount field:

     

    The same concept above can be used to call your stored procedures within Sybase as well. As a side-note, Sybase drivers aren't natively in DQ+ but you can add custom JDBC drivers into DQ+. My example above was performed against a Postgres database.