Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » Updating SQL query at the run-time(How do you insert runtime generated values into the SQL query?)
Updating SQL query at the run-time [message #843443] Fri, 13 April 2012 03:34 Go to next message
andrew smith is currently offline andrew smithFriend
Messages: 43
Registered: February 2012
Member
Hello,

What is the best way to update the SQL query for the dataset at a runtime in BIRT RCP?

For example, I want the dataSet to execute the following query;
SELECT [theName] FROM myDB WHERE [nameID] in (28,45)
If I hard-code this query, everything goes smoothly...

But what if I want (66,69) instead of (23,45) ie I do not know the nameID set a priori.
I tried setting up a variable 'beforeFactory'
report beforeFactory:
idList = "";

Then I would make an invisible auxilary table and in the 'onCreate:' event section, I would put
idList = "66,69";

then in the dataset's property binding I would do :
"SELECT [theName] FROM myDB WHERE [nameID] in (" +idList + ")"
But this does not seem to work...

I also tried it via parameters, it did not work either.

Finally, I tried to override the sql query via:

idList = "66,69";
DATASET QUERY DEFINITION:
SELECT [theName] FROM myDB WHERE [nameID] in (@nameIDstring)
EVENT: ODA Data Set::beforeOpen::
queryText = queryText.replace("@nameIDstring",idList);

That did not seem to work either...

Thank you in advance!!
Andrew

[Updated on: Fri, 13 April 2012 03:35]

Report message to a moderator

Re: Updating SQL query at the run-time [message #843977 is a reply to message #843443] Fri, 13 April 2012 15:02 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

This should have worked:


> idList = "66,69";
> DATASET QUERY DEFINITION:
> SELECT [theName] FROM myDB WHERE [nameID] in (@nameIDstring)
> EVENT: ODA Data Set::beforeOpen::
> queryText = queryText.replace("@nameIDstring",idList);

try
this.queryText = this.queryText.replace("@nameIDstring",idList);

See
http://www.eclipse.org/birt/phoenix/examples/reports/birt2.1/querymod/index.php

Jason


On 4/12/2012 11:34 PM, andrew smith wrote:
> Hello,
> What is the best way to update the SQL query for the dataset at a
> runtime in BIRT RCP?
> For example, I want the dataSet to execute the following query;
> SELECT [theName] FROM myDB WHERE [nameID] in (28,45)
> If I hard-code this query, everything goes smoothly...
>
> But what if I want (66,69) instead of (23,45) ie I do not know the
> nameID set a priori. I tried setting up a variable 'beforeFactory'
> report beforeFactory:
> idList = "";
>
> Then I would make an invisible auxilary table and in the 'onCreate:'
> event section, I would put idList = "66,69";
>
> then in the dataset's property binding I would do :
> "SELECT [theName] FROM myDB WHERE [nameID] in (" +idList + ")"
> But this does not seem to work...
>
> I also tried it via parameters, it did not work either.
> Finally, I tried to override the sql query via:
>
> idList = "66,69";
> DATASET QUERY DEFINITION:
> SELECT [theName] FROM myDB WHERE [nameID] in (@nameIDstring)
> EVENT: ODA Data Set::beforeOpen::
> queryText = queryText.replace("@nameIDstring",idList);
Re: Updating SQL query at the run-time [message #846413 is a reply to message #843977] Mon, 16 April 2012 03:25 Go to previous messageGo to next message
andrew smith is currently offline andrew smithFriend
Messages: 43
Registered: February 2012
Member
Jason,

Thanks a lot for your advice on placing 'this' reference in the code. It did help!! I also figured out another mistake I was making:
Under a 'property binding' tab, the "text query" text area, the SQL string should be enclosed in the quotation marks:
"Select * from myTable "
If I omit the quotation marks, then Birt produces errors.

Thank you for all your help!!
Andrew
Re: Updating SQL query at the run-time [message #854432 is a reply to message #846413] Tue, 24 April 2012 00:32 Go to previous messageGo to next message
andrew smith is currently offline andrew smithFriend
Messages: 43
Registered: February 2012
Member
Hello,

Just a quick question which is a follow up to my previous post...
Suppose I have a dataset (DS_01), whose query is defined at a design time and is static, for example;
'select [years] from myDB', which returns the vector of years ie [1900,1901,1902...].
Now suppose I issue another SQL query (to a different database) which is updated at a run-time with the 'years' vector (from DS_01) in a fashion described above to generate 2nd dataset (DS_02).
Finally, suppose I have another dataset, DS_03, which is static and its query does not change at a run-time.

Is it possible to create a joint data set from DS_02 and DS_03?

When I try to create a joint dataset, I get the following error:

A BIRT exception occurred.
Plug-in Provider:Eclipse BIRT Project
Plug-in Name:BIRT Data Engine
Plug-in ID:org.eclipse.birt.data
Version:3.7.2.v20120213
Error Code:data.engine.FailToExecuteScript
Error Message:Fail to execute script in function __bm_beforeOpen(). Source:
------
" + this.queryText = this.queryText.replace("@progIDstring",progIDVector.substr(0,progIDVector.length-2)); + "
-----
A BIRT exception occurred. See next exception for more information.
Error evaluating Javascript expression. Script engine error: ReferenceError: "progIDVector" is not defined. (/report/data-sets/oda-data-set[@id="508"]/method[@name="beforeOpen"]#1)
Script source: <inline>, line: 0, text:
__bm_beforeOpen()

It looks like the beforeOpen section can not be executed and as such the function that joins these dataset does not know what the contents of the DS_02, but I am not sure...

Thank you in advance!!

Andrew

[Updated on: Tue, 24 April 2012 00:36]

Report message to a moderator

Re: Updating SQL query at the run-time [message #855166 is a reply to message #854432] Tue, 24 April 2012 15:16 Go to previous message
Jason Weathersby is currently offline Jason WeathersbyFriend
Messages: 9167
Registered: July 2009
Senior Member

Andrew,

I do not believe this will work. You could always check to see if
progIDVector is not defined and then put in dummy values when this is
the case, but I doubt this is what you want.

Jason

On 4/23/2012 8:32 PM, andrew smith wrote:
> Hello,
> Just a quick question which is a follow up to my previous post...
> Suppose I have a dataset (DS_01), whose query is defined at a design
> time and is static, for example;
> 'select [years] from myDB', which returns the vector of years ie
> [1900,1901,1902...]. Now suppose I issue another SQL query (to a
> different database) which is updated at a run-time with the 'years'
> vector (from DS_01) in a fashion described above to generate 2nd dataset
> (DS_02). Finally, suppose I have another dataset, DS_03, which is static
> and its query does not change at a run-time.
> Is it possible to create a joint data set from DS_02 and DS_03?
> When I try to create a joint dataset, I get the following error:
>
> A BIRT exception occurred.
> Plug-in Provider:Eclipse BIRT Project
> Plug-in Name:BIRT Data Engine
> Plug-in ID:org.eclipse.birt.data
> Version:3.7.2.v20120213
> Error Code:data.engine.FailToExecuteScript
> Error Message:Fail to execute script in function __bm_beforeOpen(). Source:
> ------
> " + this.queryText =
> this.queryText.replace("@progIDstring",progIDVector.substr(0,progIDVector.length-2));
> + "
> -----
> A BIRT exception occurred. See next exception for more information.
> Error evaluating Javascript expression. Script engine error:
> ReferenceError: "progIDVector" is not defined.
> (/report/data-sets/oda-data-set[@id="508"]/method[@name="beforeOpen"]#1)
> Script source: <inline>, line: 0, text:
> __bm_beforeOpen()
>
> Thank you in advance!!
>
> Andrew
Previous Topic:Display image on top of text or through text
Next Topic:Disk cached datasets
Goto Forum:
  


Current Time: Tue Jan 21 20:58:57 GMT 2020

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

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

Back to the top