I recently installed the Teradata .NET data provider and I am trying to use named parameters and multi-value parameters in my SQL. I am finding information on this topic hard to come by.
In order to get parameters to work do I need to write my own Teradata data extension from scratch, expand an existing data extension to work with Teradata, or can I simple edit my .config files to point to a generic extension wrapper already existing
Thanks!
Teradata data extension question
Rohit_crystal
Did you ever have any luck with the Teradata I am working on the same issue now.
R
khalod_is
You need to implement a custom data extension.
In order to have named parameters working, you need to implement your own commandtext parsing algorithm that detects the parameter names.
Since the Teradata .NET data provider does not support multi-value query parameters natively, you have to implement a "query parameter rewrite" algorithm in the ExecuteReader method of your custom data extension. Basic outline of the algorithm:
1. for every parameter in the parameter collection, you have to find if that parameter is used in a IN-clause; e.g. ... IN (@P1)
2. determine the string that @P1 needs to be replaced with (i.e. "Value1","Value2","Value3") - pay attention particularly to single-quotes / double-quotes as needed by the database (in your case: Teradata)
3. remove all occurrences of @P1 from the commandtext and replace them with the string you generated in step 2
4. remove P1 from the parameter collection(!)
There may be some custom data extension samples available on the Internet, but I'm not aware of any sample that would implement a query parameter rewrite algorithm.
-- Robert