Search
Calendar
May 2017
S M T W T F S
« Sep    
 123456
78910111213
14151617181920
21222324252627
28293031  
Your widget title
Archives

Posts Tagged ‘BLOB’

PostHeaderIcon How to Read a BLOB for a Human Being?

Case

I have had to access a BLOB and read its content. By principle, I dislike using binary objects, which do not suit easy tracing and auditing. Anyway, in my case, floats are stored in a BLOB, and I need read them in order to validate my current development.

You have many ways to read the content of the BLOB. I used two: SQL and Java

SQL

Start your TOAD for Oracle ; you can launch queries similar to this:

SELECT UTL_RAW.cast_to_binary_float
             (DBMS_LOB.submyrecord (myrecord.myrecordess,
                                    4,
                                    1 + (myrecordessnameid * 4)
                                   )
             ) AS myrecordessvalue
  FROM mytable myrecord
 WHERE myrecordessid = 123456; 

You can also run a stored procedure, similar to this:

DECLARE
   blobAsVariable  BLOB;
   my_vr           RAW (4);
   blobValue       FLOAT;
   bytelen         NUMBER  := 4;
   v_index         NUMBER  := 5;
   jonathan        RAW (4);
   loopLength      INT;
BEGIN
   SELECT myField
     INTO blobAsVariable
     FROM myTable
    WHERE tableid = (5646546846);

   DBMS_LOB.READ (blobAsVariable, bytelen, 1, jonathan);
   loopLength := UTL_RAW.cast_to_binary_integer (jonathan);

   FOR rec IN 1 .. loopLength
   LOOP
      DBMS_LOB.READ (blobAsVariable, bytelen, v_index, my_vr);
      blobValue := UTL_RAW.cast_to_binary_float (my_vr);
      v_index := v_index + 4;
      DBMS_OUTPUT.put_line (TO_CHAR (blobValue));
   END LOOP;
END;

Java

I am still not sure to be DBA expert. Indeed I am convinced I am more fluent in Java than in PL/SQL 😉

Create a Spring configuration file, let’s say BlobRuntimeTest-applicationContext.xml:

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd">

    <!-- $Id: BlobRuntimeTest-applicationContext.xml  $ -->
    <bean id="dataSource" destroy-method="close" class="org.apache.commons.dbcp.BasicDataSource">
        <property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
        <property name="url" value="jdbc:oracle:thin:@myDBserver:1234:MY_SCHEMA"/>
        <property name="username" value="jonathan"/>
        <property name="password" value="lalou"/>
        <property name="initialSize" value="2"/>
        <property name="minIdle" value="2"/>
    </bean>

    <bean id="jdbcTemplate" class="org.springframework.jdbc.core.JdbcTemplate">
        <property name="dataSource" ref="dataSource"/>
    </bean>

</beans>

Now create a runtime test:

/**
 * User: Jonathan Lalou
 * Date: Aug 7, 2011
 * Time: 5:22:33 PM
 * $Id: BlobRuntimeTest.java $
 */
public class BlobRuntimeTest extends TestCase {
    private static final Logger LOGGER = Logger.getLogger(BlobRuntimeTest.class);

    private static final String TABLE = "jonathanTable";
    private static final String PK_FIELD = "jonathanTablePK";
    private static final String BLOB_FIELD = "myBlobField";
    private static final int[] PK_VALUES = {123, 456, 789};

    private ApplicationContext applicationContext;
    private JdbcTemplate jdbcTemplate;

    @Before
    public void setUp() throws Exception {
        applicationContext = new ClassPathXmlApplicationContext(
                "lalou/jonathan/the/cownboy/BlobRuntimeTest-applicationContext.xml");
        assertNotNull(applicationContext);
        jdbcTemplate = (JdbcTemplate) applicationContext.getBean("jdbcTemplate");
        assertNotNull(jdbcTemplate);
    }

    @After
    public void tearDown() throws Exception {
    }

    @Test
    public void testGetArray() throws Exception {
        for (int pk_value : PK_VALUES) {
            final Blob blob;
            final byte[] bytes;
            final float[] floats;

            blob = (Blob) jdbcTemplate.queryForObject("select " + BLOB_FIELD + " from " + TABLE + " where " + PK_FIELD + " = " + pk_value, Blob.class);
            assertNotNull(blob);
            bytes = blob.getBytes(1, (int) blob.length());
            // process your blob: unzip, read, concat, add, etc..
            // floats = ....

            LOGGER.info("Blob size: "  + floats.length);
            LOGGER.info(ToStringBuilder.reflectionToString(floats));
        }
    }
}