A strange problem with org-babel and SQLite

emacs-orgmode@gnu.org archives
 help / color / mirror / code / Atom feed

* 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).