topic-35930

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Build-in Querybuilder
Posted: Tue Aug 07, 2018 06:31 AM

Hi,

I want to implement a query-builder in an application.
Is there an easy way to verify the correct syntax before running the query?
The users of the application are engineers, so they know how to make queries.

I want that they enter something like :
NAME = 'FI123' OR PTDESC like '%B701%'
They don't have to enter the table-name. It's one that is selected, so i can already enter the beginning op the query....
I just add the antered text after the 'WHERE' clause

It will be also nice that the is maybe an autocomplete, or a selection-list of the available field-names

Antonio Linares

Posts: 44158

Joined: Thu Oct 06, 2005 05:47 PM

Re: Build-in Querybuilder
Posted: Tue Aug 07, 2018 07:18 AM

Marc,

Please review FWH function FWExpBuilder( cExp, oRs, nClrTxt, nClrBack )

This function is used from FWH\samples\fivedbu.prg so you can test it

regards, saludos

Antonio Linares
www.fivetechsoft.com

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Build-in Querybuilder
Posted: Tue Aug 07, 2018 07:31 AM

Thank you Antonio,

That's a very good start.

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Build-in Querybuilder
Posted: Fri Aug 17, 2018 03:17 PM

Antonio,

I have tested it, but I have a problem.
I'm using a recordset.

I first call FWExpBuilder()

Then I call FWCheckExpression() to see if the result is ok, but I'm always get the error 'Variable does not exist' This is the fieldname of the oRs.
I tested it with FWH64 May 2016 (FWH 6.04 Revd Build-3)

Antonio Linares

Posts: 44158

Joined: Thu Oct 06, 2005 05:47 PM

Re: Build-in Querybuilder
Posted: Fri Aug 17, 2018 09:11 PM

Please copy here the expression that you build

In case that you are using a local variable, turn it into a public one

regards, saludos

Antonio Linares
www.fivetechsoft.com

Marc Vanzegbroeck

Posts: 1195

Joined: Mon Oct 17, 2005 05:41 AM

Re: Build-in Querybuilder
Posted: Sat Aug 18, 2018 08:44 AM

Here is a little test:
adoHaalPointselRecordset() returns the recordset.

Code (fw): Select all Collapse

 FUNCTION tpsquery()
local cfilter :=  ''
oRsQuery = adoHaalPointselRecordset()
FWExpBuilder( @cFilter, oRsQuery )
IF FWCheckExpression (cfilter)
ELSE
    msgalert('Filter invalid')
ENDIF
return nil

This is the query: