Java Code Examples for java.sql.PreparedStatement#setBytes()

The following examples show how to use java.sql.PreparedStatement#setBytes() . 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 want to check out the right sidebar which shows the related API usage.
Example 1
@Test
public void testStringConcatExpression() throws Exception {
    long ts = nextTimestamp();
    String query = "SELECT entity_id,a_string FROM atable where 2 || a_integer || ? like '2%'";
    String url = getUrl() + ";" + PhoenixRuntime.CURRENT_SCN_ATTRIB + "=" + (ts + 5); // Run query at timestamp 5
    Properties props = new Properties(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(url, props);
    byte []x=new byte[]{127,127,0,0};//Binary data
    try {
        PreparedStatement statement = conn.prepareStatement(query);
        statement.setBytes(1, x);
        statement.executeQuery();
        fail();
    } catch (SQLException e) { // expected
        assertTrue(e.getMessage().contains("Concatenation does not support"));
    } finally {
        conn.close();
    }
}
 
Example 2
public void setParameter(PreparedStatement ps, int i, Object parameter, JdbcType jdbcType) throws SQLException
{
    if (parameter == null)
    {
        ps.setNull(i, Types.BINARY);
    }
    else
    {
        try
        {
            ps.setBytes(i, (byte[])parameter);
        }
        catch (Throwable e)
        {
            throw new SerializationException(e);
        }
    }
}
 
Example 3
Source Project: gemfirexd-oss   File: BlobTest.java    License: Apache License 2.0 6 votes vote down vote up
public static void insertData_43623(final Connection conn,
    final byte[] jobData) throws SQLException {
  PreparedStatement pstmt = conn.prepareStatement("insert into "
      + "QUARTZ_TRIGGERS(TRIGGER_NAME, TRIGGER_GROUP, TRIGGER_STATE, "
      + "JOB_NAME, JOB_GROUP, JOB_DATA) values(?, ?, ?, ?, ?, ?)");
  AvailablePort.rand.nextBytes(jobData);
  final int numRows = 10;
  for (int cnt = 1; cnt <= numRows; cnt++) {
    pstmt.setString(1, "trig" + cnt);
    pstmt.setString(2, "grp" + cnt);
    pstmt.setString(3, "st" + cnt);
    pstmt.setString(4, "job" + cnt);
    pstmt.setString(5, "jgrp" + cnt);
    pstmt.setBytes(6, jobData);
    pstmt.execute();
  }
}
 
Example 4
/***********************************************************************************************
        * All the methods below this line are used by fixtures
        **********************************************************************************************/
private void insertEL(PreparedStatement psi, PreparedStatement pss, PreparedStatement psd, int length, String expectedValue) throws SQLException, IOException 
{
               byte[] data = new byte[length];

	// random simple value check
	int off = (int)  (System.currentTimeMillis() % ((long) length));
	data[off] = 0x23;

	psi.setBytes(1, (length <= 32672) ? data : null);
	psi.setBytes(2, (length <= 32700) ? data : null);
	psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data), length); // BLOB column

	psi.executeUpdate();
	selectData(pss,data,off,length, expectedValue);
	psd.executeUpdate();

	// Set values using stream and then verify that select is successful
	psi.setBinaryStream(1, (length <= 32672) ? new java.io.ByteArrayInputStream(data) : null, length);
	psi.setBinaryStream(2, (length <= 32700) ? new java.io.ByteArrayInputStream(data) : null, length);
	psi.setBinaryStream(3, new java.io.ByteArrayInputStream(data), length); // BLOB column
	psi.executeUpdate();

	selectData(pss,data,off,length, expectedValue);
	psd.executeUpdate();
}
 
Example 5
public void fillParameters(Object stm)
			throws SQLException
	{
		int i = 1;
		PreparedStatement pstm = ( PreparedStatement )stm;
		if( iAssignments != null )
		{
			for( Assignment a: iAssignments )
			{
				a.getOperand().setBackend( getBackend() );
//				a.getOperand().setUnicode( unicode );
				if( a.getOperand().getInnerObject() instanceof byte[] )
				{
					pstm.setBytes( i, ( byte[] )a.getOperand().getInnerObject() );
					++i;
				}
			}
		}
	}
 
Example 6
Source Project: ignite   File: JdbcCheckpointSpi.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Creates checkpoint.
 *
 * @param conn Active database connection.
 * @param key Checkpoint key.
 * @param state Checkpoint data.
 * @param expTime Checkpoint expire time.
 * @return Number of rows affected by query.
 * @throws SQLException Thrown in case of any errors.
 */
private int createCheckpoint(Connection conn, String key, byte[] state, Time expTime) throws SQLException {
    PreparedStatement st = null;

    try {
        st = conn.prepareStatement(insSql);

        st.setString(1, key);
        st.setBytes(2, state);
        st.setTime(3, expTime);

        return st.executeUpdate();
    }
    finally {
        U.close(st, log);
    }
}
 
Example 7
/**
 * Test setNull() on Clob/Blob using Varchar/binary types
 *
 * @exception SQLException if error occurs
 */
public void testNullClobBlob() throws SQLException {
    
    byte[] b2 = new byte[1];
    b2[0] = (byte)64;
    
    PreparedStatement pStmt = prepareStatement("insert into ClobBlob values (?,?)");
    
    pStmt.setNull(1, Types.VARCHAR);
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.setNull(1, Types.VARCHAR,"");
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.close();
    
    Statement stmt1 = createStatement();
    ResultSet rs = stmt1.executeQuery("select * from ClobBlob");
    String [][]  expectedRows = new String[][] { { null, bytesToString(b2) },
    { null, bytesToString(b2) } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs.close();
    
    stmt1.close();
}
 
Example 8
Source Project: gemfirexd-oss   File: PrepStmtNullTest.java    License: Apache License 2.0 6 votes vote down vote up
/**
 * Test setNull() on Clob/Blob using Varchar/binary types
 *
 * @exception SQLException if error occurs
 */
public void testNullClobBlob() throws SQLException {

    byte[] b2 = new byte[1];
    b2[0] = (byte)64;

    PreparedStatement pStmt = prepareStatement("insert into ClobBlob values (?,?)");

    pStmt.setNull(1, Types.VARCHAR);
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.setNull(1, Types.VARCHAR,"");
    pStmt.setBytes(2, b2);
    pStmt.execute();
    pStmt.close();

    Statement stmt1 = createStatement();
    ResultSet rs = stmt1.executeQuery("select * from ClobBlob");
    String [][]  expectedRows = new String[][] { { null, bytesToString(b2) },
    { null, bytesToString(b2) } };
    JDBC.assertFullResultSet(rs, expectedRows);
    rs.close();

    stmt1.close();
}
 
Example 9
Source Project: redesocial   File: ArtigoDAO.java    License: MIT License 6 votes vote down vote up
/**
 * Método para inserir um artigo no banco de dados
 * @param dto identificador de artigo
 * @author Davi de Faria
 * @throws Exception Possíveis exceções que podem acontecer
 */
@Override
public void inserir(Artigo dto) throws Exception {
    Connection conexao = getConexao();
    
    if (dto.getArtigo().equals("")){
        throw new Exception("O arquivo do artigo não pode estar vazio!");
    }
    
    PreparedStatement pstmt = conexao.prepareStatement("insert into artigos (idioma, revista, issn, data, area_conhecimento, titulo, resumo, url, artigo, categoria) values (?,?,?,?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);
    
    pstmt.setString(1, dto.getIdioma());
    pstmt.setString(2, dto.getRevista());
    pstmt.setString(3, dto.getISSN());
    pstmt.setTimestamp(4, new java.sql.Timestamp(dto.getData().getTime()));
    pstmt.setString(5, dto.getAreaConhecimento());
    pstmt.setString(6, dto.getTitulo());
    pstmt.setString(7, dto.getResumo());
    pstmt.setString(8, dto.getURL());
    pstmt.setBytes(9, dto.getArtigo());
    pstmt.setInt(10, dto.getCategoria().getId());
    
    pstmt.executeUpdate();
    
    dto.setId(getId(pstmt));
}
 
Example 10
Source Project: phoenix   File: BinaryRowKeyIT.java    License: Apache License 2.0 6 votes vote down vote up
@Test
public void testInsertPaddedBinaryValue() throws SQLException {
    Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES);
    Connection conn = DriverManager.getConnection(getUrl(), props);
    try {
        initTableValues();
        conn.setAutoCommit(true);
        conn.createStatement().execute("DELETE FROM test_table");
       
        String query = "UPSERT INTO test_table"
                + "(a_binary, a_string) "
                + "VALUES(?,?)";
        PreparedStatement stmt = conn.prepareStatement(query);
        stmt.setBytes(1, new byte[] {0,0,0,0,0,0,0,0,1});
        stmt.setString(2, "a");
        stmt.execute();
        
        ResultSet rs = conn.createStatement().executeQuery("SELECT a_string FROM test_table");
        assertTrue(rs.next());
        assertEquals("a",rs.getString(1));
        assertFalse(rs.next());
    } finally {
        conn.close();
    }
}
 
Example 11
@Override
public void persist(String exchangeName, Binding binding) throws BrokerException {
    Connection connection = null;
    PreparedStatement statement = null;
    try {
        connection = getConnection();
        statement = connection.prepareStatement(RDBMSConstants.PS_INSERT_BINDING);
        statement.setString(1, exchangeName);
        statement.setString(2, binding.getQueue().getName());
        statement.setString(3, binding.getBindingPattern());
        FieldTable arguments = binding.getArguments();
        byte[] bytes = new byte[(int) arguments.getSize()];
        ByteBuf byteBuf = Unpooled.wrappedBuffer(bytes);
        byteBuf.resetWriterIndex();
        arguments.write(byteBuf);
        statement.setBytes(4, bytes);

        statement.executeUpdate();
        connection.commit();
    } catch (SQLException e) {
        String message = "Error occurred while storing binding " + binding;
        rollback(connection, message);
        throw new BrokerException(message, e);
    } finally {
        close(connection, statement);
    }
}
 
Example 12
public void set(PreparedStatement st, Object value, int index) throws HibernateException, SQLException {
	byte[] internalValue = toInternalFormat( value );
	if ( Environment.useStreamsForBinary() ) {
		st.setBinaryStream( index, new ByteArrayInputStream( internalValue ), internalValue.length );
	}
	else {
		st.setBytes( index, internalValue );
	}
}
 
Example 13
@Test
public void testSaltedVarbinaryUpperBoundQuery() throws Exception {
    String tableName = generateUniqueName();
    String ddl = "CREATE TABLE " + tableName +
            " ( k VARBINARY PRIMARY KEY, a INTEGER ) SALT_BUCKETS = 3";
    String dml = "UPSERT INTO " + tableName + " values (?, ?)";
    String sql2 = "SELECT * FROM " + tableName + " WHERE k = ?";

    try (Connection conn = DriverManager.getConnection(getUrl())) {
        conn.createStatement().execute(ddl);
        PreparedStatement stmt = conn.prepareStatement(dml);
        stmt.setInt(2, 1);

        stmt.setBytes(1, new byte[] { 5 });
        stmt.executeUpdate();
        stmt.setBytes(1, new byte[] { 5, 0 });
        stmt.executeUpdate();
        stmt.setBytes(1, new byte[] { 5, 1 });
        stmt.executeUpdate();
        stmt.close();
        conn.commit();

        stmt = conn.prepareStatement(sql2);
        stmt.setBytes(1, new byte[] { 5 });
        ResultSet rs = stmt.executeQuery();

        assertTrue(rs.next());
        assertArrayEquals(new byte[] {5},rs.getBytes(1));
        assertEquals(1,rs.getInt(2));
        assertFalse(rs.next());
        stmt.close();
    }
}
 
Example 14
Source Project: pinlater   File: PinLaterMySQLBackend.java    License: Apache License 2.0 4 votes vote down vote up
@Override
protected String enqueueSingleJob(String queueName, PinLaterJob job, int numAutoRetries)
    throws Exception {
  final long currentTimeMillis = System.currentTimeMillis();
  Connection conn = null;
  PreparedStatement stmt = null;
  ResultSet rs = null;
  final ImmutableMap.Entry<String, MySQLDataSources> shard = getRandomEnqueueableShard();
  try {
    conn = shard.getValue().getGeneralDataSource().getConnection();
    String jobsTableName =
        MySQLBackendUtils.constructJobsTableName(queueName, shard.getKey(), job.getPriority());
    stmt = conn.prepareStatement(
        String.format(MySQLQueries.ENQUEUE_INSERT, jobsTableName),
        Statement.RETURN_GENERATED_KEYS);
    stmt.setInt(1, PinLaterJobState.PENDING.getValue());
    stmt.setInt(2, job.getNumAttemptsAllowed());
    stmt.setInt(3, job.getNumAttemptsAllowed());
    stmt.setString(4, job.getCustomStatus());
    stmt.setTimestamp(5, new Timestamp(currentTimeMillis));
    stmt.setTimestamp(6, new Timestamp(job.isSetRunAfterTimestampMillis()
                                       ? job.getRunAfterTimestampMillis() : currentTimeMillis));
    stmt.setBytes(7, job.getBody());
    stmt.executeUpdate();
    rs = stmt.getGeneratedKeys();
    rs.next();
    return new PinLaterJobDescriptor(
        queueName, shard.getKey(), job.getPriority(), rs.getLong(1)).toString();
  } catch (SQLException e) {
    boolean shouldRetry = checkExceptionIsRetriable(e, shard.getKey(), "enqueue");
    if (shouldRetry && numAutoRetries > 0) {
      // Retry the enqueue, potentially on a different shard.
      Stats.incr("enqueue-failures-retry");
      return enqueueSingleJob(queueName, job, numAutoRetries - 1);
    }
    // Out of retries, throw the exception. Wrap it into a PinLaterException if the exception
    // is recognized and return the appropriate error code.
    if (MySQLBackendUtils.isDatabaseDoesNotExistException(e)) {
      throw new PinLaterException(ErrorCode.QUEUE_NOT_FOUND, "Queue not found: " + queueName);
    }
    throw e;
  } finally {
    JdbcUtils.closeResultSet(rs);
    JdbcUtils.closeStatement(stmt);
    JdbcUtils.closeConnection(conn);
  }
}
 
Example 15
@Override
public List<Integer> addApplications(List<Application> applications,
                                     int tenantId) throws DeviceManagementDAOException {
    Connection conn;
    PreparedStatement stmt = null;
    ResultSet rs;
    List<Integer> applicationIds = new ArrayList<>();
    try {
        conn = this.getConnection();
        stmt = conn.prepareStatement("INSERT INTO DM_APPLICATION (NAME, PLATFORM, " +
                "CATEGORY, VERSION, TYPE, LOCATION_URL, IMAGE_URL, TENANT_ID,APP_PROPERTIES, " +
                "APP_IDENTIFIER, MEMORY_USAGE, IS_ACTIVE) " +
                "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", new String[]{"id"});

        for (Application application : applications) {

            stmt.setString(1, application.getName());
            stmt.setString(2, application.getPlatform());
            stmt.setString(3, application.getCategory());
            stmt.setString(4, application.getVersion());
            stmt.setString(5, application.getType());
            stmt.setString(6, application.getLocationUrl());
            stmt.setString(7, application.getImageUrl());
            stmt.setInt(8, tenantId);

            // Removing the application properties saving from the application table.
            stmt.setBytes(9, null);

            stmt.setString(10, application.getApplicationIdentifier());

            // Removing the application memory
            stmt.setInt(11, 0);
            stmt.setBoolean(12, true);

            stmt.executeUpdate();

            rs = stmt.getGeneratedKeys();
            if (rs.next()) {
                applicationIds.add(rs.getInt(1));
            }
        }
        return applicationIds;
    } catch (SQLException e) {
        throw new DeviceManagementDAOException("Error occurred while adding bulk application list", e);
    } finally {
        DeviceManagementDAOUtil.cleanupResources(stmt, null);
    }
}
 
Example 16
@Override
public void setNonNullParameter(PreparedStatement ps, int i, Byte[] parameter, JdbcType jdbcType) throws SQLException {
  ps.setBytes(i, ByteArrayUtils.convertToPrimitiveArray(parameter));
}
 
Example 17
Source Project: Azzet   File: TestDatabase.java    License: Open Software License 3.0 4 votes vote down vote up
/**
 * Inserts the request into the database.
 * 
 * @param request
 * 		The request string.
 * @param input
 * 		The asset as an InputStream.
 * @return
 * 		Returns true if the asset was inserted, otherwise false.
 * @throws Exception
 * 		An error occurred with the database.
 */
protected boolean insert(String request, InputStream input) throws Exception
{
	Connection con = getConnection();
	
	PreparedStatement stmt = con.prepareStatement(INSERT_QUERY);
	
	boolean inserted = false;
	try 
	{
		stmt.setString(1, request);
		
		// Try Blob, BinaryStream, and finally Bytes if neither work.
		try 
		{
			stmt.setBlob(2, input);
			
			System.out.println(driverName + " inserted as Blob");
		}
		catch (AbstractMethodError e0) 
		{
			try 
			{
				stmt.setBinaryStream(2, input);

				System.out.println(driverName + " inserted as BinaryStream");
			}
			catch (AbstractMethodError e1) 
			{
				stmt.setBytes(2, FormatUtility.getBytes(input));

				System.out.println(driverName + " inserted as Bytes");
			}
		}
		
		inserted = (stmt.executeUpdate() == 1);
	}
	finally 
	{
		stmt.close();
	}

	return inserted;
}
 
Example 18
Source Project: glowroot   File: EnvironmentDao.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public void bind(PreparedStatement preparedStatement) throws SQLException {
    preparedStatement.setBytes(1, environment.toByteArray());
}
 
Example 19
Source Project: mybatis   File: ByteArrayTypeHandler.java    License: Apache License 2.0 4 votes vote down vote up
@Override
public void setNonNullParameter(PreparedStatement ps, int i, byte[] parameter, JdbcType jdbcType)
    throws SQLException {
  ps.setBytes(i, parameter);
}
 
Example 20
Source Project: StatsAgg   File: DatabaseUtils.java    License: Apache License 2.0 4 votes vote down vote up
private static int setPreparedStatementParameter(PreparedStatement preparedStatement, List<Object> preparedStatementParameters, Object object, AtomicInteger index) {
    
    if ((preparedStatement == null) || (preparedStatementParameters == null)) {
        logger.warn("Can't set preparedStatementParameters - preparedStatementParameters or preparedStatement is null");
        return -1;
    } 

    try {
        if (object == null) {
            preparedStatement.setObject(index.getAndIncrement(), null);
        } 
        else if (object instanceof BigDecimal) {
            preparedStatement.setBigDecimal(index.getAndIncrement(), (BigDecimal) object);
        }
        else if (object instanceof Blob) {
            preparedStatement.setBlob(index.getAndIncrement(), (Blob) object);
        }
        else if (object instanceof Boolean) {
            preparedStatement.setBoolean(index.getAndIncrement(), (Boolean) object);
        }
        else if (object instanceof Byte) {
            preparedStatement.setByte(index.getAndIncrement(), (Byte) object);
        }
        else if (object instanceof byte[]) {
            preparedStatement.setBytes(index.getAndIncrement(), (byte[]) object);
        }
        else if (object instanceof Clob) {
            preparedStatement.setClob(index.getAndIncrement(), (Clob) object);
        }
        else if (object instanceof Double) {
            preparedStatement.setDouble(index.getAndIncrement(), (Double) object);
        }
        else if (object instanceof Float) {
            preparedStatement.setFloat(index.getAndIncrement(), (Float) object);
        }
        else if (object instanceof Integer) {
            preparedStatement.setInt(index.getAndIncrement(), (Integer) object);
        }
        else if (object instanceof List) {
            for (Object listObject : (List) object) {
                setPreparedStatementParameter(preparedStatement, preparedStatementParameters, listObject, index);
            }
        }
        else if (object instanceof Long) {
            preparedStatement.setLong(index.getAndIncrement(), (Long) object);
        }
        else if (object instanceof NClob) {
            preparedStatement.setNClob(index.getAndIncrement(), (NClob) object);
        }
        else if (object instanceof Ref) {
            preparedStatement.setRef(index.getAndIncrement(), (Ref) object);
        }
        else if (object instanceof RowId) {
            preparedStatement.setRowId(index.getAndIncrement(), (RowId) object);
        }
        else if (object instanceof SQLXML) {
            preparedStatement.setSQLXML(index.getAndIncrement(), (SQLXML) object);
        }
        else if (object instanceof Short) {
            preparedStatement.setShort(index.getAndIncrement(), (Short) object);
        }
        else if (object instanceof String) {
            preparedStatement.setString(index.getAndIncrement(), (String) object);
        }
        else if (object instanceof Time) {
            preparedStatement.setTime(index.getAndIncrement(), (Time) object);
        }
        else if (object instanceof java.sql.Timestamp) {
            preparedStatement.setTimestamp(index.getAndIncrement(), (java.sql.Timestamp) object);
        }
        else if (object instanceof java.sql.Date) {
            preparedStatement.setDate(index.getAndIncrement(), (java.sql.Date) object);
        }
        else if (object instanceof java.util.Date) {
            java.util.Date tempDate = (java.util.Date) object;
            java.sql.Date dateSql = new java.sql.Date(tempDate.getTime());
            preparedStatement.setDate(index.getAndIncrement(), dateSql);
        }
        else {
            if (object instanceof Object) {}
            else {
                logger.warn("Setting PreparedStatement parameter to 'object' type when object is not an object type");
            }
            
            preparedStatement.setObject(index.getAndIncrement(), object);
        }
        
        return index.get();
    }
    catch (Exception e) {
        logger.error(e.toString() + System.lineSeparator() + StackTrace.getStringFromStackTrace(e));
        return -1;
    }  
    
}