0

I have a png file which is stored as CLOB in oracle db.

and i want to get this data with jdbc template using below code

LobHandler lob = new DefaultLobHandler();
return jdbcTemplate.queryForObject(
  "select table from column",
  arr,
  (rs, rowNum) -> lob.getClobAsString(rs, "CLOB_DATA")
);

and i am converting to byte array as follows since the encoding in oracle db is WE8MSWIN1252

clob.getBytes(Charset.forName("windows-1252"));

however i get the different bytes when i read the file manually with below code and compare with the db data.

File path = new File("path/to/file");
Files.readAllBytes(path.toPath());

some chars are not loaded correctly. what could be the problem?

mstfdz
  • 2,616
  • 3
  • 23
  • 27
  • 1
    CLOBs are for storing character data. You want to be storing binary data in a BLOB – Andrew Sayer Nov 10 '20 at 19:12
  • 1
    Image data should be stored in the database as a BLOB so you avoid problems with character conversion. Does the image data from the database produce an image? – Gilbert Le Blanc Nov 10 '20 at 19:14
  • yes it does produce an image, and i cannot change it to blob since there are already added data as CLOB in DB. – mstfdz Nov 10 '20 at 19:17

1 Answers1

3

I have a png file which is stored as CLOB in oracle db.

Don't do this. If you have binary data then store it in a binary format, such as a BLOB, and don't store it in a format which was not intended for that purpose.

however i get the different bytes when i read the file manually with below code and compare with the db data.

That's because you're using a CLOB for something it wasn't intended for.

what could be the problem?

The problem is that you are using the wrong data type for your data.

i cannot change it to blob since there are already added data as CLOB in DB.

Change them all to a BLOB and then just convert the BLOB to a CLOB when you need to get character data out. Trying to do it the other way round (without any encoding of the binary to make is safe to store as a string) is going to continue creating issues like the one you already have.

If you really must use a CLOB (please, DON'T) then store the binary in an encoded format, such as Base64, which can be safely stored as character data.

MT0
  • 143,790
  • 11
  • 59
  • 117