query fails to delete silently

[codifies]

I've been using a query like the old delph table component.

ie just with a very simple select without join or where

when I want to delete insert or edit I've moved the cursor to a selected record and show a form of db widgets
to let the user do the delete or edit and then hide the form.

I've found that when doing this after an update operation further deletes at a later stage fail silently.

my only solution has been keep the query closed most of the time, only opening it when the form is shown and then closing the query immediately after any post or delete operation.

this wouldn't have been an issue if I'd been creating and deleting a form that had the query component on it....

Anyone else seen silent deletion failures with queries?

[ludob]

Never seen before. Post some code.
A possibility is that the update causes an error and applyupdates stops applying the changes. But then again, that is only an educated guess

[BigChimp]

Lazarus+FPC+OS type/version will be useful as well...

[codifies]

I'm going to set my sig!

I'll try to make a minimum example that shows the issue later....!

[codifies]

aah subtle one this...

what was happening when I was creating a new record I inserted into the edit forms query, and relied of the edit form to then post the new record once the user had entered its data...

when I posted the insert I was refreshing just the main forms query and hiding the form.
you can then see the new record in the record selector (a read only grid)

when reopening the edit form (where the delete button is) the edit query seems to be working with an somehow(?) invalid record (despite the fact you can post edits and all the forms data components work normally)

However when you delete this record using the edit forms query it fails silently and you cannot delete the record

restarting the app allows you to delete the record

I found by refreshing both queries after posting (in case you're posting a new record) that the record can then be deleted and everything works as expected. (you can continue having both queries active all the time without needing to open/close the edit form query)

as you can see from above even describing this issue is not trivial and if it *is* a bug? how best to describe / report it...?

sorry if my explanation is less than clear!

[codifies]

now its silently failing to delete all the time!

I'll have to look at this again another day - bare with me!

[codifies]

Okay I have looked at this again

delete *is* failing silently ( onDeleteError is NOT triggered and without it I don't see any kind of exception)

the only way I can make sure it works in all circumstances is to keep the editors query closed when hiding the edit form

AND (now this is the odd one!)

call applyUpdates after the delete call! (could be just because applyUpdates is refreshing the queries data set, haven't just tried with a refresh (which delete should be doing anyhow!))

I've take the liberty of posting the whole project here with sql data dump as I strongly suspect that this demonstrates a bug!

As is the code works however I maintain it should also work without every line preceded by a

Code: [Select]

// work aroundcomment !

this includes a number of open / close calls on the editors query in both units and also a call to applyUpdates.

[ludob]

Using 2 datasets to browse/edit/delete the same data is really looking for problems. I don't know why you are not using the same dataset for mainunit and editorunit. Everything would be in sync without all the problems you are encountering now. In the same time you would avoid all the superfluous DB interactions, locates, etc.

haven't just tried with a refresh (which delete should be doing anyhow!)

No. It shouldn't. A dataset is a copy of the data in memory. Edit, Insert, Append, Delete, Post are actions on the in memory dataset. They are logged in memory and do not affect the DB. The change log is only transferred to the database with ApplyUpdates. Refresh reloads the dataset from the DB. So a delete followed by a refresh is a no-op. Actually an exception will be raised telling you to ApplyUpdates before Refresh.

Now your next comment will be that mainPeopleDatasource is read only because of the join. That is because sqldb can not figure out automatically how to update/delete/insert into a multi-table relation. But that is exactly the purpose of InsertSQL, etc. Fill those in and you can use mainPeopleDatasource everywhere. Your second table is just a static lookup, so the queries are trivial.

[codifies]

Using 2 datasets to browse/edit/delete the same data is really looking for problems.

I agree entirely but it was the only way the dblookup would work without giving the error message
invalid variant typecast

I'm looking again to fix this...

I still think the project hi-lights a bug btw.

[ludob]

invalid variant typecast

That occurs when the KeyField and ListField (TDBLookup controls) or KeyFields and LookupKeyFields (fklookup fields) don't have the same data type.

[codifies]

They actually do (at least in the underlaying db), but after deleting and recreating the dbgrid & dblookupcombo then replacing all the field defs the problem went away

interestingly though I've ended up giving up on the join as although the dblookup was changing the value of fktitleid it wasn't being reflected in the joined title field...

I could have stuck with the join but that would have meant more locate madness

I've ended up with a lookup query field and a dummy work around field (as per the other thread)...

[codifies]

... further deletes are still failing silently....

this time - using just one query component - although the record is removed from the grid, or rerunning the application the record is still there...

[ludob]

Transactions enabled on the database? Missing Commit?

DeleteSQL assigned with a wrong delete query?

[codifies]

DOH missing applyUpdates
(or does that just work by chance and should I be using something else?)

its really confusing as I'm used to this behaviour

http://docs.embarcadero.com/products/rad_studio/delphiAndcpp2009/HelpUpdate2/EN/html/delphivclwin32/DB_TDataSet_Delete.html

sql UPDATE and DELETE are distinct operations

shouldn't applyUpdates be commit or applyChanges to avoid confusion?