public void testQueryWithIndex() { final Connection conn = getConnection(); // Query using name final String queryStr1 = "SELECT {[Measures].[Org Salary], [Measures].[Count]} " + "ON COLUMNS, " + "{[Employees].[Sheri Nowmer]} " + "ON ROWS FROM [HR]"; final Query query1 = conn.parseQuery(queryStr1); final Result result1 = conn.execute(query1); // Query using key final String queryStr2 = "SELECT {[Measures].[Org Salary], [Measures].[Count]} " + "ON COLUMNS, " + "{[Employees].&[1]} " + "ON ROWS FROM [HR]"; final Query query2 = conn.parseQuery(queryStr2); final Result result2 = conn.execute(query2); // Results of two previous queries must be the same assertEquals( result2.getCell(new int[] {0, 0}).getValue(), result1.getCell(new int[] {0, 0}).getValue()); assertEquals( result2.getCell(new int[] {1, 0}).getValue(), result1.getCell(new int[] {1, 0}).getValue()); }
/* * (non-Javadoc) * * @see org.pentaho.connection.IPentahoConnection#executeQuery(java.lang.String) */ public IPentahoResultSet executeQuery(final String query) { Query mdxQuery = nativeConnection.parseQuery(query); Result result = nativeConnection.execute(mdxQuery); resultSet = new MDXResultSet(result, nativeConnection, useExtendedColumnNames); return resultSet; }
public void testDataSourceOverrideUserPass() throws SQLException, NamingException { // use the datasource property to connect to the database Util.PropertyList properties = TestContext.instance().getConnectionProperties().clone(); final Dialect dialect = TestContext.instance().getDialect(); if (dialect.getDatabaseProduct() == Dialect.DatabaseProduct.ACCESS) { // Access doesn't accept user/password, so this test is pointless. return; } final String jdbcUser = properties.get(RolapConnectionProperties.JdbcUser.name()); final String jdbcPassword = properties.get(RolapConnectionProperties.JdbcPassword.name()); if (jdbcUser == null || jdbcPassword == null) { // Can only run this test if username and password are explicit. return; } // Define a data source with bogus user and password. properties.put(RolapConnectionProperties.JdbcUser.name(), "bogususer"); properties.put(RolapConnectionProperties.JdbcPassword.name(), "boguspassword"); properties.put(RolapConnectionProperties.PoolNeeded.name(), "false"); final StringBuilder buf = new StringBuilder(); final DataSource dataSource = RolapConnection.createDataSource(null, properties, buf); final String desc = buf.toString(); assertTrue(desc, desc.startsWith("Jdbc=")); assertTrue(desc, desc.indexOf("JdbcUser=bogususer; JdbcPassword=boguspassword") >= 0); final String jndiName = "jndiDataSource"; THREAD_INITIAL_CONTEXT.set( new InitialContext() { public Object lookup(String str) { return str.equals(jndiName) ? dataSource : null; } }); // Create a property list that we will use for the actual mondrian // connection. Replace the original JDBC info with the data source we // just created. final Util.PropertyList properties2 = new Util.PropertyList(); for (Pair<String, String> entry : properties) { properties2.put(entry.getKey(), entry.getValue()); } properties2.remove(RolapConnectionProperties.Jdbc.name()); properties2.put(RolapConnectionProperties.DataSource.name(), jndiName); // With JdbcUser and JdbcPassword credentials in the mondrian connect // string, the data source's "user" and "password" properties are // overridden and the connection succeeds. properties2.put(RolapConnectionProperties.JdbcUser.name(), jdbcUser); properties2.put(RolapConnectionProperties.JdbcPassword.name(), jdbcPassword); mondrian.olap.Connection connection = null; try { connection = DriverManager.getConnection(properties2, null); Query query = connection.parseQuery("select from [Sales]"); final Result result = connection.execute(query); assertNotNull(result); } finally { if (connection != null) { connection.close(); connection = null; } } // If we don't specify JdbcUser and JdbcPassword in the mondrian // connection properties, mondrian uses the data source's // bogus credentials, and the connection fails. properties2.remove(RolapConnectionProperties.JdbcUser.name()); properties2.remove(RolapConnectionProperties.JdbcPassword.name()); for (String poolNeeded : Arrays.asList("false", "true")) { // Important to test with & without pooling. Connection pools // typically do not let you change user, so it's important that // mondrian handles these right. properties2.put(RolapConnectionProperties.PoolNeeded.name(), poolNeeded); try { connection = DriverManager.getConnection(properties2, null); fail("Expected exception"); } catch (MondrianException e) { final String s = TestContext.getStackTrace(e); assertTrue( s, s.indexOf("Error while creating SQL connection: " + "DataSource=jndiDataSource") >= 0); switch (dialect.getDatabaseProduct()) { case DERBY: assertTrue( s, s.indexOf( "Caused by: java.sql.SQLException: " + "Schema 'BOGUSUSER' does not exist") >= 0); break; case ORACLE: assertTrue( s, s.indexOf( "Caused by: java.sql.SQLException: ORA-01017: " + "invalid username/password; logon denied") >= 0); break; case MYSQL: assertTrue( s, s.indexOf( "Caused by: java.sql.SQLException: Access denied " + "for user 'bogususer'") >= 0); break; case POSTGRESQL: assertTrue( s, s.indexOf( "Caused by: org.postgresql.util.PSQLException: " + "FATAL: password authentication failed for " + "user \"bogususer\"") >= 0); break; } } finally { if (connection != null) { connection.close(); connection = null; } } } }
protected Result executeQuery(String mdx, Connection connection) { Query query = connection.parseQuery(mdx); query.setResultStyle(ResultStyle.LIST); return connection.execute(query); }
/** * During MDX query parse and execution, checks that the query results (or does not result) in a * particular SQL statement being generated. * * <p>Parses and executes the MDX query once for each SQL pattern in the current dialect. If there * are multiple patterns, runs the MDX query multiple times, and expects to see each SQL statement * appear. If there are no patterns in this dialect, the test trivially succeeds. * * @param testContext non-default test context if required * @param mdxQuery MDX query * @param patterns Set of patterns * @param negative false to assert if SQL is generated; true to assert if SQL is NOT generated * @param bypassSchemaCache whether to grab a new connection and bypass the schema cache before * parsing the MDX query * @param clearCache whether to clear cache before executing the MDX query */ protected void assertQuerySqlOrNot( TestContext testContext, String mdxQuery, SqlPattern[] patterns, boolean negative, boolean bypassSchemaCache, boolean clearCache) { Connection connection = testContext.getConnection(); mdxQuery = testContext.upgradeQuery(mdxQuery); // Run the test once for each pattern in this dialect. // (We could optimize and run it once, collecting multiple queries, and // comparing all queries at the end.) Dialect dialect = testContext.getDialect(); Dialect.DatabaseProduct d = dialect.getDatabaseProduct(); boolean patternFound = false; for (SqlPattern sqlPattern : patterns) { if (!sqlPattern.hasDatabaseProduct(d)) { // If the dialect is not one in the pattern set, skip the // test. If in the end no pattern is located, print a warning // message if required. continue; } patternFound = true; String sql = sqlPattern.getSql(); String trigger = sqlPattern.getTriggerSql(); sql = dialectize(d, sql); trigger = dialectize(d, trigger); // Create a dummy DataSource which will throw a 'bomb' if it is // asked to execute a particular SQL statement, but will otherwise // behave exactly the same as the current DataSource. RolapUtil.setHook(new TriggerHook(trigger)); Bomb bomb = null; try { if (bypassSchemaCache) { connection = testContext.withSchemaPool(false).getConnection(); } final Query query = connection.parseQuery(mdxQuery); if (clearCache) { clearCache((RolapCube) query.getCube()); } final Result result = connection.execute(query); Util.discard(result); bomb = null; } catch (Bomb e) { bomb = e; } catch (RuntimeException e) { // Walk up the exception tree and see if the root cause // was a SQL bomb. bomb = Util.getMatchingCause(e, Bomb.class); if (bomb == null) { throw e; } } finally { RolapUtil.setHook(null); } if (negative) { if (bomb != null) { fail("forbidden query [" + sql + "] detected"); } } else { if (bomb == null) { fail("expected query [" + sql + "] did not occur"); } assertEquals( replaceQuotes(sql.replaceAll("\r\n", "\n")), replaceQuotes(bomb.sql.replaceAll("\r\n", "\n"))); } } // Print warning message that no pattern was specified for the current // dialect. if (!patternFound) { String warnDialect = MondrianProperties.instance().WarnIfNoPatternForDialect.get(); if (warnDialect.equals(d.toString())) { System.out.println( "[No expected SQL statements found for dialect \"" + dialect.toString() + "\" and test not run]"); } } }