I haven’t developed anything on Delphi for five-six years. So it took me some time to recall this process. In order not to spend this time next time when I forgot about this, I want to write down this here.
The first thing is that it is not obvious at all how to connect Delphi to MySQL, I have mentioned about this in one of my previous post.
The next thing is that there are different database controls in Delphi, one of them are only unidirectional dataset, this means you can’t connect them directly to your DBGrid component, hence, you can’t push data back to database.
These are TSQLConnection, TSQLDataSet, TDataSetProvider, TClientDataSet and TDataSource. So if you connect your DBGrid to pack of these component selecting appropriate dataSource which is connected to appropriate clientDataSet, which is connected to appropriate dataSetProvider through ProviderName property, which is connected to appropriate SQLDataSet (which is unidirectional), which has SQLConnection property connected to appropriate connection, then you will not be able to inline edit data so that it would go back to database. If you even edit something, it will be modified only on temporary memory dataset, but not in back-end database.
So what you should do if you to enable ability of inline-editing of back-end database?
You need another pack of components, which are, for example, ADOConnection, ADOTable, DataSource.
The naive implementation of this could not work.
At least for me. When I tried this, I received an error “The specified DSN contains an architecture mismatch between the Driver and Application.”
After intensive googling I have finally found the explanation of this strange error.
So I had 64-bit Windows OS, 32-bit version of Delphi IDE and 64-bit version of MySQL ODBC Connector. They didn’t match, so I had to install 32-bit version of MySQL ODBC Connector here.
After that everything worked fine.
P.S. Another reminder:
64-bit version of ODBC Manager is usually here: c:\windows\system32\odbcad32.exe
32-bit version of ODBC Manager is usually here: c:\windows\sysWOW64\odbcad32.exe
P.P.S. Example of ADOConnection ConnectionString
Provider=MSDASQL.1;Password=userpassword;Persist Security Info=True;User ID=username;Data Source=MySQL32;Extended Properties=”DATABASE=dbname;DSN=MySQL32;OPTION=0;PWD=userpassword;PORT=3306;UID=username;”
P.P.P.S. Published code of my project here.