autoinc doesn't refresh

[ugo_greenbee]

MyDac 8.1.4 with Delphi 2007

Hi, I try to understand how it work. Sometimes my primary key, autoinc doesn't show me the value after an insert in my TMyQuery component.

I do an "append" and "post" on my TMyQuery with the query:

Code: Select all

SELECT article_compo.*
, (SELECT CONCAT(IFNULL(article_soumission.nom_article, ""), ' ', article_soumission.descrip) FROM article_soumission
   WHERE article_soumission.idarticle_soumission = article_compo.idarticle_soumission_compo) AS concat_article
FROM article_compo
ORDER BY concat_article

I tried to change UpdatingTable propertie, Refresh after insert, SQLs statement but the autoinc value still missing.
on 2 differents networks (differents servers) after my "post" i have acces to the value of the primary key autoinc.

on another network the value is NULL from the TMYQuery Componenent but with the workbench I have access to the real value.

if I change my query for:

Code: Select all

SELECT article_compo.*
, (SELECT CONCAT(IFNULL(article_soumission.nom_article, ""), ' ', article_soumission.descrip) FROM article_soumission
   WHERE article_soumission.idarticle_soumission = article_compo.idarticle_soumission_compo) AS concat_article
FROM article_compo

it work on the 3 differents networks ...

Where the problem come from ?

I got a lot of problem of that kind (insert, update, refresh) with query with left join or more than one select. Do you have some advice about it. I know about (updates SQLs "statement type") but i prefer to make my query work without if possible.

Thanks

[ugo_greenbee]

I now use TMyQuery.InsertId; property and I'm able to have the correct value for the autoinc. But I'll appreciate if someone tell me what is wrong.

Thanks

[VladimirK]

Hello,

The problem is known and is due to the fact that MySQL (depending on a version) can not return the information about auto-incremental and primary key fields. We cannot affect this MySQL server behaviour.

[ugo_greenbee]

I had the problem again, with your advice about mysql could be the source of the problem, I tested another query (having autoinc problem) with Mysql server on the same computer:

- 5.5.29 autoinc worked
- 5.5.40 autoinc worked
- 5.6.17 autoinc don't work
- 5.6.21 GA autoinc don't work
- 5.7.15 m15 autoinc don't work

What should I do ?:
-Change a setting in Mysql ?
-Change a setting in Mydac ?
-Change the way we use MyDac ?
-Wait a patch (from who, when) ?
-Something else ?

I doubt Mysql support will help me, cause I'm having the problem with mydac and delphi. If the problem is known, is there a solution ? Mydac should have a solution.

Thanks

[ViktorV]

Please upgrade to the latest version of MyDAC (8.4.11) and check if the problem still persists. If this doesn't help solve the problem, please send a small sample to demonstrate the issue to viktorv*devart*com, including a script to create database objects.