0

I have previously downloaded some files and stored them in a PGSQL database. I have used 'bytea' type for the 'content' column where I store the file content as a binary stream. Now, if the file has been updated (at the original source that hosts the file) then I need to update the corresponding entry in my database. So I have made a Java PreparedStatement to update as follows:

PreparedStatement sqlStatement;

// args below is the list of column objects
for(int i=0; args != null && i < args.size(); i++)
{
    if(i+1 == blobIndex)
        sqlStatement.setBinaryStream(blobIndex, (InputStream) args.get(i), blobSize);
    else
        sqlStatement.setObject(i+1, args.get(i));   
}
sqlStatement.executeUpdate();

I get the following error:

org.postgresql.util.PSQLException: ERROR: syntax error at or near ","
  Position: 143
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2103)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1836)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:512)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:606)
    at org.postgresql.ds.jdbc23.AbstractJdbc23PooledConnection$StatementHandler.invoke(AbstractJdbc23PooledConnection.java:455)
    at com.sun.proxy.$Proxy1.executeUpdate(Unknown Source)
    at PGSqlAdapter.executeQuery(PGSqlAdapter.java:99)

I am using JDBC/Postgresql version (on Maven): 9.1-901-1.jdbc4

EDIT Here's the final sql statement that is being executed (got this via debug mode):

UPDATE  file SET  content='<stream of 61079 bytes>', sourceUserID='d@gmail.com', accountid='4d2b4396-c5bf-3051-b257-73a8d8fea41c', source='googledrive', path='[{"id":"0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","isRoot":false,"kind":"drive#parentReference","parentLink":"https://www.googleapis.com/drive/v2/files/0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","selfLink":"https://www.googleapis.com/drive/v2/files/0Bwn0zc8sQE8CdEx6Y1dabHhLUE0/parents/0Bwn0zc8sQE8CX3FxQTNvNG8tYTg"}]', userid='x@x.com', extraparams='{"crc":"2fc453da4ce077f7e267627fbc780d93","isroot":false,"lastmodified":1379712225540,"parentid":"0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","filetype":"image/jpeg","nodeid":"0Bwn0zc8sQE8CdEx6Y1dabHhLUE0"}', filename='SPACE.JPG', size='61079' WHERE userid='x@x.com', accountid='4d2b4396-c5bf-3051-b257-73a8d8fea41c', source='googledrive', filename='SPACE.JPG', path='[{"id":"0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","isRoot":false,"kind":"drive#parentReference","parentLink":"https://www.googleapis.com/drive/v2/files/0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","selfLink":"https://www.googleapis.com/drive/v2/files/0Bwn0zc8sQE8CdEx6Y1dabHhLUE0/parents/0Bwn0zc8sQE8CX3FxQTNvNG8tYTg"}]', sourceUserID='d@gmail.com'

I don't know the version of PGSQL as of now, but will update here as soon as I get to know that information. I also should mention that insert of the blob works perfectly fine:

INSERT INTO file (content,sourceUserID,accountid,source,path,userid,extraparams,filename,size) VALUES ('<stream of 2081345 bytes>','d@gmail.com','4d2b4396-c5bf-3051-b257-73a8d8fea41c','googledrive','[{"id":"0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","isRoot":false,"kind":"drive#parentReference","parentLink":"https://www.googleapis.com/drive/v2/files/0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","selfLink":"https://www.googleapis.com/drive/v2/files/0Bwn0zc8sQE8CaU80engzNDJlSkU/parents/0Bwn0zc8sQE8CX3FxQTNvNG8tYTg"}]','x@gmail.com','{"crc":"244fb7ce4677a05de7a8e92175b9cbcf","isroot":false,"lastmodified":1379712217782,"parentid":"0Bwn0zc8sQE8CX3FxQTNvNG8tYTg","filetype":"image/png","nodeid":"0Bwn0zc8sQE8CaU80engzNDJlSkU"}','MARBLE24.PNG','2081345')
Chris Travers
  • 25,424
  • 6
  • 65
  • 182
Rocky Inde
  • 1,511
  • 1
  • 20
  • 27
  • You appear to have left out the text of the SQL statement you are attempting to run. The server version would also be useful to know. – Craig Ringer Nov 13 '13 at 01:43
  • @CraigRinger Thanks, I forgot update that. – Rocky Inde Nov 13 '13 at 01:59
  • Looks like a JDBC driver issue - the driver should read the stream and substitute the stream contents when you use `setBinaryStream` to set a `bytea`, since PostgreSQL doesn't support streaming of `bytea` values. Do you still have the issue with the current PostgreSQL driver? – Craig Ringer Nov 13 '13 at 02:31
  • Yes, I'm using the latest JDBC driver (available via Maven). – Rocky Inde Nov 13 '13 at 02:49
  • @RockyIndie No you're not, the productId just changed. See: http://mvnrepository.com/artifact/org.postgresql/postgresql – Craig Ringer Nov 13 '13 at 07:40
  • @CraigRinger Thanks. I've been using the artifacts from: http://mvnrepository.com/artifact/postgresql/postgresql I will try out yours and see if it works. – Rocky Inde Nov 13 '13 at 20:54

0 Answers0