Hi Cecil,
On Mon, 3 Sep 2018 03:23:17 +0200
Cecil Westerhof <cldwester...@gmail.com> 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
> 2018-09-03 3:09 GMT+02:00 Cecil Westerhof <cldwester...@gmail.com>:
>
> > 2018-09-01 14:24 GMT+02:00 Robert Klein <rokl...@roklein.de>:
> >
> >> Hi Cecil,
> >>
> >> On Sat, 1 Sep 2018 11:12:57 +0200
> >> Cecil Westerhof <cldwester...@gmail.com> wrote:
> >>
> >> > 2018-08-31 13:22 GMT+02:00 Robert Klein <rokl...@roklein.de>:
> >> >
> >> > > On Fri, 31 Aug 2018 12:24:33 +0200
> >> > > Cecil Westerhof <cldwester...@gmail.com> wrote:
> >> > >
> >> > > > 2018-08-31 11:17 GMT+02:00 Robert Klein <rokl...@roklein.de>:
> >> > > >
> >> > > > > Hi Cecil,
> >> > > > >
> >> > > > > On Fri, 31 Aug 2018 10:47:50 +0200
> >> > > > > Cecil Westerhof <cldwester...@gmail.com> wrote:
> >> > > > >
> >> > > > > > 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?
> >> > > > > >
> >> > > > >
> >> > > > > does it work outside of org/babel/emacs, that is, when you
> >> > > > > use the query in a command line sqlite session, does it
> >> > > > > work? “quote” is also a function in sqlite, so this might
> >> > > > > be your issue.
> >> > > >
> >> > > > Yes, in sqlite3 and sqlitebrowser it works without problems.
> >> > > > In org-babel even 'SELECT *' goes wrong.
> >> > > >
> >> > >
> >> > > I can't reproduce the issue, it works for me. What are your
> >> > > org-mode and Emacs versions?
> >> > >
> >> >
> >> > 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/)
> >> >
> >> >
> >> > >
> >> > > Can you provide an ECM (Example, complete, minimal) org-mode
> >> > > setup / Emacs initialization file?
> >> > >
> >> >
> >> > (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?
> >> >
> >>
> >> 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 “emacs -Q -l sqlite.emacs
> >> sqlite.org” and then press C-c C-c inside the sqlite code to see
> >> the issue?
> >>
> >
> > 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
> >
>
>
>
