Hitachi Vantara Pentaho Community Forums
Results 1 to 7 of 7

Thread: Mondrian with Sybase error

  1. #1
    Join Date
    Mar 2017
    Posts
    8

    Default Mondrian with Sybase error

    Hi,

    I am getting a similar error as in the following post http://jira.pentaho.com/browse/MONDRIAN-1510 on Mondrian+Sybase, which is caused by a missing alias at the generated SQL query.

    In the link I read that this is fixed, but I have tried with the following mondrian jars and I am still getting the error:
    3.6.6
    4.3.0.1
    4.6.0.0

    So where can I get the fixed version ?

    Thank you!

    Edit: I think it is the same issue:
    My schema:


    <?xml version="1.0"?>
    <Schema name="GenericDynaListSchema">
    <Cube name="GenericDynaListCube">
    <View alias="GenericDynaListCube">
    <SQL dialect="generic">
    <=!=[=C=D=A=T=A=[SELECT * FROM (select * from job_consumption_data where fiscal_year >= 2010) wrappedQry]=]=>
    </SQL>
    </View>
    <Dimension name="part_number">
    <Hierarchy hasAll="true">
    <Level name="part_number" visible="true" column="part_number" type="String" uniqueMembers="false" />
    </Hierarchy>
    </Dimension><Measure name="qty_produced" column="qty_produced" aggregator="sum" formatString="Standard"/></Cube>
    </Schema>

    It is a very simple table with fields: id, part_number, qty_produced. Nothing more.

    Query if putting "qty_produced" in the measures, and "part_number" in the rows:

    select {[Measures].[qty_produced]}
    ON COLUMNS,
    {Hierarchize(
    {[part_number].[part_number].Members}
    )} ON ROWS
    from [GenericDynaListCube]

    SQL generated (and giving the error):
    Mondrian Error:Internal error: Reading row count from query [select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (select * from job_consumption_data where fiscal_year >= 2010) wrappedQry) as "GenericDynaListCube"]; sql=[select count(*) from (select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (select * from job_consumption_data where fiscal_year >= 2010) wrappedQry) as "GenericDynaListCube")]

    Error: SybSQLException: SQL Anywhere Error -131: Syntax error near '(end of line)' on line 1

    If you change
    select count(*) from (select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (select * from job_consumption_data where fiscal_year >= 2010) wrappedQry) as "GenericDynaListCube")
    to
    select count(*) from (select distinct "GenericDynaListCube"."part_number" as "c0" from (SELECT * FROM (select * from job_consumption_data where fiscal_year >= 2010) wrappedQry) as "GenericDynaListCube") AS DUMMY

    then the query works. I guess this is the same issue in the link?
    Last edited by edulid1; 03-19-2017 at 06:08 AM.

  2. #2
    Join Date
    Aug 2006
    Posts
    287

    Default

    This was fixed in Pentaho 5.0 or Mondrian 3.7 I believe

  3. #3
    Join Date
    Mar 2017
    Posts
    8

    Default

    Hi,

    ok, there seems to be a bug in my code somewhere else, but I am not sure where to look.

    Some Dialects seem to be not correctly registered:
    If I take a look at mondrian.spi.Dialect I see the following:

    mondrian.spi.impl.AccessDialect
    mondrian.spi.impl.Db2Dialect
    mondrian.spi.impl.Db2OldAs400Dialect
    mondrian.spi.impl.DerbyDialect
    mondrian.spi.impl.FirebirdDialect
    mondrian.spi.impl.GreenplumDialect
    mondrian.spi.impl.HiveDialect
    mondrian.spi.impl.HsqldbDialect
    mondrian.spi.impl.ImpalaDialect
    mondrian.spi.impl.InfobrightDialect
    mondrian.spi.impl.InformixDialect
    mondrian.spi.impl.InterbaseDialect
    mondrian.spi.impl.LucidDbDialect
    mondrian.spi.impl.MicrosoftSqlServerDialect
    mondrian.spi.impl.MySqlDialect
    mondrian.spi.impl.MonetDbDialect
    mondrian.spi.impl.NeoviewDialect
    mondrian.spi.impl.NetezzaDialect
    mondrian.spi.impl.OracleDialect
    mondrian.spi.impl.PostgreSqlDialect
    mondrian.spi.impl.RedshiftDialect
    mondrian.spi.impl.SqlStreamDialect
    mondrian.spi.impl.SybaseDialect
    mondrian.spi.impl.TeradataDialect
    mondrian.spi.impl.VectorwiseDialect
    mondrian.spi.impl.VerticaDialect
    mondrian.spi.impl.IngresDialect

    Debugging the code, I see:
    DialectManager.chainDialectFactory.dialectFactoryList:

    [mondrian.spi.impl.JdbcDialectFactory@261cc6e,
    mondrian.spi.impl.JdbcDialectFactory@d018637,
    mondrian.spi.impl.JdbcDialectFactory@37e43e85,
    mondrian.spi.impl.JdbcDialectFactory@443488c8,
    mondrian.spi.impl.JdbcDialectFactory@483a2e9c,
    mondrian.spi.impl.GreenplumDialect$1@490b3992,
    mondrian.spi.impl.HiveDialect$1@5f8d2620,
    mondrian.spi.impl.JdbcDialectFactory@3f9f97d1,
    mondrian.spi.impl.ImpalaDialect$1@26c81070,
    mondrian.spi.impl.InfobrightDialect$1@369f7a92,
    mondrian.spi.impl.JdbcDialectFactory@15f647,
    mondrian.spi.impl.JdbcDialectFactory@43f7d88b,
    mondrian.spi.impl.JdbcDialectFactory@25da0b15,
    mondrian.spi.impl.JdbcDialectFactory@68dcaed6,
    mondrian.spi.impl.MySqlDialect$1@4754ca44,
    mondrian.spi.impl.JdbcDialectFactory@3a9a71a5,
    mondrian.spi.impl.JdbcDialectFactory@4fed4740,
    mondrian.spi.impl.NetezzaDialect$1@42cafa75,
    mondrian.spi.impl.JdbcDialectFactory@232d0e82,
    mondrian.spi.impl.PostgreSqlDialect$1@4a53441b,
    mondrian.spi.impl.RedshiftDialect$1@6fa1dd56,
    mondrian.spi.impl.JdbcDialectFactory@7a2d9b85,
    mondrian.spi.impl.JdbcDialectFactory@313fc35c,
    mondrian.spi.impl.JdbcDialectFactory@619c3afc,
    mondrian.spi.impl.JdbcDialectFactory@b610f4,
    mondrian.spi.impl.JdbcDialectFactory@16d152d4,
    mondrian.spi.impl.JdbcDialectFactory@59bb4671]

    So some dialects are registered as JdbcDialectFactory (for example the Sybase DialectFactory), while others, like PostgreSQL, are registered correctly.
    Any idea how can I correct this ?

    Thank you

  4. #4
    Join Date
    Mar 2017
    Posts
    8

    Default

    Hi,

    any idea? where are the dialects registered to dialectFactoryList ? What may be happening here?

    Thanks

  5. #5
    Join Date
    Mar 2017
    Posts
    8

    Default

    Ok, I downloaded mondrians sources and debugged, and I think this is a mondrian bug with the jconn4.jar driver

    In the mondrian.spi.impl.JdbcDialectImpl.getProduct(String productName, String productVersion) I see:

    ...
    else if (upperProductName.indexOf("SYBASE") >= 0
    || upperProductName.indexOf("ADAPTIVE SERVER") >= 0)
    {
    // Sysbase Adaptive Server Enterprise 15.5 via jConnect 6.05 returns
    // "Adaptive Server Enterprise" as a product name.
    return DatabaseProduct.SYBASE;
    ...

    But I have productName = "SQL Anywhere". I use jconn4.jar

    Could you please confirm this is a bug?

    Thanks
    Last edited by edulid1; 04-03-2017 at 05:45 AM.

  6. #6
    Join Date
    Mar 2017
    Posts
    8

    Default

    This patch works for me: please confirm this is a bug.

    mondrian.spi.impl.JdbcDialectImpl.getProduct(String productName, String productVersion):
    -> Add || upperProductName.indexOf("SQL ANYWHERE") >= 0

    ---------
    else if (upperProductName.indexOf("SYBASE") >= 0
    || upperProductName.indexOf("ADAPTIVE SERVER") >= 0 || upperProductName.indexOf("SQL ANYWHERE") >= 0)
    {
    // Sysbase Adaptive Server Enterprise 15.5 via jConnect 6.05 returns
    // "Adaptive Server Enterprise" as a product name.
    return DatabaseProduct.SYBASE;
    }
    ---------

    Thanks
    Last edited by edulid1; 04-03-2017 at 07:16 AM.

  7. #7
    Join Date
    Mar 2017
    Posts
    8

    Default

    Any information on this ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.