There is an issue when trying to delete a row from and MS SQL database using the TMiletusClientDataSet. The QueryText is 'select * from LoginHistory where ID := Some ID number which queries only those rows for the current user. Then using the code below to delete all the rows.
WaitMsgDeleteAll.Show;
try
LoginHistDataset.First;
repeat
LoginHistDataset.Delete;
until LoginHistDataset.Eof;
finally
WaitMsgDeleteAll.Hide;
end;
Getting the following error from the MSSQL driver.
Project SvpDb.exe raised exception class EMSSQLNativeException with message '[FireDAC][Phys][ODBC][Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid object name 'SWINGCOACHDB.db_owner.LoginHistory'.'.
On another data set a different error when trying to delete / modify a row from the Users table with the QueryText = 'select * from Users'. The local data is being changed but the database update / delete does not happen.
Project SvpDb.exe raised exception class EDatabaseError with message 'Cannot perform this operation on a closed dataset'.
There is an issue when trying to delete a row from and MS SQL database using the TMiletusClientDataSet. The QueryText is 'select * from LoginHistory where ID := Some ID number which queries only those rows for the current user. Then using the code below to delete all the rows.
We tried this here but could not reproduce it with your code snippet alone. Can you provide a sample that includes all the steps that are needed to reproduce it?
On another data set a different error when trying to delete / modify a row from the Users table with the QueryText = 'select * from Users'. The local data is being changed but the database update / delete does not happen.
A sample for this one would be appreciated too as we also cannot reproduce this.
Perhaps also check if you have the same issue using just FireDAC alone from a VCL/FMX app (to rule out it's related to your MySQL DB)?
I am also using a TADOConnection from a VCL windows service on the same database and tables with no issues inserting or updating the fields. So I do not think it is the database itself. It is a Microsoft SQL server setup running on a different computer not MySQL. There are a bunch of settings in the TMiletusMSSQLDriver that are not covered in the documentation. Perhaps and example of a typical configuration of the drive would help.
Misread MS SQL as MySQL. Retried with MS SQL too but still cannot reproduce, so there is a step or setting somewhere that could be important, but without knowing what default settings were changed, we cannot guess how to create a similar setup.
We tried connecting two different ways, both of them were successful and we were able to delete records without issues:
One remote that is hosted by an external service: Server (the server address), UserName + Password (credentials), DataBase (the name of the database).
One locally hosted with MS login: Server (MACHINE\SQLEXPRESS address), OSAuthent set to False, DataBase (the name of the database)
The ID field used in the index has all unique values. This is the message that is being sent to the driver.
{"MessageID":59,"MessageData":{"QueryText":"select * from Users","DriverID":"MSSQL","Settings":{"DBDriverGUID":"{E4075FA7-D3D2-3DD6-0C30-CB5F949203D0}","DataBase":"SwingCoachDB","UserName":"SVP_ONLINE","Password":"SvpCoach2024#","LoginPrompt":"false","MonitorByInitial":"false","PoolCleanupTimeout":30000,"Pooled":"false","PoolExpireTimeout":90000,"PoolMaximumItems":50,"Params":,"Address":"","ApplicationName":"","Encrypt":"No","ExtendedMetadata":"false","Language":"","LoginTimeout":0,"MARS":"Yes","MetaCaseIns":"true","MetaCurCatalog":"","MetaCurSchema":"","MetaDefCatalog":"","MetaDefSchema":"","Network":"","ODBCAdvanced":"","OSAuthent":"No","Server":"192.168.0.200","Workstation":"","MetaCaseInsCat":"Choose","VariantFormat":"String"},"GUID":"{487323BE-9A6A-EDF4-1154-3CCDFB4E6402}","IndexName":"ID","IndexValue":"55"}}
I have narrowed down the issue. The problem is in the AfterScroll on the dataset or OnDataChange for the data source. I am changing the filtering of another data set. When it is commented out, it functions, but if I try to change the filtering after the delete, I get the error. The code works fine when you scroll the dataset. Only get the error when I delete a record.
procedure TMainForm.UsersDatasetAfterScroll(DataSet: TDataSet);
begin
if (UsersDataset.State <> dsEdit) and (UsersDataset.State <> dsInsert) then
begin
LoginHistDataset.Active := false;
LoginHistDataset.QueryText := 'select * from LoginHistory where UserLink=' + EDRecordId.Text;
LoginHistDataset.Active := true;
end;
end;
Just a note, from my testing and trying to work around the issue, it seems any code that references a data set in the after scroll caused the issue, even if there is a condition around it. I believe it may have to do with the way the TMiletus messages are being dispatched. Have not found a way to work around the issue.
You mentioned the OnDataChange event. Is that also implemented alongside with the AfterScroll event? If yes, how?
Right now we made two connections to two MSSQL databases but simply changing the QueryText in the AfterScroll event (Active property included) is working as expected.