We recommend switching to the latest versions of Edge, Firefox, Chrome or Safari. Using Internet Explorer will result in a loss of website functionality.

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

Comments

1 comment

  • Avatar
    Gerard Cafaro

    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.

    0
    Comment actions Permalink

Please sign in to leave a comment.