emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed
From: Cecil Westerhof <cldwesterhof@gmail.com>
To: emacs-orgmode@gnu.org
Subject: Re: A strange problem with org-babel and SQLite
Date: Mon, 3 Sep 2018 03:09:26 +0200	[thread overview]
Message-ID: <CAG-LmmANEVboeoWHYRb0+=OOcGQWOgkDr1UOtyVHQ4dioYVNyA@mail.gmail.com> (raw)
In-Reply-To: <20180901142453.312ef164@happy.intern.roklein.de>

[-- Attachment #1: Type: text/plain, Size: 6007 bytes --]

2018-09-01 14:24 GMT+02:00 Robert Klein <roklein@roklein.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 <cldwesterhof@gmail.com> wrote:
> > >
> > > > 2018-08-31 11:17 GMT+02:00 Robert Klein <roklein@roklein.de>:
> > > >
> > > > > Hi Cecil,
> > > > >
> > > > > On Fri, 31 Aug 2018 10:47:50 +0200
> > > > > Cecil Westerhof <cldwesterhof@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

[-- Attachment #2: Type: text/html, Size: 9768 bytes --]

  parent reply	other threads:[~2018-09-03  1:09 UTC|newest]

Thread overview: 13+ messages / expand[flat|nested]  mbox.gz  Atom feed  top
2018-08-31  8:47 A strange problem with org-babel and SQLite Cecil Westerhof
2018-08-31  9:17 ` Robert Klein
2018-08-31 10:24   ` Cecil Westerhof
2018-08-31 11:22     ` Robert Klein
2018-09-01  9:12       ` Cecil Westerhof
2018-09-01 12:24         ` Robert Klein
2018-09-02 13:22           ` Cecil Westerhof
2018-09-03  0:19             ` Cecil Westerhof
2018-09-03  1:09           ` Cecil Westerhof [this message]
2018-09-03  1:23             ` Cecil Westerhof
2018-09-05  6:56               ` Robert Klein
2018-09-09 12:36                 ` Robert Klein
2018-09-10  7:05                   ` Cecil Westerhof

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

  Avoid top-posting and favor interleaved quoting:
  https://en.wikipedia.org/wiki/Posting_style#Interleaved_style

  List information: https://www.orgmode.org/

* Reply using the --to, --cc, and --in-reply-to
  switches of git-send-email(1):

  git send-email \
    --in-reply-to='CAG-LmmANEVboeoWHYRb0+=OOcGQWOgkDr1UOtyVHQ4dioYVNyA@mail.gmail.com' \
    --to=cldwesterhof@gmail.com \
    --cc=emacs-orgmode@gnu.org \
    /path/to/YOUR_REPLY

  https://kernel.org/pub/software/scm/git/docs/git-send-email.html

* If your mail client supports setting the In-Reply-To header
  via mailto: links, try the mailto: link
Be sure your reply has a Subject: header at the top and a blank line before the message body.
Code repositories for project(s) associated with this public inbox

	https://git.savannah.gnu.org/cgit/emacs/org-mode.git

This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).