Creating SQL

SQL is a powerful method of querying database tables. It allows you to specify multiple conditions for the retrieval of your data. You can use a single query on more than one table at a time.

SprintDB Pro uses ADOCE (ActiveX® Data Objects for the Windows® CE operating system) SQL.

For further information on ADOCE SQL, please visit the Microsoft website.

Using the SQL Editor

To simplify the building of your SQL statements, particularly for experienced users but also to allow you to view the entire contents of a statement, SprintDB Pro supplies a SQL Editor. This is located on the SQL Tab.

The Editor allows you to manually enter your SQL statements, but still provides Selection DropDown boxes to speed up the process.

<Top>

SQL Statements

SQL statements begin with the word "SELECT", and use the following syntax:

SELECT - Join
SELECT - Like
SELECT - Order By
SELECT - Projection
SELECT - Restricted
SELECT - Simple

Data Definition Language Statements

ALTER TABLE
CREATE DATABASE
CREATE INDEX
CREATE TABLE
DROP DATABASE
DROP INDEX
DROP TABLE

SELECT - Join

This combines two tables using a join field common to both tables. ADOCE uses a subset of the fields in the combined database to build a recordset.

Because the tables being combined may have fields with the same name, it may be necessary to specify the table name with the field name. For example, if more than one table has the field ProductID, the table name is not optional and you must specify <tablename>.ProductID as the fieldname, where <tablename> is the name of a table that contains a field named ProductID.

This is the only type of join supported in ADOCE.

At least one of the tables in each join clause must be indexed on the joined field.

A recordset returned by a JOIN statement with one or more columns from a single table can be updated. If a returned column has data from more than one table, it is read-only and cannot be updated.

Example

SELECT table1_field1,table1.field,table2.field FROM table1 INNER JOIN table2 ON table1.field3=table2.field4

<Back>

SELECT - Like

This statement searches for and returns all fields that contain a pattern similar to the pattern specified.

Example

SELECT field1,field2 FROM table1 WHERE field3 LIKE 'abcd%'

<Back>

SELECT - Order By

This statement sorts the selected rows by the fields specified.

Syntax

SELECT * FROM tablename ORDER BY fieldname [ASC | DESC]

The ASC tag sorts the data in ascending order, while DESC sorts it in descending order. DESC is the default sort order.

Example

SELECT field1,field2 FROM table1 ORDER BY field1

<Back>

SELECT - Projection

This statement uses a subset of the fields in the database to build a recordset.

Example

SELECT field1,field2 FROM table1

<Back>

SELECT - Restricted

This statement restricts the data returned by comparing the data in the table against the conditions specified in the WHERE clause.

Syntax

SELECT * FROM tablename WHERE fieldname-expression [AND | OR] fieldname-expression

Example

SELECT * FROM table1 WHERE field1 NOT LIKE 'a%' AND field2>=40 AND field3<>100

<Back>

SELECT - Simple

This statement returns all the fields and all the rows from the specified table.

Syntax

SELECT * FROM tablename

Example

SELECT * FROM table1

<Back>

ALTER TABLE

This statement supports several different commands to change the structure of a table, including adding, deleting, moving, and renaming fields.

Syntax ALTER TABLE tablename TO tablename2

ALTER TABLE tablename ADD fieldname fieldtype [BEFORE fieldname2]

ALTER TABLE tablename DROP fieldname

ALTER TABLE tablename MOVE fieldname [BEFORE fieldname2]

ALTER TABLE tablename RENAME fieldname TO fieldname2

Parameters

tablename : Specifies the name of the table in which to make structural changes.

tablename2 : Specifies a new name for an existing table. A table with the same name cannot already exist.

fieldname : Specifies the name of the column in the table to add, remove, or rename. In order to add a column, it cannot already exist.

fieldname2 : Specifies the name of another column in the table. For ADD and MOVE, the name must exist in the table. For RENAME, the name cannot already exist.

fieldtype : Specifies the data type for the column. For more information about data types, see CREATE TABLE.

Example

ALTER TABLE changeme TO changed ALTER TABLE table changed RENAME f1 TO firstfield

<Back>

CREATE DATABASE

This statement is used to create a database on the device

Syntax

CREATE DATABASE 'database_name.cdb'

Parameters

database_name.cdb : Specifies the name of the database to be created. This name must be in single quotes and have the .cdb file extension.

Example

CREATE DATABASE 'myDatabase.cdb'

<Back>

CREATE INDEX

This statement is used to create an index for a field.

Syntax

CREATE INDEX indexname ON tablename (fieldname [DESC])

Parameters

indexname : Specifies the name of a new index.

tablename : Specifies the name of the table on which to create the index.

fieldname : Specifies the name of the field for which to create the index. Only one field is indexed at a time, and only one index is created per field. The index can be given additional properties by specifying an index attribute after the field name.

Attribute DESC :Sort the data in descending order. Default is ascending.

Example

CREATE INDEX i1 ON indexme (f1)

CREATE INDEX i1 ON indexme (f1 DESC)

<Back>

CREATE TABLE

This statement creates a new table.

Syntax

CREATE TABLE tablename (fieldname fieldtype [, fieldname fieldntype])

Parameters

tablename : Specifies the name for a new table.

fieldname : Specifies the name of the column to create in the table.

fieldtype : Specifies the data type for the column. It can be one of the values described in the following table.

Value

Description

Varchar[(n)]

Null-terminated Unicode character string of length n, with a maximum of 255 characters. If n is not supplied, then 1 is assumed, per ANSI SQL.

Text

Variable length string that can hold up to 32,000 characters, typically used for more than 255 characters. This type is also known as Memo field.

Long Varbinary

Binary value of less than 65,469 bytes. This type is also known as OLE Object.

Integer, int

4-byte signed integer.

Smallint

2-byte signed integer.

Float

Double-precision floating point value.

Datetime

Date value.

Bit

Logical or Boolean value—zero for False, nonzero for True.

Example

CREATE TABLE myTable1 (ID Integer, nameField Varchar(20), isMarried Bit, notesField Text)

<Back>

DROP DATABASE

This statement deletes a database from the device.

Syntax

DROP DATABASE 'database_name.cdb' [,…n]

Parameters

database_name : Specifies the name of the database to be removed. This name must be in single quotes and have the .cdb file extension.

n : Specifies additional databases to be dropped in a comma separated list.

Example

DROP DATABASE myDatabase

<Back>

DROP INDEX

This statement removes an index from a field.

Syntax

DROP TABLE

This statement permanently deletes a table from a Windows CE–based device.

Syntax

DROP TABLE tablename

Parameters

tablename : Specifies the name of the table to delete.

Example

DROP TABLE allfields

<Back>