coding for multiple databases

[Dr. Hawkins]

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