Hello

-
Hello,

I just fell over this one with my 1st Delphi project moved to XE7 (previously XE6):

    dsQ := TFDQuery.Create(Self);
    dsQ.Connection := DM1.FDConnection1; // MySQL Server
    dsQ.SQL.Text := 'SELECT gueltig_id FROM gueltig ORDER BY gueltig_id DESC';
    dsQ.Open;
    if not dsQ.Eof then
        ResultID := dsQ.Fields[0].AsInteger + 1 // XE6 gives 156989 = correct    XE7 returns 5400 = WRONG!!
    else
        ResultID := 100; // just dont want to start with 1 or 10
    dsQ.Close;
    dsQ.Free;

This little code sample returns the correct result in XE6, but completely incorrect in XE7 (both debug mode). Both On exactly the same server.

MySQL Server is v5.6.12
FireDAC version is standard delivered with XE7
Delphi XE7 running on Windows 8.1 Update 1 in a VM (same OS for XE6, but separate VM).

Moving code into another unit (form) doesn't help. Nor using TFDTable instead of TFDQuery.

Anybody any clue?

#delphi #programming #xe7 #firedac #mysql

Comments

  1. jeff weirSeptember 24, 2014 at 6:26 AM

    Same result using FieldByName as Fields[0]?

    ReplyDelete
  2. Yannick HubertySeptember 24, 2014 at 6:29 AM

    Yes! Was previously .FieldByName('gueltig_id').AsInteger

    And checked all TFDConnection params, fetchall, etc...)

    ReplyDelete
  3. Oliver FunckeSeptember 24, 2014 at 6:40 AM

    What's the mysql data type of gueltig_id ?

    ReplyDelete
  4. Sam Shaw (蕭沖)September 24, 2014 at 6:43 AM

    Please check the FDquery property
    FechOptions
    and
    just after open, check the value of
    RecordSize
    and
    RecordCount

    then compare to the correct value by other means, for example phpmyadmin or db explorer ...

    ReplyDelete
  5. Yannick HubertySeptember 24, 2014 at 7:03 AM

    Oliver Funcke data type in table definition is INT(11)

    ReplyDelete
  6. Yannick HubertySeptember 24, 2014 at 7:20 AM

    Oliver Funcke Sam Shaw

    .RecordCount is incorrect, indeed (by far) !

    dsQ.FetchOptions.Mode = fmAll;
    (does this is of any importance?)

    and

    DM1.FDConnection1.FetchOptions = afAll
    DM1.FDConnection1.FetchOptions.Mode = fmAll

    So, not all the records are returned, and I simply seem to fail to find the (modified?) concerned parameter or property in XE7...

    ReplyDelete
  7. Oliver FunckeSeptember 24, 2014 at 7:22 AM

    I couldn't test with MySQL, but a local table gives the desired result:

    FDMTGueltig.Insert;
    FDMTGueltig.Fields[0].AsInteger := 156988;
    FDMTGueltig.Post;
     ShowMessage((FDMTGueltig.FieldByName('gueltig_bis').AsInteger+1).ToString); // 156989

    Also, the query you are using is very  bad performancewise - I assume you want to retrieve the highest gueltig_bis id to generate the next one. Better with:

    SELECT max(coalesce(gueltig_id,99))+1 as gueltig_id FROM gueltig

    ReplyDelete
  8. Yannick HubertySeptember 24, 2014 at 7:38 AM

    Yes, I imagine my example is far away from performance-optimized... fact is that even with your suggestion (MAX...) the result is (the same) incorrect one simply because the whole table isn't read, and I don't find the correct property to fetch all the records.

    ReplyDelete
  9. Yannick HubertySeptember 24, 2014 at 7:51 AM

    Sam Shaw Ok, thanks, I will try (but does not explain why XE7 behaves differently from XE6...). I hope I don't fell over too much of such "differences".

    ReplyDelete
  10. Yannick HubertySeptember 24, 2014 at 7:56 AM

    Sam Shaw Tried your suggestion, same wrong result. So, the problem is not TFDQuery, but at TFDConnection in conjunction with MySQL, I suppose.

    Digging...

    ReplyDelete
  11. Oliver FunckeSeptember 24, 2014 at 8:03 AM

    Did you check the TField properties? (ftInteger, Datasize etc.)

    ReplyDelete
  12. Yannick HubertySeptember 24, 2014 at 9:16 AM

    Temporary result:

    I can't believe it, it not only seems to be, but it IS a problem on the Windows 8.1 Update 1 workstation (VM) !!!

    XE6-Version on the XE6-developer-PC: correct result
    XE7-Version on the XE6-developer-PC: correct result

    XE6-Version on the XE7-developer-PC: wrong result
    XE7-Version on the XE7-developer-PC: wrong result

    So I'm not sure if it still can be a FireDAC issue which may behaving differently on different PCs, due to whatever. The MySQL-dll (libmysql.dll) is the same on both PCs for both versions.

    Thanks to all who participated until now. I will update the thread as soon as I have found a/the solution.

    Oliver Funcke Sam Shaw jeff weir

    ReplyDelete
  13. Oliver FunckeSeptember 24, 2014 at 9:30 AM

    For testing I used :
    Delphi XE7
    Client libmysql.dll 6.1.5
    Server 5.6.21-log MySQL Community Server
    Windows 8.1 pro x64 all updates applied
    VMWare player 6.0.3 build-1895310

    ReplyDelete
  14. Daniel RailSeptember 24, 2014 at 11:30 AM

    Yannick Huberty Are you sure that you are using the exact same database in both instances?

    ReplyDelete
  15. Yannick HubertySeptember 24, 2014 at 11:10 PM

    Yes! Absolutely! Same server, same database.. It happens that on the newer Win8.1 Upd 1 machine (where XE7 is installed), only 1574 records are retrieved resp. counted, where the next +1 value would be 5400, while on the slightly elder machine (XE6 machine, Windows installed from same .iso cd) all records are taken into account, and which gives the correct result.

    Putting now the PCs on same update level, nd will reinstall MySQL client libraries.

    ReplyDelete
  16. Daniel RailSeptember 25, 2014 at 5:35 AM

    Yannick Huberty My reasoning for making sure that it was the exact same database is that you mentioned that from one VM, XE6 and XE7, returned the correct result, and from the other VM, XE6 and XE7, returned the wrong result.  And, you also mentioned that using "SELECT max(coalesce(gueltig_id,99))+1 as gueltig_id FROM gueltig" gave you the same incorrect result. 

    To me, this usually indicates two different databases are being used.  Because, "SELECT max(coalesce(gueltig_id,99))+1 as gueltig_id FROM gueltig" is run on the server and not locally on the workstation.  So, if this query gives you two different results from two different VMs, then the only logical conclusion that I can come up with, is that they are not connecting to the same database(although it could still be the same server/computer).

    Compare the connection strings that you are using within both VMs, just to be certain.  And, just to prove me wrong, post those connection strings here.

    ReplyDelete
  17. Yannick HubertySeptember 25, 2014 at 6:09 AM

    Daniel Rail YOU ARE RIGHT!

    I did say it's the same server and database because I select the same combo-entry out of a list of available database profiles on both VMs. Fact is that the underlaying profiles (stored in a local .ini file) differred, even if the database profile name was the same (on which I based my previous answer). Simply said the .ini files had not the same content inside same-named sections.

    Your comment incited me to check in detail the value of the selected database for the chosen profile, on both PCs.

    Could have done it earlier... thought I simply copied the profile .ini's from one VM to the other, it seems this was definitely not the case.

    +1 for you

    ReplyDelete

Post a Comment