Recent

Author Topic: Lazreport print sql parameters  (Read 567 times)

busicomp

  • New member
  • *
  • Posts: 6
Lazreport print sql parameters
« 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.

GAN

  • Sr. Member
  • ****
  • Posts: 350
Lazarus 2.0.8 FPC 3.0.4 Linux Mint Mate 19.3
Zeos 7̶.̶2̶.̶6̶ 7.1.3a-stable - Sqlite 3.32.3 - LazReport

busicomp

  • New member
  • *
  • Posts: 6
Re: Lazreport print sql parameters
« Reply #2 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.

dseligo

  • Hero Member
  • *****
  • Posts: 707
Re: Lazreport print sql parameters
« Reply #3 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.

busicomp

  • New member
  • *
  • Posts: 6
Re: Lazreport print sql parameters
« Reply #4 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

gucao

  • New Member
  • *
  • Posts: 33
Re: Lazreport print sql parameters
« Reply #5 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.  
« Last Edit: July 12, 2022, 05:32:29 am by gucao »

dseligo

  • Hero Member
  • *****
  • Posts: 707
Re: Lazreport print sql parameters
« Reply #6 on: July 15, 2022, 10:48:23 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;

 

TinyPortal © 2005-2018