bug with org-babel/sqlite?

bug with org-babel/sqlite?

[Dushyant Juneja]

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

Hi,

Looks like I stumbled upon a bug with org-babel and sqlite. I have the
following in my org file. PFA the bug description (bug.org). The essence is
that sqlite queries work with :results verbatim, but not otherwise. Any
ideas on how to get past this?

This is with emacs 24.5.2, RHEL6.

Dushyant

[-- Attachment #2: bug.org --]
[-- Type: application/octet-stream, Size: 1623 bytes --]

#+TITLE: Bug report
#+PROPERTY: mkdirp yes

* Setup
Need to exec this first in order for sqlite src to be exec'ed.
#+BEGIN_SRC emacs-lisp :results silent
(org-babel-do-load-languages
 'org-babel-load-languages
 '((emacs-lisp . t)
   (sh         . t)
   (sqlite     . t)
   (C          . t)))
#+END_SRC

* CSV files
For a sample, I have the following CSV file:

#+BEGIN_SRC csv :tangle sample1.csv
Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very Large""",,5000.00
1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00
#+END_SRC

* Playing with SQL
Now stat playing with SQL. 

The following snippet works fine:
#+BEGIN_SRC sqlite :db bug.db :exports results :results verbatim
DROP TABLE IF EXISTS cars;
CREATE TABLE cars (Year,Make,Model,Description,Price);
.mode csv
.separator ","
.import sample1.csv cars
SELECT * FROM cars;
#+END_SRC

#+RESULTS:
: Year,Make,Model,Description,Price
: 1997,Ford,E350,"ac, abs, moon",3000.00
: 1999,Chevy,"Venture ""Extended Edition""","",4900.00
: 1999,Chevy,"Venture ""Extended Edition, Very Large""","",5000.00
: 1996,Jeep,"Grand Cherokee","MUST SELL!
: air, moon roof, loaded",4799.00


However, the following fails, just because =:results= is set to default.
# BEGIN_SRC sqlite :db sample.db :exports results :results verbatim
#+BEGIN_SRC sqlite :db sample.db :exports results
DROP TABLE IF EXISTS cars;
CREATE TABLE cars ( Year,Make,Model,Description,Price );
select * from cars;
.mode csv
.import sample1.csv cars
SELECT * FROM cars;
#+END_SRC

Fwd: bug with org-babel/sqlite?

[Kaushal Modi]

[-- Attachment #1.1: Type: text/plain, Size: 853 bytes --]

Hi Dushyant,

The folks following the org mailing list might be able to help you out on
this. So I am forwarding this email there.

I haven't tried recreating this as I do not use sqlite.

But when you say that in the case where you use the default :results sqlite
doesn't work, what doesn't work? Do you get an error?

---------- Forwarded message ---------
From: Dushyant Juneja <juneja.dushyant@gmail.com>
Date: Wed, Sep 14, 2016 at 10:08 AM
Subject: bug with org-babel/sqlite?
To: help-gnu-emacs@gnu.org <help-gnu-emacs@gnu.org>


Hi,

Looks like I stumbled upon a bug with org-babel and sqlite. I have the
following in my org file. PFA the bug description (bug.org). The essence is
that sqlite queries work with :results verbatim, but not otherwise. Any
ideas on how to get past this?

This is with emacs 24.5.2, RHEL6.

Dushyant
-- 

Kaushal Modi

[-- Attachment #1.2: Type: text/html, Size: 1404 bytes --]

[-- Attachment #2: bug.org --]
[-- Type: application/octet-stream, Size: 1623 bytes --]

#+TITLE: Bug report
#+PROPERTY: mkdirp yes

* Setup
Need to exec this first in order for sqlite src to be exec'ed.
#+BEGIN_SRC emacs-lisp :results silent
(org-babel-do-load-languages
 'org-babel-load-languages
 '((emacs-lisp . t)
   (sh         . t)
   (sqlite     . t)
   (C          . t)))
#+END_SRC

* CSV files
For a sample, I have the following CSV file:

#+BEGIN_SRC csv :tangle sample1.csv
Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very Large""",,5000.00
1996,Jeep,Grand Cherokee,"MUST SELL!
air, moon roof, loaded",4799.00
#+END_SRC

* Playing with SQL
Now stat playing with SQL. 

The following snippet works fine:
#+BEGIN_SRC sqlite :db bug.db :exports results :results verbatim
DROP TABLE IF EXISTS cars;
CREATE TABLE cars (Year,Make,Model,Description,Price);
.mode csv
.separator ","
.import sample1.csv cars
SELECT * FROM cars;
#+END_SRC

#+RESULTS:
: Year,Make,Model,Description,Price
: 1997,Ford,E350,"ac, abs, moon",3000.00
: 1999,Chevy,"Venture ""Extended Edition""","",4900.00
: 1999,Chevy,"Venture ""Extended Edition, Very Large""","",5000.00
: 1996,Jeep,"Grand Cherokee","MUST SELL!
: air, moon roof, loaded",4799.00


However, the following fails, just because =:results= is set to default.
# BEGIN_SRC sqlite :db sample.db :exports results :results verbatim
#+BEGIN_SRC sqlite :db sample.db :exports results
DROP TABLE IF EXISTS cars;
CREATE TABLE cars ( Year,Make,Model,Description,Price );
select * from cars;
.mode csv
.import sample1.csv cars
SELECT * FROM cars;
#+END_SRC

Re: Fwd: bug with org-babel/sqlite?

[Nick Dokos]

Kaushal Modi <kaushal.modi@gmail.com> writes:

> Hi Dushyant,
>
> The folks following the org mailing list might be able to help you out on this. So I am forwarding this
> email there.
>
> I haven't tried recreating this as I do not use sqlite.
>
> But when you say that in the case where you use the default :results sqlite doesn't work, what doesn't
> work? Do you get an error?
>
> ---------- Forwarded message ---------
> From: Dushyant Juneja <juneja.dushyant@gmail.com>
> Date: Wed, Sep 14, 2016 at 10:08 AM
> Subject: bug with org-babel/sqlite?
> To: help-gnu-emacs@gnu.org <help-gnu-emacs@gnu.org>
>
> Hi,
>
> Looks like I stumbled upon a bug with org-babel and sqlite. I have the
> following in my org file. PFA the bug description (bug.org). The essence is
> that sqlite queries work with :results verbatim, but not otherwise. Any
> ideas on how to get past this?
>
> This is with emacs 24.5.2, RHEL6.
>
> Dushyant
> --
>
> Kaushal Modi
>
>

Changing the CSV file slightly gets rid of the problem:
,----
| #+BEGIN_SRC csv :tangle sample1.csv
| Year,Make,Model,Description,Price
| 1997,Ford,E350,"ac, abs, moon",3000.00
| 1999,Chevy,"Venture ""Extended Edition""","",4900.00
| 1999,Chevy,"Venture ""Extended Edition, Very Large""",,5000.00
| 1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.00
| #+END_SRC
`----
                                      ^
The CSV file had a newline            ^here.

If you eliminate the newline, things work as they should. Whether
that's a bug in ob-sqlite or sqlite itself, I have no idea.

-- 
Nick

Re: Fwd: bug with org-babel/sqlite?

[Dushyant Juneja]

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

Thanks, Kaushal for forwarding me the reply. Indeed, I was not on the list.
I have requested a subscription now, nevertheless.

Hi Nick,

Yes. That seems to help. However, I suspect the bug is with ob-sqlite
rather than sqlite itself. If I do the following directly in sqlite3
terminal, it seems to work just fine:

119 $ sqlite3
SQLite version 3.7.11 2012-03-20 11:35:50
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> .mode csv
sqlite> create table foo(Year,Make,Model,Description,Price);
sqlite> .import sample1.csv foo
sqlite> select * from foo;
Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very Large""","",5000.00
1996,Jeep,"Grand Cherokee","MUST SELL!
air, moon roof, loaded",4799.00

Given that SQL is hence able to treat it properly, it looks like the bug
lies with ob-sqlite wrongly interpreting the newline.

Dushyant

On Thu, Sep 15, 2016 at 6:32 AM Kaushal Modi <kaushal.modi@gmail.com> wrote:

> Not sure if you have subscribed to the org mode mailing list. If not, you
> would not have received this reply. So forwarding that to you.
>
> ---------- Forwarded message ---------
> From: Nick Dokos <ndokos@gmail.com>
> Date: Wed, Sep 14, 2016, 5:18 PM
> Subject: Re: [O] Fwd: bug with org-babel/sqlite?
> To: <emacs-orgmode@gnu.org>
>
>
> Kaushal Modi <kaushal.modi@gmail.com> writes:
>
> > Hi Dushyant,
> >
> > The folks following the org mailing list might be able to help you out
> on this. So I am forwarding this
> > email there.
> >
> > I haven't tried recreating this as I do not use sqlite.
> >
> > But when you say that in the case where you use the default :results
> sqlite doesn't work, what doesn't
> > work? Do you get an error?
> >
> > ---------- Forwarded message ---------
> > From: Dushyant Juneja <juneja.dushyant@gmail.com>
> > Date: Wed, Sep 14, 2016 at 10:08 AM
> > Subject: bug with org-babel/sqlite?
> > To: help-gnu-emacs@gnu.org <help-gnu-emacs@gnu.org>
> >
> > Hi,
> >
> > Looks like I stumbled upon a bug with org-babel and sqlite. I have the
> > following in my org file. PFA the bug description (bug.org). The
> essence is
> > that sqlite queries work with :results verbatim, but not otherwise. Any
> > ideas on how to get past this?
> >
> > This is with emacs 24.5.2, RHEL6.
> >
> > Dushyant
> > --
> >
> > Kaushal Modi
> >
> >
>
> Changing the CSV file slightly gets rid of the problem:
> ,----
> | #+BEGIN_SRC csv :tangle sample1.csv
> | Year,Make,Model,Description,Price
> | 1997,Ford,E350,"ac, abs, moon",3000.00
> | 1999,Chevy,"Venture ""Extended Edition""","",4900.00
> | 1999,Chevy,"Venture ""Extended Edition, Very Large""",,5000.00
> | 1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.00
> | #+END_SRC
> `----
>                                       ^
> The CSV file had a newline            ^here.
>
> If you eliminate the newline, things work as they should. Whether
> that's a bug in ob-sqlite or sqlite itself, I have no idea.
>
> --
> Nick
>
>
> --
>
> Kaushal Modi
>

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