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.0Anonymous
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