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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I don't think NAME is a reserved word in SQL, but DATE, TIME, NUMBER are (I think).
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
:) 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.
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.
ASKER
YES SIR!,YES!! :):):) I will give it a try :)
ASKER
AllFields := 'o.order_id,o.number,o.sta tus,o.proc ess_status ,o.quantit y,o.referr er,o.order _product_i d';
AllFields_1 := ',o.delivery_value,o.extra _delivery_ value,o.de livery_cha rge,o.sale _price,o.i p,o.sess_i d,o.date,o .time,o.pr oblem,o.de livery_sta tus,o.labe lled,o.inv oiced,o.sh ipped,o.pr ocessed_by ';
AllFields2 := ',c.customer_id,c.order_nu mber,c.bna me,c.bsurn ame,c.emai l,c.bphone ,c.bmobile ,c.b_house _no,c.badd r1,c.baddr 2,c.baddr3 ,c.bcity,c .bpostcode ,c.bcountr y,c.sname, c.ssurname ,c.sphone, c.smobile, c.s_house_ no';
AllFields2_1 := ',c.saddr1,c.saddr2,c.sadd r3,c.scity ,c.spostco de,c.scoun try,c.ip,c .sess_id,c .referrer_ partner,c. comment,c. card_numbe r,c.card_t ype,c.card _issue,c.c ard_valid_ from_month ';
AllFields2_2 := ',c.card_valid_from_year,c .card_expi re_month,c .card_expi re_year,c. card_secur ity_code,c .software_ card_name, c.software _mii_name, c.software _issuer,c. software_c ountry,c.a dditional_ note,c.whe re_have_yo u_seen_us, c.no_of_pr inted,c.da te,c.time' ;
conditionalSQL.Add ('SELECT DISTINCT '+AllFields+AllFields_1+Al lFields2+A llFields2_ 1+AllField s2_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('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.bname LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.bsurname LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.bpostcode LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.spostcode LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.email LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.card_number LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%'));
//conditionalSQL.Add ('AND orders_copy.o_number = customer.order_number');
conditionalSQL.Add ('GROUP BY o.number');
SAME ERROR!
AllFields_1 := ',o.delivery_value,o.extra
AllFields2 := ',c.customer_id,c.order_nu
AllFields2_1 := ',c.saddr1,c.saddr2,c.sadd
AllFields2_2 := ',c.card_valid_from_year,c
conditionalSQL.Add ('SELECT DISTINCT '+AllFields+AllFields_1+Al
conditionalSQL.Add ('LEFT JOIN orders o ON (o.number = c.order_number)');
conditionalSQL.Add ('WHERE c.order_number LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.bname LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.bsurname LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.bpostcode LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.spostcode LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.email LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.card_number LIKE '+quotedStr('%'+edtRHSearc
//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
????
I see: o.date
????
o."time"
and if any other reserved words ....
and if any other reserved words ....
ASKER
AllFields := 'o.order_id,o."number",o.s tatus,o.pr ocess_stat us,o.quant ity,o.refe rrer,o.ord er_product _id';
AllFields_1 := ',o.delivery_value,o.extra _delivery_ value,o.de livery_cha rge,o.sale _price,o.i p,o.sess_i d,o."date" ,o."time", o.problem, o.delivery _status,o. labelled,o .invoiced, o.shipped, o.processe d_by';
AllFields2 := ',c.customer_id,c.order_nu mber,c.bna me,c.bsurn ame,c.emai l,c.bphone ,c.bmobile ,c.b_house _no,c.badd r1,c.baddr 2,c.baddr3 ,c.bcity,c .bpostcode ,c.bcountr y,c.sname, c.ssurname ,c.sphone, c.smobile, c.s_house_ no';
AllFields2_1 := ',c.saddr1,c.saddr2,c.sadd r3,c.scity ,c.spostco de,c.scoun try,c.ip,c .sess_id,c .referrer_ partner,c. comment,c. card_numbe r,c.card_t ype,c.card _issue,c.c ard_valid_ from_month ';
AllFields2_2 := ',c.card_valid_from_year,c .card_expi re_month,c .card_expi re_year,c. card_secur ity_code,c .software_ card_name, c.software _mii_name, c.software _issuer,c. software_c ountry,c.a dditional_ note,c.whe re_have_yo u_seen_us, c.no_of_pr inted,c."d ate",c."ti me"';
conditionalSQL.Add ('SELECT DISTINCT '+AllFields+AllFields_1+Al lFields2+A llFields2_ 1+AllField s2_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('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.bname LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.bsurname LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.bpostcode LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.spostcode LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.email LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%')+' OR ');
conditionalSQL.Add ('c.card_number LIKE '+quotedStr('%'+edtRHSearc hOrder.Tex t+'%'));
when I run this query it says
You have an error in your SQL syntax near "number",o.status,o.proces s....
AllFields_1 := ',o.delivery_value,o.extra
AllFields2 := ',c.customer_id,c.order_nu
AllFields2_1 := ',c.saddr1,c.saddr2,c.sadd
AllFields2_2 := ',c.card_valid_from_year,c
conditionalSQL.Add ('SELECT DISTINCT '+AllFields+AllFields_1+Al
conditionalSQL.Add ('LEFT JOIN orders o ON (o.number = c.order_number)');
conditionalSQL.Add ('WHERE c.order_number LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.bname LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.bsurname LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.bpostcode LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.spostcode LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.email LIKE '+quotedStr('%'+edtRHSearc
conditionalSQL.Add ('c.card_number LIKE '+quotedStr('%'+edtRHSearc
when I run this query it says
You have an error in your SQL syntax near "number",o.status,o.proces
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.
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?"
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?"
ASKER
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 ....
"c.date"
"c.time"
"c.number"
I tested it with an Access database: it works fine ....
ASKER
sorry, nothing changes! Invalid Argument
I sorry too, I tested with an Access database with ADO components ....
ASKER
there might be another problem. I will try to change database field names :(
Is this a mySQL table?
ASKER
yes it is
ASKER
I changed the database and guess what! Still same error. I got the error in this file
ZAbstractRODataset.pas
and the error
function TZAbstractRODataset.Create ResultSet( SQL: string;
MaxRows: Integer): IZResultSet;
begin
Connection.ShowSQLHourGlas s;
try
if not Assigned(Statement) then
Statement := CreateStatement(FSQL.State ments[0].S QL, Properties);
SetStatementParams(Stateme nt, FSQL.Statements[0].ParamNa mesArray,
FParams, FDataLink);
if RequestLive then -------------------------- ---------- ---------- ---------- ---------- >>**** Delphi stops here *****
Statement.SetResultSetConc urrency(rc Updatable)
else Statement.SetResultSetConc urrency(rc ReadOnly);
Statement.SetFetchDirectio n(fdForwar d);
if IsUniDirectional then
Statement.SetResultSetType (rtForward Only)
else Statement.SetResultSetType (rtScrollI nsensitive );
if MaxRows > 0 then
Statement.SetMaxRows(MaxRo ws);
Result := Statement.ExecuteQueryPrep ared;
finally
Connection.HideSQLHourGlas s;
end;
end;
I'm ready to give more point if this is solved.
ZAbstractRODataset.pas
and the error
function TZAbstractRODataset.Create
MaxRows: Integer): IZResultSet;
begin
Connection.ShowSQLHourGlas
try
if not Assigned(Statement) then
Statement := CreateStatement(FSQL.State
SetStatementParams(Stateme
FParams, FDataLink);
if RequestLive then --------------------------
Statement.SetResultSetConc
else Statement.SetResultSetConc
Statement.SetFetchDirectio
if IsUniDirectional then
Statement.SetResultSetType
else Statement.SetResultSetType
if MaxRows > 0 then
Statement.SetMaxRows(MaxRo
Result := Statement.ExecuteQueryPrep
finally
Connection.HideSQLHourGlas
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
Maybe someone else can remember what they were
ASKER
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_numbe r` LIKE "%'+edtRHSearchOrder.Text+ '%") OR'
+'(`customer`.`bphone` LIKE "%'+edtRHSearchOrder.Text+ '%") OR'
+'(`customer`.`sphone` LIKE "%'+edtRHSearchOrder.Text+ '%")'
+'GROUP BY `orders`.`number` ORDER BY `orders`.`number` DESC';
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`
+'WHERE'
+'(`customer`.`bpostcode` LIKE "%'+edtRHSearchOrder.Text+
+'(`customer`.`spostcode` LIKE "%'+edtRHSearchOrder.Text+
+'(`customer`.`bsurname` LIKE "%'+edtRHSearchOrder.Text+
+'(`customer`.`ssurname` LIKE "%'+edtRHSearchOrder.Text+
+'(`customer`.`card_number
+'(`customer`.`email` LIKE "%'+edtRHSearchOrder.Text+
+'(`customer`.`order_numbe
+'(`customer`.`bphone` LIKE "%'+edtRHSearchOrder.Text+
+'(`customer`.`sphone` LIKE "%'+edtRHSearchOrder.Text+
+'GROUP BY `orders`.`number` ORDER BY `orders`.`number` DESC';
Fantastic !
I did not know that ....
I did not know that ....
Yes, the tick symbol (`) has special meaning in mySQL.