Home » Archived » BIRT » Using POJO to access JDBC via Scripted Data Source(It's not a function, it's an object.)
Using POJO to access JDBC via Scripted Data Source [message #640076] |
Thu, 18 November 2010 23:14 |
Rick Seiden Messages: 4 Registered: August 2010 |
Junior Member |
|
|
I'm really pretty new to BIRT, and I'm sure this isn't going to be enough information to get the answer right away, so I apologize in advance for that.
My environment:
-Windows XP SP3
-Birt 2.6.1 installed from the complete install package, not by downloading Eclipse first and then installing it
Some background:
I have a JDBC data source that has millions and millions of records across several tables I need to join, and limit the input based on certain fields in one table. Something like this:
select p1.*,p2.* from p1 join p2 on p1.number=p2.number where p1.timefield>'1/1/2010' and p1.timefield<'10/1/2010'
That query works great, but I want the start and end dates to be parameters to my report. When I use a JDBC data source, I can't include the parameters in the sql statement, they have to go in the filter. So, all records are returned from the database to Birt, then filtered to match my criteria. This takes far too much time.
So, I figured I'd go with a scripted data source where I could use the parameters in JavaScript to build the SQL statement.
Using the Java perspective in the same Eclipse environment and workspace, I have created a new project called "jdbc", and a new class under there called BirtJDBC. The BirtJDBC object has a constructor that takes three parameters--the JDBC url, the username and the password. The BirtJDBC object has functions and variables needed to connect to my database via JDBC.
I've build another class that I used to test the BirtJDBC object, and I can connect to, and read from the database, without errors, so I know my BirtJDBC object is valid, at least for Java.
So, I go into the open function of the scripted data set, and start out with
db=new Packages.jdbc.BirtJDBC("jdbcurl","username","password")
That's all I have so far. When I try to preview the report, which I would expect to return nothing, I get an error:
A BIRT exception occurred: Error evaluating Javascript expression. Script engine error: TypeError: [JavaPackage jdbc.BirtJDBC] is not a function, it is object. (#1)
I've tried a few things, like using just Packages.BirtJDBC, and copying the BirtJDBC.class file to the following directories:
- ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\scri ptlib
- ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\WEB- INF
- ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\WEB- INF\classes
- ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\WEB- INF\lib
but nothing seems to work.
So, I guess my questions are:
1. When using a POJO to create a scripted data source, where do you put the compiled .class file so the data set's open function can "see" it?
2. What's the proper was to call the constructor for your class? This will vary, I think, based on what your project is named, but I'm not sure how, or if it relates to the answer to number 1 above.
Thanks in advance!
Rick Seiden
PS: I know I didn't make it detailed enough, and you guys are going to ask me for information before you can help me. I'll provide whatever I can. Thanks again!
|
|
| |
Re: Using POJO to access JDBC via Scripted Data Source [message #640210 is a reply to message #640076] |
Fri, 19 November 2010 15:27 |
|
Rick,
Why can you not use a parameter in the jdbc datasource? You should be
able to add a ? to signify a dataset parameter. You can then link this
to a report parameter in the dataset editor. Another option is to
change the query in the beforeOpen script of the dataset something like
this.queryText = this.queryText + " where mycolumnvalue = " +
params["mycolparametervalue"].value
On the classpath for the designer take a look at this post:
http://birtworld.blogspot.com/2009/12/birt-designer-classpat h-changes.html
Import packages not classes and then call constructors for example
if my class is my.jdbc.BirtJDBC I would do this
importPackage(Packages.my.jdbc);
db = new BirtJDBC("jdbcurl","username","password");
Jason
On 11/18/2010 6:14 PM, Rick Seiden wrote:
> I'm really pretty new to BIRT, and I'm sure this isn't going to be
> enough information to get the answer right away, so I apologize in
> advance for that.
>
> My environment:
>
> -Windows XP SP3
> -Birt 2.6.1 installed from the complete install package, not by
> downloading Eclipse first and then installing it
>
> Some background:
>
> I have a JDBC data source that has millions and millions of records
> across several tables I need to join, and limit the input based on
> certain fields in one table. Something like this:
>
>
> select p1.*,p2.* from p1 join p2 on p1.number=p2.number where
> p1.timefield>'1/1/2010' and p1.timefield<'10/1/2010'
>
>
> That query works great, but I want the start and end dates to be
> parameters to my report. When I use a JDBC data source, I can't include
> the parameters in the sql statement, they have to go in the filter. So,
> all records are returned from the database to Birt, then filtered to
> match my criteria. This takes far too much time.
>
> So, I figured I'd go with a scripted data source where I could use the
> parameters in JavaScript to build the SQL statement.
>
> Using the Java perspective in the same Eclipse environment and
> workspace, I have created a new project called "jdbc", and a new class
> under there called BirtJDBC. The BirtJDBC object has a constructor that
> takes three parameters--the JDBC url, the username and the password. The
> BirtJDBC object has functions and variables needed to connect to my
> database via JDBC.
>
> I've build another class that I used to test the BirtJDBC object, and I
> can connect to, and read from the database, without errors, so I know my
> BirtJDBC object is valid, at least for Java.
>
> So, I go into the open function of the scripted data set, and start out
> with
>
>
> db=new Packages.jdbc.BirtJDBC("jdbcurl","username","password")
>
>
> That's all I have so far. When I try to preview the report, which I
> would expect to return nothing, I get an error:
>
>
> A BIRT exception occurred: Error evaluating Javascript expression.
> Script engine error: TypeError: [JavaPackage jdbc.BirtJDBC] is not a
> function, it is object. (#1)
>
>
> I've tried a few things, like using just Packages.BirtJDBC, and copying
> the BirtJDBC.class file to the following directories:
>
>
> ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\scri ptlib
> ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\WEB- INF
> ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\WEB- INF\classes
> ...\org.eclipse.birt.report.viewer_2.6.1.v20100913\birt\WEB- INF\lib
>
>
> but nothing seems to work.
>
> So, I guess my questions are:
>
> 1. When using a POJO to create a scripted data source, where do you put
> the compiled .class file so the data set's open function can "see" it?
>
> 2. What's the proper was to call the constructor for your class? This
> will vary, I think, based on what your project is named, but I'm not
> sure how, or if it relates to the answer to number 1 above.
>
> Thanks in advance!
> Rick Seiden
>
> PS: I know I didn't make it detailed enough, and you guys are going to
> ask me for information before you can help me. I'll provide whatever I
> can. Thanks again!
|
|
|
Goto Forum:
Current Time: Tue Jun 18 04:24:18 GMT 2024
Powered by FUDForum. Page generated in 0.04641 seconds
|