Build-in Querybuilder

[Marc Vanzegbroeck]

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]

Marc,

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

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

[Marc Vanzegbroeck]

Thank you Antonio,

That's a very good start.

[Marc Vanzegbroeck]

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]

Please copy here the expression that you build

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

[Marc Vanzegbroeck]

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

Code: Select all  Expand view  RUN

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

This is the query:


Here the message:

[Marc Vanzegbroeck]

I found the mysql_error() in the mariadb functions to display the last sql-error, but don't know how to display it

[Marc Vanzegbroeck]

I found it with calling 'SHOW ERRORS'