Hello,
Am wondering if there are guidelines somewhere to write SQL code which is compatible in SQL Server, mySQL as well as postgreSQL. I am writing a program and the user has a choice of backend database. It will be really difficult for me to create separate queries for each and every db. I do not want to use an ORM or anything like that.
Thank you for your time.
Well, the best path you can choose consist in adopting the SQL ISO Standard.
Postgres and MySQL are heavy supporters, but I don't know if you could say the same about SQL Server.
Anyway, i think that an ORM solution could be the best for you, because usually they abstract these differences very well.
But if you don't wanna use it you can always store your queries in different repositories, each one for a specific database and load them at runtime.
Follow SQL:2003 standards.
Do unit tests when porting code from one system to another.
Don't use functions without looking how they are implemented in the dbms and what they are accepting as parameters.
Stuff to watch out for: Some SUBSTRING functions are 0-based in one system and 1-based in another. DATEDIFF works differently too. Some dbms have microseconds for datetime data type some don't.
With that being said, writing truly portable SQL is a dream... but does it really matter to be truly portable? A skilled DBA should know the little differences and port SQL codes quickly.