SQL 2008 - FILESTREAM OpenSQLFilestream invalid handle
jh
"real" machine with Windows XP Pro via TCP/IP and sql authentication (the
server has enabled mixed mode). FILESTREAM is enabled for remote client
streaming, database allow for Full Access with FILESTREAM. The table is
created properly and there's no problem with "traditional" reading/writing
BLOBs of FILESTREAM column. I use my own Win32 application developed in
Delphi 2007 Pro with AnyDAC 2 library, SQL Server Native Client 10.0.
What I do:
- I'm beginning a transaction and set the dataset in edit mode
- I get a proper PathName() for a BLOB FILESTREAM column (I've created a
view for that table with PathName() column)
- I read a transaction token (varbinary data - array of byte) and the value
looks correct.
- I call imported OpenSQLFilestream function to get handle with read/write
mode. LastError (6) measn INVALID FILE HANDLE, and the value is AFAIR
0xFFFFFFFF.
Most examples I saw do the same (although they're in a managed code C# or
VB.NET but they import the same Win32 OpenSQLFilestream function). Do you
have any idea why I cannot get a valid file handle?
Jacek
Erland Sommarskog
assist with, since I don't know Delphi. I would guess that you have some
error around one of the parameters to OpenSqlFilestream or the
import of it.
Could you post the code, including how you import OpenSqlFilestream?
No promises, but maybe we are able to spot something.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
jh
news:Xns9CBDD6E60...@127.0.0.1...
> I suspect this is a "how do I do this in Delphi", which is not easy to
> assist with, since I don't know Delphi. I would guess that you have some
> error around one of the parameters to OpenSqlFilestream or the
> import of it. Could you post the code, including how you import
> OpenSqlFilestream?
OK. Here is declaration of OpenSqlFilestream:
<code>
function OpenSqlFilestream(
Path: PChar;
Access: Cardinal;
Options: Cardinal;
txnToken: TtxnToken;
txnTokenLenght: Cardinal;
AllocationSize: PInt64): THandle stdcall; external 'sqlncli10.dll';
</code>
where:
type
TtxnToken = array of byte;
Some consts I use:
<code>
const
SQL_FILESTREAM_READ = 0;
SQL_FILESTREAM_WRITE = 1;
SQL_FILESTREAM_READWRITE = 2;
SQL_FILESTREAM_OPEN_NONE = $00000000;
SQL_FILESTREAM_OPEN_FLAG_ASYNC = $00000001;
SQL_FILESTREAM_OPEN_FLAG_NO_BUFFERING = $00000002;
SQL_FILESTREAM_OPEN_FLAG_NO_WRITE_THROUGH = $00000004;
SQL_FILESTREAM_OPEN_FLAG_SEQUENTIAL_SCAN = $00000008;
SQL_FILESTREAM_OPEN_FLAG_RANDOM_ACCESS = $00000010;
</code>
My BLOB reading proc:
<code>
procedure TSQLFileStream.ReadFromSQL(AStream: TStream);
const
BufferSize = 8192;
var
Buffer: PChar;
ReadBytes: Cardinal;
TokenField: TVarBytesField; // a field with array of byte value
FHandle: THandle;
er: Cardinal;
begin
// Start a transaction
FQuery.Connection.StartTransaction;
// Get a transaction token
FTokenQuery.Open('SELECT GET_FILESTREAM_TRANSACTION_CONTEXT() as Token');
TokenField := TVarBytesField(FTokenQuery.FieldByName('Token'));
// Get a file handle
FHandle := OpenSqlFilestream(
PChar(FSQLFilePath),
SQL_FILESTREAM_READ,
SQL_FILESTREAM_OPEN_NONE,
TokenField.Value,
TokenField.Size,
nil);
FTokenQuery.Close;
if (FHandle <> 0) and (FHandle <> $FFFFFFFF) then
// Valid handle
begin
// Prepare buffer
GetMem(Buffer, BufferSize);
try
// Read the file while no error and ReadBytes > 0, ReadBytes is
evaluated after reading
while Windows.ReadFile(FHandle, Buffer^, BufferSize, ReadBytes, nil)
and (ReadBytes > 0) do
AStream.Write(Buffer^, ReadBytes)
finally
// Free buffer memory
FreeMem(Buffer);
end;
// Close file handle
CloseHandle(FHandle);
end else
begin
er := GetLastError;
MessageBox(0, PChar('Error ' + IntToStr(er)), 'End', 0);
end;
// End the transaction
FQuery.Connection.Commit;
end;
</code>
Regards,
Jacek
Erland Sommarskog
> Uzytkownik "Erland Sommarskog" <esq...@sommarskog.se> napisal w wiadomosci
> news:Xns9CBDD6E60...@127.0.0.1...
>> I suspect this is a "how do I do this in Delphi", which is not easy to
>> assist with, since I don't know Delphi. I would guess that you have some
>> error around one of the parameters to OpenSqlFilestream or the
>> import of it. Could you post the code, including how you import
>> OpenSqlFilestream?
>
> OK. Here is declaration of OpenSqlFilestream:
I saw nothing that stood out. However, I found this in Books Online:
If the function succeeds, the return value is an open handle to a
specified file. If the function fails, the return value is
INVALID_HANDLE_VALUE. For extended error information, call
GetLastError().
So I would suggest that you add a call to GetLastError() to get better
information.
I would also suggest that you verify that PChar(FSQLFilePath) gives you
a pointer to a Unicode value.
jh
> I saw nothing that stood out. However, I found this in Books Online:
>
> If the function succeeds, the return value is an open handle to a
> specified file. If the function fails, the return value is
> INVALID_HANDLE_VALUE. For extended error information, call
> GetLastError().
>
> So I would suggest that you add a call to GetLastError() to get better
> information.
That's what you can find in my code:
if (FHandle <> 0) and (FHandle <> $FFFFFFFF) then
// Valid handle
begin
// here some routines
end else
begin
er := GetLastError;
MessageBox(0, PChar('Error ' + IntToStr(er)), 'End', 0);
end;
> I would also suggest that you verify that PChar(FSQLFilePath) gives you a
> pointer to a Unicode value.
I'll try this. From my antother test project:
CREATE TABLE [dbo].[FSBLOBs](
[BLOBID] int IDENTITY(1,1) NOT NULL Primary Key,
[BLOBName] varchar(50) NULL,
[BLOBData] varbinary(max) filestream NULL,
[BLOBPath] varchar(max) NULL,
[rowguid] uniqueidentifier NOT NULL rowguidcol unique
)
CREATE TRIGGER [dbo].[T_FSBLOBS_IU] ON [dbo].[FSBLOBs]
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON;
if UPDATE([BLOBData])
UPDATE [dbo].[FSBLOBs]
SET [BLOBPath] = CONVERT(varchar(max), i.[BLOBData].PathName())
FROM [FSBLOBs] AS fs
INNER JOIN INSERTED AS i
ON fs.[BLOBID] = i.[BLOBID]
SET NOCOUNT OFF;
END
I fill the path in a table trigger and it's not a Unicode data. Thank you
for the tip.
Best regards,
Jacek
Erland Sommarskog
> That's what you can find in my code:
>
> if (FHandle <> 0) and (FHandle <> $FFFFFFFF) then
> // Valid handle
> begin
> // here some routines
> end else
> begin
> er := GetLastError;
> MessageBox(0, PChar('Error ' + IntToStr(er)), 'End', 0);
> end;
Good! But did you ever tell us what the message box said? :-)
>> I would also suggest that you verify that PChar(FSQLFilePath) gives you a
>> pointer to a Unicode value.
>...
> I fill the path in a table trigger and it's not a Unicode data. Thank you
> for the tip.
It doesn't whether the filename is Unicode in the table. But value you
pass to OpenSqlFilestream must be a pointer to a Unicode string.
Eh, wait a minute. I looked your code last night about the last thing I
did. I might have been a little tired... I can't see that you set
FSQLFilePath anywhere. You should get the value with the PathName
function from SQL. That part seems to be missing. (Disclaimer: I'm at
work now, and I don't have the time look in the SQL 2008 docs, so I talk
from memory.(
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
jh
>> That's what you can find in my code:
> Good! But did you ever tell us what the message box said? :-)
Yes, I did in my first post: "LastError (6) means INVALID FILE HANDLE"
> It doesn't whether the filename is Unicode in the table. But value you
> pass to OpenSqlFilestream must be a pointer to a Unicode string.
I'll try this, thank you.
> Eh, wait a minute. I looked your code last night about the last thing I
> did. I might have been a little tired... I can't see that you set
> FSQLFilePath anywhere. You should get the value with the PathName function
> from SQL. That part seems to be missing. (Disclaimer: I'm at work now, and
> I don't have the time look in the SQL 2008 docs, so I talk from memory.(
In my last post I wrote about the trigger: SET [BLOBPath] =
CONVERT(varchar(max), i.[BLOBData].PathName()) so the proper path is filled
everytime I store BLOB in a table and it uses PathName() method of
FILESTREAM column.
Regards,
Jacek
Bob Beauchemin
the one where SQL Server is running?). If so, don't forget to open the port
for SMB on the SQL Server box.
You would think that not having the port open would produce a different
error message though.
Cheers,
Bob Beauchemin
SQLskills
"jh" <NIE_SP...@radio.kielce.pl> wrote in message
news:hdei5s$ar4$1...@inews.gazeta.pl...
jh
wiadomosci news:uWLxU9tY...@TK2MSFTNGP06.phx.gbl...
> Are you trying to use the handle off-host (e.g. from a machine other than
> the one where SQL Server is running?).
I tried running the program from the remote machine and from the server
itself. Sharing files and printers was enabled, also tried with firewall
turned off.
Regars,
Jacek
Erland Sommarskog
> In my last post I wrote about the trigger: SET [BLOBPath] =
> CONVERT(varchar(max), i.[BLOBData].PathName()) so the proper path is
> filled everytime I store BLOB in a table and it uses PathName() method
> of FILESTREAM column.
idea. What if SQL Server decides to reorganize the data and make the
path invalid?
I would suggest that you try to retrieve the path at run-time.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
jh
> I'm not sure that I see the point with this. Or even if it is a good idea.
> What if SQL Server decides to reorganize the data and make the path
> invalid?
Well, the path is build on FILESTREAM file group allocation and the server
cannot change it like in any other database files. Server share path cannot
be change - you can change it in server service settings. File name cannot
be change because it's based on GUID stored in the table... So I can't see
no reason for changing the path by the server itself. What's more, it's only
for testing purpose and finally I plan to access the data via view, when I
can call for FilePath() method instead of an additinal column in a table.
Regards,
Jacek
Erland Sommarskog
> dUzytkownik "Erland Sommarskog" <esq...@sommarskog.se> napisal w
you computed in the trigger?
Also, did you store any values before you made the configuration changes?
One operation that definitely can change the path is if you backup
the database and restore it elsewhere.
jh
> But did you test calling PathName() directly, rather than using the value
> you computed in the trigger?
Yes, the same problem.
> Also, did you store any values before you made the configuration changes?
No, I've recreated the tabel and inserted the data once more.
I've installed Visual Studio 2008 Express on my (developer) machine to test
if it's a problem in my library it but unfortunatelly it cannot connect
remote database - only local connections :(
Regards,
Jacek
Erland Sommarskog
> Uzytkownik "Erland Sommarskog" <esq...@sommarskog.se> napisal w wiadomosci
> news:Xns9CC15A656...@127.0.0.1...
>> But did you test calling PathName() directly, rather than using the value
>> you computed in the trigger?
>
> Yes, the same problem.
and not a Unicode string for the filename. But that is really a guess.