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

JDBC Execute node with SQL from Input Field

Comments

5 comments

  • Avatar
    Gerry Mullin

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

    0
    Comment actions Permalink
  • Avatar
    M M

    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.

    0
    Comment actions Permalink
  • Avatar
    Adrian Williams

    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

     

    0
    Comment actions Permalink
  • Avatar
    Paul Rasmussen

    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)

     

    0
    Comment actions Permalink
  • Avatar
    M M

    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!

    0
    Comment actions Permalink

Please sign in to leave a comment.