Patching Phone-SQLitePlugin to store and retrieve JSON

[ns-1m]

Since most of time, we are dealing with JSON and AJAX in mobile devices especially using jQuery web framework (i.e JQTouch, jQuery Mobile), why don't we just patch Phone-SQLitePlugin to store and retrieve JSON,

jQuery.getJSON() http://api.jquery.com/jQuery.getJSON/

http://supportforums.blackberry.com/t5/Tablet-OS-SDK-for-Adobe-AIR/Using-SQLite-to-store-JSON-object/td-p/717325

Store (from the forum)

  // GOOD example
  // assumes myobject and myconn already exist
     var json_text:String = JSON.encode(myobject);
     var stmt:SQLStatement = new SQLStatement();
     stmt.sqlConnection = myconn;
     stmt.text = "INSERT INTO test VALUES (:bar)";
     stmt.parameters[':bar'] = json_text;
     stmt.execute();

Here is the source code for SQLite3JSON

http://www.ch-werner.de/sqliteodbc/sqlite3json.tgz http://groups.google.com/group/spatialite-users/browse_thread/thread/7e513080d621554d

/*

• Execute SQL sending JSON result using print function pointer *
• Layout of JSON result: *
• object {results, sql}
• results[] object {columns, rows, changes, last_insert_rowid, error}
• columns[]
• object {name, decltype, type } (sqlite3column*)
• rows[]
• changes (sqlite3_changes)
• last_insert_rowid (sqlite3_last_insert_rowid)
• error (sqlite3_errmsg)
• sql (SQL text) *
• For each single SQL statement in "sql" an object in the "results"
• array is produced. *
• Arguments:
• db handle to open SQLite3 database
• sql query(ies) to be executed by sqlite3_prepare et.al.
• pfunc function pointer to char output function
• arg handle for pfunc *
• Result:

• SQLite error code, see sqlite3.h */

  int sqlite3json(sqlite3 *db, const char *sql, putfunc *pfunc, void *arg);

Is this means?, e.g. ( I need help here :-)

var text = SQL;
SQL =  SELECT * from ....
var JSON_outuput = sqltie3json("my_sqlite3_database", SQL); // I need help here to interpret the function properly

What we need is the javascript for this function i.e. int sqlite3json(sqlite3 db, const char sql, putfunc pfunc, void arg);

Sqile3json.h and sqile3json.m (i.e. sqlite3json.c) can be added to the PGSQLPlug,m and *.h without a problem.

Another discussion on SQLite to JSON http://www.nimblekit.com/forum/viewtopic.php?f=5&t=2668

From the above forum.

   function json2DB (data) {
           var jsonData = eval("(" + data+ ")");   
        for (i = 1; i < 4; i++) {
        query = "INSERT INTO item (category,title,subtitle,image,right_image,js) VALUES('1','" + jsonData[i].name + "','" + jsonData[i].program_id + "','image12.png','1','')";

        database.executeSQL(query);
        //NKAlert("Test", query); this shows the query. checked it with sqlite manager and the sql is correct
    }
}

I like to hear opinions and ideas.

Thanks.

Noli

[davibe]

See lawnchair adapter

Davide Bertola (from iPhone)

Il giorno 25/mar/2012, alle ore 06:52, Noli Sicad reply@reply.github.com ha scritto:

Since most of time, we are dealing with JSON and AJAX in mobile devices, why don't we just patch Phone-SQLitePlugin to store and retrieve JSON,

http://supportforums.blackberry.com/t5/Tablet-OS-SDK-for-Adobe-AIR/Using-SQLite-to-store-JSON-object/td-p/717325

Here a bit of the code from the blackberry forum

// GOOD example // assumes myobject and myconn already exist var json_text:String = JSON.encode(myobject); var stmt:SQLStatement = new SQLStatement(); stmt.sqlConnection = myconn; stmt.text = "INSERT INTO test VALUES (:bar)"; stmt.parameters[':bar'] = json_text; stmt.execute();

---

Reply to this email directly or view it on GitHub: https://github.com/davibe/Phonegap-SQLitePlugin/issues/22

[ns-1m]

I want to use CRUD SQL commands rather use SQLite3 as storage.

Probably store is bad idea, I agree Lawnchair is better for storing JSON in this case. In rather use Insert into table then, I can retreive using SQL command (i.e Select command).

However, I think getting result using Select statement to get JSON array is a good idea.

My objective is to use my app for field data collection, hence, I need to use the database e.g. my_sqlite3.sqlite in the desktop as well, so lawnchair is the way to go for data portability.

Noli

[ns-1m]

This is very complicated topic. We just wait until UnQL.