babel and postgresql

babel and postgresql

[dmg]

Hi everybody, Eric,

I have been starting using Babel with postgresql, R and perl, and I am
loving it. I really want to thank everybody for their work.

I have found one particular issue that bothers me.

Say I have the following babel section:

#+name: abc
#+begin_src sql :engine postgresql :cmdline mydb
select * from aliases limit 1;
#+end_src

the output is:

#+name: abc
| alias                     | uniname      |
| Jon <tixy@xyz.org>        | jon          |

Note how the column names are not separated from the body:

What I want it this:

#+name:
| alias                     | uniname      |
|---------------------------+--------------|
| Jon <tixy@xyz.org>        | jon          |

I have tracked the problem, and it is that in ob-sql.el the code of
org-babel-execute:sql thinks that postgres will return a header separator,
and it does not.

I am not sure what is the best way to fix it, but I have come with a
patch that does it (but replaced the older code). The code in
org-babel-execute:sql
needs to be modified so it does this only for the postgres backend:
split the list into first member and rest and insert a 'hline in
between. My solution is rough, but it works (sorry, I am just an elisp beginner)


diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 3586ec9..95cac85 100644
--- a/lisp/ob-sql.el
+++ b/lisp/ob-sql.el
@@ -170,11 +170,12 @@ This function is called by `org-babel-execute-src-block'."
 	  )
 	(org-table-import out-file '(16))
 	(org-babel-reassemble-table
-	 (mapcar (lambda (x)
-		   (if (string= (car x) header-delim)
-		       'hline
-		     x))
-		 (org-table-to-lisp))
+	 (funcall (lambda (x)
+		    (cons (car-safe x)
+			  (cons 'hline (cdr-safe x))
+			  )
+		    )
+		(org-table-to-lisp))
 	 (org-babel-pick-name (cdr (assoc :colname-names params))
 			      (cdr (assoc :colnames params)))
 	 (org-babel-pick-name (cdr (assoc :rowname-names params))



-- 
--dmg

---
Daniel M. German
http://turingmachine.org

Re: babel and postgresql

[dmg]

It looks like my inexperience with Lisp made me ignore the obvious.
What is needed is not my patch below, but to add posgresql to the
condition of the case:

From:
	(case (intern engine)
	  ('mysql


To:

	(case (intern engine)
	  ('(postgresql mysql)

the problem is that I can't get it to work (and I could not find good
documentation for the case macro anywhere but its definition. It says
that it takes either an atom (i.e. 'mysql)
or a list (i.e. '(postgresql mysql) ) but it just does not work me.

But the code for mysql and postgres seems to be the same.

Sorry for the confusion.

--daniel

On Sat, Feb 23, 2013 at 10:42 PM, dmg <dmg@uvic.ca> wrote:
> Hi everybody, Eric,
>
> I have been starting using Babel with postgresql, R and perl, and I am
> loving it. I really want to thank everybody for their work.
>
> I have found one particular issue that bothers me.
>
> Say I have the following babel section:
>
> #+name: abc
> #+begin_src sql :engine postgresql :cmdline mydb
> select * from aliases limit 1;
> #+end_src
>
> the output is:
>
> #+name: abc
> | alias                     | uniname      |
> | Jon <tixy@xyz.org>        | jon          |
>
> Note how the column names are not separated from the body:
>
> What I want it this:
>
> #+name:
> | alias                     | uniname      |
> |---------------------------+--------------|
> | Jon <tixy@xyz.org>        | jon          |
>
> I have tracked the problem, and it is that in ob-sql.el the code of
> org-babel-execute:sql thinks that postgres will return a header separator,
> and it does not.
>
> I am not sure what is the best way to fix it, but I have come with a
> patch that does it (but replaced the older code). The code in
> org-babel-execute:sql
> needs to be modified so it does this only for the postgres backend:
> split the list into first member and rest and insert a 'hline in
> between. My solution is rough, but it works (sorry, I am just an elisp beginner)
>
>
> diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
> index 3586ec9..95cac85 100644
> --- a/lisp/ob-sql.el
> +++ b/lisp/ob-sql.el
> @@ -170,11 +170,12 @@ This function is called by `org-babel-execute-src-block'."
>           )
>         (org-table-import out-file '(16))
>         (org-babel-reassemble-table
> -        (mapcar (lambda (x)
> -                  (if (string= (car x) header-delim)
> -                      'hline
> -                    x))
> -                (org-table-to-lisp))
> +        (funcall (lambda (x)
> +                   (cons (car-safe x)
> +                         (cons 'hline (cdr-safe x))
> +                         )
> +                   )
> +               (org-table-to-lisp))
>          (org-babel-pick-name (cdr (assoc :colname-names params))
>                               (cdr (assoc :colnames params)))
>          (org-babel-pick-name (cdr (assoc :rowname-names params))
>
>
>
> --
> --dmg
>
> ---
> Daniel M. German
> http://turingmachine.org



-- 
--dmg

---
Daniel M. German
http://turingmachine.org

Re: babel and postgresql

[Bastien]

Hi Daniel,

dmg <dmg@uvic.ca> writes:

> It looks like my inexperience with Lisp made me ignore the obvious.
> What is needed is not my patch below, but to add posgresql to the
> condition of the case:
>
> From:
> 	(case (intern engine)
> 	  ('mysql
>
>
> To:
>
> 	(case (intern engine)
> 	  ('(postgresql mysql)
>
> the problem is that I can't get it to work (and I could not find good
> documentation for the case macro anywhere but its definition. It says
> that it takes either an atom (i.e. 'mysql)
> or a list (i.e. '(postgresql mysql) ) but it just does not work me.

I applied this patch, which should do the right thing given your
explanations:

  http://orgmode.org/cgit.cgi/org-mode.git/commit/?id=f8e874

Let me know.  Thanks!

-- 
 Bastien