ThumbsPlus

All forums => ThumbsPlus v10 Questions => Topic started by: jaybird23238 on 2016-07-01 08:54:20

Title: SQL in searching
Post by: jaybird23238 on 2016-07-01 08:54:20

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?

Title: SQL in searching
Post by: Daan van Rooijen on 2016-07-01 12:13:22

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?

Title: SQL in searching
Post by: jaybird23238 on 2016-07-05 20:24:02

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.

Title: SQL in searching
Post by: Daan van Rooijen on 2016-07-05 20:36:50

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.