BLOB corrupted from previous version

fodox · Post by **fodox** » 25.01.2010, 16:51

I have an application in Delphi5 with Zeos 6.6.2-RC, and now I installed Delphi 2010 with Zeos 7.0.0-dev. I use SQlite version 3 with the same DLL.

The archives created with D5 are incompatible if read from D2010. In particular blob data is corrupted: data compressed with zlib can't be decompressed; jpeg doesn't match; sometimes the size I retrieve is less than the original.

I tried to store a stream of 1000 bytes in D5, filled five times with numbers 1..200. Reading the stream from D2010 the size is 999bytes and the sequence is similar but sometimes an error occurs.

How can I solve?

Thanks,
Fodox

liquidsphere · Post by **liquidsphere** » 25.01.2010, 21:32

Got same issue. I managed to overcome it by changing how zeos decodes and encodes strings.
First, in ZDbcSqliteUtils unit I added my own 2 functions:

Code: Select all

function DecodeStringToBin(Value: AnsiString): AnsiString;
begin
  if Odd(Length(Value)) then
    Value := '0'+Value;
  SetLength(Result, Length(Value) div 2);
  HexToBin(PAnsiChar(Value), PAnsiChar(Result), Length(Result));
end;

function EncodeStringToHex(Value: ansistring): ansistring;
begin
  SetLength(Result, Length(Value)*2);
  BinToHex(PAnsiChar(Value), PAnsiChar(Result), Length(Value));
  Result := AnsiQuotedStr(Result,'''');
end;

Then, in ZDbcSqliteResultset I changed GetBlob function:

Code: Select all

function TZSQLiteResultSet.GetBlob(ColumnIndex: Integer): IZBlob;
var
  Stream: TStream;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckBlobColumn(ColumnIndex);
{$ENDIF}
  Stream := nil;
  try
    if not IsNull(ColumnIndex) then
    begin
      if TZAbstractResultSetMetadata(Metadata).GetColumnType(ColumnIndex)
        <> stBinaryStream then
        Stream := TStringStream.Create(GetString(ColumnIndex))
      else
        Stream := TStringStream.Create(DecodeStringToBin(GetString(ColumnIndex)));      //My fix for blob reading
      Result := TZAbstractBlob.CreateWithStream(Stream)
    end
    else
      Result := TZAbstractBlob.CreateWithStream(nil);
  finally
    if Assigned(Stream) then
      Stream.Free;
  end;
end;

And in ZDbcSqliteStatement unit changed TZSQLitePreparedStatement.PrepareSQLParam function:

Code: Select all

function TZSQLitePreparedStatement.PrepareSQLParam(ParamIndex: Integer): string;
var
  Value: TZVariant;
  TempBytes: TByteDynArray;
  TempBlob: IZBlob;
begin
  TempBytes := nil;
  if InParamCount <= ParamIndex then
    raise EZSQLException.Create(SInvalidInputParameterCount);

  Value := InParamValues[ParamIndex];
  if DefVarManager.IsNull(Value)  then
    Result := 'NULL'
  else
  begin
    case InParamTypes[ParamIndex] of
      stBoolean:
            if SoftVarManager.GetAsBoolean(Value) then
               Result := '''Y'''
            else
               Result := '''N''';
      stByte, stShort, stInteger, stLong, stBigDecimal, stFloat, stDouble:
        Result := SoftVarManager.GetAsString(Value);
      stString, stBytes:
        Result := GetEscapeString(SoftVarManager.GetAsString(Value));
      stDate:
        Result := '''' + FormatDateTime('yyyy-mm-dd',
          SoftVarManager.GetAsDateTime(Value)) + '''';
      stTime:
        Result := '''' + FormatDateTime('hh:mm:ss',
          SoftVarManager.GetAsDateTime(Value)) + '''';
      stTimestamp:
        Result := '''' + FormatDateTime('yyyy-mm-dd hh:mm:ss',
          SoftVarManager.GetAsDateTime(Value)) + '''';
      stAsciiStream, stUnicodeStream, stBinaryStream:
        begin
          TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
          if not TempBlob.IsEmpty then
          begin
            if InParamTypes[ParamIndex] = stBinaryStream then
              Result := EncodeStringToHex(TempBlob.GetString) //My Fix for blob storing
            else
              Result := GetEscapeString(TempBlob.GetString);
          end
          else
            Result := 'NULL';
        end;
    end;
  end;
end;

It's not the most elegant solution but it works for me. It would be better to encode blobs to default Sqlite format.

Post by **mdaems** » 26.01.2010, 00:18

liquidsphere,

Seems like you have some sqlite experience.

It would be better to encode blobs to default Sqlite format.

Do you know how to do that?
Changes between 6.6 and 7.0 are probably due to the new string handling in Delphi 2009 we tried to handle.

W8 : before rushing into it : please check if patch http://fisheye2.atlassian.com/changelog/zeos/?cs=763 is already done in your copy. I only merged this to trunk a few days ago.

Mark

fodox · Post by **fodox** » 28.01.2010, 17:07

liquidsphere,
your solution doesn't work for me. It's ok if I store streams with D2010 and I retrieve it always with D2010, but I have archives created with D5 and now I must read them with D2010.

I tried the simple stream of 1000 bytes (five times 1..200). Saving the stream with D5 and reading with D2010, I obtain a stream with half size, all bytes are 0.

Thank you anyway.

fodox · Post by **fodox** » 02.02.2010, 16:52

I applied the patch, and my problem is in ZDBCsqliteUtils.pas unit, in function DecodeString, at this line:

Code: Select all

{$IFDEF DELPHI12_UP}
value := utf8decode(value);
{$ENDIF}

If I write blob data with D2010 and then I read, there is no corruption (apparently ...). But if I try to read when the data blob is written with D5 data is corrupted, becouse zeos lib in D5 doesn't write in utf8; in this case I have to remove the line shown up.

I don't know how to convert old archives of my applications with the new feature of zeos, so I would like to disable writing data in utf8 in D2010. Where can I find those lines of code?

wwywt · Post by **wwywt** » 20.12.2010, 08:53

i get the same problem
my data blob written with delphi2007
now i must read it with delphi2010...

Post by **mdaems** » 20.12.2010, 21:02

Who's taking the challenge of writing a convertor or a backward compatibility patch?
See discussion in http://zeos.firmos.at/viewtopic.php?t=3011

Mark

wwywt · Post by **wwywt** » 22.12.2010, 06:33

i have solve the problem of reading old vision of blob data.
it need test.it's my code;

Code: Select all

ZDbcSqLiteResultSet.pas

TZSQLiteResultSet = class(TZAbstractResultSet)
private
  FInfo:TStrings;
public
 constructor Create(PlainDriver: IZSQLitePlainDriver; Statement: IZStatement;
      SQL: string; Handle: Psqlite; StmtHandle: Psqlite_vm;
      ColumnCount: Integer; ColumnNames: PPAnsiChar; ColumnValues: PPAnsiChar; Info: TStrings = nil);

constructor TZSQLiteResultSet.Create(PlainDriver: IZSQLitePlainDriver;
  Statement: IZStatement; SQL: string; Handle: Psqlite;
  StmtHandle: Psqlite_vm; ColumnCount: Integer; ColumnNames: PPAnsiChar;
  ColumnValues: PPAnsiChar; Info: TStrings = nil);
begin
  FInfo := Info;
  inherited Create(Statement, SQL, TZSQLiteResultSetMetadata.Create(
    Statement.GetConnection.GetMetadata, SQL, Self));

  FHandle := Handle;
  FStmtHandle := StmtHandle;
  FPlainDriver := PlainDriver;
  ResultSetConcurrency := rcReadOnly;
  FColumnCount := ColumnCount;
  FColumnNames := ColumnNames;
  FColumnValues := ColumnValues;
  Open;
end;

function TZSQLiteResultSet.GetBlob(ColumnIndex: Integer): IZBlob;
var
  Stream: TStream;
begin
{$IFNDEF DISABLE_CHECKING}
  CheckBlobColumn(ColumnIndex);
{$ENDIF}
  Stream := nil;
  try
    if not IsNull(ColumnIndex) then
    begin
      if TZAbstractResultSetMetadata(Metadata).GetColumnType(ColumnIndex)
        <> stBinaryStream then
        Stream := TStringStream.Create(GetString(ColumnIndex))
      else
      begin
        if SameText(FInfo.Values['OldBLOB'], 'TRUE') then
        begin
          Stream := TStringStream.Create(DecodeStringAnsi(GetString(ColumnIndex)));
        end
        else
        begin
          Stream := TStringStream.Create(DecodeString(GetString(ColumnIndex)));
        end;
      end;
      Result := TZAbstractBlob.CreateWithStream(Stream)
    end
    else
      Result := TZAbstractBlob.CreateWithStream(nil);
  finally
    if Assigned(Stream) then
      Stream.Free;
  end;
end;

ZDbcSqLiteStatement.pas

function TZSQLiteStatement.CreateResultSet(const SQL: string; StmtHandle: Psqlite_vm;
   ColumnCount: Integer; ColumnNames: PPAnsiChar; ColumnValues: PPAnsiChar): IZResultSet;
var
  CachedResolver: TZSQLiteCachedResolver;
  NativeResultSet: TZSQLiteResultSet;
  CachedResultSet: TZCachedResultSet;
begin
  { Creates a native result set. }
  NativeResultSet := TZSQLiteResultSet.Create(FPlainDriver, Self, SQL, FHandle,
    StmtHandle, ColumnCount, ColumnNames, ColumnValues,self.info);
  NativeResultSet.SetConcurrency(rcReadOnly);

  { Creates a cached result set. }
  CachedResolver := TZSQLiteCachedResolver.Create(FPlainDriver, FHandle, Self,
    NativeResultSet.GetMetaData);
  CachedResultSet := TZCachedResultSet.Create(NativeResultSet, SQL,
    CachedResolver);

  { Fetches all rows to prevent blocking. }
  CachedResultSet.SetType(rtScrollInsensitive);
  CachedResultSet.Last;
  CachedResultSet.BeforeFirst;
  CachedResultSet.SetConcurrency(GetResultSetConcurrency);

  Result := CachedResultSet;
end;




ZDbcSqLiteUtils.pas

function EncodeStringAnsi(Value: Ansistring): Ansistring;

function DecodeStringAnsi(Value: Ansistring): Ansistring;

implementation

function EncodeStringAnsi(Value: Ansistring): Ansistring;
var
  I: Integer;
  SrcLength, DestLength: Integer;
  SrcBuffer, DestBuffer: PAnsiChar;
begin
  SrcLength := Length(Value);
  SrcBuffer := PAnsiChar(Value);
  DestLength := 2;
  for I := 1 to SrcLength do
  begin
    if SrcBuffer^ in [#0, '''', '%'] then
      Inc(DestLength, 2)
    else
      Inc(DestLength);
    Inc(SrcBuffer);
  end;

  SrcBuffer := PAnsiChar(Value);
  SetLength(Result, DestLength);
  DestBuffer := PAnsiChar(Result);
  DestBuffer^ := '''';
  Inc(DestBuffer);

  for I := 1 to SrcLength do
  begin
    if SrcBuffer^ = #0 then
    begin
      DestBuffer[0] := '%';
      DestBuffer[1] := '0';
      Inc(DestBuffer, 2);
    end
    else if SrcBuffer^ = '%' then
    begin
      DestBuffer[0] := '%';
      DestBuffer[1] := '%';
      Inc(DestBuffer, 2);
    end
    else if SrcBuffer^ = '''' then
    begin
      DestBuffer[0] := '''';
      DestBuffer[1] := '''';
      Inc(DestBuffer, 2);
    end
    else
    begin
      DestBuffer^ := SrcBuffer^;
      Inc(DestBuffer);
    end;
    Inc(SrcBuffer);
  end;
  DestBuffer^ := '''';
end;


function DecodeStringAnsi(Value: Ansistring): Ansistring;
var
  SrcLength, DestLength: Integer;
  SrcBuffer, DestBuffer: PAnsiChar;
begin
  SrcLength := Length(Value);
  SrcBuffer := PAnsiChar(Value);
  SetLength(Result, SrcLength);
  DestLength := 0;
  DestBuffer := PAnsiChar(Result);

  while SrcLength > 0 do
  begin
    if SrcBuffer^ = '%' then
    begin
      Inc(SrcBuffer);
      if SrcBuffer^ <> '0' then
        DestBuffer^ := SrcBuffer^
      else
        DestBuffer^ := #0;
      Inc(SrcBuffer);
      Dec(SrcLength, 2);
    end
    else
    begin
      DestBuffer^ := SrcBuffer^;
      Inc(SrcBuffer);
      Dec(SrcLength);
    end;
    Inc(DestBuffer);
    Inc(DestLength);
  end;
  SetLength(Result, DestLength);
end;

now when i want to read old data just use
zquery.Properties.Add('OldBLOB=true');
then it will use ansistring format .
i need more people test if the solution is OK.

PS :when I try to post blob data (an icon ) to my database i got an error.
any body know how to do?
my error message:SQL ERROR:unrecognized token:''''''

wwywt · Post by **wwywt** » 24.12.2010, 09:11

i think if we can found a functionin delphi2010 which result is the same with the function Encodingstring. then we can solve the problem of write old vision blob data.

function EncodeString(Value: string): string;
var
I: Integer;
SrcLength, DestLength: Integer;
SrcBuffer, DestBuffer: PAnsiChar;
begin
SrcLength := Length(Value);
SrcBuffer := PAnsiChar(Value);
DestLength := 2;
for I := 1 to SrcLength do
begin
if SrcBuffer^ in [#0, '''', '%'] then
Inc(DestLength, 2)
else
Inc(DestLength);
Inc(SrcBuffer);
end;

SrcBuffer := PAnsiChar(Value);
SetLength(Result, DestLength);
DestBuffer := PAnsiChar(Result);
DestBuffer^ := '''';
Inc(DestBuffer);

for I := 1 to SrcLength do
begin
if SrcBuffer^ = #0 then
begin
DestBuffer[0] := '%';
DestBuffer[1] := '0';
Inc(DestBuffer, 2);
end
else if SrcBuffer^ = '%' then
begin
DestBuffer[0] := '%';
DestBuffer[1] := '%';
Inc(DestBuffer, 2);
end
else if SrcBuffer^ = '''' then
begin
DestBuffer[0] := '''';
DestBuffer[1] := '''';
Inc(DestBuffer, 2);
end
else
begin
DestBuffer^ := SrcBuffer^;
Inc(DestBuffer);
end;
Inc(SrcBuffer);
end;
DestBuffer^ := '''';
end;

Post by **mdaems** » 25.12.2010, 09:00

wwywt,
I don't understand the meaning of you second piece of code.

BUT:
If you tested reading BLOBs from an old database with your first patch, I think it's the right solution. I hope you can also add the 'old encoding on write' to the patch.
The code doesn't break behaviour when the parameter is not set. So for me it's safe enough to commit to testing branch.

Thanks for the good job!

Mark

wwywt · Post by **wwywt** » 27.12.2010, 05:00

i found the same function encodingstring get the different result between delphi2007 and delphi2010 . i think it maybe the reson of cause write old data error. i'm still try to solve it. if i have a solution i will post to here.

Post by **mdaems** » 27.12.2010, 09:30

Isn't the problem in TZSQLitePreparedStatement.PrepareSQLParam function? I think there you could do the same (but inverted) trick as you did in TZSQLiteResultSet.GetBlob. (which means using your EncodeAnsi function when the oldBLOB setting is used)

Mark

wwywt · Post by **wwywt** » 27.12.2010, 11:04

emm.. i found may be the problem is caused by an ansistring convert to a string. see my test demo in delphi2010.

Code: Select all

function StreamToString(mStream: TStream): AnsiString;
var
I: Integer;
begin
Result := '';
if not Assigned(mStream) then Exit;
SetLength(Result, mStream.Size);
for I := 0 to Pred(mStream.Size) do try
mStream.Position := I;
mStream.Read(Result[Succ(I)], 1);
except
Result := '';
end;
end;

function EncodeString(Value: ansistring): ansistring;
var
  I: Integer;
  SrcLength, DestLength: Integer;
  SrcBuffer, DestBuffer: PansiChar;
begin
  SrcLength := Length(Value);
  SrcBuffer := PansiChar(Value);
  DestLength := 2;
  for I := 1 to SrcLength do
  begin
    if SrcBuffer^ in [#0, '''', '%'] then
      Inc(DestLength, 2)
    else Inc(DestLength);
    Inc(SrcBuffer);
  end;

  SrcBuffer := PansiChar(Value);
  SetLength(Result, DestLength);
  DestBuffer := PansiChar(Result);
  DestBuffer^ := '''';
  Inc(DestBuffer);

  for I := 1 to SrcLength do
  begin
    if SrcBuffer^ = #0 then
    begin
      DestBuffer[0] := '%';
      DestBuffer[1] := '0';
      Inc(DestBuffer, 2);
    end
    else if SrcBuffer^ = '%' then
    begin
      DestBuffer[0] := '%';
      DestBuffer[1] := '%';
      Inc(DestBuffer, 2);
    end
    else if SrcBuffer^ = '''' then
    begin
      DestBuffer[0] := '''';
      DestBuffer[1] := '''';
      Inc(DestBuffer, 2);
    end
    else
    begin
      DestBuffer^ := SrcBuffer^;
      Inc(DestBuffer);
    end;
    Inc(SrcBuffer);
  end;
  DestBuffer^ := '''';
end;


procedure TForm1.Button1Click(Sender: TObject);
var a,c:AnsiString;
    b:string;
    mm:TFileStream;
begin
  mm:=TFileStream.Create('c:\skype.ico',fmOpenRead);
  try
    mm.Position:=0;
    a:=StreamToString(mm);
    a:=EncodeString(a);
    OutputDebugStringA(PAnsiChar('a:'+a));
    b:=string(a);
    OutputDebugString(PChar('b:'+b));
    c:='';
    c:=AnsiString(b);
    OutputDebugStringA(PAnsiChar('c:'+c));
  finally
    mm.free;
  end;
end;

i compare the out put info. the string b is not the same of ansistring a.
maybe it's the reason:when encodingstringansi function get a correct sql string. the sql string(ansi) was convert to a string(unicode). in this covert process the sql string has been changed!so i can't write a correct data to database.i don't know how to solve this problem.

Post by **mdaems** » 27.12.2010, 13:38

You should handle BLOB's using SQL parameters. Just take it as a 'best practise'.

That's where the difficulty with Delphi 2009 comes from. Inserting a BLOB value inside an SQL string just doesn't work anymore (unless it is a Character LOB maybe, as simple characters can be converted during the Aansistring to string conversion). Believe me, it's not only a SqlLite problem. The new string format just isn't usable to handle BLOB's anymore.
When updating the BLOB using parameters you will probably need to change the TZSQLitePreparedStatement.PrepareSQLParam function to use old encoding. (If you want to discuss this, I'm online on IM most of this week, I think)

Mark

wwywt · Post by **wwywt** » 28.12.2010, 03:29

MARK
My pool english...hehe...i will contact you on MSN.
wwywt.