Eclipse Community Forums: BIRT » Dataset

Home » Archived » BIRT » Dataset - More than one parameter(How to pass more than one parameter in the dataset sql )

Dataset - More than one parameter [message #734810]

Sun, 09 October 2011 23:10

Eclipse User

Dear All

I have a little issue here when it comes to passing more than one parameter in the sql dataset

When i have only one parameter, i do the following:

SELECT * FROM MYTABLE WHERE ID = ?

and then i create a parameter in the dataset that i link to the report parameter. It works fine. However, what do i do if i have more than one parameter? I put more "?" in the sql?

I read some where in the forum that there is a way like:

Select * From MYTABLE where ID1 =@ID1 OR ID2 = @ID2

Then in code of mydataset beforeOpen I put this script:

queryText = queryText.replace("@ID1",params["param1"]);
queryText = queryText.replace("@ID2",params["param2"]);

It seems to address the issue. But is there a better way to do it?

Please help me here.. Thanking you in advance..

Karvesh

Re: Dataset - More than one parameter [message #734812 is a reply to message #734810]

Sun, 09 October 2011 23:32

Eclipse User

Originally posted by: Donkey Hottie

10.10.2011 6:10, karvesh kirjoitti:
> Dear All
>
> I have a little issue here when it comes to passing more than one
> parameter in the sql dataset
>
> When i have only one parameter, i do the following:
> SELECT * FROM MYTABLE WHERE ID = ?
>
> and then i create a parameter in the dataset that i link to the report
> parameter. It works fine. However, what do i do if i have more than one
> parameter? I put more "?" in the sql?
>
> I read some where in the forum that there is a way like:
>
> Select * From MYTABLE where ID1 =@ID1 OR ID2 = @ID2
>
> Then in code of mydataset beforeOpen I put this script:
>
> queryText = queryText.replace("@ID1",params["param1"]);
> queryText = queryText.replace("@ID2",params["param2"]);
>
> It seems to address the issue. But is there a better way to do it?
>
> Please help me here.. Thanking you in advance..
>
> Karvesh

yes, there are better way! Your way is dangerous and vulnerable to SQL
Injection attacks (data is a http param to your app!).

Just put ?,?,? until there are enough parameters... The parameters are
numbered from 1..n and each param goes to it's ? in the string. That is
the purpose.

--

Q: Why did the astrophysicist order three hamburgers?
A: Because he was hungry.

Re: Dataset - More than one parameter [message #734876 is a reply to message #734812]

Mon, 10 October 2011 05:11

Eclipse User

One way we can try is to directly write the Query in

beforeOpen() like below

this.queryText = "Select * From MYTABLE where ID1 ='"+ params["param1"] + "' OR ID2 ='" + params["param2"] + "'";

Re: Dataset - More than one parameter [message #735045 is a reply to message #734876]

Mon, 10 October 2011 14:26

Eclipse User

Originally posted by: Donkey Hottie

10.10.2011 12:11, vandhanaa.r kirjoitti:
> One way we can try is to directly write the Query in
> beforeOpen() like below
>
> this.queryText = "Select * From MYTABLE where ID1 ='"+ params["param1"]
> + "' OR ID2 ='" + params["param2"] + "'";
>

Absolutely no. This creates bad software.

--

I dote on his very absence.
-- William Shakespeare, "The Merchant of Venice"

Re: Dataset - More than one parameter [message #735130 is a reply to message #735045]

Mon, 10 October 2011 22:47

Eclipse User

Hello

I tried the ?s in the sql and defined the parameters in the order specified. It works fine. Thanks a lot

Karvesh

Re: Dataset - More than one parameter [message #735150 is a reply to message #735130]

Tue, 11 October 2011 01:59

Eclipse User

I tried passing ? but in that case the order in which we pass the parameter is important right.

Wish to know if we directly append the parameters in the query how does it affect application /Code ? Related to the performance ?

Re: Dataset - More than one parameter [message #735496 is a reply to message #735150]

Tue, 11 October 2011 23:03

Eclipse User

Vandhanaa

The order of the parameters must match with the order of the ? used in the sql.
Regarding the speed, i have no idea. I suppose directly concatenating the parameters in the script can be faster because the parameters are not validated or prepared to prevent any injection.

However, i came across another problem when using "IN" clauses in my sql. I have an sql statement like:

SELECT * FROM myTable where ID IN (23.24,25)

I tried doing it using the parameter setting but cant seem to get it working. So for the moment i am passing the string of IDs as a parameter that i am concatenating in script before open

Karvesh

Previous Topic:	PHP BIRT Integration - parameter Passing
Next Topic:	How to change embedded image for each row of table?

Goto Forum:

-=] Back to Top [=-

Current Time: Fri Jul 25 04:25:44 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter