resolve to dataset / notes / deletions

[RoyS]

Hi,
I have a Delphi XE app using client datasets through providers to MyDAC MyQuery components (latest).

To be able to delete all rows in a cds in a while not eof loop and then applyupdates to commit the changes I have to set resolvetodataset=false on the provider.

Unfortunately, in the same app I need resolvetodataset=true on the same provider as it contains a text blob column which is otherwise set to null when the row is updated and the field is unchanged.

I appreciate that this may be a bug / problem in Midas but I think that it is more likely to be resolved by yourselves driving the fix.

Note - this is not consistently happening - further investigation required for the exact conditions.

If anyone can stop me from re-inventing the wheel and explain please do

Thanks,
Roy

[AndreyZ]

Hello,

You can delete all rows from ClientDataSet and apply updates to commit changes regardless of the ResolveToDataSet property value. The only difference in this case will be the way of posting data: by a SQL query if ResolveToDataSet is False, and by TMyQuery otherwise.
I cannot reproduce the problem with text blob columns. Can you provide some code example when text blob row value is set to null while updating a field without changes.

[RoyS]

Thanks for te quick response, I need to investigate this a lot more I think to see if it is anything to do wih the visual components, settings differences etc. as it is not consistent behaviour.

The app is quite large so I need to pull code out and test separately

This could take a while but in the meantime I have workarounds by programmatically changing resolvetodataset on the troublesome ones

thanks
Roy

[AndreyZ]

We are looking forward to hearing from you.

[RoyS]

I feel sure that this is actually a CDS problem so please close this post if you wish but just for completeness to reproduce this error

create a simple table withan integer primary key and 2 memo fields (I used the tmp database / UTF8 / Innodb).

create a form with edit boxes for the three fields, nav bar, and a datasource -> client dataset -> dataset provider -> MyQuery -> MyConnection

Run it, add a row to the table with data in all three fields

change the SECOND memo field, post, and refresh.

memo1 is null if resolve to dataset is false.

note that changing memo1 does not null memo2

I have a sample project I can email if required.

(Can't reproduce the deletion problem out of the large app so I will raise that one separately if I manage to narrow it down).

[AndreyZ]

Please send your sample to andreyz*devart*com, including a script to create a table.

[AndreyZ]

We have investigated the problem. This problem is caused by two reasons:
1) you are updating only one column in the TClientDataSet component;
2) in the MyQuery.SQLUpdate property you are using SQL code that updates all columns in the table:

Code: Select all

UPDATE memotest
SET
idmemotest = :idmemotest, memo1 = :memo1, memo2 = :memo2
WHERE
idmemotest = :Old_idmemotest

When the TClientDataSet component is being updated, columns values that weren't changed aren't transfered to the underline dataset (in this case it's the TMyQuery component). You can check it in the TUpdateTree.DoUpdates method of the Provider.pas unit. There you can see that Delta.FieldByName('memo1').Value is empty when you are updating the TClientDataSet component.
To solve the problem, you should remove SQL code from the TMyQuery.SQLUpdate property. In this case correct SQL code for updating will be automatically generated by the standard components if the TDataSetProvider.ResolveToDataSet property is set to False, and by MyDAC if it's set to True.

[RoyS]

Thanks,

I generate the SQL as part of my normal procedure when using MyDAC components, it seems that this may not be correct - can you clarify when you feel it would be necessary to generate the SQL at design time and that the standard procedure should be just to implement the main select statement?

I appreciate your quick response on this, it is very helpful.

Roy

[AndreyZ]

MyDAC components generate SQL code in the most efficient way, and usually you shouldn't write SQL code for inserting, updating, locking, or removing records. But if you are working with views or using table joins, MyDAC cannot generate correct SQL code automatically. In these cases you should write SQL code yourself.

[Justmade]

... But if you are working with views or using table joins, MyDAC cannot generate correct SQL code automatically. In these cases you should write SQL code yourself.

Actually, even in joins with one primary table to update, the MyDAC generate good enough code for those operation most of the time (set updating table if it is not the first table in the join). Only when we need to update multiple table or need other more complicated operation do we need to write those SQL ourselves. Cheers