Datasource connection in Play for Scala

[Pablo S]

Hi Xavier, I'm trying to implement Pivot4J in Play for Scala, with AngularJS as the user interface.

I'm looking at your example to connect to the datasource using a pooled connection, however I see that you use the field dataSource both in the left and right sides of the statement. In Scala it would be:

      val config = new GenericObjectPool.Config();

      config.maxActive = 3;

      config.maxIdle = 3;

      val dataSource = new PooledOlapDataSource(dataSource, config);

and I get the message:

      "recursive value dataSource needs type"

Is this intentional? if yes, what's the syntax in Scala?

[Xavier Cho]

Hi Pablo,

It seems there's a typo in your code, as the variable 'dataSource' is
reused in its own definition.

PooledOlapDataSource is just a wrapper around a real data source,
so you might want to define it first.

Hope this helps!

Xavier

2016년 7월 4일 월요일 오전 9시 51분 21초 KST에 Pablo S 님이 쓴 글:

[Pablo S]

Thanks, that worked.

I'm getting now an exception, though. This is how I connect:

      val dataSource = new SimpleOlapDataSource();

      dataSource.setConnectionString("jdbc:mondrian:Jdbc=jdbc:odbc:MondrianFoodMart;Catalog=FoodMart.xml;");

      val config = new GenericObjectPool.Config();

      config.maxActive = 3;

      config.maxIdle = 3;

      

      val dataSource2 = new PooledOlapDataSource(dataSource, config);

      val initialMdx = "SELECT ....."

        val model = new PivotModelImpl(dataSource2);

        model.setMdx(initialMdx);

        model.initialize();    // <-- crashes here

play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution exception[[PivotException: java.sql.SQLException: No suitable driver found for jdbc:mondrian:Jdbc=jdbc:odbc:MondrianFoodMart;Catalog=FoodMart.xml;]]

        at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:280)

        at play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:206)

        at play.api.GlobalSettings$class.onError(GlobalSettings.scala:160)

        at play.api.DefaultGlobal$.onError(GlobalSettings.scala:188)

        at play.api.http.GlobalSettingsHttpErrorHandler.onServerError(HttpErrorHandler.scala:98)

        at play.core.server.netty.PlayRequestHandler$$anonfun$2$$anonfun$apply$1.applyOrElse(PlayRequestHandler.scala:1

this is my pivot4j-config.xml:

              <datasource>

<name>FoodMart Mondrian</name>

<description>FoodMart sample data source.</description>

<connection-info>

<url>jdbc:mondrian:</url>

<driverClass>mondrian.olap4j.MondrianOlap4jDriver</driverClass>

                <properties>

                    <property name="Catalog">FoodMart.xml</property>

                    <property name="Jdbc">jdbc:mysql://localhost:3306/foodmart</property>

                    <property name="JdbcDrivers">com.mysql.jdbc.Driver</property>

                    <property name="JdbcUser">root</property>

                    <property name="JdbcPassword">xxxx</property>

                </properties>

</connection-info>

</datasource>

[Xavier Cho]

It seems that the server fails to find Mondrian library in its classpath, or
It could be the case that Mondrian is loaded but it fails to find a suitable
driver to handle ODBC connection.

In either case, I think you should verify the classpath settings so that it
contains both of the libraries.


2016년 7월 4일 월요일 오전 10시 19분 21초 KST에 Pablo S 님이 쓴 글:

> Thanks, that worked.
>
> I'm getting now an exception, though. This is how I connect:
>
> val dataSource = new SimpleOlapDataSource();
>
> dataSource.setConnectionString("jdbc:mondrian:Jdbc=jdbc:odbc:MondrianFoodMar
> t;Catalog=FoodMart.xml;"); val config = new GenericObjectPool.Config();
> config.maxActive = 3;
> config.maxIdle = 3;
>
> val dataSource2 = new PooledOlapDataSource(dataSource, config);
>
> val initialMdx = "SELECT ....."
>
> val model = new PivotModelImpl(dataSource2);
> model.setMdx(initialMdx);
> model.initialize(); // <-- crashes here
>
> play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution
> exception[[PivotException: java.sql.SQLException: No suitable driver found
> for jdbc:mondrian:Jdbc=jdbc:odbc:MondrianFoodMart;Catalog=FoodMart.xml;]]
> at
> play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErr
> orHandler.scala:280) at
> play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:2

> 06) at play.api.GlobalSettings$class.onError(GlobalSettings.scala:160) at

[Pablo S]

The mondrian jar and its dependencies were missing. I incorrectly assumed that olap4j would add Mondrian.

I'm getting closer, but now I have this error and I'm stuck. Both Mondrian and the MySql jar are in the classpath. Any ideas?

play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution exception[[PivotException: java.sql.SQLException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:odbc:MondrianFoodMart]]

        at play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErrorHandler.scala:280)

        at play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:206)

        at play.api.GlobalSettings$class.onError(GlobalSettings.scala:160)

        at play.api.DefaultGlobal$.onError(GlobalSettings.scala:188)

        at play.api.http.GlobalSettingsHttpErrorHandler.onServerError(HttpErrorHandler.scala:98)

        at play.core.server.netty.PlayRequestHandler$$anonfun$2$$anonfun$apply$1.applyOrElse(PlayRequestHandler.scala:100)

        at play.core.server.netty.PlayRequestHandler$$anonfun$2$$anonfun$apply$1.applyOrElse(PlayRequestHandler.scala:99)

        at scala.concurrent.Future$$anonfun$recoverWith$1.apply(Future.scala:344)

        at scala.concurrent.Future$$anonfun$recoverWith$1.apply(Future.scala:343)

        at scala.concurrent.impl.CallbackRunnable.run(Promise.scala:32)

Caused by: org.pivot4j.PivotException: java.sql.SQLException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:odbc:MondrianFoodMart

        at org.pivot4j.impl.PivotModelImpl.initialize(PivotModelImpl.java:203)

        at test.TestPivot4J$$anonfun$test$1.apply(TestPivot4J.scala:49)

        at test.TestPivot4J$$anonfun$test$1.apply(TestPivot4J.scala:18)

        at play.api.mvc.ActionBuilder$$anonfun$apply$13.apply(Action.scala:371)

        at play.api.mvc.ActionBuilder$$anonfun$apply$13.apply(Action.scala:370)

        at play.api.mvc.Action$.invokeBlock(Action.scala:498)

        at play.api.mvc.Action$.invokeBlock(Action.scala:495)

        at play.api.mvc.ActionBuilder$$anon$2.apply(Action.scala:458)

        at play.api.mvc.Action$$anonfun$apply$2$$anonfun$apply$5$$anonfun$apply$6.apply(Action.scala:112)

        at play.api.mvc.Action$$anonfun$apply$2$$anonfun$apply$5$$anonfun$apply$6.apply(Action.scala:112)

Caused by: java.sql.SQLException: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:odbc:MondrianFoodMart

        at org.pivot4j.datasource.PooledOlapDataSource.createConnection(PooledOlapDataSource.java:141)

        at org.pivot4j.datasource.AbstractOlapDataSource.getConnection(AbstractOlapDataSource.java:105)

        at org.pivot4j.datasource.AbstractOlapDataSource.getConnection(AbstractOlapDataSource.java:95)

        at org.pivot4j.impl.PivotModelImpl.createConnection(PivotModelImpl.java:260)

        at org.pivot4j.impl.PivotModelImpl.initialize(PivotModelImpl.java:201)

        at test.TestPivot4J$$anonfun$test$1.apply(TestPivot4J.scala:49)

        at test.TestPivot4J$$anonfun$test$1.apply(TestPivot4J.scala:18)

        at play.api.mvc.ActionBuilder$$anonfun$apply$13.apply(Action.scala:371)

        at play.api.mvc.ActionBuilder$$anonfun$apply$13.apply(Action.scala:370)

        at play.api.mvc.Action$.invokeBlock(Action.scala:498)

Caused by: mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while creating SQL connection: Jdbc=jdbc:odbc:MondrianFoodMart

        at mondrian.resource.MondrianResource$_Def0.ex(MondrianResource.java:987)

        at mondrian.olap.Util.newInternal(Util.java:2410)

        at mondrian.olap.Util.newError(Util.java:2426)

        at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:247)

        at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:188)

        at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:216)

        at mondrian.rolap.RolapSchemaPool.createRolapSchema(RolapSchemaPool.java:294)

        at mondrian.rolap.RolapSchemaPool.getByKey(RolapSchemaPool.java:227)

        at mondrian.rolap.RolapSchemaPool.get(RolapSchemaPool.java:166)

        at mondrian.rolap.RolapSchemaPool.get(RolapSchemaPool.java:72)

Caused by: java.sql.SQLException: No suitable driver found for jdbc:odbc:MondrianFoodMart

        at java.sql.DriverManager.getConnection(Unknown Source)

        at java.sql.DriverManager.getConnection(Unknown Source)

        at org.apache.commons.dbcp.DriverManagerConnectionFactory.createConnection(DriverManagerConnectionFactory.java:51)

        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:290)

        at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1188)

        at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:95)

        at mondrian.rolap.RolapConnection.<init>(RolapConnection.java:227)

        at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:188)

        at mondrian.rolap.RolapSchema.<init>(RolapSchema.java:216)

[Xavier Cho]

The problem seems to be caused by missing JDBC-ODBC bridge, but I would
rather suggest you to dump it all together and use the MySQL JDBC driver
instead, in case there's no other reason to prevent it.

As you might already know, the ODBC-JDBC bridge should only be used in
when there's absolutely no other alternative, as there's no reason to put
another layer between your application and the backend which even has very
rudimentary functionalities.

P.S. Quick Google search revealed that Oracle has removed the JDBC-ODBC
driver in Java 8, as even they don't recommend using it.

If you really have to rely on ODBC, you can download the driver from Oracle,
or purchase a better quality bridge driver from other vendors.

2016년 7월 4일 월요일 오후 1시 51분 4초 KST에 Pablo S 님이 쓴 글:

> The mondrian jar and its dependencies were missing. I incorrectly assumed
> that olap4j would add Mondrian.
>
> I'm getting closer, but now I have this error and I'm stuck. Both Mondrian
> and the MySql jar are in the classpath. Any ideas?
>
>
> play.api.http.HttpErrorHandlerExceptions$$anon$1: Execution
> exception[[PivotException: java.sql.SQLException:
> mondrian.olap.MondrianException: Mondrian Error:Internal error: Error while
> creating SQL connection: Jdbc=jdbc:odbc:MondrianFoodMart]]
> at

> play.api.http.HttpErrorHandlerExceptions$.throwableToUsefulException(HttpErr
> orHandler.scala:280) at
> play.api.http.DefaultHttpErrorHandler.onServerError(HttpErrorHandler.scala:2
> 06) at play.api.GlobalSettings$class.onError(GlobalSettings.scala:160) at
> play.api.DefaultGlobal$.onError(GlobalSettings.scala:188) at
> play.api.http.GlobalSettingsHttpErrorHandler.onServerError(HttpErrorHandler.
> scala:98) at
> play.core.server.netty.PlayRequestHandler$$anonfun$2$$anonfun$apply$1.applyO

> rElse(PlayRequestHandler.scala:100) at
> play.core.server.netty.PlayRequestHandler$$anonfun$2$$anonfun$apply$1.applyO

[Xavier Cho]

Ah... I see that you already have MySQL driver in your classpath. In that case, I assume your intention was to use it 

instead of ODBC, but inadvertently used a wrong connection URL which is for JDBC-ODBC bridge.

You can just replace it with a correct URL for MySQL driver, then I suppose it would fix the problem.

2016년 7월 5일 화요일 오전 1시 51분 21초 UTC+9, Pablo S 님의 말:

[Pablo S]

Many thanks, it worked. As you said, I never intended to use ODBC, I just copied the connection string from the Pivot4J Analytics config file.

This works (JDBC/MySql connection):

      val dataSource = new SimpleOlapDataSource();

      dataSource.setConnectionString("jdbc:mondrian:Jdbc=jdbc:mysql://localhost:3306/foodmart?user=root&password=xxxxx;" + 

          "Catalog=lib/FoodMart.xml;JdbcDrivers=com.mysql.jdbc.Driver");

[Xavier Cho]

Glad to know you have solved your problem.

Please feel free to ask if you have any further question :)

2016년 7월 4일 월요일 오후 9시 43분 26초 KST에 Pablo S 님이 쓴 글: