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
zc_Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.