|
|
Re: Property Binding - report parameter to SQL [message #543147 is a reply to message #542972] |
Mon, 28 June 2010 14:31 |
|
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 #543189 is a reply to message #543181] |
Mon, 28 June 2010 16:18 |
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 #543263 is a reply to message #543262] |
Mon, 28 June 2010 20:57 |
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
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.04835 seconds