Discussions

Web tier: servlets, JSP, Web frameworks: inserting files to orace(blob) -immediate response please!!

  1. This is a very common topic posted in this forum!

    I want to load a file from JSP and insert into oracle-blob(without using java beans).


    I have read so many solutions posted here. But I couldn't find any solution for my problem!!I would appreciate any response. Please help me to find some solution.

    Thanks
  2. Use a file upload utility class (http://www.servlets.com/cos/index.html, or http://www.orionsupport.com/articles/fileupload.html) and use it to save the posted file to the web server disk.

    Then you can use the BlobFileBean I wrote below - if you don't want to use the bean just embed the code in your JSP.

    (Message was too long so I had to get rid of readBlob method body :-<)

    John H.

    import java.io.*;
    import java.sql.*;

    import oracle.jdbc.driver.*;
    import oracle.sql.*;

    public class FileBlobBean
    {
        private String blobName;
        private int blobSize;
        private File blobFile;

        public FileBlobBean()
        {
            blobName = null;
            blobSize = 0;
            blobFile = null;
        }

        public String getBlobName()
        {
            return blobName;
        }

        public void setBlobName(String s)
        {
            blobName = s;
        }

        public File getBlobFile()
        {
            return blobFile;
        }

        public void setBlobFile(File f)
        {
            blobFile = f;
        }

        public void readBlob()
        {
        }

        public void writeBlob()
        {
            if (blobName != null)
            {
                if (!(blobName.equals("")))
                {
                    Connection con = getDbConnection();
                    if (con != null)
                    {
                        try
                        {
                            Statement stmt = con.createStatement();
                            String sql = "SELECT blob_name ";
                            sql += "FROM blob_demo ";
                            sql += "WHERE blob_name = '" + blobName + "'";
                            boolean exists = false;
                            ResultSet rs = stmt.executeQuery(sql);
                            if (rs.next())
                            {
                                exists = true;
                            }
                            rs.close();
                            if (!exists)
                            {
                                sql = "INSERT INTO blob_demo (blob_name, blob_data) ";
                                sql += "VALUES ('" + blobName + "', empty_blob())";
                                stmt.executeUpdate(sql);
                            }
                            sql = "SELECT blob_data ";
                            sql += "FROM blob_demo ";
                            sql += "WHERE blob_name = '" + blobName + "' FOR UPDATE";
                            rs = stmt.executeQuery(sql);
                            if (rs.next())
                            {
                                BLOB b = ((OracleResultSet)(rs)).getBLOB(1);
                                FileInputStream fileIn = new FileInputStream(blobFile);
                                OutputStream blobOut = b.getBinaryOutputStream();
                                byte[] buff = new byte[128];
                                int bytesRead = 0;
                                while ((bytesRead = fileIn.read(buff)) > -1)
                                {
                                    blobOut.write(buff, 0, bytesRead);
                                }
                                fileIn.close();
                                blobOut.close();
                            }
                            rs.close();
                            stmt.close();
                            con.close();
                        }
                        catch (Exception ex)
                        {
                            System.out.println(ex);
                        }
                    }
                }
            }
        }

        private Connection getDbConnection()
        {
            Connection con = null;
            try
            {
                DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                con = DriverManager.getConnection("jdbc:oracle:thin:@blobhost:blobport:blobsid", "blob", "blob");
                con.setAutoCommit(false);
            }
            catch (Exception ex)
            {
            }
            return con;
        }
    }
  3. Hi there, I did the uploading for JPG images without any porblem {less than 2KB size], when ever i insert bigger than that, it is not storing :o( Please help