Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Dataset - More than one parameter(How to pass more than one parameter in the dataset sql )
Dataset - More than one parameter [message #734810] Mon, 10 October 2011 03:10 Go to next message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
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] Mon, 10 October 2011 03:32 Go to previous messageGo to next message
Eclipse UserFriend
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 09:11 Go to previous messageGo to next message
vandhanaa.r is currently offline vandhanaa.rFriend
Messages: 18
Registered: July 2011
Junior Member
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 18:26 Go to previous messageGo to next message
Eclipse UserFriend
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] Tue, 11 October 2011 02:47 Go to previous messageGo to next message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
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 05:59 Go to previous messageGo to next message
vandhanaa.r is currently offline vandhanaa.rFriend
Messages: 18
Registered: July 2011
Junior Member
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] Wed, 12 October 2011 03:03 Go to previous message
karvesh ghunsam is currently offline karvesh ghunsamFriend
Messages: 95
Registered: July 2011
Member
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:
  


Current Time: Fri Nov 28 06:03:40 GMT 2014

Powered by FUDForum. Page generated in 0.04917 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software