Java Code Examples for java.sql.DatabaseMetaData#getColumns()

The following examples show how to use java.sql.DatabaseMetaData#getColumns() . You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
Source File: PutDatabaseRecord.java    From nifi with Apache License 2.0 6 votes vote down vote up
public static TableSchema from(final Connection conn, final String catalog, final String schema, final String tableName,
                               final boolean translateColumnNames, final boolean includePrimaryKeys) throws SQLException {
    final DatabaseMetaData dmd = conn.getMetaData();

    try (final ResultSet colrs = dmd.getColumns(catalog, schema, tableName, "%")) {
        final List<ColumnDescription> cols = new ArrayList<>();
        while (colrs.next()) {
            final ColumnDescription col = ColumnDescription.from(colrs);
            cols.add(col);
        }

        final Set<String> primaryKeyColumns = new HashSet<>();
        if (includePrimaryKeys) {
            try (final ResultSet pkrs = dmd.getPrimaryKeys(catalog, null, tableName)) {

                while (pkrs.next()) {
                    final String colName = pkrs.getString("COLUMN_NAME");
                    primaryKeyColumns.add(normalizeColumnName(colName, translateColumnNames));
                }
            }
        }

        return new TableSchema(cols, translateColumnNames, primaryKeyColumns, dmd.getIdentifierQuoteString());
    }
}
 
Example 2
Source File: MetaDataRegressionTest.java    From r-course with MIT License 6 votes vote down vote up
private void checkMetadataForBug22613(Connection c) throws Exception {
    String maxValue = "a,bc,def,ghij";
    String maxValue2 = "1,2,3,4,1585,ONE,TWO,Y,N,THREE";

    DatabaseMetaData meta = c.getMetaData();
    this.rs = meta.getColumns(null, this.conn.getCatalog(), "bug22613", "s");
    this.rs.first();

    assertEquals(maxValue.length(), this.rs.getInt("COLUMN_SIZE"));

    this.rs = meta.getColumns(null, this.conn.getCatalog(), "bug22613", "s2");
    this.rs.first();

    assertEquals(maxValue2.length(), this.rs.getInt("COLUMN_SIZE"));

    this.rs = meta.getColumns(null, c.getCatalog(), "bug22613", "t");
    this.rs.first();

    assertEquals(4, this.rs.getInt("COLUMN_SIZE"));
}
 
Example 3
Source File: MetaDataRegressionTest.java    From Komondor with GNU General Public License v3.0 6 votes vote down vote up
/**
 * Fixed BUG#27915 - DatabaseMetaData.getColumns() doesn't contain SCOPE_*
 * or IS_AUTOINCREMENT columns.
 * 
 * @throws Exception
 */
public void testBug27915() throws Exception {
    createTable("testBug27915", "(field1 int not null primary key auto_increment, field2 int)");
    DatabaseMetaData dbmd = this.conn.getMetaData();

    this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testBug27915", "%");
    this.rs.next();

    checkBug27915();

    if (versionMeetsMinimum(5, 0)) {
        this.rs = getConnectionWithProps("useInformationSchema=true").getMetaData().getColumns(this.conn.getCatalog(), null, "testBug27915", "%");
        this.rs.next();

        checkBug27915();
    }
}
 
Example 4
Source File: DbHelper.java    From crud-intellij-plugin with Apache License 2.0 6 votes vote down vote up
private List<Column> getAllColumn(String tableName) {
    Connection conn = getConnection(db);
    try {
        DatabaseMetaData metaData = conn.getMetaData();
        ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
        String primaryKey = null;
        while (primaryKeys.next()) {
            primaryKey = primaryKeys.getString("COLUMN_NAME");
        }
        ResultSet rs = metaData.getColumns(null, null, tableName, null);
        List<Column> ls = new ArrayList<>();
        while (rs.next()) {
            String columnName = rs.getString("COLUMN_NAME");
            Column column = new Column(rs.getString("REMARKS"), columnName, rs.getInt("DATA_TYPE"));
            if (!StringUtils.isNullOrEmpty(primaryKey) && columnName.equals(primaryKey)) {
                column.setId(true);
            }
            ls.add(column);
        }
        return ls;
    } catch (SQLException e) {
        throw new RuntimeException(e.getMessage(), e);
    } finally {
        closeConnection(conn);
    }
}
 
Example 5
Source File: DBTestBase.java    From netbeans with Apache License 2.0 5 votes vote down vote up
protected final boolean columnExists(String tablename, String colname)
        throws Exception {
    tablename = fixIdentifier(tablename);
    colname = fixIdentifier(colname);
    String schemaName = getSchema();
    DatabaseMetaData md = getConnection().getMetaData();
    ResultSet rs = md.getColumns(null, schemaName, tablename, colname);
    
    int numrows = printResults(
            rs, "columnExists(" + tablename + ", " + colname + ")");
    
    rs.close();
    
    return numrows > 0;
}
 
Example 6
Source File: DSGenerator.java    From micro-integrator with Apache License 2.0 5 votes vote down vote up
private ResultSet getColumnNames (DatabaseMetaData metaData, String schema, String dbName,
String tableName, String columnNamePattern) throws SQLException {
     ResultSet columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
     if (columnNames.next()) {
          columnNames = metaData.getColumns(dbName, schema, tableName, columnNamePattern);
     }  else {
          columnNames = metaData.getColumns(null, schema, tableName,columnNamePattern);
     }

     return columnNames;
 }
 
Example 7
Source File: MetaDataRegressionTest.java    From Komondor with GNU General Public License v3.0 5 votes vote down vote up
/**
 * Tests fix for BUG#1673, where DatabaseMetaData.getColumns() is not
 * returning correct column ordinal info for non '%' column name patterns.
 * 
 * @throws Exception
 *             if the test fails for any reason
 */
public void testFixForBug1673() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1673");
        this.stmt.executeUpdate("CREATE TABLE testBug1673 (field_1 INT, field_2 INT)");

        DatabaseMetaData dbmd = this.conn.getMetaData();

        int ordinalPosOfCol2Full = 0;

        this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testBug1673", null);

        while (this.rs.next()) {
            if (this.rs.getString(4).equals("field_2")) {
                ordinalPosOfCol2Full = this.rs.getInt(17);
            }
        }

        int ordinalPosOfCol2Scoped = 0;

        this.rs = dbmd.getColumns(this.conn.getCatalog(), null, "testBug1673", "field_2");

        while (this.rs.next()) {
            if (this.rs.getString(4).equals("field_2")) {
                ordinalPosOfCol2Scoped = this.rs.getInt(17);
            }
        }

        assertTrue("Ordinal position in full column list of '" + ordinalPosOfCol2Full + "' != ordinal position in pattern search, '"
                + ordinalPosOfCol2Scoped + "'.",
                (ordinalPosOfCol2Full != 0) && (ordinalPosOfCol2Scoped != 0) && (ordinalPosOfCol2Scoped == ordinalPosOfCol2Full));
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug1673");
    }
}
 
Example 8
Source File: QueryDatabaseMetaDataIT.java    From phoenix with Apache License 2.0 5 votes vote down vote up
@Test
public void testRemarkColumn() throws SQLException {
    Connection conn = DriverManager.getConnection(getUrl());
    // Retrieve the database metadata
    DatabaseMetaData dbmd = conn.getMetaData();
    ResultSet rs = dbmd.getColumns(null, null, null, null);
    assertTrue(rs.next());

    // Lookup column by name, this should return null but not throw an exception
    String remarks = rs.getString("REMARKS");
    assertNull(remarks);

    // Same as above, but lookup by position
    remarks = rs.getString(12);
    assertNull(remarks);

    // Iterate through metadata columns to find 'COLUMN_NAME' == 'REMARKS'
    boolean foundRemarksColumn = false;
    while (rs.next()) {
        String colName = rs.getString("COLUMN_NAME");
        if (PhoenixDatabaseMetaData.REMARKS.equals(colName)) {
            foundRemarksColumn = true;
            break;
        }
    }
    assertTrue("Could not find REMARKS column", foundRemarksColumn);
}
 
Example 9
Source File: SchemaInferrer.java    From EchoQuery with GNU General Public License v2.0 5 votes vote down vote up
private void populateColumnToTable(String table, DatabaseMetaData md)
    throws SQLException {
  ResultSet columns = md.getColumns(null, null, table, null);
  while (columns.next()) {
    String col = columns.getString("COLUMN_NAME").toLowerCase();
    Set<String> previousSet =
        columnToTable.getOrDefault(col, new HashSet<String>());
    previousSet.add(table);
    columnToTable.put(col, previousSet);
  }
}
 
Example 10
Source File: TableDataManager.java    From flowable-engine with Apache License 2.0 4 votes vote down vote up
public TableMetaData getTableMetaData(String tableName) {
    TableMetaData result = new TableMetaData();
    try {
        result.setTableName(tableName);
        DatabaseMetaData metaData = getDbSqlSession()
                .getSqlSession()
                .getConnection()
                .getMetaData();

        if ("postgres".equals(getDbSqlSession().getDbSqlSessionFactory().getDatabaseType())) {
            tableName = tableName.toLowerCase();
        }

        String catalog = null;
        if (getProcessEngineConfiguration().getDatabaseCatalog() != null && getProcessEngineConfiguration().getDatabaseCatalog().length() > 0) {
            catalog = getProcessEngineConfiguration().getDatabaseCatalog();
        }

        String schema = null;
        if (getProcessEngineConfiguration().getDatabaseSchema() != null && getProcessEngineConfiguration().getDatabaseSchema().length() > 0) {
            schema = getProcessEngineConfiguration().getDatabaseSchema();
        }

        ResultSet resultSet = metaData.getColumns(catalog, schema, tableName, null);
        while (resultSet.next()) {
            boolean wrongSchema = false;
            if (schema != null && schema.length() > 0) {
                for (int i = 0; i < resultSet.getMetaData().getColumnCount(); i++) {
                    String columnName = resultSet.getMetaData().getColumnName(i + 1);
                    if ("TABLE_SCHEM".equalsIgnoreCase(columnName) || "TABLE_SCHEMA".equalsIgnoreCase(columnName)) {
                        if (!schema.equalsIgnoreCase(resultSet.getString(resultSet.getMetaData().getColumnName(i + 1)))) {
                            wrongSchema = true;
                        }
                        break;
                    }
                }
            }

            if (!wrongSchema) {
                String name = resultSet.getString("COLUMN_NAME").toUpperCase();
                String type = resultSet.getString("TYPE_NAME").toUpperCase();
                result.addColumnMetaData(name, type);
            }
        }

    } catch (SQLException e) {
        throw new ActivitiException("Could not retrieve database metadata: " + e.getMessage());
    }

    if (result.getColumnNames().isEmpty()) {
        // According to API, when a table doesn't exist, null should be returned
        result = null;
    }
    return result;
}
 
Example 11
Source File: CassandraConnectionTest.java    From cassandra-jdbc-driver with Apache License 2.0 4 votes vote down vote up
@Test(groups = {"unit", "server"})
public void testGetMetaData() {
    try {
        DatabaseMetaData metaData = conn.getMetaData();
        assertNotNull(metaData);
        assertEquals("KEYSPACE", metaData.getSchemaTerm());

        ResultSet rs = metaData.getTableTypes();
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.TABLE_TYPE_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        assertEquals(CassandraUtils.TABLE_TYPE_DATA[0],
                CassandraUtils.getAllData(rs)[0]);
        rs.close();

        rs = metaData.getSchemas();
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.SCHEMA_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        Logger.debug(CassandraUtils.getAllData(rs));
        rs.close();

        rs = metaData.getTables(null, "system", "peers", null);
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.TABLE_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        Logger.debug(CassandraUtils.getAllData(rs));
        rs.close();

        rs = metaData.getColumns(null, "system", "peers", null);
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.COLUMN_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        Logger.debug(CassandraUtils.getAllData(rs));
        rs.close();

        rs = metaData.getIndexInfo(null, "system", "peers", false, true);
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.INDEX_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        Logger.debug(CassandraUtils.getAllData(rs));
        rs.close();

        rs = metaData.getPrimaryKeys(null, "system", "peers");
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.PK_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        Logger.debug(CassandraUtils.getAllData(rs));
        rs.close();

        rs = metaData.getUDTs(null, "system", "%", null);
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.UDT_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        Logger.debug(CassandraUtils.getAllData(rs));
        rs.close();

        rs = metaData.getColumns(null, "system", "IndexInfo", null);
        assertTrue(rs instanceof DummyCassandraResultSet);
        assertEquals(extractColumnNames(CassandraUtils.COLUMN_COLUMNS),
                CassandraUtils.getColumnNames(rs));
        Logger.debug(CassandraUtils.getAllData(rs));
        rs.close();
    } catch (SQLException e) {
        e.printStackTrace();
        fail("Error occurred during testing: " + e.getMessage());
    }
}
 
Example 12
Source File: SnowflakeDriverIT.java    From snowflake-jdbc with Apache License 2.0 4 votes vote down vote up
@Test
public void testBoolean() throws Throwable
{
  Connection connection = null;
  Statement statement = null;
  ResultSet resultSet = null;

  try
  {
    connection = getConnection();

    statement = connection.createStatement();
    statement.execute("alter SESSION set CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX=true");

    DatabaseMetaData metadata = connection.getMetaData();

    // Create a table with boolean columns
    statement.execute("create or replace table testBooleanT1(c1 boolean)");

    // Insert values into the table
    statement.execute(
        "insert into testBooleanT1 values(true), (false), (null)");

    // Get values from the table
    PreparedStatement preparedStatement = connection.prepareStatement(
        "select c1 from testBooleanT1");

    // I. Test ResultSetMetaData interface
    resultSet = preparedStatement.executeQuery();

    ResultSetMetaData resultSetMetaData = resultSet.getMetaData();

    // Verify the column type is Boolean
    assertEquals(Types.BOOLEAN, resultSetMetaData.getColumnType(1));

    // II. Test DatabaseMetadata interface
    ResultSet columnMetaDataResultSet =
        metadata.getColumns(
            null, // catalog
            null, // schema
            "TESTBOOLEANT1", // table
            null // column
        );

    resultSetMetaData = columnMetaDataResultSet.getMetaData();

    // assert column count
    assertEquals(24, resultSetMetaData.getColumnCount());

    assertTrue(columnMetaDataResultSet.next());
    assertEquals(Types.BOOLEAN, columnMetaDataResultSet.getInt(5));
  }
  finally // cleanup
  {
    // drop the table
    if (statement != null)
    {
      statement.execute("drop table testBooleanT1");
    }
    closeSQLObjects(resultSet, statement, connection);
  }
}
 
Example 13
Source File: testListAgg.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
public testListAgg() {

		try {
			Connection cxn = DriverManager.getConnection("jdbc:gemfirexd://localhost:1530");
			Statement stmt = cxn.createStatement();
			/*			stmt.execute("DROP TABLE cdsdba.XML_DOC_1");
			stmt.execute("DROP PROCEDURE ListAgg");
			stmt.execute("DROP ALIAS  ListAggProcessor");
			
			stmt.execute("CREATE PROCEDURE ListAgg(IN groupBy VARCHAR(256), "
					+"IN ListAggCols VARCHAR(256), IN tableName VARCHAR(128), "
					+"IN whereClause VARCHAR(256), IN delimiter VARCHAR(10)) "
					+"LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA "
					+"DYNAMIC RESULT SETS 1 EXTERNAL NAME 'ListAggProcedure.ListAgg';");
			String aliasString = "CREATE ALIAS ListAggProcessor FOR '"
					+ LISTAGGPROCESSOR.class.getName() + "'"; */
/*			System.out.println(aliasString);
			stmt.execute(aliasString);*/
			
	/*		String tableDDL = "create table cdsdba.XML_DOC_1 (ID int NOT NULL,"
					+ " SECONDID int not null, THIRDID varchar(10) not null) PARTITION BY COLUMN (ID)";

			stmt.execute(tableDDL);*/
			DatabaseMetaData dbmd = cxn.getMetaData();
			ResultSet resultSet = dbmd.getColumns(null, "CDSDBA", "XML_DOC_1", null);
			

			while (resultSet.next()) {
			    String strTableName = resultSet.getString("COLUMN_NAME");
			    System.out.println("TABLE_NAME is " + strTableName);
			}
	/*		stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (2, 1, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (3, 3, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (4, 4, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (5, 5, '3'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (2, 1, '9'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (2, 3, '4'); ");
			stmt.execute("INSERT INTO cdsdba.XML_DOC_1  VALUES (3, 3, '4'); ");
			stmt.execute("select count(*) from cdsdba.XML_DOC_1 ");*/
			
			String queryString = "{CALL CDSDBA.ListAgg(?,?,?,?,?) WITH RESULT PROCESSOR ListAggProcessor }";
			queryString = "{CALL CDSDBA.ListAgg('structure_id_nbr DESC','create_mint_cd','CDSDBA.XML_DOC_1','MSG_PAYLOAD_QTY=5',',') WITH RESULT PROCESSOR ListAggProcessor ;}";
			CallableStatement cs = cxn.prepareCall(queryString);

			String groupBy = "ID";
			String listAgg = "THIRDID";
			String table = "XML_DOC_1";
			String whereClause = "";
			String delimiter = ",";




			cs = cxn.prepareCall(queryString);
/*			cs.setString(1, groupBy);
			cs.setString(2, listAgg);
			cs.setString(3, table);
			cs.setString(4, whereClause);
			cs.setString(5, delimiter);*/


			long startTime = new Date().getTime();
			cs.execute();

			long endTime = new Date().getTime();
			System.out.println("Duration = "+(endTime-startTime));
			ResultSet thisResultSet;
			boolean moreResults = true;
			int cnt = 0;

			do {
				thisResultSet = cs.getResultSet();
				ResultSetMetaData rMeta = thisResultSet.getMetaData();
				int colCnt = rMeta.getColumnCount();
				for (int i = 1; i < colCnt+1; i++) {
					System.out.print(rMeta.getColumnName(i));
					System.out.print("\t");
				}
				System.out.println("");
				
				if (cnt == 0) {
					while (thisResultSet.next()) {
						for (int i = 1; i < colCnt + 1; i++) {
							System.out.print(thisResultSet.getObject(i));
							System.out.print("\t");
						}
						System.out.println("");
					}
					System.out.println("ResultSet 1 ends\n");
					cnt++;
				} 
				moreResults = cs.getMoreResults();
			} while (moreResults);

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
 
Example 14
Source File: MetaDataRegressionTest.java    From r-course with MIT License 4 votes vote down vote up
/**
 * Tests fix for BUG#23304 - DBMD using "show" and DBMD using
 * information_schema do not return results consistent with eachother.
 * 
 * (note this fix only addresses the inconsistencies, not the issue that the
 * driver is treating schemas differently than some users expect.
 * 
 * We will revisit this behavior when there is full support for schemas in
 * MySQL).
 * 
 * @throws Exception
 */
public void testBug23304() throws Exception {
    if (!versionMeetsMinimum(5, 0)) {
        return;
    }

    Connection connShow = null;
    Connection connInfoSchema = null;

    ResultSet rsShow = null;
    ResultSet rsInfoSchema = null;

    try {
        Properties noInfoSchemaProps = new Properties();
        noInfoSchemaProps.setProperty("useInformationSchema", "false");

        Properties infoSchemaProps = new Properties();
        infoSchemaProps.setProperty("useInformationSchema", "true");
        infoSchemaProps.setProperty("dumpQueriesOnException", "true");

        connShow = getConnectionWithProps(noInfoSchemaProps);
        connInfoSchema = getConnectionWithProps(infoSchemaProps);

        DatabaseMetaData dbmdUsingShow = connShow.getMetaData();
        DatabaseMetaData dbmdUsingInfoSchema = connInfoSchema.getMetaData();

        assertNotSame(dbmdUsingShow.getClass(), dbmdUsingInfoSchema.getClass());

        rsShow = dbmdUsingShow.getSchemas();
        rsInfoSchema = dbmdUsingInfoSchema.getSchemas();

        compareResultSets(rsShow, rsInfoSchema);

        /*
         * rsShow = dbmdUsingShow.getTables(connShow.getCatalog(), null,
         * "%", new String[] {"TABLE", "VIEW"}); rsInfoSchema =
         * dbmdUsingInfoSchema.getTables(connInfoSchema.getCatalog(), null,
         * "%", new String[] {"TABLE", "VIEW"});
         * 
         * compareResultSets(rsShow, rsInfoSchema);
         * 
         * rsShow = dbmdUsingShow.getTables(null, null, "%", new String[]
         * {"TABLE", "VIEW"}); rsInfoSchema =
         * dbmdUsingInfoSchema.getTables(null, null, "%", new String[]
         * {"TABLE", "VIEW"});
         * 
         * compareResultSets(rsShow, rsInfoSchema);
         */

        createTable("t_testBug23304",
                "(field1 int primary key not null, field2 tinyint, field3 mediumint, field4 mediumint, field5 bigint, field6 float, field7 double, field8 decimal, field9 char(32), field10 varchar(32), field11 blob, field12 mediumblob, field13 longblob, field14 text, field15 mediumtext, field16 longtext, field17 date, field18 time, field19 datetime, field20 timestamp)");

        rsShow = dbmdUsingShow.getColumns(connShow.getCatalog(), null, "t_testBug23304", "%");
        rsInfoSchema = dbmdUsingInfoSchema.getColumns(connInfoSchema.getCatalog(), null, "t_testBug23304", "%");

        compareResultSets(rsShow, rsInfoSchema);
    } finally {
        if (rsShow != null) {
            rsShow.close();
        }

        if (rsInfoSchema != null) {
            rsInfoSchema.close();
        }
    }
}
 
Example 15
Source File: MetaDataRegressionTest.java    From r-course with MIT License 4 votes vote down vote up
/**
 * Tests fix for Bug#33594 - When cursor fetch is enabled, wrong metadata is
 * returned from DBMD.
 * 
 * The fix is two parts.
 * 
 * First, when asking for the first column value twice from a cursor-fetched
 * row, the driver didn't re-position, and thus the "next" column was
 * returned.
 * 
 * Second, metadata statements and internal statements the driver uses
 * shouldn't use cursor-based fetching at all, so we've ensured that
 * internal statements have their fetch size set to "0".
 */
public void testBug33594() throws Exception {
    if (!versionMeetsMinimum(5, 0, 7)) {
        return;
    }
    boolean max_key_l_bug = false;

    try {
        createTable("bug33594", "(fid varchar(255) not null primary key, id INT, geom linestring, name varchar(255))");
    } catch (SQLException sqlEx) {
        if (sqlEx.getMessage().indexOf("max key length") != -1) {
            createTable("bug33594", "(fid varchar(180) not null primary key, id INT, geom linestring, name varchar(255))");
            max_key_l_bug = true;
        }
    }

    Properties props = new Properties();
    props.put("useInformationSchema", "false");
    props.put("useCursorFetch", "false");
    props.put("defaultFetchSize", "100");
    Connection conn1 = null;
    try {
        conn1 = getConnectionWithProps(props);
        DatabaseMetaData metaData = conn1.getMetaData();
        this.rs = metaData.getColumns(null, null, "bug33594", null);
        this.rs.next();
        assertEquals("bug33594", this.rs.getString("TABLE_NAME"));
        assertEquals("fid", this.rs.getString("COLUMN_NAME"));
        assertEquals("VARCHAR", this.rs.getString("TYPE_NAME"));
        if (!max_key_l_bug) {
            assertEquals("255", this.rs.getString("COLUMN_SIZE"));
        } else {
            assertEquals("180", this.rs.getString("COLUMN_SIZE"));
        }

        Properties props2 = new Properties();
        props2.put("useInformationSchema", "false");
        props2.put("useCursorFetch", "true");
        props2.put("defaultFetchSize", "100");

        Connection conn2 = null;

        try {
            conn2 = getConnectionWithProps(props2);
            DatabaseMetaData metaData2 = conn2.getMetaData();
            this.rs = metaData2.getColumns(null, null, "bug33594", null);
            this.rs.next();
            assertEquals("bug33594", this.rs.getString("TABLE_NAME"));
            assertEquals("fid", this.rs.getString("COLUMN_NAME"));
            assertEquals("VARCHAR", this.rs.getString("TYPE_NAME"));
            if (!max_key_l_bug) {
                assertEquals("255", this.rs.getString("COLUMN_SIZE"));
            } else {
                assertEquals("180", this.rs.getString("COLUMN_SIZE"));
            }

            // we should only see one server-side prepared statement, and
            // that's
            // caused by us going off to ask about the count!
            assertEquals("1", getSingleIndexedValueWithQuery(conn2, 2, "SHOW SESSION STATUS LIKE 'Com_stmt_prepare'").toString());
        } finally {
            if (conn2 != null) {
                conn2.close();
            }
        }
    } finally {
        if (conn1 != null) {
            conn1.close();
        }
    }

}
 
Example 16
Source File: MySqlMetaData.java    From DataDefender with Apache License 2.0 4 votes vote down vote up
@Override
protected ResultSet getColumnResultSet(final DatabaseMetaData md, final String tableName) throws SQLException {
    return md.getColumns(null, null, tableName, null);
}
 
Example 17
Source File: JDBCAdapter.java    From hana-native-adapters with Apache License 2.0 4 votes vote down vote up
@Override
public Metadata importMetadata(String tableuniquename) throws AdapterException {
	/*
	 * nodeId does match the format: catalog.schema.tablename
	 */
	
	String[] nodecomponents = tableuniquename.split("\\.");
	if (nodecomponents.length != 3) {
		throw new AdapterException("Unique Name of the table does not match the format catalog.schema.tablename: " + tableuniquename);
	}
	String catalogname = nodecomponents[0];
	String catalog_search_string;
	if (catalogname.equals("<none>")) {
		catalog_search_string = null;
	} else {
		catalog_search_string = catalogname;
	}

	String schemaname = nodecomponents[1];
	String schema_search_string;
	if (schemaname.equals("<none>")) {
		schema_search_string = null;
	} else {
		schema_search_string = schemaname;
	}
	
	String tablename = nodecomponents[2];
	if (tablename == null || tablename.length() == 0) {
		throw new AdapterException("Table Name portion cannot be empty: " + tableuniquename);
	}

	TableMetadata metas = new TableMetadata();
	metas.setName(tableuniquename);
	metas.setPhysicalName(tableuniquename);
	try{
		ResultSet rsColumns = null;
		DatabaseMetaData meta = conn.getMetaData();
		//catalog, schemaPattern, tableNamePatter, types
		rsColumns = meta.getColumns(catalog_search_string, schema_search_string, tablename, null);
		List<Column> cols = new ArrayList<Column>();
		while (rsColumns.next()) {
			Column col = getColumn(rsColumns);
			cols.add(col);
		}
		metas.setColumns(cols);
		metas.setUniqueKeys(getUniqueKeys(tableuniquename));
		setPrimaryFlagForColumns(metas);
		
	}catch(SQLException e){
		throw new AdapterException(e.getMessage());
	}
	return metas;
}
 
Example 18
Source File: PostgreSqlDataSource.java    From AuthMeReloaded with GNU General Public License v3.0 4 votes vote down vote up
private boolean isColumnMissing(DatabaseMetaData metaData, String columnName) throws SQLException {
    try (ResultSet rs = metaData.getColumns(null, null, tableName, columnName.toLowerCase())) {
        return !rs.next();
    }
}
 
Example 19
Source File: testListAgg.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
public testListAgg() {

		try {
			Connection cxn = DriverManager.getConnection("jdbc:gemfirexd:");
			Statement stmt = cxn.createStatement();

			stmt.execute("DROP TABLE XML_DOC_1");
			stmt.execute("DROP PROCEDURE ListAgg");
			stmt.execute("DROP ALIAS  ListAggProcessor");

			stmt.execute("CREATE PROCEDURE ListAgg(IN groupBy VARCHAR(256), "
					+ "IN ListAggCols VARCHAR(256), IN tableName VARCHAR(128), "
					+ "IN whereClause VARCHAR(256), IN delimiter VARCHAR(10)) "
					+ "LANGUAGE JAVA PARAMETER STYLE JAVA READS SQL DATA "
					+ "DYNAMIC RESULT SETS 1 EXTERNAL NAME 'ListAggProcedure.ListAgg';");
			String aliasString = "CREATE ALIAS ListAggProcessor FOR '"
					+ LISTAGGPROCESSOR.class.getName() + "'";
			System.out.println(aliasString);
			stmt.execute(aliasString);

			String tableDDL = "create table APP.XML_DOC_1 (ID int NOT NULL,"
					+ " SECONDID int not null, THIRDID varchar(10) not null) PARTITION BY COLUMN (ID)";

			stmt.execute(tableDDL);
			DatabaseMetaData dbmd = cxn.getMetaData();
			ResultSet resultSet = dbmd.getColumns(null, "CDSDBA", "XML_DOC_1",
					null);

			while (resultSet.next()) {
				String strTableName = resultSet.getString("COLUMN_NAME");
				System.out.println("TABLE_NAME is " + strTableName);
			}
			
			  stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (2, 1, '3'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (3, 3, '3'); " );
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (4, 4, '3'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (5, 5, '3'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (2, 1, '9'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (2, 3, '4'); ");
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (3, 3, '4'); "); 
			 stmt.execute("INSERT INTO APP.XML_DOC_1  VALUES (5, 3, '4'); "); 
			 stmt.execute("select count(*) from APP.XML_DOC_1 ");
			 

			String queryString = "{CALL APP.ListAgg(?,?,?,?,?) WITH RESULT PROCESSOR ListAggProcessor }";
			// queryString =
			// "{CALL CDSDBA.ListAgg('structure_id_nbr DESC','create_mint_cd','CDSDBA.XML_DOC_1','MSG_PAYLOAD_QTY=5',',') WITH RESULT PROCESSOR ListAggProcessor ;}";
			CallableStatement cs = cxn.prepareCall(queryString);

			String groupBy = "ID";
			String listAgg = "THIRDID";
			String table = "XML_DOC_1";
			String whereClause = "";
			String delimiter = ",";

			cs = cxn.prepareCall(queryString);
			cs.setString(1, groupBy);
			cs.setString(2, listAgg);
			cs.setString(3, table);
			cs.setString(4, whereClause);
			cs.setString(5, delimiter);

			long startTime = new Date().getTime();
			cs.execute();

			long endTime = new Date().getTime();
			System.out.println("Duration = " + (endTime - startTime));
			ResultSet thisResultSet;
			boolean moreResults = true;
			int cnt = 0;

			do {
				thisResultSet = cs.getResultSet();
				ResultSetMetaData rMeta = thisResultSet.getMetaData();
				int colCnt = rMeta.getColumnCount();
				for (int i = 1; i < colCnt + 1; i++) {
					System.out.print(rMeta.getColumnName(i));
					System.out.print("\t");
				}
				System.out.println("");

				if (cnt == 0) {
					while (thisResultSet.next()) {
						for (int i = 1; i < colCnt + 1; i++) {
							System.out.print(thisResultSet.getObject(i));
							System.out.print("\t");
						}
						System.out.println("");
					}
					System.out.println("ResultSet 1 ends\n");
					cnt++;
				}
				moreResults = cs.getMoreResults();
			} while (moreResults);

		} catch (SQLException e) {
			e.printStackTrace();
		}

	}
 
Example 20
Source File: JDBCUtil.java    From james-project with Apache License 2.0 3 votes vote down vote up
/**
 * Checks database metadata to see if a column exists in a table. This
 * method is sensitive to the case of both the provided table name and
 * column name.
 * 
 * @param dbMetaData
 *            the database metadata to be used to look up this column
 * @param tableName
 *            the case sensitive table name
 * @param columnName
 *            the case sensitive column name
 * 
 * @throws SQLException
 *             if an exception is encountered while accessing the database
 */
public boolean columnExistsCaseSensitive(DatabaseMetaData dbMetaData, String tableName, String columnName) throws SQLException {
    ResultSet rsTables = dbMetaData.getColumns(null, null, tableName, columnName);
    try {
        return rsTables.next();
    } finally {
        closeJDBCResultSet(rsTables);
    }
}