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.
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.
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)
// *** 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:
SELECT CAST(json(jsonObj) AS TEXT) AS jtext FROM MyTable
The value delivered by ... Fields[0].AsString is always truncated at 1021 characters.
WorkaroundI have to store the original json value in a TEXT field in the table.
CREATE TABLE MyTable (
id INTEGER PRIMARY KEY AUTOINCREMENT,
jsonObj JSON,
jsonObjText TEXT
)
Now when I retrieve the value with
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:
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;
Recent
