From mboxrd@z Thu Jan 1 00:00:00 1970 From: Robert Klein Subject: Re: A strange problem with org-babel and SQLite Date: Sun, 9 Sep 2018 14:36:22 +0200 Message-ID: <20180909143622.46baea3c@happy.intern.roklein.de> References: <20180831111725.3aeba880@lt70.mpip-mainz.mpg.de> <20180831132244.6ebcc9df@lt70.mpip-mainz.mpg.de> <20180901142453.312ef164@happy.intern.roklein.de> <20180905085602.18488701@lt70.mpip-mainz.mpg.de> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:56453) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1fyyxB-0000Ee-P5 for emacs-orgmode@gnu.org; Sun, 09 Sep 2018 08:36:38 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1fyyx7-00028U-O7 for emacs-orgmode@gnu.org; Sun, 09 Sep 2018 08:36:37 -0400 Received: from mout.kundenserver.de ([217.72.192.75]:54497) by eggs.gnu.org with esmtps (TLS1.0:DHE_RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1fyyx7-00027P-CQ for emacs-orgmode@gnu.org; Sun, 09 Sep 2018 08:36:33 -0400 In-Reply-To: <20180905085602.18488701@lt70.mpip-mainz.mpg.de> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Sender: "Emacs-orgmode" To: Cecil Westerhof Cc: emacs-orgmode@gnu.org On Wed, 5 Sep 2018 08:56:02 +0200 Robert Klein wrote: > Hi Cecil, >=20 > On Mon, 3 Sep 2018 03:23:17 +0200 > Cecil Westerhof wrote: >=20 > > 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. > >=20 > > - Jamie Paolinett", > > "Hoe gebruik jij je verbeelding om > > je mogelijkheden te vergroten?" > > ]'), > > (2, "Second record.") > > ; > > SELECT * > > FROM quotes > > ; > > #+END_SRC > >=20 > > When I put a JSON field in the quote field the parsing goes wrong. > >=20 > > =20 >=20 > 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: >=20 >=20 > - org-babel-execute:sqlite (ob-sqlite, line 60) > calls (for converting the results) >=20 > - org-babel-sqlite-table-or-scalar (ob-sqlite, line 133), > which apparently thinks the result looks like a =E2=80=9Ctrivial table= =E2=80=9D and > calls >=20 > - 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 =E2=80=9Cread=E2=80=9D 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). >=20 >=20 > Line numbers are from Org release_9.1.14-1-g4931fc. >=20 >=20 >=20 >=20 > That's no solution of course. To resolve this, >=20 > - is there a reason to evaluate table cell contents as lisp code? >=20 > If no, >=20 > - don't use org-babel-read (in org-babel-sqlite-table-or-scalar) > - or compare =E2=80=9C(org-babel-result-cond...)=E2=80=9D code with other= ob-*.el > (ob-sql.el?) and rewrite. >=20 > If yes, >=20 > - is there a way to check if a string is correct lisp code before > calling =E2=80=9Cread=E2=80=9D? >=20 >=20 >=20 > In the =E2=80=9Cyes=E2=80=9D case, there's still the issue of JSON being = possibly > detected as =E2=80=9Ccorrect=E2=80=9D lisp code (e.g. ["alfa"]). >=20 >=20 > In your case, if you haven't invested too much in the dependency on > JSON, you might want to redesign the database, e.g.=20 >=20 >=20 > 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' > ); >=20 >=20 >=20 >=20 > Best regards > Robert Hi Cecil, could you try to put the following code in your .emacs _after_ =E2=80=9Corg-babel-do-load-languages=E2=80=9D 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 \"(\", \"\\=3D'\", \"\\=3D`\" 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=3D cell "*this*"))) (eval (read cell)) (if (and (not inhibit-lisp-eval) (string=3D (substring cell 0 1) "\"")) (read cell) (progn (set-text-properties 0 (length cell) nil cell) cell)))) cell)) (This should work for Emacs 25.x) Best regards Robert