I writing a report in Visual Studio that takes a user input parameter and runs against an ODBC datasource. I would like to write the query manually and have reporting services replace part of the where clause with the parameter value before sending it to the database. What seems to be happening is that the @parmName I am assuming will be replaced is actually being sent as part of the SQL statement. Am I missing a configuration setting somewhere or is this simply not possible?
我在Visual Studio中编写一个报表,它接受用户输入参数并针对ODBC数据源运行。我想手动编写查询并使报告服务将where子句的一部分替换为参数值,然后再将其发送到数据库。似乎正在发生的是我假设将被替换的@parmName实际上是作为SQL语句的一部分发送的。我错过了某处的配置设置或者这根本不可能吗?
I am not using the filter option in the tool because this appears to bring back the full dataset from the database and do the filtering on the SQL Server.
我没有在工具中使用过滤器选项,因为这似乎从数据库中带回完整数据集并在SQL Server上进行过滤。
4 个解决方案
#1
5
It sounds like you'll need to treat the SQL Statement as an expression. For example:
听起来您需要将SQL语句视为表达式。例如:
="Select col1, col2 from table 1 Where col3 = " & Parameters!Param1.Value
If the where clause is a string you would need to do the following:
如果where子句是字符串,则需要执行以下操作:
="Select col1, col2 from table 1 Where col3 = '" & Parameters!Param1.Value & "'"
Important: Do not use line breaks in your SQL expression. If you do you will get an error.
重要说明:不要在SQL表达式中使用换行符。如果你这样做,你会收到一个错误。
Holla back if you need any more assistance.
如果您需要更多帮助,Holla会回来。
#2
1
Doesn't ODBC use the old "?" syntax for parameters? Try this:
ODBC不使用旧的“?”参数的语法?试试这个:
select col1, col2 from table1 where col3 = ?
The order of your parameters becomes important then, but it's less vulnerable to SQL injection than simply appending the parameter value.
然后,参数的顺序变得很重要,但它不像简单地附加参数值那样容易受到SQL注入的影响。
#3
1
Encountered same problem trying to query an access database via ODBC.
尝试通过ODBC查询访问数据库时遇到同样的问题。
My original query: SELECT A.1 FROM A WHERE A.1 = @parameter resulted in error. Altered to: SELECT A.1 FROM A WHERE A.1 = ?.
我的原始查询:SELECT A.1 FROM A WHERE A.1 = @parameter导致错误。改为:SELECT A.1 FROM A WHERE A.1 = ?.
You then have to map the query parameter with your report parameter.
然后,您必须使用报表参数映射查询参数。
#4
0
I am a bit confused about this question, if you are looking for simple parameter usage then the notation is :*paramName* , however if you want to structurally change the WHERE clause (as you might in sql+ using ?) then you should really be using custom code within the report to define a function that returns the required sql for the query.
我对这个问题有点困惑,如果你正在寻找简单的参数用法,那么符号是:* paramName *,但是如果你想在结构上改变WHERE子句(你可能在sql +中使用?)那么你应该真的是使用报表中的自定义代码来定义返回查询所需的sql的函数。
Unfortunately, when using custom code, parameters cannot be referenced directly in the generated query but have to have there values concatenated into the resultant String, thus introducing the potential for SQL injection.
不幸的是,在使用自定义代码时,参数不能直接在生成的查询中引用,但必须将这些值连接到结果字符串中,从而引入SQL注入的可能性。
#1
5
It sounds like you'll need to treat the SQL Statement as an expression. For example:
听起来您需要将SQL语句视为表达式。例如:
="Select col1, col2 from table 1 Where col3 = " & Parameters!Param1.Value
If the where clause is a string you would need to do the following:
如果where子句是字符串,则需要执行以下操作:
="Select col1, col2 from table 1 Where col3 = '" & Parameters!Param1.Value & "'"
Important: Do not use line breaks in your SQL expression. If you do you will get an error.
重要说明:不要在SQL表达式中使用换行符。如果你这样做,你会收到一个错误。
Holla back if you need any more assistance.
如果您需要更多帮助,Holla会回来。
#2
1
Doesn't ODBC use the old "?" syntax for parameters? Try this:
ODBC不使用旧的“?”参数的语法?试试这个:
select col1, col2 from table1 where col3 = ?
The order of your parameters becomes important then, but it's less vulnerable to SQL injection than simply appending the parameter value.
然后,参数的顺序变得很重要,但它不像简单地附加参数值那样容易受到SQL注入的影响。
#3
1
Encountered same problem trying to query an access database via ODBC.
尝试通过ODBC查询访问数据库时遇到同样的问题。
My original query: SELECT A.1 FROM A WHERE A.1 = @parameter resulted in error. Altered to: SELECT A.1 FROM A WHERE A.1 = ?.
我的原始查询:SELECT A.1 FROM A WHERE A.1 = @parameter导致错误。改为:SELECT A.1 FROM A WHERE A.1 = ?.
You then have to map the query parameter with your report parameter.
然后,您必须使用报表参数映射查询参数。
#4
0
I am a bit confused about this question, if you are looking for simple parameter usage then the notation is :*paramName* , however if you want to structurally change the WHERE clause (as you might in sql+ using ?) then you should really be using custom code within the report to define a function that returns the required sql for the query.
我对这个问题有点困惑,如果你正在寻找简单的参数用法,那么符号是:* paramName *,但是如果你想在结构上改变WHERE子句(你可能在sql +中使用?)那么你应该真的是使用报表中的自定义代码来定义返回查询所需的sql的函数。
Unfortunately, when using custom code, parameters cannot be referenced directly in the generated query but have to have there values concatenated into the resultant String, thus introducing the potential for SQL injection.
不幸的是,在使用自定义代码时,参数不能直接在生成的查询中引用,但必须将这些值连接到结果字符串中,从而引入SQL注入的可能性。