JDBC Execute node with SQL from Input Field

Follow

Mario Ermacora

• December 03, 2019 07: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

0

• 

  Gerry Mullin

  • December 04, 2019 15:54

  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
• 

  M M

  • January 07, 2020 10:45
  • Edited

  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
• 

  Adrian Williams

  • January 09, 2020 13:30

  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
• 

  Paul Rasmussen

  • January 09, 2020 17:04
  • Edited

  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
• 

  M M

  • January 10, 2020 10: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!

  0

  Comment actions Permalink

Please sign in to leave a comment.