Support for multiple SQL implementations

so if you're writing a piece of software which you want to be able to handle communication with databases on multiple sql implementations, what method would you use?

i've been thinking about two so far and not sure which one to pick, or if there are more, basically to either write ansi sql code or whatever, not use any implementation specific special features so my sql queries work with a sql function wrapper and always execute properly

the method i'm leaning more towards right now is to keep ALL sql queries stored in a file, configuration, and have different queries and database schemes for the implementations i'd like to support

i like the second method even though it's more work, because i only want support for the biggest ones, like mysql, postgres and maybe mssql, and i don't even know that much about mssql so i could prolly reuse some of the mysql queries by setting it as a default, or postgres, so if a query doesn't exist in the mssql set, it uses the corresponding mysql query

The easy way would be to use a wrapper library (e.g. SQLAlchemy or something) that handles that sort of thing for you.

Unless there are serious optimization requirements, write the structure for each DBMS in different files, and use standard, basic SQL for queries, because you are not going to alter the database structure within your app.

If you don't like that, do >>2

If you can't use that, do what you said.

Why are we being so helpful?
Anyway, you need to localize the SQL queries, right? And what do we use for localizating language strings? That's right, gettext.

>>4
Not really the same thing. Human languages cannot be as easily translated to each other as computer ones.

I've never used it, but I've always heard good things about SQLAlchemy. Alternatively, there are plenty of other frameworks for providing generic standardized SQL functionality. For example, both Python and Perl have a fairly standardized way of accessing a database (cursors and the DBI, respectively).

I think writing your own is excessive and silly, unless either the purpose of your task is explicitly to write a wrapper, or if the solutions already available are unable to fulfill your needs.

It's called the provider pattern.

>>4
gettext
I lol'd

Why are we being so helpful?
I don't know, maybe because it's not an average homework question? Some of us believe it's not about trolling everyone, but trolling only people that deserve it. And from a more realistic point of view, it's just asking yourself "Do I want to help this guy?". You can feel free to reply "no" and troll, of course. For me, the question seems both simple and interesting.

SQL is a bunch of confusing bullshit that nobody needs.

Do you really need support for multiple SQL implementations? If not, why bother?

MySQL is dying (Netcraft confirms it).

So just implement a good PostgreSQL support, and you will cover pretty much everything except massive things that really need Oracle.

>>11
back to /., please

>>12 On /. it's an old meme.

What sort of advanced SQL are you using where you need to write non-standard queries?

well the trade off in these situations is obvious, you either let the sql server do the work for you, as we were all taught in high school, or you do the sorting and work with your code

i personally prefer to let the sql server do as much as possible, this is why i'm leaning towards having preset sql queries for all implementations

this is also very dynamic if a client wants to change anything, i noticed this in cyrus imapd where you can write the sql query used to fetch user info

as long as the info returned is in the same structure, you can use AS aliases for this, you can have the sql query do whatever you want

>>3
i don't want to use a prebuilt wrapper because i'm getting paid a lot of money for this and i plan to resell it, would be bad if at some point a client says "hey but this is code you just took from this other project"

also i don't see a problem in writing this myself because it's just php so it's a breeze

so basically the best suggestion i get here is to use standard sql, which goes against me first choice so now i have some serious pondering to do, thanks for the help jerks

php
Now you have two problems.

>>16
Just copy it and strip out the comments then.

>>16
Unless you write your own language and compiler, you'll be using code from other projects. Much of the point of free software is that you don't have to reinvent the wheel every time you try something new.

ORDER BY is standard enough to be used with all DBMS.

>>20
What about getting the last auto-generated value of a primary key? SELECT MAX(id) FROM table is somewhat less efficient than SELECT LAST_INSERT_ID FROM table or SELECT @@IDENTITY FROM table, not to mention the table creation syntax (especially key creation), structure queries and metadata access are radically different between databases.

>>21
listen to this man, he's not a fgt

>>21
That's why you keep your auto-generating code outside of the SQL server, just as Codd and Date would have intended.

Don't forget Sqlite! I really like that library. Great for small apps and computers where memory is limited. mysqld is currently using 17MB on my computer. I haven't checked how much loading the Sqlite library is, but I'd wage it's much less. On the other hand, MySQL only requires one process for any number of applications, and MySQL, of course, is superior in terms of features than Sqlite. Still, the idea of needing a 17MB process running in the background on my desktop computer bothers me.

>>24
SQLite is sorely limited, I wouldn't use it for large datasets or complicated queries.

>>25
Or anything which could possibly generate more than one concurrent connection to the database. SQLite is a perfect backend for a lightweight application, but for anything of any scale you really need to use a more scalable database package.

Good for my kopipe collection, not for my porn collection.

>>26
Oh yeah, if you try to query and modify a table at the same time, one of them will usually fail. Rubbish.

All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy
 All work and no play makes Jack a dull boy

SQL
Back to your enterprise grade cubicle farm, ``please"!

>>29
It takes a special kind of person to reply to a post from 2008 with an utter shitpost like that.

Go back to /g/.

It's called ODBC, noob.

bampu pantsu