?
Solved

ZEOS ZUpdateSQL + MySQL problem

Posted on 2005-03-24
7
Medium Priority
?
1,961 Views
Last Modified: 2010-08-05
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
Comment
Question by:zc_
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13620669
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
 
LVL 15

Expert Comment

by:mikelittlewood
ID: 13620696
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
 

Author Comment

by:zc_
ID: 13620815
Thank You for your fast respond. But both versions doesn't work.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:zc_
ID: 13620870
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
 
LVL 15

Accepted Solution

by:
mikelittlewood earned 1500 total points
ID: 13624271
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
 

Author Comment

by:zc_
ID: 13627263
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
 

Author Comment

by:zc_
ID: 13650138
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question