Java Code Examples for java.sql.ResultSetMetaData

The following examples show how to use java.sql.ResultSetMetaData. These examples are extracted from open source projects. 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 Project: incubator-iotdb   Source File: IoTDBAuthorizationIT.java    License: Apache License 2.0 6 votes vote down vote up
private void validateResultSet(ResultSet set, String ans) throws SQLException {
  try {
    StringBuilder builder = new StringBuilder();
    ResultSetMetaData metaData = set.getMetaData();
    int colNum = metaData.getColumnCount();
    while (set.next()) {
      for (int i = 1; i <= colNum; i++) {
        builder.append(set.getString(i)).append(",");
      }
      builder.append("\n");
    }
    assertEquals(ans, builder.toString());
  } finally {
    set.close();
  }
}
 
Example 2
Source Project: dalesbred   Source File: ResultTableResultSetProcessor.java    License: MIT License 6 votes vote down vote up
@Override
public @NotNull ResultTable process(@NotNull ResultSet resultSet) throws SQLException {
    ResultSetMetaData metaData = resultSet.getMetaData();
    int columnCount = metaData.getColumnCount();

    ResultTable.Builder builder = createBuilder(metaData);
    while (resultSet.next()) {
        Object[] row = new Object[columnCount];

        for (int i = 0; i < columnCount; i++)
            row[i] = resultSet.getObject(i+1);

        builder.addRow(asList(row));
    }

    return builder.build();
}
 
Example 3
Source Project: gemfirexd-oss   Source File: EventCallbackWriterImpl.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Build querying string to delete a row in the backend database
 * 
 * @param tableName
 *          fully qualified name of the table
 * @param pkMeta
 *          meta-data of the primary key columns of the table
 * 
 * @return SQL query string to delete a row in the backend database
 * 
 * @throws SQLException
 *           on error
 */
private String buildDeleteQuery(String tableName, ResultSetMetaData pkMeta)
    throws SQLException {

  StringBuilder query = new StringBuilder().append("DELETE FROM ");

  query.append(tableName);
  query.append(" WHERE ");
  // use the primary key columns to fire the delete on backend DB
  final int numCols = pkMeta.getColumnCount();
  for (int col = 1; col < numCols; col++) {
    query.append(pkMeta.getColumnName(col));
    query.append("=? AND ");
  }
  query.append(pkMeta.getColumnName(numCols));
  query.append("=?");

  return query.toString();
}
 
Example 4
Source Project: r-course   Source File: MetaDataRegressionTest.java    License: MIT License 6 votes vote down vote up
private void checkRsmdForBug13277(ResultSetMetaData rsmd) throws SQLException {

        int i = ((com.mysql.jdbc.ConnectionImpl) this.conn)
                .getMaxBytesPerChar(CharsetMapping.getJavaEncodingForMysqlCharset(((com.mysql.jdbc.Connection) this.conn).getServerCharset()));
        if (i == 1) {
            // This is INT field but still processed in
            // ResultsetMetaData.getColumnDisplaySize
            assertEquals(20, rsmd.getColumnDisplaySize(1));
        }

        if (versionMeetsMinimum(4, 1)) {
            assertEquals(false, rsmd.isDefinitelyWritable(1));
            assertEquals(true, rsmd.isReadOnly(1));
            assertEquals(false, rsmd.isWritable(1));
        }
    }
 
Example 5
Source Project: r-course   Source File: MetaDataRegressionTest.java    License: MIT License 6 votes vote down vote up
/**
 * Tests fix for BUG#2855, where RSMD is not returning correct (or matching)
 * types for FLOAT.
 * 
 * @throws Exception
 *             if the test fails.
 */
public void testBug2855() throws Exception {
    try {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2855");
        this.stmt.executeUpdate("CREATE TABLE testBug2855 (field1 FLOAT)");
        this.stmt.executeUpdate("INSERT INTO testBug2855 VALUES (1)");

        this.rs = this.stmt.executeQuery("SELECT * from testBug2855");

        assertTrue(this.rs.next());

        ResultSetMetaData rsmd = this.rs.getMetaData();

        assertTrue(rsmd.getColumnClassName(1).equals(this.rs.getObject(1).getClass().getName()));
        assertTrue("java.lang.Float".equals(rsmd.getColumnClassName(1)));
    } finally {
        this.stmt.executeUpdate("DROP TABLE IF EXISTS testBug2855");
    }
}
 
Example 6
Source Project: shardingsphere   Source File: BaseDQLIT.java    License: Apache License 2.0 6 votes vote down vote up
private void assertRows(final ResultSet actualResultSet, final List<DataSetRow> expectedDatSetRows) throws SQLException {
    int count = 0;
    ResultSetMetaData actualMetaData = actualResultSet.getMetaData();
    while (actualResultSet.next()) {
        int index = 1;
        assertTrue("Size of actual result set is different with size of expected dat set rows.", count < expectedDatSetRows.size());
        for (String each : expectedDatSetRows.get(count).getValues()) {
            if (Types.DATE == actualResultSet.getMetaData().getColumnType(index)) {
                if (!NOT_VERIFY_FLAG.equals(each)) {
                    assertThat(new SimpleDateFormat("yyyy-MM-dd").format(actualResultSet.getDate(index)), is(each));
                    assertThat(new SimpleDateFormat("yyyy-MM-dd").format(actualResultSet.getDate(actualMetaData.getColumnLabel(index))), is(each));
                }
            } else {
                assertThat(String.valueOf(actualResultSet.getObject(index)), is(each));
                assertThat(String.valueOf(actualResultSet.getObject(actualMetaData.getColumnLabel(index))), is(each));
            }
            index++;
        }
        count++;
    }
    assertThat("Size of actual result set is different with size of expected dat set rows.", count, is(expectedDatSetRows.size()));
}
 
Example 7
Source Project: kylin-on-parquet-v2   Source File: DriverTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testPreparedStatementWithMockData() throws SQLException {
    Driver driver = new DummyDriver();

    Connection conn = driver.connect("jdbc:kylin://test_url/test_db", null);
    PreparedStatement state = conn.prepareStatement("select * from test_table where id=?");
    state.setInt(1, 10);
    ResultSet resultSet = state.executeQuery();

    ResultSetMetaData metadata = resultSet.getMetaData();
    assertEquals(12, metadata.getColumnType(1));
    assertEquals("varchar", metadata.getColumnTypeName(1));
    assertEquals(1, metadata.isNullable(1));

    while (resultSet.next()) {
        assertEquals("foo", resultSet.getString(1));
        assertEquals("bar", resultSet.getString(2));
        assertEquals("tool", resultSet.getString(3));
    }

    resultSet.close();
    state.close();
    conn.close();
}
 
Example 8
Source Project: Kylin   Source File: DriverTest.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testPreStatementWithMockData() throws SQLException {
    Driver driver = new DummyDriver();

    Connection conn = driver.connect("jdbc:kylin://test_url/test_db", null);
    PreparedStatement state = conn.prepareStatement("select * from test_table where id=?");
    state.setInt(1, 10);
    ResultSet resultSet = state.executeQuery();

    ResultSetMetaData metadata = resultSet.getMetaData();
    assertEquals(12, metadata.getColumnType(1));
    assertEquals("varchar", metadata.getColumnTypeName(1));
    assertEquals(1, metadata.isNullable(1));

    while (resultSet.next()) {
        assertEquals("foo", resultSet.getString(1));
        assertEquals("bar", resultSet.getString(2));
        assertEquals("tool", resultSet.getString(3));
    }
}
 
Example 9
Source Project: kareldb   Source File: JDBC.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Check the nullability of the column definitions for
 * the ResultSet matches the expected values.
 *
 * @param rs
 * @param nullability
 * @throws SQLException
 */
public static void assertNullability(ResultSet rs,
                                     boolean[] nullability) throws SQLException {
    ResultSetMetaData rsmd = rs.getMetaData();
    int actualCols = rsmd.getColumnCount();

    assertEquals("Unexpected column count:",
        nullability.length, rsmd.getColumnCount());

    for (int i = 0; i < actualCols; i++) {
        int expected = nullability[i] ?
            ResultSetMetaData.columnNullable : ResultSetMetaData.columnNoNulls;
        assertEquals("Column nullability do not match for column " + (i + 1),
            expected, rsmd.isNullable(i + 1));
    }
}
 
Example 10
Source Project: syndesis   Source File: DbMetaDataHelper.java    License: Apache License 2.0 6 votes vote down vote up
@SuppressFBWarnings("OBL_UNSATISFIED_OBLIGATION")
/* default */ List<SqlParam> getOutputColumnInfo(
        final String sqlSelectStatement) throws SQLException {
    List<SqlParam> paramList = new ArrayList<>();
    try (PreparedStatement stmt = createPreparedStatement(sqlSelectStatement);
        ResultSet resultSet = stmt.executeQuery();) {
        ResultSetMetaData metaData = resultSet.getMetaData();
        if (metaData.getColumnCount()>0){
            for (int i=1; i<=metaData.getColumnCount(); i++) {
                SqlParam param = new SqlParam(metaData.getColumnName(i));
                param.setJdbcType(JDBCType.valueOf(metaData.getColumnType(i)));
                paramList.add(param);
            }
        }
        return paramList;
    }
}
 
Example 11
Source Project: gemfirexd-oss   Source File: AbstractWriter.java    License: Apache License 2.0 6 votes vote down vote up
protected String getSql(Event event) throws SQLException {
	ResultSetMetaData meta = event.getResultSetMetaData();
	List<Object> newRow = event.getNewRow();
	StringBuffer str = new StringBuffer();
	/*
	Log.getLogWriter().info("from metadata, schema name is " + meta.getSchemaName(1) 
			+ " table name is " + meta.getTableName(1));
	*/
	if (event.getModifiedColumns() == null) throw new TestException("event.getModifiedColumns " +
			"return null on BEFORE_UPDATE");
	
	str.append("update " + meta.getSchemaName(1) + "." 
			+ meta.getTableName(1) + " set ");		
	for (int i=0; i<event.getModifiedColumns().length; i++) {
                       str.append(" " + meta.getColumnName(event.getModifiedColumns()[i]) + "=");
                       appendValue(str, newRow.get(event.getModifiedColumns()[i]-1),
                                               meta.getColumnType(event.getModifiedColumns()[i]));
	}
	str.delete(str.length() -1 , str.length());
	return str.toString();
}
 
Example 12
Source Project: stategen   Source File: SqlFactory.java    License: GNU Affero General Public License v3.0 6 votes vote down vote up
private ResultSetMetaData executeSqlForResultSetMetaData(String sql,PreparedStatement ps,List<SqlParameter> params,Map<String, Object> randomValues)throws SQLException {
//      SqlParseHelper.setRandomParamsValueForPreparedStatement(SqlParseHelper.removeOrders(executeSql), ps);
        StatementCreatorUtils.setRandomParamsValueForPreparedStatement(sql, ps, params,randomValues);
        try {
            ps.setMaxRows(3);
            ps.setFetchSize(3);
            ps.setQueryTimeout(20);
            ResultSet rs = null;
            if(ps.execute()) {
                rs = ps.getResultSet();
                return rs.getMetaData();
            }
            return null;
        }catch(SQLException e) {
            if(isDataIntegrityViolationException(e)) {
                GLogger.warn("ignore executeSqlForResultSetMetaData() SQLException,errorCode:"+e.getErrorCode()+" sqlState:"+e.getSQLState()+" message:"+e.getMessage()+ "\n executedSql:"+sql);
                return null;
            }
            String message = "errorCode:"+e.getErrorCode()+" SQLState:"+e.getSQLState()+" errorCodeTranslatorDataBaaseName:"+getErrorCodeTranslatorDataBaaseName()+" "+ e.getMessage();
            throw new SQLException(message,e.getSQLState(),e.getErrorCode());
        }
    }
 
Example 13
Source Project: gemfirexd-oss   Source File: ProcedureTest.java    License: Apache License 2.0 5 votes vote down vote up
public void testExecuteQueryWithDataAwareProcedureCall()
throws SQLException {

  setup();
  CallableStatement cs = prepareCall("CALL RETRIEVE_DYNAMIC_RESULTS(?) "
      + "ON TABLE EMP.PARTITIONTESTTABLE WHERE SECONDID in (?,?,?) AND THIRDID='3'");
  cs.setInt(1, 2);
  cs.setInt(2, 3);
  cs.setInt(3, 4);
  cs.setInt(4, 5);
  cs.execute();
  
  String[][] results=new String[2][1];
  results[0][0]="1";
  results[1][0]="1";
           
  int rsIndex=-1;
  do {
    ++rsIndex;
    int rowIndex=0;
    ResultSet rs = cs.getResultSet();
    ResultSetMetaData metaData = rs.getMetaData();
    int rowCount = metaData.getColumnCount();
    while (rs.next()) {
      String row="";
      for (int i = 1; i <=rowCount; ++i) {
        Object value = rs.getObject(i);
        row+=value.toString();          
      }
      if(rsIndex>1 || rowIndex>1) {
        fail("the result is not correct!");
      }
      if(!row.equals(results[rsIndex][rowIndex])) {
        fail("the result is not correct!");
      }
      ++rowIndex;
    }
  } while (cs.getMoreResults());    
  
}
 
Example 14
Source Project: calcite-avatica   Source File: MetaResultSetTest.java    License: Apache License 2.0 5 votes vote down vote up
@Test public void testGetCatalogs() throws SQLException {
  DatabaseMetaData metadata = getDatabaseMetadata();
  try (ResultSet rs = metadata.getCatalogs()) {
    ResultSetMetaData rsMeta = rs.getMetaData();

    assertEquals(1, rsMeta.getColumnCount());
    assertColumn(rsMeta, 1, "TABLE_CAT", Types.VARCHAR, DatabaseMetaData.columnNoNulls);
  }
}
 
Example 15
Source Project: incubator-iotdb   Source File: IoTDBAliasIT.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void selectAggregationWithAliasTest() throws ClassNotFoundException {
  String[] retArray = new String[]{
      "4,4,28.3,26.3,"
  };

  Class.forName(Config.JDBC_DRIVER_NAME);
  try (Connection connection = DriverManager
      .getConnection(Config.IOTDB_URL_PREFIX + "127.0.0.1:6667/", "root", "root");
      Statement statement = connection.createStatement()) {
    boolean hasResultSet = statement
        .execute("select count(speed), max_value(temperature) from root.sg.*");
    Assert.assertTrue(hasResultSet);

    try (ResultSet resultSet = statement.getResultSet()) {
      ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
      StringBuilder header = new StringBuilder();
      for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
        header.append(resultSetMetaData.getColumnName(i)).append(",");
      }
      assertEquals("count(root.sg.d1.speed),count(root.sg.d2.speed),"
              + "max_value(root.sg.d1.temperature),max_value(root.sg.d2.temperature),",
          header.toString());

      int cnt = 0;
      while (resultSet.next()) {
        StringBuilder builder = new StringBuilder();
        for (int i = 1; i <= resultSetMetaData.getColumnCount(); i++) {
          builder.append(resultSet.getString(i)).append(",");
        }
        assertEquals(retArray[cnt], builder.toString());
        cnt++;
      }
      assertEquals(retArray.length, cnt);
    }
  } catch (Exception e) {
    e.printStackTrace();
    fail(e.getMessage());
  }
}
 
Example 16
@Test
public void testBasicResultSetMetaData() throws Exception {
    String query = "SELECT organization_id, a_string, b_string, a_integer i, a_date FROM atable WHERE organization_id='000000000000000' and substr(entity_id,1,3)=? and a_string = 'foo'";
    Connection conn = DriverManager.getConnection(getUrl(), TestUtil.TEST_PROPERTIES);
    PreparedStatement statement = conn.prepareStatement(query);
    ResultSetMetaData md = statement.getMetaData();
    assertEquals(5, md.getColumnCount());
    
    assertEquals("organization_id".toUpperCase(),md.getColumnName(1));
    assertEquals("a_string".toUpperCase(),md.getColumnName(2));
    assertEquals("b_string".toUpperCase(),md.getColumnName(3));
    assertEquals("i".toUpperCase(),md.getColumnName(4));
    assertEquals("a_date".toUpperCase(),md.getColumnName(5));
    
    assertEquals(String.class.getName(),md.getColumnClassName(1));
    assertEquals(String.class.getName(),md.getColumnClassName(2));
    assertEquals(String.class.getName(),md.getColumnClassName(3));
    assertEquals(Integer.class.getName(),md.getColumnClassName(4));
    assertEquals(Date.class.getName(),md.getColumnClassName(5));
    
    assertEquals("atable".toUpperCase(),md.getTableName(1));
    assertEquals(java.sql.Types.INTEGER,md.getColumnType(4));
    assertEquals(true,md.isReadOnly(1));
    assertEquals(false,md.isDefinitelyWritable(1));
    assertEquals("i".toUpperCase(),md.getColumnLabel(4));
    assertEquals("a_date".toUpperCase(),md.getColumnLabel(5));
    assertEquals(ResultSetMetaData.columnNoNulls,md.isNullable(1));
    assertEquals(ResultSetMetaData.columnNullable,md.isNullable(5));
}
 
Example 17
Source Project: hop   Source File: Database.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * @param ps The prepared insert statement to use
 * @return The generated keys in auto-increment fields
 * @throws HopDatabaseException in case something goes wrong retrieving the keys.
 */
public RowMetaAndData getGeneratedKeys( PreparedStatement ps ) throws HopDatabaseException {
  ResultSet keys = null;
  try {
    keys = ps.getGeneratedKeys(); // 1 row of keys
    ResultSetMetaData resultSetMetaData = keys.getMetaData();
    if ( resultSetMetaData == null ) {
      resultSetMetaData = ps.getMetaData();
    }
    IRowMeta rowMeta;
    if ( resultSetMetaData == null ) {
      rowMeta = new RowMeta();
      rowMeta.addValueMeta( new ValueMetaInteger( "ai-key" ) );
    } else {
      rowMeta = getRowInfo( resultSetMetaData, false, false );
    }

    return new RowMetaAndData( rowMeta, getRow( keys, resultSetMetaData, rowMeta ) );
  } catch ( Exception ex ) {
    throw new HopDatabaseException( "Unable to retrieve key(s) from auto-increment field(s)", ex );
  } finally {
    if ( keys != null ) {
      try {
        keys.close();
      } catch ( SQLException e ) {
        throw new HopDatabaseException( "Unable to close resultset of auto-generated keys", e );
      }
    }
  }
}
 
Example 18
Source Project: gemfirexd-oss   Source File: UDTTest.java    License: Apache License 2.0 5 votes vote down vote up
/**
 * Check the ResultSetMetaData for a query whose first column is a UDT.
 */
private void checkRSMD
    (
     Connection conn,
     String query,
     String expectedClassName,
     int expectedDisplaySize,
     int expectedJDBCType,
     String expectedSQLTypeName,
     int expectedPrecision,
     int expectedScale
     ) throws Exception
{
    PreparedStatement ps = conn.prepareStatement( query );
    ResultSet rs = ps.executeQuery();
    ResultSetMetaData rsmd = rs.getMetaData();

    assertEquals( rsmd.getColumnClassName( 1 ), expectedClassName );
    assertEquals( rsmd.getColumnDisplaySize( 1 ), expectedDisplaySize );
    assertEquals( rsmd.getColumnType( 1 ), expectedJDBCType );
    assertEquals( rsmd.getColumnTypeName( 1 ), expectedSQLTypeName );
    assertEquals( rsmd.getPrecision( 1 ), expectedPrecision );
    assertEquals( rsmd.getScale( 1 ), expectedScale );

    rs.close();
    ps.close();
}
 
Example 19
/**
 * Prints a diff between a ResultSet and an expected Object[][]
 * value to a PrintStream. The first line is a heading with name
 * and type of each column. Each row is printed as a
 * comma-separated list of columns. The printed value of a column
 * is getObject(i).toString(). <p>
 *
 * If the expected value does not match the value from the RS, the
 * expected value is printed followed by the actual value in angle
 * brackets.  The comparion starts from 'fromRow' (zero-based row
 * index). Unmatched rows are printed with 'null' for the missing
 * values. <p>
 *
 * dumpee must be positioned on a valid row, or moreRows must be
 * false.  Closes the RS when done.
 * @param expected the expected value of the RS
 * @param fromRow row to start comparison from
 * @param dumpee the ResultSet to dump
 * @param moreRows true if there are more rows in the RS
 * @param stream the stream to dump the ResultSet to
 */
private static void dumpDiff(Object[][] expected, int fromRow,
                             ResultSet dumpee, boolean moreRows,
                             PrintStream stream) throws SQLException {
    final ResultSetMetaData rm = dumpee.getMetaData();
    final int colCount = rm.getColumnCount();
    for (int c = 1; c <= colCount; ++c) {
        stream.print("" + rm.getColumnLabel(c) + " " +
                         rm.getColumnTypeName(c) + ", ");
    }
    stream.println("");

    for (; moreRows || fromRow < expected.length; ++fromRow) {
        for (int c = 1; c <= colCount; ++c) {
            final Object e =
                (fromRow<expected.length?expected[fromRow][c-1]:null);
            final Object ret = (moreRows?dumpee.getObject(c):null);
            stream.print(e);
            if (e == null || ret == null || !ret.equals(e)) {
                stream.print("<" + ret +">");
            }
            stream.print(", ");
        }
        stream.println("");
        moreRows = dumpee.next();
    }
    dumpee.close();
}
 
Example 20
Source Project: database   Source File: CommonTest.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void metadataColumnTypes() {
  String timestampColumnName = "data_millis";
  String dateColumnName = "local_date";
  new Schema()
      .addTable("dbtest")
      .addColumn(timestampColumnName).asDate().table()
      .addColumn(dateColumnName).asLocalDate().table().schema().execute(db);
  db.toSelect("select * from dbtest").query(new RowsHandler<Void>() {
    @Override
    public Void process(Rows rs) throws Exception {

      ResultSetMetaData metadata = rs.getMetadata();
      for (int i=1; i <= metadata.getColumnCount(); i++) {
        String columnName = metadata.getColumnName(i);
        String columnType = metadata.getColumnTypeName(i);

        if (columnName.equalsIgnoreCase(timestampColumnName)) {
          if ("sqlserver".equals(db.flavor().toString())) {
            assertEquals("DATETIME2", columnType.toUpperCase());
          } else{
            assertEquals("TIMESTAMP", columnType.toUpperCase());
          }
        } else if (columnName.equalsIgnoreCase(dateColumnName)) {
          assertEquals("DATE", columnType.toUpperCase());
        } else {
          fail("Unexpected column " + columnName + " of type " + columnType);
        }
      }
      return null;
    }
  });
}
 
Example 21
Source Project: SimpleFlatMapper   Source File: JdbcMapperBuilder.java    License: MIT License 5 votes vote down vote up
/**
 * add the all the property present in the metaData
 *
 * @param metaData the metaDAta
 * @return the current builder
 * @throws SQLException when an error occurs getting the metaData
 */
public JdbcMapperBuilder<T> addMapping(final ResultSetMetaData metaData) throws SQLException {
    for (int i = 1; i <= metaData.getColumnCount(); i++) {
        addMapping(metaData.getColumnLabel(i), i, metaData.getColumnType(i));
    }

    return this;
}
 
Example 22
@SuppressWarnings("unchecked")
@Test
public void test99() throws Exception {
    RowSetMetaData rsmd1 = rsmd;
    ResultSetMetaData rsmd2 = rsmd;
    Class clzz = rsmd.getClass();
    assertTrue(rsmd1.isWrapperFor(clzz));
    assertTrue(rsmd2.isWrapperFor(clzz));
    RowSetMetaDataImpl rsmdi = (RowSetMetaDataImpl) rsmd2.unwrap(clzz);

    // False should be returned
    assertFalse(rsmd1.isWrapperFor(this.getClass()));
    assertFalse(rsmd2.isWrapperFor(this.getClass()));
}
 
Example 23
Source Project: systemds   Source File: SqlCPInstruction.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void processInstruction(ExecutionContext ec) {
	String conn = ec.getScalarInput(_conn).getStringValue();
	String user = ec.getScalarInput(_user).getStringValue();
	String pass = ec.getScalarInput(_pass).getStringValue();
	String query = ec.getScalarInput(_query).getStringValue();
	
	try (Connection connection = user.isEmpty() ? DriverManager.getConnection(conn) :
			DriverManager.getConnection(conn, user, pass)) {
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery("SELECT COUNT(*) FROM (" + query + ") AS sub");
		resultSet.next();
		int rows = resultSet.getInt(1);
		resultSet = statement.executeQuery(query);
		ResultSetMetaData meta = resultSet.getMetaData();
		int cols = meta.getColumnCount();
		ValueType[] schema = getSchemaFromMetaData(meta);
		
		int[] dims = {rows, cols};
		TensorBlock outBlock = new TensorBlock(schema, dims);
		// TODO: perf
		for (int row = 0; resultSet.next(); row++)
			for (int i = 0; i < cols; i++)
				setCell(outBlock, resultSet, schema[i], new int[]{row, i});
		ec.setTensorOutput(_output.getName(), outBlock);
		ec.getDataCharacteristics(_output.getName()).setDim(0, rows).setDim(1, cols);
	}
	catch (SQLException e) {
		throw new DMLRuntimeException("SQL Error: " + e.getMessage());
	}
}
 
Example 24
Source Project: localization_nifi   Source File: TestJdbcCommon.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testSignedIntShouldBeInt() throws SQLException, IllegalArgumentException, IllegalAccessException {
    final ResultSetMetaData metadata = mock(ResultSetMetaData.class);
    when(metadata.getColumnCount()).thenReturn(1);
    when(metadata.getColumnType(1)).thenReturn(Types.INTEGER);
    when(metadata.isSigned(1)).thenReturn(true);
    when(metadata.getColumnName(1)).thenReturn("Col1");
    when(metadata.getTableName(1)).thenReturn("Table1");

    final ResultSet rs = mock(ResultSet.class);
    when(rs.getMetaData()).thenReturn(metadata);

    Schema schema = JdbcCommon.createSchema(rs);
    Assert.assertNotNull(schema);

    Schema.Field field = schema.getField("Col1");
    Schema fieldSchema = field.schema();
    Assert.assertEquals(2, fieldSchema.getTypes().size());

    boolean foundIntSchema = false;
    boolean foundNullSchema = false;

    for (Schema type : fieldSchema.getTypes()) {
        if (type.getType().equals(Schema.Type.INT)) {
            foundIntSchema = true;
        } else if (type.getType().equals(Schema.Type.NULL)) {
            foundNullSchema = true;
        }
    }

    assertTrue(foundIntSchema);
    assertTrue(foundNullSchema);
}
 
Example 25
Source Project: tmxeditor8   Source File: DBOperator.java    License: GNU General Public License v2.0 5 votes vote down vote up
protected Map<Integer, Map<String, String>> query(String querySql, Object[] params) throws SQLException {
	data.clear();
	PreparedStatement psmt = null;
	ResultSet rt = null;
	try {
		if (null == conn) {
			return data;
		}
		psmt = conn.prepareStatement(querySql);

		for (int i = 0; i < params.length; i++) {
			setParameter(psmt, i + 1, params[i]);
		}

		rt = psmt.executeQuery();
		ResultSetMetaData meta = rt.getMetaData();
		int cols = meta.getColumnCount();
		int rowIndex = 0;
		while (rt.next()) {
			Map<String, String> rowData = new HashMap<String, String>();
			for (int i = 1; i <= cols; i++) {
				rowData.put(meta.getColumnLabel(i).toUpperCase(), rt.getString(i));
			}
			data.put(rowIndex++, rowData);
		}
	} finally {
		if (rt != null) {
			rt.close();
		}
		if (psmt != null) {
			psmt.close();
		}
	}
	return data;

}
 
Example 26
@Override
public ResultSetMetaData getResultSetMetaData() throws SQLException {
    if (resultSetMetaData == null) {
        // Just compile top level query without optimizing to get ResultSetMetaData
        QueryPlan plan = new QueryCompiler(PhoenixStatement.this).compile(this);
        resultSetMetaData = new PhoenixResultSetMetaData(connection, plan.getProjector());
    }
    return resultSetMetaData;
}
 
Example 27
Source Project: vividus   Source File: DatabaseStepsTests.java    License: Apache License 2.0 5 votes vote down vote up
private ResultSet mockResultSet(String columnName, String value) throws SQLException
{
    ResultSetMetaData rsmd = mock(ResultSetMetaData.class);
    when(rsmd.getColumnCount()).thenReturn(1);
    when(rsmd.getColumnLabel(1)).thenReturn(columnName);
    ResultSet rs = mock(ResultSet.class);
    when(rs.next()).thenReturn(true).thenReturn(false);
    when(rs.getMetaData()).thenReturn(rsmd);
    when(rs.getObject(1)).thenReturn(value);
    return rs;
}
 
Example 28
@DataProvider(name = "validSetNullableValues")
private Object[][] validSetNullableValues() {
    return new Object[][]{
        {ResultSetMetaData.columnNoNulls},
        {ResultSetMetaData.columnNullable},
        {ResultSetMetaData.columnNullableUnknown}
    };
}
 
Example 29
Source Project: dremio-oss   Source File: JdbcTestQueryBase.java    License: Apache License 2.0 5 votes vote down vote up
protected static void testQuery(String sql) throws Exception{
  boolean success = false;
  try {
    for (int x = 0; x < 1; x++) {
      Stopwatch watch = Stopwatch.createStarted();
      Statement s = getConnection().createStatement();
      ResultSet r = s.executeQuery(sql);
      System.out.println(String.format("QueryId: %s", r.unwrap(DremioResultSet.class).getQueryId()));
      boolean first = true;
      while (r.next()) {
        ResultSetMetaData md = r.getMetaData();
        if (first == true) {
          for (int i = 1; i <= md.getColumnCount(); i++) {
            System.out.print(md.getColumnName(i));
            System.out.print('\t');
          }
          System.out.println();
          first = false;
        }

        for (int i = 1; i <= md.getColumnCount(); i++) {
          System.out.print(r.getObject(i));
          System.out.print('\t');
        }
        System.out.println();
      }

      System.out.println(String.format("Query completed in %d millis.", watch.elapsed(TimeUnit.MILLISECONDS)));
    }

    System.out.println("\n\n\n");
    success = true;
  } finally {
    if (!success) {
      Thread.sleep(2000);
    }
  }
}
 
Example 30
Source Project: datacollector   Source File: TestSqlParserProcessor.java    License: Apache License 2.0 5 votes vote down vote up
@Test
public void testUnsupportedToError() throws Exception {
  SqlParserConfigBean config = new SqlParserConfigBean();
  config.resolveSchema = true;
  config.sqlField = "/sql";
  config.resultFieldPath = "/res";
  config.dbTimeZone = "UTC";
  config.sendUnsupportedFields = false;
  config.unsupportedFieldOp = UnsupportedFieldTypeValues.TO_ERROR;
  Record r = RecordCreator.create();
  Map<String, Field> fields = new HashMap<>();
  fields.put("sql", Field.create("INSERT INTO \"SYS\".\"TEST\"(\"ID\", \"CL\") VALUES ('10', EMPTY_CLOB())"));
  r.set(Field.create(fields));
  SqlParserProcessor processor = new SqlParserProcessor(config);
  ResultSetMetaData rsmd = setupMocks("\"SYS\".\"TEST\"", processor);
  Mockito.when(rsmd.getColumnCount()).thenReturn(2);
  Mockito.when(rsmd.getColumnType(eq(1))).thenReturn(Types.INTEGER);
  Mockito.when(rsmd.getColumnName(eq(1))).thenReturn("ID");
  Mockito.when(rsmd.getColumnType(eq(2))).thenReturn(Types.CLOB);
  Mockito.when(rsmd.getColumnName(eq(2))).thenReturn("CL");
  ProcessorRunner runner = new ProcessorRunner.Builder(SqlParserDProcessor.class, processor)
      .addOutputLane("s").setOnRecordError(OnRecordError.TO_ERROR).build();
  runner.runInit();
  StageRunner.Output output = runner.runProcess(ImmutableList.of(r));
  Assert.assertTrue(output.getRecords().get("s").isEmpty());
  Record result = runner.getErrorRecords().get(0);
  Assert.assertEquals(10, result.get("/res/ID").getValueAsInteger());
  Assert.assertNull(result.get("/res/CL"));
  assertOperationCode(result, OperationType.INSERT_CODE);
  assertTableSchema(result, "SYS", "TEST");
}