共用方式為

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

  • 發行項

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

Comments

  • Anonymous
    October 03, 2008
    PingBack from http://www.easycoded.com/working-with-sql-server-2008-filestream-using-v12-jdbc-driver/

  • Anonymous
    April 26, 2010
    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

  • Anonymous
    December 17, 2010
    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...

  • Anonymous
    April 25, 2011
    The comment has been removed