3

Now it generates inserts like

INSERT [Bla] ([id], [description], [name], [version])
VALUES (CAST(1 AS Numeric(19, 0)), convert(t...

It's very SQL Server specific. I would like to create a script that everybody can use, database agnostic. I have very simple data types - varchars, numbers, dates, bits(boolean).

I think

insert into bla values (1, 'die', '2001-01-01 11:11:11')

should work in all DBMSs, right?

bluish
  • 26,356
  • 27
  • 122
  • 180
IAdapter
  • 62,595
  • 73
  • 179
  • 242

3 Answers3

3

Some basic rules:

Get rid of the square brackets. In your case they are not needed - not even in SQL Server. (At the same time make sure you never use reserved words or special characters in column or table names).

If you do need to use special characters or reserved words (which is not something I would recommend), then use the standard double quotes (e.g. "GROUP").

But remember that names are case sensitive then: my_table is the same as MY_TABLE but "my_table" is different to "MY_TABLE" according to the standard. Again this might vary between DBMS and their configuration.

The CAST operator is standard and works on most DBMS (although not all support casting in all possible combinations).

convert() is SQL Server specific and should be replaced with an approriate CAST expression.

Try to specify values in the correct data type, never rely on implicit data conversion (so do not use '1' for a number). Although I don't think casting a 1 to a numeric() should be needed.

Usually I also recommend to use ANSI literals (e.g. DATE '2011-03-14') for DATE/TIMESTAMP literals, but SQL Server does not support that. So it won't help you very much.

  • What about `BIT`? I haven't heard about its support by any product other than MS's and would very much like to learn about any such one. (I mean MS's `BIT`, not the ANSI `BIT`.) – Andriy M Mar 14 '11 at 10:12
  • @Andriy M: BIT is probably better mapped to boolean (although e.g. Oracle does not support this) –  Mar 14 '11 at 10:16
  • Yes, but the point is, if the other DBMS does not support `bit` directly, you'll have to maintain specific versions for both DBMSs. Which is a shame, because without `bit` your set of basic rules seems to allow for the possibility to keep single versions of scripts for several different DBMSs in some cases. I mean, `bit` is such a simple data type, and yet... – Andriy M Mar 14 '11 at 10:58
  • 1
    @Andriy M: Due to the fact that no DBMS completely implements the ANSI standard (and in a consistent manner) you will never be able to have a single SQL script that runs on all DBMS. If you want to have such a thing you might want to consider using something like Liquibase that knows all the differences. For you it's still a single "script" and Liquibase does the translation for you. But the syntax differences are usually the smallest problems... –  Mar 14 '11 at 11:17
2

A quick glance at the Wikipedia article on SQL, will tell you a bit about standardisation of SQL across different implementations, such as MS SQL, PostgreSQL, Oracle etc.

In short, there is a number of ANSI standards but there is varying support for it throught each product.

The general way to support multiple database servers from your software product is to accept there are differences, code for them at the database level, and make your application able to call the same database access code irrespective of database server.

Jamiec
  • 133,658
  • 13
  • 134
  • 193
  • but insert into bla values (1, 'die', '2001-01-01 11:11:11') should work in all DBs, right? – IAdapter Mar 14 '11 at 11:27
  • Yes, that one will. But there is plenty of stuff you could do which wouldn't be portable. As I mentioned in my answer, it is my opinion that having completely database agnostic SQL scripts is a pipe dream. – Jamiec Mar 14 '11 at 11:38
0

There are a number of problems with number formats which will not port between dbmses however this pales when you look at the problems with dates and date formats. For instance the default DATE format used in an ORACLE DB depends on the whims of whoever installed the software, you can use date conversion functions to get ORACLE to accept the common date formats - but these functions are ORACLE specific.

Besides how do you know the table and column names will be the same on the target DB?

If you are serious about this, really need to port data between hydrogenous DBMSes, and know a bit of perl thn try using SqlFairy which is available from CPAN. The sheer size of this download should be enough to convince you how complex this problem can be.

James Anderson
  • 27,109
  • 7
  • 50
  • 78
  • the tables are created by JPA, so they should be the same everywhere. too bad that JPA cant handle the default data. I want the user of my app to choose what DB he wants to use, but I need the default data :( – IAdapter Mar 17 '11 at 11:27