Has anyone used Classic ASP with SQL Server 2008 and it's new FileStream feature?

Question

I'm getting ready to use SQL Server 2008's FileStream capability but I'm not sure if Classic ASP can read and write from SQL 2008 FileStream. We still have an old application we'd like to update to support File Uploads to database and would like to consider FileStream. If needed I can build a COM object via .NET to handle this but would like to know if there is a better way, thanks!

Answer 1

Here is some example code. I have not tried it, but got it from http://www.experts-exchange.com/Microsoft/Development/MS%5FAccess/Access%5FCoding-Macros/Q%5F24772719.html:

Function Download_File(SQL_Txt As String, Conn As ADODB.Connection, FieldName As String, Fname As String, Optional FPath As String = "") As String
        Dim Z As Variant, I As Long, Fn As String, Max As Long, X As New ADODB.Recordset, Fx As New FileSystemObject
        Dim FieldType As ADODB.DataTypeEnum
        Const Delta = 32768
        On Error GoTo Download_File_Err
        I = 1
        Download_File = ""


        X.CursorLocation = adUseServer
        X.Open SQL_Txt, Conn, adOpenStatic, adLockReadOnly

        Max = X(FieldName).ActualSize
        FieldType = X(FieldName).Type
        If FieldType = adLongVarChar Then
            Z = X(FieldName).GetChunk(Delta) 'Legggi la porzione di file...
        Else
            'FieldType =adLongVarBinary ->sicuramente!
            Z = BinaryToString(X(FieldName).GetChunk(Delta))
        End If
        'Apri-Crea il nuovo file e scrivi la prima porzione di file...
        Fx.OpenTextFile(Fn, ForWriting, True).Write Z
        While Len(Z) > 0
            If FieldType = adLongVarChar Then
                Z = Nz(X(FieldName).GetChunk(Delta), "")
            Else
                Z = BinaryToString(X(FieldName).GetChunk(Delta))
            End If
            'Salva la porzione di file...
            Fx.OpenTextFile(Fn, ForAppending, False).Write Z
            I = I + 1
        Wend
        X.Close
        Set Fx = Nothing
        Msg
        Download_File = Fn 'Segnala avvenuto scaricamento del file con nome e percorso...
        Exit Function
        Download_File_Err:
            MsgBox Err.Description
            Msg
            X.Close
        Set Fx = Nothing
End Function

Public Function Upload_File(SQL_Txt, Conn As ADODB.Connection, FieldName As String, Optional FPath_and_Name As String = "") As String
        Dim Z As String, L As Long, Fx As New FileSystemObject, X As New ADODB.Recordset
        Dim FieldType As ADODB.DataTypeEnum
        Const Delta = 16384
        On Error GoTo Upload_File_err
        Upload_File = ""
        L = FileLen(FPath_and_Name)
        'In questo caso si usa il cursore lato server... (Ma perchè il cursore lato client fallisce?)
        X.CursorLocation = adUseServer
        X.Open SQL_Txt, Conn, adOpenDynamic, adLockOptimistic
        FieldType = X(FieldName).Type
        If FieldType = adLongVarChar Then
            'Leggi tutti i caratteri dal file...
            Z = Fx.OpenTextFile(FPath_and_Name, ForReading).Read(L)
            X.Update FieldName, Z
        Else
            Z = Fx.OpenTextFile(FPath_and_Name, ForReading).Read(L)
            X.Update FieldName, StringToBinary(Z)
        End If
        X.Close
        Msg
        Upload_File = FPath_and_Name
        Exit Function
        Upload_File_err:
            MsgBox Err.Description
            X.Close
            Msg
End Function

Function BinaryToString(ByteArray As Variant) As String
        '--- Fast Converts the binary content to text
        'Antonin Foller, http://www.motobit.com
        Dim X As New ADODB.Recordset, L As Long
        BinaryToString = ""
        If IsNull(ByteArray) Then Exit Function
        L = LenB(ByteArray)
        If L > 0 Then
            X.Fields.Append "mBinary", adLongVarChar, L
            X.Open
            X.AddNew
            'In questo caso particolare AppendChunk converte l'array di byte
            'in stringa! fantastico.
            X("mBinary").AppendChunk ByteArray
            X.Update
            BinaryToString = X("mBinary")
        End If
        X.Close
End Function

Function StringToBinary(S As String) As Variant
        'Converts the string into a Binary array()
        'Standard conversion...
        Dim I As Long, V As Variant
        StringToBinary = Null
        If S = "" Then Exit Function
        ReDim V(0 To Len(S) - 1) As Byte
        For I = 1 To Len(S)
            V(I - 1) = Asc(Mid(S, I, 1))
        Next I
        StringToBinary = V
End Function