Android Databases
This article give a brief overview of Android (SQLite) databases and how they are used with HAC. It then shows how to create a simple book database app. Both a book database project and its app can be downloaded from the HAC projects web page.
So what is a database? A database is an organized collection of data arranged in one or more tables. Each table represents one collection such as a book or video catalog and has its own layout as defined by its record structure. Each record within a table is stored in its own a row, and all rows within the table have the same format as defined by the number of fields(columns) and their data types.
A table within a database has:-
– rows and columns
– a row = one record
– a column = fields within the records
For instance a record may have the following format:-
Column:- 1 2 3 4
Name:- ID Title Author Comment
Values:- 1 Android J Smithy Android coding
Note, each table has a primary column and the primary column values must be unique to ensure individual records can found. Generally the database itself takes care of the primary column as records are added to it.
.
Location of Database Files
The Android OS ensures that each app can store database files within the app’s private protected folder thus preventing other apps from accessing them and users from browsing for them. These protected database files are often referred to as internal database files.
There are times though when it is necessary to work with so called external database files, These are database files in other locations such as on the SD card or when bundled with an app in its data folder. External database files are particularly useful when it comes to backing up, upgrading, emailing or exporting the database onto a desktop computer.
HAC apps can work with both internal and external database files. It also has Export and Import functions for copying databases both from the internal area and into the internal area.
HAC apps can work with multiple databases simultaneously and in order to do this most HAC database related commands/functions identify the database by specifying its full file path.
.
Using a Database
The 4 basic operation on databases are Create or Open, Insert, Query and Upgrade
- Create – this creates a database as specified by the location, table name and record structure
- Insert – this puts records into the database
- Query – this requests information or records meeting certain criteria from the database
- Upgrade – used when the structure of the database must be changed – eg adding an extra column.
.
Defining a Database
Before creating and using a database some thought must be given to the structure and format of the data to be stored. For instance, are the fields numeric or text, and which field is the primary one.
Android SQLite has the following three data types although it does not check for valid data formats -:-
- text (string) – eg ‘Hello World’,
- long integer – eg 23095
- real – eg 14.268
Note, HAC uses strings(text) for all its variables and will automatically convert the data types to string.
.
Example of a Book Database
This example is a simple book database where the database file name is ‘infox.db’ and the table name is ‘books’. It has the following record format:-
ID (primary) – integer
Title – string
Authors – string
Comment – string
With HAC most of the database related commands and functions require that the database be identified by specifying its full file path.
Note, many of HAC’s database functions return a 1 if successful, otherwise they return 0. However, in the example below, no error checking is carried out in order to try and make the code easier to understand.
a) Open or Create
A database must be open before it can be used. If it already exists then opening it will do, otherwise it must be created, after which it will be in the open state.
Opening an existing database is easy, just use the DbOpenFN function. This function needs the full file path to the database and the file path can point to either an internal or external database file. The database can be set to either read-only mode or read/write mode by setting the mode flag to 0 or 1 respectively.
Creating a database is more complex because the structure of the database must be passed to the DbCreateFN function. In the following example it is assumed that the database does not exist and therefore must be created.
Note the part where it forms the file path pointing to the database. Line 10 uses a database file within the App’s ‘Local Folder’ while Line 11 has a commented out part which refers to an Internal database file.
@ --- sets the database path --- @ -- local path is chosen but internal commented out --- Global dpPath Local dbname @ filename Put 'infox.db' into dbname @ --- form source path --- Put LocalPathFN into dbPath @Put DatabasePathFN into dbPath Append '/' onto dbPath Append dbname onto dbPath
This next part specifies the table structure and then creates the database:-
@ --- sets up and creates database from dbPath ---
@ --- table name is 'books'
Global dbPath
Local version,table,structure,okay
@ db version
Put 1 into version
@ table name
Put 'books' into table
@ form db structure - columns and their data types
Put '(' into structure
Append '_id integer primary key autoincrement,' onto structure
Append ' title text not null,' onto structure
Append ' author text not null,' onto structure
Append ' comment text not null);' onto structure
@ --- Create Database ---
Put DbCreateFN(dbPath,version,table,structure) into okay
.
b) Insert 3 records
The SQL commands for inserting a record and its data into a database can appear quite daunting to the uninitiated therefore the HAC commands try to make it easier for the beginner to insert data into a database.
The insert data process uses three steps to insert a record:-
1 – Init – creates the record
2 – Build – adds data to the record
3 – Insert – inserts the record into the database
@ --- insert 3 records into the database --- Global dbPath Local dbname,path,command,okay @ --- Record 1 --- DbInsertInit(dbPath,'books') DbInsertByName(dbPath,'title','Android G1') DbInsertByName(dbPath,'author','Zaks') DbInsertByName(dbPath,'comment','info about phone') Put DbInsertDoFN(dbPath) into okay @ --- Record 2 --- DbInsertInit(dbPath,'books') DbInsertByName(dbPath,'title','Dogs') DbInsertByName(dbPath,'author','Meloni') DbInsertByName(dbPath,'comment','about dogs') Put DbInsertDoFN(dbPath) into okay @ --- Record 3 --- DbInsertInit(dbPath,'books') DbInsertByName(dbPath,'title','Android Databases') DbInsertByName(dbPath,'author','Peters') DbInsertByName(dbPath,'comment','Android and sql') Put DbInsertDoFN(dbPath) into okay
.
c) Queries
Querying a database involves asking it if certain fields contain a search value(s), either strings or numbers. The database will then return any requested fields in the form of an array that can then be accessed for information. Queries can be quite complex, involving matching several fields to values, in which case SQLite really shows its power.
Building the query is the most difficult part of querying a database because the programmer needs to have some understanding of SQLite syntax. The example shown here is simple but still very useful.
There are three steps to querying a database:-
1 – Build – build the query command(string).
2 – Execute – execute the query command on the database.
3 – Retrieve – retrieve any results from query.
Note, if the query string contains single quotes then this can clash with HAC’s compiler syntax as HAC uses single quotes to start and end literal strings. Therefore in the example below the single quotes which are part of the query are implemented using the ChrFN(39) function. At the present time there are also some issues with the Android OS implementation of SQLite, in particular for strings containing special characters such as % sign.
@ --- Query database records for titles including 'Android' ---
Global dpPath
Local table,query,count,n
@ -- set table name --
Put 'books' into table
@ -- build SQLite query string --
Put 'SELECT _id, title FROM books WHERE title LIKE ' into query
Append ChrFN(39) onto query
Append '%Android%' onto query
Append ChrFN(39) onto query
@ -- display query in field 1 --
Put query into field 1
@ -- execute and display query status in field 2 --
Put DbExecQueryFN(dbPath,query) into field 2
@ -- see how many results were returned --
Put DbQueryCountFN(dbPath) into count
@ -- display count in field 3 --
Put count into field 3
@ -- display any results in field 4 --
Clear field 4
if count>0 then
for n=1 to count
Put DbQueryResultFN(dbPath,n,'title') after field 4
endfor
endif
.
d) Close
It is best to close a database when it is not in use as this makes it less likely to be corrupted and also saves memory.
@ --- Close database --- Global dbPath DbClose(dbPath)
.
Summary
The above example should help almost anyone create their own database app with HAC. Although it doesn’t go into much depth with SQLite syntax it still shows how to create a database, add information to it, query the database, and then close it.
HAC supports both internal and external databases, and apps can have multiple databases open at the same time.
For more information about creating database apps with HAC, look at HAC’s built-in help or its HAC Help pdf.
There is a database project and its built app on our examples web page http://www.hypernextandroid.com/hnfiles/rescreator.html Compared to the example presented here, the project is more of a test bed that demonstrates HAC’s database functionality.
.
Members of HAC forums can download the latest trial version of HAC, see details here:- http://www.hypernextandroid.com/hnfiles/downloads.html
HyperNext Android Creator 