[Mondrian] problem with connect to sql server and hqldb
hadi ganteng
narou71185 at yahoo.com
Sun Jul 20 22:52:09 EDT 2008
i have a problem with connecting Mondrian to Sql Server 2000 and Hsqldb 1.8.0.8,
i use Apache Tomcat 5.5 as the web server and Mozzila Firefox as the browser
the Mondrian version that i use is Mondrian 2.4.2.9831
here is the detail for each problem with the connection
i do nothing with the FoodMart.xml and mondrian.properties
---------------
SQL Server 2000
---------------
when i connect Mondrian to sql server, it return no error, but when i do
the drill down, Mondrian return a null value in each cell.
i connect the Mondrian with SQL Server via ODBC in system DSN, i also do the login in the ODBC.
i tested this ODBC with Borland Delphi 7.0, it works fine.
i attached file picture when i run the Mondrian.
this is the mondrian.jsp i edited to connect to SQL Server:
------------------------------------
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<%-- uses a dataSource --%>
<%-- jp:mondrianQuery id="query01" dataSource="jdbc/MondrianFoodmart" catalogUri="/WEB-INF/demo/FoodMart.xml" --%>
<%-- uses mysql --%>
<%-- jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml"--%>
<%-- uses a role defined in FoodMart.xml --%>
<%-- jp:mondrianQuery role="California manager" id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver" jdbcUrl="jdbc:odbc:MondrianFoodMart" catalogUri="/WEB-INF/queries/FoodMart.xml" --%>
<jp:mondrianQuery id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver"
jdbcUser="narou" jdbcPassword="071185"
jdbcUrl="jdbc:odbc:MondrianSQLServer" catalogUri="/WEB-INF/queries/FoodMart.xml">
select
{[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
{([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])
</jp:mondrianQuery>
<c:set var="title01" scope="session">Test Query uses Mondrian OLAP</c:set>
--------------------------------------
--------------
Hsqldb 1.8.0.8
--------------
i use HSQL Database Engine Server in Hsqldb for the database. i attached the picture file
when i run the Hsqldb server and the manager. Hsqldb has this error:
--------------------------------
org.apache.jasper.JasperException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException:
Mondrian Error:Internal error: while building member cache; sql=[select "time_by_day"."the_year" as
"c0" from "time_by_day" as "time_by_day" group by "time_by_day"."the_year" order by "time_by_day".
"the_year" ASC]
org.apache.jasper.JasperException: javax.servlet.jsp.JspException: javax.servlet.jsp.JspException:
Mondrian Error:Internal error: while building member cache; sql=[select "time_by_day"."the_year" as
"c0" from "time_by_day" as "time_by_day" group by "time_by_day"."the_year" order by "time_by_day".
"the_year" ASC]
at org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:476)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:371)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:315)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:265)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:269)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at com.tonbeller.wcf.controller.RequestFilter$MyHandler.normalRequest(RequestFilter.java:139)
at com.tonbeller.wcf.controller.RequestSynchronizer.handleRequest(RequestSynchronizer.java:127)
at com.tonbeller.wcf.controller.RequestFilter.doFilter(RequestFilter.java:263)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:215)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:188)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:210)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:174)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:117)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:108)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:151)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:870)
at org.apache.coyote.http11.Http11BaseProtocol$Http11ConnectionHandler.processConnection(Http11BaseProtocol.java:665)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:528)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:81)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:685)
at java.lang.Thread.run(Unknown Source)
-----------------------------------
this is the mondrian.jsp i edited to connect to hsqldb:
-----------------------------------
<%@ page session="true" contentType="text/html; charset=ISO-8859-1" %>
<%@ taglib uri="http://www.tonbeller.com/jpivot" prefix="jp" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jstl/core" %>
<%-- uses a dataSource --%>
<%-- jp:mondrianQuery id="query01" dataSource="jdbc/MondrianFoodmart" catalogUri="/WEB-INF/demo/FoodMart.xml" --%>
<%-- uses mysql --%>
<%-- jp:mondrianQuery id="query01" jdbcDriver="com.mysql.jdbc.Driver" jdbcUrl="jdbc:mysql://localhost/foodmart" catalogUri="/WEB-INF/queries/FoodMart.xml"--%>
<%-- uses a role defined in FoodMart.xml --%>
<%-- jp:mondrianQuery role="California manager" id="query01" jdbcDriver="sun.jdbc.odbc.JdbcOdbcDriver" jdbcUrl="jdbc:odbc:MondrianFoodMart" catalogUri="/WEB-INF/queries/FoodMart.xml" --%>
<jp:mondrianQuery id="query01" jdbcDriver="org.hsqldb.jdbcDriver" jdbcUser="sa" jdbcPassword="" jdbcUrl="jdbc:hsqldb:hsql://localhost/" catalogUri="/WEB-INF/queries/FoodMart.xml">
select
{[Measures].[Unit Sales], [Measures].[Store Cost], [Measures].[Store Sales]} on columns,
{([Promotion Media].[All Media], [Product].[All Products])} ON rows
from Sales
where ([Time].[1997])
</jp:mondrianQuery>
<c:set var="title01" scope="session">Test Query uses Mondrian OLAP</c:set>
------------------------------------
i think i make a mistake for jdbcUrl in each mondrian.jsp, please correct this or maybe
someone can make a simple tutorial for me to connect to each database engine
thank you very much,
Hadi
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.pentaho.org/pipermail/mondrian/attachments/20080720/f5519456/attachment.html
-------------- next part --------------
A non-text attachment was scrubbed...
Name: hsqldb server and manager.JPG
Type: image/jpeg
Size: 166964 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20080720/f5519456/attachment.jpe
-------------- next part --------------
A non-text attachment was scrubbed...
Name: mondrian null.JPG
Type: image/jpeg
Size: 27996 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20080720/f5519456/attachment-0001.jpe
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sql server null.JPG
Type: image/jpeg
Size: 89426 bytes
Desc: not available
Url : http://lists.pentaho.org/pipermail/mondrian/attachments/20080720/f5519456/attachment-0002.jpe
More information about the Mondrian
mailing list