JSON with SQLite. SELECT json(jfield) FROM MYTABLE gives string of 1021 chars

Forum > Databases

(1/1)

strubri:
I came across an interesting situation. Reporting it here to help anyone who may have stumbled on this problem.

Lets say we have a table in an SQLite database with a JSON field.

--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE MyTable ( id INTEGER PRIMARY KEY AUTOINCREMENT, jsonObj JSON )
We store values into field jsonObj with JSONB() so that it is stored in a binary format for better indexing/updating. The SQLite documentation for this is very clear as is the functionality.

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure saveJSONObj(constref _jsonObj: TJSONObject);const   Q = 'INSERT INTO MyTable(jsonObj) ' + sLinebreak + 'VALUES (jsonb(:jsonObj))'; // note that the SQLite function jsonb() is called before storing the value in the field var _qry: TSQLQuery;begin _qry := dbModule.newQuery(Q); // This is a helper function that instantiates a TSQLQuery object, // assigns the current open SQLite Connection and initializes the Transaction // It also sets the SQL property to the insert command in "Q" try _qry.Params[0].AsString := _jsonObj.FormatJSON(AsJSONFormat); _qry.ExecSQL; finally _qry.Free; end;end;
It is pretty straight foward up to this point. However when we want to retrieve the JSON from the database, the following code fails because the Fields[] object only returns a string that is maximum 1021 characters (in Lazarus 2.0.12, which i have to maintain an older code base)

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---// *** THIS FAILS ***function getJSONObj(_ID: int64): TJSONObject;const Q = 'SELECT json(jsonObj) FROM MyTable WHERE id = :id';var _qry: TSQLQuery;   function parseJSON(const _json: string): TJSONData; begin with TJSONParser.Create(_json, [joUTF8]) do // in unit jsonparser begin try try Result := Parse; except Result:= nil; end; finally Free; // the TJSONParser object end; end; end; begin Result := nil; _qry := dbModule.newQuery(Q); // This is a helper function that instantiates a TSQLQuery object, // assigns the currenty open SQLite Connection and initializes the Transaction // It also sets the SQL property to the insert command in "Q" try _qry.Params[0].AsLargeInt := _ID; _qry.Open; if not _qry.Fields[0].IsNull then begin _jsonStr := _qry.Fields[0].AsString; // this contains only 1021 characters. JSON parsing fails for large objects Result := TJSONObject(parseJSON(_jsonStr)); end; finally _qry.Free; end;end;
At this point I do no know where the error lies. I tried many different things to get this to work including things like:

--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT CAST(json(jsonObj) AS TEXT) AS jtext FROM MyTable
The value delivered by ... Fields[0].AsString is always truncated at 1021 characters.

Workaround
I have to store the original json value in a TEXT field in the table.

--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE MyTable ( id INTEGER PRIMARY KEY AUTOINCREMENT, jsonObj JSON, jsonObjText TEXT )
Now when I retrieve the value with

--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---SELECT jsonObjText FROM MyTable the json object is parsed correctly.

I assume it is because TSQLQuery cannot correctly identify that the field returned by the SELECT statement is in fact a TEXT field (ftMemo). The value is shown correctly within DB Browser (for SQLite). This could be driver issue, it could be a Lazarus LCL issue.

Of course, this means that any updates to the table must happen on both fields. You could use the same parameter name in the prepared SQL statement so that the pascal code only needs to assign the valid JSON text to only one ParameterByName(). Something like this:

--- Code: Pascal [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---procedure saveJSONObj(const _id: int64; constref _jsonObj: TJSONObject);const Q = 'UPDATE MyTable ' + sLinebreak + 'SET jsonObj = jsonb(:jsonObj), ' + sLinebreak // note that the SQLite function jsonb() is called before storing the value in the field + 'jsonObjText = :jsonObj' + sLinebreak // note that the same parameter name is assigned here. Internally this registers only once in the list of Params + 'WHERE id = :id'; var _qry: TSQLQuery;begin _qry := dbModule.newQuery(Q); // This is a helper function that instantiates a TSQLQuery object, // assigns the currenty open SQLite Connection and initializes the Transaction // It also sets the SQL property to the insert command in "Q" try _qry.ParamByName('jsonObj').AsString := _jsonObj.FormatJSON(AsJSONFormat); _qry.ParamByName('id').AsLargeInt := _id; _qry.ExecSQL; finally _qry.Free; end; end;

Zvoni:
There is no JSON-DataType (!!) in SQLite.
SQLite stores JSON always as TEXT

--- Code: SQL [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---CREATE TABLE MyTable ( id INTEGER PRIMARY KEY AUTOINCREMENT, jsonObj JSON /* <-- Nonsense */)
SQLite only has JSON-Functions and operators

And don't use AUTOINCREMENT for your Primary Key. You don't need it.

EDIT: And since you INSERT/UPDATE a JSONB, use BLOB as storage-Class

strubri:
JSON type was introduced in SQLite recently. Yes it used to that you could only store a JSON value as text before. I don't know how it stores it internally now but they have updated the driver to allow devs to work better with JSON values. I tested this on SQLite Version 3.46.0

I find that the JSON type is better for indexing and sorting within JSON objects and arrays in a SELECT statement. Also when you store a value with JSONB() in a JSON type field the UPDATE statement affects only the json fields that have to be updated. You no longer have to replace the whole JSON field value. The functionality seems to be on par with the native JSON handling in Postgres.

The point of this post was to show that when you have a table with JSON datatype and you want to retrieve the whole JSON field as text inside Lazarus, the TSQLQuery object only returns a string field with 1021 chars.

About the autoincrement for primary key - understood.

Thank you for your feedback.

Navigation

[0] Message Index