Sql query clarion 10

questions
1

I have had a clarion 10 project converted from TPS to sql. Really to see the advantages of sql and how things work. All good so far.

I am looking at ways of extending the functionality of the program. Currently I have a browse window, there are a few filter fields.

Simply put:

If status 《》 All
Brw1.setfilter = (‘fil:status = status’, 20)

If status2 《》 All
Brw1.setfilter = (‘fil:status2 = status2’, 20)

If status3 《》 All
Brw1.setfilter = (‘fil:status3 = status3’, 20)

The above is summarised as I am typing on my mobile.

The question is: I have two fields in the table with long pieces of text in. Let’s call them text 1 and text 2

I would also like to filter records that match a 4th filter, for example filter 4. Filter 4 would be a text entry box,

So If for example I had a record with The cat sat on the mat in text1 and “the dog sat on the mat” in text2

If I entered ‘cat’ into the search field

Text1 true , text2 false, so show record

If I entered ‘the’

Both records would be true so show record

If I entered ‘dog’ text 2 would be true so show record

If I entered ‘horse’ nothing would show

So how should I code this please

A. What would the best syntax be for returning positive filters on partial matches

B. What would the syntax be to search two fields and returning positive filters if either of them are positive.

C. Is it possible to ignore case on searches ?

2

A. Look up your chosen SQL for LIKE. WHERE text1 LIKE ‘%cat%’
B add OR text2 LIKE ‘%dog%’
C. force everything to upper or lower case. Or if using Postgres look at the CITEXT type

3

Just a side note, any browse boxes which are slow due to a filter, can be created as a stored procedure or a view table in the sql server.

And then import the sql view table into your dct as a table. Any (record/SQL row) (Clarion form/process) updates you need to do on the view will need the unique id of the record in the sql view for clarion to work properly.

SQL View
School Name
Pupil Name

to update school will need
SQL View
School Unique ID (Clarion needs this)
School Name
Pupil Name

to update pupil will need
SQL View
School Name
Pupil Unique ID (Clarion needs this)
Pupil Name

to update both School and Pupil will need
SQL View
School Unique ID (Clarion needs this)
School Name
Pupil Unique ID (Clarion needs this)
Pupil Name

I’d start with creating SQL Views first, before starting with Stored Procedures. You can also pass parameters to Stored Procedures, which can be variables or ranges of variables.

Views are fast.
Stored Procedures are faster than SQL Views, which can be handy if your app is used in a busy environment with lots of updates and users hitting the database all at the same time.

fwiw.

1 Like
4

Hi Just playing with this now.

So using the like statement inside to brw.setfilter command.

Just wondering what the correct syntax needs to be please

I am using

Mybrw.setfilter(‘sql:string like %loc:filter%’)

LIKE ‘%cat%’

5

Mybrw.SetFilter(‘SQL(SQL:String like <39>%’ & loc:Filter & ‘%<39>)’)

What out for SQL injection depending on how loc:Filter is populated.
this is MS SQL syntax.

1 Like
6

Just remember that anything inside the SQL() is pasted literally into the WHERE.
There is no substitution of Clarion variable like normal.

7

Thanks. This worked really well and now I am grasping more about sending sql commands. One more question. The Embedded code to send the sql filter is in my browse.applyfilter routine

To trigger this routine I am using my browser.resetfrombuffer to trigger the filter.

This works OK. But it seams on some filters some fields on the browse form are not being redesplayed.

For example the browse i have , has a description and this description field is also shown on the form for the selected record

The displayed record on screen does not update immediately after a filter. Should I just use display() after the reset from buffer?

8

Are the fields you are displaying on screen either in the browse or in the hot fields list? If they are not explicitly in the VIEW used by the browse then they won’t be returned in a SQL environment.