From mboxrd@z Thu Jan 1 00:00:00 1970 From: Robert Klein Subject: Re: A strange problem with org-babel and SQLite Date: Wed, 5 Sep 2018 08:56:02 +0200 Message-ID: <20180905085602.18488701@lt70.mpip-mainz.mpg.de> References: <20180831111725.3aeba880@lt70.mpip-mainz.mpg.de> <20180831132244.6ebcc9df@lt70.mpip-mainz.mpg.de> <20180901142453.312ef164@happy.intern.roklein.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]:56476) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1fxRje-0006YQ-Ge for emacs-orgmode@gnu.org; Wed, 05 Sep 2018 02:56:19 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1fxRjZ-00028e-8R for emacs-orgmode@gnu.org; Wed, 05 Sep 2018 02:56:18 -0400 Received: from mout.kundenserver.de ([212.227.126.130]:58928) by eggs.gnu.org with esmtps (TLS1.0:DHE_RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1fxRjX-00025o-Qe for emacs-orgmode@gnu.org; Wed, 05 Sep 2018 02:56:12 -0400 In-Reply-To: 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 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. >=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 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 =E2=80=9Ctrivial table=E2= =80=9D 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 =E2=80=9Cread=E2=80=9D in line 29= 27 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 =E2=80=9C(org-babel-result-cond...)=E2=80=9D code with other o= b-*.el (ob-sql.el?) and rewrite. If yes, - is there a way to check if a string is correct lisp code before calling =E2=80=9Cread=E2=80=9D? In the =E2=80=9Cyes=E2=80=9D case, there's still the issue of JSON being po= ssibly detected as =E2=80=9Ccorrect=E2=80=9D 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.=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' ); Best regards Robert > 2018-09-03 3:09 GMT+02:00 Cecil Westerhof : >=20 > > 2018-09-01 14:24 GMT+02:00 Robert Klein : > > =20 > >> Hi Cecil, > >> > >> On Sat, 1 Sep 2018 11:12:57 +0200 > >> Cecil Westerhof wrote: > >> =20 > >> > 2018-08-31 13:22 GMT+02:00 Robert Klein : > >> > =20 > >> > > On Fri, 31 Aug 2018 12:24:33 +0200 > >> > > Cecil Westerhof wrote: > >> > > =20 > >> > > > 2018-08-31 11:17 GMT+02:00 Robert Klein : > >> > > > =20 > >> > > > > Hi Cecil, > >> > > > > > >> > > > > On Fri, 31 Aug 2018 10:47:50 +0200 > >> > > > > Cecil Westerhof wrote: > >> > > > > =20 > >> > > > > > I have a strange problem with org-babel and SQLite. > >> > > > > > > >> > > > > > I have a database that is created with: > >> > > > > > CREATE TABLE "quotes" ( > >> > > > > > quoteID TEXT PRIMARY KEY, > >> > > > > > quote TEXT NOT NULL UNIQUE, > >> > > > > > lastUsed TEXT, > >> > > > > > totalUsed INT DEFAULT 'unused' > >> > > > > > ) > >> > > > > > > >> > > > > > When using: > >> > > > > > #+BEGIN_SRC sqlite :db > >> > > > > > ~/Twitter/twitter.sqlite :colnames yes SELECT lastUsed > >> > > > > > , totalUsed > >> > > > > > FROM quotes > >> > > > > > ORDER BY lastused ASC > >> > > > > > , totalUsed DESC > >> > > > > > LIMIT 40 > >> > > > > > #+END_SRC > >> > > > > > > >> > > > > > Everything is fine. But when I use (add the quote field > >> > > > > > in the select): #+BEGIN_SRC sqlite :db > >> > > > > > ~/Twitter/twitter.sqlite :colnames yes SELECT quote > >> > > > > > , lastUsed > >> > > > > > , totalUsed > >> > > > > > FROM quotes > >> > > > > > ORDER BY lastused ASC > >> > > > > > , totalUsed DESC > >> > > > > > LIMIT 40 > >> > > > > > #+END_SRC > >> > > > > > > >> > > > > > I get: > >> > > > > > executing Sqlite code block... > >> > > > > > Wrote /tmp/babel-27920y_/ob-input-2792BTG > >> > > > > > org-babel-read: End of file during parsing > >> > > > > > > >> > > > > > What could be the problem? > >> > > > > > =20 > >> > > > > > >> > > > > does it work outside of org/babel/emacs, that is, when you > >> > > > > use the query in a command line sqlite session, does it > >> > > > > work? =E2=80=9Cquote=E2=80=9D is also a function in sqlite, so= this might > >> > > > > be your issue. =20 > >> > > > > >> > > > Yes, in sqlite3 and sqlitebrowser it works without problems. > >> > > > In org-babel even 'SELECT *' goes wrong. > >> > > > =20 > >> > > > >> > > I can't reproduce the issue, it works for me. What are your > >> > > org-mode and Emacs versions? > >> > > =20 > >> > > >> > GNU Emacs 25.1.1 (x86_64-pc-linux-gnu, GTK+ Version 3.22.11) of > >> > 2017-09-15, modified by Debian > >> > Org-mode version 8.2.10 (release_8.2.10 > >> > @ /usr/share/emacs/25.1/lisp/org/) > >> > > >> > =20 > >> > > > >> > > Can you provide an ECM (Example, complete, minimal) org-mode > >> > > setup / Emacs initialization file? > >> > > =20 > >> > > >> > (add-hook 'org-babel-after-execute-hook 'bh/display-inline-images > >> > 'append) > >> > > >> > ; Make babel results blocks lowercase > >> > (setq org-babel-results-keyword "results") > >> > > >> > (defun bh/display-inline-images () > >> > (condition-case nil > >> > (org-display-inline-images) > >> > (error nil))) > >> > > >> > (org-babel-do-load-languages > >> > (quote org-babel-load-languages) > >> > (quote ((emacs-lisp . t) > >> > (sqlite . t) > >> > (dot . t) > >> > (ditaa . t) > >> > (R . t) > >> > (python . t) > >> > (ruby . t) > >> > (gnuplot . t) > >> > (clojure . t) > >> > (sh . t) > >> > (ledger . t) > >> > (org . t) > >> > (plantuml . t) > >> > (latex . t)))) > >> > > >> > ; Do not prompt to confirm evaluation > >> > ; This may be dangerous - make sure you understand the > >> > consequences ; of setting this -- see the docstring for details > >> > (setq org-confirm-babel-evaluate nil) > >> > > >> > ; Use fundamental mode when editing plantuml blocks with C-c ' > >> > (add-to-list 'org-src-lang-modes (quote ("plantuml" . > >> > fundamental))) > >> > > >> > Is this what you need, or do you need more? > >> > =20 > >> > >> sorry, I still can't reproduce the issue, even using the same stock > >> Emacs and org-mode from Debian 9 as you do. > >> > >> Could you _attach_ the emacs init file and the org-mode file which > >> shows the issue? (so I can simply start =E2=80=9Cemacs -Q -l sqlite.e= macs > >> sqlite.org=E2=80=9D and then press C-c C-c inside the sqlite code to s= ee > >> the issue? > >> =20 > > > > It is quite strange. It looks like it has something to do with the > > database itself, because the following works: > > #+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 > > (1, "First record."), > > (2, "Second record.") > > ; > > SELECT * > > FROM quotes > > ; > > #+END_SRC > > > > The create is just copied from the original database. > > > > The following works: > > #+BEGIN_SRC sqlite :db ~/testingOrgBabel.sqlite :colnames yes > > SELECT * > > FROM quotes > > LIMIT 2 > > ; > > #+END_SRC > > > > > > But this does not work: > > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > SELECT * > > FROM quotes > > LIMIT 2 > > ; > > #+END_SRC > > > > > > Weird indeed. > > > > > > > > What I also tried: > > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > CREATE TABLE "quotes2" ( > > quoteID TEXT PRIMARY KEY, > > quote TEXT NOT NULL UNIQUE, > > lastUsed TEXT, > > totalUsed INT DEFAULT 'unused' > > ) > > #+END_SRC > > > > > > Then I can do: > > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > SELECT * > > FROM quotes2 > > #+END_SRC > > > > But when I do: > > #+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes > > INSERT INTO quotes2 > > SELECT * from quotes > > #+END_SRC > > > > the select does not work any-more. > > > > But the select from the command-line tool works without a problem. > > > > -- > > Cecil Westerhof > > =20 >=20 >=20 >=20