SQLUpdate???

sjordi · Post by **sjordi** » Tue 15 Mar 2005 13:13

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.

Ikar · Post by **Ikar** » Wed 16 Mar 2005 09:31

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.

sjordi · Post by **sjordi** » Thu 17 Mar 2005 07:49

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?

Ikar · Post by **Ikar** » Thu 17 Mar 2005 10:39

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.

sjordi · Post by **sjordi** » Thu 17 Mar 2005 11:20

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() ;

Ikar · Post by **Ikar** » Thu 17 Mar 2005 14:34

> There should be something similar to a COMMIT somewhere.

Is it possible that you started a transaction?

sjordi · Post by **sjordi** » Thu 17 Mar 2005 17:32

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.

Ikar · Post by **Ikar** » Fri 18 Mar 2005 08:01

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

GEswin · Post by **GEswin** » Fri 18 Mar 2005 09:35

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

sjordi · Post by **sjordi** » Fri 18 Mar 2005 09:46

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.