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:23:17 +0200 [thread overview]
Message-ID: <CAG-LmmDvt0B8dknUw5bRUGLEEiyuGdXsw9GQFKNU2iP1bCO0nA@mail.gmail.com> (raw)
In-Reply-To: <CAG-LmmANEVboeoWHYRb0+=OOcGQWOgkDr1UOtyVHQ4dioYVNyA@mail.gmail.com>
[-- Attachment #1: Type: text/plain, Size: 7173 bytes --]
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.
2018-09-03 3:09 GMT+02:00 Cecil Westerhof <cldwesterhof@gmail.com>:
> 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
>
--
Cecil Westerhof
[-- Attachment #2: Type: text/html, Size: 10929 bytes --]
next prev parent reply other threads:[~2018-09-03 1:28 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
2018-09-03 1:23 ` Cecil Westerhof [this message]
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-LmmDvt0B8dknUw5bRUGLEEiyuGdXsw9GQFKNU2iP1bCO0nA@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).