Date: Sat, 7 May 2016 10:04:25 -0700 (MST)
From: "peter.penzov" <peter.penzov@gmail.com>
To: pgsql-jdbc@postgresql.org
Message-ID: <1462640665299-5902501.post@n5.nabble.com>
In-Reply-To: 
 <CADK3HHJ=uXdoEk139uTdb54hEst9tL9Cqs7+LB-wuQUxmEzE3A@mail.gmail.com>
References: <1462562098748-5902345.post@n5.nabble.com>
 <1462568821046-5902400.post@n5.nabble.com>
 <CADK3HHJ=uXdoEk139uTdb54hEst9tL9Cqs7+LB-wuQUxmEzE3A@mail.gmail.com>
Subject: Re: org.postgresql.util.PSQLException: ERROR: column "file" is of
 type oid but expression is of type bytea
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-jdbc-owner@postgresql.org

I managed to create this:

public void upload() throws SQLException, GSSException
{
    if (file != null)
    {
        try
        {
            InputStream inputStream = file.getInputStream();
            Connection conn = ds.getConnection();
            PreparedStatement ps = null;
            boolean committed = false;
            try
            {
                conn.setAutoCommit(false);

                ps = conn.prepareStatement("INSERT INTO PROCEDURE_FILES (ID,
PROCEDURE_ID, FILE_NAME, FILE) "
                    + " VALUES (?, ?, ?, ?)");
                ps.setInt(1, obj.number);
                ps.setInt(2, obj.number);
                ps.setString(3, file.getSubmittedFileName());

                ps.setBlob(4, inputStream, inputStream.available());

                ps.executeUpdate();
                ps.close();

                conn.commit();
                committed = true;
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }

            finally
            {
                if (!committed)
                {
                    conn.rollback();
                }

                if (ps != null)
                {
                    ps.close();
                }
                conn.close();
            }
        }
        catch (IOException e)
        {
            FacesContext.getCurrentInstance().addMessage(null, new
FacesMessage("Upload failed!"));
        }
    }
}

public void initFileDBData() throws SQLException, IOException
    {
        Connection conn = ds.getConnection();
        PreparedStatement ps = null;

        try
        {
            conn.setAutoCommit(false);

            ps = conn.prepareStatement("SELECT * FROM PROCEDURE_FILES WHERE
ID = ?");

            ps.setInt(1, id);
            ResultSet rs = ps.executeQuery();
            while (rs.next())
            {
                String file_name = rs.getString("FILE_NAME");
                Blob oid = rs.getBlob("FILE");
                InputStream binaryStreasm = oid.getBinaryStream();

                FacesContext fc = FacesContext.getCurrentInstance();
                ExternalContext ec = fc.getExternalContext();

                ec.responseReset();
                ec.setResponseContentLength(binaryStreasm.available());
                ec.setResponseHeader("Content-Disposition", "attachment;
filename=\"" + file_name + "\"");

                byte[] buf;

                buf = new byte[binaryStreasm.available()];
                int offset = 0;
                int numRead = 0;
                while ((offset < buf.length) && ((numRead =
binaryStreasm.read(buf, offset, buf.length - offset)) >= 0))
                {
                    offset += numRead;
                }

                HttpServletResponse response
                    = (HttpServletResponse)
FacesContext.getCurrentInstance()
                    .getExternalContext().getResponse();

                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition",
"attachment;filename=" + file_name);
                response.getOutputStream().write(buf);
                response.getOutputStream().flush();
                response.getOutputStream().close();
                FacesContext.getCurrentInstance().responseComplete();
            }
        }
        finally
        {
            if (ps != null)
            {
                ps.close();
            }
            conn.close();
        }
    }


But when I download the file it's always 0 bytes in size. Do you have any
idea how I can fix this?


--
View this message in context: http://postgresql.nabble.com/org-postgresql-util-PSQLException-ERROR-column-file-is-of-type-oid-but-expression-is-of-type-bytea-tp5902345p5902501.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.


-- 
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc