coding for multiple databases

Peter Haworth pete at
Sat Mar 23 15:30:59 EDT 2013

SQLite lets you define whatever data types you want.  Nothing to stop you
defining a column with BOOLEAN data type, internally it will be stored as
NUMERIC.  After that, you can use:

SELECT * FROM mytable WHERE icare=1

...for all your databases

lcSQL Software <>

On Sat, Mar 23, 2013 at 10:45 AM, Dr. Hawkins <dochawk at> wrote:

> As I try to keep this thing coded to support  postgres, SQLite, and mySQL,
> I'm getting increasingly frustrated by the "oh, we don't support that; code
> it our way instead" attitude of SQLite and mySQL.
> Unless my calendar is off, it's 2013.  mySQL doesn't even have a boolean,
> and SQLite can't handle TRUE without quotes.
> I've selected postgres for the standard server version (no, I don't want to
> discuss and revisit this), but have planned on sqlite for stand-alone,
> non-networked copies.
> I'm starting to think it might be less work to bundle postgres . . . which
> is just plain insane!
> Anyway, to deal with queries where I have to use non-standard syntax, it
> seems that I can have switches:
> switch dbTyp
> case postgres
> put "SELECT * FROM mytable WHERE icare=True" into dcmd
> break
> case mySql
> put "SELECT * FROM mytable WHERE icare=1" into dcmd
> break
> case SQLite
> put "SELECT * FROM mytable WHERE icare='TRUE'" into dcmd
> break
> end switch
> Or, I could make a two-dimensional array
> put "SELECT * FROM mytable where icare=" & dbWords[dbTyp][TRUE] & quote
> into dcmd
> Which, of course, means maintaining the 2d array.
> How do others deal with this?
> The switch seems much faster, while the array would be easier to write.
> (Oh, and the reason for wanting mysql at all is it seems to be less
> expensive to find third party servers that speak it)
> --
> Dr. Richard E. Hawkins, Esq.
> (702) 508-8462
> _______________________________________________
> use-livecode mailing list
> use-livecode at
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:

More information about the Use-livecode mailing list