Specifying key fields in WHERE clause of UPDATE query ?

[Gobol]

Hello all!

At the beginning - this is my first post on this forum, great job providing the alternative way to ODBC/BDE !

I would like to ask how to specify which field would be a key field in WHERE clause of UPDATE query which is send automagically on <obj> . Post; proc ?
I'm trying to use Delphi7 and Zeos 6.5.1a.

My investigations began when I had to post some updates to table with float fields and only some of them (updates) arrived to the destination.
The query sent by the Zeos to MySQL 5 was :

Code: Select all

UPDATE ... SET fKMOG=1492,fKMMIA=1492,fZRP1=766,fZNP1=643.8,fZNP3=22.98,fCPOG=94.0083333333333,fCPEF=94.0083333333333 WHERE NAZWISKO='...' AND sSKIER=1002 AND fKMOG=1367 AND fKMJAZ=0 AND fKMMIA=1367 AND fKMPRZY=0 AND fKMMIAPR=0 AND fZRP1=702 AND fZNP1=589.8 AND fZRP2=0 AND fZNP2=0 AND fZRP3=0 AND fZNP3=21.06 AND fCPOG=86.0083333333333 AND fCPEF=86.0083333333333 AND fCPPR=0

And the query according to WHERE clause affects 0 rows - but it should affect the exact one.
The problem is, that there are float fields in WHERE clause which confuses database in comparision.
So the question is how to switch off that float fields from WHERE clause ?
There is a ProviderFlags property of each TField in Delphi, but I couldn't get it to work properly - it had no effect in queries.

Thanks in advance for replying...

btw: sorry for my english

[mdaems]

Hi,

Did you try the WhereMode=wmWhereKeyOnly setting on your query component? Normally that would do the trick, unless zeos can't decide which key to use. (Hope I don't have to figure out why that's the case)

Give it a try. If that's not the solution we will need some more details!! Database server, create table statement (complete!!) and sql-statement, for instance.

Greets,
Mark

BTW. We've seen worse versions of 'English' here...

[Gobol]

Yes, I tried setting that property (WhereMode) ... each case -> the result was the same - no changes in query sent to the MySQL 5 server by Zeos.
I've also tried setting KeyFields in each of TField object associated to TZTable, I had set the ProviderFlags to [inUpdate] only in all fields except one, and the inKey option in that one field.

I'm using TZTable property as an access component to table created by query :

Code: Select all

CREATE TABLE  `dane`.`dbrwk` (
  `NAZWISKO` varchar(50) character set latin1 collate latin1_bin NOT NULL default '',
  `sSKIER` smallint(5) unsigned NOT NULL default '0',
  `fKMOG` double(15,2) NOT NULL default '0.00',
  `fKMJAZ` double(15,2) NOT NULL default '0.00',
  `fKMMIA` double(15,2) NOT NULL default '0.00',
  `fKMPRZY` double(15,2) NOT NULL default '0.00',
  `fKMMIAPR` double(15,2) NOT NULL default '0.00',
  `fZRP1` double(15,2) NOT NULL default '0.00',
  `fZNP1` double(15,2) NOT NULL default '0.00',
  `fZRP2` double(15,2) NOT NULL default '0.00',
  `fZNP2` double(15,2) NOT NULL default '0.00',
  `fZRP3` double(15,2) NOT NULL default '0.00',
  `fZNP3` double(15,2) NOT NULL default '0.00',
  `fCPOG` double(15,2) NOT NULL default '0.00',
  `fCPEF` double(15,2) NOT NULL default '0.00',
  `fCPPR` double(15,2) NOT NULL default '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=latin2 COMMENT='InnoDB free: 11264 kB';

I have tried other charset & engine options but still no success.

The code in Delphi that modifies that table is like this:

Code: Select all

 Table.Open;
 Table.Edit;
 accessing fields via FieldValues['...']:= or TableFieldName.Value:=
 Table.Post;
 Table.Close;

Table is a TZTable component. Set to work in non-caching mode (CachedUpdates = false).

Do I have to have an index field in table in order to get it to work properly with key fields ?

[Gobol]

Ok, sorry - my fault. I hadn't set a primary key for the table - so the Zeos & MySQL was confused about that what I wanted to do

Anyway thanks for answering.
Greets,
Bogusz