Difference between revisions of "Portable SQL/Common/DBMS Differences"
(→Differences between database implementations) |
(removed backlink) |
||
| (14 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
__TOC__ | __TOC__ | ||
| + | == Identifiers == | ||
| − | = | + | 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] |
| + | |||
| + | {| class="wikitable" style="text-align:center" | ||
| + | ! 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 ||[http://www.dbazine.com/db2/db2-disarticles/gulutzan5 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 || [http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1188931,00.html 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 == | == NULL in column definition == | ||
| Line 9: | Line 44: | ||
{| class="wikitable" style="text-align:center" | {| class="wikitable" style="text-align:center" | ||
| − | ! Database !! Default | + | ! Database !! Default !! Definition allowed !! Notes |
|- | |- | ||
! Firebird | ! Firebird | ||
| Line 36: | Line 71: | ||
CREATE TABLE mytable ( id INTEGER NULL ) | 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 [[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!
