ansaurus

Question

Answer 1

+1 A:

do this inside TRANSACTION
do "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()" without any "FROM TABLE"

There is a sample in Working with FILESTREAM using VB .NET By Yan Pan:

' Obtain a transaction context. All FILESTREAM BLOB operations occur '
' within a transaction context to maintain data consistency. '
Dim transaction As SqlTransaction = 
    sqlConnection.BeginTransaction("mainTranaction")
sqlCommand.Transaction = transaction
sqlCommand.CommandText = "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()"
Dim obj As Object = sqlCommand.ExecuteScalar()
Dim txContext As Byte() = Nothing
If Not obj.Equals(DBNull.Value) Then
    txContext = DirectCast(obj, Byte())
Else
    Throw New System.Exception("GET_FILESTREAM_TRANSACTION_CONTEXT() failed")
End If

' Obtain a handle that can be passed to the Win32 FILE APIs. '
Dim sqlFileStream As New SqlFileStream(filePath, txContext, FileAccess.Write)

' Converting the image to a byte array. '
' Please change C:\Spire.jpg to your image file path. '
Dim byteImg As Byte()
byteImg = File.ReadAllBytes("C:\Spire.jpg")
'Write the image file to the FILESTREAM BLOB. '
sqlFileStream.Write(byteImg, 0, byteImg.Length)

' Close the FILESTREAM handle. '
sqlFileStream.Close()

' Commit the write operation that was performed on the FILESTREAM BLOB. ' 
sqlCommand.Transaction.Commit()

Try to change your code according to sample:

filePath variable is initiated before opening file transaction
there is a simple "SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()" command for file context
there is a null check and DirectCast(obj, Byte()) to cast value into byte
not sure if it make difference, but SqlFileStream is opening with txContext, not txContext.Value

UPDATE
How I understand the problem:

filePathName - filename from where we will open filestream. this value should be selected from some field of some your table. If you don't know what [table].[field] it is, see inside db table values and find where file path are saved.

fileToken - filestream transaction context. should be selected in separate command execute, and casted to Byte.

SQL Injections means when you do things like

RemoteSQLcmd = New SqlCommand("SELECT * FROM " & imTable(0) & " WHERE " 
    & imTable(0) & "_Code = " & "'" & imParam(1) 
    & "'", RemoteSQLConn, RemoteSQLtx)

in codebehind and imParam is an URL parameter value than someone may play a bad joke with you putting "'; DROP TABLE users;" in it which may resolve into

SELECT * FROM table WHERE table_Code = ''; DROP TABLE users;

Max Gontar 2009-12-31 17:35:34

Look I did what you told to do but i receive the same problemPlease look at my next post it has more particular tips for it

Lefteris Gkinis 2010-01-02 09:15:32

You should really accept the answer only of it's working...

Max Gontar 2010-01-02 10:50:33

OK Max I did that

Lefteris Gkinis 2010-01-02 11:07:11

You can select the transaction context in the same command. Doing it separately will degrade performance: additional round trip to the server.

Pawel Marciniak 2010-01-04 18:30:42

Answer 2

A:

Dim imGUID As Guid = Guid.NewGuid()
Dim imImage As Byte() = New Byte(imStream.Length) {}
Dim bytesRead As Integer = imStream.Read(imImage, 0, imStream.Length)
Dim SecondColumnNames As String = _
            imTable(1) & "_GUID, " & _
            imTable(1) & "_" & imTable(0) & "_ID"
Dim SecondFieldsValues As String = "'" & imGUID.ToString & "', '" & AbsRecord & "'"
RemoteSQLcmd = New SqlCommand("INSERT INTO " & imTable(1) & _
            " (" & SecondColumnNames & ")  VALUES (" & SecondFieldsValues & ")", RemoteSQLConn, RemoteSQLtx)
RemoteSQLcmd.Parameters.Add("@" & imTable(1) & "_GUID", SqlDbType.UniqueIdentifier).Value = imGUID
  RemoteSQLcmd.Parameters.Add("@" & imTable(1) & "_Image", SqlDbType.Image).Value = imImage
  RemoteSQLcmd.ExecuteNonQuery()
  RemoteSQLcmd = New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() FROM " & imTable(1), RemoteSQLConn, RemoteSQLtx)
Dim tokenObject As Object = RemoteSQLcmd.ExecuteScalar()
tokenReader = RemoteSQLcmd.ExecuteReader(CommandBehavior.SingleRow)
tokenReader.Read()

As you can see i put again the FROM claus.

But please look what i'm receiving in the reader

tokenReader.Depth 0

tokenReader.FieldCount 1

tokenReader.HasRows True

tokenReader.IsClosed False

tokenReader.Item In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.

tokenReader.RecordsAffected -1

As you can see here i have only one column to read and nothing else

I really don't know if that is helpful but anyway i put it there

tokenReader.VisibleFieldCount 1

Lefteris Gkinis 2010-01-02 09:30:45

Answer 3

A:

Look Max

I did what you told me but nothing

RemoteSQLcmd = New SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", RemoteSQLConn, RemoteSQLtx) 
Dim tokenObject As Object = RemoteSQLcmd.ExecuteScalar() 
tokenReader = RemoteSQLcmd.ExecuteReader(CommandBehavior.SingleRow) 
tokenReader.Read() 
fileToken = DirectCast(tokenReader(1), Byte()) 
filePathName = tokenReader.GetSqlString(3)

And the Transaction starts far upper of this command

And never stops

Lefteris Gkinis 2010-01-02 11:13:25

I really can't understand where do i have to expect to take the fileToken and the filePathNameI mean from what column 0,1,2,3,4,5 ...... where

Lefteris Gkinis 2010-01-02 11:15:57

I see. Try to tokenReader.Read() fileToken = DirectCast(tokenObject, Byte())

Max Gontar 2010-01-02 11:16:48

see one more update to my answer

Max Gontar 2010-01-02 11:31:01

Yes and the filePathName?

Lefteris Gkinis 2010-01-02 14:05:05

filePathName - filename from where we will open filestream. this value should be selected from some field of some your table. If you don't know what [table].[field] it is, see inside db table values and find where file path are saved. Can you look into database? do you have access with management studio or something like that, to write a direct sql query?

Max Gontar 2010-01-02 14:32:00

wait, are you sure you have this filePathName stored somewhere in db? This image, that you want to pass, where is it placed (phisically)?

Max Gontar 2010-01-02 14:37:03

look this pathname in my DB there no anywherei mean i can't find any pathname parameter in my table and especially in the specific file 'Image'the only path i found is in my DB in the Files TAB

Lefteris Gkinis 2010-01-02 15:11:45

Phisically the files from my DB are in the Server in the place C:\sqlDATA\.......

Lefteris Gkinis 2010-01-02 15:12:54

No, we have misunderstanding.. You say you want to store some image into db table. To do this you have to read it from file. And path to this file is filePathName. If it's in Files table, you should read it from there.

Max Gontar 2010-01-02 16:30:52

Well Dear Max, finally i found the solutionThe solution is not only one tip from the tips that you have send meThe solution is all the tips together and some moreBut mst importand is the place where we put the instructionsThe order of the instruction makes the deferentAnd the filePathName comes from the SQL server it self, it is a part of the FILESTREAM table and especially from the column which holds the image

Lefteris Gkinis 2010-01-03 08:07:03

Now i'm hold on the instruction Dim sqlFile As SqlFileStream = New SqlFileStream(filePathName, fileToken, FileAccess.Write)Which return me the 'Access Dinied' I suppose it is something with the SQL account that i'm using I will try to look at it

Lefteris Gkinis 2010-01-03 08:10:09

Ok then, thumbs up :) !

Max Gontar 2010-01-03 16:53:00

ansaurus

tags:

views:

answers:

TSQL/VB.NET - write stream to a table

related questions