coding for multiple databases
pete at lcsql.com
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 <http://www.lcsql.com>
On Sat, Mar 23, 2013 at 10:45 AM, Dr. Hawkins <dochawk at gmail.com> 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
> case mySql
> put "SELECT * FROM mytable WHERE icare=1" into dcmd
> case SQLite
> put "SELECT * FROM mytable WHERE icare='TRUE'" into dcmd
> 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 lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
More information about the Use-livecode