Languages

Menu
Sites
Language
Gear S2 with sqlite

hi,

is it possible to use sqlite in gear s2 or other gear devices?

currently I am using gear s2 with Tizen version 2.3.2.2

I got this "sample code" from forum, but it is not working in my gear s2. 

https://developer.tizen.org/ko/forums/native-application-development/complete-tutorial-sqlite-database-crud-operation-and-data-access-tizen-native-application?langredirect=1

do you have other sample code?

thanks

PS.

here is the code with some modification 

/*

    at the main app, service_app_crete function.

*/

 

bool service_app_create(void *data) {

 

    dlog_print(DLOG_DEBUG, LOG_TAG, "service_app_create start");

    if (initdb() == SQLITE_OK) {

        int ret = insertMsgIntoDb(10, "testtest");

 

        dlog_print(DLOG_DEBUG, LOG_TAG, "insertMsgIntoDb ret = %d", ret);

 

        if (!ret) {

            dlog_print(DLOG_DEBUG, LOG_TAG, "insert successfully!");

        } else {

            dlog_print(DLOG_ERROR, LOG_TAG, "Data insert error! %d", ret);

        }

 

    }

 

    dlog_print(DLOG_DEBUG, LOG_TAG, "service_app_create end");

    return true;

}

-----------------------------------

sqlitedbhelper.c

-----------------------------------

#include <string.h>

#include <sqlite3.h>

#include <stdint.h>

#include <stdlib.h>

#include <storage.h>

#include <app_common.h>

#include <sqlitedbhelper.h>

#include <stdio.h>

 

#define DB_NAME "database.db"

#define TABLE_NAME "MyDataTable"

#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.*/

 

 

#define VERIFY_SQLITE_OK(tag, arg, logLevel, retValue) do{ if (SQLITE_OK != (arg)) \

            { OC_LOG_V((logLevel), tag, ("Error in " #arg ", Error Message: %s"), \

               sqlite3_errmsg(g_db)); return retValue; }}while(0)

 

 

sqlite3 *healthDataDb; /*name of database*/

int select_row_count = 0;

 

int opendb() {

    char * dataPath = app_get_data_path();

//    char * dataPath = "/opt/usr/media/Downloads/";

    int size = strlen(dataPath) + 10;

 

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

 

    strcpy(path, dataPath);

    strncat(path, DB_NAME, size);

 

    dlog_print(DLOG_DEBUG, LOG_TAG,"DB Path = [%s]", path);

 

    int ret = sqlite3_open_v2(path, &healthDataDb, SQLITE_OPEN_CREATE | SQLITE_OPEN_READWRITE, NULL);

    dlog_print(DLOG_DEBUG, LOG_TAG, "opendb ret = %d",ret);

 

    if (ret != SQLITE_OK)

        dlog_print(DLOG_ERROR, LOG_TAG,"DB Create Error! [%s]", sqlite3_errmsg(healthDataDb));

 

    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*/

 

    dlog_print(DLOG_DEBUG, LOG_TAG, "crate table query : %s", sql);

 

    ret = sqlite3_exec(healthDataDb, sql, NULL, 0, &ErrMsg); /*execute query*/

    if (ret != SQLITE_OK) {

        dlog_print(DLOG_ERROR, LOG_TAG, "Table Create Error! [%s]", ErrMsg);

        sqlite3_free(ErrMsg);

        sqlite3_close(healthDataDb); /*close db instance as instance is still open*/

 

        return SQLITE_ERROR;

    }

    dlog_print(DLOG_DEBUG, LOG_TAG, "Db Table created successfully!");

    sqlite3_close(healthDataDb); /*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);",    msg_data, type, dateTime);

    dlog_print(DLOG_DEBUG, LOG_TAG,"Insert query = [%s]", sqlbuff);

 

    ret = sqlite3_exec(healthDataDb, sqlbuff, insertcb, 0, &ErrMsg); /*execute query*/

    if (ret != SQLITE_OK) {

        dlog_print(DLOG_ERROR, LOG_TAG,"Insertion Error! [%s]", sqlite3_errmsg(healthDataDb));

        sqlite3_free(ErrMsg);

        sqlite3_close(healthDataDb); /*close db instance for failed case*/

 

        return SQLITE_ERROR;

    }

 

    sqlite3_close(healthDataDb); /*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) {

        dlog_print(DLOG_ERROR, LOG_TAG,"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 " TABLE_NAME " ORDER BY QR_ID DESC"; /*select query*/

    int ret;

    char *ErrMsg;

    select_row_count = 0;

 

    ret = sqlite3_exec(healthDataDb, sql, selectAllItemcb, (void*) msg_data,

            &ErrMsg);

    if (ret != SQLITE_OK) {

        dlog_print(DLOG_DEBUG, LOG_TAG,"select query execution error [%s]", ErrMsg);

        sqlite3_free(ErrMsg);

        sqlite3_close(healthDataDb); /*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;

 

    dlog_print(DLOG_DEBUG, LOG_TAG,"select query execution success!");

    sqlite3_close(healthDataDb); /*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 " TABLE_NAME " where QR_ID=%d;", id);

 

    int ret = 0;

    char *ErrMsg;

 

    ret = sqlite3_exec(healthDataDb, sql, selectAllItemcb, (void*) msg_data,

            &ErrMsg);

    if (ret != SQLITE_OK) {

        dlog_print(DLOG_DEBUG, LOG_TAG,"select query execution error [%s]", ErrMsg);

        sqlite3_free(ErrMsg);

        sqlite3_close(healthDataDb);

 

        return SQLITE_ERROR;

    }

 

    dlog_print(DLOG_DEBUG, LOG_TAG,"select query execution success!");

 

    /*assign fetched data into caller's struct*/

    *msg_data = qrydata;

 

    sqlite3_close(healthDataDb); /*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 " TABLE_NAME " QueryData where QR_ID=%d;", id);

 

    int counter = 0, ret = 0;

    char *ErrMsg;

 

    ret = sqlite3_exec(healthDataDb, sql, deletecb, &counter, &ErrMsg);

    if (ret != SQLITE_OK) {

        dlog_print(DLOG_ERROR, LOG_TAG,"Delete Error! [%s]", sqlite3_errmsg(healthDataDb));

        sqlite3_free(ErrMsg);

        sqlite3_close(healthDataDb);

 

        return SQLITE_ERROR;

    }

 

    sqlite3_close(healthDataDb);

 

    return SQLITE_OK;

}

 

int deleteMsgAll() {

    if (opendb() != SQLITE_OK) /*create database instance*/

        return SQLITE_ERROR;

 

    char sql[BUFLEN];

    snprintf(sql, BUFLEN, "DELETE FROM " TABLE_NAME ";");

 

    int counter = 0, ret = 0;

    char *ErrMsg;

 

    ret = sqlite3_exec(healthDataDb, sql, deletecb, &counter, &ErrMsg);

    if (ret != SQLITE_OK) {

        dlog_print(DLOG_ERROR, LOG_TAG,"Delete Error! [%s]", sqlite3_errmsg(healthDataDb));

        sqlite3_free(ErrMsg);

        sqlite3_close(healthDataDb);

 

        return SQLITE_ERROR;

    }

 

    sqlite3_close(healthDataDb);

 

    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 " TABLE_NAME ";";

    char *ErrMsg;

 

    int ret = 0;

 

    ret = sqlite3_exec(healthDataDb, sql, row_count_cb, NULL, &ErrMsg);

    if (ret != SQLITE_OK) {

        dlog_print(DLOG_ERROR, LOG_TAG,"Count Error! [%s]", sqlite3_errmsg(healthDataDb));

        sqlite3_free(ErrMsg);

        sqlite3_close(healthDataDb);

 

        return SQLITE_ERROR;

    }

 

    dlog_print(DLOG_DEBUG, LOG_TAG,"Total row found[%d]", g_row_count);

 

    sqlite3_close(healthDataDb);

 

    *num_of_rows = g_row_count;

    g_row_count = 0;

    return SQLITE_OK;

}

 

Edited by: cui on 24 May, 2017

Responses

4 Replies
Shaswati Saha

Please follow the code below. It works for me. Hope it'll work in your case too.
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"
#include <dlog.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);


	 int ret = sqlite3_open_v2( path , &sampleDb, SQLITE_OPEN_CREATE|SQLITE_OPEN_READWRITE, NULL);

	 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*/

   dlog_print(DLOG_DEBUG, "shaswati", "create table query : %s", sql);
   ret = sqlite3_exec(sampleDb, sql, NULL, 0, &ErrMsg); /*execute query*/
   if(ret != SQLITE_OK)
   {
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb); /*close db instance as instance is still open*/

	   return SQLITE_ERROR;
   }

   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)
{
	initdb();

	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);

	ret = sqlite3_exec(sampleDb, sqlbuff, insertcb, 0, &ErrMsg); /*execute query*/
	if (ret != SQLITE_OK)
	{
	   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].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 SampleTable 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)
	{
	   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;

	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 SampleTable where QR_ID=%d;", id);

   int ret = 0;
   char *ErrMsg;

    ret = sqlite3_exec(sampleDb, sql, selectAllItemcb, (void*)msg_data, &ErrMsg);
	if (ret != SQLITE_OK)
	{
	   sqlite3_free(ErrMsg);
	   sqlite3_close(sampleDb);

	   return SQLITE_ERROR;
	}

        /*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 SampleTable where QR_ID=%d;", id);

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

   ret = sqlite3_exec(sampleDb, sql, deletecb, &counter, &ErrMsg);
	if (ret != SQLITE_OK)
	{
	   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 SampleTable;");

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

   ret = sqlite3_exec(sampleDb, sql, deletecb, &counter, &ErrMsg);
	if (ret != SQLITE_OK)
	{
	   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 SampleTable;";
   char *ErrMsg;

   int ret = 0;

   ret = sqlite3_exec(sampleDb, sql, row_count_cb, NULL, &ErrMsg);
	if (ret != SQLITE_OK)
	{
	    sqlite3_free(ErrMsg);
	    sqlite3_close(sampleDb);

	    return SQLITE_ERROR;
	}

	sqlite3_close(sampleDb);

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

In the app_create function write the code according to the following snippet.
 

int num_of_rows = 0;
int ret;
strcpy(msgdata->msg, "test data3");

ret = insertMsgIntoDb(2, msgdata->msg);

dlog_print(DLOG_DEBUG, "shaswati", "Insertion %d", ret);
if(!ret){
		dlog_print(DLOG_DEBUG, "shaswati", "Inserted msg successfully!");
	} else {
		dlog_print(DLOG_DEBUG, "shaswati", "Insertion Error!");
	}

QueryData* retmsgdata = (QueryData*) calloc (1, sizeof(QueryData));
ret = getAllMsgFromDb(&retmsgdata, &num_of_rows);

dlog_print(DLOG_DEBUG, "shaswati", "Retrieve %d", ret);
if(!ret){
	dlog_print(DLOG_DEBUG, "shaswati", "Retrieved %s [%d] rows successfully!", retmsgdata->msg,num_of_rows);
} else {
	dlog_print(DLOG_DEBUG, "shaswati", "Error!");
}

Please have a look into the status of my db after inserting three times.
http://imgur.com/a/Pq6pA

Shaswati Saha

Hi, Is there any update? Could you solve the problem?

Ben Iti

 

 

Shaswati,

How do I find this sample.db?

I'm on a mac and I can't find this sample.db file anywhere. 

I've built the code to run on my emulator. How do I find the file inside the emulator?

 

Shaswati Saha

You may try searching for it in your application data directory path. Use device manager in this case.