java.sql.Types Java Examples

/**
     * Returns descriptors for the columns that shall be read from the result set when
     * reading the meta data for foreign keys originating from a table. Note that the
     * columns are read in the order defined by this list.<br/>
     * Redefine this method if you want more columns or a different order.
     * 
     * @return The map column name -> descriptor for the result set columns
     */
    protected List initColumnsForFK()
    {
        List result = new ArrayList();

        result.add(new MetaDataColumnDescriptor("PKTABLE_NAME",  Types.VARCHAR));
// GemStone changes BEGIN
        result.add(new MetaDataColumnDescriptor("PKTABLE_SCHEM",  Types.VARCHAR));
        result.add(new MetaDataColumnDescriptor("FKTABLE_SCHEM",  Types.VARCHAR));
// GemStone changes END
        // we're also reading the table name so that a model reader impl can filter manually
        result.add(new MetaDataColumnDescriptor("FKTABLE_NAME",  Types.VARCHAR));
        result.add(new MetaDataColumnDescriptor("KEY_SEQ",       Types.TINYINT, Short.valueOf((short)0)));
        result.add(new MetaDataColumnDescriptor("FK_NAME",       Types.VARCHAR));
        result.add(new MetaDataColumnDescriptor("UPDATE_RULE",   Types.TINYINT));
        result.add(new MetaDataColumnDescriptor("DELETE_RULE",   Types.TINYINT));
        result.add(new MetaDataColumnDescriptor("PKCOLUMN_NAME", Types.VARCHAR));
        result.add(new MetaDataColumnDescriptor("FKCOLUMN_NAME", Types.VARCHAR));

        return result;
    }
 

@Test
public void testEL() throws Exception {
  JdbcQueryExecutor queryExecutor = createExecutor("CREATE TABLE ${record:value('/table')} AS SELECT * FROM origin");

  ExecutorRunner runner = new ExecutorRunner.Builder(JdbcQueryDExecutor.class, queryExecutor)
    .setOnRecordError(OnRecordError.STOP_PIPELINE)
    .build();
  runner.runInit();

  Map<String, Field> map = new HashMap<>();
  map.put("table", Field.create("el"));

  Record record = RecordCreator.create();
  record.set(Field.create(map));

  runner.runWrite(ImmutableList.of(record));
  runner.runDestroy();

  assertTableStructure("el",
    new ImmutablePair("ID", Types.INTEGER),
    new ImmutablePair("NAME", Types.VARCHAR)
  );

  assertEquals(runner.getEventRecords().get(0).getEventType(), "successful-query");
}
 

private int normalizeType(int type, int size)
{
    switch (type)
    {
        case Types.CHAR:
            type = Types.VARCHAR;
            break;
        case Types.BIT:
        case Types.BOOLEAN:
        case Types.TINYINT:
            type = Types.SMALLINT;
            break;
        case Types.FLOAT:
            type = Types.DOUBLE;
            break;
    }
    return type;
}
 

private void createAddGatewayEventErrorHandlerProcedure(TransactionController tc,
    HashSet<?> newlyCreatedRoutines) throws StandardException {
  // procedure argument names
  String[] arg_names = { "FUNCTION_STR", "INIT_INFO_STR" };
  // procedure argument types
  TypeDescriptor[] arg_types = {
      DataTypeDescriptor.getCatalogType(Types.VARCHAR,
          Limits.DB2_VARCHAR_MAXWIDTH),
      DataTypeDescriptor.getCatalogType(Types.VARCHAR,
          Limits.DB2_VARCHAR_MAXWIDTH) };
  String methodName = "addGfxdGatewayEventErrorHandler(java.lang.String, java.lang.String)";
  String aliasName = "ATTACH_GATEWAY_EVENT_ERROR_HANDLER";
  this.createGfxdProcedure(methodName, getSystemSchemaDescriptor().getUUID(),
      arg_names, arg_types, 0, 0, RoutineAliasInfo.NO_SQL, null, tc,
      aliasName, CALLBACK_CLASSNAME, newlyCreatedRoutines, true);
}
 

/**
 * SQL 2005 allows nvarchar(max) as the output parameter of a stored
 * procedure. Test this functionality now.
 */
public void testNvarcharMaxOutput() throws Exception
{
   if( supportsTDS9() )
   {
      Statement stmt = con.createStatement();
      stmt.execute( "CREATE PROC #sp_test @in nvarchar(max), @out nvarchar(max) output as set @out = @in" );
      StringBuffer buf = new StringBuffer( 5000 );
      buf.append( '<' );
      for( int i = 0; i < 4000; i++ )
      {
         buf.append( 'X' );
      }
      buf.append( '>' );
      CallableStatement cstmt = con.prepareCall( "{call #sp_test(?,?)}" );
      cstmt.setString( 1, buf.toString() );
      cstmt.registerOutParameter( 2, Types.LONGVARCHAR );
      cstmt.execute();
      assertTrue( buf.toString().equals( cstmt.getString( 2 ) ) );
      cstmt.close();
      stmt.close();
   }
}
 

@Override
public void setBigDecimal(int parameterIndex, BigDecimal x) throws SQLException
{
  logger.debug("setBigDecimal(parameterIndex: {}, BigDecimal x)", parameterIndex);

  if (x == null)
  {
    setNull(parameterIndex, Types.DECIMAL);
  }
  else
  {
    ParameterBindingDTO binding = new ParameterBindingDTO(
        SnowflakeUtil.javaTypeToSFTypeString(Types.DECIMAL), String.valueOf(x));
    parameterBindings.put(String.valueOf(parameterIndex), binding);
  }
}
 

@Test
public void getObjectNonNull_typeBinary() throws SQLException {
    rowDescriptorBuilder.setType(ISCConstants.SQL_TEXT);
    rowDescriptorBuilder.setSubType(ISCConstants.CS_BINARY);
    fieldDescriptor = rowDescriptorBuilder.toFieldDescriptor();
    field = new FBBinaryField(fieldDescriptor, fieldData, Types.BINARY);

    final byte[] bytes = getRandomBytes();
    toReturnValueExpectations(bytes);

    Object value = field.getObject();

    assertThat(value, instanceOf(byte[].class));
    assertArrayEquals(bytes, (byte[]) value);
    assertNotSame("Expected a clone of the bytes", bytes, value);
}
 

/**
 * Test delete entities with where clause and parameters
 * 
 * @throws SQLException
 */
@Test
public void testDeleteWithWhereClauseAllShards() throws SQLException {
    String whereClause = "type=?";
    StatementParameters parameters = new StatementParameters();
    parameters.set(1, Types.SMALLINT, 1);

    DalHints hints = new DalHints();
    int res;

    // By allShards
    assertEquals(3, getCountByDb(dao, 0));
    assertEquals(3, getCountByDb(dao, 1));
    res = dao.delete(whereClause, parameters, new DalHints().inAllShards());
    assertResEquals(6, res);
    assertEquals(0, dao.query(whereClause, parameters, new DalHints().inAllShards()).size());
}
 

private void update(N node) {
    jdbcTemplate.update(
            getDiscriminatedQuery(
                    new Query("update :tableName set :left = ?, :right = ?, :level = ?, :parentId = ? where :id = ?").build()
            ),
            preparedStatement -> {
                preparedStatement.setObject(1, node.getTreeLeft());
                preparedStatement.setObject(2, node.getTreeRight());
                preparedStatement.setObject(3, node.getTreeLevel());
                if (node.getParentId() == null) {
                    preparedStatement.setNull(4, Types.OTHER);
                } else {
                    preparedStatement.setObject(4, node.getParentId());
                }
                preparedStatement.setObject(5, node.getId());
                setDiscriminatorParams(preparedStatement, 6);
            }
    );
}
 

public void testTableCreationWithMultipleStaticPartKeys() throws Exception {
  final int TOTAL_RECORDS = 1 * 10;
  String table = getTableName().toUpperCase();
  ColumnGenerator[] cols = new ColumnGenerator[] {
    HCatalogTestUtils.colGenerator(HCatalogTestUtils.forIdx(0),
      "varchar(20)", Types.VARCHAR, HCatFieldSchema.Type.VARCHAR, 20, 0,
      new HiveVarchar("1", 20), "1", KeyType.STATIC_KEY),
    HCatalogTestUtils.colGenerator(HCatalogTestUtils.forIdx(1),
      "varchar(20)", Types.VARCHAR, HCatFieldSchema.Type.VARCHAR, 20, 0,
      new HiveVarchar("2", 20), "2", KeyType.STATIC_KEY),
  };
  List<String> addlArgsArray = new ArrayList<String>();
  addlArgsArray.add("--hcatalog-partition-keys");
  addlArgsArray.add("col0,col1");
  addlArgsArray.add("--hcatalog-partition-values");
  addlArgsArray.add("1,2");
  addlArgsArray.add("--create-hcatalog-table");
  setExtraArgs(addlArgsArray);
  runHCatImport(addlArgsArray, TOTAL_RECORDS, table, cols, null, true, false);
}
 

static final Object setObject(int targetType, Date source) {
    switch (targetType) {

    case Types.DATE:
        return source;

    case Types.TIMESTAMP:
        return new Timestamp(source.getTime());

    case Types.CHAR:
    case Types.VARCHAR:
    case Types.LONGVARCHAR:
        return String.valueOf(source);

    default:
        throw new IllegalArgumentException("SQLState.LANG_DATA_TYPE_SET_MISMATCH java.sql.Date" + ClientTypes.getTypeString(targetType));
    }
}
 

@Test
public void testAddInvoiceProcWithoutMetaDataUsingMapParamSource() throws Exception {
	initializeAddInvoiceWithoutMetaData(false);
	SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withProcedureName("add_invoice");
	adder.declareParameters(
			new SqlParameter("amount", Types.INTEGER),
			new SqlParameter("custid", Types.INTEGER),
			new SqlOutParameter("newid",
			Types.INTEGER));
	Number newId = adder.executeObject(Number.class, new MapSqlParameterSource().
			addValue("amount", 1103).
			addValue("custid", 3));
	assertEquals(4, newId.intValue());
	verifyAddInvoiceWithoutMetaData(false);
	verify(connection, atLeastOnce()).close();
}
 

@Test
public void testHashCode() throws Exception {
    assertEquals(new JdbcColumnKey("col", 1, Types.ARRAY).hashCode(), new JdbcColumnKey("col", 1, Types.ARRAY).hashCode());
    assertNotEquals(new JdbcColumnKey("col", 1, Types.ARRAY).hashCode(), new JdbcColumnKey("col", 1, Types.VARCHAR).hashCode());
    assertNotEquals(new JdbcColumnKey("col", 1, Types.ARRAY).hashCode(), new JdbcColumnKey("col", 2, Types.ARRAY).hashCode());
    assertNotEquals(new JdbcColumnKey("col", 1, Types.ARRAY).hashCode(), new JdbcColumnKey("col1", 1, Types.ARRAY).hashCode());
}
 

static final void appendJdbcTypeMappingQuery(StringBuilder buf, String mysqlTypeColumnName) {

        buf.append("CASE ");
        Map<String, Integer> typesMap = new HashMap<String, Integer>();
        typesMap.putAll(mysqlToJdbcTypesMap);
        typesMap.put("BINARY", Integer.valueOf(Types.BINARY));
        typesMap.put("VARBINARY", Integer.valueOf(Types.VARBINARY));

        Iterator<String> mysqlTypes = typesMap.keySet().iterator();

        while (mysqlTypes.hasNext()) {
            String mysqlTypeName = mysqlTypes.next();
            buf.append(" WHEN UPPER(");
            buf.append(mysqlTypeColumnName);
            buf.append(")='");
            buf.append(mysqlTypeName);
            buf.append("' THEN ");
            buf.append(typesMap.get(mysqlTypeName));

            if (mysqlTypeName.equalsIgnoreCase("DOUBLE") || mysqlTypeName.equalsIgnoreCase("FLOAT") || mysqlTypeName.equalsIgnoreCase("DECIMAL")
                    || mysqlTypeName.equalsIgnoreCase("NUMERIC")) {
                buf.append(" WHEN ");
                buf.append(mysqlTypeColumnName);
                buf.append("='");
                buf.append(mysqlTypeName);
                buf.append(" UNSIGNED' THEN ");
                buf.append(typesMap.get(mysqlTypeName));
            }
        }

        buf.append(" ELSE ");
        buf.append(Types.OTHER);
        buf.append(" END ");
    }
 

@Test
public void setStringNull() throws SQLException {
    fieldDescriptor = createIntegerFieldDescriptor(-1);
    field = new FBBigDecimalField(fieldDescriptor, fieldData, Types.NUMERIC);
    setNullExpectations();
    
    field.setString(null);
}
 

@Before
@Override
public void setUp() throws Exception {
    super.setUp();

    rowDescriptorBuilder.setType(ISCConstants.SQL_FLOAT);
    fieldDescriptor = rowDescriptorBuilder.toFieldDescriptor();
    field = new FBFloatField(fieldDescriptor, fieldData, Types.FLOAT);
}
 

@Test
public void getIntTooHigh() throws SQLException {
    expectedException.expect(TypeConversionException.class);
    fieldDescriptor = createLongFieldDescriptor(-2);
    field = new FBBigDecimalField(fieldDescriptor, fieldData, Types.NUMERIC);
    toReturnLongExpectations((Integer.MAX_VALUE + 1L) * 100);
    
    field.getInt();
}
 

protected void appendValue(StringBuffer str, Object value, int type) {
	switch (type){
	case Types.BIGINT:
	case Types.DECIMAL:
	case Types.NUMERIC:
	case Types.SMALLINT:
	case Types.TINYINT:
	case Types.INTEGER:
	case Types.FLOAT:
	case Types.DOUBLE:
	case Types.REAL:
	case Types.TIMESTAMP:
		str.append(value + ",");
		break;
	default:
		str.append("'" + value +"',");
	}
}
 

private void testCommonSplitterTypes(
    DataDrivenDBInputFormat<NullDBWritable> format) {
  assertEquals(BigDecimalSplitter.class, format.getSplitter(Types.DECIMAL)
      .getClass());
  assertEquals(BigDecimalSplitter.class, format.getSplitter(Types.NUMERIC)
      .getClass());
  assertEquals(BooleanSplitter.class, format.getSplitter(Types.BOOLEAN)
      .getClass());
  assertEquals(BooleanSplitter.class, format.getSplitter(Types.BIT)
      .getClass());
  assertEquals(IntegerSplitter.class, format.getSplitter(Types.BIGINT)
      .getClass());
  assertEquals(IntegerSplitter.class, format.getSplitter(Types.TINYINT)
      .getClass());
  assertEquals(IntegerSplitter.class, format.getSplitter(Types.SMALLINT)
      .getClass());
  assertEquals(IntegerSplitter.class, format.getSplitter(Types.INTEGER)
      .getClass());
  assertEquals(FloatSplitter.class, format.getSplitter(Types.DOUBLE)
      .getClass());
  assertEquals(FloatSplitter.class, format.getSplitter(Types.REAL).getClass());
  assertEquals(FloatSplitter.class, format.getSplitter(Types.FLOAT)
      .getClass());
  assertEquals(TextSplitter.class, format.getSplitter(Types.LONGVARCHAR)
      .getClass());
  assertEquals(TextSplitter.class, format.getSplitter(Types.CHAR).getClass());
  assertEquals(TextSplitter.class, format.getSplitter(Types.VARCHAR)
      .getClass());
  // if unknown data type splitter is null
  assertNull(format.getSplitter(Types.BINARY));
}
 

/**
 * Tests NullIf with unary minus and unary plus
 * @throws SQLException
 */
public void testNullIfWithUnaryMinusAndPlus() throws SQLException{
  PreparedStatement ps = prepareStatement("select nullif(-?,c11) from t1");
  try {
    ps.setInt(1,22);
    int[] expectedTypes= new int[]{Types.INTEGER};
    JDBC.assertParameterTypes(ps,expectedTypes);
    Object[][] expectedRows = new Object[][]{{new String("-22")},{new String("-22")}};
    JDBC.assertFullResultSet(ps.executeQuery(), expectedRows, true);
  } finally {
    ps.close();
  }
}
 

@Override
public String getIdentitySelectString(String table, String column, int type)
		throws MappingException {
	return type == Types.BIGINT
			? "select dbinfo('serial8') from informix.systables where tabid=1"
			: "select dbinfo('sqlca.sqlerrd1') from informix.systables where tabid=1";
}
 

/**
 * Tests a database model with a table with a column with a size spec.
 */
public void testColumnWithSize2() throws Exception
{
    Database model = readModel(
        "<database xmlns='" + DatabaseIO.DDLUTILS_NAMESPACE + "' name='test'>\n" +
        "  <table name='SomeTable'\n" +
        "         description='Some table'>\n" +
        "    <column name='ID'\n" +
        "            type='DECIMAL'\n" +
        "            size='10,3'/>\n" +
        "  </table>\n" +
        "</database>");

    assertEquals("test", model.getName());
    assertEquals(1, model.getTableCount());
    
    Table table = model.getTable(0);

    assertEquals("SomeTable", "Some table", 1, 0, 0, 0, 0,
                 table);
    assertEquals("ID", Types.DECIMAL, 10, 3, null, null, null, false, false, false,
                 table.getColumn(0));

    assertEquals(
        "<?xml version='1.0' encoding='UTF-8'?>\n" +
        "<database xmlns=\"" + DatabaseIO.DDLUTILS_NAMESPACE + "\" name=\"test\">\n" +
        "  <table name=\"SomeTable\" description=\"Some table\">\n" +
        "    <column name=\"ID\" primaryKey=\"false\" required=\"false\" type=\"DECIMAL\" size=\"10,3\" autoIncrement=\"false\" />\n" +
        "  </table>\n" +
        "</database>\n",
        model);
}
 

public synchronized void update(MonitoredPage client) throws SQLException {
    if (client.getHistoryReference() != null) {
        psUpdate.setInt(1, client.getHistoryReference().getHistoryId());
        client.setHrefPersisted(true);
    } else {
        psUpdate.setNull(1, Types.INTEGER);
    }
    psUpdate.setLong(2, client.getIndex());
    psUpdate.executeUpdate();
}
 

@Test
public void testOptionConvertersOverridesRegisteredTypeConverter() {
    CommandSpec spec = CommandSpec.create();
    spec.addOption(OptionSpec.builder("-c", "--count").paramLabel("COUNT").arity("1").type(int.class).description("number of times to execute").build());
    spec.addOption(OptionSpec.builder("-s", "--sql").paramLabel("SQLTYPE").type(int.class).converters(
            new TypeConversionTest.SqlTypeConverter()).description("sql type converter").build());
    CommandLine commandLine = new CommandLine(spec);
    commandLine.parseArgs("-c", "33", "-s", "BLOB");
    assertEquals(Integer.valueOf(33), spec.optionsMap().get("-c").getValue());
    assertEquals(Integer.valueOf(Types.BLOB), spec.optionsMap().get("-s").getValue());
}
 

private Object getValue(Row row, ReplicatorColumnMeta meta, boolean isBlobRow) {
    Object result = row.get(meta.hcolumnPos);
    if ((result == null) && isBlobRow) {
        return null;
    }
    // special logic to handle mysql 0000-00-00 00:00
    if (result==null && meta.dataType==Types.TIMESTAMP && meta.nullable==DatabaseMetaData.columnNoNulls) {
        if ("0000-00-00 00:00:00".equals(meta.defaultValue)) {
            result = "0000-00-00 00:00:00";
        }
    }
    if (result==null && meta.dataType==Types.DATE && meta.nullable==DatabaseMetaData.columnNoNulls) {
        if ("0000-00-00".equals(meta.defaultValue)) {
            result = "0000-00-00";
        }
    }
    if (result instanceof Date && ((Date)result).getTime() == Long.MIN_VALUE) {
        result = "0000-00-00";
    }
    if (result instanceof Timestamp && ((Timestamp)result).getTime() == Long.MIN_VALUE) {
        result = "0000-00-00 00:00:00";
    }
    if (result instanceof Duration) {
        result = UberFormatter.duration((Duration)result);
    }
    return result;
}
 

@Test
public void setBigDecimalLong() throws SQLException {
    fieldDescriptor = createLongFieldDescriptor(-5);
    field = new FBBigDecimalField(fieldDescriptor, fieldData, Types.NUMERIC);
    setLongExpectations(1234567890123L);
    
    field.setBigDecimal(new BigDecimal("12345678.90123"));
}
 

@Test
public void test2() throws SQLException {
	String testString = "Bla";
	SqlLobValue lob = new SqlLobValue(testString, handler);
	lob.setTypeValue(preparedStatement, 1, Types.BLOB, "test");
	verify(creator).setBlobAsBytes(preparedStatement, 1, testString.getBytes());
}
 

public OraOopConnManager(final SqoopOptions sqoopOptions) {
  super(OraOopConstants.ORACLE_JDBC_DRIVER_CLASS, sqoopOptions);
  if (this.options.getConf().getBoolean(
      OraOopConstants.ORAOOP_MAP_TIMESTAMP_AS_STRING,
      OraOopConstants.ORAOOP_MAP_TIMESTAMP_AS_STRING_DEFAULT)) {
    timestampJavaType = "String";
  } else {
    timestampJavaType = super.toJavaType(Types.TIMESTAMP);
  }
}
 

/**
 * Test Query range of result with where clause when return empty collection
 * 
 * @throws SQLException
 */
@Test
public void testQueryFromWithWhereClauseEmpty() throws SQLException {
    String whereClause = "type=? order by id";
    List<ClientTestModel> models = null;
    for (int i = 0; i < mod; i++) {
        StatementParameters parameters = new StatementParameters();
        parameters.set(1, Types.SMALLINT, 10);

        // By tabelShard
        models = dao.queryFrom(whereClause, parameters, new DalHints().inTableShard(i), 0, 10);
        assertTrue(null != models);
        assertEquals(0, models.size());

        // By tableShardValue
        models = dao.queryFrom(whereClause, parameters, new DalHints().setTableShardValue(i), 0, 10);
        assertTrue(null != models);
        assertEquals(0, models.size());

        // By shardColValue
        models = dao.queryFrom(whereClause, parameters, new DalHints().setShardColValue("index", i), 0, 10);
        assertTrue(null != models);
        assertEquals(0, models.size());

        // By shardColValue
        models = dao.queryFrom(whereClause, parameters, new DalHints().setShardColValue("tableIndex", i), 0, 10);
        assertTrue(null != models);
        assertEquals(0, models.size());
    }
}
 

private void migrateTemplateSwiftRef(Connection conn, Map<Long, Long> swiftStoreMap) {
    s_logger.debug("Updating template_store_ref table from template_swift_ref table");
    try (
            PreparedStatement tmplStoreInsert =
                conn.prepareStatement("INSERT INTO `cloud`.`template_store_ref` (store_id,  template_id, created, download_pct, size, physical_size, download_state, local_path, install_path, update_count, ref_cnt, store_role, state) values(?, ?, ?, 100, ?, ?, 'DOWNLOADED', '?', '?', 0, 0, 'Image', 'Ready')");
            PreparedStatement s3Query = conn.prepareStatement("select swift_id, template_id, created, path, size, physical_size from `cloud`.`template_swift_ref`");
            ResultSet rs = s3Query.executeQuery();
        ) {
        while (rs.next()) {
            Long swift_id = rs.getLong("swift_id");
            Long tmpl_id = rs.getLong("template_id");
            Date created = rs.getDate("created");
            String path = rs.getString("path");
            Long size = rs.getObject("size") != null ? rs.getLong("size") : null;
            Long psize = rs.getObject("physical_size") != null ? rs.getLong("physical_size") : null;

            tmplStoreInsert.setLong(1, swiftStoreMap.get(swift_id));
            tmplStoreInsert.setLong(2, tmpl_id);
            tmplStoreInsert.setDate(3, created);
            if (size != null) {
                tmplStoreInsert.setLong(4, size);
            } else {
                tmplStoreInsert.setNull(4, Types.BIGINT);
            }
            if (psize != null) {
                tmplStoreInsert.setLong(5, psize);
            } else {
                tmplStoreInsert.setNull(5, Types.BIGINT);
            }
            tmplStoreInsert.setString(6, path);
            tmplStoreInsert.setString(7, path);
            tmplStoreInsert.executeUpdate();
        }
    } catch (SQLException e) {
        String msg = "Unable to migrate template_swift_ref." + e.getMessage();
        s_logger.error(msg);
        throw new CloudRuntimeException(msg, e);
    }
    s_logger.debug("Completed migrating template_swift_ref table.");
}