Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » 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 18:14 Go to next message
Rick Seiden is currently offline 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 #640180 is a reply to message #640076] Fri, 19 November 2010 08:33 Go to previous messageGo to next message
Rick Seiden is currently offline Rick Seiden
Messages: 4
Registered: August 2010
Junior Member
And now to answer my own question!

1) In Birt, go to Windows -> Preferences,
2) Drill down through Java -> Build Path -> Classpath Variables.
3) Add a new Classpath Variable to point to the folder containing your .class file

After doing this, my system finds the BirtJDBC.class file no problem. Now if only it worked. Smile
Re: Using POJO to access JDBC via Scripted Data Source [message #640210 is a reply to message #640076] Fri, 19 November 2010 10:27 Go to previous message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

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!
Previous Topic:onRender in table-header
Next Topic:Tomcat 5.5 + birt
Goto Forum:
  


Current Time: Fri Aug 01 10:09:10 EDT 2014

Powered by FUDForum. Page generated in 0.01933 seconds