Java Code Examples for java.sql.Connection#setCatalog()

@Test
public void testDefaultCatalog() throws Exception {
    DataSourceProxy d1 = this.createDefaultDataSource();
    d1.setMaxActive(1);
    d1.setJdbcInterceptors(ConnectionState.class.getName());
    d1.setDefaultCatalog("information_schema");
    d1.setMinIdle(1);
    Connection c1 = d1.getConnection();
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"information_schema");
    c1.close();
    c1 = d1.getConnection();
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"information_schema");
    c1.setCatalog("mysql");
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"mysql");
    c1.close();
    c1 = d1.getConnection();
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"information_schema");
}
 

protected void apply(Connection conn, ConnectionProperties connProps)
    throws SQLException {
  if (connProps.isAutoCommit() != null) {
    conn.setAutoCommit(connProps.isAutoCommit());
  }
  if (connProps.isReadOnly() != null) {
    conn.setReadOnly(connProps.isReadOnly());
  }
  if (connProps.getTransactionIsolation() != null) {
    conn.setTransactionIsolation(connProps.getTransactionIsolation());
  }
  if (connProps.getCatalog() != null) {
    conn.setCatalog(connProps.getCatalog());
  }
  if (connProps.getSchema() != null) {
    conn.setSchema(connProps.getSchema());
  }
}
 

/**
 * Build properties for the Driver, including the given username and password (if any),
 * and obtain a corresponding Connection.
 * @param username the name of the user
 * @param password the password to use
 * @return the obtained Connection
 * @throws SQLException in case of failure
 * @see java.sql.Driver#connect(String, java.util.Properties)
 */
protected Connection getConnectionFromDriver(@Nullable String username, @Nullable String password) throws SQLException {
	Properties mergedProps = new Properties();
	Properties connProps = getConnectionProperties();
	if (connProps != null) {
		mergedProps.putAll(connProps);
	}
	if (username != null) {
		mergedProps.setProperty("user", username);
	}
	if (password != null) {
		mergedProps.setProperty("password", password);
	}

	Connection con = getConnectionFromDriver(mergedProps);
	if (this.catalog != null) {
		con.setCatalog(this.catalog);
	}
	if (this.schema != null) {
		con.setSchema(this.schema);
	}
	return con;
}
 

@Test
public void testPStmtCatalog() throws Exception {
    final Connection conn = getConnection();
    conn.setCatalog("catalog1");
    final DelegatingPreparedStatement stmt1 = (DelegatingPreparedStatement) conn.prepareStatement("select 'a' from dual");
    final TesterPreparedStatement inner1 = (TesterPreparedStatement) stmt1.getInnermostDelegate();
    assertEquals("catalog1", inner1.getCatalog());
    stmt1.close();

    conn.setCatalog("catalog2");
    final DelegatingPreparedStatement stmt2 = (DelegatingPreparedStatement) conn.prepareStatement("select 'a' from dual");
    final TesterPreparedStatement inner2 = (TesterPreparedStatement) stmt2.getInnermostDelegate();
    assertEquals("catalog2", inner2.getCatalog());
    stmt2.close();

    conn.setCatalog("catalog1");
    final DelegatingPreparedStatement stmt3 = (DelegatingPreparedStatement) conn.prepareStatement("select 'a' from dual");
    final TesterPreparedStatement inner3 = (TesterPreparedStatement) stmt3.getInnermostDelegate();
    assertEquals("catalog1", inner3.getCatalog());
    stmt3.close();

    assertNotSame(inner1, inner2);
    assertSame(inner1, inner3);
}
 

/**
 * Determine whether there are currently thread-bound credentials,
 * using them if available, falling back to the statically specified
 * username and password (i.e. values of the bean properties) otherwise.
 * <p>Delegates to {@link #doGetConnection(String, String)} with the
 * determined credentials as parameters.
 * @see #doGetConnection
 */
@Override
public Connection getConnection() throws SQLException {
	JdbcUserCredentials threadCredentials = this.threadBoundCredentials.get();
	Connection con = (threadCredentials != null ?
			doGetConnection(threadCredentials.username, threadCredentials.password) :
			doGetConnection(this.username, this.password));

	if (this.catalog != null) {
		con.setCatalog(this.catalog);
	}
	if (this.schema != null) {
		con.setSchema(this.schema);
	}
	return con;
}
 

/**
 * Build properties for the Driver, including the given username and password (if any),
 * and obtain a corresponding Connection.
 * @param username the name of the user
 * @param password the password to use
 * @return the obtained Connection
 * @throws SQLException in case of failure
 * @see java.sql.Driver#connect(String, java.util.Properties)
 */
protected Connection getConnectionFromDriver(@Nullable String username, @Nullable String password) throws SQLException {
	Properties mergedProps = new Properties();
	Properties connProps = getConnectionProperties();
	if (connProps != null) {
		mergedProps.putAll(connProps);
	}
	if (username != null) {
		mergedProps.setProperty("user", username);
	}
	if (password != null) {
		mergedProps.setProperty("password", password);
	}

	Connection con = getConnectionFromDriver(mergedProps);
	if (this.catalog != null) {
		con.setCatalog(this.catalog);
	}
	if (this.schema != null) {
		con.setSchema(this.schema);
	}
	return con;
}
 

private void loadSchema(String schemaSql) throws SQLException, IOException {
	Connection conn = DriverManager.getConnection(connectionString, user, pwd);
	
	// Delete all tables
	conn.createStatement().execute("DROP DATABASE IF EXISTS " + loadDatabase);
	conn.createStatement().execute("CREATE DATABASE " + loadDatabase);

	conn.setCatalog(loadDatabase);
	
	// Create schema
	ScriptRunner runner = new ScriptRunner(conn, false, true);
	runner.runScript(new StringReader(schemaSql));
	conn.close();

	extractor = new SchemaExtractor(connectionString, loadDatabase, user, pwd);
}
 

@Test
public void testDefaultCatalog() throws Exception {
    DataSourceProxy d1 = this.createDefaultDataSource();
    d1.setMaxActive(1);
    d1.setJdbcInterceptors(ConnectionState.class.getName());
    d1.setDefaultCatalog("information_schema");
    d1.setMinIdle(1);
    Connection c1 = d1.getConnection();
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"information_schema");
    c1.close();
    c1 = d1.getConnection();
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"information_schema");
    c1.setCatalog("mysql");
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"mysql");
    c1.close();
    c1 = d1.getConnection();
    Assert.assertEquals("Catalog should be information_schema",c1.getCatalog(),"information_schema");
}
 

public Connection getConnection(String client,boolean readonly) throws SQLException
	{
		DataSource ds = dataSources.get(client);
		Connection c = null;
		if (ds != null)
		{
			c = ds.getConnection();
			if (readonly)
				c.setReadOnly(true);
			else
				c.setReadOnly(false);
			c.setCatalog(clientToCatalog.get(client));
		}
		else {
//			logger.error("Can't get connection for client "+client);
            final String message = "Can't get connection for client " + client;
            logger.error(message, new Exception(message));
        }
		return c;
	}
 

/**
 * Build properties for the Driver, including the given username and password (if any),
 * and obtain a corresponding Connection.
 * @param username the name of the user
 * @param password the password to use
 * @return the obtained Connection
 * @throws SQLException in case of failure
 * @see java.sql.Driver#connect(String, java.util.Properties)
 */
@UsesJava7
protected Connection getConnectionFromDriver(String username, String password) throws SQLException {
	Properties mergedProps = new Properties();
	Properties connProps = getConnectionProperties();
	if (connProps != null) {
		mergedProps.putAll(connProps);
	}
	if (username != null) {
		mergedProps.setProperty("user", username);
	}
	if (password != null) {
		mergedProps.setProperty("password", password);
	}

	Connection con = getConnectionFromDriver(mergedProps);
	if (this.catalog != null) {
		con.setCatalog(this.catalog);
	}
	if (this.schema != null) {
		con.setSchema(this.schema);
	}
	return con;
}
 

/**
 * Tests fix for Bug#12417 - stored procedure catalog name is case-sensitive
 * on Windows (this is actually a server bug, but we have a workaround in
 * place for it now).
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug12417() throws Exception {
    if (serverSupportsStoredProcedures() && isServerRunningOnWindows()) {

        createProcedure("testBug12417", "()\nBEGIN\nSELECT 1;end\n");

        Connection ucCatalogConn = null;

        try {
            ucCatalogConn = getConnectionWithProps((Properties) null);
            ucCatalogConn.setCatalog(this.conn.getCatalog().toUpperCase());
            ucCatalogConn.prepareCall("{call testBug12417()}");
        } finally {
            if (ucCatalogConn != null) {
                ucCatalogConn.close();
            }
        }
    }
}
 

private void trySetConnectionProperties(Query query, Connection connection)
        throws SQLException
{
    // Required for jdbc drivers that do not implement all/some of these functions (eg. impala jdbc driver)
    // For these drivers, set the database default values in the query database
    try {
        connection.setClientInfo("ApplicationName", "verifier-test:" + queryPair.getName());
        connection.setCatalog(query.getCatalog());
        connection.setSchema(query.getSchema());
    }
    catch (SQLClientInfoException ignored) {
        // Do nothing
    }
}
 

private void resetDB() throws SQLException, IOException {
	Connection conn = DriverManager.getConnection(connectionString, user, pwd);

	// Delete all tables
	conn.createStatement().execute("DROP DATABASE IF EXISTS " + this.database);
	conn.createStatement().execute("CREATE DATABASE " + this.database);

	conn.setCatalog(this.database);

	// Create schema
	ScriptRunner runner = new ScriptRunner(conn, false, true);
	runner.runScript(new StringReader(schemaSql));
	conn.close();
}
 

void classify() throws Exception {
	String scenarioPath = Paths.get(System.getProperty("user.dir")).toString() + "/scenarios/scenariosRQ1evaluation/";
	
	// Connect to the evaluation database
	Connection conn = DriverManager.getConnection(connectionString, user, pwd);
	conn.setCatalog(database);
	
	// Collect the rules to analyze
	List<String> rulesList = collectRules(conn, scenarioPath);
	
	classifyRules(rulesList, scenarioPath);
}
 

public void executeWithJdbc(
    @NonNull final Handle handle, final String database, @NonNull final String sql)
    throws SQLException {
  // Use JDBC API to excute raw SQL without any return value and no binding in SQL statement, so
  // we don't need to escape colon(:)
  // SQL statement with colon(:) inside needs to be escaped if using JDBI Handle.execute(sql)
  Connection connection = handle.getConnection();
  if (database != null) {
    connection.setCatalog(database);
  }
  Statement statement = connection.createStatement();
  statement.execute(sql);
}
 

public Connection createConnection() throws SQLException
{
    Connection connection = null;
    try
    {
        connection = new CatalogCachingConnection(createDriverConnection());
        if (databaseType != null)
        {
            databaseType.configureConnection(connection);
        }
        if (this.schemaName != null)
        {
            if (databaseType != null)
            {
                databaseType.setSchemaOnConnection(connection, this.schemaName);
            }
            else
            {
                connection.setCatalog(this.schemaName);
            }
        }

    }
    catch (SQLException e)
    {
        SQLException moreInfoException = new SQLException("error creating connection to database: " + this.schemaName+
        " for connection string: "+this.connectString);
        moreInfoException.initCause(e);
        throw moreInfoException;
    }
    return connection;
}
 

private void trySetConnectionProperties(Query query, Connection connection)
        throws SQLException
{
    // Required for jdbc drivers that do not implement all/some of these functions (eg. impala jdbc driver)
    // For these drivers, set the database default values in the query database
    try {
        connection.setClientInfo("ApplicationName", "verifier-rewrite");
        connection.setCatalog(catalogOverride.orElse(query.getCatalog()));
        connection.setSchema(schemaOverride.orElse(query.getSchema()));
    }
    catch (SQLClientInfoException ignored) {
        // Do nothing
    }
}
 

/**
 * Tests fix for BUG#61150 - First call to SP
 * fails with "No Database Selected"
 * The workaround introduced in DatabaseMetaData.getCallStmtParameterTypes
 * to fix the bug in server where SHOW CREATE PROCEDURE was not respecting
 * lower-case table names is misbehaving when connection is not attached to
 * database and on non-casesensitive OS.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug61150() throws Exception {
    NonRegisteringDriver driver = new NonRegisteringDriver();
    Properties oldProps = driver.parseURL(BaseTestCase.dbUrl, null);

    String host = driver.host(oldProps);
    int port = driver.port(oldProps);
    StringBuilder newUrlToTestNoDB = new StringBuilder("jdbc:mysql://");
    if (host != null) {
        newUrlToTestNoDB.append(host);
    }
    newUrlToTestNoDB.append(":").append(port).append("/");

    Statement savedSt = this.stmt;

    Properties props = getHostFreePropertiesFromTestsuiteUrl();
    props.remove(NonRegisteringDriver.DBNAME_PROPERTY_KEY);
    Connection conn1 = DriverManager.getConnection(newUrlToTestNoDB.toString(), props);

    this.stmt = conn1.createStatement();
    createDatabase("TST1");
    createProcedure("TST1.PROC", "(x int, out y int)\nbegin\ndeclare z int;\nset z = x+1, y = z;\nend\n");

    CallableStatement cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("TST1");
    cStmt = null;
    cStmt = conn1.prepareCall("{call TST1.PROC(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    conn1.setCatalog("mysql");
    cStmt = null;
    cStmt = conn1.prepareCall("{call `TST1`.`PROC`(?, ?)}");
    cStmt.setInt(1, 5);
    cStmt.registerOutParameter(2, Types.INTEGER);

    cStmt.execute();
    assertEquals(6, cStmt.getInt(2));
    cStmt.clearParameters();
    cStmt.close();

    this.stmt = savedSt;
}
 

@Test
public void testPooledConnection() throws SQLException
{
  Map<String, String> properties = getConnectionParameters();

  SnowflakeConnectionPoolDataSource poolDataSource =
      new SnowflakeConnectionPoolDataSource();

  poolDataSource.setUrl(properties.get("uri"));
  poolDataSource.setPortNumber(Integer.parseInt(properties.get("port")));
  poolDataSource.setSsl("on".equals(properties.get("ssl")));
  poolDataSource.setAccount(properties.get("account"));
  poolDataSource.setUser(properties.get("user"));
  poolDataSource.setPassword(properties.get("password"));

  PooledConnection pooledConnection = poolDataSource.getPooledConnection();
  TestingConnectionListener listener = new TestingConnectionListener();
  pooledConnection.addConnectionEventListener(listener);

  Connection connection = pooledConnection.getConnection();
  connection.createStatement().execute("select 1");

  try
  {
    // should fire connection error events
    connection.setCatalog("unexisted_database");
    fail();
  }
  catch (SQLException e)
  {
    assertThat(e.getErrorCode(), is(2043));
  }

  // should not close underlying physical connection
  // and fire connection closed events
  connection.close();

  List<ConnectionEvent> connectionClosedEvents =
      listener.getConnectionClosedEvents();
  List<ConnectionEvent> connectionErrorEvents =
      listener.getConnectionErrorEvents();

  // assert connection close event
  assertThat(connectionClosedEvents.size(), is(1));
  ConnectionEvent closedEvent = connectionClosedEvents.get(0);
  assertThat(closedEvent.getSQLException(), is(nullValue()));
  assertThat(closedEvent.getSource(), instanceOf(SnowflakePooledConnection.class));
  assertThat((PooledConnection) closedEvent.getSource(),
             sameInstance(pooledConnection));

  // assert connection error event
  assertThat(connectionErrorEvents.size(), is(1));
  ConnectionEvent errorEvent = connectionErrorEvents.get(0);

  assertThat(errorEvent.getSource(),
             instanceOf(SnowflakePooledConnection.class));
  assertThat((PooledConnection) errorEvent.getSource(),
             sameInstance(pooledConnection));
  // 2043 is the error code for object not existed
  assertThat(errorEvent.getSQLException().getErrorCode(), is(2043));

  // assert physical connection is not closed
  Connection physicalConnection =
      ((SnowflakePooledConnection) pooledConnection).getPhysicalConnection();

  assertThat(physicalConnection.isClosed(), is(false));

  pooledConnection.removeConnectionEventListener(listener);

  // will close physical connection
  pooledConnection.close();
  assertThat(physicalConnection.isClosed(), is(true));
}
 

/**
 * Collect for each query, for each path if they can succeed, and if so how much time they need on average.
 * This is stored in table `evaluationRQ5`
 */
private void init() {
	// Init query list
	queryRuleBudgets = new LinkedList<List<Integer>>();
	output.println("Budget, Query, Rules, Coverage");
	
	// Get the data
	try {
		// Connect to the evaluation database
		Connection conn = DriverManager.getConnection(connectionString, user, pwd);
		conn.setCatalog(database);
		
		ResultSet result = conn.createStatement().executeQuery("SELECT System, QueryNo, Solvable, Runtime FROM evaluationRQ5");
		String currentSystem = "";
		int currentQueryNo = -1;
		List<Integer> currentRuleList = null;
		while (result.next()) {
			String system = result.getString(1);
			int queryNo = result.getInt(2);
			int solvable = result.getInt(3);
			int runtime = result.getInt(4);
			
			if (!system.equals(currentSystem) || queryNo != currentQueryNo) {
				currentSystem = system;
				currentQueryNo = queryNo;
				
				// Set new list for coverage rules
				currentRuleList = new LinkedList<Integer>();
				queryRuleBudgets.add(currentRuleList);
			}
			
			// Add this coverage rule's time budget
			if (solvable == 0) {
				currentRuleList.add(UNSOLVABLE_BUDGET);
			} else {
				currentRuleList.add(runtime);
			}
		}
	} catch (SQLException e) {
		e.printStackTrace();
	}
}