Devart Forums

Hi,
any example of using SQLUpdate (C++Builder)?

I can't use a simple update statement in the TMyQuery component.

If I set it to the SQL propety:
update apparts set selection = 'N' ;

A call to Open() doesn't work.
ExecSQL() is not implemented.

Then I guess I'm supposed to set that SQL statement into
the TMyQuery SQLUpdate property.
Ok and then what???
How do I execute it?

Sorry, but nothing is in the (poor) documentation.
So any help with a real C++ example would be appreciated.

If you need to execute query "update apparts set selection = 'N'" you should use Execute() method.

PS: A necessity in using SQLUpdate usually occurs quite seldom.

Sorry, but this doesn't work.
Execute doesn't do anything.
So I guessed I then had to commit the changes, so I added ApplyUpdates, which complains about a "closed dataset"...

dataContainer->qryGeneric->Close() ;
dataContainer->qryGeneric->SQL->Clear() ;
dataContainer->qryGeneric->SQL->Add(
"update apparts set selection = 'N' " ) ;
// Execute doesn't post the changes
dataContainer->qryGeneric->Execute() ;
// Apply Updates complains about qryGeneric being closed
dataContainer->qryGeneric->ApplyUpdates() ;

What's wrong?

ApplyUpdates is applicable only for SELECT statements.

> update apparts set selection = 'N'

Would you like for all records of SET table to assign 'N' for selection field? This code must work, please send a complete sample and we could answer exactly.

Yes I need to reset all the records.
The Execute() works, but nothing at all is changed in the table.
There should be something similar to a COMMIT somewhere.

Sending a complete sample is kind of difficult since you don't have the table and other components.

But here is the code:

// Reset to N all records
qryGeneric->Close() ;
qryGeneric->SQL->Clear() ;
qryGeneric->SQL->Add(
"UPDATE apparts SET selection='N' ;" ) ;
qryGeneric->Execute() ;

// Then select the list again
qryGeneric->Close() ;
qryGeneric->SQL->Clear() ;
qryGeneric->SQL->Add(
"SELECT ap.*, im.adresse FROM apparts ap, immeubles im "
"WHERE ap.dateresiliation IS NOT NULL AND "
"ap.dateremiselocation IS NULL AND ap.noimmeuble = im.noimmeuble;" ) ;
qryGeneric->Open() ;

> There should be something similar to a COMMIT somewhere.

Is it possible that you started a transaction?

No,
you have the entire SQL code in my example. No transactions.
If I run the SQL code into any SQL editor connected to the MySQL database, it works perfectly.

We couldn't reproduce the problem.
Please send us complete small sample to demonstrate it and include script to create server objects.

Can it be that you've set CachedUpdates = True ?

I just suppressed the TMyConnection component, and put a new one in the project. Reconnected the tables and everything works correctly now.
It has the same properties as the previous one. Try to understand !
I guess something got screwed up in the DFM file.
It is ok now.