Page 1 of 1

MySQl 4.1/5 & BLOB fields

Posted: 23.08.2006, 13:17
by aperger
Hi developers,

I can read and write BLOB field into my databases with ZeOS.
Unfortunatelly if I use mysql-4.1/mysql-5 protocol on a MySQL server (4.1/5), I can not get back the same content of the BLOB field as it was saved. The following procedures were tested and used with the following protocols: firebird, postgresql, ado (MSSQL).

Can anybody help?

Code: Select all

// (De-)Compress functions
procedure CompressStream(aSource,aTarget:TStream);
var
	compStream:TCompressionStream;
begin
  compStream:=TCompressionStream.Create(clFastest,aTarget);
  try
    aSource.Position:=0;
    compStream.CopyFrom(aSource,aSource.Size);
    compStream.CompressionRate;
  finally
    compStream.Free;
  end;
end;

procedure DecompressStream(aSorce,aTarget:TStream);
var
	decompStream:TDecompressionStream;
  nRead:integer;
  Buffer:array[0..1023] of Char;
  Count:integer;
begin
  decompStream:=TDecompressionStream.Create(aSorce);
  try
    if aSorce.Size > 0 then begin
      decompStream.Position:=0;
      Count:=1024;
      repeat

        try
          nRead:=decompStream.Read(Buffer,Count);
        except
          on E:Exception do begin
            raise Exception.Create('Decompression error when write ir to stream!');
          end;
        end;

        try
          aTarget.WriteBuffer(Buffer,nRead);
        except
          on E:Exception do begin
            raise Exception.Create('Decompression error when write ir to stream!');
          end;
        end;

      until nRead=0;
    end;
  finally
    decompStream.Free;
  end;
end;

procedure ReadBlobField(ImageField:TBlobField;Image:TImage;DeCompress:boolean);
var
  memoStream:TMemoryStream;
  compStream:TMemoryStream;
begin
	if ImageField.IsNull then begin
   	memoStream:=TMemoryStream.Create;
    Image.AutoSize:=true;
    Image.Picture.Bitmap.LoadFromStream(memoStream);
    Image.AutoSize:=false;
		exit;
	end;

  memoStream:=TMemoryStream.Create;
  if DeCompress then compStream:=TMemoryStream.Create else compStream:=nil;

  try
    // blob kiolvasása

    (* DbExpress
    bstream:=TSQLBlobStream.Create(ImageField,bmReadWrite);
    bstream.ReadBlobData;
    bstream.Position:=0;
    try
      if DeCompress then begin
        compStream.LoadFromStream(bstream);
        compStream.Position:=0;
        DecompressStream(compStream,memoStream);
      end else begin
        memoStream.LoadFromStream(bstream);
      end;
    except
      on E:Exception do begin
        ShowPSError('Blob field reading error: '+#10+E.Message);
        memoStream.Size:=0;
        if DeCompress then compStream.Size:=0;
      end;
    end;
    bstream.Free;
    *)
    try
      if DeCompress then begin
        ImageField.SaveToStream(compStream);
        compStream.Position:=0;
        DecompressStream(compStream,memoStream);
      end else begin
        ImageField.SaveToStream(memoStream);
      end;
    except
      on E:Exception do begin
        ShowPSError('Blob field reading error: '+#10+E.Message);
        memoStream.Size:=0;
        if DeCompress then compStream.Size:=0;
      end;
    end;


    try
      Image.AutoSize:=true;
      memoStream.Position:=0;
      Image.Picture.Bitmap.FreePixmap;
      Image.Picture.Bitmap.FreeImage;
      if memoStream.Size>0 then begin
        memoStream.Position:=0;
        Image.Picture.Bitmap.LoadFromStream(memoStream);
      end;
      Image.AutoSize:=false;
    except
      on E:Exception do ShowPSError('Image creating error: '+#10+E.Message);
    end;
  finally
     memoStream.Free;
     if DeCompress then compStream.Free;
  end;
end;

procedure WriteBlobParam(ImageParam:TParam;Image:TImage;Compress:boolean);
var
	memoStream:TMemoryStream;
	compStream:TMemoryStream;
begin
	memoStream:=TMemoryStream.Create;
	if Compress then compStream:=TMemoryStream.Create else compStream:=nil;
	try
		Image.Picture.Bitmap.SaveToStream(memoStream);
		if memoStream.Size=0 then begin
      if AnsiContainsText(DM.conDB.Protocol,'mssql') or
        (AnsiContainsText(DM.conDB.Database, 'SQLOLEDB')) then begin
			  ImageParam.Value:=0
      end else begin
        ImageParam.Value:=Null;
        // ImageParam.LoadFromStream(memoStream,ImageParam.DataType);
      end;
		end else begin
			// memoStream.Size;
			memoStream.Position:=0;
      if Compress then begin
        CompressStream(memoStream,compStream);
        compStream.Position:=0;
        ImageParam.LoadFromStream(compStream,ImageParam.DataType);
      end else begin
        memoStream.Position:=0;
        ImageParam.LoadFromStream(memoStream,ImageParam.DataType);
      end;
      memoStream.Position:=0;
		end;
	finally
		memoStream.Free;
		if Compress then compStream.Free
	end;
end;

Posted: 09.10.2006, 10:44
by aperger
Hi Developers,

Rev.108 cause the same problem (D7 - CLX). How can I save and restore XML file or IMAGE into/from a MySQL database?

Posted: 09.10.2006, 11:11
by mdaems
Hi Aperger,

Do you know what gets in the database? Does anything get in? Can you see what's different ? Is it just some bytes or does everything disappear?

It may be nice to know what is actually fed to these procedures. What's the query and the field properties of the field you read.
Do you have a very easy program to test this with? Like inserting an xml file into a one row/ one field table and reading it back?

I hope somebody with knowledge of mysql and blobs can help you then. Or does everybody have trouble with blobs?

Mark

Posted: 09.10.2006, 11:18
by aperger
Hi Mark,

The problem:
I. If the contect of the BLOB field is an XML file, the XML parser can not parse it.

II. If the content of the BLOB field is an IMAGE, the result is incorrect on the screen. The biggest part of the picture is black at the top.

I don't know where is the bug at saving or at reading.

Attila

ps.: If the picture is small, everything looks ok.

Posted: 09.10.2006, 11:23
by aperger
Hi Mark (Part2)

I use the same source to save streams into BLOB with ZeOS. If I use mysql protocol the code is not working, If I use ado, postgresql, firebird the program is working.

CODE at the top : procedure ReadBlobField, procedure WriteBlobParam