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

static int streamInStringCol(PreparedStatement ps, String data, boolean binaryColumn) throws Exception {
	int nRows = 0;

	if (data == null)
	{
		ps.setAsciiStream(1, null, 0);
		nRows = ps.executeUpdate();
	}
	else
	{
		ByteArrayInputStream bais = new ByteArrayInputStream(data.getBytes("US-ASCII"));
		if (binaryColumn)
			ps.setBinaryStream(1, bais, data.length());
		else
			ps.setAsciiStream(1, bais, data.length());
		nRows = ps.executeUpdate();
		bais.close();
	}
	return nRows;
}
 

private static void insertDataUsingAsciiStream(PreparedStatement ps, int intValue, String fileName, int maxValueAllowed)
throws Exception{
 	File file = new File(fileName);
 	InputStream fileIn = new FileInputStream(file);
 	System.out.println("===> testing(using setAsciiStream) " + fileName + " length = " + file.length());
 	// insert a streaming column
 	ps.setInt(1, intValue);
 	ps.setAsciiStream(2, fileIn, (int)file.length());
	//for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
	//for long varchars, any character truncation will throw an exception.
 	try {
		ps.executeUpdate();
		System.out.println("No truncation and hence no error");
 	}
 	catch (SQLException e) {
		if (file.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error
			System.out.println("expected exception for data > " + maxValueAllowed + " in length");
		else
			TestUtil.dumpSQLExceptions(e,true);
 	}
 	fileIn.close();
}
 

private static void insertDataUsingAsciiStream(PreparedStatement ps, int intValue, String fileName, int maxValueAllowed)
throws Exception{
 	File file = new File(fileName);
 	InputStream fileIn = new FileInputStream(file);
 	System.out.println("===> testing(using setAsciiStream) " + fileName + " length = " + file.length());
 	// insert a streaming column
 	ps.setInt(1, intValue);
 	ps.setAsciiStream(2, fileIn, (int)file.length());
	//for varchars, trailing blank truncation will not throw an exception. Only non-blank characters cause truncation error
	//for long varchars, any character truncation will throw an exception.
 	try {
		ps.executeUpdate();
		System.out.println("No truncation and hence no error");
 	}
 	catch (SQLException e) {
		if (file.length() > maxValueAllowed && e.getSQLState().equals("22001")) //truncation error
			System.out.println("expected exception for data > " + maxValueAllowed + " in length");
		else
			TestUtil.dumpSQLExceptions(e,true);
 	}
 	fileIn.close();
}
 

static int streamInStringCol(PreparedStatement ps, String data, boolean binaryColumn) throws Exception {
	int nRows = 0;

	if (data == null)
	{
		ps.setAsciiStream(1, null, 0);
		nRows = ps.executeUpdate();
	}
	else
	{
		ByteArrayInputStream bais = new ByteArrayInputStream(data.getBytes("US-ASCII"));
		if (binaryColumn)
			ps.setBinaryStream(1, bais, data.length());
		else
			ps.setAsciiStream(1, bais, data.length());
		nRows = ps.executeUpdate();
		bais.close();
	}
	return nRows;
}
 

@Override
public void setClobAsAsciiStream(
		PreparedStatement ps, int paramIndex, @Nullable InputStream asciiStream, int contentLength)
		throws SQLException {

	if (streamAsLob) {
		if (asciiStream != null) {
			Reader reader = new InputStreamReader(asciiStream, StandardCharsets.US_ASCII);
			if (contentLength >= 0) {
				ps.setClob(paramIndex, reader, contentLength);
			}
			else {
				ps.setClob(paramIndex, reader);
			}
		}
		else {
			ps.setClob(paramIndex, (Clob) null);
		}
	}
	else if (wrapAsLob) {
		if (asciiStream != null) {
			ps.setClob(paramIndex, new PassThroughClob(asciiStream, contentLength));
		}
		else {
			ps.setClob(paramIndex, (Clob) null);
		}
	}
	else if (contentLength >= 0) {
		ps.setAsciiStream(paramIndex, asciiStream, contentLength);
	}
	else {
		ps.setAsciiStream(paramIndex, asciiStream);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(asciiStream != null ? "Set ASCII stream for CLOB with length " + contentLength :
				"Set CLOB to null");
	}
}
 

@Test
public void assertSetAsciiStream() throws SQLException, IOException {
    for (PreparedStatement each : preparedStatements) {
        try (InputStream inputStream = new ByteArrayInputStream(new byte[]{})) {
            each.setAsciiStream(1, inputStream);
            each.setAsciiStream(2, inputStream, 100);
            each.setAsciiStream(3, inputStream, 100L);
            assertParameter(each, 1, inputStream);
            assertParameter(each, 2, inputStream);
            assertParameter(each, 3, inputStream);
        }
    }
}
 

@Override
public void setClobAsAsciiStream(
		PreparedStatement ps, int paramIndex, InputStream asciiStream, int contentLength)
		throws SQLException {

	if (streamAsLob) {
		if (asciiStream != null) {
			try {
				ps.setClob(paramIndex, new InputStreamReader(asciiStream, "US-ASCII"), contentLength);
			}
			catch (UnsupportedEncodingException ex) {
				throw new SQLException("US-ASCII encoding not supported: " + ex);
			}
		}
		else {
			ps.setClob(paramIndex, (Clob) null);
		}
	}
	else if (wrapAsLob) {
		if (asciiStream != null) {
			ps.setClob(paramIndex, new PassThroughClob(asciiStream, contentLength));
		}
		else {
			ps.setClob(paramIndex, (Clob) null);
		}
	}
	else {
		ps.setAsciiStream(paramIndex, asciiStream, contentLength);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(asciiStream != null ? "Set ASCII stream for CLOB with length " + contentLength :
				"Set CLOB to null");
	}
}
 

public void testUpdateAsciiStreamLengthlessParameterName()
        throws IOException, SQLException {
    // Array to keep updated data fetched from the database.
    byte[] bytesRet = new byte[10];

    // Input Stream inserted initially.
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    // InputStream that is used for update.
    InputStream isForUpdate = new
            java.io.ByteArrayInputStream(BYTES2);

    // Prepared Statement used to insert the data.
    PreparedStatement ps_sb = prep("dLongVarchar");
    ps_sb.setInt(1, key);
    ps_sb.setAsciiStream(2, is, BYTES1.length);
    ps_sb.executeUpdate();
    ps_sb.close();

    // Update the data.
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream("dLongVarchar", isForUpdate);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream isRet = rs1.getAsciiStream(1);
    isRet.read(bytesRet);
    isRet.close();

    for (int i=0; i < BYTES2.length; i++) {
        assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
    }
    rs1.close();
}
 

@Test
public void testQueryWithMultiHost() throws SQLException {
    PreparedStatement preparedStatement = multiHostConnection.prepareStatement("SELECT * FROM test WHERE a = ? or b = ? or c=? or d = ?", 1, 1);
    preparedStatement.setAsciiStream(1, inputStream);
    preparedStatement.setAsciiStream(2, inputStream, 10);
    preparedStatement.setAsciiStream(3, inputStream, 1000000L);
    preparedStatement.setCharacterStream(4, reader);
    ResultSet resultSet = preparedStatement.executeQuery();

    preparedStatement.close();

    verify(mysqlPreparedStatement).executeQuery();
    verify(mysqlPreparedStatement).close();
}
 

public void testUpdateAsciiStreamLengthlessParameterName()
        throws IOException, SQLException {
    // Array to keep updated data fetched from the database.
    byte[] bytesRet = new byte[10];

    // Input Stream inserted initially.
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    // InputStream that is used for update.
    InputStream isForUpdate = new
            java.io.ByteArrayInputStream(BYTES2);

    // Prepared Statement used to insert the data.
    PreparedStatement ps_sb = prep("dLongVarchar");
    ps_sb.setInt(1, key);
    ps_sb.setAsciiStream(2, is, BYTES1.length);
    ps_sb.executeUpdate();
    ps_sb.close();

    // Update the data.
    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream("dLongVarchar", isForUpdate);
    rs1.updateRow();
    rs1.close();

    // Query to see whether the data that has been updated.
    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream isRet = rs1.getAsciiStream(1);
    isRet.read(bytesRet);
    isRet.close();

    for (int i=0; i < BYTES2.length; i++) {
        assertEquals("Error in updateAsciiStream", BYTES2[i], bytesRet[i]);
    }
    rs1.close();
}
 

static int updateLongString(Connection conn, int oldkey, int newkey)
	 throws Exception
{
	PreparedStatement ps = conn.prepareStatement(
		"update foo set a = ?, b = ? where a = " + oldkey);

	String updateString = pad("", newkey);
	ByteArrayInputStream bais = new ByteArrayInputStream(updateString.getBytes("US-ASCII"));
	ps.setInt(1, newkey);
	ps.setAsciiStream(2, bais, updateString.length());
	int nRows = ps.executeUpdate();
	ps.close();
	return nRows;
}
 

private int updateLongString(int oldkey, int newkey, String tableName)
        throws Exception {
    PreparedStatement ps = prepareStatement("update " + tableName
            + " set a = ?, b = ? where a = " + oldkey);

    String updateString = pad("", newkey);
    ByteArrayInputStream bais = new ByteArrayInputStream(updateString
            .getBytes("US-ASCII"));
    ps.setInt(1, newkey);
    ps.setAsciiStream(2, bais, updateString.length());
    int nRows = ps.executeUpdate();
    ps.close();
    return nRows;
}
 

static void streamTest3(Connection conn, long length) throws Exception
{
	Statement sourceStmt = conn.createStatement();
	sourceStmt.executeUpdate("create table foo (a int not null constraint pk primary key, b long varchar)");

	insertLongString(conn, 1, pad("Broadway", length), false);
	insertLongString(conn, 2, pad("Franklin", length), false);
	insertLongString(conn, 3, pad("Webster", length), false);
	PreparedStatement ps = conn.prepareStatement(
		"update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");

	File file = new File("extin/short.data");
	InputStream fileIn = new FileInputStream(file);
	ps.setAsciiStream(1, fileIn, (int)(file.length()));
	ps.executeUpdate();
	fileIn.close();

	ps = conn.prepareStatement(
		"update foo set a=a+1000, b=? where a<99 and a in (select a from foo)");
	file = new File("extin/shortbanner");
	fileIn = new FileInputStream(file);
	ps.setAsciiStream(1, fileIn, (int)(file.length()));
	ps.executeUpdate();
	fileIn.close();

	sourceStmt.executeUpdate("drop table foo");
}
 

private void setParameter(PreparedStatement preparedStatement, Object[] args) throws
    SQLException {
  if (args == null || args.length == 0) {
    return;
  }
  for (int i = 0; i < args.length; i++) {
    Object arg = args[i];
    if (TypeUtil.isInt(arg)) {
      preparedStatement.setInt(i + 1, (Integer) arg);
    } else if (TypeUtil.isString(arg)) {
      preparedStatement.setString(i + 1, (String) arg);
    } else if (TypeUtil.isLong(arg)) {
      preparedStatement.setLong(i + 1, (Long) arg);
    } else if (TypeUtil.isDouble(arg)) {
      preparedStatement.setDouble(i + 1, (Double) arg);
    } else if (TypeUtil.isFloat(arg)) {
      preparedStatement.setFloat(i + 1, (Float) arg);
    } else if (TypeUtil.isBoolean(arg)) {
      preparedStatement.setBoolean(i + 1, (Boolean) arg);
    } else if (TypeUtil.isByte(arg)) {
      preparedStatement.setByte(i + 1, (Byte) arg);
    } else if (TypeUtil.isDate(arg)) {
      preparedStatement.setDate(i + 1, (Date) arg);
    } else if (TypeUtil.isShort(arg)) {
      preparedStatement.setShort(i + 1, (Short) arg);
    } else if (TypeUtil.isArray(arg)) {
      preparedStatement.setArray(i + 1, (Array) arg);
    } else if (TypeUtil.isInputStream(arg)) {
      preparedStatement.setAsciiStream(i + 1, (InputStream) arg);
    } else if (TypeUtil.isBigDecimal(arg)) {
      preparedStatement.setBigDecimal(i + 1, (BigDecimal) arg);
    } else if (TypeUtil.isBlob(arg)) {
      preparedStatement.setBlob(i + 1, (Blob) arg);
    } else if (TypeUtil.isBytes(arg)) {
      preparedStatement.setBytes(i + 1, (byte[]) arg);
    } else if (TypeUtil.isClob(arg)) {
      preparedStatement.setClob(i + 1, (Clob) arg);
    } else if (TypeUtil.isNClob(arg)) {
      preparedStatement.setNClob(i + 1, (NClob) arg);
    } else {
      throw new IllegalArgumentException(
          "Type:" + arg.getClass().getName() + " is not supported");
    }
  }
}
 

protected void insertObjectDo(Object value, PreparedStatement statement, int index) throws SQLException {
    statement.setAsciiStream(index, ((AsciiStream) value).getInputStream(), ((AsciiStream) value).getLength());
}
 

/**
 * This methods tests the ResultSet interface method
 * updateAsciiStream
 *
 * @throws SQLException if some error occurs while calling the method
 */

public void testUpdateAsciiStream()
throws Exception {
    //create the table
    stmt.execute("create table UpdateTestTable_ResultSet (sno int, " +
            "datacol LONG VARCHAR)");

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //Input Stream inserted initially
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    //InputStream that is used for update
    InputStream is_for_update = new
            java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prepareStatement
            ("insert into UpdateTestTable_ResultSet values(?,?)");
    ps_sb.setInt(1,1);
    ps_sb.setAsciiStream(2,is,BYTES1.length);
    ps_sb.executeUpdate();
    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted

    ResultSet rs1 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet for update");
    rs1.next();
    rs1.updateAsciiStream(2,is_for_update,(int)BYTES2.length);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateAsciiStream method is the same
    //data that we expected

    rs1 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet");
    rs1.next();
    InputStream is_ret = rs1.getAsciiStream(2);

    is_ret.read(bytes_ret);
    is_ret.close();

    for(int i=0;i<BYTES2.length;i++) {
        assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
    }
    rs1.close();
    //delete the table
    stmt .execute("drop table UpdateTestTable_ResultSet");
}
 

/**
 * This methods tests the ResultSet interface method
 * updateClob
 *
 * @throws SQLException if some error occurs while calling the method
 */
public void testUpdateClob()
throws Exception {
    // Life span of Clob objects are limited by the transaction.  Need
    // autocommit off so Clob objects survive execution of next statement.
    getConnection().setAutoCommit(false);

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //1 Input Stream for insertion
    InputStream is1 = new java.io.ByteArrayInputStream(BYTES1);

    //2 Input Stream for insertion
    InputStream is2 = new java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dClob");

    //first insert
    ps_sb.setInt(1,key);
    ps_sb.setAsciiStream(2,is1,BYTES1.length);
    ps_sb.executeUpdate();

    //second insert
    int key2 = requestKey();
    ps_sb.setInt(1,key2);
    ps_sb.setAsciiStream(2,is2,BYTES2.length);
    ps_sb.executeUpdate();

    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted
    //we do not have set methods on Clob and Blob implemented
    //So query the first Clob from the database
    //update the second result set with this
    //Clob value

    ResultSet rs1 = fetchUpd("dClob", key);
    rs1.next();
    Clob clob = rs1.getClob(1);
    rs1.close();

    rs1 = fetchUpd("dClob", key2);
    rs1.next();
    rs1.updateClob(1,clob);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateClob method is the same
    //data that we expected

    rs1 = fetch("dClob", key2);
    rs1.next();
    assertEquals(clob, rs1.getClob(1));
    rs1.close();
}
 

/**
 * This methods tests the ResultSet interface method
 * updateAsciiStream
 *
 * @throws SQLException if some error occurs while calling the method
 */

public void testUpdateAsciiStream()
throws Exception {
    //create the table
    stmt.execute("create table UpdateTestTable_ResultSet (sno int, " +
            "datacol LONG VARCHAR)");

    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //Input Stream inserted initially
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    //InputStream that is used for update
    InputStream is_for_update = new
            java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prepareStatement
            ("insert into UpdateTestTable_ResultSet values(?,?)");
    ps_sb.setInt(1,1);
    ps_sb.setAsciiStream(2,is,BYTES1.length);
    ps_sb.executeUpdate();
    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted

    ResultSet rs1 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet for update");
    rs1.next();
    rs1.updateAsciiStream(2,is_for_update,(int)BYTES2.length);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateAsciiStream method is the same
    //data that we expected

    rs1 = stmt.executeQuery
            ("select * from UpdateTestTable_ResultSet");
    rs1.next();
    InputStream is_ret = rs1.getAsciiStream(2);

    is_ret.read(bytes_ret);
    is_ret.close();

    for(int i=0;i<BYTES2.length;i++) {
        assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
    }
    rs1.close();
    //delete the table
    stmt .execute("drop table UpdateTestTable_ResultSet");
}
 

@Override
public void setClobAsAsciiStream(
		PreparedStatement ps, int paramIndex, InputStream asciiStream, int contentLength)
		throws SQLException {

	if (streamAsLob) {
		if (asciiStream != null) {
			try {
				Reader reader = new InputStreamReader(asciiStream, "US-ASCII");
				if (contentLength >= 0) {
					ps.setClob(paramIndex, reader, contentLength);
				}
				else {
					ps.setClob(paramIndex, reader);
				}
			}
			catch (UnsupportedEncodingException ex) {
				throw new SQLException("US-ASCII encoding not supported: " + ex);
			}
		}
		else {
			ps.setClob(paramIndex, (Clob) null);
		}
	}
	else if (wrapAsLob) {
		if (asciiStream != null) {
			ps.setClob(paramIndex, new PassThroughClob(asciiStream, contentLength));
		}
		else {
			ps.setClob(paramIndex, (Clob) null);
		}
	}
	else if (contentLength >= 0) {
		ps.setAsciiStream(paramIndex, asciiStream, contentLength);
	}
	else {
		ps.setAsciiStream(paramIndex, asciiStream);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(asciiStream != null ? "Set ASCII stream for CLOB with length " + contentLength :
				"Set CLOB to null");
	}
}
 

/**
 * This methods tests the ResultSet interface method
 * updateAsciiStream
 *
 * @throws SQLException if some error occurs while calling the method
 */

public void testUpdateAsciiStreamStringParameterName()
throws Exception {
    //Byte array in which the returned bytes from
    //the Database after the update are stored. This
    //array is then checked to determine if it
    //has the same elements of the Byte array used for
    //the update operation

    byte[] bytes_ret = new byte[10];

    //Input Stream inserted initially
    InputStream is = new java.io.ByteArrayInputStream(BYTES1);

    //InputStream that is used for update
    InputStream is_for_update = new
            java.io.ByteArrayInputStream(BYTES2);

    //Prepared Statement used to insert the data
    PreparedStatement ps_sb = prep("dLongVarchar");
    ps_sb.setInt(1, key);
    ps_sb.setAsciiStream(2,is,BYTES1.length);
    ps_sb.executeUpdate();
    ps_sb.close();

    //Update operation
    //use a different ResultSet variable so that the
    //other tests can go on unimpacted

    ResultSet rs1 = fetchUpd("dLongVarchar", key);
    rs1.next();
    rs1.updateAsciiStream("dLongVarchar",is_for_update,(int)BYTES2.length);
    rs1.updateRow();
    rs1.close();

    //Query to see whether the data that has been updated
    //using the updateAsciiStream method is the same
    //data that we expected

    rs1 = fetch("dLongVarchar", key);
    rs1.next();
    InputStream is_ret = rs1.getAsciiStream(1);

    is_ret.read(bytes_ret);
    is_ret.close();

    for(int i=0;i<BYTES2.length;i++) {
        assertEquals("Error in updateAsciiStream",BYTES2[i],bytes_ret[i]);
    }
    rs1.close();
}