How to use Android NDK SQLite3 extension json1

Question

I am sorry that I lack English first.

I compiled it because I needed SQLite3 extension JSON1 in Android NDK

gcc -Os -I. -DSQLITE_THREADSAFE=0     -DSQLITE_ENABLE_JSON1             \
   -DSQLITE_DEFAULT_MEMSTATUS=0       -DSQLITE_USE_ALLOCA               \
   -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 -DSQLITE_LIKE_DOESNT_MATCH_BLOBS  \
   -DSQLITE_MAX_EXPR_DEPTH=0          -DSQLITE_OMIT_DECLTYPE            \
   -DSQLITE_OMIT_PROGRESS_CALLBACK    -DSQLITE_OMIT_SHARED_CACHE        \
   -DSQLITE_OMIT_DEPRECATED           -DHAVE_USLEEP     -DHAVE_READLINE \
   shell.c sqlite3.c -ldl -lreadline -lncurses -o sqlite3

and result of compile is sqlite3.exe

how to use this file for Anroid NDK or can I find other way?

The code I want to use

JNIEXPORT void JNICALL
Java_com_test_ndkapp_MainActivity_SQLiteCPP(
        JNIEnv *jenv,
        jobject self,
        jstring database_path
){
    sqlite3 *db;
    sqlite3_stmt *stmt;

    // jstring to const char *
    const char *database = jenv->GetStringUTFChars(database_path, 0);

    char *zErrMsg = 0;
    int rc;

    //SQL ERROR:no such function: json
    const char * create_sql = "create table user(name, phone)";
    const char * insert_sql = "insert into user (name, phone) values(\"oz\", json('{\"cell\":\"+491765\", \"home\":\"+498973\"}'))";
    const char * select_sql = "select json_extract(user.phone, '$.cell') from user";

    if (sqlite3_open_v2(database, &db, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE , NULL) == SQLITE_OK) {
        LOGE("database open!!");

        rc = sqlite3_exec(db, create_sql, NULL, NULL, &zErrMsg);

        if( rc != SQLITE_OK ) {
            LOGE("SQL ERROR:%s", zErrMsg);
            sqlite3_free(zErrMsg);
        } else {
            LOGE("CREATE Operation done successfully\n");
        }

        rc = sqlite3_exec(db, insert_sql, NULL, NULL, &zErrMsg);

        if( rc != SQLITE_OK ) {
            LOGE("SQL ERROR:%s", zErrMsg);
            sqlite3_free(zErrMsg);
        } else {
            LOGE("INSERT Operation done successfully\n");
        }

        rc = sqlite3_prepare_v2(db, select_sql, -1, &stmt, NULL);

        if( rc != SQLITE_OK ) {
            LOGE("SQL ERROR:%s", zErrMsg);
            sqlite3_free(zErrMsg);
        } else {
            while ((rc = sqlite3_step(stmt)) == SQLITE_ROW) {
                const unsigned char * aaa           = sqlite3_column_text (stmt, 0);
                const unsigned char * bbb           = sqlite3_column_text (stmt, 1);

                LOGE("%s, %s", aaa, bbb);
            }

            if (rc != SQLITE_DONE) {
                LOGE("error: %s", sqlite3_errmsg(db));
            }

            LOGE("SELECT Operation done successfully\n");

            sqlite3_finalize(stmt);
        }

    } else {
        LOGE("database can not open!! : %s", sqlite3_errmsg(db));
    }

    sqlite3_close(db);
}

I tried this
just put .c file and .h file in cpp folder → SQLite3 is worked but no such function json

score 2 · Accepted Answer · answered Dec 04 '18 at 08:36

2

You need libsqlite3, not an executable, so you don't need to compile shell.c. There is a GitHub project that includes the Android.mk that creates such library for you, you will probably find the static library easier to use in your JNI project.

answered Dec 04 '18 at 08:36

Alex Cohn

56,089
9
113
307

1

Your answer was of great help. I got libsqlite3.so and .a files and the answer to your other writing was helpful during the process. Now I will try to use it at CmakeList. – SeongJae Lee Dec 05 '18 at 05:41

score 1 · Answer 2 · answered Dec 06 '18 at 00:50

I found out how you can use JSON1 by adding a Flag in an environment that uses CMakeLists if you develop NDK in AndroidStudio.

First, download sqlite-amalgamation-(the version you want).zip
and unzip .zip file
https://www.sqlite.org/download.html (Download link)

Place the 4 files you have obtained on the desired path in the cpp directory of the project.

The path to the file that I used

Then add code to CMakeList.txt.

add_library( # Sets the name of the library.
         native-lib

         # Sets the library as a shared library.
         SHARED

         # Provides a relative path to your source file(s).
         src/main/cpp/native-lib.cpp
         src/main/cpp/sqlite/shell.c
         src/main/cpp/sqlite/sqlite3.c
         src/main/cpp/sqlite/sqlite3.h
         src/main/cpp/sqlite/sqlite3ext.h
    )

....

add_definitions( -DSQLITE_ENABLE_JSON1 )

Now you can use the JSON1 functions of SQLite3.

const char * create_sql = "create table user(name, phone)";
const char * insert_sql = "insert into user (name, phone) values(\"oz\", json('{\"cell\":\"+491765\", \"home\":\"+498973\"}'))";
const char * select_sql = "select json_extract(user.phone, '$.cell') from user";

How to use Android NDK SQLite3 extension json1

2 Answers2