B
SQL Support

This document describes SQL support for Oracle Lite for the Palm Computing Platform. Topics include:

Section B.1, "Oracle Lite for the Palm Computing Platform SQL Support"

B.1 Oracle Lite for the Palm Computing Platform SQL Support

This section discusses Oracle Lite for the Palm Computing Platform SQL support.

B.1.1 Supported SQL Statements

The following lists the SQL statements supported by Oracle Lite for the Palm Computing Platform. SQL statements are required to be uppercase.

Table B-1 Supported SQL Statements

Statement	Description
CREATE TABLE	Creates a table.
DROP TABLE	Drops a table.
CREATE INDEX	Creates an index on a table.
DROP INDEX	Drops an index.
DELETE	Deletes one or many rows from a table.
INSERT	Inserts a row into a table.
SELECT	Finds and retrieves a set of rows.
UPDATE	Updates one or many rows.
COMMIT	Commits changes.
ROLLBACK	Rolls back changes.

B.1.2 CREATE TABLE

Creates a table in the database.

Figure B-1 CREATE TABLE Statement Syntax

Description of cre_tab.gif is contained in the surrounding text

Table B-2 CREATE TABLE Arguments

Argument	Description
table	The name of the table to create.
column	The name of the column.
datatype	The datatype of the column. See "Supported Datatypes" for a list of supported datatypes.
table_constraint	Optional constraint, either PRIMARY KEY or NOT NULL.

B.1.3 DROP TABLE

The DROP TABLE statement removes a table from the database.

Figure B-2 DROP TABLE Statement Syntax

Description of dro_tab.gif is contained in the surrounding text

Table B-3 DROP TABLE Arguments

Argument	Description
table	The name of the table to drop.

B.1.4 CREATE INDEX

The CREATE INDEX statement creates an index on a table.

Figure B-3 CREATE INDEX Statement Syntax

Description of cre_ind.gif is contained in the surrounding text

Table B-4 CREATE INDEX Arguments

Argument	Description
index	The name of the index. Must be unique in the database.
table	The name of the table on which to create the index.
column	The name of the column to use to form the index.

B.1.5 DELETE

The DELETE statement deletes all rows that match the criteria set by the WHERE clause. If the WHERE clause is not specified, all rows in the table are deleted.

Figure B-4 DELETE Statement Syntax

Description of del_fro.gif is contained in the surrounding text

Table B-5 DELETE Arguments

Argument	Description
table	The name of the table.
condition	Optional search condition. Only rows that match the condition are deleted. If the WHERE clause is not specified, all rows in the table are deleted.

B.1.6 DROP INDEX

The DROP INDEX statement deletes an index by its name.

Figure B-5 DROP INDEX Statement Syntax

Description of dro_ind.gif is contained in the surrounding text

Table B-6 DROP INDEX Arguments

Argument	Description
index	The name of the index to drop.

B.1.7 INSERT

The INSERT statement inserts a new row into the table. If the column list is not specified, data are updated in the sequence the columns are defined in the table.

Figure B-6 INSERT Statement Syntax

Description of ins_into.gif is contained in the surrounding text

Table B-7 INSERT Arguments

Argument	Description
table	The name of the table in which to insert the row.
column	The name of the column.
value	Literal value to store in the column. See "Literal Values" for more information.

B.1.8 SELECT

The SELECT statement queries rows from one or many tables.

Figure B-7 SELECT Statement Syntax

Description of sel_fro.gif is contained in the surrounding text

Table B-8 SELECT Arguments

Argument	Description
table	The name of the table.
column	The name of the column.
function	Column functions. See "Supported Group Functions" for a list of supported column functions.
condition	The selection condition. Only rows that match the condition are selected. If not specified, all rows in the table are selected. See "Conditions" for more information.
ORDER BY	Orders rows returned by the SELECT statement according to the given column.

B.1.9 UPDATE

The UPDATE statement updates all rows that match the specified criteria.

Figure B-8 UPDATE Statement Syntax

Description of upd_tab.gif is contained in the surrounding text

Table B-9 UPDATE Arguments

Argument	Description
table	The name of the table.
column	The name of the column.
value	Literal value to store in the column. See "Literal Values" for more information.
condition	The search condition. Only rows that match the condition are updated. If the WHERE clause is not specified, all rows in the table are updated. See "Conditions" for more information.

B.1.10 COMMIT

The COMMIT statement commits the changes made in the current transaction.

Figure B-9 COMMIT Statement Syntax

Description of com_wor.gif is contained in the surrounding text

B.1.11 ROLLBACK

The ROLLBACK statement rolls back changes made in the current transaction.

Figure B-10 ROLLBACK Statement Syntax

Description of rol_wor.gif is contained in the surrounding text

B.1.12 Conditions

The WHERE clause defines a condition to select the result list of rows. The WHERE clause can be used with the commands SELECT, UPDATE, and DELETE.

Figure B-11 WHERE Clause Syntax

Description of whe_cla.gif is contained in the surrounding text

Table B-10 WHERE Clause Parameters

Element	Description
column	Name of a column.
operator	Comparison operator. See "Comparison Operators".
value	Literal value. See "Literal Values" for more information.

Each compare statement has a left operand, a comparison operator, and a right operand.

The left operand must be a valid name of a column. For SELECT statements, the column name can be specified in the format table.column, where table is the name of the table. If a table name is not specified, the first table specified in the FROM clause is used.

B.1.13 Comparison Operators

Comparison operators define how to compare the left and the right operands. The following table lists the supported operators:

Table B-11 Comparison Operators

Operator	Description
=	Equal to.
<>, !=, ^=	Not equal to.
<	Less than.
>	Greater than.
<=	Less than or equal to.
>=	Greater than or equal to.
LIKE	Starts with the specified characters in the right operand. The "%" symbol is only supported when placed at the end of the string.
NOT LIKE	Does not start with the specified characters.
IS NULL	Is a NULL value.
IS NOT NULL	Is not a NULL value.

The right operand can be either a literal value or a name of a column. For the statements UPDATE and DELETE, the right operand must be a literal value. The type of the two operands must be the same for the statement to be valid.

Comparison statements can be chained together by AND or OR operators. The AND operator takes precedence over the OR operator. Grouping by using parenthesis is not currently supported.

For example, to search for rows with a column EmpID (of type INTEGER) equal to 158:

WHERE EmpID = 158

To search for rows with a column EmpID larger than 50 but less than 100, and not equal to 90, or with the column Name (of type VARCHAR) starting with the string 'Sam':

WHERE EmpID > 50 AND EmpID < 100 AND EmpID <> 90 OR Name LIKE 'Sam%'

To search for all rows in the Emp table with the column EmpID matching the rows in the Dept table, and where the Name column is not NULL:

WHERE Emp.EmpID = Dept.EmpID AND Emp.Name IS NOT NULL

B.1.14 Literal Values

The term literal values refer to a fixed data value. For example, 'Jack', 'Sam', AND '101' are all character literal values. Literal number values have a limit of 2147483647 to -2147483647. If a larger number is needed, you can append the number with a decimal and a zero (.0) to force the parser to view it as a real number. For example, instead of 1234567890, use 1234567890.0.

Table B-12 Literal Value Parameters

Literal	Description
text	Literal strings with properties of CHAR, VARCHAR, and VARCHAR2. Text literal values are encapsulated by single quotes, for example: 'Jack' and 'How are you today?'. To specify a single quote character inside a text literal, escape the single quote with another single quote in front of it, for example: 'My cat''s name'. To specify an empty string (length is zero), specify two single quotes ('').
integer	Numbers that contain no decimal point. Integers are specified as an array of digits, optionally preceded by a sign character (+ or -). For example: 123, 256789, and -125. Integer literal values are typed as INTEGER or CHAR (single character).
number	Number literal values are real numbers with decimal points. A number literal value must start with an optional sign character (+ or -), one or many digits, one and only one decimal point (.), and one of many digits. For example: 12.345, 55.0, -1234.5567, and +123.9. Number literal values are treated as type NUMBER, DECIMAL, or NUMERIC. A value is treated as an integer if a decimal point is not included.
date, time, timestamp	Date and time are specified like literal text, except the string must be specified in one of the following formats: 'yyyy-mm-dd' (specifies date) 'hh:mm:ss' (specifies time) 'yyyy-mm-dd hh:mm:ss' (specifies date and time) For example: '1970-07-15' '1999-12-30 12:50:00' '12:59:31'

B.1.15 Supported Datatypes

Oracle Lite for the Palm Computing Platform supports the following datatypes:

Table B-13 Supported Datatypes

Datatype	Description
BINARY	Enables storage of binary data up to 4,096 bytes.
BLOB	A binary large object. Must specify precision (the number of decimal values or bits that can be stored). Maximum size is 64KB.
CHAR	Fixed length character data. Maximum size is 10KB. Default and minimum size is 1 byte.
DATE	Valid date range from January 1, 4712 BC to December 31, 4712 AD. Format is 'yyyy-mm-dd'.
DECIMAL	A number that can be measured in terms of precision (decimal value) or scale (fractional value). You can measure precision by using DECIMAL (p). You can measure scale by using DECIMAL (p, s). The scale cannot be larger than the precision.
DOUBLE	A signed, approximate, numeric value with a mantissa decimal precision 15. Its absolute value is either zero or between 10^-308 and 10^308.
FLOAT	A floating point number.
INTEGER and INT	An integer value whose precision is defined depending upon the operating system. The value range is from -214783647 to 214783647.
NUMBER and NUMERIC	A number that can be measured in terms of precision (decimal value) or scale (fractional value). You can measure precision by using NUMERIC (p). You can measure scale by using NUMERIC (p, s). The maximum value for precision is 38. The scale cannot be larger than the precision.
REAL	Enables you to request a single-precision floating point with no options. The precision is chosen by the implementation and is normally the default single-precision datatype on the hardware platform.
SMALLINT	A small integer whose precision is defined upon implementation. The value range is from -32768232767 to 32768232767.
TIME	Stores a time value in terms of hours, minutes, and seconds. Hours are represented by two digits ranging from 00 through 23. Minutes are represented by two digits ranging from 00 through 59. The seconds value ranges from 00 through 59. Format is 'hh:mm:ss'.
TIMESTAMP	Stores date and time. Format is 'yyyy-mm-dd hh:mm:ss'.
VARCHAR	Variable-length character string with a maximum length size of 10KB. You must specify size. The VARCHAR datatype with precision is defined as a variable length string.
VARCHAR2	Variable-length character string with a maximum length size of 10KB. You must specify size.

B.1.16 Supported Group Functions

A group function returns results based on groups of rows. Group functions can be used in the column list of a SELECT statement. Oracle Lite for the Palm Computing Platform supports the following group functions:

Table B-14 Supported Group Functions

Function	Description
AVG(column)	Average value of column in a group of rows.
COUNT(column)	The number of rows in the query. Only counts rows with column specified with NOT NULL values.
COUNT(*)	The count of all rows in the table, regardless of whether any columns in rows are NULL.
MAX(column)	Maximum value of column for a group of rows.
MIN(column)	Minimum value of column for a group of rows.
SUM(column)	The sum of column for a group of rows.

Note:

AVG and SUM are only supported in the INTEGER (INT) and SMALLINT datatypes.

The GROUP BY clause must be specified if group functions are used in a SELECT statement.

B.1.17 Supported Date Functions

Date functions return dates. Oracle Lite for the Palm Computing Platform supports the following date functions:

Table B-15 Supported Date Functions

Function	Description
CURRENT_DATE	Return the current date.
CURRENT_TIME	Returns the current time.
SYSDATE	Returns the current date and time.

B SQL Support