Page 1 of 1
Different codings on different computers
Posted: Fri 08 May 2009 10:54
by Alexander_73
On MS SQL Server 2008 there is a stored procedure returning the table in a kind of a xml-text:
Code: Select all
CREATE PROCEDURE [dbo].[atisp_GetCreateTable](@FieldsAttrs VARCHAR(5000), @Fields VARCHAR(2500), @TableName VARCHAR(40), @IxCount INT) AS BEGIN
DECLARE @S VARCHAR(8000), @BeforeXML VARCHAR(60), @BetweenXML VARCHAR(40), @AfterXML VARCHAR(120);
SET @BeforeXML='select CAST(''';
SET @BetweenXML='''+isnull((';
SET @AfterXML=' for xml path(''ROW''), root(''ROWDATA'')), '''') + '''' as xml)';
SET @S='SELECT '+@Fields+' FROM '+@TableName;
IF @IxCount=1
SET @S=@S+' ORDER BY 1'
ELSE
SET @S=@S+' ORDER BY 1,2';
SET @S=@BeforeXML+@FieldsAttrs+@BetweenXML+@S+@AfterXML;
EXEC(@S)
END
END
On client there is an object TMSStoredProc to save stored procs' results to a file.
If I save the data to a file so:
Code: Select all
F:=TFileStream.Create(TblFileName,fmCreate);
with F do try
s:=FMSProc.Fields[0].AsString;
s:=''+s;
i:=Length(s);
F.Write(s[1],I)
finally
Free
end
Then file is normal xml-table which is able to open any TClientDataSet. Such code is not good.
But if I save the data to a file so:
Code: Select all
F:=TFileStream.Create(TblFileName,fmCreate);
with F do try
s:='';
i:=Length(s);
F.Write(s[1],I);
TBlobField(FMSProc.Fields[0]).SaveToStream(F);
finally
Free
end
Begins any mysticism. On my computer (WinXP) this program normally creates the same xml-file, but in working system (Windows 2003 Server, Windows 2008 Server) in the file is any another coding (maybe UNICODE?), data writing per 2 bytes on char (on text view "< D A T A P A C K E T", but not "<DATAPACKET"), and no any TClientDataSet may open this file. Why so?
Posted: Wed 20 May 2009 08:36
by Dimon
I could not reproduce the problem.
Please send me the following information:
- the code page used on the computer on that the problem is raised;
- the configuration of your MS SQL server;
- the exact version of your IDE.
Posted: Wed 20 May 2009 11:20
by Alexander_73
- Code page on the computer is Windows 1251, all russian, Windows Server 2008 x64.
- Microsoft SQL Server Enterprise Evaluation Edition (64-bit); Version 10.0.1763.0; Language English (USA); Server Collation Cyrillic_General_CI_AS; Default Language Russian; In DataBase Properties Collation SQL_Latin1_General_CP1251_CI_AS.
- CodeGear™ RAD Studio 2007 Enterprise Version 11.0.2902.10471, SDAC Professional Edition 4.7.0.46.
On my computer with windows XP also Windows 1251 all russian, but problem is not raised. May be, problem on system with x64?
Posted: Thu 21 May 2009 13:04
by Dimon
The point is that XML fields in SQL Server keep data as Unicode strings, and therefore the SaveToStream method write data in Unicode.
To solve the problem you should use the AsString property to get correct value for writing to file, as you did in your first sample.
Posted: Thu 21 May 2009 15:33
by Alexander_73
Operations with AsString too strongly fragments memory when the many of big tables are made. Sometimes raises Out of memory on the computer with Windows Server 2008 and 16 GB memory, but on the my computer with 2 GB always enough for operating with Stream
Posted: Fri 22 May 2009 09:57
by Dimon
We are investigating this problem. As soon as we solve the problem we will let you know.
Posted: Thu 28 May 2009 09:48
by Dimon
Alexander_73 wrote:Operations with AsString too strongly fragments memory when the many of big tables are made. Sometimes raises Out of memory on the computer with Windows Server 2008 and 16 GB memory, but on the my computer with 2 GB always enough for operating with Stream
Please, try to download the latest SDAC build (4.70.0.47) and check if this problem still exists, if you use the AsString property to get correct value for writing to file.
Posted: Mon 08 Jun 2009 12:23
by Alexander_73
Dimon wrote:Alexander_73 wrote:Operations with AsString too strongly fragments memory when the many of big tables are made. Sometimes raises Out of memory on the computer with Windows Server 2008 and 16 GB memory, but on the my computer with 2 GB always enough for operating with Stream
Please, try to download the latest SDAC build (4.70.0.47) and check if this problem still exists, if you use the AsString property to get correct value for writing to file.
I have tried. The problem with out of memory still has remained.
Posted: Wed 10 Jun 2009 08:44
by Dimon
To solve the problem you can use the following code:
Code: Select all
const
MaxBufSize = $F000;
var
F: TFileStream;
Blob: TBlob;
Count, BufSize, N, Position: Cardinal;
Buffer: Pointer;
sa: AnsiString;
begin
F := TFileStream.Create(TblFileName,fmCreate);
try
Blob := MSStoredProc.GetBlob(MSStoredProc.Fields[0]);
Count := Blob.Size;
Position := 0;
if Count > MaxBufSize then BufSize := MaxBufSize else BufSize := Count;
GetMem(Buffer, BufSize);
try
while Count 0 do begin
N := Blob.Read(Position, BufSize, Buffer);
sa := AnsiString(Marshal.PtrToStringUni(Buffer, N div 2));
F.Write(sa[1], Length(sa));
Dec(Count, N);
Inc(Position, N);
end;
finally
FreeMem(Buffer, BufSize);
end;
finally
F.Free;
end;
end;
Posted: Wed 10 Jun 2009 09:15
by Alexander_73
Mmm... I'm sorry, what is Marshal.PtrToStringUni in Delphi Win32? I know it only as .Net class.
Posted: Wed 10 Jun 2009 13:34
by Dimon
You should add the CLRClasses unit to the uses clause to use this code.