Prev: Script to check the Syntax on all stored procedures in a database
Next: Sql Server on VMWare
From: jh on 7 Nov 2009 04:11
SQL 2008 Express running in Virtual PC on Windows XP Pro, accessed from a
"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

From: Erland Sommarskog on 8 Nov 2009 15:07
jh (NIE_SPAMUJ_jh(a)radio.kielce.pl) writes:
> SQL 2008 Express running in Virtual PC on Windows XP Pro, accessed from a
> "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?

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?
No promises, but maybe we are able to spot something.


--
Erland Sommarskog, SQL Server MVP, esquel(a)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

From: jh on 8 Nov 2009 16:15
Uzytkownik "Erland Sommarskog" <esquel(a)sommarskog.se> napisal w wiadomosci
news:Xns9CBDD6E603874Yazorman(a)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

From: jh on 11 Nov 2009 05:30
Uzytkownik "Erland Sommarskog" <esquel(a)sommarskog.se> napisal w wiadomosci
news:Xns9CBFEDC76AEEYazorman(a)127.0.0.1...
> 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

From: Erland Sommarskog on 11 Nov 2009 08:21
jh (NIE_SPAMUJ_jh(a)radio.kielce.pl) writes:
> 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, esquel(a)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
 |  Next  |  Last
Pages: 1 2 3
Prev: Script to check the Syntax on all stored procedures in a database
Next: Sql Server on VMWare