Different codings on different computers

[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?

[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.

[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?

[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.

[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

[Dimon]

We are investigating this problem. As soon as we solve the problem we will let you know.

[Dimon]

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.

[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

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.

[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;

[Alexander_73]

Mmm... I'm sorry, what is Marshal.PtrToStringUni in Delphi Win32? I know it only as .Net class.

[Dimon]

You should add the CLRClasses unit to the uses clause to use this code.