How to pass the "where"of a query as a parameter / variable
I'm considering switching from Rave to Fast-Report, I'm testing the trial version of Fast 4 the only obstacle that we are encountering is how to pass an entire string as a parameter for the WHERE sql. In fact, our entire structure of the press includes the formation of the where clause in the program delphi (eg "id> 10 AND NAME =" FOO ") then the transition to the query that is defined within the fast final type" SELECT * FROM CUSTOMER WHERE: parwhere "Rave with this mechanism worked perfectly, while fast is given an error. And 'possible to resolve or find an alternative to our needs?
Comments
fizvestaj.frxreport1.variables := 'id> 10 AND NAME =" FOO "';
and then use it your SQL as you suggested
I've also used this
(fizvestaj.frxReport1.FindComponent('MyDACQuery1') as TfrxMyDACQuery).SQL.Text:=(fizvestaj.frxReport1.FindComponent('MyDACQuery1') as TfrxMyDACQuery).SQL.Text + 'id> 10 AND NAME =" FOO "';
but this is not good, because if you are changing the report again you must erase the added part every time before you save your work.
And the last
(fizvestaj.frxReport1.FindComponent('MyDACQuery1') as TfrxMyDACQuery).SQL.Text:='here you write the whole SQL statement'
and the program changes it every time as you wish, from delphi...
I am using MYDACQuery components ....
1. Delphi: frxReport.Variables : = WHERE;
2. By Fast Reports, the page data object ADOQuery1.Sql:
Select
T1 .*
T2.Descrizione
From
Turns T1
T2 Guard
WHERE
T1.idGuardia = T2.ID and: wheresql
In ADOQuery1.Params
wheresql = to the variable: <parWheresql>
If I pass the variable <parWheresql> for example the string "ID = 1" is given an error in undeclared identifier -> 'ID' although a field ID of the table, the same happens if I assign a value to default variable within the fast report, cio?? me it is very convenient because I can test printing design-time without running the exe delphi.
-L 'your last solution is one that works, and that I could replace my intuition mechanism, but that will have to draw the report blindly without being able to prove that as the parameter: wheresql inside SQL always returns a set empty.
Finally I tried the following code:
ListaQuery: = TStrings.Create;
frxReport.GetDataSetList (ListaQuery);
for i: = 0 to ListaQuery.Count-1 do begin
frxQuery: = TfrxADOQuery (frxReport.GetDataset (ListaQuery ));
Sql: = frxQuery.SQL.Text;
ReplaceStr (sql, ': wheresql', WHERE);
frxQuery.SQL.Text: = sql;
end;
But the line: frxReport.GetDataSetList (ListaQuery) went into the exception!
if you are, look at the main demo internal datasets internal query report to see how
to set the params and use a dialog box built into the report.
Note when you pass string values into a categorized variable in must have extra delimiters
or it will throw an error
Moreover, the same problem I have played in Fast-Report, perhaps it is enough to put strings with delimiters details, but which ones?
I enclose some pictures explaining:
I enclose some pictures of how I set the parameters and the passing variables in the first case (Figure delphi code: FastVar4) does not work, while in the second one (Figure FastVar5) but is discounted because it changes the SQL string at runtime.
Is there a solution to my problem or should I resign?
You cannot build query that way:
...
T1.idGuardia = T2.ID and: wheresql
You must type all the conditions to the sql request and to the variables you can only assign values, not the whole "WHERE" string.
...
T1.idGuardia = T2.ID
and T1.value1 = :1
and T2.value2 = :2
etc.
SQL[8] := ' and ' + RestOfYourWhereFhrase
Mick