Lazarus

Programming => Packages and Libraries => LazReport => Topic started by: busicomp on July 08, 2022, 01:11:48 am

Title: Lazreport print sql parameters
Post by: busicomp on July 08, 2022, 01:11:48 am

Greetings.
I'm sure this is possible, but I've spent the last couple of days looking, unsuccessfully, for an answer.

In the old Delphi Qreport, I used the "QUERYPARAM" evaluation function to print out the user submitted values for runtime SQL parameters(eg from_date).

How do I do this in LazReport ?

Apologies if this is covered somewhere, but I just could not find it ~ probably wrong keywords, or similar.

Title: Re: Lazreport print sql parameters
Post by: GAN on July 09, 2022, 06:52:41 am

With variables https://wiki.freepascal.org/LazReport_Tutorial#Setting_up_variables (https://wiki.freepascal.org/LazReport_Tutorial#Setting_up_variables)
but do not asociate them to any field https://wiki.freepascal.org/LazReport_Tutorial#General_purpose_variables (https://wiki.freepascal.org/LazReport_Tutorial#General_purpose_variables)

Title: Re: Lazreport print sql parameters
Post by: busicomp on July 09, 2022, 11:04:29 am

Thanks for the reply! 
I've already seen these tutorials. What I'm after is setting the variable to the run-time entered parameter value. For example Postgresql SQL:
SELECT * FROM TABLE WHERE TABLE.DATEFIELD > :From_Date ;
This already causes the report to prompt for a valid date, stores it in the dataset's "From_Date" parameter, and also selects rows as required.
I'm simply trying to "print" (or set variable to) the entered value for that param.
Do I need to actually use pascal code in a beforeprint, or whatever, to extract the tdataset.params values into a variable that can be used to print?
This is what the standard function QUERYPARAM("dataset_name","param_name") {from memory} returned. It was quite simple and very useful.
Thanks again for any assistance.

Title: Re: Lazreport print sql parameters
Post by: dseligo on July 09, 2022, 01:49:06 pm

AFAIK there is no 'queryparam' function in LazReport.

You could also do it like this:

Code: MySQL  [Select][+][-]

1. SELECT field1, field2, ..., fieldN, :From_Date as From_Date FROM TABLE WHERE TABLE.DATEFIELD > :From_Date;

You can now use 'From_Date' as any other DB field in your report.

Title: Re: Lazreport print sql parameters
Post by: busicomp on July 12, 2022, 02:15:41 am

Just a quick reply:
Doesn't allow me to do that in Postgresql ... still playing and checking for other alternatives.

Thanks

Title: Re: Lazreport print sql parameters
Post by: gucao on July 12, 2022, 05:30:38 am

Maybe this will help you

Code: Pascal  [Select][+][-]

1. begin
2.   VDateStr:=FORMATDATETIME('yyyy-mm-dd', [lrDateEdit1.Date]);
3.   lrSQLQuery1.Active:=False;
4.   lrSQLQuery1.SQL:='Select F1.AFiled From';
5.   lrSQLQuery1.SQL:=lrSQLQuery1.SQL+'(Select '''+VDateStr+'''::"timestamp" as AFiled)F1';
6.   lrSQLQuery1.SQL:=lrSQLQuery1.SQL+'where F1.AFiled='''+VDateStr+'''';
7.  
8.   MESSAGEBOX([lrSQLQuery1.SQL], '提示', 0);
9.  
10. end;
11.  

Title: Re: Lazreport print sql parameters
Post by: dseligo on July 15, 2022, 10:48:23 am

Quote from: busicomp on July 12, 2022, 02:15:41 am

Just a quick reply:
Doesn't allow me to do that in Postgresql ... still playing and checking for other alternatives.

What do you mean by that? Who doesn't allow you? Do you get an error somewhere?

I just tried with ZEOSdbo components and with built-in SQLdb components and it works just fine. I can attach test project if you need it.

Here is code I used in test (table name is 'proba', column names are 'a' and 'b'):

Code: Pascal  [Select][+][-]

1. procedure TForm1.btnPosgreSQL_SQLdbClick(Sender: TObject);
2. begin
3.   //PQConnection1.Connected := True;
4.   qSQLdb.Close;
5.   qSQLdb.SQL.Text := 'select a, b, :filter as afilter '+
6.                      'from proba '+
7.                     'where a >= :filter';
8.   qSQLdb.ParamByName('filter').AsInteger := StrToInt(edFilter.Text);
9.   qSQLdb.Open;
10. end;