Thursday, January 12, 2017

Three SQLite Gotchas

If you are using SQLite coming from say a Microsoft SQL Server background, here are a few differences that might throw you.
  1. There is no TOP as in "select top 50 from....".  
    Instead you use LIMIT, and you put it at the end of your query like "Select blah from blah order by blah limit 50".
  2. Paging because of the above is actually made easy.  You can use OFFSET with LIMIT for easy paging like so. "Select blah from blah order by and wheres blah limit 20 offset 50..." if you pass in a paging variable like say @page you can times your offset to get you what page you want, like "offset * @page".   There is even a shorthand for it, but with a gotcha...the offset and limit are swapped.  Like "select .... order by blah limit 40,10" actually means offset 40 limit 10, which is counter intuitive but there for backwards compatibility.
  3. CASE.  Case is kind of a bummer for comparisons in SQLite.  you can set in on your indexes or on your connection strings to ignore case on your where @mystring = 'blah' type queries like so

    - programa collate nocase in your connections string
    - collate nocase on the end of your queries
    - or add collate nocase on your index when creating a column

    But alas, not that simple.  this only works for UTF-8, if you are using UTF-16 you have to use more exotic solutions with collate binary (I'll leave a link).

    Out of the box you can fake insensitivity using the like operator,  select blah where x like @xxx +'%'... though note here you are better off adding that % operator on your sql param then having it in your query string, for some reason the later doesn't work for me but maybe I'm missing something.

    Also instr, the equivalent to charindex in SQL, insn't case insensitive.  Bummer.

Some useful links-

No comments: