SQLite2: filters

uncategorized
1

I had one of my browses limited by a year spin box.

In the filter for the browse I had: year(fil:dt) = loc:year

According to the documentation I think this should work: Year is a function listed in the “Expresssion Translator” section. However, it yields an empty list.

After trial and error, the only thing I found that works is
FIL:dt >= ‘yearstart’ and fil:dt <= ‘yearend’

So far as I can tell, anything wrapped in SQL() does not work (like dt between ‘yearstart’ and ‘yearend’). I couldn’t tell from the documentation whether or not SQL() was supposed to work. The log file unfortunately (I’m using version 1.10) gives no help when it doesn’t like a filter: you just get a line like:
[SQLite2][F][2096][051062S][JonWaterhouse][9292][2][10/14/25][12:26:47][][.\email_tracking.sqlite][holidays][66][CLAR]SETFILEERROR :1 [p040]SQLite Error.[holidays]

Given the lack of info in the logging file, I hoped that maybe PROP:WHERE would tell me what was trying to be sent to the server, and placed it on all the browse reset methods, after the parent call, but it was always blank, both for filters that worked and that didn’t work.

Not a showstopper by any means, but it would be nice to know what is going on.

2

I’ll be at my desk tomorrow Jon, so i can give you more specific information then. It sounds to me though that the local variable is either not beinv bound correctly, or something else along thouse lines.

Inspect prop:sql (eg s = customers{prop:sql}) to see the whole Sql statement. Then again i would expect LoggingOn(customers) to return a lot of info as well.

Please feel free to post the whole block of code surrounding your original attempt. (Including the call to BIND etc.)

3

I changed the message in the resets to PROP:SQL, and they all show “PRAGMA foreign_keys=ON;”

The relevant Clarion lines from the WindowManager. Init:

  SELF.Errors &= GlobalErrors                              ! Set this windows ErrorManager to the global ErrorManager
  BIND('loc:year',loc:year)                                ! Added by: BrowseBox(ABC)
  SELF.AddItem(Toolbar)
  CLEAR(GlobalRequest)                                     ! Clear GlobalRequest after storing locally
  CLEAR(GlobalResponse)
  IF SELF.Request = SelectRecord
     SELF.AddItem(?Close,RequestCancelled)                 ! Add the close control to the window manger
  ELSE
     SELF.AddItem(?Close,RequestCompleted)                 ! Add the close control to the window manger
  END
  loggingon(holidays,'c:\temp\ptss.log')
  Relate:holidays.Open()                                   ! File holidays used by this procedure, so make sure it's RelationManager is open
  SELF.FilesOpened = True
  BRW1.Init(?Browse:1,Queue:Browse:1.ViewPosition,BRW1::View:Browse,Queue:Browse:1,Relate:holidays,SELF) ! Initialize the browse manager
  SELF.Open(QuickWindow)                                   ! Open window
  Do DefineListboxStyle
  BRW1.Q &= Queue:Browse:1
  BRW1.RetainRow = 0
  BRW1.AddSortOrder(,hol:pk_holiday)                       ! Add the sort order for hol:pk_holiday for sort order 1
  BRW1.AddLocator(BRW1::Sort0:Locator)                     ! Browse has a locator for sort order 1
  BRW1::Sort0:Locator.Init(,hol:dt,1,BRW1)                 ! Initialize the browse locator using  using key: hol:pk_holiday , hol:dt
  BRW1.SetFilter('(year(hol:dt) = loc:year)')              ! Apply filter expression to browse
  BRW1.AddResetField(LOC:year)                             ! Apply the reset field
  BRW1.AddField(hol:dt,BRW1.Q.hol:dt)                      ! Field hol:dt is a hot field or requires assignment from browse

LOC:Year was bound by the template because I had it set as a reset field. I put a couple of messages in the driver code, just to see if things were being called as I would expect them to be (in order), specifically:
at the top of DriverBaseClass.TranslateFunction
and in the ‘year’ branch
at the top of DriverBaseClass.TranslateYear,
at the top of DriverFileSQLite2Class._TranslateDatePartSQL
and in the
ElsIf self.fields.type = ClaDate or self.fields.SqlTimeStamp branch of that method

None of those messages appeared.

The trace looks like:

[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][0][SQL ]1 rows, 6 columns in result set
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][OPTN]/pragma_foreign_keys =[ON]
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][SQL ]PRAGMA foreign_keys=ON;
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][SQL ]0 rows, 0 columns in result set
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(29457 PROP:Memos,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][CLAR]SETNULLS(string)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(31989 PROP:Object,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(29453 PROP:Driver,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(29453 PROP:Driver,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:09][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(29184 PROP:SQL,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(31989 PROP:Object,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(31836 PROP:Column,1)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(31836 PROP:Column,1)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]NAME()
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(31989 PROP:Object,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]GETPROPERTY(31989 PROP:Object,0)
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]SETERROR :90 File System Error
[SQLite2][F][3820][051062S][JonWaterhouse][12640][2][10/14/25][16:51:11][][.\email_tracking.sqlite][holidays][66][CLAR]SETFILEERROR :1 [p040]SQLite Error.[holidays]

:

4

can you post your declarion of the Holidays FILe please?

5

thanks for this report Jon. It’s exactly the kind of report I’m hoping to get. I can duplicate the effect, and I understand why it’s happening. Once I’ve determined the best way to sort it out, I’ll let you know.

Fundamentally it’s caused by a collection of things together. The root is a combination of this being a VIEW and a date-related function (Year, Month, Day etc). The local variable is not a problem.

6

Clarion declaration

holidays        FILE,DRIVER('SQLite2'),OWNER(GLO:database_file),NAME('holidays'),PRE(hol)
pk_holiday        KEY(hol:dt),PRIMARY,NOCASE
record            RECORD
dt                  DATE
                  END
                END

SQLite declaration

CREATE TABLE holidays
(dt date);
7

Fixed in build 1.12 (now up). Thanks Jon.