Gear S2 with sqlite

By cui

24 May 2017 11:43 English 4 Replies

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

25 May 2017 21:05

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

Reply to this post
Mark as answer
- Report
- Edit
- Delete

Shaswati Saha

02 Jun 2017 04:38

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

Reply to this post
Mark as answer
- Report
- Edit
- Delete

Ben Iti

05 Feb 2018 00:46

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?

Reply to this post
Mark as answer
- Report
- Edit
- Delete

Shaswati Saha Ben Iti

05 Feb 2018 22:05

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

Reply to this post
Mark as answer
- Report
- Edit
- Delete

User Menu

Tizen Developers

Community

Community

Responses

Search form

Languages

Responses