how to pass parameters in other oledb providers apart from SQL server native client

Question

what will be the query in other oledb provider if I need to pass parameters?

Suppose in SQL server Oledb source I am using

select * from Test where ID=?

What will be the query if I need pass the parameter using other oledb provider (PI Oledb provider).

Please help

thanks

Saikat

Answer 1

Hi, the easiest way is to use a variable. Check the link below:

http://stackoverflow.com/questions/58540/how-to-resolve-sql-query-parameters-mapping-issues-while-using-oracle-ole-db-pro

David.

Answer 2

David,

Its not working. Below is the query in which I need to pass parameter. This is the PI server query.

SELECT i1.tag, i1.time, i1.value
FROM piarchive..piinterp i1
WHERE i1.tag=? AND i1.time BETWEEN 'T-30d' AND 't'
AND i1.timestep = '15m'

Now, if I follow the above link and put the following query in the expression

"SELECT i1.tag, i1.time, i1.value
FROM piarchive..piinterp i1
WHERE i1.time BETWEEN 'T-30d' AND 't'
AND i1.timestep = '15m' AND  i1.tag= " + @[User::PiTagName]

I got the below result on clicking Evaluate expression button which is not correct.

SELECT i1.tag, i1.time, i1.value

FROM piarchive..piinterp i1

WHERE i1.time BETWEEN 'T-30d' AND 't'

AND i1.timestep = '15m' AND  i1.tag= SELECT i1.tag, i1.time, i1.value

FROM piarchive..piinterp i1

WHERE i1.time BETWEEN 'T-30d' AND 't'

AND i1.timestep = '15m' AND  i1.tag=

Answer 3

You need to create two variables, one for the query and another for the parameter, and in the query variable (that is evaluated as an expression) you should put your select + variable (the one with the filter value).

David.

Answer 4

Thanks David for the solution.