Found out why ADO did not return results anymore:
Found out why ADO did not return results anymore:
http://andremussche.blogspot.nl/2013/08/ado-bug-do-not-use-comments-in.html
http://andremussche.blogspot.nl/2013/08/ado-bug-do-not-use-comments-in.html
That's useful to know.
ReplyDeleteWoW!
ReplyDeleteIt is Good, It is Useful (^-^)=b
Ah, I've encountered this bug before, and always tried to avoid comments in queries in ADO, even without parameters.
ReplyDeleteAs far as i recall i did commented a line (disabled it) in Ado
ReplyDeletejust recently and it was OK
Am i missing some thing?
You probably didn't have any parameters?
ReplyDeleteNo you are wrong
ReplyDeleteSELECT EntryDate,GrowerCode,QAType, Kosher, ItemCode, Sum(Qty) AS TQty, Sum(Weight) AS TWeight, Sum(DefectedQty) AS DefQty
,DrWholeBody,DrLiver
FROM tblStock
where (EntryDate >= :FromEntryDate) and (EntryDate <= :ToEntryDate)
--and (GrowerCode >= '0000')
and (QAType <> 'N')
and (ItemCode >= '0003') and (ItemCode <= '0100')
GROUP BY EntryDate,GrowerCode,QAType,ItemCode, Kosher,DrWholeBody,DrLiver
union
SELECT EntryDate,GrowerCode, QAType,Kosher, ItemCode, Sum(Qty) AS TQty, Sum(Weight) AS TWeight, Sum(DefectedQty) AS DefQty
,DrWholeBody,DrLiver
FROM tblStock
where (EntryDate >=:FromEntryDate1) and (EntryDate <= :ToEntryDate1)
--and (GrowerCode >='0000')
and (QAType = 'N')
and (ItemCode =:ItemCode)
GROUP BY EntryDate,GrowerCode,QAType,ItemCode, Kosher,DrWholeBody,DrLiver
ORDER BY EntryDate,GrowerCode,ItemCode,QAType, Kosher DESC
as you can see this -- is a comment and (EntryDate <= :ToEntryDate1)
ReplyDeleteis a parameter
P.S Delphi 7
ReplyDeleteI did notice that it would work sometimes when there were comments and parameters, and sometimes it wouldn't .
ReplyDeleteKeep investigating may be it is somthing else.
ReplyDeleteWhen you say sometimes you mean the SAME CODE?
Then there is a problem
but diffrent CODE ? keep investigating
iS IT Delphi ? WHICH Delphi ARE WE TALKING ABOUT?
P.S i have -- in many SQL code not just once and all work ok.
I can think of may be hiden charcters ?
I goes wrong when you have a comment before the where clause (because the where clause is replaced by 1=2 etc).
ReplyDeleteAnd probably only when you have parameters?
I tested it with sql server and client side cursor (no problem with server side cursor)
can you put a short SQL here (that has a problem) so i will test it tomorow?
ReplyDeleteshlomo abuisak: see my blog for an example :)
ReplyDeleteselect *
from (select 1 as value) as dummytable
--comment before where clause with a parameter
where value > ?
this query won't return any records (should return "1") and all other queries after it too... :(
to try your sql i put it in SQL server 2008 to check for syntax
ReplyDeletewhat i did is
select *
from (select 1 as value) as tblMlay
--comment before where clause with a parameter
where value > 1
however if i use where value > ? it dose not allow it
so if sql server is not working niether will ado Delphi will.
I found that checking SQL against 2008 will always work
Andre put the question mark ? to signify that's where he would put a parameter (in Delphi code), and not the literal question mark.
ReplyDeleteshlomo abuisak That's right: SQL Server Management Studio does not support parameters. 2nd: ? is the way sql server processes parameters. So you need to do this in Delphi like this:
ReplyDeletewith TADOQuery.Create(nil) do
begin
Connection := MyAdoConnection;
SQL.Text := 'select t.v'#13 +
'from (select 1 as v) as t'#13 +
'--dummy'#13 +
'where v > ?';
Parameters.Items[0].Value := 0;
Open;
MessageDlg(Format('%d',[RecordCount]), mtInformation, [mbOK], 0);
end;
Btw: with ? you get an unnamed parameter but works OK in TADOQuery
i've only used the colon with a name type of parameter. So you have :
ReplyDeleteWHERE v > :myparameter
and then in the Delphi code you would use
Parameters.ParamByName('myparameter').asInteger := 0;
Phillip Woon Yes that's the delphi way of handling parameters (to be able to name them) but when you use low level ADO (instead of TADOQuery: big speed improvement! we use them for our own ORM) you need the ? when you talk directly to sql server.
ReplyDeletebtw: it goes wrong too when you use :myparameter
Yes, it goes wrong too. I did not know that about the parameters.. Something to keep in mind for when I need more speed.. Thanks
ReplyDeleteIIRC You can use comments of type /* comment here */ whithout problems.
ReplyDeleteKlaus Edelmann that's true and much safer indeed :) but my customer wanted to dynamically disable a line in sql so -- was the easiest way (and it could be done using a simple search and replace of the starting text instead of exact line with possible misses if one would change the sql in the future).
ReplyDeleteyou could have used an paramter to achieve this:
ReplyDeleteSELECT *
FROM users
WHERE Name1 = 'Muss'
AND (:pOnlyName1 =1 OR Name2 = 'Andr')
In this statemant Parameter pOnlyName1 controls if Name2 has to meet the condition too: When the parameter = 1 then only Name1 has top match, Name1 and Name2 otherwise.
JM2C
Klaus Edelmann I know, but this one was about disabling a "cross apply", not a filter
ReplyDelete(and the cross apply keyword is just in front of the where clause with params and ADO doesn't like comments in front of where lines)
Thanks for the feedback. So I learned sth. about this feature which I didn't know yet ...
ReplyDelete