MyDAC : Unknown column error

[dormky 2]

I have a TMyTable on which I've just added a field called "FileVersion" using right-click on the control, right-click again and "New Field".

When I log all the fields in "MyTable.Fields.FieldName", FileVersion does indeed exist.

But when setting the table's "Active" property, I get the following error :

 

#42S22Unknown column 'FILEVERSION' in 'field list'

 

I honestly have no idea what I'm supposed to do here ? Looking at other fields in that table, they all look the exact same. They all exist in the exact same places (in the dfm file and the code).

Edited February 29 by dormky

[ertank 27]

I don't use MySQL. But,

I would use same letter case as the table column name.

I would double check that design-time and run-time actually uses the same server, database, table

 

Edit: Reading your problem again, it feels you added a column in TMyTable but not in your database table.

Edited February 29 by ertank

[dormky 2]

Every field is shown in all caps in errors ; MySQL doesn't care about that anyway.

And yes, I did add it in the database. And I only have one database.

[Cristian Peța 103]

Too little details but a debug call-stack should tell us something more.

[JonRobertson 72]

On 2/29/2024 at 3:08 AM, dormky said:

I honestly have no idea what I'm supposed to do here ? Looking at other fields in that table, they all look the exact same. They all exist in the exact same places (in the dfm file and the code).

The error "42S22 Unknown column" is not coming from the Delphi compiler, MyDAC, or the underlying TDataSet. It is coming from MySQL. From an online search, the error means that the field specified (FILEVERSION) in the field list of the SQL statement is not a column in the table.

 

https://sebhastian.com/mysql-unknown-column-field-list/

When you call MyTable.Open, MyDAC is generating a SELECT statement based on the fields that you have added to your MyTable component. That SELECT statement has FILEVERSION in its field list. When executed by MySQL, the unknown column error occurs.

 

I don't know if the following will work with TMyTable, but it might since TMyTable has TDataSet as an ancestor.

1. Drop another TMyTable component on the form or datamodule.
2. Set its properties to the same connection, database, and table as your current TMyTable component.
3. Double-click on the new TMyTable component.
4. If a fields window appears, right-click it and select Add Fields (or press Ctrl-A).
5. When the Add Fields dialog appears, is FILEVERSION in the list?

Using the debugger, ShowMessage, or logging, verify at run-time that the Connection and Database properties on your MyTable component are set to the expected values.

[dormky 2]

Alright, so it turns out that when compiling with the DEBUG flag, all components keep the csDesigning flag in their ComponentState, leading MyDAC to connect automatically to whatever is set at design time.

 

How can I prevent this ? Apparently SetDesigning cannot be called manually (it's protected) but of course a helper class gets around that easily. Still, I'd rather have a solution that doesn't rely on these kind of shenanigans.

[ertank 27]

I am using UniDAC and there is TUniConnection.Options.KeepDesignConnected parameter. I expect MyDAC to have a similar parameter.

 

On 2/29/2024 at 2:31 PM, dormky said:

And yes, I did add it in the database. And I only have one database.

I didn't understand why this is a problem since you have a single database and necessary columns added in it.

[Gary 18]

+1 for JonRobertson & ertank

I use UniDac as well and you use the "AddField" Menu selection not the "NewField" Menu. I am always forgetting to set the Options.KeepDesignConnected property as well. 

[Stano 143]

3 hours ago, Gary said:

I am always forgetting to set the Options.KeepDesignConnected property as well. 

Setting it while running doesn't help?

[Cristian Peța 103]

13 hours ago, Stano said:

Setting it while running doesn't help?

After connection is created is too late.

But default is True....

https://docs.devart.com/unidac/devart.dac.tdaconnectionoptions.keepdesignconnected.htm

Edited March 5 by Cristian Peța

[dormky 2]

On 3/4/2024 at 4:25 PM, ertank said:

I am using UniDAC and there is TUniConnection.Options.KeepDesignConnected parameter. I expect MyDAC to have a similar parameter.

 

I didn't understand why this is a problem since you have a single database and necessary columns added in it.

It's because with the default connection parameters, it was connecting to the production database. Which of course did not have the added column.

Don't ask me why this is done by default at design time, I inherited this project...

[JonRobertson 72]

On 3/4/2024 at 1:23 PM, Stano said:

Setting it while running doesn't help?

From the docs, KeepDesignConnected is not about auto connecting to the design-time connection. If the project is built with Connected set to True, then loading the form/datamodule/components will set the property's state to True when the form is loaded, making (or trying and failing) to make the connection before your code starts. That is standard VCL component behavior for published properties.

 

By setting KeepDesignConnected to False, the component will not make a connection during form load, even if the Connected property was saved as true.

 

There are several other workarounds. There are add-ons that will set various property values every time the form is saved. Personally, I derive a component and change the stored attribute of the property, like so

 

  TmyADOConnection = class(Data.Win.ADODB.TADOConnection)
  published
    property Connected stored False;
    property LoginPrompt stored False;
  end;