Running the same SQL script on different DBs

At times, I find myself in need to run the same SQL script in different database products. Because these databases have dfferent SQL dialects, there needs to be some kind of if db = mysql then … else … end logic in the scripts.

For example, MySQL starts a transaction with begin work while SQLite starts it with begin transaction.

Solving such a requirement is easily possible if there is a preprocessor and the scripts can be run from the command line.

This is demonstrated with the following relevant portion of the SQL script:

…
#ifdef MYSQL
  begin work;
#elif defined SQLITE
  begin transaction;
#endif
…

Now, to execute the script in MySQL, the preprocessor needs to be started with the -E flag (so that neither the compiler nor the linker will be invoked) and the -P flag (so that the command line markers are excluded from the output).

Additionally, the -D flag is used to specify the database environment.

With all that said, the script is executed for MySQL like so

$ gcc -E -P -DMYSQL  | mysql -u rene

And similarly for SQLite with

$ gcc -E -P -DSQLITE | sqlite db