Problem using "username" in dynamic query

I'm using Actuate Birt going against SQL 2008. I have no Java experience at all - all of the code here is pulled from examples I found on the web and modified to fit my setup.

This is the query in the dataset in my report:
select * from d_PAT where P_ID in

I have this script written in the 'beforeOpen' section of the dataset. This is supposed to grab the username and plug it onto the end of my query:

this.queryText = this.queryText + " (select P_ID from U_PAT where USER_ID in (select USER_ID from d_USERS where USERNAME = '" + reportContext.getAppContext().get("ServerUserName") + "'))"

When I run it I get a blank report. If I run the query in SQL and hardcode the ID and username it works fine.

I also tried changing the script to this, just to try something different, but this also pulls up a blank report:

myMap = reportContext.getAppContext();
usr = myMap.get("ServerUserName");
if(usr != null)
{
this.queryText = this.queryText + " (select P_ID from U_PAT where USER_ID in (select USER_ID from d_USERS where USERNAME = '" + usr + "'))"
}

Also, I wrote a simple report and in the "initialize" section I used the same script and it works - the report returns the username.

I am stumped. I think there may be something wrong with the Java even though it reads correctly to me. It looks like it makes sense but like I said earlier I don't know Java. Any help would be greatly appreciated.

Find more posts tagged with

Comments

Megha Nidhi Dahal

Hi, 
 
Can the username be passed as a report parameter? If so, you can replace the <pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.getAppContext().get("ServerUserName")</pre> with <pre class='_prettyXprint _lang-auto _linenums:0'>params["ParameterName"]</pre>. 
 
I have used things this way and it has worked. But be sure not to pass the username in quotes as you are explicitly applying the quotes in the script. 
 
-Megha

nelsont

<blockquote class='ipsBlockquote' data-author="'Megha Nidhi Dahal'" data-cid="96448" data-time="1329844798" data-date="21 February 2012 - 10:19 AM">
Hi, 
 
Can the username be passed as a report parameter? If so, you can replace the <pre class='_prettyXprint _lang-auto _linenums:0'>reportContext.getAppContext().get("ServerUserName")</pre> with <pre class='_prettyXprint _lang-auto _linenums:0'>params["ParameterName"]</pre>. 
 
I have used things this way and it has worked. But be sure not to pass the username in quotes as you are explicitly applying the quotes in the script. 
 
-Megha </blockquote>
 
Thanks Megha, 
 
Unforunately no, I can't pass in the username as a parameter because we don't want someone to be able to view another users records. This data contains some health information and HIPPA regulations require that only specific people are allowed to view that data.

bgbaird

You can use it as a parameter, but hide the parameter.

nelsont

No, the user would still have to enter thier username. That means anyone could enter anyone's username. I need to read the username from the server so that the report only runs for who is logged in.

johnw

Rewrite your query to be:
(select
P_ID from U_PAT where USER_ID in
(select USER_ID from d_USERS
where USERNAME = ?))

This will create a dataset parameter. Set the dataset parameter type to string. In the dataset parameters
default value expression, use:
reportContext.getAppContext().get("ServerUserName")

You will need to be sure to pass in the "ServerUserName" value into the application context, assuming you are using the report engine API and not the web viewer example.

You don't need to re-write the query, and you really shouldn't do it anyway since it is a bad security practice.

nelsont

I'll give this a shot. Just so I'm sure I understand, this is taking everything out of the script portion of the report. I should be using your suggestion as the actual query in my dataset and I should have nothing in the script. Your suggestion makes sense and I'll give it a try as soon as I am back in front of my machine. I'll post a reply letting you know if I had any luck. Thanks very much.

nelsont

While I was trying your suggestion I noticed something else. I already had a parameter defined in my dataset. The full query in my dataset is this:

select * from d_PAT where P_ID = ? and P_ID in...

I noticed that the existing parameter was not mapped to a report parameter. I had the option of mapping it to P_ID, so I did.

The problem is that I also implemented your solution at the same time, so I'm not sure which fixed it. I honestly think the missing parameter was the problem, since your solution and the two previous things I tried are basically doing the exact same things, only written differently.

However, just seeing what you did there gave me some more understanding into how the underlying engine works in the reports and helps me fit things together a little better, so you helped regardless. I also would not have been in the position to see the missing parameter if I wasn't in there trying your solution.

FYI I left the final report using your suggestion. It will be easier for someone to come behind me and see quickly what the query is doing while not needing to read any java script.

Thanks again, I really appreciate it.

nelsont

I went back into my report and took out the mapping for the first parameter, and it breaks again. When I mapped the first parameter to P_ID again it works.

So the answer was that the first parameter was not mapped to a report parameter, not that the Java was incorrect. Thanks for your reply anyway.