Sql error

[PaulinhoCe]

Good afternoon, I am using Delphi 7 with zquery and I have a problem when I build a SQL statement to filter the data.
usuario_id the field is integer but when I compare with the field in another table with the same structure, sql says that the types are incompatible: string and integer. See figure.

I do not know why this happens because the two fields are integer, the two structures are identical but must be transformed into a string of fields to compare and the result is empty. Does the error is in assembling the sql?

zquery1.Close;
zquery1.sql.clear;
zquery1.sql.add ('select * from quotes where usuario_id = "' + zquery2usuario_id.value +'"');
zquery1.Open;

Thank you for answers,

Paulinho Cé

[mdaems]

This is a delphi error. Probably the data type of zquery2usuario_id.value is an integer. So you'll have to put IntToStr() around.

Mark

[PaulinhoCe]

Yes, I've done that but the query returns empty, does not filter properly.
The problem is that SQL interprets as a string field in the table and asks to turn the variable into string but returns an empty result.
I've used the TrimRight to eliminate empty spaces but still resulting empty.
With BDE do not, the comparison is done correctly and returns.
The question is why Zquery returns a string field as if it is defined as integer?


Thanks,

Paulinho Cé

[guidoaerts]

maybe you better put the variable in a param

zquery1.Close;
zquery1.sql.clear;
zquery1.sql.add ('select * from quotes where usuario_id = :yourparam');
zquery1.parambyname('yourparam').value := zquery2usuario_id.asinteger;
zquery1.Open;

guido

[mdaems]

Did you try adding a TZSQLMonitor to your application, so you can see in the log exaclty what query is sent to the server? Then try if this query returns a result at the mysql command line (or in phpmyadmin).

Mark

[PaulinhoCe]

Friends, let me explain the situation: I have a zquery2 (users) where I search the user name typed in the edit. Finding the name, the value of usuario_id have to be the parameter for searching the zquery1 (budgets).

In BDE and other database engines this strategy works perfectly but with Mysql and Zeos, does not work correctly.
Where is the error: Mysql or Zeos?

procedure Tforca.Edit7Change(Sender: TObject);
begin
zquery2.close;
zquery2.SQL.clear;
zquery2.SQL.add('select * from users where nome like"'+edit7.text+'%"');
zquery2.Open;
showmessage(zquery2usuario_id.asstring);

zquery1.Close;
zquery1.sql.clear;
zquery1.sql.add ('select * from orcamentos where usuario_id = :yourparam');
zquery1.parambyname('yourparam').value := zquery2usuario_id.asinteger;
zquery1.Open;
showmessage(zquery1usuario_id.asstring);
end;

I'm using ShowMessage () to check the value returned by the query.
Enter the name or the first letters in the edit and zquery2 usuario_id returns the value correctly.
Using this parameter in the search usuario_id zquery1 the return is empty when it should be the same as zquery2.

Thanks for all,

Paulinho cé

[guidoaerts]

I tried this with Firebird, works ok.
Except that I use zquery.fieldbyname() in stead of persistent fields. But that shouldn't be a problem.
Guido