Bind variables in update query in ODBC Connector

[robjones]

Hi,

I'm having a problem getting bind variables to work correctly in an ODBC Connector stage which is performing an update to a SQL Server 2012 table.

The SQL I'm trying to get it to execute is :

Code: Select all

update TBL_BlobData
set Data=(select * from OPENROWSET(Bulk 'orchestrate.MediaFilePath' ,SINGLE_BLOB) a)
where Id=orchestrate.Id;

... where "MediaFilePath" is a varchar column containing the path and name of a file, the content of which I want to be loaded into an image column in the SQL Server table , and where "Id" is just a numeric key identifying the record to be updated.

Write mode of Connector is set to 'Update'. I have the "Id" column defined as a key, and the "MediaFilePath" as a non-key column.

When executing the job, I'm seeing the following warning in the log, and records rejected by the ODBC Connector target stage:

"odbc_TBL_BlobData,0: Unable to determine association between statement parameters and table columns. The connector will not be able to obtain external schema and only limited schema reconciliation will be performed"

Running the SQL manually with bind variables replaced by actual values, as below, works as expected and updates the existing record with the content of the referenced file.

Code: Select all

update TBL_BlobData
set Data=(select * from OPENROWSET(Bulk '\\fs01\public$\POC\Test.xhtml',SINGLE_BLOB) a)
where Id=10009935600000000001236;

Can anybody shed any light on why I might be seeing this warning? I suspect it's something to do with single-quoting the bind variable, although I've tried escaping the quote as below, but I still received the same warning:

Code: Select all

update TBL_BlobData
set Data=(select * from OPENROWSET(Bulk ''orchestrate.MediaFilePath'' ,SINGLE_BLOB) a)
where Id=orchestrate.Id

Any advice would be gratefully received.

[robjones]

Finally found a solution for this, in case anybody else was having similar problems.

Instead of using my custom SQL directly in the ODBC stage, I had to construct it as a text string in a transformer first, then pass that string as a bind variable to be executed by the EXEC() function in the ODBC stage.

For the filepath that I needed to pass to the OpenRowset function, I had to enclose this in embedded single-quotes, with the single-quotes themselves being double-quoted and concatenated to the filepath in a stage variable.

'SQL' column derivation in transformer:

Code: Select all

'update TBL_BlobData 
set Data=(select * from OPENROWSET(Bulk ' : svMediaFilePath : '  ,SINGLE_BLOB) a) 
where Id=' : lnk_tfm.Id

'svMediaFilePath' stage variable

Code: Select all

"'" : lnk_tfm.Document_Path : lnk_tfm.Document_Filename : '.' : lnk_tfm.Document_File_Extension : "'"

Custom SQL in ODBC connector stage

Code: Select all

EXEC (orchestrate.SQL)

Hope this helps somebody else!