#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Delphi XE2 and mysql5


    I have a sum query on a integer field in delphi. It does not work. It tells me invalid field. If i change the TYPE from INTEGER to DOUBLE the sum query works. Any ideas. The sum query (with the integer field) works in MYSQL if i run it there.But if i run it through DELPHI it does not work.

    Please help
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    352
    Rep Power
    7
    Originally Posted by icuras
    I have a sum query on a integer field in delphi. It does not work. It tells me invalid field. If i change the TYPE from INTEGER to DOUBLE the sum query works. Any ideas. The sum query (with the integer field) works in MYSQL if i run it there.But if i run it through DELPHI it does not work.

    Please help
    Please provide the structure of your table(s) and the query...
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Re


    Here is the query

    Code:
               
    var
    vvvPanels :integer;
    vvvClientValue :real;
    begin
    with qJc2 do
                begin
                  close;
                  SQl.Clear;
                  SQL.add(' SELECT sum(ClientPanel) as vPanels, sum(ClientValue) as vClientValue from clientmaster where clientnumber =:clientnumber')
    ParamByName('clientnumber').AsString := 'S001'
    open;
    vvvPanels := FieldByName('vPanels').AsInteger;
    vvvClientValue := FieldByName('vClientValue').AsInteger;
    close;
    end;
    The Table is pretty standard

    I have the following

    ClientNumber :String;
    Client Name :string;
    ClientPanel :integer;
    ClientValue :double;

    I have tried getting the Panels out as a float as well. Just dont pick it up.The CLIENT VALUE it picks up perfectly.

    Any ideas. Only solution that i know works if i change the integer in the database to float. But i dont want to do that as there is many instances throughout the program where i use this field as an integer.So i have to change a lot of code in other parts of the program

    Please help
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    352
    Rep Power
    7
    Originally Posted by icuras
    Here is the query

    Code:
               
    var
    vvvPanels :integer;
    vvvClientValue :real;
    begin
    with qJc2 do
                begin
                  close;
                  SQl.Clear;
                  SQL.add(' SELECT sum(ClientPanel) as vPanels, sum(ClientValue) as vClientValue from clientmaster where clientnumber =:clientnumber')
    ParamByName('clientnumber').AsString := 'S001'
    open;
    vvvPanels := FieldByName('vPanels').AsInteger;
    vvvClientValue := FieldByName('vClientValue').AsInteger;
    close;
    end;
    The Table is pretty standard

    I have the following

    ClientNumber :String;
    Client Name :string;
    ClientPanel :integer;
    ClientValue :double;

    I have tried getting the Panels out as a float as well. Just dont pick it up.The CLIENT VALUE it picks up perfectly.

    Any ideas. Only solution that i know works if i change the integer in the database to float. But i dont want to do that as there is many instances throughout the program where i use this field as an integer.So i have to change a lot of code in other parts of the program

    Please help
    Possibly, missing a Group By, other than that, you could cast or convert your result to a specific data type

    http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html
    Last edited by majlumbo; May 3rd, 2013 at 08:15 AM.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2007
    Posts
    169
    Rep Power
    37

    Re


    Thanks Ive tried group by. It did not make a difference. Iv tried casting as well. Dont seem to work. Please look at the code

    Code:
    SELECT cast(sum(Clientpanel) as vPanels)) as double(13,2) as newvariable FROM clientmaster
    What i dont understand is why delphi would have a problem with this query but a frontend like SQLYOG or mysqlbrowser it runs fine.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2008
    Posts
    352
    Rep Power
    7
    Originally Posted by icuras
    Thanks Ive tried group by. It did not make a difference. Iv tried casting as well. Dont seem to work. Please look at the code

    Code:
    SELECT cast(sum(Clientpanel) as vPanels)) as double(13,2) as newvariable FROM clientmaster
    What i dont understand is why delphi would have a problem with this query but a frontend like SQLYOG or mysqlbrowser it runs fine.
    The cast should look like so...

    Code:
    SELECT cast(sum(ClientPanel), AS SIGNED) as vPanels, sum(ClientValue) as vClientValue from clientmaster where clientnumber =:clientnumber
    or if you do want it to be a float, then replace SIGNED with Decimal(13,2)

IMN logo majestic logo threadwatch logo seochat tools logo