Property Binding query in Data Set

Hello!

I have a data set with following query:

select
ITIMUSER.PERSON_CN.CN
from ITIMUSER.PERSON_CN,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT
where ITIMUSER.PERSON_CN.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.OMISTAJA AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.DN AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.ERUID = ?

I have a data set parameter like:
param1 String input 'KR_Suurasiakasyhteydet Assistentti_baduere' None

when I preview results, everything works fine. But when I change data set so that query is:
select
ITIMUSER.PERSON_CN.CN
from ITIMUSER.PERSON_CN,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT

and in Property Binding I have defined:
"select
ITIMUSER.PERSON_CN.CN
from ITIMUSER.PERSON_CN,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT
where ITIMUSER.PERSON_CN.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.OMISTAJA AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.DN AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.ERUID = " + ?

then I got following error, eventhought as far as I understand the query should be exactly the same:

Cannot set the string value (KR_Suurasiakasyhteydet Assistentti_baduere) to parameter 1
Cannot set preparedStatement parameter string value.
SQL error #1: [ibm][db2][jcc][10145][10844] Virheellinen parametri 1: Parametrin hakemisto ei kuulu arvoalueeseen.

I appreciated if someone could say what goes wrong.

Find more posts tagged with

Comments

bhanley

I am not sure how you are setting the Property Binding you describe below. The binding of a parameter into a query is handled in an expression editor. Here you would generally not be executing additional SQL. Why are you replicating the SQL in the data set in the parameter binding.

Can you post the design to this thread so I can have a look?

HannuN

I updated BirtDesigner 2.2.1 -> 2.3 and when I tried again, I got a different error message, which basically said that there is an error in the script. Based on error message I then surrounded ? with single quotes from Property Bindind like this:
select
ITIMUSER.PERSON_CN.CN
from ITIMUSER.PERSON_CN,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT
where ITIMUSER.PERSON_CN.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.OMISTAJA AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.DN AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.ERUID = " + '?'

and now it works fine. I tried same also in BIRTDesigner 2.2.1, but it still fails.

The reason, why I want to use property bindind is that I'm building a subreport type of report and this query gets it's parameter from report row e.g 'KR_Suurasiakasyhteydet Assistentti' but in this query I have to add to the end e.g. '_baduere' i.e. the final query will be:

select
ITIMUSER.PERSON_CN.CN
from ITIMUSER.PERSON_CN,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA,
ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT
where ITIMUSER.PERSON_CN.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.OMISTAJA AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.DN = ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.DN AND ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.ERUID = " + '?' + '/_baduere' ESCAPE "/"

Maybe the data model is not the best one but at least now I able to proceed.

LeBrtExprt

Hello,

I saw your posted this message earlier this year, but I also ran in to the same thing and thought I would reply for others. It has to do with the Javascript syntax. Btw, doing a preview does not factor in the property binding, only when you run the report. Try change your query text to the following:

"select " +
" ITIMUSER.PERSON_CN.CN " +
" from ITIMUSER.PERSON_CN, " +
" ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA, " +
" ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT " +
" where ITIMUSER.PERSON_CN.DN = " + "
" ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.OMISTAJA AND " +
" ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT_OMISTAJA.DN = " +
" ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.DN AND " +
" ITIMUSER.KVHRYHMATMUOKKAUSACCOUNT.ERUID = ? "

Le BIRT Expert