* A strange problem with org-babel and SQLite
@ 2018-08-31 8:47 Cecil Westerhof
2018-08-31 9:17 ` Robert Klein
0 siblings, 1 reply; 13+ messages in thread
From: Cecil Westerhof @ 2018-08-31 8:47 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 1024 bytes --]
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?
--
Cecil Westerhof
[-- Attachment #2: Type: text/html, Size: 1523 bytes --]
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
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
0 siblings, 1 reply; 13+ messages in thread
From: Robert Klein @ 2018-08-31 9:17 UTC (permalink / raw)
To: emacs-orgmode
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.
Best regards
Robert
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-08-31 9:17 ` Robert Klein
@ 2018-08-31 10:24 ` Cecil Westerhof
2018-08-31 11:22 ` Robert Klein
0 siblings, 1 reply; 13+ messages in thread
From: Cecil Westerhof @ 2018-08-31 10:24 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 1695 bytes --]
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.
--
Cecil Westerhof
[-- Attachment #2: Type: text/html, Size: 2700 bytes --]
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-08-31 10:24 ` Cecil Westerhof
@ 2018-08-31 11:22 ` Robert Klein
2018-09-01 9:12 ` Cecil Westerhof
0 siblings, 1 reply; 13+ messages in thread
From: Robert Klein @ 2018-08-31 11:22 UTC (permalink / raw)
To: emacs-orgmode
Hi Cecil,
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?
Can you provide an ECM (Example, complete, minimal) org-mode setup /
Emacs initialization file?
Best regards
Robert
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-08-31 11:22 ` Robert Klein
@ 2018-09-01 9:12 ` Cecil Westerhof
2018-09-01 12:24 ` Robert Klein
0 siblings, 1 reply; 13+ messages in thread
From: Cecil Westerhof @ 2018-09-01 9:12 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 3495 bytes --]
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?
--
Cecil Westerhof
[-- Attachment #2: Type: text/html, Size: 5514 bytes --]
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-09-01 9:12 ` Cecil Westerhof
@ 2018-09-01 12:24 ` Robert Klein
2018-09-02 13:22 ` Cecil Westerhof
2018-09-03 1:09 ` Cecil Westerhof
0 siblings, 2 replies; 13+ messages in thread
From: Robert Klein @ 2018-09-01 12:24 UTC (permalink / raw)
To: emacs-orgmode
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?
Best regards
Robert
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
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
1 sibling, 1 reply; 13+ messages in thread
From: Cecil Westerhof @ 2018-09-02 13:22 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 4574 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?
>
My emacs init has grown over the years and it includes a lot of others. I
should try to create
a clean one.
--
Cecil Westerhof
[-- Attachment #2: Type: text/html, Size: 7095 bytes --]
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-09-02 13:22 ` Cecil Westerhof
@ 2018-09-03 0:19 ` Cecil Westerhof
0 siblings, 0 replies; 13+ messages in thread
From: Cecil Westerhof @ 2018-09-03 0:19 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 4993 bytes --]
2018-09-02 15:22 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?
>>
>
> My emacs init has grown over the years and it includes a lot of others. I
> should try to create
>
> a clean one.
>
I think I am almost there. Is it possible to create a memory database with
org-babel instead of a file based
one. That would make more transparent I think.
>
> --
> Cecil Westerhof
>
--
Cecil Westerhof
[-- Attachment #2: Type: text/html, Size: 8125 bytes --]
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-09-01 12:24 ` Robert Klein
2018-09-02 13:22 ` Cecil Westerhof
@ 2018-09-03 1:09 ` Cecil Westerhof
2018-09-03 1:23 ` Cecil Westerhof
1 sibling, 1 reply; 13+ messages in thread
From: Cecil Westerhof @ 2018-09-03 1:09 UTC (permalink / raw)
To: emacs-orgmode
[-- 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 --]
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-09-03 1:09 ` Cecil Westerhof
@ 2018-09-03 1:23 ` Cecil Westerhof
2018-09-05 6:56 ` Robert Klein
0 siblings, 1 reply; 13+ messages in thread
From: Cecil Westerhof @ 2018-09-03 1:23 UTC (permalink / raw)
To: emacs-orgmode
[-- 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 --]
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-09-03 1:23 ` Cecil Westerhof
@ 2018-09-05 6:56 ` Robert Klein
2018-09-09 12:36 ` Robert Klein
0 siblings, 1 reply; 13+ messages in thread
From: Robert Klein @ 2018-09-05 6:56 UTC (permalink / raw)
To: Cecil Westerhof; +Cc: emacs-orgmode
Hi Cecil,
On Mon, 3 Sep 2018 03:23:17 +0200
Cecil Westerhof <cldwesterhof@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 <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
> >
>
>
>
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-09-05 6:56 ` Robert Klein
@ 2018-09-09 12:36 ` Robert Klein
2018-09-10 7:05 ` Cecil Westerhof
0 siblings, 1 reply; 13+ messages in thread
From: Robert Klein @ 2018-09-09 12:36 UTC (permalink / raw)
To: Cecil Westerhof; +Cc: emacs-orgmode
On Wed, 5 Sep 2018 08:56:02 +0200
Robert Klein <roklein@roklein.de> wrote:
> Hi Cecil,
>
> On Mon, 3 Sep 2018 03:23:17 +0200
> Cecil Westerhof <cldwesterhof@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
Hi Cecil,
could you try to put the following code in your .emacs _after_
“org-babel-do-load-languages” for ob-sqlite?
(defun org-babel-read (cell &optional inhibit-lisp-eval)
"Convert the string value of CELL to a number if appropriate.
Otherwise if cell looks like lisp (meaning it starts with a
\"(\", \"\\='\", \"\\=`\" or a \"[\") then read it as lisp,
otherwise return it unmodified as a string. Optional argument
NO-LISP-EVAL inhibits lisp evaluation for situations in which is
it not appropriate."
(if (and (stringp cell) (not (equal cell "")))
(or (org-babel-number-p cell)
(if (and (not inhibit-lisp-eval)
(or (member (substring cell 0 1) '("(" "'" "`" "["))
(string= cell "*this*")))
(eval (read cell))
(if (and (not inhibit-lisp-eval)
(string= (substring cell 0 1) "\""))
(read cell)
(progn (set-text-properties 0 (length cell) nil cell)
cell)))) cell))
(This should work for Emacs 25.x)
Best regards
Robert
^ permalink raw reply [flat|nested] 13+ messages in thread
* Re: A strange problem with org-babel and SQLite
2018-09-09 12:36 ` Robert Klein
@ 2018-09-10 7:05 ` Cecil Westerhof
0 siblings, 0 replies; 13+ messages in thread
From: Cecil Westerhof @ 2018-09-10 7:05 UTC (permalink / raw)
To: emacs-orgmode
[-- Attachment #1: Type: text/plain, Size: 9620 bytes --]
2018-09-09 14:36 GMT+02:00 Robert Klein <roklein@roklein.de>:
> On Wed, 5 Sep 2018 08:56:02 +0200
> Robert Klein <roklein@roklein.de> wrote:
>
> > Hi Cecil,
> >
> > On Mon, 3 Sep 2018 03:23:17 +0200
> > Cecil Westerhof <cldwesterhof@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
>
> Hi Cecil,
>
> could you try to put the following code in your .emacs _after_
> “org-babel-do-load-languages” for ob-sqlite?
>
> (defun org-babel-read (cell &optional inhibit-lisp-eval)
> "Convert the string value of CELL to a number if appropriate.
> Otherwise if cell looks like lisp (meaning it starts with a
> \"(\", \"\\='\", \"\\=`\" or a \"[\") then read it as lisp,
> otherwise return it unmodified as a string. Optional argument
> NO-LISP-EVAL inhibits lisp evaluation for situations in which is
> it not appropriate."
> (if (and (stringp cell) (not (equal cell "")))
> (or (org-babel-number-p cell)
> (if (and (not inhibit-lisp-eval)
> (or (member (substring cell 0 1) '("(" "'" "`" "["))
> (string= cell "*this*")))
> (eval (read cell))
> (if (and (not inhibit-lisp-eval)
> (string= (substring cell 0 1) "\""))
> (read cell)
> (progn (set-text-properties 0 (length cell) nil cell)
> cell)))) cell))
>
>
> (This should work for Emacs 25.x)
>
It goes quit a way in the right direction.
When I execute:
SELECT quote
, lastUsed
, totalUsed
FROM quotes
WHERE lastUsed = DATE('now')
I get:
| quote | lastUsed | totalUsed |
|-----------------------------------+------------+-----------|
| "[ | | |
| The thought manifests the word; | | |
| The word manifests the deed; | | |
| The deed develops into habit; | | |
| And habit hardens into character; | | |
| So watch the thought and | | |
| its ways with care. | | |
| | | |
| - Juan Mascaro"" | | |
| Je gedachten zijn | | |
| de grondslag van je: | | |
| daden | | |
| gewoontes en | | |
| karakter. | | |
| | | |
| Waak over je gedachten."" | | |
| ]" | 2018-09-10 | 5 |
Is not completely correct, because the real quote is:
[
"The thought manifests the word;
The word manifests the deed;
The deed develops into habit;
And habit hardens into character;
So watch the thought and
its ways with care.
- Juan Mascaro",
"Je gedachten zijn
de grondslag van je:
daden,
gewoontes en
karakter.
Waak over je gedachten."
]
But that is really not a problem.
When I execute:
SELECT *
FROM quotes
WHERE lastUsed = DATE('now')
I get:
| quoteID | quote | lastUsed | totalUsed |
|--------------------------------------+------------+----------+-----------|
| 55d0b51c-7b46-44f8-9e67-ebe59c63ca34 | "[ | | |
| The thought manifests the word; | | | |
| The word manifests the deed; | | | |
| The deed develops into habit; | | | |
| And habit hardens into character; | | | |
| So watch the thought and | | | |
| its ways with care. | | | |
| | | | |
| - Juan Mascaro"" | | | |
| Je gedachten zijn | | | |
| de grondslag van je: | | | |
| daden | | | |
| gewoontes en | | | |
| karakter. | | | |
| | | | |
| Waak over je gedachten."" | | | |
| ]" | 2018-09-10 | 5 | |
So everything after the first line of quote goes to quoteID. And the other
fields go a to the previous field.
At the moment that is not a query I need to do, and if I need everything I
could rewrite it as:
SELECT quote
, quoteID
, lastUsed
, totalUsed
FROM quotes
WHERE lastUsed = DATE('now')
and then I get:
| quote | quoteID
| lastUsed | totalUsed |
|-----------------------------------+--------------------------------------+------------+-----------|
| "[ |
| | |
| The thought manifests the word; |
| | |
| The word manifests the deed; |
| | |
| The deed develops into habit; |
| | |
| And habit hardens into character; |
| | |
| So watch the thought and |
| | |
| its ways with care. |
| | |
| |
| | |
| - Juan Mascaro"" |
| | |
| Je gedachten zijn |
| | |
| de grondslag van je: |
| | |
| daden |
| | |
| gewoontes en |
| | |
| karakter. |
| | |
| |
| | |
| Waak over je gedachten."" |
| | |
| ]" | 55d0b51c-7b46-44f8-9e67-ebe59c63ca34
| 2018-09-10 | 5 |
I would get a problem when I have two fields that contain JSON data, but I
think it is very unlikely I will get in such a situation.
--
Cecil Westerhof
[-- Attachment #2: Type: text/html, Size: 18093 bytes --]
^ permalink raw reply [flat|nested] 13+ messages in thread
end of thread, other threads:[~2018-09-10 7:11 UTC | newest]
Thread overview: 13+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
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
2018-09-05 6:56 ` Robert Klein
2018-09-09 12:36 ` Robert Klein
2018-09-10 7:05 ` Cecil Westerhof
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).