Genesys Engage on-premises

 View Only

Discussion Thread View

  • 1.  GIA iHub SQL query help

    Posted 02-16-2021 20:10

    Hi all,

    I am trying to connect to an external database as a proof of concept for a few different things including CLI pre-authentication and VIP caller ID for execs and the like to get special IVR treatments. I tested the SQL query directly on the DB and it works but I am getting the below responses in iHub.

    Has anyone had a similar experience? I'm a beginner with Groovy and I haven't found any fixes online (believe me, I've searched!)

    First draft
    result = context.sqlSelect('<dbHost>', 1000, "SELECT [name] FROM [<dbName>].[dbo].[<dbTable>] WHERE [cli] = <cli>","")

    Each time the script is run, the value after AsyncSqlRowsResponseImpl@ is different. It appears the query is successful but I am not getting the data I need

    <response>
    <status>success</status>
    <variables>
        <variable name="result" value="com.speechstorm.indy.sql.AsyncSqlRowsResponseImpl@5b9acb6e"/>
    </variables>
</response>

    Current draft

    result = context.sqlSelect('<dbHost>', 1000, "SELECT [name] FROM [<dbName>].[dbo].[<dbTable>] WHERE [cli] = <cli>","").getRows()


    This one appears to fail due to com.microsoft.sqlserver.jdbc.SQLServerException: (The index 1 is out of range.) but the query has not changed.

    <response>
    <status>error</status>
    <variables>
        <variable name="ErrorDescription" value="javax.script.ScriptException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: Failed to execute statement: SELECT [name] FROM [<dbName>].[dbo].[dbTable] WHERE [cli] = <cli>"/>
    </variables>
</response>

    Caused by: com.speechstorm.indy.scripting.ScriptletException: (javax.script.ScriptException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: Failed to execute statement: SELECT [name] FROM [<dbName>].[dbo].[dbTable] WHERE [cli] = <cli>)
Caused by: javax.script.ScriptException: (java.util.concurrent.ExecutionException: java.lang.RuntimeException: Failed to execute statement: SELECT [name] FROM [<dbName>].[dbo].[dbTable] WHERE [cli] = <cli>)
Caused by: java.util.concurrent.ExecutionException: (java.lang.RuntimeException: Failed to execute statement: SELECT [name] FROM [<dbName>].[dbo].[dbTable] WHERE [cli] = <cli>)
Caused by: java.lang.RuntimeException: (Failed to execute statement: SELECT [name] FROM [<dbName>].[dbo].[dbTable] WHERE [cli] = <cli>)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: (The index 1 is out of range.)


    #Integrations

    ------------------------------
    Oscar Alvarez
    Link Group
    ------------------------------



  • 2.  RE: GIA iHub SQL query help

    Posted 02-17-2021 08:43
    Edited by Bernhard Seifert 02-17-2021 08:43
    Hi,

    your SQL statement cannot set up and is rejected by DB => 
    Caused by: com.speechstorm.indy.scripting.ScriptletException: (javax.script.ScriptException: java.util.concurrent.ExecutionException: java.lang.RuntimeException: Failed to execute statement: SELECT [name] FROM [<dbName>].[dbo].[dbTable] WHERE [cli] = <cli>)
    You can not use variables in context.sqlSelect() directly, except for values after an operator (see below).

    You need to construct the statement first and pass the whole statement as string to context.sqlSelect().

    Like:
    column=  "columnAsString"
    table = "tableAsString"

    sqlStatement = "SELECT " + column + " FROM " + table
    result = context.sqlSelect('<dbHost>', 1000, sqlStatement,"")

    As mentioned before values after operators can be passed within the statement using "?" and the sqlParameters array as last parameter of context.sqlSelect().
    Like:
    sqlParameters = [cli, "test"]
    sqlStatement = "SELECT * FROM table WHERE cli = ? AND something = ?"
    In that case first question mark inserts value of cli variable and second question mark inserts the string "test"

    Finally you should iterate over the results like:
    results.rows.eachWithIndex.

    Best regards,
    Bernhard



    ------------------------------
    Bernhard Seifert
    Tieto Austria GmbH
    ------------------------------

    Original Message


  • 3.  RE: GIA iHub SQL query help

    Posted 02-17-2021 22:34

    Thanks Bernhard,

    I came to the same conclusion yesterday after posting the question and I've got it working now.

    After some fiddling with the returned value I've got it in a usable state :)



    ------------------------------
    Oscar Alvarez
    Link Market Services Limited
    ------------------------------

    Original Message


Need Help finding something?

Check out the Genesys Knowledge Network - your all-in-one access point for Genesys resources