ITTIA DB SQL Getting Started Guide

ITTIA DB SQL is a relational database management software library for embedded systems and intelligent Internet-of-Things devices. Device applications embed ITTIA DB SQL to store, manage, analyze, connect, and distribute data.

Request an evaluation kit for your TQ target board.

The ITTIA DB SQL SDK contains the following folders:

ITTIA DB SQL documentation includes:

• User’s Guide: The essential guide to ITTIA DB SQL usage and software development.
• C API Reference: Reference manual for the ITTIA DB C API.
• C++ API Reference: Reference manual for the ITTIA DB C++ API.

Host

• OSELAS toolchain Appnote building OSELAS Toolchain or Install the binary OSELAS Toolchain

Target

• Target system with operating RootFS
• User with password (needed for ssh remote access)
• Network connection to development host

To install the ITTIA DB SQL development kit on the host, unpack the archive as root with the following command:

tar xzf -C / ittiasql-*-linux*.tar.gz

The ITTIA DB SQL SDK will be installed to /opt/ittiadb-<version>/<arch>.
Next, transfer the bin and lib folders from the SDK to the target’s /usr folder.

Finally, add the toolchain to the host PATH.

Generic:

PATH=PATH:/path/to/your/OSELAS.Toolchain<version>/<cross compiler name>/gcc-<version>-glibc-<version>-binutils-<version>-kernel-<version>-sanitized/bin 

For this tutorial:

PATH=$PATH:/opt/OSELAS.Toolchain-2013.12.2/arm-v7a-linux-gnueabihf/gcc-4.8.3-glibc-2.18-binutils-2.24-kernel-3.12-sanitized/bin

On the target:

1. Run the ITTIA SQL Utility with the command ittiasql.
2. Create a new database file with the .create command. The database file is created in the current working directory by default.
3. Create a table, insert a few rows, and using select to list all rows in the table. For example:

   create table hello_world (x integer, y varchar(20));
   insert into hello_world
     values (1, 'Hello World');
   insert into hello_world
     select n, 'Hello ' || cast(n as varchar) from $nat(3);
   select * from hello_world;

4. Save all changes to the database with the commit; SQL statement. If you do not commit your changes, they will be lost when you close the database.
5. Use the .exit command to close the database when you are finished.

On the host, in the ITTIA DB SQL SDK folder, run:

make -C examples

This will build database sample programs. Copy one of the example programs, such as examples/c/sql/sql_select_query_c, to a writeable directory on the target, then run it from that directory. Most examples will create a database file, perform some updates, then prompt for a SQL statement. Enter a blank line to quit the example, or enter a statement, such as:

select * from tables

Start the ITTIA DB Server by running dbserver –no-restrict on the target. Use the –help option to learn more about this command. To connect the database on server from the ittiasql utility, open the example database that you created in the previous section using the .open command and the <idb+tcp://> protocol:

.open idb+tcp://localhost/example.db

Use the ITTIA DB Server to share a database file between different applications and processes. In each application, prefix the file name with idb+tcp://localhost/ to connect with dbserver.

On the host, create hello_sql.c in a new folder:

#include <storage/data/environment.h>
#include <storage/ittiadb/connection.h>
#include <storage/ittiadb/query.h>
#include <storage/ittiadb/transaction.h>
#include <storage/data/single_field.h>
#include <storage/types/character_varying.h>
 
#include <iostream>
 
using ::storage::data::Environment;
using ::storage::ittiadb::Connection;
using ::storage::ittiadb::Query;
using ::storage::ittiadb::Transaction;
using ::storage::data::SingleField;
using ::storage::types::CharacterVarying;
 
int main(int argc, char* argv[])
{
    Environment::init();
 
    // Create a local database file
    Connection database("hello_sql.ittiadb");
    database.open(Connection::CreateAlways);
 
    // Initialize database schema
    Query(database, 
        "create table hello_world ("
        "  id integer,"
        "  message varchar(20)"
        ")"
        ).execute();
 
    // Insert a record
    Query(database,
        "insert into hello_world values (0, 'hello world')"
        ).execute();
 
    // Read the value of a single database field
    SingleField message;
    Query(database,
        "select message from hello_world where id = 0"
        ).execute(message);
 
    // Save changes and release locks
    Transaction(database).commit();
 
    // Output field value
    std::cout << *message.to< CharacterVarying<20> >() << std::endl;
 
    return 0;
}

Create GNUmakefile in the same folder:

ARCH = arm-v7a-linux-gnueabihf
CXX = $(ARCH)-g++
 
ITTIA_DB_VERSION = 1.0.0
ITTIA_DB_HOME = /opt/ittiadb-$(ITTIA_DB_VERSION)/$(ARCH)
 
hello_sql: hello_sql.o
     $(CXX) -o $@ $(LDFLAGS) $< -L$(ITTIA_DB_HOME)/lib -littiasql -lstorage -pthread
 
hello_sql.o: hello_sql.cpp
     $(CXX) -c -o $@ $(CPPFLAGS) $(CXXFLAGS) -pthread -I$(ITTIA_DB_HOME)/include $<

Run make to compile the program hello_sql. Transfer this program to the target and run from a writeable directory.

The ITTIA DB SQL SDK includes examples in the following categories:

• File Storage Examples
• Statement Examples
• Replication Examples
• Security Examples
• Error Handling Examples
• Data Modeling Examples
• Memory Storage Examples
• Shared Access Examples
• Phone Book

If the ITTIA DB SQL shared library is not installed to /usr/lib on the target, you may get a missing library error. This error can be remedied by setting the LD_LIBRARY_PATH environment variable on the target:

export LD_LIBRARY_PATH=/path/to/lib

During evaluation on TQ platforms, you have access to to full support from ITTIA.

• Contact ITTIA Technical Support

• Linux
• VxWorks
• QNX
• Windows

• ITTIA DB SQL Training
• Request a consulation