Not able to read xlsx file saved in postgres database (as bytea) using apache POI.

**Amit Kumar M** · Sep 7 '11, 11:31 AM

Hi,

I am using apache POI to read a xlsx file and upload the data in the database. I have to do this work in a scheduler(at scheduled time) running on Jboss. As scheduler is running on different server from the one file was uploaded, I am using postgres bytea datatype to save the file in database using below code

Code:

PreparedStatement ps =
                        con.prepareStatement( "UPDATE tk_tablename SET tk_filecolumnname = ? WHERE primarykey = '"
                            + fileAttachment.getPrimaryKey() + "';" );
                    FilePathAssociation filePathAssociation = fileAttachment.getFilePathAssociation();
                    if ( filePathAssociation != null )
                    {
                        File blobFile = new File( filePathAssociation.getPhysicalFilePath() );
                        FileInputStream fis = new FileInputStream( blobFile );
                        ps.setBinaryStream( 1, fis, (int)blobFile.length() );
                        ps.executeUpdate();
                        ps.close();
                        fis.close();
                    }

This is working fine and the file is saved in database.

But while reading the file on server using below code

Code:

 ResultSet rs =
                        stmt.executeQuery( "SELECT tk_filecolumnname FROM tk_tablename WHERE primarykey = '"
                            + fileAttachment.getPrimaryKey() + "';" );
                    if ( rs != null && rs.next() )
                    {
                        InputStream fileInputStream = rs.getBinaryStream( 1 );
                        Workbook workbook = WorkbookFactory.create( fileInputStream ); // apache POI code to read a xlsx file.
                        rs.close();
                        return file;
                    }

It is giving below error,

Code:

java.lang.IllegalArgumentException: Your InputStream was neither an OLE2 stream, nor an OOXML stream

I know to read a xlsx file, POI needs a stream backed by OOXML. But why

Code:

ResultSet.getBinaryStream()

method is not returning the same input stream that was saved while creating file.

Please help or share your knowledge.

thanks,
Amit.