11 Working Directly with the Database

This document includes the following sections:

Section 11.1, "About Using a Client Database"
Section 11.2, "Enabling Applications to Use SQL Initialization Scripts"

11.1 About Using a Client Database

As stated in Section 8.5, "Specifying the Client Database Location for an Application," you can enable an application to bypass synchronization with the backend server using Oracle Database Lite Mobile Server in favor of a SQLite client database by selecting the Standalone Database on Client option in the Create Database Connection dialog of the deployment profiles dialog and then defining the location of database in the Device Database File field using one of the platform-appropriate formats listed in Table 11-1.

Table 11-1 Fully Qualified Paths to SQLite Databases on BlackBerry and Windows Mobile

Usage Scenario	Path Format in Device Database File Field
Windows Mobile device with a database on an internal file system	`\SAMPLE.db`
Windows Mobile device with a database on an external storage card	`\Storage Card\SAMPLE.db` You can only create SQL databases in flash memory on Windows Mobile emulators, not on the storage card. Windows Mobile devices, however, do not have this limitation.
BlackBerry with a database on an internal file system	`/store/home/user/SAMPLE.db` In general, SQLite databases on BlackBerry smartphones can only be created on an SD card. While some BlackBerry smartphones permit databases on internal flash memory, you should always specify a database that resides on an SD card to ensure maximum compatibility.
BlackBerry, with a database on an external SD card	`/SDCard/SAMPLE.db`

Because SQL databases are binary-compatible across platforms, you can use the same database file on either Windows Mobile or BlackBerry by entering the location in the appropriate format.

Using the Run Database Initialization Script options, as shown in Figure 11-1, you can enable the application to use a SQL script to initialize the database each time the application starts.

Figure 11-1 Enabling the Use of a SQL Script

11.2 Enabling Applications to Use SQL Initialization Scripts

If you do not want an application to synchronize data with Oracle Database Lite Mobile Server, you can enable it to use a client database through a SQL initialization script. Although this simple script supports a subset of SQL syntax, it is robust enough to populate a database with some default values.

Example 11-1 illustrates a SQL initialization script. This example shows some of the supported SQL syntax (described in Section 11.2.3, "SQL Syntax") through its use of the DROP TABLE, CREATE TABLE, and INSERT commands and the NUMBER and VARCHAR2 data types. For more information, see Section 11.2.1, "Supported Column Data Type Declarations," and Section 11.2.2, "Literal Format for Date Types."

Example 11-1 A SQL Initialization Script

DROP TABLE PERSONS;

CREATE TABLE PERSONS
(
PERSON_ID NUMBER(15) NOT NULL,
FIRST_NAME VARCHAR2(30),
LAST_NAME VARCHAR2(30),
EMAIL VARCHAR2(25) NOT NULL
);

INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 100, 'David', 'King', 'steven@king.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 101, 'Neena', 'Kochhar', 'neena@kochhar.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 102, 'Lex', 'De Haan', 'lex@dehaan.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 103, 'Alexander', 'Hunold', 'alexander@hunold.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 104, 'Bruce', 'Ernst', 'bruce@ernst.net');

11.2.1 Supported Column Data Type Declarations

Table 11-2 lists the data types used in column declarations. The types in italic font get mapped.

Table 11-2 Data Types

Declared Type	Mapped Type
BIGINT	BIGINT
BINARY	BINARY
BINARY_DOUBLE	DOUBLE
BINARY_FLOAT	FLOAT
BLOB	BLOB
CHAR	CHAR
CLOB	CLOB
DATE	DATE
DECIMAL	DECIMAL
FLOAT	FLOAT
INT	INT
LONG	BIGINT
LONG VARCHAR	LONG VARCHAR
NCHAR	NCHAR
NUMBER	DECIMAL
NUMERIC	DECIMAL
TEXT	VARCHAR
TIME	TIME
TIMESTAMP	TIMESTAMP
VARCHAR	VARCHAR

11.2.2 Literal Format for Date Types

Table 11-3 lists the literal formats for date types that are allowed in the SQL script.

Table 11-3 Literal Formats for Date Types

Declared Type	Allowed Format
DATE	'yyyy-mm-dd'
TIME	'hh:mm:ss'
TIMESTAMP	'yyyy-mm-dd hh:mm:ss.fffffffff'

11.2.3 SQL Syntax

The SQL script supports a subset of the SQL data types and commands.

Declaring Data Types

Example 11-2 shows the syntax for declaring data types.

Example 11-2 Declaring Data Types

CHAR( n ) | NCHAR( n ) | VARCHAR( n ) | LONG VARCHAR( n ) | INT | 
DECIMAL( p,s ) | FLOAT | DOUBLE | DATE | TIME | TIMESTAMP| BLOB | CLOB | BINARY

Using Commands

Example 11-3 lists the supported commands.

Table 11-4 Supported Commands

Command	Type	Description	BNF Notation
`DROP TABLE`	DDL	Removes existing objects from the database.	`DROP TABLE name...`
`CREATE TABLE`	DDL	Creates a new table.	`CREATE TABLE table ( column type [ NULL \| NOT NULL ] [ UNIQUE ] [ DEFAULT value ] [column_constraint_clause \| PRIMARY KEY ] [, ... ] ] [, ...] )`
`INSERT`	DML	Inserts new rows into table.	`INSERT INTO table ( column [, ...] ) VALUES ( expression [, ...] )`

11.2.4 Inserting Multiple Rows into a Table

Example 11-3 shows adding rows to a table one at a time, using separate INSERT statements. Alternatively, you can use a single INSERT statement in the following form to add multiple rows into the table, as illustrated in Example 11-4:

INSERT INTO table (column1, column2,...) VALUES(?,?,?,?);

Example 11-3 Inserting Rows into a Table

INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 100, 'Steven', 'King', 'steven@king.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 101, 'Neena', 'Kochhar', 'neena@kochhar.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 102, 'Lex', 'De Haan', 'lex@dehaan.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 103, 'Alexander', 'Hunold', 'alexander@hunold.net');
INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES ( 104, 'Bruce', 'Ernst', 'bruce@ernst.net');

Example 11-3 shows how a single INSERT statement adds the same rows to the PERSONS table as did the separate statements used in Example 11-4.

Example 11-4 Inserting Multiple Rows into a Table

INSERT INTO PERSONS (PERSON_ID, FIRST_NAME, LAST_NAME, EMAIL) VALUES(?,?,?,?);
{
100, 'Steven', 'King', 'steven@king.net'
101, 'Neena', 'Kochhar', 'neena@kochhar.net'
102, 'Lex', 'De Haan', 'lex@dehaan.net'
103, 'Alexander', 'Hunold', 'alexander@hunold.net'
104, 'Bruce', 'Ernst', 'bruce@ernst.net'
};

11.2.5 Commit Handling

Commit statements are ignored when encountered. Each statement is committed as it is read from the SQL script.

11.3 Adding the SQL Script as a Resource to the ADF Mobile Client Application

After you write the script, you add it as a resource to the ADF Mobile client application by selecting the required Run database initialization script option and entering its location in the Initialization script field in the Client Database page. For more information, see Section 8.5, "Specifying the Client Database Location for an Application."