Thread: org.postgresql.util.PSQLException: ERROR: column "file" is of type oid but expression is of type bytea

I want to implement file upload into PostgreSQL using JSF page. I tried this
code:

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.setBinaryStream(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();
            }

            FacesContext.getCurrentInstance().addMessage(null, new
FacesMessage("Upload successfully ended!"));
        }
        catch (IOException e)
        {
            FacesContext.getCurrentInstance().addMessage(null, new
FacesMessage("Upload failed!"));
        }
    }
}

But I get error error:

org.postgresql.util.PSQLException: ERROR: column "file" is of type oid but
expression is of type bytea
  Hint: You will need to rewrite or cast the expression.
  Position: 86

What is the proper way to cast the Object?



--
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-tp5902345.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

possible reason of the problem:

javax.servlet.http.Part
a Part does not represent an entire file... is just a part...

full code
http://pastebin.com/guQcSiwG



--
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-tp5902345p5902400.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

possible reason of the problem:

javax.servlet.http.Part
a Part does not represent an entire file... is just a part...

full code
http://pastebin.com/guQcSiwG



--
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-tp5902345p5902400.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

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.

Did you managed to successfully implement similar code? Can you give some
advice how to fix it?



--
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-tp5902345p5902507.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Did you managed to successfully implement similar code? Can you give some
advice how to fix it?

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.

Did you managed to successfully implement similar code? Can you give some
advice how to fix it?



--
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-tp5902345p5902507.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Did you managed to successfully implement similar code? Can you give some
advice how to fix it?