Data Description and Creation
Now that we know which databases and tables we are using, we can look at differences in how we specify various features during the creation process.
Creating Databases to Work with UTF-8
To create databases that understand UTF-8 input and store string values as Unicode text in tables, we use the following queries. We also use these to specify the default sorting order (collation) for these new databases:
Server | Syntax |
---|
MySQL |
CREATE DATABASE DBName
DEFAULT CHARACTER SET utf8
DEFAULT COLLATE utf8_general_ci;
| PostgreSQL |
CREATE DATABASE MooCow WITH ENCODING 'UNICODE'
or initdb -E UNICODE from command line. | Oracle | When Creating Database, specify UTF8 or AL32UTF8 as CHARACTER SET | Microsoft SQL Server | Done at installation time. |
Setting the Connection to UTF-8
Even if the database server is set up to use UTF-8 correctly, we need to ensure that the connection is set up correctly:
Server | Syntax |
---|
MySQL | SET NAMES 'utf8' | PostgreSQL | SET NAMES 'utf8' or SET CLIENT_ENCODING TO 'utf8' | Oracle | Set the NLS_LANG environment variable to AL32UTF8 or simply UTF8. Do this before starting Apache or IIS. | Microsoft SQL Server | Make sure freeTDS is configured properly. |
Auto-Incrementing Fields
Fields that have an auto-incrementing index after every row insertion are quite different from server to server:
Server | Syntax |
---|
MySQL | AUTO_INCREMENT | PostgreSQL | SERIAL, BIGSERIAL datatypes | Oracle |
CREATE SEQUENCE supplier_seq
MINVALUE 1
MAXVALUE 999999999999999999999999999
START WITH 1
INCREMENT BY 1
CACHE 20;
sequence.nextval is critical use of feature.
INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(supplier_seq.nextval, 'Some Company');
| Microsoft SQL Server | IDENTITY |
Date/Time Column Types
To create a column of a date or time type, you must change your SQL from server to server:
Server | Syntax |
---|
MySQL | DATE, TIME, DATETIME | PostgreSQL | DATE, TIME, TIMESTAMP | Oracle | DATE (does DATE and times), TIMESTAMP (date with more precision) and INTERVAL HOURS TO SECONDS (HH:MM:SS), INTERVAL YEAR TO DAY (YYYY-MM-DD), TIME datatype exists too. | Microsoft SQL Server | DATETIME, SMALLDATETIME only |
Binary Column Types
To store binary data in your database, you need to use a special data type:
Server | Syntax |
---|
MySQL | BLOB | PostgreSQL | bytea | Oracle | BLOB | Microsoft SQL Server | image |
Large Text Column Types
Because most CHAR and VARCHAR columns are limited to 255 characters, you sometimes need larger text column types. These are as follows:
Server | Syntax |
---|
MySQL | TEXT | PostgreSQL | TEXT | Oracle | CLOB | Microsoft SQL Server | TEXT |
Bulk Data Insertion
Inserting data into your database outside of PHP code can sometimes be done by using what is known as bulk data insertion. It is very specific to servers:
Server | Syntax |
---|
MySQL | LOAD DATA and LOAD DATA LOCAL | PostgreSQL | COPY TableName (col1,...,coln) FROM FILENAME WITH ... | Oracle | Control File: LOAD DATA
INFILE <dataFile>
APPEND INTO TABLE <tableName>
FIELDS TERMINATED BY '<separator>'
(<list of all attribute names to load>) and then data file | Microsoft SQL Server | You will have to do this by hand or in code. |
|