Java Code Examples for java.sql.ResultSetMetaData#isNullable()

The following examples show how to use java.sql.ResultSetMetaData#isNullable() . 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: ResultSetIterator.java    From spliceengine with GNU Affero General Public License v3.0 6 votes vote down vote up
public ResultSetIterator(Connection connection, PreparedStatement ps, ExecRow execRow) {
    this.connection = connection;
    try {
        this.resultSet = ps.executeQuery();
        ResultSetMetaData rsm = resultSet.getMetaData();
        isNullable = new boolean[rsm.getColumnCount()];
        for (int i =0;i<rsm.getColumnCount();i++) {
            isNullable[i] =rsm.isNullable(i+1)!=0;
        }
    }  catch (Exception e) {
        try {
            if (connection != null)
                connection.close();
        } catch (Exception cE) {
            throw new RuntimeException(cE);
        }
        throw new RuntimeException(e);
    }
    this.execRow = execRow;
}
 
Example 2
Source File: ResultSetRecordSet.java    From nifi with Apache License 2.0 6 votes vote down vote up
private static RecordSchema createSchema(final ResultSet rs, final RecordSchema readerSchema) throws SQLException {
    final ResultSetMetaData metadata = rs.getMetaData();
    final int numCols = metadata.getColumnCount();
    final List<RecordField> fields = new ArrayList<>(numCols);

    for (int i = 0; i < numCols; i++) {
        final int column = i + 1;
        final int sqlType = metadata.getColumnType(column);

        final DataType dataType = getDataType(sqlType, rs, column, readerSchema);
        final String fieldName = metadata.getColumnLabel(column);

        final int nullableFlag = metadata.isNullable(column);
        final boolean nullable;
        if (nullableFlag == ResultSetMetaData.columnNoNulls) {
            nullable = false;
        } else {
            nullable = true;
        }

        final RecordField field = new RecordField(fieldName, dataType, nullable);
        fields.add(field);
    }

    return new SimpleRecordSchema(fields);
}
 
Example 3
Source File: JDBCAvroRegistry.java    From components with Apache License 2.0 6 votes vote down vote up
protected Schema inferSchemaResultSetMetaData(ResultSetMetaData metadata) throws SQLException {
    List<Field> fields = new ArrayList<>();

    int count = metadata.getColumnCount();
    for (int i = 1; i <= count; i++) {
        int size = metadata.getPrecision(i);
        int scale = metadata.getScale(i);
        boolean nullable = ResultSetMetaData.columnNullable == metadata.isNullable(i);

        int dbtype = metadata.getColumnType(i);
        String fieldName = metadata.getColumnLabel(i);
        String dbColumnName = metadata.getColumnName(i);

        // not necessary for the result schema from the query statement
        boolean isKey = false;

        Field field = sqlType2Avro(size, scale, dbtype, nullable, fieldName, dbColumnName, null, isKey);

        fields.add(field);
    }

    return Schema.createRecord("DYNAMIC", null, null, false, fields);
}
 
Example 4
Source File: ResultSetUtil.java    From Mycat2 with GNU General Public License v3.0 6 votes vote down vote up
public static int toFlag(ResultSetMetaData metaData, int column)
		throws SQLException {

	int flags = 0;
	if (metaData.isNullable(column) == 1) {
		flags |= 1;
	}

	if (metaData.isSigned(column)) {
		flags |= 16;
	}

	if (metaData.isAutoIncrement(column)) {
		flags |= 128;
	}

	return flags;
}
 
Example 5
Source File: ClickHouseConverter.java    From clickhouse-jdbc-bridge with Apache License 2.0 6 votes vote down vote up
/**
 * Retrieves the columns DDL in format, readable by ClickHouse
 */
public String getColumnsDDL(ResultSetMetaData meta) throws SQLException {
    StringBuilder builder = new StringBuilder("columns format version: 1\n");

    builder.append(meta.getColumnCount());
    builder.append(" columns:\n");
    for (int i = 1; i <= meta.getColumnCount(); i++) {
        int precision = meta.getPrecision(i);
        int type = meta.getColumnType(i);
        int scale = meta.getScale(i);

        boolean nullable = ResultSetMetaData.columnNullable == meta.isNullable(i);
        builder.append(ClickHouseUtil.quoteIdentifier(meta.getColumnName(i)));
        builder.append(" ");
        builder.append(getBySQLType(type, precision, scale).getName(nullable,meta.getPrecision(i), meta.getScale(i)));
        builder.append('\n');
    }
    return builder.toString();
}
 
Example 6
Source File: PreparedStatementTest.java    From dremio-oss with Apache License 2.0 6 votes vote down vote up
boolean isEqualsTo(ResultSetMetaData metadata, int colNum) throws SQLException {
  return
      metadata.getCatalogName(colNum).equals(InfoSchemaConstants.IS_CATALOG_NAME) &&
      metadata.getSchemaName(colNum).isEmpty() &&
      metadata.getTableName(colNum).isEmpty() &&
      metadata.getColumnName(colNum).equals(columnName) &&
      metadata.getColumnLabel(colNum).equals(columnName) &&
      metadata.getColumnType(colNum) == type &&
      metadata.isNullable(colNum) == nullable &&
      // There is an existing bug where query results doesn't contain the precision for VARCHAR field.
      //metadata.getPrecision(colNum) == precision &&
      metadata.getScale(colNum) == scale &&
      metadata.isSigned(colNum) == signed &&
      metadata.getColumnDisplaySize(colNum) == displaySize &&
      metadata.getColumnClassName(colNum).equals(className) &&
      metadata.isSearchable(colNum) &&
      metadata.isAutoIncrement(colNum) == false &&
      metadata.isCaseSensitive(colNum) == false &&
      metadata.isReadOnly(colNum) &&
      metadata.isWritable(colNum) == false &&
      metadata.isDefinitelyWritable(colNum) == false &&
      metadata.isCurrency(colNum) == false;
}
 
Example 7
Source File: QueryHeaderBuilder.java    From shardingsphere with Apache License 2.0 6 votes vote down vote up
private static QueryHeader build(final ResultSetMetaData resultSetMetaData, final SchemaContext schema, final String columnName, final int columnIndex) throws SQLException {
    String schemaName = schema.getName();
    String actualTableName = resultSetMetaData.getTableName(columnIndex);
    Optional<DataNodeRoutedRule> dataNodeRoutedRule = schema.getSchema().getRules().stream().filter(each -> each instanceof DataNodeRoutedRule).findFirst().map(rule -> (DataNodeRoutedRule) rule);
    String tableName;
    boolean primaryKey;
    if (null != actualTableName && dataNodeRoutedRule.isPresent()) {
        tableName = dataNodeRoutedRule.get().findLogicTableByActualTable(actualTableName).orElse("");
        TableMetaData tableMetaData = schema.getSchema().getMetaData().getSchema().getConfiguredSchemaMetaData().get(tableName);
        primaryKey = null != tableMetaData && tableMetaData.getColumns().get(columnName.toLowerCase()).isPrimaryKey();
    } else {
        tableName = actualTableName;
        primaryKey = false;
    }
    String columnLabel = resultSetMetaData.getColumnLabel(columnIndex);
    int columnLength = resultSetMetaData.getColumnDisplaySize(columnIndex);
    Integer columnType = resultSetMetaData.getColumnType(columnIndex);
    int decimals = resultSetMetaData.getScale(columnIndex);
    boolean signed = resultSetMetaData.isSigned(columnIndex);
    boolean notNull = resultSetMetaData.isNullable(columnIndex) == ResultSetMetaData.columnNoNulls;
    boolean autoIncrement = resultSetMetaData.isAutoIncrement(columnIndex);
    return new QueryHeader(schemaName, tableName, columnLabel, columnName, columnLength, columnType, decimals, signed, primaryKey, notNull, autoIncrement);
}
 
Example 8
Source File: SelectStatementsTester.java    From spanner-jdbc with MIT License 5 votes vote down vote up
private void testSelect(String sql, Object... parameters) throws SQLException {
  PreparedStatement ps = connection.prepareStatement(sql);
  for (int i = 1; i <= parameters.length; i++) {
    ps.setObject(i, parameters[i - 1]);
  }
  try (ResultSet rs = ps.executeQuery()) {
    ResultSetMetaData metadata = rs.getMetaData();
    for (int i = 1; i <= metadata.getColumnCount(); i++) {
      metadata.getColumnClassName(i);
      metadata.getColumnDisplaySize(i);
      metadata.getColumnLabel(i);
      metadata.getColumnName(i);
      metadata.getColumnType(i);
      metadata.getColumnTypeName(i);
      metadata.getPrecision(i);
      metadata.getScale(i);
      metadata.getCatalogName(i);
      metadata.getSchemaName(i);
      metadata.getTableName(i);
      metadata.isNullable(i);
      metadata.isAutoIncrement(i);
      metadata.isCaseSensitive(i);
      metadata.isCurrency(i);
      metadata.isDefinitelyWritable(i);
      metadata.isReadOnly(i);
      metadata.isSearchable(i);
      metadata.isSigned(i);
      metadata.isWritable(i);
    }
    while (rs.next()) {
      // do nothing
    }
  }
}
 
Example 9
Source File: ResultSetLiveTest.java    From tutorials with MIT License 5 votes vote down vote up
@Test
public void givenDbConnectionF_whenRSMetaInfo_thenCorrect() throws SQLException {
    int columnCount = 0;
    try (PreparedStatement pstmt = dbConnection.prepareStatement("select * from employees", ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE); ResultSet rs = pstmt.executeQuery()) {
        ResultSetMetaData metaData = rs.getMetaData();
        columnCount = metaData.getColumnCount();
        for (int i = 1; i <= columnCount; i++) {
            String catalogName = metaData.getCatalogName(i);
            String className = metaData.getColumnClassName(i);
            String label = metaData.getColumnLabel(i);
            String name = metaData.getColumnName(i);
            String typeName = metaData.getColumnTypeName(i);
            Integer type = metaData.getColumnType(i);
            String tableName = metaData.getTableName(i);
            String schemaName = metaData.getSchemaName(i);
            boolean isAutoIncrement = metaData.isAutoIncrement(i);
            boolean isCaseSensitive = metaData.isCaseSensitive(i);
            boolean isCurrency = metaData.isCurrency(i);
            boolean isDefiniteWritable = metaData.isDefinitelyWritable(i);
            boolean isReadOnly = metaData.isReadOnly(i);
            boolean isSearchable = metaData.isSearchable(i);
            boolean isReadable = metaData.isReadOnly(i);
            boolean isSigned = metaData.isSigned(i);
            boolean isWritable = metaData.isWritable(i);
            int nullable = metaData.isNullable(i);
        }
    }

    assertEquals("column count", 4, columnCount);
}
 
Example 10
Source File: JDBCAvroRegistryString.java    From components with Apache License 2.0 5 votes vote down vote up
protected Schema inferSchemaResultSetMetaData(ResultSetMetaData metadata) throws SQLException {
    List<Field> fields = new ArrayList<>();

    Set<String> existNames = new HashSet<String>();
    int index = 0;
    
    int count = metadata.getColumnCount();
    for (int i = 1; i <= count; i++) {
        int size = metadata.getPrecision(i);
        int scale = metadata.getScale(i);
        boolean nullable = ResultSetMetaData.columnNullable == metadata.isNullable(i);

        int dbtype = metadata.getColumnType(i);
        String fieldName = metadata.getColumnLabel(i);
        String dbColumnName = metadata.getColumnName(i);

        // not necessary for the result schema from the query statement
        boolean isKey = false;

        String validName = NameUtil.correct(fieldName, index++, existNames);
        existNames.add(validName);
        
        Field field = sqlType2Avro(size, scale, dbtype, nullable, validName, dbColumnName, null, isKey);

        fields.add(field);
    }
    return Schema.createRecord("DYNAMIC", null, null, false, fields);
}
 
Example 11
Source File: VTIResultSet.java    From gemfirexd-oss with Apache License 2.0 5 votes vote down vote up
private boolean[] setNullableColumnList() throws SQLException, StandardException {

		if (runtimeNullableColumn != null)
			return runtimeNullableColumn;

		// Derby-style table functions return SQL rows which don't have not-null
		// constraints bound to them
		if ( isDerbyStyleTableFunction )
		{
		    int         count = getAllocatedRow().nColumns() + 1;
            
		    runtimeNullableColumn = new boolean[ count ];
		    for ( int i = 0; i < count; i++ )   { runtimeNullableColumn[ i ] = true; }
            
		    return runtimeNullableColumn;
		}

		if (userVTI == null)
			return null;

		ResultSetMetaData rsmd = userVTI.getMetaData();
		boolean[] nullableColumn = new boolean[rsmd.getColumnCount() + 1];
		for (int i = 1; i <  nullableColumn.length; i++) {
			nullableColumn[i] = rsmd.isNullable(i) != ResultSetMetaData.columnNoNulls;
		}

		return runtimeNullableColumn = nullableColumn;
	}
 
Example 12
Source File: PostgresDynamicResultSetOutputter.java    From sql-layer with GNU Affero General Public License v3.0 5 votes vote down vote up
protected static PostgresType typeFromSQL(ResultSetMetaData metaData, int columnIndex, TypesTranslator typesTranslator) throws SQLException {
    TypeId typeId = TypeId.getBuiltInTypeId(metaData.getColumnType(columnIndex));
    if (typeId == null) {
        try {
            typeId = TypeId.getUserDefinedTypeId(metaData.getColumnTypeName(columnIndex),
                                                 false);
        }
        catch (StandardException ex) {
            throw new SQLParserInternalException(ex);
        }
    }
    DataTypeDescriptor sqlType;
    if (typeId.isDecimalTypeId() || typeId.isNumericTypeId()) {
        sqlType = new DataTypeDescriptor(typeId,
                                         metaData.getPrecision(columnIndex),
                                         metaData.getScale(columnIndex),
                                         metaData.isNullable(columnIndex) != ResultSetMetaData.columnNoNulls,
                                         metaData.getColumnDisplaySize(columnIndex));
    }
    else {
        sqlType = new DataTypeDescriptor(typeId,
                                         metaData.isNullable(columnIndex) != ResultSetMetaData.columnNoNulls,
                                         metaData.getColumnDisplaySize(columnIndex));
    }
    TInstance type = typesTranslator.typeForSQLType(sqlType);
    return PostgresType.fromDerby(sqlType, type);
}
 
Example 13
Source File: ClickHouseResultSetTest.java    From clickhouse-jdbc with Apache License 2.0 5 votes vote down vote up
@Test
public void testClassNamesObjects() throws Exception {
    String testData = ClickHouseTypesTestData.buildTestString();
    ByteArrayInputStream is = new ByteArrayInputStream(testData.getBytes("UTF-8"));
    ResultSet rs = buildResultSet(is, testData.length(), "db", "table", false, null,
        TimeZone.getTimeZone("UTC"), props);
    rs.next();
    ResultSetMetaData meta = rs.getMetaData();
    for (int i = 1; i <= meta.getColumnCount(); i++) {
        String typeName = meta.getColumnTypeName(i);
        String className = null;
        try {
            className = meta.getColumnClassName(i);
        } catch (Exception e) {
            fail("Unable to determine class name for " + typeName, e);
        }
        Class<?> clazz = Class.forName(className);
        assertNotNull(
            clazz,
            "Class not available. class name: " + className + ", type name: " + typeName);
        Object o = rs.getObject(i);
        if (o == null && meta.isNullable(i) > 0) {
            continue;
        }
        assertNotNull(
            o,
            "Object null. class name: " + className + ", type name: " + typeName);
        assertTrue(
            clazz.isAssignableFrom(rs.getObject(i).getClass()),
            "Class mismatch. class name: " + className + ", type name: " + typeName +
                " object class: " + o.getClass().getCanonicalName());
    }
}
 
Example 14
Source File: ColumnInfo.java    From ymate-platform-v2 with Apache License 2.0 5 votes vote down vote up
public static Map<String, ColumnInfo> create(ConfigInfo configInfo, String dbType, String tableName, List<String> primaryKeys, DatabaseMetaData databaseMetaData, ResultSetMetaData metaData) throws SQLException {
    Map<String, ColumnInfo> _returnValue = new LinkedHashMap<String, ColumnInfo>(metaData.getColumnCount());
    //
    for (int _idx = 1; _idx <= metaData.getColumnCount(); _idx++) {
        // 获取字段元数据对象
        ResultSet _column = databaseMetaData.getColumns(configInfo.getDbName(),
                "oracle".equalsIgnoreCase(dbType) ? configInfo.getDbUserName().toUpperCase() : configInfo.getDbUserName(), tableName, metaData.getColumnName(_idx));
        if (_column.next()) {
            // 提取字段定义及字段默认值
            String _name = metaData.getColumnName(_idx).toLowerCase();
            ColumnInfo _columnInfo = new ColumnInfo(
                    configInfo.getNamedFilter(),
                    _name,
                    metaData.getColumnClassName(_idx),
                    metaData.isAutoIncrement(_idx),
                    primaryKeys.contains(_name),
                    metaData.isSigned(_idx),
                    metaData.getPrecision(_idx),
                    metaData.getScale(_idx),
                    metaData.isNullable(_idx),
                    _column.getString("COLUMN_DEF"),
                    _column.getString("REMARKS"));
            _returnValue.put(_name, _columnInfo);
        }
        _column.close();
    }
    return _returnValue;
}
 
Example 15
Source File: ResultColumnList.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/** 
 * Generate an RCL to match the contents of a ResultSetMetaData.
 * This is useful when dealing with VTIs.
 *
 * @param rsmd			The ResultSetMetaData.
 * @param tableName		The TableName for the BCNs.
 * @param javaClassName	The name of the VTI
 *
 * @exception StandardException			Thrown on error
 */
public void createListFromResultSetMetaData(ResultSetMetaData rsmd,
											TableName tableName,
											String javaClassName)
		throws StandardException
{
	try
	{
		// JDBC columns #s are 1-based
		// Check to make sure # of columns >= 1
		int numColumns = rsmd.getColumnCount();

		if (numColumns <= 0)
		{
			throw StandardException.newException(SQLState.LANG_INVALID_V_T_I_COLUMN_COUNT, 
								javaClassName, String.valueOf(numColumns));
		}

		for (int index = 1; index <= numColumns; index++)
		{
			boolean nullableResult = 
				(rsmd.isNullable(index) != ResultSetMetaData.columnNoNulls);

			TypeId cti;

			int jdbcColumnType = rsmd.getColumnType(index);

			switch (jdbcColumnType) {
			case Types.JAVA_OBJECT:
			case Types.OTHER:
			{
				cti = TypeId.getUserDefinedTypeId(rsmd.getColumnTypeName(index), false);
				break;
			}
			default:
			{
				cti = TypeId.getBuiltInTypeId(jdbcColumnType);
				break;
			}
			}

			// Handle the case where a VTI returns a bad column type
			if (cti == null)
			{
				throw StandardException.newException(SQLState.LANG_BAD_J_D_B_C_TYPE_INFO, Integer.toString(index));
			}

			// Get the maximum byte storage for this column
			int maxWidth;

			/* Get maximum byte storage from rsmd for variable
			 * width types, set it to MAXINT for the long types,
			 * otherwise get it from the TypeId
			 */
			if (cti.variableLength())
			{
				maxWidth = rsmd.getColumnDisplaySize(index);
			}
			else if (jdbcColumnType == Types.LONGVARCHAR ||
					 jdbcColumnType == Types.LONGVARBINARY)
			{
				maxWidth = Integer.MAX_VALUE;
			}
			else
			{
				maxWidth = 0;
			}

			int precision = cti.isDecimalTypeId() ? rsmd.getPrecision(index) : 0;
			int scale = cti.isDecimalTypeId() ? rsmd.getScale(index) : 0;
			DataTypeDescriptor dts = new DataTypeDescriptor(cti, 
										precision,
										scale, 
										nullableResult, 
										maxWidth);
			addColumn( tableName, rsmd.getColumnName(index), dts );
		}
	}
	catch (Throwable t)
	{
		if (t instanceof StandardException)
		{
			throw (StandardException) t;
		}
		else
		{
			throw StandardException.unexpectedUserException(t);
		}
	}
}
 
Example 16
Source File: ResultColumnList.java    From spliceengine with GNU Affero General Public License v3.0 4 votes vote down vote up
/**
 * Generate an RCL to match the contents of a ResultSetMetaData.
 * This is useful when dealing with VTIs.
 *
 * @param rsmd          The ResultSetMetaData.
 * @param tableName     The TableName for the BCNs.
 * @param javaClassName The name of the VTI
 * @throws StandardException Thrown on error
 */
public void createListFromResultSetMetaData(ResultSetMetaData rsmd,
                                            TableName tableName,
                                            String javaClassName) throws StandardException{
    try{
        // JDBC columns #s are 1-based
        // Check to make sure # of columns >= 1
        int numColumns=rsmd.getColumnCount();

        if(numColumns<=0){
            throw StandardException.newException(SQLState.LANG_INVALID_V_T_I_COLUMN_COUNT,
                    javaClassName,String.valueOf(numColumns));
        }

        for(int index=1;index<=numColumns;index++){
            boolean nullableResult=
                    (rsmd.isNullable(index)!=ResultSetMetaData.columnNoNulls);

            TypeId cti;

            int jdbcColumnType=rsmd.getColumnType(index);

            switch(jdbcColumnType){
                case Types.JAVA_OBJECT:
                case Types.OTHER:{
                    cti=TypeId.getUserDefinedTypeId(rsmd.getColumnTypeName(index),false);
                    break;
                }
                default:{
                    cti=TypeId.getBuiltInTypeId(jdbcColumnType);
                    break;
                }
            }

            // Handle the case where a VTI returns a bad column type
            if(cti==null){
                throw StandardException.newException(SQLState.LANG_BAD_J_D_B_C_TYPE_INFO,Integer.toString(index));
            }

            // Get the maximum byte storage for this column
            int maxWidth;

            /* Get maximum byte storage from rsmd for variable
             * width types, set it to MAXINT for the long types,
             * otherwise get it from the TypeId
             */
            if(cti.variableLength()){
                maxWidth=rsmd.getColumnDisplaySize(index);
            }else if(jdbcColumnType==Types.LONGVARCHAR || jdbcColumnType==Types.LONGVARBINARY){
                maxWidth=Integer.MAX_VALUE;
            }else{
                maxWidth=0;
            }

            int precision=cti.isDecimalTypeId()?rsmd.getPrecision(index):0;
            int scale=cti.isDecimalTypeId()?rsmd.getScale(index):0;
            DataTypeDescriptor dts=new DataTypeDescriptor(cti, precision, scale, nullableResult, maxWidth);
            addColumn(tableName,rsmd.getColumnName(index),dts);
        }
    }catch(Throwable t){
        if(t instanceof StandardException){
            throw (StandardException)t;
        }else{
            throw StandardException.unexpectedUserException(t);
        }
    }
}
 
Example 17
Source File: Foorm.java    From sakai with Educational Community License v2.0 4 votes vote down vote up
/**
 * 
 * @param table
 * @param formDefinition
 * @param vendor
 * @param md
 * @return
 */
public String[] formAdjustTable(String table, String[] formDefinition, String vendor, ResultSetMetaData md) {
	ArrayList<String> rv = new ArrayList<String>();

	for (String formField : formDefinition) {
		Properties info = parseFormString(formField);
		String field = info.getProperty("field", null);
		String type = info.getProperty("type", null);
		if ( "header".equals(type) ) continue;
		String maxs = adjustMax(info.getProperty("maxlength", null));
		int maxlength = 0;
		if (maxs != null) maxlength = (new Integer(maxs)).intValue();
		if (maxlength < 1) maxlength = 80;

		String sqlType = null;
		boolean autoIncrement = false;
		int sqlLength = -1;
		boolean isNullable = false;			
		try {
			for( int i = 1; i <= md.getColumnCount(); i++ ) {
				if ( field.equalsIgnoreCase(md.getColumnLabel(i)) ) {
					sqlLength = md.getColumnDisplaySize(i);
					autoIncrement = md.isAutoIncrement(i);
					sqlType = getSuperType(md.getColumnClassName(i));
					isNullable = (md.isNullable(i) == ResultSetMetaData.columnNullable);
					break;
				}
			}
		} catch(Exception e) {
			// ignore
		}

		log.debug("{} ({}) type={}", field, maxlength, type);
		log.debug("{} ({}) auto={} type={} null={}", field, sqlLength, autoIncrement, sqlType, isNullable);

		//  If the field is not there...
		if ( sqlType == null ) {
			if ( "oracle".equals(vendor) ) {
				rv.add("ALTER TABLE "+table+" ADD ( " + formSql(formField, vendor) + " )");
			} else if ( "mysql".equals(vendor) ) {
				rv.add("ALTER TABLE "+table+" ADD " + formSql(formField, vendor));
			} else {
				rv.add("ALTER TABLE "+table+" ADD COLUMN " + formSql(formField, vendor));
			}
			continue;
		}

		String ff = formSql(formField, vendor);

		// BLTI-220, BLTI-238 - Required will be enforced in software - not the DB
		boolean shouldAlter = false;
		if ("key".equals(type)) {
			if ( ! NUMBER_TYPE.equals(sqlType) ) log.warn("{} must be Integer and Auto Increment", field);
		} else if ("autodate".equals(type)) {
		} else if ("url".equals(type) || "text".equals(type) || "textarea".equals(type)) {
			if ( "oracle.sql.CLOB".equals(sqlType) || "oracle.jdbc.OracleClob".equals(sqlType) ) continue;  // CLOBS large enough :)
			if ( ! STRING_TYPE.equals(sqlType)) {
				log.warn("{} must be String field", field);
				continue;
			}
			if ( sqlLength < maxlength ) shouldAlter = true;
			if ( ! isNullable ) shouldAlter = true; // BLTI-220, BLTI-238

			// shouldAlter = true; // Temporary SAK-31695 to force ALTER statements to be emitted

		} else if ("radio".equals(type) || "checkbox".equals(type) || "integer".equals(type) ) {
			if ( NUMBER_TYPE.equals(sqlType)) continue;
			log.warn("{} must be Integer field", field);
		}

		if ( shouldAlter ) {
			if ( "oracle".equals(vendor) ) {
				rv.add("ALTER TABLE "+table+" MODIFY ( " + ff + " )");
			} else if ( "mysql".equals(vendor) ) {
				rv.add("ALTER TABLE "+table+" MODIFY " + ff);
			} else {
				rv.add("ALTER TABLE "+table+" ALTER COLUMN " + ff);
			}
		}
	}

	return rv.toArray(new String[rv.size()]);
}
 
Example 18
Source File: ResultColumnList.java    From gemfirexd-oss with Apache License 2.0 4 votes vote down vote up
/** 
 * Generate an RCL to match the contents of a ResultSetMetaData.
 * This is useful when dealing with VTIs.
 *
 * @param rsmd			The ResultSetMetaData.
 * @param tableName		The TableName for the BCNs.
 * @param javaClassName	The name of the VTI
 *
 * @exception StandardException			Thrown on error
 */
public void createListFromResultSetMetaData(ResultSetMetaData rsmd,
											TableName tableName,
											String javaClassName)
		throws StandardException
{
	try
	{
		// JDBC columns #s are 1-based
		// Check to make sure # of columns >= 1
		int numColumns = rsmd.getColumnCount();

		if (numColumns <= 0)
		{
			throw StandardException.newException(SQLState.LANG_INVALID_V_T_I_COLUMN_COUNT, 
								javaClassName, String.valueOf(numColumns));
		}

		for (int index = 1; index <= numColumns; index++)
		{
			boolean nullableResult = 
				(rsmd.isNullable(index) != ResultSetMetaData.columnNoNulls);

			TypeId cti;

			int jdbcColumnType = rsmd.getColumnType(index);

			switch (jdbcColumnType) {
			case Types.JAVA_OBJECT:
			case Types.OTHER:
			{
				cti = TypeId.getUserDefinedTypeId(rsmd.getColumnTypeName(index), false);
				break;
			}
			default:
			{
				cti = TypeId.getBuiltInTypeId(jdbcColumnType);
				break;
			}
			}

			// Handle the case where a VTI returns a bad column type
			if (cti == null)
			{
				throw StandardException.newException(SQLState.LANG_BAD_J_D_B_C_TYPE_INFO, Integer.toString(index));
			}

			// Get the maximum byte storage for this column
			int maxWidth;

			/* Get maximum byte storage from rsmd for variable
			 * width types, set it to MAXINT for the long types,
			 * otherwise get it from the TypeId
			 */
			if (cti.variableLength())
			{
				maxWidth = rsmd.getColumnDisplaySize(index);
			}
			else if (jdbcColumnType == Types.LONGVARCHAR ||
					 jdbcColumnType == Types.LONGVARBINARY)
			{
				maxWidth = Integer.MAX_VALUE;
			}
			else
			{
				maxWidth = 0;
			}

			int precision = cti.isDecimalTypeId() ? rsmd.getPrecision(index) : 0;
			int scale = cti.isDecimalTypeId() ? rsmd.getScale(index) : 0;
			DataTypeDescriptor dts = new DataTypeDescriptor(cti, 
										precision,
										scale, 
										nullableResult, 
										maxWidth);
			addColumn( tableName, rsmd.getColumnName(index), dts );
		}
	}
	catch (Throwable t)
	{
		if (t instanceof StandardException)
		{
			throw (StandardException) t;
		}
		else
		{
			throw StandardException.unexpectedUserException(t);
		}
	}
}
 
Example 19
Source File: DbCommonServiceImpl.java    From bdf3 with Apache License 2.0 4 votes vote down vote up
@Override
public List<ColumnInfo> findMultiColumnInfos(String dbInfoId, String sql) throws Exception {
	if (!StringUtils.hasText(sql)) {
		return null;
	}
	DataSource ds = this.getDataSourceByDbInfoId(dbInfoId);
	Connection conn = null;
	Statement st = null;
	ResultSet rs = null;
	try {
		conn = DataSourceUtils.getConnection(ds);
		st = conn.createStatement();
		rs = st.executeQuery(sql);
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		List<ColumnInfo> columnNames = new ArrayList<ColumnInfo>();
		ColumnInfo columnInfo = null;
		for (int i = 1; i <= count; i++) {
			columnInfo = new ColumnInfo();
			columnInfo.setColumnName(rsmd.getColumnLabel(i));
			columnInfo.setColumnType(rsmd.getColumnTypeName(i));
			columnInfo.setTableName(rsmd.getTableName(i));
			if (rsmd.getPrecision(i) > 0 && !columnInfo.getColumnType().equals("DATETIME") && !columnInfo.getColumnType().equals("TIMESTAMP") && !columnInfo.getColumnType().equals("DATE")) {
				columnInfo.setColumnSize(String.valueOf(rsmd.getPrecision(i)));
			}
			if (rsmd.getScale(i) > 0 && !columnInfo.getColumnType().equals("DATETIME") && !columnInfo.getColumnType().equals("TIMESTAMP") && !columnInfo.getColumnType().equals("DATE")) {
				columnInfo.setColumnSize(columnInfo.getColumnSize() + "," + rsmd.getScale(i));
			}
			int flagI = rsmd.isNullable(i);
			if (flagI == 0) {
				columnInfo.setIsnullAble(false);
			} else {
				columnInfo.setIsnullAble(true);
			}
			columnNames.add(columnInfo);
		}
		return columnNames;
	} finally {
		JdbcUtils.closeResultSet(rs);
		JdbcUtils.closeStatement(st);
		JdbcUtils.closeConnection(conn);
	}
}
 
Example 20
Source File: AnalyzeDB.java    From CloverETL-Engine with GNU Lesser General Public License v2.1 4 votes vote down vote up
/**
 *  Description of the Method
 *
 * @param  metadata          Description of Parameter
 * @param  fieldNo           Description of Parameter
 * @return                   Description of the Returned Value
 * @exception  SQLException  Description of Exception
 * @since                    September 25, 2002
 */
private static String dbMetadata2jetel(ResultSetMetaData metadata, int fieldNo, JdbcSpecific jdbcSpecific) throws SQLException {
	StringBuffer strBuf = new StringBuffer();
	char fieldType = jdbcSpecific.sqlType2jetel(metadata.getColumnType(fieldNo));

	// head
	strBuf.append("\t<Field name=\"");
	strBuf.append(metadata.getColumnName(fieldNo));

	// DATA TYPE
	strBuf.append("\" type=\"");
	try{
		strBuf.append(SQLUtil.jetelType2Str(fieldType));
	}catch(Exception ex){
		throw new RuntimeException("Field name " + metadata.getColumnName(fieldNo), ex);
	}
	
	strBuf.append("\"");
	// NULLABLE
	if (metadata.isNullable(fieldNo) == ResultSetMetaData.columnNullable) {
		strBuf.append(" nullable=\"yes\"");
	}
	// DELIMITER
	strBuf.append(" delimiter=\"");
	if (fieldNo == metadata.getColumnCount()) {
		strBuf.append("\\n");
		// last field by default delimited by NEWLINE character
	} else {
		strBuf.append(delimiter);
	}
	strBuf.append("\"");
	/*
	*  this is not safe - at least Oracle JDBC driver reports NUMBER to be currency
	* // FORMAT (in case of currency)
	* if (metadata.isCurrency(fieldNo)) {
	*	strBuf.append(" format=\"???#.#\"");
	* }
	*/

	// end
	strBuf.append(" />");

	return strBuf.toString();
}