New post
Question
· Jun 30, 2016

How to perform an SQL 'SELECT' with parameters.

#SQL #Ensemble

Hi,

I'm trying to perform a SELECT with parameters using the EnsLib.SQL.OutboundAdapter. The SELECT returns results but seems to discard the parameters I try to send. I have tried two methods.

First:

Set par(1) = "20160630"
Set par(1,"SqlType") = 12
Set sql = "SELECT Cod, Ing, score FROM [bbdd].[dbo].[vw_Test] WHERE MyParam >= '?'"
Set tSC = ..Adapter.ExecuteQueryParmArray(.QueryResultSet,sql,.par)

Second:

Set par(1) = "20160630"
Set par(1,"SqlType") = 12
Set sql = "SELECT Cod, Ing, score FROM [bbdd].[dbo].[vw_Test] WHERE MyParam >= '?'"
Set tSC = ..Adapter.ExecuteQuery(.QueryResultSet,sql,par)

Although both methods are returning results none of them are using the parameter.

What am I doing wrong?

Thanks in advance.

Discussion (4)2
Log in or sign up to continue
Dani Fibla · Jul 4, 2016

Hi Javier,

After execute both methods try this if the rowcount bigger than 0

d QueryResultSet.Rewind()

d QueryResultSet.Next()

Also you can try to delete the single quote when you put the parameter

MyParam >= '?'   -->  MyParam >= ?

When you use the ExecuteQuery , you can directly send the stringlike this

Set par = "20160630"

Set sql = "SELECT Cod, Ing, score FROM [bbdd].[dbo].[vw_Test] WHERE MyParam >= ?"
Set tSC = ..Adapter.ExecuteQuery(.QueryResultSet,sql,par)

It's not necessary the array.

2 0