如何创建接受多个(CSV)值的BIRT数据集，该数据集可在"IN"内部使用. select语句中的子句 [英] How to create a BIRT dataset that accepts multiple (CSV) values that it can be used inside "IN" clause in select statement

问题描述

我试图在BIRT报表中创建一个数据集，该数据集包含带有"IN"子句的select语句，并以逗号分隔的值代替?"使用可以接受多个值的BIRT参数.

I am trying to create a dataset in BIRT report that contains a select statement with "IN" clause and pass a comma separate value in place of "?" using a BIRT parameter that accepts multiple values.

例如:从(?)中的ID的表中选择*

for eg : select * from table where ID in ( ? )

我尝试将其添加到我的数据集中从(params ["paramer_name"].value)中ID的表中选择* *"，但是它不起作用.

I tried adding this in my dataset "select * from table where ID in ( params["paramer_name"].value)" but it is not working.

我不想使用BIRT数据集的内置过滤器，因为在查询中使用"IN"子句可以在很大程度上降低数据库服务器中查询的成本.

I do not want to use the built in Filter of BIRT dataset because using the "IN" clause in query reduces the cost of query to lot of extent in my database server.

有没有一种简单的方法，无需添加冗长的Java脚本?

Is there a simple way to do the same without adding long java scripts ???

仅供参考:用户选择的参数列表来自另一个数据集，我想将所选值用作另一个数据集的输入.

FYI : The list of parameter that user selects comes from another dataset, and I want to use the selected value as an input to another dataset.

非常感谢您的帮助...

Thanks a lot for your help...

推荐答案

我们不能使用常规SQL参数'?'来执行此操作.

We cannot do this with a regular SQL parameter '?'.

一种解决方法是替换此?"通过查询中的默认值，并在数据集的"beforeOpen"脚本中动态插入适当的逗号分隔值列表:

A workaround is to replace this '?' by a default value in the query, and dynamically inject an appropriate comma-separated list of values in the "beforeOpen" script of the dataset:

默认查询

Default query

假设ID的数据类型为整数，请按以下方式设置查询(当然，在此处使用有效的ID可以预览数据):

Assuming the datatype of ID is an integer, set up the query like this (of course use here a valid ID to be able to preview data):

select * from table where ID in ( 1000 )

数据集的

"beforeOpen"脚本:

"beforeOpen" script of the dataset:

   this.queryText=this.queryText.replaceAll('1000',params["parameter_name"].value.join(","));

这样，如果"parameter_name"返回3个值1100,1200,1300，则发送到数据库的查询将是:

This way, if "parameter_name" returns 3 values 1100,1200,1300 the query sent to the database will be:

select * from table where ID in ( 1100,1200,1300)

如果ID的数据类型为String，则类似，我们只需要使用引号就可以了.但是对于String类型，这种处理方式使SQL注入攻击成为可能，我们首先应该检查参数值是否看起来像我们期望的那样.

It is similar if the datatype of ID is a String, we just have to play a little bit with quotes. However with a String type this kind of handling makes SQL Injection attacks possible, we should firstly check if parameter values look like what we expect.

这篇关于如何创建接受多个(CSV)值的BIRT数据集，该数据集可在"IN"内部使用. select语句中的子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！