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

The following examples show how to use java.sql.PreparedStatement#setBlob() . These examples are extracted from open source projects. You can vote up the ones you like or vote down the ones you don't like, and go to the original project or source file by following the links above each example. You may check out the related API usage on the sidebar.
Example 1
public  void    test_blob_Blob_String()
    throws Exception
{
    Connection  conn = getConnection();

    declareAndRunFunction
        ( "blob_Blob_String", "blob", new String[] { "varchar( 10 )" }, "'3'", "33" );

    // now test blob arguments
    declareFunction( conn, "varchar_Blob_Blob", "varchar( 10 )", new String[] { "blob" } );

    runFunction( conn, "varchar_Blob_Blob", " \"blob_Blob_String\"( 'abc' )", "abc", null );

    // make sure that you can set lob-typed ? parameters
    PreparedStatement ps = chattyPrepare( conn, "values ( \"varchar_Blob_Blob\"( ? ) )" );
    String expectedValue = "34";
    Blob blob = AnsiSignatures.blob_Blob_String( expectedValue );
    ps.setBlob( 1, blob );
    String actualValue = getScalarString( ps );
    assertTrue( expectedValue.equals( actualValue ) );
}
 
Example 2
@Override
public void setBlobAsBinaryStream(
		PreparedStatement ps, int paramIndex, InputStream binaryStream, int contentLength)
		throws SQLException {

	Blob blob = ps.getConnection().createBlob();
	try {
		FileCopyUtils.copy(binaryStream, blob.setBinaryStream(1));
	}
	catch (IOException ex) {
		throw new DataAccessResourceFailureException("Could not copy into LOB stream", ex);
	}

	this.temporaryBlobs.add(blob);
	ps.setBlob(paramIndex, blob);

	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ?
				"Copied binary stream into temporary BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 
Example 3
@Override
public void setBlobAsBytes(PreparedStatement ps, int paramIndex, byte[] content)
		throws SQLException {

	if (content != null) {
		Blob blob = ps.getConnection().createBlob();
		blob.setBytes(1, content);
		this.temporaryBlobs.add(blob);
		ps.setBlob(paramIndex, blob);
	}
	else {
		ps.setBlob(paramIndex, (Blob) null);
	}

	if (logger.isDebugEnabled()) {
		logger.debug(content != null ? "Copied bytes into temporary BLOB with length " + content.length :
				"Set BLOB to null");
	}
}
 
Example 4
@Override
public void setBlobAsBinaryStream(
		PreparedStatement ps, int paramIndex, InputStream binaryStream, int contentLength)
		throws SQLException {

	if (binaryStream != null) {
		Blob blob = ps.getConnection().createBlob();
		try {
			FileCopyUtils.copy(binaryStream, blob.setBinaryStream(1));
		}
		catch (IOException ex) {
			throw new DataAccessResourceFailureException("Could not copy into LOB stream", ex);
		}
		this.temporaryBlobs.add(blob);
		ps.setBlob(paramIndex, blob);
	}
	else {
		ps.setBlob(paramIndex, (Blob) null);
	}

	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ?
				"Copied binary stream into temporary BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 
Example 5
@Test(expected = SQLException.class)
public void testMultiHostWithException() throws SQLException {
    when(mysqlPreparedStatement.executeQuery()).thenThrow(new SQLException());
    try {
        PreparedStatement preparedStatement = multiHostConnection.prepareStatement("SELECT * FROM test WHERE a = ? or b = ? or c=? or d = ? or e=?");
        preparedStatement.setBigDecimal(1, new BigDecimal(10000));
        preparedStatement.setBlob(2, inputStream);
        preparedStatement.setBlob(3, inputStream, 1000000L);
        preparedStatement.setByte(3, (byte) 1);
        preparedStatement.setBytes(4, new byte[] {
            1,
            2
        });
        preparedStatement.setLong(5, 100L);

        ResultSet resultSet = preparedStatement.executeQuery();

        preparedStatement.close();
    } finally {
        verify(mysqlPreparedStatement).executeQuery();
        verify(mysqlPreparedStatement, times(0)).close();
        verify(mysqlPreparedStatement).setBigDecimal(anyInt(), any(BigDecimal.class));
        verify(mysqlPreparedStatement).setBlob(anyInt(), any(InputStream.class));
        verify(mysqlPreparedStatement).setBlob(anyInt(), any(InputStream.class), anyLong());
        verify(mysqlPreparedStatement).setByte(anyInt(), anyByte());

        TraceSegment traceSegment = segmentStorage.getTraceSegments().get(0);
        List<AbstractTracingSpan> spans = SegmentHelper.getSpans(traceSegment);
        assertThat(spans.size(), is(1));
        assertDBSpan(spans.get(0), "Mysql/JDBI/PreparedStatement/executeQuery", "SELECT * FROM test WHERE a = ? or b = ? or c=? or d = ? or e=?");

        List<LogDataEntity> logData = SpanHelper.getLogs(spans.get(0));
        Assert.assertThat(logData.size(), is(1));
        assertThat(logData.size(), is(1));
        assertDBSpanLog(logData.get(0));
    }

}
 
Example 6
Source Project: effectivejava   File: DefaultLobHandler.java    License: Apache License 2.0 5 votes vote down vote up
@Override
public void setBlobAsBinaryStream(
		PreparedStatement ps, int paramIndex, InputStream binaryStream, int contentLength)
		throws SQLException {

	if (streamAsLob) {
		if (binaryStream != null) {
			ps.setBlob(paramIndex, binaryStream, contentLength);
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (wrapAsLob) {
		if (binaryStream != null) {
			ps.setBlob(paramIndex, new PassThroughBlob(binaryStream, contentLength));
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else {
		ps.setBinaryStream(paramIndex, binaryStream, contentLength);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ? "Set binary stream for BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 
Example 7
@Test
public void assertSetBlob() throws SQLException, IOException {
    for (PreparedStatement each : preparedStatements) {
        try (InputStream inputStream = new ByteArrayInputStream(new byte[]{})) {
            each.setBlob(1, (Blob) null);
            each.setBlob(2, inputStream);
            each.setBlob(3, inputStream, 100L);
            assertParameter(each, 1, null);
            assertParameter(each, 2, inputStream);
            assertParameter(each, 3, inputStream);
        }
    }
}
 
Example 8
@Override
public void setBlobAsBinaryStream(
		PreparedStatement ps, int paramIndex, InputStream binaryStream, int contentLength)
		throws SQLException {

	if (streamAsLob) {
		if (binaryStream != null) {
			if (contentLength >= 0) {
				ps.setBlob(paramIndex, binaryStream, contentLength);
			}
			else {
				ps.setBlob(paramIndex, binaryStream);
			}
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (wrapAsLob) {
		if (binaryStream != null) {
			ps.setBlob(paramIndex, new PassThroughBlob(binaryStream, contentLength));
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (contentLength >= 0) {
		ps.setBinaryStream(paramIndex, binaryStream, contentLength);
	}
	else {
		ps.setBinaryStream(paramIndex, binaryStream);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(binaryStream != null ? "Set binary stream for BLOB with length " + contentLength :
				"Set BLOB to null");
	}
}
 
Example 9
@Override
public void setBlob(final int parameterIndex, final Blob x)
		throws SQLException {
	ParameterCallback callback = new ParameterCallbackAction(
			parameterIndex, x) {

		@Override
		public void call(PreparedStatement preparedStatement)
				throws SQLException {
			preparedStatement.setBlob(parameterIndex(), x);
		}
	};
	addParameterCallback(callback);
}
 
Example 10
@Override
public void setBlobAsBytes(PreparedStatement ps, int paramIndex, byte[] content)
		throws SQLException {

	if (streamAsLob) {
		if (content != null) {
			ps.setBlob(paramIndex, new ByteArrayInputStream(content), content.length);
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else if (wrapAsLob) {
		if (content != null) {
			ps.setBlob(paramIndex, new PassThroughBlob(content));
		}
		else {
			ps.setBlob(paramIndex, (Blob) null);
		}
	}
	else {
		ps.setBytes(paramIndex, content);
	}
	if (logger.isDebugEnabled()) {
		logger.debug(content != null ? "Set bytes for BLOB with length " + content.length :
				"Set BLOB to null");
	}
}
 
Example 11
/**
 * tests set bytes method of blob in memory only mode (less than 4k)
 */
public void testSetBytesSmallBlob () throws SQLException {
  Connection con = TestUtil.getConnection();
  Statement stmt = con.createStatement();
  stmt.execute ("create table blobtest (id integer, data Blob)"+ getSuffix());
  stmt.close();
  
  con.setAutoCommit (false);
  PreparedStatement pstmt = con.prepareStatement("insert into " +
                                                 "blobtest (id, data) values (?,?)");
  pstmt.setInt (1,1);
  Blob blob = con.createBlob();
  //add 1024 bytes
  byte [] data = new byte [BUFFER_SIZE];
  for (int i = 0; i < BUFFER_SIZE; i++) {
    data [i] = (byte) (i % 255);
  }
  blob.setBytes (1, data);
  assertEquals (BUFFER_SIZE, blob.length());
  pstmt.setBlob (2, blob);
  pstmt.executeUpdate();
  
  stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select data from blobtest where id = 1");
  assertEquals(true, rs.next());
  blob = rs.getBlob (1);
  assertEquals (BUFFER_SIZE, blob.length());
  //update blob in the middle
  byte [] data1 = new byte [UPDATE_SIZE];
  for (int i = 0; i < UPDATE_SIZE; i++)
    data1 [i] = 120;//just any value
  blob.setBytes (UPDATE_SIZE, data1);
  byte [] data2 = blob.getBytes (100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
 
  //update it at the end
  blob.setBytes (BUFFER_SIZE + 1, data1);
  assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  
  //insert the blob and test again
  pstmt.setInt (1, 2);
  pstmt.setBlob (2, blob);
  pstmt.executeUpdate();
  
  rs = stmt.executeQuery("select data from blobtest where " +
                         "id = 2");
  assertEquals(true, rs.next());
  blob = rs.getBlob (1);
  assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes (100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  
  //test truncate on small size blob
  blob = con.createBlob();
  data = new byte [100];
  for (int i = 0; i < 100; i++) {
    data [i] = (byte) i;
  }
  blob.setBytes (1, data);
  assertEquals (blob.length(), 100);
  blob.truncate (50);
  assertEquals (blob.length(), 50);
  blob.setBytes (1, data);
  assertEquals ("set failed", blob.length(), 100);
  blob.truncate (50);
  assertEquals ("truncation failed", blob.length(), 50);
  rs.close();
  con.commit();
  stmt.close();
  pstmt.close();
  
  stmt = con.createStatement();
  stmt.execute ("drop table blobtest");
  this.waitTillAllClear();
  stmt.close();
}
 
Example 12
Source Project: sockslib   File: JdbcTemplate.java    License: Apache License 2.0 4 votes vote down vote up
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");
    }
  }
}
 
Example 13
/**
 * tests set bytes method of blob in memory only mode (less than 4k)
 */
public void testSetBytesSmallBlob () throws SQLException {
  Connection con = TestUtil.getConnection();
  Statement stmt = con.createStatement();
  stmt.execute ("create table blobtest (id integer, data Blob)"+ getSuffix());
  stmt.close();
  
  con.setAutoCommit (false);
  PreparedStatement pstmt = con.prepareStatement("insert into " +
                                                 "blobtest (id, data) values (?,?)");
  pstmt.setInt (1,1);
  Blob blob = con.createBlob();
  //add 1024 bytes
  byte [] data = new byte [BUFFER_SIZE];
  for (int i = 0; i < BUFFER_SIZE; i++) {
    data [i] = (byte) (i % 255);
  }
  blob.setBytes (1, data);
  assertEquals (BUFFER_SIZE, blob.length());
  pstmt.setBlob (2, blob);
  pstmt.executeUpdate();
  
  stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select data from blobtest where id = 1");
  assertEquals(true, rs.next());
  blob = rs.getBlob (1);
  assertEquals (BUFFER_SIZE, blob.length());
  //update blob in the middle
  byte [] data1 = new byte [UPDATE_SIZE];
  for (int i = 0; i < UPDATE_SIZE; i++)
    data1 [i] = 120;//just any value
  blob.setBytes (UPDATE_SIZE, data1);
  byte [] data2 = blob.getBytes (100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
 
  //update it at the end
  blob.setBytes (BUFFER_SIZE + 1, data1);
  assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  
  //insert the blob and test again
  pstmt.setInt (1, 2);
  pstmt.setBlob (2, blob);
  pstmt.executeUpdate();
  
  rs = stmt.executeQuery("select data from blobtest where " +
                         "id = 2");
  assertEquals(true, rs.next());
  blob = rs.getBlob (1);
  assertEquals (BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes (100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  
  //test truncate on small size blob
  blob = con.createBlob();
  data = new byte [100];
  for (int i = 0; i < 100; i++) {
    data [i] = (byte) i;
  }
  blob.setBytes (1, data);
  assertEquals (blob.length(), 100);
  blob.truncate (50);
  assertEquals (blob.length(), 50);
  blob.setBytes (1, data);
  assertEquals ("set failed", blob.length(), 100);
  blob.truncate (50);
  assertEquals ("truncation failed", blob.length(), 50);
  rs.close();
  con.commit();
  stmt.close();
  pstmt.close();
  
  stmt = con.createStatement();
  stmt.execute ("drop table blobtest");
  this.waitTillAllClear();
  stmt.close();
}
 
Example 14
/**
 * Tests large blob (more than 4k) to ensure LOBStreamControl uses file.
 */
public void testSetBytesLargeBlob() throws SQLException {
  Connection con = TestUtil.getConnection();
  Statement stmt = con.createStatement();
  stmt.execute ("create table blobtest (id integer, data Blob)" + getSuffix());
  stmt.close();

  con.setAutoCommit (false);
  PreparedStatement pstmt = con.prepareStatement("insert into " +
                                                 "blobtest (id, data) values (?,?)");
  Blob blob = con.createBlob();
  byte [] data = new byte [BUFFER_SIZE];
  for (int i = 0; i < BUFFER_SIZE; i++) {
    data [i] = (byte) (i % 255);
  }
  //now add more than 4k so file get in use
  for (int i = 0; i < 5; i++)
    blob.setBytes (i * BUFFER_SIZE + 1, data);
  assertEquals (BUFFER_SIZE * 5 , blob.length());
  //update blob in the middle
  byte [] data1 = new byte [UPDATE_SIZE];
  for (int i = 0; i < UPDATE_SIZE; i++)
    data1 [i] = 120;//just any value
  blob.setBytes (BUFFER_SIZE + 1, data1);
  blob.setBytes (BUFFER_SIZE * 5 + 1, data1);
  assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
  //insert it into table
  pstmt.setInt (1, 3);
  pstmt.setBlob (2, blob);
  pstmt.executeUpdate ();
  
  stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select data from blobtest where " +
                                   "id = 3");
  assertEquals(true, rs.next());
  blob = rs.getBlob (1);
  byte [] data2 = blob.getBytes (BUFFER_SIZE + 1, UPDATE_SIZE);
  assertEquals (5 * BUFFER_SIZE + UPDATE_SIZE, blob.length());
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  data2 = blob.getBytes (5 * BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals (data1 [i], data2 [i]);
  //test truncate
  blob.truncate (BUFFER_SIZE);
  assertEquals ("truncate failed", BUFFER_SIZE, blob.length());
  rs.close();
  con.commit();
  stmt.close();
  pstmt.close();
}
 
Example 15
Source Project: gemfirexd-oss   File: POCClient.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * TABLE XML_DOC_1
 *   XML_DOC_ID_NBR DECIMAL(19) NOT NULL,
 *   STRUCTURE_ID_NBR DECIMAL(22) NOT NULL,
 *   CREATE_MINT_CD CHAR(1) NOT NULL,
 *   MSG_PAYLOAD_QTY DECIMAL(22) NOT NULL,
 *   MSG_PAYLOAD1_IMG BLOB(2000) NOT NULL,
 *   MSG_PAYLOAD2_IMG BLOB(2000),
 *   MSG_PAYLOAD_SIZE_NBR DECIMAL(22),
 *   MSG_PURGE_DT DATE,
 *   DELETED_FLG CHAR(1) NOT NULL,
 *   LAST_UPDATE_SYSTEM_NM VARCHAR(30),
 *   LAST_UPDATE_TMSTP TIMESTAMP NOT NULL,
 *   MSG_MAJOR_VERSION_NBR DECIMAL(22),
 *   MSG_MINOR_VERSION_NBR DECIMAL(22),
 *   OPT_LOCK_TOKEN_NBR DECIMAL(22) DEFAULT 1,
 *   PRESET_DICTIONARY_ID_NBR DECIMAL(22) DEFAULT 0 NOT NULL
 */
private void useCase2LoadData() throws SQLException {
  long currentTime = System.currentTimeMillis();
  Date date = new Date(currentTime);
  Timestamp timestamp = new Timestamp(currentTime);
  SerialBlob blob = new SerialBlob("12345678452984560289456029847609487234785012934857109348156034650234560897628900985760289207856027895602785608560786085602857602985760206106110476191087345601456105610478568347562686289765927868972691785634975604562056104762978679451308956205620437861508561034756028475180756917856190348756012876510871789546913485620720476107856479238579385923847934".getBytes(Charset.forName("UTF-8")));

  String stmt = "INSERT INTO XML_DOC_1 ("
              + " LAST_UPDATE_SYSTEM_NM,"    //  1
              + " XML_DOC_ID_NBR,"           //  2
              + " MSG_PAYLOAD1_IMG,"         //  3
              + " MSG_PAYLOAD_QTY,"          //  4
              + " MSG_MINOR_VERSION_NBR,"    //  5
              + " PRESET_DICTIONARY_ID_NBR," //  6
              + " CREATE_MINT_CD,"           //  7
              + " MSG_PAYLOAD_SIZE_NBR,"     //  8
              + " DELETED_FLG,"              //  9
              + " STRUCTURE_ID_NBR,"         // 10
              + " MSG_PURGE_DT,"             // 11
              + " OPT_LOCK_TOKEN_NBR,"       // 12
              + " MSG_MAJOR_VERSION_NBR,"    // 13
              + " LAST_UPDATE_TMSTP"         // 14
              + ") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
  PreparedStatement pstmt = this.connection.prepareStatement(stmt);

  int numBatches = POCPrms.getNumBatches();
  int batchSize = POCPrms.getBatchSize();

  Log.getLogWriter().info("Generating " + numBatches + " batches of " + batchSize);
  for (int i = 0; i < numBatches; i++) {
    for (int j = 0; j < batchSize; j++) {
      pstmt.setString(1, USECASE2_APP);
      pstmt.setInt(2, i * batchSize + j);
      pstmt.setBlob(3, blob);
      pstmt.setBigDecimal(4, BIG_DECIMAL_1);
      pstmt.setBigDecimal(5, BIG_DECIMAL_1);
      pstmt.setBigDecimal(6, BIG_DECIMAL_1);
      pstmt.setString(7, N_FLAG);
      pstmt.setBigDecimal(8, BIG_DECIMAL_1);
      pstmt.setString(9, N_FLAG);
      pstmt.setBigDecimal(10, BIG_DECIMAL_9);
      pstmt.setDate(11, date);
      pstmt.setBigDecimal(12, BIG_DECIMAL_1);
      pstmt.setBigDecimal(13, BIG_DECIMAL_1);
      pstmt.setTimestamp(14, timestamp);
      pstmt.addBatch();
    }
    pstmt.executeBatch();
    pstmt.clearBatch();
  }
  pstmt.close();
}
 
Example 16
Source Project: calcite   File: ResultSetEnumerable.java    License: Apache License 2.0 4 votes vote down vote up
/** Assigns a value to a dynamic parameter in a prepared statement, calling
 * the appropriate {@code setXxx} method based on the type of the value. */
private static void setDynamicParam(PreparedStatement preparedStatement,
    int i, Object value) throws SQLException {
  if (value == null) {
    preparedStatement.setObject(i, null, SqlType.ANY.id);
  } else if (value instanceof Timestamp) {
    preparedStatement.setTimestamp(i, (Timestamp) value);
  } else if (value instanceof Time) {
    preparedStatement.setTime(i, (Time) value);
  } else if (value instanceof String) {
    preparedStatement.setString(i, (String) value);
  } else if (value instanceof Integer) {
    preparedStatement.setInt(i, (Integer) value);
  } else if (value instanceof Double) {
    preparedStatement.setDouble(i, (Double) value);
  } else if (value instanceof java.sql.Array) {
    preparedStatement.setArray(i, (java.sql.Array) value);
  } else if (value instanceof BigDecimal) {
    preparedStatement.setBigDecimal(i, (BigDecimal) value);
  } else if (value instanceof Boolean) {
    preparedStatement.setBoolean(i, (Boolean) value);
  } else if (value instanceof Blob) {
    preparedStatement.setBlob(i, (Blob) value);
  } else if (value instanceof Byte) {
    preparedStatement.setByte(i, (Byte) value);
  } else if (value instanceof NClob) {
    preparedStatement.setNClob(i, (NClob) value);
  } else if (value instanceof Clob) {
    preparedStatement.setClob(i, (Clob) value);
  } else if (value instanceof byte[]) {
    preparedStatement.setBytes(i, (byte[]) value);
  } else if (value instanceof Date) {
    preparedStatement.setDate(i, (Date) value);
  } else if (value instanceof Float) {
    preparedStatement.setFloat(i, (Float) value);
  } else if (value instanceof Long) {
    preparedStatement.setLong(i, (Long) value);
  } else if (value instanceof Ref) {
    preparedStatement.setRef(i, (Ref) value);
  } else if (value instanceof RowId) {
    preparedStatement.setRowId(i, (RowId) value);
  } else if (value instanceof Short) {
    preparedStatement.setShort(i, (Short) value);
  } else if (value instanceof URL) {
    preparedStatement.setURL(i, (URL) value);
  } else if (value instanceof SQLXML) {
    preparedStatement.setSQLXML(i, (SQLXML) value);
  } else {
    preparedStatement.setObject(i, value);
  }
}
 
Example 17
protected int insertToTable(PreparedStatement stmt, int cid, int sid, 
    int qty, BigDecimal sub, Blob data, int tid, boolean isPut) throws SQLException {
  String blob = null;
  if (data != null) {
    if (data.length() == 0) blob = "empty data";
    else if (useMD5Checksum) blob = ResultSetHelper.convertByteArrayToChecksum(data.getBytes(1, 
        (int)data.length()), data.length());
    else blob = ResultSetHelper.convertByteArrayToString(data.getBytes(1, 
        (int)data.length()));
  }
  
  Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
      " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub
      + " data is " + blob);
  String driverName = stmt.getConnection().getMetaData().getDriverName();
  stmt.setInt(1, cid);
  stmt.setInt(2, sid);
  stmt.setInt(3, qty);
  stmt.setInt(4, qty); //availQty is the same as qty during insert
  stmt.setBigDecimal(5, sub);   
  stmt.setInt(6, tid);
  stmt.setBlob(7, data);
  int rowCount = stmt.executeUpdate();
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 

  //doing second put that may or may not successfull.
  if ( driverName.toLowerCase().contains("gemfirexd") && isPut) {
    Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
        " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub + 
        " data is " + blob + " stmt " + stmt.toString());
    
   rowCount = stmt.executeUpdate();
   warning = stmt.getWarnings(); //test to see there is a warning   
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 
  }
  return rowCount;
}
 
Example 18
Source Project: gemfirexd-oss   File: BlobTestsDUnit.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * tests set bytes method of blob in memory only mode (less than 4k)
 */
public void testSetBytesSmallBlob() throws Exception {
  startVMs(1, 4);

  Connection con = TestUtil.getConnection();
  Statement stmt = con.createStatement();
  stmt.execute("create table blobtest (id integer, data Blob)");
  stmt.close();

  con.setAutoCommit(false);
  PreparedStatement pstmt = con.prepareStatement("insert into "
      + "blobtest (id, data) values (?,?)");
  pstmt.setInt(1, 1);
  Blob blob = con.createBlob();
  // add 1024 bytes
  byte[] data = new byte[BUFFER_SIZE];
  for (int i = 0; i < BUFFER_SIZE; i++) {
    data[i] = (byte)(i % 255);
  }
  blob.setBytes(1, data);
  assertEquals(BUFFER_SIZE, blob.length());
  pstmt.setBlob(2, blob);
  pstmt.executeUpdate();

  stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery("select data from blobtest where id = 1");
  assertEquals(true, rs.next());
  blob = rs.getBlob(1);
  assertEquals(BUFFER_SIZE, blob.length());
  // update blob in the middle
  byte[] data1 = new byte[UPDATE_SIZE];
  for (int i = 0; i < UPDATE_SIZE; i++)
    data1[i] = 120;// just any value
  blob.setBytes(UPDATE_SIZE, data1);
  byte[] data2 = blob.getBytes(100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals(data1[i], data2[i]);
  // update it at the end
  blob.setBytes(BUFFER_SIZE + 1, data1);
  assertEquals(BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes(BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals(data1[i], data2[i]);
  // insert the blob and test again
  pstmt.setInt(1, 2);
  pstmt.setBlob(2, blob);
  pstmt.executeUpdate();
  rs = stmt.executeQuery("select data from blobtest where id = 2");
  assertEquals(true, rs.next());
  blob = rs.getBlob(1);
  assertEquals(BUFFER_SIZE + UPDATE_SIZE, blob.length());
  data2 = blob.getBytes(100, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals(data1[i], data2[i]);
  data2 = blob.getBytes(BUFFER_SIZE + 1, UPDATE_SIZE);
  for (int i = 0; i < UPDATE_SIZE; i++)
    assertEquals(data1[i], data2[i]);

  // test truncate on small size blob
  blob = con.createBlob();
  data = new byte[100];
  for (int i = 0; i < 100; i++) {
    data[i] = (byte)i;
  }
  blob.setBytes(1, data);
  assertEquals(blob.length(), 100);
  blob.truncate(50);
  assertEquals(blob.length(), 50);
  blob.setBytes(1, data);
  assertEquals("set failed", blob.length(), 100);
  blob.truncate(50);
  assertEquals("truncation failed", blob.length(), 50);
  rs.close();
  con.commit();
  stmt.close();
  pstmt.close();

  stmt = con.createStatement();
  stmt.execute("drop table blobtest");
  stmt.close();
}
 
Example 19
protected int insertToTable(PreparedStatement stmt, int cid, int sid, 
    int qty, BigDecimal sub, Blob data, int tid, boolean isPut) throws SQLException {
  String blob = null;
  if (data != null) {
    if (data.length() == 0) blob = "empty data";
    else if (useMD5Checksum) blob = ResultSetHelper.convertByteArrayToChecksum(data.getBytes(1, 
        (int)data.length()), data.length());
    else blob = ResultSetHelper.convertByteArrayToString(data.getBytes(1, 
        (int)data.length()));
  }
  
  Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
      " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub
      + " data is " + blob);
  String driverName = stmt.getConnection().getMetaData().getDriverName();
  stmt.setInt(1, cid);
  stmt.setInt(2, sid);
  stmt.setInt(3, qty);
  stmt.setInt(4, qty); //availQty is the same as qty during insert
  stmt.setBigDecimal(5, sub);   
  stmt.setInt(6, tid);
  stmt.setBlob(7, data);
  int rowCount = stmt.executeUpdate();
  SQLWarning warning = stmt.getWarnings(); //test to see there is a warning
  if (warning != null) {
    SQLHelper.printSQLWarning(warning);
  } 

  //doing second put that may or may not successfull.
  if ( driverName.toLowerCase().contains("gemfirexd") && isPut) {
    Log.getLogWriter().info((isPut ? "putting" : "inserting") + " into table trade.portfoliov1 cid is " + cid +
        " sid is "+ sid + " qty is " + qty + " availQty is " + qty + " subTotal is " + sub + 
        " data is " + blob + " stmt " + stmt.toString());
    
   rowCount = stmt.executeUpdate();
   warning = stmt.getWarnings(); //test to see there is a warning   
   if (warning != null) {
     SQLHelper.printSQLWarning(warning);
   } 
  }
  return rowCount;
}
 
Example 20
Source Project: gemfirexd-oss   File: LobStreamsTest.java    License: Apache License 2.0 4 votes vote down vote up
/**
 * Tests the BlobOutputStream.write(byte  b[], int off, int len) method
 **/
public void testBlobWrite3Param() throws Exception {
    InputStream streamIn = new LoopingAlphabetStream(streamSize[0]);
    assertTrue("FAIL -- file not found", streamIn != null);

    PreparedStatement stmt3 = prepareStatement(
        "SELECT b FROM testBlobX1 WHERE a = 1");
    ResultSet rs3 = stmt3.executeQuery();
    rs3.next();
    Blob blob = rs3.getBlob(1);

    assertTrue ("FAIL -- blob is NULL", (blob != null));

    int count = 0;
    byte[] buffer = new byte[1024];
    OutputStream outstream = blob.setBinaryStream(1L);
    while ((count = streamIn.read(buffer)) != -1) {
        outstream.write(buffer, 0, count);
    }
    outstream.close();
    streamIn.close();

    PreparedStatement stmt4 = prepareStatement(
        "UPDATE testBlobX1 SET b = ? WHERE a = 1");
    stmt4.setBlob(1,  blob);
    stmt4.executeUpdate();
    stmt4.close();
    rs3.close();
    // GemStone changes BEGIN
    getConnection().commit();
    // GemStone changes END

    rs3 = stmt3.executeQuery();
    assertTrue("FAIL -- blob not found", rs3.next());

    long new_length = rs3.getBlob(1).length();
    assertEquals("FAIL -- wrong blob length;",
            streamSize[0], new_length);

    // Check contents ...
    InputStream fStream = new LoopingAlphabetStream(streamSize[0]);
    InputStream lStream = rs3.getBlob(1).getBinaryStream();
    assertTrue("FAIL - Blob and file contents do not match",
            compareLob2File(fStream, lStream));

    fStream.close();
    lStream.close();
    rs3.close();
    stmt3.close();
}