updateBinaryStream on ResultSet with FileStream

Question

If I try to update a FileStream column I get following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The result set is not updatable.

Code:

System.out.print("Now, let's update the filestream data.");
FileInputStream iStream = new FileInputStream("c:\\testFile.mp3");
rs.updateBinaryStream(2, iStream, -1);
rs.updateRow();
iStream.close();

Why is this?

Table in Sql Server 2008:

CREATE TABLE [BinaryAssets].[BinaryAssetFiles](
    [BinaryAssetFileId] [int] IDENTITY(1,1) NOT NULL,
    [FileStreamId] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [Blob] [varbinary](max) FILESTREAM  NULL,
    [HashCode] [nvarchar](100) NOT NULL,
    [Size] [int] NOT NULL,
    [BinaryAssetExtensionId] [int] NOT NULL,

Query used in Java:

String strCmd = "select BinaryAssetFileId, Blob 
                 from BinaryAssets.BinaryAssetFiles 
                 where BinaryAssetFileId = 1";
stmt = con.createStatement();
rs = stmt.executeQuery(strCmd);

Answer 1

I don’t know java so I’m going to assume your query is right. Have you made sure that the filestream feature is setup correctly in SQL server? Specifically there are 3 options on the SQL server configuration manager that need setting, they are

• Enable FILESTREAM for Transact-SQL access
• Enable FILESTREAM for the I/O streaming access
• Allow remote clients to have streaming access to FILESTREAM data

Make sure all of those are ticked and try again

Answer 2

From http://support.microsoft.com/kb/945738:

This behavior is by design.

That's it. There are two workarounds outlined in the article:

Method 1
Change the query that returns the result set, or change the configuration of the underlying table. When you do this, SQL Server 2000 does not convert the cursor type.

Method 2
Manually create statements to update the table in SQL Server 2000.

(a bunch of code here)