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.





Core Web Application Development With PHP And MYSQL
Core Web Application Development with PHP and MySQL
ISBN: 0131867164
EAN: 2147483647
Year: 2005
Pages: 255

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net