Different codings on different computers

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
Alexander_73
Posts: 52
Joined: Mon 24 Mar 2008 13:22

Different codings on different computers

Post by Alexander_73 » Fri 08 May 2009 10:54

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 20 May 2009 08:36

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
Posts: 52
Joined: Mon 24 Mar 2008 13:22

Post by Alexander_73 » Wed 20 May 2009 11:20

- 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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 21 May 2009 13:04

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
Posts: 52
Joined: Mon 24 Mar 2008 13:22

Post by Alexander_73 » Thu 21 May 2009 15:33

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
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Fri 22 May 2009 09:57

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

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 28 May 2009 09:48

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.

Alexander_73
Posts: 52
Joined: Mon 24 Mar 2008 13:22

Post by Alexander_73 » Mon 08 Jun 2009 12:23

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.

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 10 Jun 2009 08:44

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
Posts: 52
Joined: Mon 24 Mar 2008 13:22

Post by Alexander_73 » Wed 10 Jun 2009 09:15

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

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Wed 10 Jun 2009 13:34

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

Post Reply