Help to Use PROP:SQL

Tags: #<Tag:0x00007f225147b178>

Hello everyone, I would like to know how to use PROP:SQL, I want to do searches in the database, but I’m used to using TPS, so I’ve always used the normal filters.
And I would also like to know which one you recommend me to use ODBC, Clarion or ABC?

Using ODBC with PostgreSQL and ABC.
Easiest is to use the same filter statements as you might embed with TPS.
For instance, in the browse ApplyFilter method before parent call:
SELF.SetFilter(‘MyPre:FieldNameA=’ & Loc:FieldValueA, ‘1Filter’)
SELF.SetFilter(‘MyPre:FieldNameB=’ & SQuote(Loc:FieldStringB), ‘2Filter’)

If you have a situation where you want to pass SQL statement directly:
DctTableLabel{PROP:Alias} = ‘v’
SELF.SetFilter(‘SQL(v.fieldnamea=’ & Loc:FieldValueA & ‘);’ , ‘1Filter’)
SELF.SetFilter(‘SQL(v.fieldnameb=’ & SQuote(Loc:FieldStringB) & ‘);’ , ‘2Filter’)

For both situations, the ODBC driver will do the work to generate a proper SQL query.
If you are not getting what you expect, you can always use ODBC Admin trace to see exactly what is being passed to PG.

1 Like

I am going to try.
I didn’t know Clarion interpreted it like that.

{prop:sql} allows you to perform direct syntax like select statement , update, insert ,… etc.

I used it with MSSQL and ODBC (ms accesss) and the speed is the same as using the original engine , sometimes I don’t even need to define the tables in anywhere in clarion, just run the query and get your results.



Furthermore, it is so efficient with data grouping which I find hard in clarion while it takes seconds with millions of records using prop:sql