SQL Data Definition Language (DDL)

< BACK  NEXT >
[oR]

The ADOXCE object model allows the objects in a database (such as tables and columns) to be added, modified, and deleted. You can also use SQL statements to do the same, which can often be more convenient and quicker. These SQL statements are called "Data Definition Language," or DDL, statements. SQL statements that manipulate data are called "Data Manipulate Language," or DML, statements. The following DDL statements are supported:

  • CREATE DATABASE

  • CREATE TABLE

  • CREATE INDEX

  • ALTER TABLE

  • DROP INDEX

  • DROP TABLE

  • DROP DATABASE

Using CREATE TABLE

The CREATE TABLE statement allows a table to be created by giving a name for the new table and the list of columns (fields) and their data types. The allowable data types for Microsoft SQL Server for Windows CE are shown in Table 16.6. For example, the following SQL statement creates a new table called Orders with four fields: CustNum (integer), OrderNum (integer), Description (Unicode string up to 100 characters long), and DateAdded (DateTime).

 CREATE TABLE Orders (CustNum INT, OrderNum INT,   Description NCHAR VARYING(100), DateAdded DATETIME); 

Table 16.6. Creates table data types
Data Type Name Description
NCHAR(size) National Character (Unicode) fixed-length character string. The size is the maximum number of characters that the field can store, up to 4,000 characters.
NCHAR VARYING(size) National Character (Unicode) variable-length character string. The size is the maximum number of characters that the field can store.
BIT Bit field occupying a single bit in a byte field.
BINARY(size) Fixed-length binary field with maximum "size" bytes up to 8,000 bytes.
VARBINARY(size) Variable-length binary field with maximum "size" bytes.
IMAGE Binary field storing up to 2,147,483,647 bytes.
DATETIME Date and time data from January1, 1753, through December31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
FLOAT Eight-byte floating-point value.
REAL Four-byte floating-point value.
INT Four-byte integer value.
SMALLINT Two-byte integer value.
TINYINT One-byte integer value.
BIGINT Eight-byte integer value.
NUMERIC(p,s) Fixed precision and scale numeric data able to store "p" decimal digits and "s" decimal digits to the right of the decimal point.
MONEY Money value with up to three decimal points for cents/pennies, and so on.

The code in Listing 16.8 creates the table called Orders. The function ExecuteSQL is passed a connection pointer and executes the SQL code.

Listing 16.8 The CREATE TABLE statement
 void ExecuteSQL(AdoNS::_ConnectionPtr& pConnection,           _bstr_t& bstrSQL) {   _variant_t varRowsAffected;   pConnection->Execute(bstrSQL,       &varRowsAffected,       AdoNS::adCmdText); } void Listing16_8() {   AdoNS::_ConnectionPtr pConnection;   if(!GetConnection(pConnection))     return;   _bstr_t bStrSQL(_T("CREATE TABLE Orders \            (CustNum INT, OrderNum INT, \            Description NCHAR VARYING(100), \            DateAdded DATETIME)"));   ExecuteSQL(pConnection, bStrSQL);   cout   _T("Table created")   endl;   pConnection->Close(); } 

Using DROP TABLE

Listing 16.4 showed how to drop a table using ADOXCE and the tables connection. An alternative method is to use the DROP TABLE statement, which is passed the name of the table to drop. Listing 16.9 shows code to drop the "Orders" table.

Listing 16.9 The DROP TABLE statement
 void Listing16_9() {   AdoNS::_ConnectionPtr pConnection;   if(!GetConnection(pConnection))       return;   _bstr_t bStrSQL(_T("DROP TABLE Orders "));   ExecuteSQL(pConnection, bStrSQL);   pConnection->Close(); } 

Using Identities and Primary Keys

Many tables require a unique integer identifier for each record in the database. Chapter 4 showed how to do this for property databases using the registry. In SQL Server for Windows CE you can use the IDENTITY key word when creating a table to create an auto-increment field. The PRIMARY KEY modifier can be used to specify that a field is the uniquely identifying field within the table. Identifying the primary key in a table is important, since applications inspecting the database design can use this to optimize data access. For example, to create a new table called "OrderDetails" with an auto-incrementing field called OrderDetailNum, you can execute the following DDL code:

 CREATE TABLE OrderDetails   (OrderDetailNum INT IDENTITY PRIMARY KEY,    OrderNum INT,    Product NCHAR VARYING(100),    Quantity INT) 

In almost all cases an IDENTITY field will also be the primary key. Listing 16.10 shows how this DDL code can be executed through ADOCE.

Listing 16.10 The CREATE TABLE with identity column
 void Listing16_10() {   AdoNS::_ConnectionPtr pConnection;   if(!GetConnection(pConnection))       return;   _bstr_t bStrSQL(_T("CREATE TABLE OrderDetails \       (OrderDetailNum INT IDENTITY PRIMARY KEY, \       OrderNum INT, \       Product NCHAR VARYING(100), \       Quantity INT)"));   ExecuteSQL(pConnection, bStrSQL);   cout   _T("Table created")   endl;   pConnection->Close(); } 

Indexes

Indexes are used to improve performance when accessing records in a table, when joining tables together (using primary and foreign keys), and for ensuring uniqueness. When the PRIMARY KEY modifier is used for a field, a unique index is used to ensure uniqueness. Other indexes should be added to the "Customers," "Orders," and "OrderDetails" tables to cater for the most frequent ways the data will be accessed. Figure 16.1 shows the relationships between these three tables, and this helps to identify where indexes should be placed.

Figure 16.1. Relationships between Customers, Orders, and OrderDetails
graphics/16fig01.gif

The following DDL code can be used to create a unique index on the "Orders" table for the CustNum field (a foreign key used in the relationship with the Customers database):

 CREATE UNIQUE INDEX OrdersInd1       ON Orders (CustNum) 

Listing 16.11 shows code to create a number of additional indexes on the three tables created in previous sections.

Listing 16.11 The CREATE INDEX statement
 void Listing16_11() {   AdoNS::_ConnectionPtr pConnection;   if(!GetConnection(pConnection))       return;   _bstr_t bStrSQL(_T("CREATE UNIQUE INDEX OrdersInd1 \            ON Orders (OrderNum)"));   ExecuteSQL(pConnection, bStrSQL);   bStrSQL = (_T("CREATE INDEX OrdersInd2 \           ON Orders (CustNum)"));   ExecuteSQL(pConnection, bStrSQL);   bStrSQL = (_T("CREATE UNIQUE INDEX Customers1 \         ON Customers (CustNum)"));   ExecuteSQL(pConnection, bStrSQL);   bStrSQL = (_T("CREATE UNIQUE INDEX Customers2 \         ON Customers (CustName)"));   ExecuteSQL(pConnection, bStrSQL);   cout   _T("Indexes created")   endl;   pConnection->Close(); } 

< BACK  NEXT >


Windows CE 3. 0 Application Programming
Windows CE 3.0: Application Programming (Prentice Hall Series on Microsoft Technologies)
ISBN: 0130255920
EAN: 2147483647
Year: 2002
Pages: 181

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