Using Mimer SQL Mobile
Mimer SQL Mobile is a full scale RDBMS with a really small footprint, which makes it suitable for resource-constrained environments such as handheld devices and mobile phones. It is a multi user database server with complete support for standard SQL and transaction management.
The database in the mobile device can be accessed simultaneously from several local applications at the same time. Concurrently, it can also be accessed from external clients in the network. Furthermore, a local application on the device can access other Mimer SQL databases on other network nodes, for example databases on Linux, UNIX, OpenVMS or Windows.
The client/server communication in Mimer SQL Mobile utilizes standardized network protocols such as LAN/WLAN, 3G/GRPS, Bluetooth, serial port, etc.
Data stored in Mimer SQL Mobile is compressed using a very efficient algorithm that minimize the use of storage media.
Mimer SQL Mobile for Windows Mobile includes a Windows based (NT, 2000, XP or Vista) development environment where you, together with Microsoft’s embedded tools, can develop database applications for mobile devices. Before the application is transferred to the target hardware it can be verified in an emulation environment on the workstation.
For synchronization between the workstation and the device, we recommend to use Microsoft ActiveSync.
The example environment
This article will present two applications - one using ODBC and one using SQL embedded in C++. These examples are ready-to-use Visual Studio projects that include step-by-step instructions on how to build the applications.
As mentioned, the provided applications use different database APIs to implement the same functionality. They use the same GUI and can access the same database.
The GUI for the application is very simple. It shows a selection list where a name can be chosen and the corresponding address is displayed. In the background a log record is stored for each performed operation. The log can be displayed and emptied.
The data model for the application is formed on three tables where one is the logbook, one is storing names and the third is storing addresses. The latter two are coupled together using the Foreign Key clause. All statements used to create the initial database are available in the mobile.sql file. The following SQL statements are used to create the tables:
create table person(
name nchar varying(50),
surname nchar varying(50),
create table person_address(
address_id int default next_value of address_id_seq,
address nchar varying(100),
city nchar varying(50),
foreign key(pnr) references person(pnr));
create table audit(
action nchar varying(50));
In addition, some statements to be used in the device are created:
create statement select_persons
select pnr, name, surname from person;
create statement select_address
select address_id, phone, zipcode, address, city from person_address
create statement insert_audit
insert into audit(logts, action)
create statement select_audit
select logts, action from audit;
create statement clear_audit
delete from audit;
create statement insert_person
insert into person(pnr, name, surname)
values(:pnr, :name, :surname);
create statement insert_address
insert into person_address(pnr, phone, zipcode, address, city)
values(:pnr, :phone, :zipcode, :address, :city);
The sequence of work can shortly be described as follows;
- Creation of the database structure
The database structure is created on the workstation.
- Creation of executables statements
The statements, which are used by the application to access the database, are created. These can either be static or dynamic. In the dynamic case, variables are used to couple the statement with the application (see the final example statement above).
- Database deployment
The database, including the statements, is then exported to the handheld device.
The first thing to do is to install Microsoft embedded Tools and then Mimer SQL Mobile.
When Mimer SQL Mobile is installed a program group called "Mimer SQL Mobile 10.0", or corresponding, is created which contains various programs and catalogues for Online Documentation, Utilities and Internet Resources.
In the "Online Documentation" catalogue, a Getting Started Guide is located – this is the starting point for the description on how to work with Mimer SQL Mobile. Further information is found in the Mimer SQL Documentation Set that includes a Programmer’s Manual, a User’s Manual, a Reference Manual and a System Management Handbook.
Below is a short description to get going with the examples. The programs used in the description are the Mimer Administrator, found directly under the Program Group, and Batch SQL, found in the Utilities sub catalogue.
Create a local database on the workstation
After installing Mimer SQL Mobile, a database should be created on the workstation. This will be the database that you will use for development. You create the database by opening the Mimer Administrator, selecting the Local tag and click Add. Give this database the name "devmobile" so that it will match with the example environment. The Description field for the database is not required but the Home Directory is. Use the Browse button to find or create a suitable directory for the database. Use the default options for the rest of the fields and press Ok.
When you get the question to create databanks, click Yes. Use the default values unless where you are asked to select a password for the database user SYSADM. Enter a password, and remember this for later. When the databanks have been created you are asked if you want to "Start Wizard for Development User and Databank and Example", unselect this option and perform only the startup of the database server. (The reason for not installing the "Development User and Databank and Example" is that it has nothing in common with the examples we are using in this description. Instead we recommend that these environments are installed in a separate database. It is of course possible to have all environments in the same database, but in mobile environments it is usually an issue to keep the environment as small as possible due to resource limitations).
Load the example environment into the database
When the database is successfully created the example database environment for the applications should be loaded. The SQL statements for doing this are available in the file mobile.sql.
Start the Batch SQL program found under the Utilities catalogue and select the database that you just created in the dialogue. Logon as the SYSADM user with the password that you selected above. When you get the SQL> prompter you should give the following statement to read the mobile.sql file:
If the file is not located in the current directory you may have to use the complete path, e.g. 'c:\tmp\mobile.sql'.
Create a Remote Database for the device or for the emulation environment on the workstation
You should now create the database in the target environment, i.e. in the device or in the emulation environment. You do this by opening the Mimer Administrator on the workstation where you select the Remote tab and click the Add button. Give this database the name "mobiledb" so that it will match with the example environment. Under Protocol you should choose "tcp – emulator" if you should use the emulator or else "rapi". You will now get into a dialogue where you should select the default options all through. The dialogue is concluded by a question asking if you want to export the database (see below).
Exporting the example database to "mobiledb"
It is now possible to export the complete database environment to the device or to the emulation environment. You can explicitly perform an export by opening the Mimer Administrator on the workstation. By a right-click on a database in the list of local databases, a menu is displayed where an option "Export to handheld" or "Export to emulation" is available. You will also get an opportunity to export when you create your target database.
When exporting the database you will get into a dialogue with predefined names for source and target databases. If these names correspond with the names of the created databases no change is needed, just fill in the SYSADM password and press Ok. Otherwise, update the database information.
Install and run the application
Unpack one of the zip-files (see below) with the client type you want to use, making sure that the catalogues in the zip-file are expanded. These zip-files contain the example applications. You can read about the applications in the ReadMe.txt that is found in the corresponding Visual Studio project.
For details on how to manage Mimer SQL on the device, please refer to the Mimer SQL Mobile for Windows, Getting Started Guide, found in the In the "Online Documentation" catalogue, or on the developer site Documentation page.
The following files and packages are used in this article:
CppExampleEsql.zip - Visual C example using Embedded SQL and Mimer SQL Mobile.
CppExampleOdbc.zip - Visual C example using ODBC and Mimer SQL Mobile.
mobile.sql – SQL statements that create and load the example database.
The latest version of Mimer SQL Mobile is found at the download page.
Please be in touch on firstname.lastname@example.org if you want further information on Mimer SQL Mobile.
Last updated: 2009-10-27