Link to home
Start Free TrialLog in
Avatar of bilgehanyildirim
bilgehanyildirim

asked on

Refere to an old question

Refere to this question

https://www.experts-exchange.com/questions/21285134/Invalid-Argument.html

the problem was the field name "name". I think this was reserved word for SQL. when I change it to o_name, everything worked fine.

But this database is 2 years old and changing the field name will cause us a lot of problem. So it there any work around for this problem?
ASKER CERTIFIED SOLUTION
Avatar of esoftbg
esoftbg
Flag of Bulgaria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
I don't think NAME is a reserved word in SQL, but DATE, TIME, NUMBER are (I think).
Avatar of bilgehanyildirim
bilgehanyildirim

ASKER

I need to use * instead of all field name. so putting NAME in " marks is not efficient for me.

And I didn't know name was a reserved word but when I changed it to different name like O_NAME the query worked perfetct.
SOLUTION
Avatar of Eddie Shipman
Eddie Shipman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
:) I know it is but there is a web site and a software running on that DB and we are not able to change the source at the moment,.
[quote]I need to use * instead of all field name. so putting NAME in " marks is not efficient for me[/quote]
You have to do what you have to do to get your program working
and if you need to add all fields in your query, I suggest you do so.
The price is very small compared to other options.
YES SIR!,YES!! :):):) I will give it a try :)
     AllFields := 'o.order_id,o.number,o.status,o.process_status,o.quantity,o.referrer,o.order_product_id';
      AllFields_1 := ',o.delivery_value,o.extra_delivery_value,o.delivery_charge,o.sale_price,o.ip,o.sess_id,o.date,o.time,o.problem,o.delivery_status,o.labelled,o.invoiced,o.shipped,o.processed_by';
      AllFields2 := ',c.customer_id,c.order_number,c.bname,c.bsurname,c.email,c.bphone,c.bmobile,c.b_house_no,c.baddr1,c.baddr2,c.baddr3,c.bcity,c.bpostcode,c.bcountry,c.sname,c.ssurname,c.sphone,c.smobile,c.s_house_no';
      AllFields2_1 := ',c.saddr1,c.saddr2,c.saddr3,c.scity,c.spostcode,c.scountry,c.ip,c.sess_id,c.referrer_partner,c.comment,c.card_number,c.card_type,c.card_issue,c.card_valid_from_month';
      AllFields2_2 := ',c.card_valid_from_year,c.card_expire_month,c.card_expire_year,c.card_security_code,c.software_card_name,c.software_mii_name,c.software_issuer,c.software_country,c.additional_note,c.where_have_you_seen_us,c.no_of_printed,c.date,c.time';

      conditionalSQL.Add ('SELECT DISTINCT '+AllFields+AllFields_1+AllFields2+AllFields2_1+AllFields2_2+' FROM customer AS c');
      conditionalSQL.Add ('LEFT JOIN orders o ON (o.number = c.order_number)');
      conditionalSQL.Add ('WHERE c.order_number LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.bname LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.bsurname LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.bpostcode LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.spostcode LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.email LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.card_number LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%'));
      //conditionalSQL.Add ('AND orders_copy.o_number = customer.order_number');
      conditionalSQL.Add ('GROUP BY o.number');

SAME ERROR!
I don't see:        o."date"
I see:                o.date
????
o."time"
and if any other reserved words ....
     AllFields := 'o.order_id,o."number",o.status,o.process_status,o.quantity,o.referrer,o.order_product_id';
      AllFields_1 := ',o.delivery_value,o.extra_delivery_value,o.delivery_charge,o.sale_price,o.ip,o.sess_id,o."date",o."time",o.problem,o.delivery_status,o.labelled,o.invoiced,o.shipped,o.processed_by';
      AllFields2 := ',c.customer_id,c.order_number,c.bname,c.bsurname,c.email,c.bphone,c.bmobile,c.b_house_no,c.baddr1,c.baddr2,c.baddr3,c.bcity,c.bpostcode,c.bcountry,c.sname,c.ssurname,c.sphone,c.smobile,c.s_house_no';
      AllFields2_1 := ',c.saddr1,c.saddr2,c.saddr3,c.scity,c.spostcode,c.scountry,c.ip,c.sess_id,c.referrer_partner,c.comment,c.card_number,c.card_type,c.card_issue,c.card_valid_from_month';
      AllFields2_2 := ',c.card_valid_from_year,c.card_expire_month,c.card_expire_year,c.card_security_code,c.software_card_name,c.software_mii_name,c.software_issuer,c.software_country,c.additional_note,c.where_have_you_seen_us,c.no_of_printed,c."date",c."time"';

      conditionalSQL.Add ('SELECT DISTINCT '+AllFields+AllFields_1+AllFields2+AllFields2_1+AllFields2_2+' FROM customer AS c');
      conditionalSQL.Add ('LEFT JOIN orders o ON (o.number = c.order_number)');
      conditionalSQL.Add ('WHERE c.order_number LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.bname LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.bsurname LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.bpostcode LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.spostcode LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.email LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%')+' OR ');
      conditionalSQL.Add ('c.card_number LIKE '+quotedStr('%'+edtRHSearchOrder.Text+'%'));

when I run this query it says

You have an error in your SQL syntax near "number",o.status,o.process....
If your using an SQL server and ADO Components, there is no need to change field names at al if you don't want to.

Just use:
SELECT
  [ID], [DATE], [TIME], [NUMBER]
FROM
  YourTable

The square brackets will return the field whether or not it is a reserved word.
BTW ... I would never use:

SELECT * FROM ...

I prefer to list the names of all fields regardless of whether I want 1 or all.
But thats just preference .. and I'm sure some people are now saying "Why?"
I am using ZEOS LIB instead of ADO
excuse me:
"c.date"
"c.time"
"c.number"
I tested it with an Access database: it works fine ....
sorry, nothing changes! Invalid Argument
I sorry too, I tested with an Access database with ADO components ....
there might be another problem. I will try to change database field names :(
Is this a mySQL table?
yes it is
I changed the database and guess what! Still same error. I got the error in this file

ZAbstractRODataset.pas

and the error

function TZAbstractRODataset.CreateResultSet(SQL: string;
  MaxRows: Integer): IZResultSet;
begin
  Connection.ShowSQLHourGlass;
  try
    if not Assigned(Statement) then
      Statement := CreateStatement(FSQL.Statements[0].SQL, Properties);
    SetStatementParams(Statement, FSQL.Statements[0].ParamNamesArray,
      FParams, FDataLink);
    if RequestLive then ------------------------------------------------------------------>>**** Delphi stops here *****
      Statement.SetResultSetConcurrency(rcUpdatable)
    else Statement.SetResultSetConcurrency(rcReadOnly);
    Statement.SetFetchDirection(fdForward);
    if IsUniDirectional then
      Statement.SetResultSetType(rtForwardOnly)
    else Statement.SetResultSetType(rtScrollInsensitive);
    if MaxRows > 0 then
      Statement.SetMaxRows(MaxRows);
    Result := Statement.ExecuteQueryPrepared;
  finally
    Connection.HideSQLHourGlass;
  end;
end;

I'm ready to give more point if this is solved.
hmmm RequestLive ... I remember some old bugs with this statement ... especially with the BDE.
Maybe someone else can remember what they were
YES!!!!

Here is the query which is perfectly working. I think the ` sign solved everything.

      conditionalSQL.text := 'SELECT *'
        +'FROM'
        +'`customer`'
        +'INNER JOIN `orders` ON (`customer`.`order_number` = `orders`.`number`)'
        +'WHERE'
        +'(`customer`.`bpostcode` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`spostcode` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`bsurname` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`ssurname` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`card_number` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`email` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`order_number` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`bphone` LIKE "%'+edtRHSearchOrder.Text+'%") OR'
        +'(`customer`.`sphone` LIKE "%'+edtRHSearchOrder.Text+'%")'
        +'GROUP BY `orders`.`number` ORDER BY `orders`.`number` DESC';
Fantastic !
I did not know that ....
Yes, the tick symbol (`) has special meaning in mySQL.