Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2015
  • Last Modified:

ZEOS ZUpdateSQL + MySQL problem

Hi!

I have a problem with ZEOS ZUpdateSQL in combination with ZQuery. ZUpdateSQL is assigned to ZQuery as an UpdateObject. The Connection to the DB is OK.

With the ZQuery object I search for a  row in a table where a value named PollKey is the the Key. That works fine. Now I want to update the field named FieldName with the value of FieldValue with ZUPdateSQL. But my code (the update) doesn't work:

--
    ZUpdateQuery.SQL.Clear;
    ZUpdateQuery.SQL.Add('SELECT *');
    ZUpdateQuery.SQL.Add('FROM ' + PollTable);
    ZUpdateQuery.SQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');

    ZUpdateSQL.ModifySQL.Clear;
    ZUpdateSQL.ModifySQL.Add('UPDATE `' + PollTable + '`');
    ZUpdateSQL.ModifySQL.Add('SET `' + FieldName + '` = ''' + FieldValue + '''');
    ZUpdateSQL.ModifySQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');

   ZUpdateQuery.Open;
--

Any ideas?

Thank You!
zc
0
zc_
Asked:
zc_
  • 4
  • 3
1 Solution
 
mikelittlewoodCommented:
Do you not have to actually grab the key value you want to use first, then pass it to the second query?
I havent used ZEOS components before so I dont know if they have some sort of facility to do the operations you want in one go.
I would have expected to see something like:

--
    ZUpdateQuery.SQL.Clear;
    ZUpdateQuery.SQL.Add('SELECT *');
    ZUpdateQuery.SQL.Add('FROM ' + PollTable);
    ZUpdateQuery.SQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');
    Open;

    ZUpdateSQL.ModifySQL.Clear;
    ZUpdateSQL.ModifySQL.Add('UPDATE `' + PollTable + '`');
    ZUpdateSQL.ModifySQL.Add('SET `' + FieldName + '` = ''' + ZUpdateQuery.FieldByName('FieldValue').asString + '''');
    ZUpdateSQL.ModifySQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');

    ZUpdateQuery.ExecSQL;
--
0
 
mikelittlewoodCommented:
Even if you used the same query to do both things you would see (I dont know what the USING utf8 part is for but I guess you do):

var
  sKey: String;
--
    ZUpdateQuery.Close;
    ZUpdateQuery.SQL.Clear;
    ZUpdateQuery.SQL.Add('SELECT *');
    ZUpdateQuery.SQL.Add('FROM ' + PollTable);
    ZUpdateQuery.SQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');
    Open;
    sKey := ZUpdateQuery.FieldByName('FieldValue').asString;

    Close;
    ZUpdateQuery.ModifySQL.Clear;
    ZUpdateQuery.ModifySQL.Add('UPDATE `' + PollTable + '`');
    ZUpdateQuery.ModifySQL.Add('SET `' + FieldName + '` = ''' + sKey + '''');
    ZUpdateQuery.ModifySQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');
    ZUpdateQuery.ExecSQL;
--
0
 
zc_Author Commented:
Thank You for your fast respond. But both versions doesn't work.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
zc_Author Commented:
Hmm. You Brought me on an idea:

    ZUpdateQuery.SQL.Clear;
    ZUpdateQuery.SQL.Add('SELECT *');
    ZUpdateQuery.SQL.Add('FROM ' + PollTable);
    ZUpdateQuery.SQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');
    Open;

    ZUpdateQuery.First;
    ZUpdateQuery.Edit;

    ZUpdateQuery.FieldByName(PollField).Value := PollValue;

    ZUpdateQuery.Post;



But this also doesn't work.
0
 
mikelittlewoodCommented:
What does USING utf8 mean and I might be able to shed some more light for you
Can you give me an example of what variables might be going in PollKey and Key as well please  :o)
0
 
zc_Author Commented:
Okay, here it comes :)

PollKey is the name of the id field ('KEY') saved in a variable. The Key value is the id I am serching for. 'KEY' is a varchar(20) field in the mysql database. it has values like '00000000000000002656'. Without utf8 and Convert my select order doesn't work. Don't ask me why. Key is in Delphi a normal string Value (also PollKey).

But my problem is that this update order with ZQuery works:

    ZUpdateQuery.SQL.Clear;
    ZUpdateQuery.SQL.Add('UPDATE `' + PollTable + '`');
    ZUpdateQuery.SQL.Add('SET `' + FieldName + '` = ''' + FieldValue + '''');
    ZUpdateQuery.SQL.Add('WHERE CONVERT( `' + PollKey + '` USING utf8 ) = ''' + Key + ''' LIMIT 1 ;');

    ZUpdateQuery.ExecSQL;

but only on my machine (mysql 4.1.10) but not on other machines. I get always a Delphi Exception with this but the value is changed. Because of this I want to do the changes with ZUpdateSQL. This is the correct way I think...
0
 
zc_Author Commented:
I've found the problem on my own.

The problem was the convert order (CONVERT( `' + PollKey + '` USING utf8 )) in my update script. Without this everything works fine even with older mysql dbs.

mike brought me on the idea to check it again. Because of this I give him my points.

thank you.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now