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;

}