JDBC Execute node with SQL from Input Field
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
-
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:
-
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 +'.'+@tableExec (@prof)
-
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!
Please sign in to leave a comment.
Comments
5 comments