MySQL Bugs: #99199: TableAdapter config Advanced Option "Refresh the datatable" is ignored

Bug #99199	TableAdapter config Advanced Option "Refresh the datatable" is ignored
Submitted:	7 Apr 2020 10:51	Modified:	15 Jul 2020 20:19
Reporter:	oracle oracle	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Visual Studio Integration	Severity:	S3 (Non-critical)
Version:	1.2.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
For other DB products like SQL Server, when this option is enabled in a table adapter config wizard advanced options, the generated SQL for INSERT and UPDATE queries gains a SELECT setatement after the insert that will retrieve values calculated by the database.

Example for a table called person, with an autoinc ID and a name.. Without this option ticked the datasetgenerator creates this: (SQLServer)

    INSERT INTO person VALUES(@name)

And with it ticked it generates:

    INSERT INTO person VALUES(@name); 
    SELECT Id, Name FROM person WHERE Id = SCOPE_IDENTITY()

-------

In MySQL for VS the dataset generator generates the same query (lacking a select) regardless of whether "Refresh the datatable" is ticked or not. Ticking the option is not remembered - it is always off after returning to the dialog

The functionality of refreshing the datatable works fine; if we manually edit the XML file of the dataset so that our generated query goes from this (MysQL syntax):

    INSERT INTO `person` (`Name`) VALUES (@p1)

To this:

    INSERT INTO `person` (`Name`) VALUES (@p1); SELECT `Id`, `Name` FROM `person` WHERE `Id` = last_insert_id() 

Then the datatable/tableadapter will correctly refresh the datatable

-----

    

How to repeat:
Install MySQL COnnector, and MySQL for VS, Add a new dataset, Add a tableadapter to it, connect to a db that has a table that has an auto increment int PK, configure the wizard to SELECT * FROM thattable, in advanced options, tick "Refresh the datatable", finish the wizard, go back into the table adapter again (right click choose CONFIGURE), note that in advanced options the setting you ticked on is now off again. Open the XML, note that the generated insert statement never has a select on the end of it (like it should) when ticking "Refresh the datatable". Add the SELECT yourself manually using the last_insert_id() function and note it works fine in the runtime

Suggested fix:
To fix the bug; heed the setting of the "Refresh the datatable" option, and make it so that the code will add a SELECT that pulls all the values from the row based on the PK, after generated INSERT and UPDATE queries in the XML of the dataset. The generator will then correctly pick up the XML and generated code that is correct

Corrections/errata:

In the original submission I said "datasetgenerator" / "data set generator" and by this I mean the device that generates the dataset.xsd XML file. 

I didn't mean the the "Custom Tool" / MSDataSetGenerator (referenced in the Properties window of a DataSet) that operates on the dataset.xsd to produce compilable .net code 

----

The bug appears to be in the tool that generates the XSD/XML file, because if the relevant query is manually patched into the XML, then the Custom Tool/MSDataSetGenerator correctly generates adapter queries that select the appropriate updated data from the DB

Extra info: This bug is probably related to bugs #70305, #37865 and #23082. Particularly with #70305 I think it's the same bug but I'm reporting it at an earlier moment in the process of :

1) developer creates dataset, 2
) mysql/vs creates dataset xml XML, 
3) msdatasetgenerator turns XML into code, 
4) developer uses code

The other bugs are focusing on the symptoms of the bug they are encountering in step 4) stage, whereas I believe these other bugs are actually occurring because earlier in the timeline, the XML was generated incorrectly at step 2. 

If this #99199 is to be closed as a duplciate, please link earlier bugs to it to make the workaround of manually including the same SQL as "refresh the datatable" performs available as an option for people landing on those earlier bugs

Duplicate of bug https://bugs.mysql.com/bug.php?id=70305.

WORKAROUND - runtime
Modify insert command before using the tableadapter or connection:
Me.<name>TableAdapter.Adapter.InsertCommand.CommandText = Me.<name>TableAdapter.Adapter.InsertCommand.CommandText & ";SELECT <field_id>, <...>, <...>, <...>, <...> FROM <tablename> WHERE <field_id> = last_insert_id()"

The record gets auto-updated with DB generated values.

MySQL team - pls repair the problem, it's as easy as above.
You only have to identify DB Server side columns (autoincrement or virtual/calculation) and add proper SELECT command to the INSERT/UPDATE command.