Home » Archived » BIRT » using temporary db tables in birt?
| using temporary db tables in birt? [message #255220] |
Wed, 12 September 2007 17:53  |
Eclipse User |
|
|
|
Hello,
is it possible to create and fill a temporary database table and using
it's content in a birt report?
I am using Birt 2.2 with mysql 5. The db connection is done directly
with JDBC without JNDI.
I don't know how birt handles db connections and if it always uses only
one connection per data source definition. Is it possible to create and
fill the temp table with a javascript function which uses the jdbc
connection defined inside the report? If so, where can I get the used db
connection handle?
The reason why I ask for this feature is because I want to create a
report which uses a small subset out of a huge table (huge in both
dimensions, rows and columns). The report consists of several different
presentations and groupings of the same data. I don't want to waste time
with selecting several times nearly the same data from the huge table.
Any hint is welcome.
Ingo
|
|
| |
| Re: using temporary db tables in birt? [message #255374 is a reply to message #255233] |
Thu, 13 September 2007 16:16  |
| Eclipse User |
|
|
|
I am aware of the scripting abilities, but I would prefer using temporary tables. If possible to use them, there are some big advantages:
1. I don't have to deploy additional JAR(s)
2. After the temp table is created, everything works without additional scripting
3. I can use database filters, sorting, ... and/or the birt features
But nevertheless I started coding a scripted datasource which caches the selected data. I would like to do this without additional Java classes and faced another problem:
How could I connect to a database? My problem is caused by the classloader, which seems not to be able to load the mysql jdbc driver inside the Javascript code (with Class.forName("com.mysql.jdbc.Driver") method). It creates a Java class not found exception. This happens although the same driver is used inside the report for another datasource.
Is it possible to connect to the database without creating additional Java classes? If so, how could this be achieved? Where do I have to place the mysql.jar file, so that it can be loaded from the Javascript code?
Ingo
Snjezana Peco schrieb:
> You can use a scripted data source and create a temporary table in a
> Java class. If you are using MySQL 5, you can use views instead of
> temporary tables.
>
> Snjeza
>
> Ingo wrote:
>> Hello,
>>
>> is it possible to create and fill a temporary database table and using
>> it's content in a birt report?
>> I am using Birt 2.2 with mysql 5. The db connection is done directly
>> with JDBC without JNDI.
>> I don't know how birt handles db connections and if it always uses only
>> one connection per data source definition. Is it possible to create and
>> fill the temp table with a javascript function which uses the jdbc
>> connection defined inside the report? If so, where can I get the used db
>> connection handle?
>> The reason why I ask for this feature is because I want to create a
>> report which uses a small subset out of a huge table (huge in both
>> dimensions, rows and columns). The report consists of several different
>> presentations and groupings of the same data. I don't want to waste
>> time with selecting several times nearly the same data from the huge
>> table.
>> Any hint is welcome.
>>
>> Ingo
>>
|
|
|
| Re: using temporary db tables in birt? [message #255399 is a reply to message #255374] |
Thu, 13 September 2007 18:22 |
| Eclipse User |
|
|
|
Ingo wrote:
> I am aware of the scripting abilities, but I would prefer using
> temporary tables. If possible to use them, there are some big advantages:
> 1. I don't have to deploy additional JAR(s)
> 2. After the temp table is created, everything works without additional
> scripting
> 3. I can use database filters, sorting, ... and/or the birt features
>
You would have all these advantages if you would use MySQL 5 views.
You wouldn't have to use a scripted data source. Additional advantage
would be improving performance because you wouldn't create temporary tables.
> But nevertheless I started coding a scripted datasource which caches the
> selected data. I would like to do this without additional Java classes
> and faced another problem:
> How could I connect to a database? My problem is caused by the
> classloader, which seems not to be able to load the mysql jdbc driver
> inside the Javascript code (with Class.forName("com.mysql.jdbc.Driver")
> method). It creates a Java class not found exception. This happens
> although the same driver is used inside the report for another datasource.
> Is it possible to connect to the database without creating additional
> Java classes? If so, how could this be achieved? Where do I have to
> place the mysql.jar file, so that it can be loaded from the Javascript
> code?
>
There are some classloading issues in BIRT.
If you send me your log, I will try to help you.
Tschüs!
Snjeza
|
|
| |
| Re: using temporary db tables in birt? [message #255684 is a reply to message #255613] |
Mon, 17 September 2007 18:11 |
| Eclipse User |
|
|
|
Ingo wrote:
>> You would have all these advantages if you would use MySQL 5 views.
>> You wouldn't have to use a scripted data source. Additional advantage
>> would be improving performance because you wouldn't create temporary
>> tables.
> Please correct me, if I am wrong: There is a performance difference between using temp tables which contain less data and using a view on a huge table - at least if I do more than one query.
> The table contains log data with timestamps and a lot of parameters. The temp table would be filled with specific data of a short period like a week, while the original table contains data of years. The view would have to do the complicated query several times on the huge table. Even if the database would cache the data, I think that the temp table would perform better.
> Maybe I'll find some time to figure it out.
>
Views are always better than temporary tables. If you are using temp.
tables, the database has to do the following:
- execute query
- create table
- populate data
- create indexes
MySQL cache is never used with temp. tables.
View does only "execute query" operation. This operation uses MySQL cache.
Queries don't have to be complicated. You can create a view using some
other view.
Queries that use created temporary tables can run faster than views, but
creating temporary tables is slower.
>> There are some classloading issues in BIRT.
>> If you send me your log, I will try to help you.
> I don't know, which log you want to see. The following line inside the Javascript database code causes a java Class not found exception:
> java.lang.Class.forName("com.mysql.jdbc.Driver");
> After some investigations I found out, that javascript try catch blocks can be used to catch exceptions which happen in java as well. So I surrounded the whole database connection script in a try block and caught the mentioned exception:
> JavaException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
> This happens in eclipse and the runtime environment, although the same report uses the same driver for "normal" jdbc datasources.
>
..log file is in your <workspace>/.metadata directory.
Snjeza
|
|
|
| Re: using temporary db tables in birt? [message #255768 is a reply to message #255684] |
Tue, 18 September 2007 18:38 |
| Eclipse User |
|
|
|
Snjezana Peco schrieb:
>
> Views are always better than temporary tables. If you are using temp.
> tables, the database has to do the following:
>
> - execute query
> - create table
> - populate data
> - create indexes
>
> MySQL cache is never used with temp. tables.
> View does only "execute query" operation. This operation uses MySQL cache.
> Queries don't have to be complicated. You can create a view using some
> other view.
> Queries that use created temporary tables can run faster than views, but
> creating temporary tables is slower.
If I say "complicated" queries, I mean in terms of db actions which are causing heavy load on the db server like selecting the same table twice or even more often with different criteria and joining the results to get a single result set. And these queries are done several times with different where clauses and "group by" columns. I am pretty confident, that in cases like these the db load could be reduced by first collecting a smaller "precalculated" set of data in temp tables and doing the different additional queries on them.
Even if in most cases views would perform good or better than working with temp tables, I am sure that there are situations where it makes sense to use them.
>
>>> There are some classloading issues in BIRT.
>>> If you send me your log, I will try to help you.
>> I don't know, which log you want to see. The following line inside the
>> Javascript database code causes a java Class not found exception:
>> java.lang.Class.forName("com.mysql.jdbc.Driver");
>> After some investigations I found out, that javascript try catch
>> blocks can be used to catch exceptions which happen in java as well.
>> So I surrounded the whole database connection script in a try block
>> and caught the mentioned exception:
>> JavaException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
>> This happens in eclipse and the runtime environment, although the same
>> report uses the same driver for "normal" jdbc datasources.
>>
>
> .log file is in your <workspace>/.metadata directory.
I just looked for it. Unfortunately the .log is empty. I've found something concerning my problems in <workspace> /.metadata/.plugins/org.eclipse.birt.report.viewer/logs/Repo rtEngine_2007_09_18_23_04_51.log, which is attached later.
Additional, I already tried to place the mysql_jdbc.jar file in different folders like <eclipse_home>/plugins/org.mozilla.rhino_1.6.0.v20070531/lib and hoped that the classloader would find it - without success. Maybe it would have worked if I had changed the manifest file, too. But I do don't like to change to much as long as there is a smarter way of making additional jars accessible to javascript.
18.09.2007 23:04:52 org.eclipse.birt.report.model.parser.ModuleReader readModule
SCHWERWIEGEND: Parsed file was not found.
18.09.2007 23:04:52 org.eclipse.birt.report.model.parser.ModuleReader readModule
SCHWERWIEGEND: Parsed file was not found.
18.09.2007 23:04:53 org.eclipse.birt.report.engine.executor.ExecutionContext evaluate
SCHWERWIEGEND: Wrapped java.lang.ClassNotFoundException: com.mysql.jdbc.Driver (<inline>#1)
org.mozilla.javascript.WrappedException: Wrapped java.lang.ClassNotFoundException: com.mysql.jdbc.Driver (<inline>#1)
at org.mozilla.javascript.Context.throwAsScriptRuntimeEx(Contex t.java:1776)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:191)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMetho d.java:197)
at org.mozilla.javascript.optimizer.OptRuntime.call1(OptRuntime .java:63)
at org.mozilla.javascript.gen.c11._c0(<inline>:1)
at org.mozilla.javascript.gen.c11.call(<inline>)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFacto ry.java:304)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime .java:2769)
at org.mozilla.javascript.gen.c11.call(<inline>)
at org.mozilla.javascript.gen.c11.exec(<inline>)
at org.eclipse.birt.core.script.ScriptContext.eval(ScriptContex t.java:223)
at org.eclipse.birt.report.engine.executor.ExecutionContext.eva luate(ExecutionContext.java:571)
at org.eclipse.birt.report.engine.executor.ExecutionContext.eva luate(ExecutionContext.java:550)
at org.eclipse.birt.report.engine.script.internal.ScriptExecuto r.handleJSInternal(ScriptExecutor.java:60)
at org.eclipse.birt.report.engine.script.internal.ScriptExecuto r.handleJS(ScriptExecutor.java:44)
at org.eclipse.birt.report.engine.script.internal.ReportScriptE xecutor.handleInitialize(ReportScriptExecutor.java:30)
at org.eclipse.birt.report.engine.api.impl.EngineTask.loadDesig n(EngineTask.java:1083)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.doR un(RunAndRenderTask.java:89)
at org.eclipse.birt.report.engine.api.impl.RunAndRenderTask.run (RunAndRenderTask.java:71)
at org.eclipse.birt.report.service.ReportEngineService.runAndRe nderReport(Unknown Source)
at org.eclipse.birt.report.service.ReportEngineService.runAndRe nderReport(Unknown Source)
at org.eclipse.birt.report.service.BirtViewerReportService.runA ndRenderReport(Unknown Source)
at org.eclipse.birt.report.service.actionhandler.BirtGetPageAll ActionHandler.__execute(Unknown Source)
at org.eclipse.birt.report.service.actionhandler.AbstractBaseAc tionHandler.execute(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseDoc umentProcessor.__executeAction(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseCom ponentProcessor.executeAction(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.BirtDocumentPro cessor.handleGetPageAll(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.eclipse.birt.report.soapengine.processor.AbstractBaseCom ponentProcessor.process(Unknown Source)
at org.eclipse.birt.report.soapengine.endpoint.BirtSoapBindingI mpl.getUpdatedObjects(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.apache.axis.providers.java.RPCProvider.invokeMethod(RPCP rovider.java:397)
at org.apache.axis.providers.java.RPCProvider.processMessage(RP CProvider.java:186)
at org.apache.axis.providers.java.JavaProvider.invoke(JavaProvi der.java:323)
at org.apache.axis.strategies.InvocationStrategy.visit(Invocati onStrategy.java:32)
at org.apache.axis.SimpleChain.doVisiting(SimpleChain.java:118)
at org.apache.axis.SimpleChain.invoke(SimpleChain.java:83)
at org.apache.axis.handlers.soap.SOAPService.invoke(SOAPService .java:453)
at org.apache.axis.server.AxisServer.invoke(AxisServer.java:281 )
at org.apache.axis.transport.http.AxisServlet.doPost(AxisServle t.java:699)
at org.eclipse.birt.report.servlet.BirtSoapMessageDispatcherSer vlet.doPost(Unknown Source)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:760)
at org.apache.axis.transport.http.AxisServletBase.service(AxisS ervletBase.java:327)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFi lter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(App licationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(Standar dWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineVa lveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase. java:995)
at org.apache.catalina.core.StandardContextValve.invoke(Standar dContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineVa lveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.CertificatesValve.invoke(Certific atesValve.java:246)
at org.apache.catalina.core.StandardPipeline$StandardPipelineVa lveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase. java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardCont ext.java:2422)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHo stValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineVa lveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(Error DispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineVa lveContext.invokeNext(StandardPipeline.java:641)
at org.eclipse.tomcat.internal.EclipseErrorReportValve.invoke(E clipseErrorReportValve.java:153)
at org.apache.catalina.core.StandardPipeline$StandardPipelineVa lveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase. java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(Standard EngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineVa lveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPip eline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase. java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapte r.java:199)
at org.apache.coyote.http11.Http11Processor.process(Http11Proce ssor.java:828)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHand ler.processConnection(Http11Protocol.java:700)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndp oint.java:584)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.ru n(ThreadPool.java:683)
at java.lang.Thread.run(Unknown Source)
Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
at java.lang.ClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findCl assInternal(BundleLoader.java:429)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findCl ass(BundleLoader.java:369)
at org.eclipse.osgi.framework.internal.core.BundleLoader.findCl ass(BundleLoader.java:357)
at org.eclipse.osgi.internal.baseadaptor.DefaultClassLoader.loa dClass(DefaultClassLoader.java:83)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClassInternal(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:174)
... 79 more
|
|
|
| Re: using temporary db tables in birt? [message #255840 is a reply to message #255768] |
Wed, 19 September 2007 10:17 |
| Eclipse User |
|
|
|
Ingo wrote:
> Snjezana Peco schrieb:
>>
>> Views are always better than temporary tables. If you are using temp.
>> tables, the database has to do the following:
>>
>> - execute query
>> - create table
>> - populate data
>> - create indexes
>>
>> MySQL cache is never used with temp. tables.
>> View does only "execute query" operation. This operation uses MySQL
>> cache.
>> Queries don't have to be complicated. You can create a view using some
>> other view.
>> Queries that use created temporary tables can run faster than views,
>> but creating temporary tables is slower.
> If I say "complicated" queries, I mean in terms of db actions which are
> causing heavy load on the db server like selecting the same table twice
> or even more often with different criteria and joining the results to
> get a single result set. And these queries are done several times with
> different where clauses and "group by" columns. I am pretty confident,
> that in cases like these the db load could be reduced by first
> collecting a smaller "precalculated" set of data in temp tables and
> doing the different additional queries on them.
> Even if in most cases views would perform good or better than working
> with temp tables, I am sure that there are situations where it makes
> sense to use them.
>
Sigh. Never mind.
>>
>>>> There are some classloading issues in BIRT.
>>>> If you send me your log, I will try to help you.
>>> I don't know, which log you want to see. The following line inside
>>> the Javascript database code causes a java Class not found exception:
>>> java.lang.Class.forName("com.mysql.jdbc.Driver");
>>> After some investigations I found out, that javascript try catch
>>> blocks can be used to catch exceptions which happen in java as well.
>>> So I surrounded the whole database connection script in a try block
>>> and caught the mentioned exception:
>>> JavaException: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
>>> This happens in eclipse and the runtime environment, although the
>>> same report uses the same driver for "normal" jdbc datasources.
>>>
>>
>> .log file is in your <workspace>/.metadata directory.
>
> I just looked for it. Unfortunately the .log is empty. I've found
> something concerning my problems in
> <workspace> /.metadata/.plugins/org.eclipse.birt.report.viewer/logs/Repo rtEngine_2007_09_18_23_04_51.log,
> which is attached later.
> Additional, I already tried to place the mysql_jdbc.jar file in
> different folders like
> <eclipse_home>/plugins/org.mozilla.rhino_1.6.0.v20070531/lib and hoped
> that the classloader would find it - without success. Maybe it would
> have worked if I had changed the manifest file, too. But I do don't like
> to change to much as long as there is a smarter way of making additional
> jars accessible to javascript.
>
You don't have to copy any jar if you use buddy classloading. You can
take a look at these threads:
http://dev.eclipse.org/newslists/news.eclipse.birt/msg22791. html
http://dev.eclipse.org/newslists/news.eclipse.birt/msg20903. html
Snjeza
|
|
|
Goto Forum:
Current Time: Fri Oct 31 04:31:07 EDT 2025
Powered by FUDForum. Page generated in 0.05416 seconds |
Home