Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Property Binding - report parameter to SQL
Property Binding - report parameter to SQL [message #542972] Mon, 28 June 2010 04:32 Go to next message
No real name is currently offline No real name
Messages: 17
Registered: June 2010
Junior Member
Hi people,

Good day. Thanks for reading my question...and for all the good answers in the past (especially to Jason).

I have an issue with my SQL? or Javascript? I dont know which one.
I'm trying to bind a couple of report params like "query" + param["param1"] + "query continues"

I'm getting Script engine error: unterminated string literal error which I think is related to this:
http://dev.eclipse.org/newslists/news.eclipse.birt/msg11874. html

Pls I need info on exactly how these quotation marks play/interact. I'm really lost on this and would appreciate some guidance or link to a guide/rules.

thanks friends Smile

Ime
Re: Property Binding - report parameter to SQL [message #543081 is a reply to message #542972] Mon, 28 June 2010 11:08 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
Its much easier to create a javascript variable to store the parameter value. Then the nested quotes problem goes away e.g.

var custId = params["CUST_ID"].value; // gets the value of CUST_ID from report parameter

if (custId != null)  {
	// this.queryText refers to the SQL query to be executed by BIRT
	this.queryText = this.queryText + " where id = " + custId;
}



HTH

[Updated on: Mon, 28 June 2010 11:09]

Report message to a moderator

Re: Property Binding - report parameter to SQL [message #543147 is a reply to message #542972] Mon, 28 June 2010 14:31 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

In the example you linked the quotation marks where because the user was
using a like with a string parameter. Can you give some more details on
your query?

Jason

On 6/28/2010 12:32 AM, asangansi@yahoo.com wrote:
> Hi people,
>
> Good day. Thanks for reading my question...and for all the good answers
> in the past (especially to Jason).
>
> I have an issue with my SQL? or Javascript? I dont know which one.
> I'm trying to bind a couple of report params like "query" +
> param["param1"] + "query continues"
>
> I'm getting Script engine error: unterminated string literal error which
> I think is related to this:
> http://dev.eclipse.org/newslists/news.eclipse.birt/msg11874. html
>
> Pls I need info on exactly how these quotation marks play/interact. I'm
> really lost on this and would appreciate some guidance or link to a
> guide/rules.
>
> thanks friends :)
>
> Ime
Re: Property Binding - report parameter to SQL [message #543164 is a reply to message #543147] Mon, 28 June 2010 15:20 Go to previous messageGo to next message
No real name is currently offline No real name
Messages: 17
Registered: June 2010
Junior Member
Thanks HTH and Jason.

My query:

SELECT residency.id
FROM residency
WHERE residency.location_id IN (SELECT location.uuid
FROM location
WHERE location.extid IN (SELECT extId FROM location1))

The last line "SELECT extId FROM location1" is the default for the param selectedLocation that the user can replace with another query or even with the actual values of extId values.

Thanks for helping.
Re: Property Binding - report parameter to SQL [message #543174 is a reply to message #542972] Mon, 28 June 2010 15:39 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
HTH = Hope That Helps Cool

Is location.extid a string or an integer column? If integer then you dont use quotes in the IN clause, if its a string you do.....

[Updated on: Mon, 28 June 2010 15:39]

Report message to a moderator

Re: Property Binding - report parameter to SQL [message #543181 is a reply to message #543174] Mon, 28 June 2010 15:53 Go to previous messageGo to next message
No real name is currently offline No real name
Messages: 17
Registered: June 2010
Junior Member
Thanks 'Hope That Helps' Smile
that was fast too

But I have two IN statements and I dont remember using ' ' in such SQL statements.

location.extID is a string though

still not sure of the places to escape and which quotation marks.

thanks
Re: Property Binding - report parameter to SQL [message #543189 is a reply to message #543181] Mon, 28 June 2010 16:18 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
OK forgetting about BIRT for a moment, if you are executing normal SQL query against a database using say SQuirreL (or similar) the format of an IN clause depends on what type the column is and how you "get" the values for the IN clause.

As you said you can have a sub-query returning you the values or you can have explicit values in the IN clause. If its a subquery you never need quotes as the database will handle this for you e.g.

select * from table where integer_col in (
    select integer_col_2 from table2
)


select * from table where string_col in (
    select string_col_2 from table2
)


Both of the above work without quotes since its a sub-query providing the IN clause data values, independent of whether its a string or an integer column. Obviously the column types must match, in that the following will not work since the column types do not match:

select * from table where integer_col in (
    select string_col_2 from table2
)



Where it gets awkward is if you give the IN clause explicit values, which you indicate the user can do. In this scenario you dont use quotes if the column is an integer:

select * from table where integer_col in (12, 24, 55)


but if the column is a string you must use single quotes

select * from table where string_column in ('blah', 'blah', 'blah')


In your report there are two scenarios:

1) User submits SQL snippet - (as an aside this may not be the safest thing to do as it leaves you open to malicious "SQL injection"). In this case the query should end up as

SELECT residency.id
FROM residency
WHERE residency.location_id IN (
    SELECT location.uuid FROM location
    WHERE location.extid IN (
        SELECT ext FROM usertable123
    )
)


Note: no quotes as its a qub-query

2) User submits actual extId values, which are strings.

SELECT residency.id
FROM residency
WHERE residency.location_id IN (
    SELECT location.uuid FROM location
    WHERE location.extid IN (
        'uservalue1', 'uservalue2', 'uservalue3'
    )
)


Note: quotes are required as the values are explicitly specified strings.

It would be easiest if the user had to use the quotes in the string they pass in (when specifying values) as then you wouldnt know/care if the input was SQL or explicit values.

In other words, user would enter either of the following (exactly as they appear):
         SELECT ext FROM usertable123

or
        'uservalue1', 'uservalue2', 'uservalue3'


Does this make any sense?

[Updated on: Mon, 28 June 2010 16:20]

Report message to a moderator

Re: Property Binding - report parameter to SQL [message #543211 is a reply to message #543189] Mon, 28 June 2010 17:25 Go to previous messageGo to next message
No real name is currently offline No real name
Messages: 17
Registered: June 2010
Junior Member
perfect sense, as expected.
Thanks!

The problem is I get error with this:

"SELECT residency.id
FROM residency
WHERE residency.location_id IN (SELECT location.uuid
FROM location
WHERE location.extid IN ("
+ param["selectLocationQuery "] + "))"

and also with this:

"SELECT residency.id
FROM residency
WHERE residency.location_id IN (SELECT location.uuid
FROM location
WHERE location.extid IN ('"
+ param["selectLocationQuery "] + "'))"

I tried the scripting way, modifying queryText and it worked but I would just want to learn whats wrong with this method - I'm not dealing with the quotation marks properly?
I get Script engine error: unterminated string literal error

thanks
Re: Property Binding - report parameter to SQL [message #543262 is a reply to message #542972] Mon, 28 June 2010 20:51 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
When trying to do the "non-script" method, where are you actually putting the SQL you included above? Is it in the dataset editor? If so there are a few issues with the SQL

1) You dont wrap the entire SQL statement in quotes, as you have done.

2) You can only reference parameters by name in the scripting method i.e. you cannot use syntax param["selectLocationQuery"] in the dataset editor. This is because the above is not SQL, its javascript syntax, and only SQL is allowed in the dataset editor. Javascript syntax is only allowed in the "script" method to modify the queryText as you say you did.

You can reference parameters in the dataset editor using "Variable substitution", whereby the dataset parameter is represented by a ? symbol. If you define a dataset parameter and link it to the report parameter you should be able to write the query as

SELECT residency.id
FROM residency
WHERE residency.location_id IN (
    SELECT location.uuid FROM location 
    WHERE location.extid IN (?)
)
Re: Property Binding - report parameter to SQL [message #543263 is a reply to message #543262] Mon, 28 June 2010 20:57 Go to previous messageGo to next message
No real name is currently offline No real name
Messages: 17
Registered: June 2010
Junior Member
I was using th property bindings in the dataset editor.
It tries to use javascript for parameter substitution to make the sql...but it seems to be frought with escape errors...or something.

I'm now using the method u suggested but I just want to be sure I learnt from that experience...knowing that what went wrong go go wrong again somewhere else.

thanks

[Updated on: Mon, 28 June 2010 21:00]

Report message to a moderator

Re: Property Binding - report parameter to SQL [message #543355 is a reply to message #542972] Tue, 29 June 2010 08:15 Go to previous messageGo to next message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
I never bother with the "Property Binding" tab in dataset editor. I always just use the "Query" tab......and if I need a scripted dataset I use the scripting method we used above.

hope you've a better idea now
Trevor
Re: Property Binding - report parameter to SQL [message #543746 is a reply to message #543355] Wed, 30 June 2010 13:19 Go to previous messageGo to next message
No real name is currently offline No real name
Messages: 17
Registered: June 2010
Junior Member
Thanks, everything is going well.
Just that u mentioned the risk of SQL injection.

I'm now checking if the string contains the words INTO, INSERT, UPDATE
basically using s.indexOf as there is no contains() method in javascript.

thanks again
icon14.gif  Re: Property Binding - report parameter to SQL [message #543777 is a reply to message #542972] Wed, 30 June 2010 14:29 Go to previous message
Missing name Missing name is currently offline Missing name Missing name
Messages: 94
Registered: November 2009
Member
Great stuff, good to hear Razz
Previous Topic:How to get BIRT 2.6.0 source from CVS repository?
Next Topic:Writing a custom data provider
Goto Forum:
  


Current Time: Fri Oct 31 09:19:56 GMT 2014

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

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