Accessing File-Stream Data Methods (T-SQL and Managed API)

Question

I have "File-Stream" enabled database. I have created table with column that has the "file-stream" attribute and manage to record successfully row in the table. So, what I have got is image stored in my "File-Stream" as BLOB.

What is my problem?

I have to get this image and show it in the web browser using classic asp (I am total newbie in this server-language and I am not allowed to use the asp.net). I have searched and read a lot (there are many information about doing that with asp.net and almost nothing showing how to do this with classic asp) and found a article (http://www.simple-talk.com/sql/learn-sql-server/an-introduction-to-sql-server-filestream/) that shows to ways to read the data:

"Accessing FILESTREAM Data using TSQL" and "Accessing FILESTREAM data with Managed API"

The first one I have been able to understand and use. The second one (there is a example with vb.net code) I haven't been able.

This is the code:

‘Create a connection to the database

Dim ConStr As String

ConStr = "Data Source=JACOBXPS\katmaifs;Initial Catalog=NorthPole" & _

     ";Integrated Security=True"

Dim con As New SqlConnection(ConStr)

con.Open()



'Retrieve the FilePath() of the image file

Dim sqlCommand As New SqlCommand()

sqlCommand.Connection = con

sqlCommand.CommandText = "SELECT ItemImage.PathName() AS PathName " + _

                     "FROM items WHERE ItemNumber = 'MS1001'"

Dim filePath As String = sqlCommand.ExecuteScalar()



'Obtain a Transaction Context

Dim transaction As SqlTransaction = con.BeginTransaction("ItemTran")

sqlCommand.Transaction = transaction

sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"

Dim txContext As Byte() = sqlCommand.ExecuteScalar()



' Open and read file using SqlFileStream Class

Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Read)

Dim buffer As Byte() = New Byte(sqlFileStream.Length) {}

sqlFileStream.Read(buffer, 0, buffer.Length)



'Bind the image data to an image control

Dim ms As MemoryStream = New MemoryStream(buffer)

Dim bmp As Bitmap = New Bitmap(ms)

ItemImage.Image = bmp



'Cleanup

sqlFileStream.Close()

sqlCommand.Transaction.Commit()

con.Close()

I was not able to transform this in classic asp but in the same article I have read something more frustrating:

When accessing FILESTREAM data using TSQL, SQL Server reads the content of the >FILESTREAM data file and serves it to the client. SQL Server memory is used for reading >the content of the data file. Accessing FILESTREAM data using Win32 Streaming does not >use SQL Server memory. In addition it allows the application to take advantage of the >Streaming capabilities of the NT File System.

So what is my real problem?

Can be the vb.net code transform and used in a classic asp?And if it can, does that mean that when I am using the "file-stream" enable futures and want to display data in the web it will be more slow then using desktop application?

I am pretty confused and will appreciate any answer or link with article to read.

Answer 1

A little confused on the question but if all you're trying to do is display some BLOB data from the database as an Image on the webpage then you should have an "image.asp" page that looks something like this ...

SQL = "SELECT FILE_NAME,IMAGE_FILE FROM tblImages WHERE ID = " & request("id")
Set rs =db.Execute( SQL )
if not(rs.eof) then

    Response.ContentType = "application/octet-stream"
    Response.AddHeader "Content-Disposition", "attachment;filename=" & rs("FILE_NAME")
    Response.BinaryWrite rs("IMAGE_FILE")
else
    response.Write("No such record found in the database at row " & request("id"))
end if