Ignore case in SQLite database search

Scripting Forums AppleScript | Mac OS X
#1

Hi,

I have been following this article (http://macscripter.net/viewtopic.php?id=24744) about setting up a SQLite database.
I have managed to set one up, query it, add, delete etc.

However, it seems to treat queries as case explicit, so in the following example from the unScripted article the result is {}

set head to "sqlite3 -column ~/desktop/TestDB2.db " & quote
set tLast to "select lastname from mods where firstname = 'bruce'; " -->> Should be 'Bruce'
set surname to do shell script head & tLast & quote

In my case, I am getting information regarding Colours from another database and using this SQLite db to lookup the colours and their cmyk values. I won’t have problems with the majority, as they are Pantone numbers (100, 101 etc), but if the original string is entered as eg. ‘white’, or ‘cyan’, then I am not going to get a result.

Is there a way like ignoring case / end ignoring?

Thanks

Ian

Browser: Safari 525.13
Operating System: Mac OS X (10.4)

#2

The simplest solution is to use like and not the equals sign (=), in this manner:

set tLast to "select lastname from mods where firstname like 'bruce'; "

This will return anything with Bruce, brucE, or any other variation.

Take a look at the SQLite documentation for more details, especially the section on expressions.

Good luck,

#3

Craig,

Thanks for your reply. That has set me on the right track.

On a side note, if multiple users need access to the database (they will only be reading, not writing to it), is it OK to read the db over the network, or would it be better to copy it locally and delete when finished?

Thanks

Ian

#4

Ian:

From what I understand, SQLite is able to take care of itself under those kind of circumstances. I have not ever been involved with network stuff, so I may not be the right person to answer this. Have you considered joining theSQLite Users mailing list? Those people are very, very helpful.