LAE

 View Only
  • 1.  JDBC Execute node with SQL from Input Field

    Posted 12-03-2019 00:03

    Wondering if anyone at Infogix or the broader LAE user community has a custom JDBC Execute node for LAE that is able to take the SqlQuery from a node input field rather than a parameter? Similar to the JDBC node enhancements recently introduced in D3S Analyze 3.5.0?

    The use case is the ability to create a persistent work table in an Azure hosted SQL Server data warehouse, where the table, column names & types can be configured dynamically based on upstream logic and data.

    Thanks in advance,

    Mario Ermacora



  • 2.  RE: JDBC Execute node with SQL from Input Field

    Employee
    Posted 12-04-2019 08:54

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Hi Mario, I have a custom node that will help you. I'll send it to you in a separate email.



  • 3.  RE: JDBC Execute node with SQL from Input Field

    Posted 01-07-2020 03:44

    Hi - does anyone have a workaround or custom node for D360 Analyze version 3.4.2 for the same purpose - via JDBC execute? We're unable to upgrade to 3.5.0 just yet, but the functionality for SQL query to be provided from node input is required.



  • 4.  RE: JDBC Execute node with SQL from Input Field

    Employee
    Posted 01-09-2020 06:31

    I am not aware of a solution that would work with Data360 Analyze 3.4.x. 

    You can however parameterize a SQL statement using SqlQueryFieldBindings. This was discussed recently in another post on the forum here:

    https://support.infogix.com/hc/en-us/community/posts/360038228174-JDBC-execute-use-input-in-SQL-querry

     



  • 5.  RE: JDBC Execute node with SQL from Input Field

    Employee
    Posted 01-09-2020 10:03

    The recent version of Analyze supports this functionality; however, I see the question was for LAE. In addition to the solution Gerry provided, a native SQL Server method exists.  The SQL Server solution is to build the query dynamically and execute it.  Here is an example of the syntax: 

    declare
    @AssetID nvarchar(50) = ?,
    @db nvarchar(100) = ?,
    @schema nvarchar(100) = ?,
    @table nvarchar(100) = ?,
    @column nvarchar(100) = ?,
    @prof nvarchar(max)

    set @prof = 'Select ' +
    @AssetID + ' AS AssetID
    ,COUNT(' +@column +') AS [RowCount]
    ,COUNT(DISTINCT ' + @column +') AS UniqueCount
    From ' +@db +'.'+@schema +'.'+@table

    Exec (@prof)

     



  • 6.  RE: JDBC Execute node with SQL from Input Field

    Posted 01-10-2020 03:48

    Thanks for the responses, folks.

    Adrian: I was hoping that the '?' syntax would also work when specifying the database column name, but its usability appears to be restricted to the right hand side of the WHERE clause. In other words, I can't do SELECT * FROM dbtable WHERE ? = ?, and then put 'database_field', 'value_field' in the SqlQueryFieldBindings. This is why I thought of dynamically creating the entire SQL script for each data input row in a transform node and then feeding that into the JDBC Execute node.

    Paul: Unfortunately, what we have is a PostgreSQL database, and as far as I have been able to determine, it doesn't seem to support a similar scripting language to SQL Server. I'm happy to be corrected, however!