Working with SQL Server 2008 Filestream using v1.2 JDBC driver

Working with SQL Server 2008 Filestream using v1.2 JDBC driver

  • Comments 4

In the previous post "Working with SQL Server 2008 Date/Time Data Types using v1.2 JDBC driver",I talked about how the current JDBC driver can interop with the new Date/Time/DateTime2/DatetimeOffset data types.  This time around, I will describe how to interop with SQL Server 2008 Filestream.

Please refer to "SQL Server 2008 Books Online" for detailed information about Filestream.

To demostrate how the v1.2 driver can retrieve a resultset containing a filestream column, I used the following T-SQL query to create the test database and table:

CREATE DATABASE Archive
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'c:\data\archdat1.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'c:\data\filestream1')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'c:\data\archlog1.ldf')
GO

CREATE TABLE foo
(
   id int NOT NULL PRIMARY KEY,
   Photo varbinary(max) FILESTREAM NULL,
   MyRowGuidColumn uniqueidentifier NOT NULL ROWGUIDCOL
   UNIQUE DEFAULT NEWID()
)
GO

Let's assume that the table has already been populated with some data.  Here is the code snippet that I used to retrieve the column values:

strCmd = "select * from foo";
rs = stmt.executeQuery(strCmd);
if (rs.next())
{
    ResultSetMetaData rsmd = rs.getMetaData();
    if (null != rsmd)
    {
        String x = rsmd.getColumnTypeName(2);
        String name = rsmd.getColumnName(2);
        System.out.println("Column " + name + " is data type: " + x);
        int i = rsmd.getColumnType(2);
        switch (i)
        {
        case (java.sql.Types.LONGVARBINARY):
            System.out.println("Column is of JDBC type LONGVARBINARY.");
            InputStream bStream = rs.getBinaryStream(2);
            byte[] y = new byte[1024];
            if (null != bStream)
            {
                // write the retrieved filestream data as a file.
                System.out.println("Write the filestream data out as a file.");
                FileOutputStream fs = new FileOutputStream("c:\\myFile");
                int read = bStream.read(y);
                while (read != -1)
                {
                     fs.write(y);
                     fs.flush();
                     read = bStream.read(y);
                }
                fs.close();
            }
            break;
        default:
            throw new Exception("Unexpected data type found: " + String.valueOf(i) + ". Expected LONGVARBINARY.");
        }
    }

    // now, update the data with a new file content.
    System.out.print("Now, let's update the filestream data.");
    FileInputStream iStream = new FileInputStream("c:\\testFile.xml");
    rs.updateBinaryStream(2, iStream, -1);
    rs.updateRow();
    iStream.close();
}
rs.close();

When executed as a console application, you'll get the following output:

Column Photo is data type: varbinary
Column is of JDBC type LONGVARBINARY.
Write the filestream data out as a file.
Now, let's update the filestream data.

As you've probably already noticed, SQL Server 2008 Filestream is simply a LONGVARBINARY to the v1.2 JDBC driver.  This means you can operate on a Filestream like any other LONGVARBINARY, though I recommend that you stick with binaryStream to minize the amount of buffering the driver has to do.

Jimmy Wu
SQL Server JDBC Team

Leave a Comment
  • Please add 2 and 5 and type the answer here:
  • Post
  • PingBack from http://www.easycoded.com/working-with-sql-server-2008-filestream-using-v12-jdbc-driver/

  • Hi, I used your example to set up a connection with Sql Server (Table with FileStream). It works great, but when I try to update the Row, I get following error:

    Now, let's update the filestream data.com.microsoft.sqlserver.jdbc.SQLServerException: The result set is not updatable.

    Any idea?

    I'm using jdbc 3.0

  • I am getting the following error on  

    int read = bStream.read(y);

    java.io.IOException: The stream is closed.

    com.microsoft.sqlserver.jdbc.BaseInputStream.checkClosed(SimpleInputStream.java:93)

    com.microsoft.sqlserver.jdbc.PLPInputStream.read(PLPInputStream.java:237)

    agents.AgentPage.execute(AgentPage.java:128)

    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)

    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

    java.lang.reflect.Method.invoke(Method.java:597)

    com.opensymphony.xwork2.DefaultActionInvocation.invokeAction(DefaultActionInvocation.java:441)

    com.opensymphony.xwork2.DefaultActionInvocation.invokeActionOnly(DefaultActionInvocation.java:280)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:243)

    com.opensymphony.xwork2.interceptor.DefaultWorkflowInterceptor.doIntercept(DefaultWorkflowInterceptor.java:165)

    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.validator.ValidationInterceptor.doIntercept(ValidationInterceptor.java:252)

    org.apache.struts2.interceptor.validation.AnnotationValidationInterceptor.doIntercept(AnnotationValidationInterceptor.java:68)

    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.ConversionErrorInterceptor.intercept(ConversionErrorInterceptor.java:122)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)

    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.ParametersInterceptor.doIntercept(ParametersInterceptor.java:195)

    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.StaticParametersInterceptor.intercept(StaticParametersInterceptor.java:179)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    org.apache.struts2.interceptor.MultiselectInterceptor.intercept(MultiselectInterceptor.java:75)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    org.apache.struts2.interceptor.CheckboxInterceptor.intercept(CheckboxInterceptor.java:94)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    org.apache.struts2.interceptor.FileUploadInterceptor.intercept(FileUploadInterceptor.java:306)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.ModelDrivenInterceptor.intercept(ModelDrivenInterceptor.java:89)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.ScopedModelDrivenInterceptor.intercept(ScopedModelDrivenInterceptor.java:130)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    org.apache.struts2.interceptor.debugging.DebuggingInterceptor.intercept(DebuggingInterceptor.java:267)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.ChainingInterceptor.intercept(ChainingInterceptor.java:126)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.PrepareInterceptor.doIntercept(PrepareInterceptor.java:138)

    com.opensymphony.xwork2.interceptor.MethodFilterInterceptor.intercept(MethodFilterInterceptor.java:87)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.I18nInterceptor.intercept(I18nInterceptor.java:165)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    org.apache.struts2.interceptor.ServletConfigInterceptor.intercept(ServletConfigInterceptor.java:164)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.AliasInterceptor.intercept(AliasInterceptor.java:179)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    com.opensymphony.xwork2.interceptor.ExceptionMappingInterceptor.intercept(ExceptionMappingInterceptor.java:176)

    com.opensymphony.xwork2.DefaultActionInvocation.invoke(DefaultActionInvocation.java:237)

    org.apache.struts2.impl.StrutsActionProxy.execute(StrutsActionProxy.java:52)

    org.apache.struts2.dispatcher.Dispatcher.serviceAction(Dispatcher.java:488)

    org.apache.struts2.dispatcher.ng.ExecuteOperations.executeAction(ExecuteOperations.java:77)

    org.apache.struts2.dispatcher.ng.filter.StrutsPrepareAndExecuteFilter.doFilter(StrutsPrepareAndExecuteFilter.java:91)

    note

    Any ideas...

  • Jimmy,

     I am getting the exeception com.microsoft.sqlserver.jdbc.SQLServerException: The result set is not updatable. when I run your program. How did you get around this problem?

    Thanks

    Anil

Page 1 of 1 (4 items)