SQL in searching

[jaybird23238]

When doing a search, there is a tab and an input box for SQL Statements.  At one time, I had a list of a number of squ statements that could be used,  These statements worked in earlier versions (I think it changed in v9)

Apparently either field names, or the actual SQL interpreted has changed and many of them no longer work.

Is there an updated file with this information?  Or is it actually a bug waiting to be fixed?

[Daan van Rooijen]

You must be referring to Edit | Find | tab:Advanced.

In this Find by Query dialog, the 'Field' pulldown box shows a long list of database fields that can be used in SQL searches.

I've once made an overview of ThumbsPlus' database structure, including all field names (if interested, you can download it [link=https://www.dropbox.com/s/hvckzxujqztu1m6/ThumbsPlus-9-database-structure.pdf?dl=0]here[/link]), and the fieldnames in the list seem to correspond just fine to those in the Thumbnail table in the database. I also ran a simple query ('tn.[filelength] > 1000000000' to find large files) which worked fine.

Are you sure that you've entered a valid query and that its scope (eg. 'Entire database') was correct?

[jaybird23238]

I gave up trying anything with dates some time ago because this no longer works.

In the file I have (don't know if you are the creator of this) I have the following example:



tn.file_time < ^udate(2000/06/05) and tn.file_time > ^udate(1999/01/02)

OR

tn.file_time >= ^udate(2001-03-01) and tn.file_time <= ^udate(2002-04-15)

The SQL "between" operator also works, so:

tn.file_time between ^udate(2001-07-01) and ^udate(2001-07-31)

The field name "tn.file_time" does not appear in the drop down list.  Trying the example with the field name "tn.file_time_iso gives me an error "no such function, udate"

This document is on the Cerious web site.  Is there an updated version?

Thanks in advance for any help you may provide.

[Daan van Rooijen]

All I know is that ^Udate is no longer needed and that queries like these should work fine (note the mandatory apostrophes):

tn.[file_time_iso] >= '2016-07-05'
tn.[file_time_iso] between '2016-01-01 00:00:00' And '2016-01-07 23:59:59'

In an earlier topic, user Indukult added:

>tn.[file_time_iso] is a text field in the standard iso format '[yyyy-mm-tt]' & 'T' & '[hh-mm-ss]'.
>A search compares character for character from left to right. So a search like between '2*' and '2000-01-13*' will find the files in the first 2 weeks of 2000.