TZQuery update adding where clause to sql statement

Forum related to MySQL

Moderators: gto, cipto_kh, EgonHugeist

thecoder
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 31.12.2011, 17:44

TZQuery update adding where clause to sql statement

Postby thecoder » 31.12.2011, 18:05

Please help. I am using zeoslib 6.6.6 Mysql & Delphi. When I try to update a database table using TZQuery object, with the following simple code, no updates happen. Upon checking the TZSQLMonitor log, I noticed that additional 'WHERE logincnt=0 AND entrydate IS NULL' clauses are being added as below.

Please does anyone know why this is and how to stop it?

Thanks

Dataconnect.GeneralQuery.SQL.Clear;
Dataconnect.GeneralQuery.SQL.Add('select logincnt, cid, entrydate from client_rw where uid = 2);
Dataconnect.GeneralQuery.Active:= True;
Dataconnect.GeneralQuery.Open;
Dataconnect.GeneralQuery.Edit;
Dataconnect.GeneralQuery.FieldValues['logincnt']:= 1;
Dataconnect.GeneralQuery.FieldValues['cid']:= 'testing';
Dataconnect.GeneralQuery.FieldByName('entrydate').AsDateTime:= Date;
Dataconnect.GeneralQuery.Post;

TZSQLMonitor Log

2011-12-31 16:38:00 cat: Execute, proto: mysql-5, msg: select logincnt, cid, entrydate from client_rw where uid = '2'
2011-12-31 16:38:00 cat: Execute, proto: mysql-5, msg: SHOW KEYS FROM securevpn_sch.client_rw
2011-12-31 16:38:00 cat: Execute, proto: mysql-5, msg: UPDATE securevpn_sch.client_rw SET logincnt=1,cid='testing',entrydate='2011-12-31' WHERE logincnt=0 AND entrydate IS NULL
2011-12-31 16:38:07 cat: Execute, proto: mysql-5, msg: select * from client_rw where uid = '2'

2011-12-31 16:38:09 cat: Disconnect, proto: mysql-5, msg: DISCONNECT FROM "securevpn_sch"

seawolf
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Postby seawolf » 31.12.2011, 18:40

Have you tried?

Dataconnect.GeneralQuery.FieldByName('logincnt').AsInteger := 1;

thecoder
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 31.12.2011, 17:44

Postby thecoder » 01.01.2012, 03:37

Hi,

Yes, I get the same result. I cannot imagine where the extra where clause is coming from.

The date one is already in that format.
Ta

thecoder
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 31.12.2011, 17:44

Postby thecoder » 02.01.2012, 14:32

Can anyone tell me the best way to update data in mysql, since the above does not work.

Thanks

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 01.03.2012, 00:15

Does your table contain a primary key and is the query property WhereMode set to wmWhereKeyOnly?
Zeoslib needs to decide the best way to select exactly the one row in the dataset you're updating. When a primary key is available that's enough for a good update, but also check if the right default Wheremode has ben set for your query.

Mark
Image


Return to “MySQL”

Who is online

Users browsing this forum: No registered users and 1 guest