Languages

Menu
Sites
Language
Complete tutorial for sqlite database CRUD operation and data access from Tizen native application

There are so many tutorials regarding SQL Lite database access. Most of them shield very minimal information or not Tizen application friendly.

Few days ago, I tried to implement SQLite database for my native Tizen application. I didn’t find a complete solution that will guide me completely to implement database in my native application. However, I have gone through good number of article, forum to finish my simple task. It seems to me, it is required to have a complete answer that will give us exact instruction to implement SQLite database in our native app.

I have divided total task into two layers–

  1. SQLite DB layer – all the database related methods will be there. Application will just call the APIs provided by SQLite DB end.
  2. Application layer – No need to worry about anything like- opening db, closing db, creating db or table etc . This layer will just call desired API and use saved data in the application.

 

SQLite DB layer:

There are two files –

  1. Sqlitedbhelper.h – open to application layer. Application layer will invoke this API and use database.
  2. Sqlitedbhelper.c – overall database implementation and API preparation will be done here.

 

Sqlitedbhelper.h:

We will implement few APIs that will do overall database operations. And a common structure QueryData will be used for data communication between application and database layer.

/*this structure will be commonly used in both database and application layer*/
typedef struct
{
    int id;
    int type;
    char msg[MAX_LEN];
    char date[MAX_LEN];
} QueryData;

/*inset type, msg in the database. Date will be stored from system and id is autoincrement*/
int insertMsgIntoDb(int type, const char * msg_data);

/*fetch all stored message form database. This API will return total number of rows found in this call*/
int getAllMsgFromDb(QueryData **msg_data, int* num_of_rows);

/*fetch stored message form database based on given ID. Application needs to send desired ID*/
int getMsgById(QueryData **msg_data, int id);

/*delete stored message form database based on given ID. Application needs to send desired ID*/
int deleteMsgById(int id);

/*fetch all stored message form database*/
int deleteMsgAll();

/*count number of stored msg in the database and will return the total number*/
int getTotalMsgItemsCount(int* num_of_rows);

Sqlitedbhelper.c:

#include <string.h>
#include <sqlite3.h>
#include <stdint.h>
#include <stdlib.h>
#include <storage.h>
#include <app_common.h>
#include <stdio.h>
#include "sqlitedbhelper.h"

#define DB_NAME "sample.db"
#define TABLE_NAME "SampleTable"
#define COL_ID "QR_ID"
#define COL_DATA "QR_DATA"
#define COL_TYPE "QR_CODE"
#define COL_DATE "QR_DATE"

#define BUFLEN 500 /*assume buffer length for query string's size.*/

sqlite3 *sampleDb; /*name of database*/
int select_row_count = 0;

/*open database instance*/
int opendb()
{
     char * dataPath = app_get_data_path(); /*fetched package path available physically in the device*/
	 int size = strlen(dataPath)+10;

	 char * path = malloc(sizeof(char)*size);

	 strcpy(path,dataPath);
	 strncat(path, DB_NAME, size);

	 DBG("DB Path = [%s]", path); /*prepared full path, database will be stored there*/

	 int ret = sqlite3_open_v2( path , &sampleDb, SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE, NULL);
	 if(ret != SQLITE_OK)
		ERR("DB Create Error! [%s]", sqlite3_errmsg(sampleDb));

	 free(dataPath);
	 free(path);
         /*didn't close database instance as this will be handled by caller e.g. insert, delete*/
	 return ret;
}

int initdb()
{
	if (opendb() != SQLITE_OK) /*create database instance*/
		return SQLITE_ERROR;

   int ret;
   char *ErrMsg;
   /*query preparation for table creation. it will not be created the table if it is exists already*/
   char *sql = "CREATE TABLE IF NOT EXISTS "\
		    TABLE_NAME" ("  \
			COL_DATA" TEXT NOT NULL, " \
			COL_TYPE" INTEGER NOT NULL, " \
			COL_DATE" TEXT NOT NULL, " \
			COL_ID" INTEGER PRIMARY KEY AUTOINCREMENT);"; /*id autoincrement*/

   DBG("crate table query : %s", sql);

   ret = sqlite3_exec(sampleDb, sql, NULL, 0, &ErrMsg); /*execute query*/
   if(ret != SQLITE_OK)
   {
	   ERR("Table Create Error! [%s]", ErrMsg);
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb); /*close db instance as instance is still open*/

	   return SQLITE_ERROR;
   }
   DBG("Db Table created successfully!");
   sqlite3_close(sampleDb); /*close the db instance as operation is done here*/

   return SQLITE_OK;
}

/*callback for insert operation*/
static int insertcb(void *NotUsed, int argc, char **argv, char **azColName){
   int i;
   for(i=0; i<argc; i++){
      /*usually we do not need to do anything.*/
   }
   return 0;
}

int insertMsgIntoDb(int type, const char * msg_data)
{
	if(opendb() != SQLITE_OK) /*create database instance*/
		return SQLITE_ERROR;

	char sqlbuff[BUFLEN];
	char *ErrMsg;
	int ret;
	/*read system date time using sqlite function*/
	char* dateTime = "strftime('%Y-%m-%d  %H-%M','now')";

        /*prepare query for INSERT operation*/
	snprintf(sqlbuff, BUFLEN, "INSERT INTO "\
			TABLE_NAME" VALUES(\'%s\', %d, %s, NULL);", /*didn't include id as it is autoincrement*/
	            		msg_data, type, dateTime);
	DBG("Insert query = [%s]", sqlbuff);

	ret = sqlite3_exec(sampleDb, sqlbuff, insertcb, 0, &ErrMsg); /*execute query*/
	if (ret != SQLITE_OK)
	{
	   ERR("Insertion Error! [%s]", sqlite3_errmsg(sampleDb));
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb); /*close db instance for failed case*/

	   return SQLITE_ERROR;
	}

	sqlite3_close(sampleDb); /*close db instance for success case*/

	return SQLITE_OK;
}

QueryData *qrydata;

/*this callback will be called for each row fetched from database. we need to handle retrieved elements for each row manually and store data for further use*/
static int selectAllItemcb(void *data, int argc, char **argv, char **azColName){
        /*
        * SQLite queries return data in argv parameter as  character pointer */
        /*prepare a temporary structure*/
	QueryData *temp = (QueryData*)realloc(qrydata, ((select_row_count + 1) * sizeof(QueryData)));

	if(temp == NULL){
		ERR("Cannot reallocate memory for QueryData");
		return SQLITE_ERROR;
	} else {
                /*store data into temp structure*/
		strcpy(temp[select_row_count].msg, argv[0]);
		temp[select_row_count].type = atoi(argv[1]);
		strcpy(temp[select_row_count].date, argv[2]);
		temp[select_row_count].qr_id = atoi(argv[3]);

                /*copy temp structure into main sturct*/
		qrydata = temp;
	}

	select_row_count ++; /*keep row count*/

   return SQLITE_OK;
}

int getAllMsgFromDb(QueryData **msg_data, int* num_of_rows)
{
	if(opendb() != SQLITE_OK) /*create database instance*/
		return SQLITE_ERROR;

	qrydata = (QueryData *) calloc (1, sizeof(QueryData)); /*preparing local querydata struct*/

   char *sql = "SELECT * FROM QueryData ORDER BY QR_ID DESC"; /*select query*/
   int ret;
   char *ErrMsg;
   select_row_count = 0;

    ret = sqlite3_exec(sampleDb, sql, selectAllItemcb, (void*)msg_data, &ErrMsg);
	if (ret != SQLITE_OK)
	{
	   DBG("select query execution error [%s]", ErrMsg);
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb); /*close db for failed case*/

	   return SQLITE_ERROR;
	}

        /*assign all retrived values into caller's pointer*/
	*msg_data = qrydata; 
        *num_of_rows = select_row_count;

	DBG("select query execution success!");
	sqlite3_close(sampleDb); /*close db for success case*/

   return SQLITE_OK;
}

int getMsgById(QueryData **msg_data, int id)
{
	if(opendb() != SQLITE_OK) /*create database instance*/
		return SQLITE_ERROR;

	qrydata = (QueryData *) calloc (1, sizeof(QueryData));

   char sql[BUFLEN];
   snprintf(sql, BUFLEN, "SELECT * FROM QueryData where QR_ID=%d;", id);

   int ret = 0;
   char *ErrMsg;

    ret = sqlite3_exec(sampleDb, sql, selectAllItemcb, (void*)msg_data, &ErrMsg);
	if (ret != SQLITE_OK)
	{
	   DBG("select query execution error [%s]", ErrMsg);
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb);

	   return SQLITE_ERROR;
	}

	DBG("select query execution success!");

        /*assign fetched data into caller's struct*/
	*msg_data = qrydata;

	sqlite3_close(sampleDb); /*close db*/

   return SQLITE_OK;
}

static int deletecb(void *data, int argc, char **argv, char **azColName){
   int i;
   for(i=0; i<argc; i++){
	/*no need to do anything*/
   }
  
   return 0;
}

int deleteMsgById(int id)
{
	if(opendb() != SQLITE_OK) /*create database instance*/
		return SQLITE_ERROR;

   char sql[BUFLEN];
   snprintf(sql, BUFLEN, "DELETE from QueryData where QR_ID=%d;", id);

   int counter = 0, ret = 0;
   char *ErrMsg;

   ret = sqlite3_exec(sampleDb, sql, deletecb, &counter, &ErrMsg);
	if (ret != SQLITE_OK)
	{
		ERR("Delete Error! [%s]", sqlite3_errmsg(sampleDb));
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb);

	   return SQLITE_ERROR;
	}

	sqlite3_close(sampleDb);

   return SQLITE_OK;
}

int deleteMsgAll()
{
	if(opendb() != SQLITE_OK) /*create database instance*/
		return SQLITE_ERROR;

   char sql[BUFLEN];
   snprintf(sql, BUFLEN, "DELETE from QueryData;");

   int counter = 0, ret = 0;
   char *ErrMsg;

   ret = sqlite3_exec(sampleDb, sql, deletecb, &counter, &ErrMsg);
	if (ret != SQLITE_OK)
	{
		ERR("Delete Error! [%s]", sqlite3_errmsg(sampleDb));
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb);

	   return SQLITE_ERROR;
	}

	sqlite3_close(sampleDb);

   return SQLITE_OK;
}

int g_row_count = 0;

static int row_count_cb(void *data, int argc, char **argv, char **azColName)
{
	g_row_count = atoi(argv[0]); /*number of rows*/

	return 0;
}
int getTotalMsgItemsCount(int* num_of_rows)
{
	if(opendb() != SQLITE_OK) /*create database instance*/
		return SQLITE_ERROR;

   char *sql = "SELECT COUNT(*) FROM QueryData;";
   char *ErrMsg;

   int ret = 0;

   ret = sqlite3_exec(sampleDb, sql, row_count_cb, NULL, &ErrMsg);
	if (ret != SQLITE_OK)
	{
		ERR("Count Error! [%s]", sqlite3_errmsg(sampleDb));
	    sqlite3_free(ErrMsg);
	    sqlite3_close(sampleDb);

	    return SQLITE_ERROR;
	}

	DBG("Total row found[%d]", g_row_count);

	sqlite3_close(sampleDb);

	*num_of_rows = g_row_count;
	g_row_count = 0;
   return SQLITE_OK;
}

 

Application Layer:

Now, our database API is ready for access from application layer. We just need to include dbhelper API in application source file. Then, call desired methods for required operation.

#include "sqlitedbhelper.h"


QueryData* msgdata;

static Eina_Bool
layout_pop_cb(void *data, Elm_Object_Item *it)
{
    if(msgdata)
	free(msgdata); /*need to free this structure in pop_cb of current layout*/
    return EINA_TRUE;
}

/*allocate msgdata memory. this will be used for retrieving data from database*/
msgdata = (QueryData*) calloc (1, sizeof(QueryData));

int num_of_rows = 0;

/*retrieve all msgdata from database*/
ret = getAllMsgFromDb(&msgdata, &num_of_rows); /*sending msgdata reference to get data from database; num_of_rows reference  to get count of rows*/
if(!ret){
	DBG("Retrieved [%d] rows successfully!", num_of_rows);
} else {
	ERR("Data retrieval error!");
}

/*get number of row count*/
ret = getHistoryItemsCount(&num_of_rows);
if(!ret){
	DBG("Total rows found: [%d]", num_of_rows);
} else {
	ERR("row count error!");
}

/*delete msg item from db by msg_id*/
ret = deleteMsgById(id);
if(ret){
	ERR("Data delete error!");
} else {
	DBG("Data delete success!");
}

 

 

Thats all! Enjoy SQLite database in your native application. Please feel free to comments if any query or suggestion.

Edited by: Mohammad Nur Nobi on 16 Nov, 2015
List

Responses

8 Replies
Jeongsu Kim

You'd better to use sqlite_mprintf with %q options, instead of sprintf and %s.

https://www.sqlite.org/c3ref/mprintf.html

or use sqlite3_prepare_v2 and sqlite3_bind_* apis to prevent SQL injection or other problems.

Anand Rudrakshi

Hi,

This is a nice implementation of sql lite operations. Why don't you add this in code snippet section rather than in Native app development.

Mohammad Nur Nobi

Thank you. Sure, I will post it in code snippet too. 

deeps uttekar

 

Hi Mohammad,

You example and explaination is good. I tried to implement same. 

I am able to open database. I am able run create query and return 0 ie success to me. 

But when I try to close database, it crashes. can you please help me. I am stuck since 3 days

this line is getting crashed in CPP ret = sqlite3_close(databaseHandle); /*close the db instance as operation is done here*/

 

#ifndef CONTROLLERS_DATABASECONTROLLER_H_
#define CONTROLLERS_DATABASECONTROLLER_H_
 
#include "storage/MasterData.h"
 
#include <app_common.h>
#include <storage.h>
#include <sqlite3.h>
#include <storage/MasterData.h>
#include <stdint.h>
#include <stdlib.h>
#include <stdio.h>
#include <storage.h>
#include<string.h>
 
class DatabaseController {
private:
static DatabaseController* instance;
DatabaseController();
public:
static DatabaseController* getInstance();
int createTables();
};
 
#endif /* CONTROLLERS_DATABASECONTROLLER_H_ */
 
 
Code of CPP
sqlite3 *databaseHandle;
int DatabaseController::createTables() {
 
char * dataPath = app_get_data_path(); /*fetched package path available physically in the device*/
//todo check why this 10 is added
int size = strlen(dataPath) + 10;
 
char * path = (char *) malloc(sizeof(char) * size);
 
strcpy(path, dataPath);
strncat(path, MasterData::DATABASE_NAME, size);
 
int ret = sqlite3_open_v2(path, &databaseHandle,
SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);
if (ret != SQLITE_OK) {
}
 
free(dataPath);
free(path);
/*didn't close database instance as this will be handled by caller e.g. insert, delete*/
 
char *ErrMsg;
std::vector < std::string > queries = MasterData::getAllCreateQueries();
for (std::string createQuery : queries) {
/*query preparation for table creation. it will not be created the table if it is exists already*/
char const *sql = createQuery.c_str();
 
ret = sqlite3_exec(databaseHandle, sql, NULL, 0, &ErrMsg); /*execute query*/
 
}
ret = sqlite3_close(databaseHandle); /*close the db instance as operation is done here*/
sqlite3_free(ErrMsg);
 
return SQLITE_OK;
}

 

Fattaneh Ameri Mahabadian

​Hello , how can I use SQLite in C# for Tizen mobile? I used SQLite for android and it works but for tizen dose not work? Is there any tricks?

Thanks

 

Ben Iti

so how Does one save the sqlite file? I am running on my emulator and I cannot find the SQLite file inside the shared folder or the res folder.

the log says that entries have successfully been entered into the db ok cool so wheres the SQLite file to confirm?

 

 

 

Mick Caberos

hi can you give a whole sample? please thanks

Yasin Ali

For more detailed information other than this helpful tutorial, I think you may

see here http://Sqlite: Managing the SQL Database