Home  Beta programs 
  Welcome to Mobipocket Developer Center
powered by FreeFind

Creating Content

Getting Started
What is the Mobipocket file formatHow do I create a Mobipocket eBookStandard eBooksAdvanced eBooks
eBook features
Cross-platform feature supportImage supportTable supportCover PageParagraph rendering and hyphenationHyperlinksGuide itemsFramesIndexes and DictionariesSectionsAuthoring tips
Extended eBook features
DatabasesSQL queriesJavascriptHTML forms
Reference
Open-eBook HTML tagsSupported HTML entitiesHTML form tagsMobipocket custom tagsOPF x-metadata tagsMobipocket URLsMobipocket Document Object Model (DOM)Mobipocket Active Server Pages (ASP)Mobipocket Active Data Objects (ADO)Mobipocket Javascript Objects
Medical
Drug interaction module
Advanced topics
Setting margins
Home | Hide TOC | Download Sample | Add to Favorites updated: 2005-01-21

SQL user's guide


Contents
Mobipocket data table model
Query syntax
Functions that support SQL queries
  Javascript functions with user interaction
  Using a SQL query to generate a RecordSet
  Re-routing the output of an interactive results set into a RecordSet object
  Using SQL queries in advanced SELECT controls
Access to external tables
Like and anagram_of operators

The main purpose of SQL queries in the mobipocket Reader is to display interactive lists of elements chosen using complex criteria. The display is similar to the index view used for indexes but search expressions can be much more complex. For example, you can request all books written by a given author which are not about religion. Moreover, SQL expressions can also be used to visually mark queried elements if they match an additional query. In the previous example, you could this time display all the books from that author but mark those about religion with a bullet. User interaction is supported as well. Queries can  be constructed in such a way as to prompt the user for one or more search keys and then display the results.

A sample screenshot (link to full sample at the top of this page):

Moreover, SQL queries can also be used to construct data collections that can then be handled in JavaScript using the RecordSet object.

1 Mobipocket data table model

Data can be defined in the html content by using the indexing tags <idx:entry>, <idx:orth>, <idx:key>. For a full refence of indexing tags, see the indexing article or the reference documentation. Starting from reader 4.8, additionnal columns can also be deined using the <idx:string>, <idx:subentry> and <idx:ext-subentry> tags. However, Mobipocket SQL can only test for the presence or absence of a value in these fields using the the IS NULL operator.

Important: when using SQL, all entries and keys must be named  using the "name" attribute on the <idx:entry> and <idx:key> tags.

The table structure accessible to SQL queries is as follows:

  • Each set of < idx:entry> tags with the same  "name" attribute defines a table.
  • The name of the table is the name defined in the  "name" attribute.
  • The first column in the table contains items defined in <idx:orth> tags inside of the <idx:entry>
  • Tne name of the first column is always "orth ", but the name of the table can also be used as the name of the first column.
  • <idx:key> tags define additional columns named as specified in the "name" attribute of the tag
  • <idx:string>, <idx:subentry> and <idx:ext-subentry> also define additional columns named in their "name" attribute.

The first column (defined by an idx:orth) is then regarded as the primary key, columns defined by idx:key as foreign keys and all other columns as regular data. This means that only data from idx:orth or idx:key columns are indexed and can therefore be used to retrieve table lines in an efficient way. Trying to match a string on a non indexed column (for example an <idx:string> column) would be so slow that it is not allowed in Mobipocket Reader.

Let's try to picture the table structure using an example:

The following content excerpt:

<idx:entry name="Books">
<idx:orth>Smalcald Articles </idx:orth>
<idx:key name="Author">Martin Luther </idx:key>
<idx:key name="Category">Religion</idx:key>
<idx:string name="Price">2.95</idx:string>
</idx:entry>
<idx:entry name="Books">
<idx:orth>The Grin Bin </idx:orth>
<idx:key name="Author">Mark Lynch </idx:key>
<idx:key name="Category">Cartoon</idx:key>
<idx:string name="Price">3.50</idx:string>
</idx:entry>
<idx:entry name="Books">
<idx:orth>The Grand Alliance </idx:orth>
<idx:key name="Author">Winston Churchill </idx:key>
<idx:key name="Category">History</idx:key>
<!-- price not specified -->
</idx:entry>

Defines the following table:

Table name: Books
Orth (searchable) Author (searchable) Category (searchable) Price (not searchable)
Smalcald Articles Martin Luther Religion

2.95

The Grin Bin Mark Lynch Cartoon

3.50

The Grand Alliance Winston Churchill History

(not set)

2 Query syntax

A request will always start with "SELECT * FROM tablename". However, because of the user interface design on PDAs, only the first column (the primary key) of the table will be displayed in the index view of the Mobipocket Reader. Please notice that all tables used in the request have to be declared in the FROM statement (separated by commas).

Then follows an expression introduced by the WHERE keyword. The expression can use AND, OR, NOT and IS NULL (from Reader 4.8) operators. Comparisons use the  = (equal) and <> (diffent) operators. String constants are written using single quotes. Operator precedence can be specified using parentheses.

The ? (question) operator can be used in place of a litteral constant to request user input (using the index view of the specified column).

Examples (see sample data table above):

SELECT * FROM books WHERE Category='Religion' Result: Smalcald Articles

SELECT * FROM books WHERE Category='Cartoon' OR Author <> 'Martin Luther' Result: The Grin Bin, The Grand Alliance

SELECT * FROM books WHERE Category=? First displays an index view of categories then the books of the category the user selected.

SELECT * FROM books, forbidden_books WHERE books <> forbidden_books This example assumes there is a second table called "forbidden_books" containing, in its first column books that should not be displayed.

SELECT * FROM books WHERE orth LIKE 'The%' Result: The Grin Bin, The Grand Alliance requires Reader 4.8

SELECT * FROM books WHERE price IS NOT NULL Result: The Grin Bin, The Grand Alliance requires Reader 4.8

SELECT * FROM books WHERE orth ANAGRAM_OF 'The Nigri Ben%1' Result: The Grin Bin (on character has been discarded, the 'e' in 'Ben') requires Reader 4.8

In short, supported satandard SQL keywords and operators are:

  • SELECT
  • FROM
  • AS
  • AND
  • OR
  • NOT
  • = (equal)
  • <> (different)
  • IS NULL requires Reader 4.8
  • IS NOT NULL requires Reader 4.8
  • LIKE requires Reader 4.8
  • NOT LIKE requires Reader 5.0

Non-standatrd operators and keywords are:

Supported comparison operations are:

  • foreign key = 'constant string'
  • foreign key = ?
  • primary key = 'constant string'
  • primary key = other key (primary or foreign)
  • data field IS NULL
  • primary key LIKE 'constant string'
  • primary key ANAGRAM_OF 'constant string'

and their opposite operations either using the oposite operator (<> instead of  =) or using the NOT logical operator.

Unsupported comparison operations are:

  • any key IS NULL
  • data field = 'constant string'
  • data field LIKE 'constant string'
  • foreign key LIKE 'constant string'

3 Functions that support SQL queries

3.1 Javascript functions with user interaction

Two Javascript functions can to be used with SQL queries do display an interactive set of results: window.sql_search and window.sql_bullet_search.

Important:
single quotes used in SQL queries must be escaped with an \ (antislash) if the query is used as the argument of a javascript function. For example, the query:

SELECT * FROM books WHERE Category='Religion'

must be written as:

sql_search('SELECT * FROM books WHERE Category=\'Religion\' ')

The functions allow you to specify a compound caption string for all screens the query will display to the user. Strings to be displayed in the different screens are separated by %%% (three consecutive percent characters).

For example, the string "caption 1 %%% caption 2 %%% caption 3" will display "caption 1" on top of the first screen (corresponding to the first ? (question) in the query, "caption 2" on top of the second screen (second ? in the query) and "caption 3" on top of the last screen containig the final results of the request. You should always have as many caption strings as ? operators in the request plus one for the final results.

It can be useful to remind the user of the items he/she clicked on in previous screens. For this purpose, you can use the %key string to insert into the caption the item name selected by the user in the previous screen. You can also use %1, %2, %3,... and so on to insert the item name chosen by the user in any previous screen (numbered from 1).

The sql_bullet_search function is used to display al list of query results where items matching a complementary request are marked by a bullet. Four kinds of bullets are available. They can be used using the following identifiers:

BUL_BLUE
BUL_CROSS_RED
BUL_CHECK_GREEN
BUL_HOSPITAL

Examples (see sample data table above):

sql_search('SELECT * FROM Books WHERE Category=?', 'Please chose a category:%%%Books in category %key are:')

This will display in the first screen:

Please choose a category:
 

Religion
Cartoon
History

Assuming the user clicks on 'Cartoon', the second screen will look like this:

Books in category Cartoon are:
 

The Grin Bin

sql_bullet_search(BUL_BLUE, false, 'SELECT * FROM Books', 'Category<>\'Religion\'')

This will display:

Search string:
 

The Grin Bin
The Grand Alliance
     Smalcald Articles

If the second argument is changed to "true", only buletted entries are displayed. You can leave this setting to the user by passing the value of a checkbox as the second parameter.

sql_bullet_search(BUL_BLUE, true, 'SELECT * FROM Books, 'Category<>\'Religion\'')

Only displays:

Search string:
 

The Grin Bin
The Grand Alliance

3.2 Using a SQL query to generate a RecordSet requires Reader 4.8

If user interaction is not what you want, you can use the sql_execute function. It returns a RecordSet object which you can manipulate in JavaScript. For example, you can move to the next or previous result using .MoveNext and .MovePrevious or retrieve the value of a given field using the .field_name syntax.

The following example retrieves all the books from the table and displays their titles in an alert box:

var records = sql_execute('SELECT * FROM books');
var results = ' - ';
while (!records.EOF)
{
   results = results + records.orth + ' - ';
   records.MoveNext();
}
alert(results);

The resulting display will be:

- The Grin Bin - The Grand Alliance - Smalcald Articles -

3.3 Re-routing the output of an interactive results set into a RecordSet object requires Reader 4.8

There is another way of obtaining a RecordSet from a SQL query. You can use the sql_search or sql_bullet_search functions with an additional parameter which is a JavaScript callback function. When the user selects an entry in the displayed list, instead of jumping to the HTML content of the entry, the Reader will call the callback function and pass it a RecordSet positioned on the line selected by the user. It then becomes possible to manipulate the RecordSet in JavaScript as needed. For example, this can be used to jump to an <idx:subentry> of an <idx:entry>.

Using a JavaScript callback is possible in index_search, cond_index_search and filtered_index_search functions as well.

Example:

sql_search('SELECT * FROM books', 'my caption string', 'my frameset', 'f_my_callback');

The callback could be (this example jumps to the entry following the one selected bu the user):

function f_my_callback(results)
{
   results.MoveNext();
   window.open(results.anchor);
}

3.4 Using SQL queries in advanced SELECT controls requires Reader 5.0

It is possible to fill a list box or a combo box in an HTML form from a database table. Use the <SELECT src="sql:  your query here"> syntax.

Example:

<SELECT name="all" src="select * from books" size="5"/>

The output in a Mobipocket ebook will be visually similar to what you get in a web browser:

However, in Mobipocket, the widget will work even if the list contains tens of thousands of items. The user interface is also optimized to handle very large collections. For example, when you scroll the list, the scrolling speed accelerates so that scrolling through any number of items will never take more than a few seconds.

4 Access to external tables

Tables from external document can also be accessed from SQL queries. They are accessed by their URL. However, they have to be renamed in the FROM part of the request in order to be used.

Example:

SELECT*FROMbooks,'oeb:redirect?title=Exlusion_List$table=banned_books' AS banned
WHERE books <> banned

In this example, the URL of the external table (in single quotes) is renamed as "banned" which is then used in the query. The resulting list of books will not contain all the books contained int the "banned_books" table in the ebook identified by "Exclusion_List".

Here is the explanation of all the parts of the URL used in the example above:

oeb:redirect?title=Exlusion_List

This finds in the books visible from the library of the Mobipcket Reader the book identified by "Exclusion_List". Book identifiers are derived from the book title and are given by prcgen (the book compiler) at the end of each build.

$table=banned_books

The $ (dollar) sign is used to access objects embedded within a document. Here, the type of the object is "table" and its name is "banned_books"

Important: For technical reasons too complex to be explained here, database columns declared with <idx:key> must also be defined by their URL and renamed in the FROM statement of the SQL query if they are in a different ebook. This shortcomming will be fixed in future a version of Mobipocket reader. Here is the syntax for performing a filtered search from a book that does not conatain the database:

SELECT * FROM 'oeb:redirect?title=my_external_data$table=books' AS books, 'oeb:redirect?title=my_external_data$table=author' AS author WHERE author = 'Mark Lynch'

5 LIKE and ANAGRAM_OF operators require Reader 4.8

LIKE operator: use wild="yes" in your data

In order to use the LIKE operator in SQL queries, the data table must be optimized. Since these optimisations have an impact on data size, they are not performed by default. You have to ask for them specifcally when defining index entries with the wild="yes" attribute (wild stands for wildcard). If you enable spell correction using the spell="yes" attribute, LIKE optimizations will be turned on automatically because the spell correction algorithm needs them. Here is an example index entry definition with LIKE optimisations:

<idx:entry wild="yes">...</idx:entry>

LIKE operator syntax

The LIKE operator allows you to use wildcards in string comparisons:

% stands for zero or more characters
_ (underscore) stands for exactly one character

Example (see data above):

SELECT * FROM books WHERE orth LIKE 'The%'

will return 'The Grin Bin' and 'The Grand Alliance'

ANAGRAM_OF operator syntax

The ANAGRAM_OF operator finds anagrams in the database. You specify letters to use to form the anagram and a the maximal number of those letters the algorithm can discard. Both parameters form a single string and are separated by a % sign.

Example (see data above):

SELECT * FROM books WHERE orth ANAGRAM_OF 'The Nigri Ben%1'

returns 'The Grin Bin'. One character has been discarded, the 'e' in 'Ben'

 

Copyright 2000-2007 Mobipocket.com