coding for multiple databases

[Peter Haworth]

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

Pete
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
> 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 lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>