2018-09-09 14:36 GMT+02:00 Robert Klein : > On Wed, 5 Sep 2018 08:56:02 +0200 > Robert Klein wrote: > > > Hi Cecil, > > > > On Mon, 3 Sep 2018 03:23:17 +0200 > > Cecil Westerhof wrote: > > > > > It has to do with the data. With the following I can reproduce it: > > > #+BEGIN_SRC sqlite :db ~/testingOrgBabel.sqlite :colnames yes > > > DROP TABLE IF EXISTS quotes > > > ; > > > CREATE TABLE "quotes" ( > > > quoteID TEXT PRIMARY KEY, > > > quote TEXT NOT NULL UNIQUE, > > > lastUsed TEXT, > > > totalUsed INT DEFAULT 'unused' > > > ) > > > ; > > > INSERT INTO quotes > > > (quoteID, quote) > > > VALUES > > > ("1230FCF5-B25D-4087-88A4-41DF3AC353DA", '[ > > > "Limitations live only in our minds. > > > But if we use our imaginations, > > > our possibilities become limitless. > > > > > > - Jamie Paolinett", > > > "Hoe gebruik jij je verbeelding om > > > je mogelijkheden te vergroten?" > > > ]'), > > > (2, "Second record.") > > > ; > > > SELECT * > > > FROM quotes > > > ; > > > #+END_SRC > > > > > > When I put a JSON field in the quote field the parsing goes wrong. > > > > > > > > > > umm, yes. Actually what seems to happen is that emacs tries to > > evaluate the JSON part as emacs lisp code, in this case an array. In > > detail, I think, this happens: > > > > > > - org-babel-execute:sqlite (ob-sqlite, line 60) > > calls (for converting the results) > > > > - org-babel-sqlite-table-or-scalar (ob-sqlite, line 133), > > which apparently thinks the result looks like a “trivial table” and > > calls > > > > - org-babel-read (ob-core.el, line 2912), > > which detects the JSON string (begins with a "[ ) as lisp and tries > > to evaluate the lisp form. The call to “read” in line 2927 then > > fails, because there is no closing ] (only the contents on one cell > > is sent to org-babel-read; note, there are no multi-line cells in > > org tables). > > > > > > Line numbers are from Org release_9.1.14-1-g4931fc. > > > > > > > > > > That's no solution of course. To resolve this, > > > > - is there a reason to evaluate table cell contents as lisp code? > > > > If no, > > > > - don't use org-babel-read (in org-babel-sqlite-table-or-scalar) > > - or compare “(org-babel-result-cond...)” code with other ob-*.el > > (ob-sql.el?) and rewrite. > > > > If yes, > > > > - is there a way to check if a string is correct lisp code before > > calling “read”? > > > > > > > > In the “yes” case, there's still the issue of JSON being possibly > > detected as “correct” lisp code (e.g. ["alfa"]). > > > > > > In your case, if you haven't invested too much in the dependency on > > JSON, you might want to redesign the database, e.g. > > > > > > CREATE TABLE "quotes" ( > > quoteID TEXT PRIMARY KEY, > > quote_en TEXT NOT NULL UNIQUE, > > quote_nl TEXT NOT NULL UNIQUE, > > lastUsed TEXT, > > totalUsed INT DEFAULT 'unused' > > ); > > > > > > > > > > Best regards > > Robert > > Hi Cecil, > > could you try to put the following code in your .emacs _after_ > “org-babel-do-load-languages” for ob-sqlite? > > (defun org-babel-read (cell &optional inhibit-lisp-eval) > "Convert the string value of CELL to a number if appropriate. > Otherwise if cell looks like lisp (meaning it starts with a > \"(\", \"\\='\", \"\\=`\" or a \"[\") then read it as lisp, > otherwise return it unmodified as a string. Optional argument > NO-LISP-EVAL inhibits lisp evaluation for situations in which is > it not appropriate." > (if (and (stringp cell) (not (equal cell ""))) > (or (org-babel-number-p cell) > (if (and (not inhibit-lisp-eval) > (or (member (substring cell 0 1) '("(" "'" "`" "[")) > (string= cell "*this*"))) > (eval (read cell)) > (if (and (not inhibit-lisp-eval) > (string= (substring cell 0 1) "\"")) > (read cell) > (progn (set-text-properties 0 (length cell) nil cell) > cell)))) cell)) > > > (This should work for Emacs 25.x) > It goes quit a way in the right direction. When I execute: SELECT quote , lastUsed , totalUsed FROM quotes WHERE lastUsed = DATE('now') I get: | quote | lastUsed | totalUsed | |-----------------------------------+------------+-----------| | "[ | | | | The thought manifests the word; | | | | The word manifests the deed; | | | | The deed develops into habit; | | | | And habit hardens into character; | | | | So watch the thought and | | | | its ways with care. | | | | | | | | - Juan Mascaro"" | | | | Je gedachten zijn | | | | de grondslag van je: | | | | daden | | | | gewoontes en | | | | karakter. | | | | | | | | Waak over je gedachten."" | | | | ]" | 2018-09-10 | 5 | Is not completely correct, because the real quote is: [ "The thought manifests the word; The word manifests the deed; The deed develops into habit; And habit hardens into character; So watch the thought and its ways with care. - Juan Mascaro", "Je gedachten zijn de grondslag van je: daden, gewoontes en karakter. Waak over je gedachten." ] But that is really not a problem. When I execute: SELECT * FROM quotes WHERE lastUsed = DATE('now') I get: | quoteID | quote | lastUsed | totalUsed | |--------------------------------------+------------+----------+-----------| | 55d0b51c-7b46-44f8-9e67-ebe59c63ca34 | "[ | | | | The thought manifests the word; | | | | | The word manifests the deed; | | | | | The deed develops into habit; | | | | | And habit hardens into character; | | | | | So watch the thought and | | | | | its ways with care. | | | | | | | | | | - Juan Mascaro"" | | | | | Je gedachten zijn | | | | | de grondslag van je: | | | | | daden | | | | | gewoontes en | | | | | karakter. | | | | | | | | | | Waak over je gedachten."" | | | | | ]" | 2018-09-10 | 5 | | So everything after the first line of quote goes to quoteID. And the other fields go a to the previous field. At the moment that is not a query I need to do, and if I need everything I could rewrite it as: SELECT quote , quoteID , lastUsed , totalUsed FROM quotes WHERE lastUsed = DATE('now') and then I get: | quote | quoteID | lastUsed | totalUsed | |-----------------------------------+--------------------------------------+------------+-----------| | "[ | | | | | The thought manifests the word; | | | | | The word manifests the deed; | | | | | The deed develops into habit; | | | | | And habit hardens into character; | | | | | So watch the thought and | | | | | its ways with care. | | | | | | | | | | - Juan Mascaro"" | | | | | Je gedachten zijn | | | | | de grondslag van je: | | | | | daden | | | | | gewoontes en | | | | | karakter. | | | | | | | | | | Waak over je gedachten."" | | | | | ]" | 55d0b51c-7b46-44f8-9e67-ebe59c63ca34 | 2018-09-10 | 5 | I would get a problem when I have two fields that contain JSON data, but I think it is very unlikely I will get in such a situation. -- Cecil Westerhof