Difference between revisions of "Portable SQL/Common/DBMS Differences"

From Linuxnetworks
Jump to: navigation, search
(→‎Differences between database implementations)
(Identifier case)
Line 36: Line 36:
  
 
  CREATE TABLE mytable ( id INTEGER NULL )
 
  CREATE TABLE mytable ( id INTEGER NULL )
 +
 +
== Identifier case ==
 +
 +
This table is originally from "Building Truly Portable Database Applications in PHP": [http://www.analysisandsolutions.com/presentations/portability/slides/case.htm Column Name Case]
 +
 +
{| class="wikitable" style="text-align:center"
 +
! Database !! Case !! Notes
 +
|-
 +
! Firebird
 +
| upper || preserved when using double quotes (")
 +
|-
 +
! MySQL
 +
| preserved ||
 +
|-
 +
! Oracle
 +
| upper ||
 +
|-
 +
! PostgreSQL
 +
| lower ||
 +
|-
 +
! SQL Server
 +
| preserved ||
 +
|-
 +
! SQLite
 +
| preserved ||
 +
|-
 +
! Sybase ASE
 +
| preserved ||
 +
|}
 +
 +
Since OpenDBX 1.1.4 the use of double quotes (") for identifiers (table and column names) is suggested. This complies to ANSI standards and is the only way to use reserved words and prevent clashes in the future if language constructs are extended by the database vendors.

Revision as of 14:18, 3 September 2006


Differences between implementations

NULL in column definition

This table is originally from "Building Truly Portable Database Applications in PHP": NULL

Database Default value Definition allowed Notes
Firebird NULL no
MySQL NULL yes
Oracle NULL yes Empty strings considered NULL
PostgreSQL NULL yes
SQL Server NOT NULL yes
SQLite NULL yes
Sybase ASE NOT NULL yes

The "Definition allowed" column indicates if specifying NULL in table column definitions is allowed for CREATE TABLE statements - NOT NULL is always allowed. If "yes", the following statement doesn't generate an error: 

CREATE TABLE mytable ( id INTEGER NULL )

Identifier case

This table is originally from "Building Truly Portable Database Applications in PHP": Column Name Case

Database Case Notes
Firebird upper preserved when using double quotes (")
MySQL preserved
Oracle upper
PostgreSQL lower
SQL Server preserved
SQLite preserved
Sybase ASE preserved

Since OpenDBX 1.1.4 the use of double quotes (") for identifiers (table and column names) is suggested. This complies to ANSI standards and is the only way to use reserved words and prevent clashes in the future if language constructs are extended by the database vendors.

Retrieved from "https://www.linuxnetworks.de/doc/index.php?title=Portable_SQL/Common/DBMS_Differences&oldid=2056"