[patch_done] mssql Blobs are limited to 4096 bytes

[ludob]

Wrong option is passed to dbsetopt to set DBTEXTLIMIT. Patch attached.

This is probably not relevant any more but in previous Zeoslib versions the DBTEXTLIMIT was taken from src/plain/ZPlainDbLibConstants.pas where it was defined as 7 instead of src/plain/ZPlainDbLibDriver.pas where it was defined as 4. The order of the units in the uses clause of ZDbcDblib made that the wrong definition was taken.

[EgonHugeist]

ludob,

You're right. Patch done Rev. 1436 \testing and Rev.1437 \testing-egonhugeist

May i ask you something? Aren't you on of the fpc core which is working on the db-units? If yes then i've got some small questions to you. Or am i totaly wrong?

Michael

[ludob]

I'm not on fpc "core" but indeed actively contributing to the db-units. You'll see my name, Ludo Brands, in some of the units and on mantis.

Questions? No problem.

Ludo

[EgonHugeist]

ludob,

core or not, it doesn't matter. Great job with the FPC at all!

Lodo, we've got some test-cases which are mostly upgraded for the fpc too. But some tests do fail for the fpc. This one is an example:

Code: Select all

procedure TZTestSQLTypesCase.TestDateTypes;
var
  NowDate: TDateTime;
begin
  NowDate := Now();

  Query.SQL.Text := 'DELETE FROM date_values WHERE d_id=:Id';
  CheckEquals(1, Query.Params.Count);
  CheckEquals('Id', Query.Params[0].Name);
  Query.Params[0].DataType := ftInteger;
  Query.Params[0].Value := TEST_ROW_ID;
  Query.ExecSQL;

  // Query.RequestLive := True;
  Query.SQL.Text := 'SELECT * FROM date_values WHERE d_id=:Id';
  CheckEquals(1, Query.Params.Count);
  CheckEquals('Id', Query.Params[0].Name);
  Query.Params[0].DataType := ftInteger;
  Query.Params[0].Value := TEST_ROW_ID;
  Query.Open;

  CheckEquals(0, Query.RecordCount);
  Query.Insert;

  Query.FieldByName('d_id').AsInteger := TEST_ROW_ID;

  if StartsWith(Protocol, 'oracle') or (Protocol = 'mssql') or (Protocol = 'sybase') then
  begin
    CheckEquals(Ord(ftDateTime), Ord(Query.FieldByName('d_date').DataType));
    CheckEquals(Ord(ftDateTime), Ord(Query.FieldByName('d_time').DataType))
  end
  else
  begin
    CheckEquals(Ord(ftDate), Ord(Query.FieldByName('d_date').DataType));
    CheckEquals(Ord(ftTime), Ord(Query.FieldByName('d_time').DataType));
  end;
  CheckEquals(Ord(ftDateTime), Ord(Query.FieldByName('d_datetime').DataType));
  CheckEquals(Ord(ftDateTime), Ord(Query.FieldByName('d_timestamp').DataType));

  Query.FieldByName('d_date').AsDateTime := NowDate;
  Query.FieldByName('d_time').AsDateTime := NowDate;
  Query.FieldByName('d_datetime').AsDateTime := NowDate;
  Query.FieldByName('d_timestamp').AsDateTime := NowDate;

  if StartsWith(Protocol, 'oracle') or (Protocol = 'mssql') or (Protocol = 'sybase') then
  begin
    CheckEquals(NowDate, Query.FieldByName('d_date').AsDateTime, 1e-10);
    CheckEquals(NowDate, Query.FieldByName('d_time').AsDateTime, 1e-10);
  end
  else
  begin
    CheckEquals(Trunc(NowDate),
      Trunc(Query.FieldByName('d_date').AsDateTime), 1e-10);
    CheckEquals(Frac(NowDate),
      Frac(Abs(Query.FieldByName('d_time').AsDateTime)), 1e-10);
  end;
  CheckEquals(NowDate, Query.FieldByName('d_datetime').AsDateTime, 1e-10);
  CheckEquals(NowDate, Query.FieldByName('d_timestamp').AsDateTime, 1e-10);

  Query.Post;

  Query.Close;
  Query.Open;

  CheckEquals(1, Query.RecordCount);
  if StartsWith(Protocol, 'oracle') or (Protocol = 'mssql') or (Protocol = 'sybase') then
  begin
    CheckEqualsDate(NowDate, Query.FieldByName('d_date').AsDateTime, [dpYear..dpSec]);
    CheckEquals(NowDate, Query.FieldByName('d_time').AsDateTime, 1e-4);
  end
  else
  begin
    CheckEquals(Trunc(NowDate),
      Trunc(Query.FieldByName('d_date').AsDateTime), 1e-4);
    CheckEquals(Frac(NowDate),
      Frac(Abs(Query.FieldByName('d_time').AsDateTime)), 1e-4);
  end;
  CheckEqualsDate(NowDate, Query.FieldByName('d_datetime').AsDateTime, [dpYear..dpSec]);
  CheckEqualsDate(NowDate, Query.FieldByName('d_timestamp').AsDateTime, [dpYear..dpSec]);

  Query.SQL.Text := 'DELETE FROM date_values WHERE d_id=:Id';
  Query.Params[0].DataType := ftInteger;
  Query.Params[0].Value := TEST_ROW_ID;
  Query.ExecSQL;
  CheckEquals(1, Query.RowsAffected);
end;

Here we make some time and date value checks. Marco wrote me that the FPC2.7 is slightly like D2009 except the string-types. And in my mind except the TDateTime handling. I've got different behavior for 32/64 bit with the FPC and this posted example fails since long times. I run my test on the FPC2.6 so it might be possible that these tests are solved now.

Now i'm wondering why we've got such strange differences concerning the TDateTime/Time/Date fields between Delphi and FPC. I don't want to say now this is a bug, Lodo. The FPC is protable to several plattforms, which delphi is not.

Now my questions:
Why do we have such differneces?
Do you think about changing this behavior?
Or would it be better to fix the tests?

Michael

[ludob]

Where exactly does the test fail? It fails both on 32 and 64?

I'm not aware of any difference between Delphi and FPC TDateTime.

There are some conversions between TDateTime, TTimeStamp, comp and back in the fpc part (DateTimeToNative, etc.) that could have different results on 32 and 64 bit systems.

[ludob]

OK. Got the test suite up and running. There is a problem in ZDataSetUtils function NativeToDateTime line 1920: the {$IFDEF FPC} should be removed. No special treatment for FPC is needed there. Tested with fpc 2.7.1 on xp2 32 with postgresql

[EgonHugeist]

ludob,

wow you got the testsuites running without instructions!

Now your result do make me wondering. It was me who added these lines. We had some issues with time values here: http://zeos.firmos.at/viewtopic.php?t=3 ... &start=116

On the other hand all intyped values where wrong intepreted... For me personally it was the only solution but i used FPC2.6.0 LCL09.???.

Do we have to expect differences here where we need a version check instead, Ludo?

Michael

[ludob]

Tested the test suite with 2.6.0 2.6.1 and 2.7.1. The test runs OK if the {$IFDEF FPC} is removed.
Made also a little test program from the info in the link you gave and I can indeed reproduce the problem with DBGrid, both with 2.6.0 and 2.7.1. The problem is not in the Zeos code but in FPC. TTimeField.SetAsString calls SetData(@R) while it should call SetData(@R,False). I'll submit a patch for that in FPC. The fpc db implementation doesn't use the NativeFormat parameter and doesn't have this problem with time fields.

[miab3]

@ludob

Wow.
All the time I supposed that the error is in fpc because the different datasets receives a variety of effects.
Thanks

Michal

[EgonHugeist]

ludob,

thanks for your time and great job! It is no problem for me to remove the {$IFDEF FPC} part. Now it would be interesting for me to know how Zeos should handle this issue. If i remove the define then i think we got again terrible threads (eventually )if they do not have this patch . It was miab3, who pointed me to this issue and i was wondering that nobody before reported this issue (version dependencies?). Is there a possibility to have a define or check to avoid further threads? That would be great.

I personally can live with this failed test if i add a comment. Like you've recognized do other tests fail too for FPC. But i was out of time to check the most of them for FPC (i don't like the depug behavior ->any tips for me to get the same beahvior like in delphi?). On my branch i've got 3 fails and one oracle Exception left for all delphi-tests with MySQL, SQLite, PostgreSQL, FireBird and Oracle left. Slightly very nice results. Testing and trunk? What can i say...? The failing FPC tests are remaining and sometimes we've to fix the tests for differnet behaviors.

What do you think? What should i do now?

Michael

[miab3]

@EgonHugeist

I checked in LazarusWin1.1.0/fpc 2.7.1 32/64 SVN 26-5-2012 (Zeos7eh1439)

After applying the hotfix to fpc
SetData (@ R, False)

can remove
1221: TimeStamp = DateTimeToTimeStamp (TDateTime (Buffer ^));
from Zeos7.

(I wonder how people used to now datasets(other then Zeos7eh) and dbgrids in Lazarus?)

Michal

[ludob]

Well, it is a bug in FPC that makes that a time set with TTimeField.AsString can't be restored correctly, not even with TTimeField.AsString. Why would you try to solve that in Zeos? Fixing one usage case breaks other cases. The problem was introduced in fpc in revision 1852 (nov 2005!). A lot of problems have been fixed since then in fpc and testing on a version before 2.0 doesn't make sense. To avoid new dbgrid threads you could create a new define (FPC_FTTIME_BUG) that is undefined with
{$IF FPC_FULLVERSION>=20700}
{$UNDEFINE FPC_FTTIME_BUG}
{$DEFINE FPC2_7UP}
{$IFEND}

Regarding the other ZEOS+FPC errors, do you have a priority list? I won't have time to look at all errors, but if you have some urgent or nasty ones, no problem.

What debug behaviour are you referring to? You have to build the compiler and lazarus with debug info (make with OPT="-g -gl") to be able to trace into the fpc db routines.

Ludo

[ludob]

(I wonder how people used to now datasets(other then Zeos7eh) and dbgrids in Lazarus?)

TBufDataSet, the FPC dataset, ignores the NativeFormat parameter in SetData. Reason why the problem doesn't occur in lazarus with native sqldb components and why it didn't pop up in the test suites.

[EgonHugeist]

ludob,

Why would you try to solve that in Zeos? Fixing one usage case breaks other cases. The problem was introduced in fpc in revision 1852 (nov 2005!). A lot of problems have been fixed since then in fpc and testing on a version before 2.0 doesn't make sense. To avoid new dbgrid threads you could create a new define (FPC_FTTIME_BUG) that is undefined with

That's a solution for all! Great! Didn't want to fix it in Zeos, Lodo. That's why i'm asking for the extra define which can be used in the ZDataSet unit and on the test to clearify the results.

Regarding the other ZEOS+FPC errors, do you have a priority list? I won't have time to look at all errors, but if you have some urgent or nasty ones, no problem.

Nope, not realy, Lodo. Each solved or fixed test which cleans up our result is a good test! And if you can help us here then there is only a 'Thank you!' left. If i have such one i'll write you. What do you think about private contact? Don't want to bothering always MarcoVV if i've got no answers. I think he's got enough work with the FPC 'em selves.

What debug behaviour are you referring to? You have to build the compiler and lazarus with debug info (make with OPT="-g -gl") to be able to trace into the fpc db routines.

Thank you for that hint i'll test it. But much more headache makes me the AccessViolocaion behavior. Simple test-case: Take 5 procedures the first calls the next until Proc5 where we try to acces an unassigned object. Call Proc1. The debugger stops on Proc1 not on Proc5, Line: (anassigned object). Delphi points me always to the line where we realy have trouble. My issue is that i want to have a common solution for all compilers. As i've opend the Win64 support for zeos+fpc i was forced to use two debuggers to find the lines where we had the trouble and then the fix for it. Other users like 'ism' did tracing the sources at the same time with the fpc+lcl debugger several hours without suitable resuts. I know about a lot of people who run into the same issue, so i thought there is no solution..

I'll implement the define this evening. Thank's for you help. Btw. don't forget to ask for commit rigths which i can grant you. I'm sure you're able to handle the svn repositories right... I only need your sf.net username.

Michael

[ludob]

What do you think about private contact?

No problem. PM or email, whatever you prefer.

Take 5 procedures the first calls the next until Proc5 where we try to acces an unassigned object. Call Proc1. The debugger stops on Proc1 not on Proc5, Line: (anassigned object). Delphi points me always to the line where we realy have trouble.

That looks like missing debug info in some of the units. When all debug info is available, the debugger stops nicely on the line that causes the problem. Win64 can be a little more complex with exceptions raised from external non-fpc code because of the incompatible SEH but there also some progress has been made recently. Let me know if you have again such a difficult debugging case.

I only need your sf.net username

ludob

Ludo