Strange error when using varbit

Hi !

I have a problem with varbit and blobs. I need to upload binary files to my database, and yesterday I've got it working.... for an hour or two. Files were uploaded, but image (which I tried to download then was corrupted). But the worst came later, postgres started to throw exceptions at me :( Help, this is part of my diploma, and i really need to get it working..

I'm using:

JDeveloper 10g
PostgreSQL 8
JDBC drivers:
- postgresql-8.0-310.jdbc3
- postgresql-8.1dev-401.jdbc3
Commons File Upload
OS: Windows XP
Java: 1.4.2
Tomcat 5.0

Connection are pooled (org.postgresql.ds.PGPoolingDataSource), looked up using JNDI.

Stack trace :
1 <----- this is generated pk
5 <----- foreign key
04_LAB_instrukcja.pdf <----- filename
application/pdf <------ mime type
768164 <------- file size

And the exception itself ;)

org.postgresql.util.PSQLException: ERROR: "%" is not a valid binary digit

at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:273)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:101)
at pl.edu.pw.mchtr.model.RequirementDAO.saveBinary(RequirementDAO.java:1074)
at pl.edu.pw.mchtr.model.Requirement.saveBinary(Requirement.java:262)
at pl.edu.pw.mchtr.controller.Upload.doPost(Upload.java:84)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:709)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:237)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at pl.edu.pw.mchtr.controller.filters.Watchdog.doFilter(Watchdog.java:59)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:186)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:157)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardContextValve.invokeInternal(StandardContextValve.java:198)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:152)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:462)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:137)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:118)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:102)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.core.StandardValveContext.invokeNext(StandardValveContext.java:104)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:520)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:929)
at org.apache.coyote.tomcat5.CoyoteAdapter.service(CoyoteAdapter.java:160)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:799)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:705)
at org.apache.tomcat.util.net.TcpWorkerThread.runIt(PoolTcpEndpoint.java:577)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:595)

Upload code:

public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException
{
boolean isMultipart = FileUpload.isMultipartContent(request);

if (isMultipart)
{
DiskFileUpload upload = new DiskFileUpload();

// Set upload parameters
upload.setSizeMax(5242880);
// upload.setRepositoryPath(tempDir);

String projectsId = "", requirementsId = "";
// Parse the request
try
{
List items = upload.parseRequest(request);
BinaryHelperBean bean = new BinaryHelperBean();
if (!items.isEmpty())
{
Iterator it = items.iterator();
while (it.hasNext())
{
FileItem item = (FileItem)it.next();
if (item.isFormField())
{
String paramName = item.getFieldName();
String fieldValue = item.getString();
if (paramName.equalsIgnoreCase("projectsId"))
projectsId = item.getString("UTF-8");
else
requirementsId = item.getString("UTF-8");
}
else
{
String fileName = item.getName();
String contentType = item.getContentType();
bean.setFilename(fileName);
bean.setContentType(contentType);
InputStream stream = item.getInputStream();
int sizeInBytes = (int)item.getSize();
bean.setFileSize(sizeInBytes);
byte[] buffer = new byte[sizeInBytes];

ByteArrayOutputStream bostream = new ByteArrayOutputStream();
byte[] chunk = new byte[128];
int real;
while((real = stream.read(chunk)) != -1)
bostream.write(chunk, 0, real);
bean.setOutputStream(bostream);
stream.close();
}
}
System.out.println("Projects id w upload: " + projectsId);
System.out.println("Requirements id w upload: " + requirementsId);
bean.setProjectsId(projectsId);
bean.setRequirementsId(requirementsId);
Requirement req = new Requirement();
req.saveBinary(bean);
}
}

DAO code that writes bytes to postgres:

public void saveBinary(BinaryHelperBean bean) throws DAOException
{
Sequencer seq = Sequencer.getInstance();
Long id = seq.getNextKey("RESOURCES");
String update = "insert into resources (resources_id, projects_id, requirements_id, " +
"filename, content_type, filesize, file) values (?, ?, ?, ?, ?, ?, ?)";
PreparedStatement pstat = null;
try
{
getConnection();
conn.setAutoCommit(false);
pstat = conn.prepareStatement(update);
pstat.setLong(1, id.longValue());
System.out.println(id.longValue() + "");
pstat.setLong(2, bean.getProjectsIdLong().longValue());
System.out.println(bean.getProjectsIdLong().toString());
if (bean.getRequirementsIdLong() != null)
pstat.setLong(3, bean.getRequirementsIdLong().longValue());
else
pstat.setObject(3, bean.getRequirementsIdLong());
pstat.setString(4, bean.getFilename());
System.out.println(bean.getFilename());
pstat.setString(5, bean.getContentType());
System.out.println(bean.getContentType());
pstat.setInt(6, bean.getFileSize());
pstat.setBinaryStream(7, bean.getInputStream(), bean.getFileSize());
System.out.println(bean.getFileSize() + "");
pstat.executeUpdate();
conn.commit();
}
catch (SQLException e)
{
try
{
conn.rollback();
}
catch (Exception ex)
{
ex.printStackTrace();
}
handleException(e, "Wyjątek przy zapisie pliku binarnego.");
}
catch (NamingException e)
{
try
{
conn.rollback();
}
catch (Exception ex)
{
ex.printStackTrace();
}
handleException(e, "Wyjątek przy zapisie pliku binarnego.");
}
finally
{
try
{
if (pstat != null)
pstat.close();
closeConnection();
}
catch (Exception e)
{
e.printStackTrace();
}
}
}

and finally (don't know if it's important) - upload jsp:

<%@ taglib uri="/WEB-INF/struts-bean.tld" prefix="bean"%>
<%@ taglib uri="http://java.sun.com/jstl/core" prefix="c"%>
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html"%>
<%@ page contentType="text/html;charset=utf-8"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title><bean:message key="binary.upload" /></title>
<link rel="stylesheet" type="text/css" href="css/main.css" charset="utf-8" />
<script language="javascript" charset="utf-8" src="CustomValidation.js" type="text/javascript">
</script>
</head>
<body>
<form enctype="multipart/form-data" action="/RMS/upload" method="post">
<P>
<%
/*c:out didn't give any output here, i had to use scriplet ;)*/
out.print("<input type=\"hidden\" value=\"");
out.print(request.getParameter("projectsId"));
out.print("\" name=\"projectsId\"/>");
out.print("<input type=\"hidden\" value=\"");
out.print(request.getParameter("requirementsId"));
out.print("\" name=\"requirementsId\"/>");
%>
</P>
<table align="center" cellspacing="3" cellpadding="3" border="0">
<tr>
<td align="center"><bean:message key="binary.upload" /></td>
</tr>
<tr>
<td><input type="file" name="filename"/></td>
</tr>
<tr>
<td>
<html:submit>
<bean:message key="binary.uploadSubmit" />
</html:submit>
</td>
</tr>
</table>
</form>
</body>
</html>

Once again, please help, I really don't know what is wrong.

Maciek

Maciej Jaźwiński wrote:

> I have a problem with varbit and blobs. I need to upload binary files to
> my database, and yesterday I've got it working.... for an hour or two.
> Files were uploaded, but image (which I tried to download then was
> corrupted). But the worst came later, postgres started to throw
> exceptions at me :( Help, this is part of my diploma, and i really need
> to get it working..

> org.postgresql.util.PSQLException: ERROR: "%" is not a valid binary digit

> String update = "insert into resources (resources_id, projects_id,
> requirements_id, " +
> "filename, content_type, filesize, file) values (?, ?, ?, ?, ?, ?, ?)";

What is the definition of the 'resources' table?

I suspect from your description and the error that you are getting
confused between bit(n) (bit string) and bytea (binary data). For your
application it sounds like you want to use bytea. There's no direct
mapping of bit(n) types in JDBC.

See http://www.postgresql.org/docs/8.0/static/datatype-binary.html and
http://www.postgresql.org/docs/8.0/static/datatype-bit.html.

-O