How to pass the "where"of a query as a parameter / variable

Comments

• stefque Serbia

  January 2011 edited 5:11PM

  I am not sure if I can answer you, but I came upon this problem too. First solution is to send it as parameter from delphi like this
  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 ....
• rcsoft

  January 2011 edited 5:11PM

  Thank you for your reply, your solution is the first one I'm trying and not working, I make a list:
  
  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!
  
  
• rcsoft

  January 2011 edited 5:11PM

  No response on this? I have to make a decision on the purchase and I would, thank you.
  
• gordk St.Catherines On. Canada.

  January 2011 edited 5:11PM

  are you using the internal ado engine in the report
  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
• rcsoft

  January 2011 edited 5:11PM

  I tried to define the variable with the 'character and the character "while not giving error but the condition is not processed past ritornarndo always a blank query.
  
• gordk St.Catherines On. Canada.

  January 2011 edited 5:11PM

  can you post a small sample demo in the binaries news group?
• gpi

  January 2011 edited 5:11PM

  wrote:

  No response on this? I have to make a decision on the purchase and I would, thank you.

  Why you ignored second post from stefque?
• rcsoft

  January 2011 edited 5:11PM

  Yes, ok. Vamba solve the string ": wheresql" manually at run-time searching for the query object from delphi. But you can not then spend a full term as a variable? This came back to me very comfortable during both the design of reports, and consider the fact that I'm trying to migrate all my prints from Rave in which this worked!
  
  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:
  

  FrVariabili1.PNG 38.1K

  FrVariabili2.PNG 34.4K

  FrVariabili3.PNG 25.6K
• gpi

  January 2011 edited 5:11PM

  Set parGiorno value to 'LUNEDI' instead of LUNEDI
• rcsoft

  January 2011 edited 5:11PM

  Ok, the strings run. But the sql WHERE passed to the internal no! In fact, the report is always "unfiltered. "
  
  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?
  

  FastVar1.PNG 7.9K

  FastVar2.PNG 8.7K

  FastVar3.PNG 26.1K

  FastVar4.PNG 9.4K

  FastVar5.PNG 9.3K
• gpi

  January 2011 edited 5:11PM

  You can't to use macros in ADOQuery (TADOQuery doesn't support macroses), you can build SQL statement or use parameters only
• gorousek

  March 2011 edited 5:11PM

  rcsoft wrote: »

  Ok, the strings run. But the sql WHERE passed to the internal no! In fact, the report is always "unfiltered. "
  
  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.
  
• Mick.pl

  March 2011 edited 5:11PM

  Try such a solution in onBeforePrint event for the band where (and when) you know the whole where clause
  

  SELECT
    T1.*,
    T2.*
  FROM
    Turni T1,
    Guardie T2
  WHERE
    T1.id = T2.id
  

  
  SQL[8] := ' and ' + RestOfYourWhereFhrase
  
  Mick