From mboxrd@z Thu Jan 1 00:00:00 1970 From: Cecil Westerhof Subject: Re: A strange problem with org-babel and SQLite Date: Mon, 3 Sep 2018 03:09:26 +0200 Message-ID: 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: multipart/alternative; boundary="00000000000054bf0c0574ed3171" Return-path: Received: from eggs.gnu.org ([2001:4830:134:3::10]:55467) by lists.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1fwdMw-0007TS-G4 for emacs-orgmode@gnu.org; Sun, 02 Sep 2018 21:09:32 -0400 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1fwdMu-0001pQ-Mt for emacs-orgmode@gnu.org; Sun, 02 Sep 2018 21:09:30 -0400 Received: from mail-oi0-x233.google.com ([2607:f8b0:4003:c06::233]:45908) by eggs.gnu.org with esmtps (TLS1.0:RSA_AES_128_CBC_SHA1:16) (Exim 4.71) (envelope-from ) id 1fwdMu-0001oz-Dy for emacs-orgmode@gnu.org; Sun, 02 Sep 2018 21:09:28 -0400 Received: by mail-oi0-x233.google.com with SMTP id t68-v6so30832297oie.12 for ; Sun, 02 Sep 2018 18:09:28 -0700 (PDT) In-Reply-To: <20180901142453.312ef164@happy.intern.roklein.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: emacs-orgmode@gnu.org --00000000000054bf0c0574ed3171 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 2018-09-01 14:24 GMT+02:00 Robert Klein : > Hi Cecil, > > On Sat, 1 Sep 2018 11:12:57 +0200 > Cecil Westerhof wrote: > > > 2018-08-31 13:22 GMT+02:00 Robert Klein : > > > > > On Fri, 31 Aug 2018 12:24:33 +0200 > > > Cecil Westerhof wrote: > > > > > > > 2018-08-31 11:17 GMT+02:00 Robert Klein : > > > > > > > > > Hi Cecil, > > > > > > > > > > On Fri, 31 Aug 2018 10:47:50 +0200 > > > > > Cecil Westerhof 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? > > > > > =E2=80=9Cquote=E2=80=9D is also a function in sqlite, so this mig= ht 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 =E2=80=9Cemacs -Q -l sqlite.emac= s > sqlite.org=E2=80=9D 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. --=20 Cecil Westerhof --00000000000054bf0c0574ed3171 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
2018-09-01 14:24 GMT+02:00 Robert Klein <roklein@r= oklein.de>:
Hi Cecil,

On Sat, 1 Sep 2018 11:12:57 +0200
Cecil Westerhof <cldwesterhof@gmail.com> wrote:

> 2018-08-31 13:22 GMT+02:00 Robert Klein <roklein@roklein.de>:
>
> > On Fri, 31 Aug 2018 12:24:33 +0200
> > Cecil Westerhof <cld= westerhof@gmail.com> wrote:
> >=C2=A0
> > > 2018-08-31 11:17 GMT+02:00 Robert Klein <roklein@roklein.de>:
> > >=C2=A0
> > > > Hi Cecil,
> > > >
> > > > On Fri, 31 Aug 2018 10:47:50 +0200
> > > > Cecil Westerhof <cldwesterhof@gmail.com> wrote:
> > > >=C2=A0
> > > > > I have a strange problem with org-babel and SQLite= .
> > > > >
> > > > > I have a database that is created with:
> > > > >=C2=A0 =C2=A0 =C2=A0CREATE TABLE "quotes"= (
> > > > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0quoteID=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0TEXT=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0PRIMAR= Y KEY,
> > > > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0quote=C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0TEXT NOT NULL=C2=A0 UNIQUE,
> > > > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0lastUsed=C2=A0 = =C2=A0 =C2=A0 =C2=A0 TEXT,
> > > > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0totalUsed=C2=A0 = =C2=A0 =C2=A0 =C2=A0INT=C2=A0 DEFAULT 'unused'
> > > > >=C2=A0 =C2=A0 =C2=A0)
> > > > >
> > > > > When using:
> > > > >=C2=A0 =C2=A0 =C2=A0#+BEGIN_SRC sqlite :db ~/Twitte= r/twitter.sqlite :colnames
> > > > > yes SELECT=C2=A0 =C2=A0lastUsed
> > > > >=C2=A0 =C2=A0 =C2=A0,=C2=A0 =C2=A0 =C2=A0 =C2=A0 to= talUsed
> > > > >=C2=A0 =C2=A0 =C2=A0FROM=C2=A0 =C2=A0 =C2=A0quotes<= br> > > > > >=C2=A0 =C2=A0 =C2=A0ORDER BY lastused=C2=A0 ASC
> > > > >=C2=A0 =C2=A0 =C2=A0,=C2=A0 =C2=A0 =C2=A0 =C2=A0 to= talUsed DESC
> > > > >=C2=A0 =C2=A0 =C2=A0LIMIT=C2=A0 =C2=A0 40
> > > > >=C2=A0 =C2=A0 =C2=A0#+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=C2= =A0 =C2=A0quote
> > > > >=C2=A0 =C2=A0 =C2=A0,=C2=A0 =C2=A0 =C2=A0 =C2=A0 la= stUsed
> > > > >=C2=A0 =C2=A0 =C2=A0,=C2=A0 =C2=A0 =C2=A0 =C2=A0 to= talUsed
> > > > >=C2=A0 =C2=A0 =C2=A0FROM=C2=A0 =C2=A0 =C2=A0quotes<= br> > > > > >=C2=A0 =C2=A0 =C2=A0ORDER BY lastused=C2=A0 ASC
> > > > >=C2=A0 =C2=A0 =C2=A0,=C2=A0 =C2=A0 =C2=A0 =C2=A0 to= talUsed DESC
> > > > >=C2=A0 =C2=A0 =C2=A0LIMIT=C2=A0 =C2=A0 40
> > > > >=C2=A0 =C2=A0 =C2=A0#+END_SRC
> > > > >
> > > > > I get:
> > > > >=C2=A0 =C2=A0 =C2=A0executing Sqlite code block...<= br> > > > > >=C2=A0 =C2=A0 =C2=A0Wrote /tmp/babel-27920y_/ob-inp= ut-2792BTG
> > > > >=C2=A0 =C2=A0 =C2=A0org-babel-read: End of file dur= ing parsing
> > > > >
> > > > > What could be the problem?
> > > > >=C2=A0
> > > >
> > > > does it work outside of org/babel/emacs, that is, when = you use
> > > > the query in a command line sqlite session, does it wor= k?
> > > > =E2=80=9Cquote=E2=80=9D is also a function in sqlite, s= o this might be your
> > > > issue.
> > >
> > > Yes, in sqlite3 and sqlitebrowser it works without problems.=
> > > In org-babel even 'SELECT *' goes wrong.
> > >=C2=A0
> >
> > I can't reproduce the issue, it works for me.=C2=A0 What are = your
> > org-mode and Emacs versions?
> >=C2=A0
>
> 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 setu= p /
> > Emacs initialization file?
> >=C2=A0
>
> (add-hook 'org-babel-after-execute-hook 'bh/display-inline-ima= ges
> 'append)
>
> ; Make babel results blocks lowercase
> (setq org-babel-results-keyword "results")
>
> (defun bh/display-inline-images ()
>=C2=A0 =C2=A0(condition-case nil
>=C2=A0 =C2=A0 =C2=A0 =C2=A0(org-display-inline-images)
>=C2=A0 =C2=A0 =C2=A0(error nil)))
>
> (org-babel-do-load-languages
>=C2=A0 (quote org-babel-load-languages)
>=C2=A0 (quote ((emacs-lisp . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (sqlite . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (dot . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (ditaa . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (R . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (python . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (ruby . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (gnuplot . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (clojure . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (sh . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (ledger . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (org . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (plantuml . t)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (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" . fu= ndamental)))
>
> Is this what you need, or do you need more?
>

sorry, I still can't reproduce the issue, even using the sa= me 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?=C2=A0 (so I can simply start =E2=80=9Cemacs -Q -l sqlite.e= macs
sqlite.o= rg=E2=80=9D 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 fol= lowing works:
#+BEGIN_SRC sqlite :db ~/testingOrgBabel.sqlite :colnames = yes
DROP TABLE IF EXISTS quotes
;
CREATE = TABLE "quotes" (
=C2=A0=C2=A0=C2=A0 quoteID=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TEXT=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0 PRIMARY KEY,
=C2=A0=C2=A0=C2=A0 quote=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TEXT NOT NULL=C2=A0 UNIQUE= ,
=C2=A0=C2=A0=C2=A0 lastUsed=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 = TEXT,
=C2=A0=C2=A0=C2=A0 totalUsed=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 I= NT=C2=A0 DEFAULT 'unused'
)
;
INS= ERT INTO quotes
(quoteID, quote)
VALUES
(1, "First record.&qu= ot;),
(2, "Second record.")
;
SELECT *
FROM=C2=A0=C2= =A0 quotes
;
#+END_SRC

The create is = just copied from the original database.

The= following works:
#+BEGIN_SRC sqlite :db ~/testingOrgBabel.sqlite :colna= mes yes
SELECT *
FROM=C2=A0=C2=A0 quotes
LIMIT=C2=A0 2
;
#+E= ND_SRC


But this does not work:
#+BEG= IN_SRC sqlite :db ~/Twitter/twitter.sqlite :colnames yes
SELECT *
FRO= M=C2=A0=C2=A0 quotes
LIMIT=C2=A0 2
;
#+END_SRC


Weird indeed.
=C2=A0


What I also tried:
#+BEGIN_SRC sqlite :db ~/Twitter/twitter.sqlite = :colnames yes
CREATE TABLE "quotes2" (
=C2=A0=C2=A0=C2=A0 q= uoteID=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TEXT=C2=A0=C2=A0=C2= =A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 PRIMARY KEY,
=C2=A0=C2=A0= =C2=A0 quote=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0=C2=A0 TE= XT NOT NULL=C2=A0 UNIQUE,
=C2=A0=C2=A0=C2=A0 lastUsed=C2=A0=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 TEXT,
=C2=A0=C2=A0=C2=A0 totalUsed=C2=A0=C2=A0= =C2=A0=C2=A0=C2=A0=C2=A0 INT=C2=A0 DEFAULT 'unused'
)
#+END_S= RC


Then I can do:
#+BEGIN_SRC sqlite :db ~/Twitter/tw= itter.sqlite :colnames yes
SELECT *
FROM=C2=A0=C2=A0 quotes2
#+END= _SRC

But when I do:
#+BEGIN_SRC sqlite :db ~/Twitter/twit= ter.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
--00000000000054bf0c0574ed3171--