0
1374
Question Javier Lorenzo Mesa · Jun 30, 2016

How to perform an SQL 'SELECT' with parameters.

#Ensemble #SQL

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

Comments

Javier Lorenzo Mesa  Jun 30, 2016 to Alexander Koblov

I have developed the two methods after reading the documentation, but it doesn't work. Even most strange is the fact that these methods work well if the parameters are used with UPDATE sentences using ExecuteUpdateParmArray with the same Adapter.

0
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.

0
Javier Lorenzo Mesa  Jul 5, 2016 to Dani Fibla

It worked!

Quotes weren't needed and the parameter doesn't need to be an array neither, so question is solved..

Thanks Dani, you've been very kind!

Best regards.

0