Home » Archived » 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  |
 andrew smith
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  |
|
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 #854432 is a reply to message #846413] |
Tue, 24 April 2012 00:32 |
andrew smith
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 |
|
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
|
|
|
Goto Forum:
Current Time: Tue Mar 21 16:04:43 GMT 2023
Powered by FUDForum. Page generated in 0.02490 seconds |
] 
Search
Help
Register
Login
Home
