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

From Linuxnetworks
Jump to: navigation, search
(→‎Identifiers)
(removed backlink)
 
(11 intermediate revisions by the same user not shown)
Line 1: Line 1:
 
__TOC__
 
__TOC__
  
 +
== Identifiers ==
  
−
= Differences between implementations =
+
Parts of this table are originally from "Building Truly Portable Database Applications in PHP": [http://www.analysisandsolutions.com/presentations/portability/slides/case.htm Column Name Case]
−
 
+
−
== NULL in column definition ==
+
−
 
+
−
This table is originally from "Building Truly Portable Database Applications in PHP": [http://www.analysisandsolutions.com/presentations/portability/slides/null.htm NULL]
+
  
 
{| class="wikitable" style="text-align:center"
 
{| class="wikitable" style="text-align:center"
−
! Database !! Default value !! Definition allowed !! Notes
+
! Database !! Length !! Allowed !! Case !! Notes
 
|-
 
|-
 
! Firebird
 
! Firebird
−
| NULL || no ||
+
| 31 || L,D (?) || upper || case preserved with double quotes (")
 
|-
 
|-
 
! MySQL
 
! MySQL
−
| NULL || yes ||
+
| 64 || L,NL,D,_,$ || preserved ||
 
|-
 
|-
 
! Oracle
 
! Oracle
−
| NULL || yes || Empty strings considered NULL
+
| 30 || L,NL,D,_,$,# || upper ||[http://www.dbazine.com/db2/db2-disarticles/gulutzan5 Info], max. DB name length is 8
 
|-
 
|-
 
! PostgreSQL
 
! PostgreSQL
−
| NULL || yes ||
+
| 63 || L,NL,D,_,$ || lower || case preserved with double quotes (")
 
|-
 
|-
 
! SQL Server
 
! SQL Server
−
| NOT NULL || yes ||
+
| 127 || L,NL,D,_,$,@,# || preserved || [http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188931,00.html Info]
 
|-
 
|-
 
! SQLite
 
! SQLite
−
| NULL || yes ||
+
| 255 || L,NL,D,_ || preserved ||
 
|-
 
|-
 
! Sybase ASE
 
! Sybase ASE
−
| NOT NULL || yes ||
+
| 131 || L,NL,D,_,$,@,# || preserved || max. DB and Cursor name length is 28
 
|}
 
|}
  
−
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:
+
Explanation of allowed symbols:
 +
* L = Letters
 +
* NL = Non-latin letters
 +
* D = Digits
  
−
CREATE TABLE mytable ( id INTEGER NULL )
+
In general, all identifiers must start with a letter or non-latin letter; digits and other symbols as first characters are often not allowed or change their meaning.
  
−
== Identifiers ==
+
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.
  
−
Parts of this table are originally from "Building Truly Portable Database Applications in PHP": [http://www.analysisandsolutions.com/presentations/portability/slides/case.htm Column Name Case]
+
== NULL in column definition ==
 +
 
 +
This table is originally from "Building Truly Portable Database Applications in PHP": [http://www.analysisandsolutions.com/presentations/portability/slides/null.htm NULL]
  
 
{| class="wikitable" style="text-align:center"
 
{| class="wikitable" style="text-align:center"
−
! Database !! Length !! Allowed !! Case !! Notes
+
! Database !! Default !! Definition allowed !! Notes
 
|-
 
|-
 
! Firebird
 
! Firebird
−
| 31 || L,D (?) || upper || case preserved with double quotes (")
+
| NULL || no ||
 
|-
 
|-
 
! MySQL
 
! MySQL
−
| 63 || L,NL,D,_,$ || preserved ||
+
| NULL || yes ||
 
|-
 
|-
 
! Oracle
 
! Oracle
−
| 30 || L,NL,D,_,$,# || upper ||[http://www.dbazine.com/db2/db2-disarticles/gulutzan5 Info], max. DB name length is 8
+
| NULL || yes || Empty strings considered NULL
 
|-
 
|-
 
! PostgreSQL
 
! PostgreSQL
−
| 63 || L,NL,D,_,$ || lower || case preserved with double quotes (")
+
| NULL || yes ||
 
|-
 
|-
 
! SQL Server
 
! SQL Server
−
| 127 || L,NL,D,_,$,@,# || preserved || [http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188931,00.html Info]
+
| NOT NULL || yes ||
 
|-
 
|-
 
! SQLite
 
! SQLite
−
| 255 || L,NL,D,_ || preserved ||
+
| NULL || yes ||
 
|-
 
|-
 
! Sybase ASE
 
! Sybase ASE
−
| 131 || L,NL,D,_,$,@,# || preserved || max. DB and Cursor name length is 28
+
| NOT NULL || yes ||
 
|}
 
|}
  
−
Explanation of allowed symbols:
+
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:
−
* L = Letters
+
−
* NL = Non-latin letters
+
−
* D = Digits
+
  
−
In general, all identifiers must start with a letter or non-latin letter; digits and other symbols as first characters are often not allowed or change their meaning.
+
CREATE TABLE mytable ( id INTEGER NULL )
  
−
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.
+
== Quotes and numeric values ==
 +
 
 +
MS SQL Server and Sybase ASE doesn't allow single quotes (') around numeric values (integer, decimal and floating point values), for example
 +
 
 +
INSERT INTO table (floatval,string) VALUES ('-3.14','some text')
 +
 
 +
generates an error when using these servers. They only accept
 +
 
 +
INSERT INTO table (floatval,string) VALUES (-3.14,'some text')
 +
 
 +
Special care must be taken in this case to avoid SQL injection attacks. Please read the section about [[Portable_SQL - Quoting#Quoting_values|quoting values]] carefully!

Latest revision as of 15:05, 22 June 2008

Identifiers

Parts of this table are originally from "Building Truly Portable Database Applications in PHP": Column Name Case

Database Length Allowed Case Notes
Firebird 31 L,D (?) upper case preserved with double quotes (")
MySQL 64 L,NL,D,_,$ preserved
Oracle 30 L,NL,D,_,$,# upper Info, max. DB name length is 8
PostgreSQL 63 L,NL,D,_,$ lower case preserved with double quotes (")
SQL Server 127 L,NL,D,_,$,@,# preserved Info
SQLite 255 L,NL,D,_ preserved
Sybase ASE 131 L,NL,D,_,$,@,# preserved max. DB and Cursor name length is 28

Explanation of allowed symbols:

  • L = Letters
  • NL = Non-latin letters
  • D = Digits

In general, all identifiers must start with a letter or non-latin letter; digits and other symbols as first characters are often not allowed or change their meaning.

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.

NULL in column definition

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

Database Default 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 )

Quotes and numeric values

MS SQL Server and Sybase ASE doesn't allow single quotes (') around numeric values (integer, decimal and floating point values), for example 

INSERT INTO table (floatval,string) VALUES ('-3.14','some text')

generates an error when using these servers. They only accept 

INSERT INTO table (floatval,string) VALUES (-3.14,'some text')

Special care must be taken in this case to avoid SQL injection attacks. Please read the section about quoting values carefully!

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