D
Mobile SQL

This document descibes Oracle Lite Mobile SQL. Each section of this document presents a different topic. These sections include:

• Overview

• Installation and Setup

• Main Form

• Table Form

• The SQL Form

• The Edit Row Options

• Palm OS-Specific Features

D.1 Overview

Mobile SQL is an application that runs on the client device (laptop, Palm OS, EPOC, and Windows CE). It allows the user to execute SQL statements against the local database. It is both a developers tool and a code example. It allows users to access functionality provided by the ODBC and Oracle Lite OKAPI interfaces of the underlying Oracle Lite database engine. Mobile SQL for Palm Computing devices has a GUI-based interface.

D.1.1 Database Access

Mobile SQL accesses the database through both the ODBC and OKAPI interface. Most functions are performed through ODBC, but functions that ODBC cannot handle are implemented using OKAPI function calls.

Figure D-1 Mobile SQL Overview

Mobile SQL provides the following:

• Create and Delete database.

• Connect to a database.

• List available tables in the database.

• Display table rows and column in a table view.

• Update column from the table.

• Delete row from the table.

• An interactive console for entering SQL statements.

D.2 Installation and Setup

To start using Mobile SQL follow these configuration steps:

1. Verify that you have installed the Java Runtime Engine version 1.2.2. If you are uncertain which version, the console command java -version returns the Java version in use.

2. Use the Install Tool from Palm Computing to install msql.prc, okindex.prc, and odbc.prc.

Once installed, a Mobile SQL icon appears on the Palm Computing device screen.

D.2.1 Starting and Stopping Mobile SQL

Mobile SQL is started by tapping the Mobile SQL icon on the device screen.

D.3 Main Form

This is the first form displayed when the program is executed. It prompts the user to connect to a database. This is also where the user can create and delete databases.

Figure D-2 Mobile SQL Database Screen

The Main Form has the following components:

Table D-1 Main Form Options

Item	Description
Database List	Shows the available Oracle Lite databases on the local device.
Open	When clicked, the currently selected (or entered) database is opened. Mobile SQL displays the Table Form.
About	Displays the About dialog.
Create Database	Displays the Edit Column dialog.
Delete Database	Displays a Confirmation dialog with the message "Are you sure you want to delete the database "<Name>"?" Where <Name> is the currently selected database name. If the user clicks the OK button, the application deletes the database.
Up Arrow	Moves the current selection to the previous database in the list.
Down Arrow	Moves the current selection to the next database in the list.

D.4 Table Form

Once the user selects a database, the program connects to that database and displays the Table Form. The Table Form provides a simple view to all the available tables in the database. If the user select a table, the table content is displayed.

Figure D-3 Table View

The Table Form has the following components:

Item	Description
Table List	Lists all of the tables in the current database. If the user selects a table from the list, the rows in the table are displayed in the Row Table.
Table Rows and Columns	Initially empty, when a table is selected, the table control displays the content of the table. Each cell in the table control displays a column in the table. Sorting: The first row of the table control displays the names of the columns. If the user click a column in the first row, the program sorts the table by the column. The first time it is clicked, it sorts the column in ascending order and displays a plus (+) sign after the column name. If the same column is clicked, the sort order is toggled between descending (-) and ascending (+) order. Only one column can be used for sorting at a time. Scrolling: If there are more columns than the table can display in one screen, a horizontal scrollbar is displayed to allow the user to scroll through the columns. If there are more rows than the table can display in one screen, a vertical scrollbar is displayed to allow the user to scroll through the rows.
View, Tables	Switch to the Table Form.
View, SQL Console	Switch to the SQL Form.
Edit, Insert	Mobile SQL allows dynamic editing of table entries. Tap the entry and Mobile SQL provides an insertion point. Insert a new row. Displays the Edit Row Dialog
Edit, Delete	Deletes the current row. Tap the Row number and you are prompted to confirm the row deletion.

D.5 The SQL Form

The SQL Form allows the user to enter SQL statements from the user interface and execute them. This allows the user to access the dynamic SQL functionality exposed by the ODBC layer.

Figure D-4 Command Entry Menu

The SQL Form has the following components:

Item	Description
Statement	Prompts the user for the SQL statement to run. The statement can be terminated by a semi-colon (;) character, but is not required. The maximum length of the statement is 511 characters. Tapping the down arrow icon displays a parsed menu of several of the most likely options at each step of the statement creation process.
Clear	If the user clicks this button, both the SQL Statement edit box and the Results text box are cleared.
Execute	When clicked, Mobile SQL executes the SQL statement.
Results	Displays the result after executing the SQL statement.
View, Tables	Switch to the Table Form.
View, SQL Console	Switch to the SQL Form.
Edit, Cut	Cut the selected text (if any) from the SQL Statement to the clipboard.
Edit, Copy	Copy the selected text (if any) from the SQL Statement edit box to the clipboard.
Edit, Paste	Copy the text from the clipboard (if any) to the SQL Statement field at the current cursor position.

D.6 The Edit Row Options

The Edit Row Dialog allows the user to modify columns of a specific row or create a new row.

Figure D-5 Eample of Row Edit

The Edit Row Dialog has the following components:

Table D-2 Edit Row Dialog Options

Item	Description
ColumnsList	A list of all of the column names and values in the table. When a new column is selected, the value in the Value edit box is updated to the temporary column, and updates the column list.
NullItButton	A push button, when pushed it nullifies the current column.
Value	An edit box that allows the user to enter the value of the currently select column.
OK	A push button, when pushed it updates the columns and dismisses the dialog.
Cancel	A push button, when pushed it cancels the changes and dismisses the dialog.

D.7 Palm OS-Specific Features

The following lists all the features implemented specifically for the Palm OS:

• In-place editing - Allows a user to modify a column in the Table Form by clicking any cell on the screen. A user can edit the column without loading the dialog.

• Beaming tables - Allows users to send a table (schema and data) to another device using the IRDA link.

• SQL hints - A list box of all recommend SQL keywords and table names.

• ORDER BY Orders rows returned by the SELECT statement according to the given column. Descending is the default order. Optionally, the ASC keyword specifies ascending order.