Timestamp and tableadapters (generated code)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
trygvelo
Posts: 13
Joined: Wed 19 Jan 2005 10:53

Timestamp and tableadapters (generated code)

Post by trygvelo » Wed 01 Oct 2008 09:47

Hi,

I had a problem today that the tableadapter configuration wizard didn't generate the insertcommand. I have a timestamp column in my database which is declared "not null default current_timestamp" as per default MySQL behaviour. I did not want to include the timestamp column in my select/insert/update since the generated tableadapter automatically defines the column with "AllowDBNull = false". I do not want to specify a value myself when I add new rows to the datatable but I want MySQL to automatically insert the value.

I guess the wizard could not create the insertcommand since columns set to not null in the database was not included in the select statement.

My workaround right now is that I manually change the AllowDBNull value to true for all timestamp columns, but this a potential source for bugs later if I forget to do this after an update of the tableadapter generation.

Any chance of a setting specifically for timestamps to choose that they don't need to be included in the select but the insertcommand will still be created? OR if running MySQL in MaxDB mode I believe it will be closer to the ANSI SQL correct behaviour, timestamp is then similar to DateTime column and are not updated automatically unless specified to do so in the table definition like: "ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP"

Any thought about this issue?

PS. Right now I don't dare changing the MySQL mode to MaxDB and spend a lot of time checking every part of every system using MySQL here...

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 01 Oct 2008 16:48

Please follow these steps:
1. In design time add to the form designer MySqlDataAdapter (double click or drag and drop).
2. In the MySqlDataAdapter context menu choose "Configure Data Adapter".
3. In the Select Text tab type "SELECT * FROM your_table_name", press Preview button and configure the connection to your database.
4. Go to the Command Generator tab, press Get Table Fields button, UNCHECK the Updating property for your timestamp column.
5. Click Generate Commands. As a result there will be generated Insert and Update commands which don't effect the timestamp column.

The Updating property defines the columns that will be effected by the Insert and Update commands.

If you have any further questions, please let us know.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Wed 01 Oct 2008 16:56

There are also the UpdatingTable and UpdatingFields properies of MySqlCommandBuilder that allows us to avoid setting any values to unchecked columns.

trygvelo
Posts: 13
Joined: Wed 19 Jan 2005 10:53

Post by trygvelo » Fri 03 Oct 2008 10:59

Yes, this is probably ok (haven't tested) for a MySqlDataAdapter, but what if I use DataSet integrated TableAdapters?

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Post by Shalex » Fri 03 Oct 2008 12:19

We recommend you to use our DataSet Wizard (Tools | MySQL | DataSet Wizard) and in the last window to check "Add this DataSet to designer". As a result, your DataSet will appear in the Form Designer. Right click on the DataSet | Edit and DataSet Editor will be shown. Select your table there, press F4 and in the Properties window you will be able to set the UpdatingFields property.

If you created DataSet without DataSet Wizard, go to the DataSet.xsd file Properties and change the Custom Tool property from "MSDataSetGenerator" to "MySQLDataSetGenerator", build the project (after this action your DataSet will appear in Toolbox), make double click on your DataSet in Toolbox (or drag and drop it) - it will appear on Form Designer. After these steps your DataSet will be available from DataSet Editor.

Post Reply