From mboxrd@z Thu Jan 1 00:00:00 1970 From: Charles Sebold Subject: Re: Re: Patch for ob-sql.el SQL output Date: Tue, 21 Dec 2010 11:08:18 -0600 Message-ID: References: <80hbe7gmwh.fsf@missioncriticalit.com> Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Return-path: Received: from [140.186.70.92] (port=54857 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PV5hJ-0001Va-0M for emacs-orgmode@gnu.org; Tue, 21 Dec 2010 12:08:28 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PV5hD-0006e4-4R for emacs-orgmode@gnu.org; Tue, 21 Dec 2010 12:08:24 -0500 Received: from mail-fx0-f48.google.com ([209.85.161.48]:56374) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PV5hC-0006dH-RV for emacs-orgmode@gnu.org; Tue, 21 Dec 2010 12:08:19 -0500 Received: by fxm2 with SMTP id 2so4144385fxm.35 for ; Tue, 21 Dec 2010 09:08:18 -0800 (PST) In-Reply-To: List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: =?ISO-8859-1?Q?S=E9bastien_Vauban?= Cc: emacs-orgmode OK, another change: added a fix for the blank line at the end. diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el index 5bb123d..2ff85d9 100644 --- a/lisp/ob-sql.el +++ b/lisp/ob-sql.el @@ -65,6 +65,7 @@ This function is called by `org-babel-execute-src-block'.= " (in-file (org-babel-temp-file "sql-in-")) (out-file (or (cdr (assoc :out-file params)) (org-babel-temp-file "sql-out-"))) + (header-delim "") (command (case (intern engine) ('msosql (format "osql %s -s \"\t\" -i %s -o %s" (or cmdline "") @@ -84,9 +85,26 @@ This function is called by `org-babel-execute-src-block'= ." (message command) (shell-command command) (with-temp-buffer + ; need to figure out what the delimiter is for the header row + (with-temp-buffer + (insert-file-contents out-file) + (goto-char (point-min)) + (when (re-search-forward "^\\(-+\\)[^-]" nil t) + (setq header-delim (match-string-no-properties 1))) + (goto-char (point-max)) + (forward-char -1) + (while (looking-at "\n") + (delete-char 1) + (goto-char (point-max)) + (forward-char -1)) + (write-file out-file)) (org-table-import out-file '(16)) (org-babel-reassemble-table - (org-table-to-lisp) + (mapcar (lambda (x) + (if (string=3D (car x) header-delim) + 'hline + 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)) 2010/12/21 Charles Sebold : > I feel silly. =A0I was just testing with one output column. > > Change the $ in the regexp to "[^-]" or just reapply patch as follows: > > diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el > index 5bb123d..32b7bf0 100644 > --- a/lisp/ob-sql.el > +++ b/lisp/ob-sql.el > @@ -65,6 +65,7 @@ This function is called by `org-babel-execute-src-block= '." > =A0 =A0 =A0 =A0 =A0(in-file (org-babel-temp-file "sql-in-")) > =A0 =A0 =A0 =A0 =A0(out-file (or (cdr (assoc :out-file params)) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(org-babel-temp-file "sql-= out-"))) > + =A0 =A0 =A0 =A0 (header-delim "") > =A0 =A0 =A0 =A0 =A0(command (case (intern engine) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ('msosql (format "osql %s -s \"\t= \" -i %s -o %s" > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 = =A0(or cmdline "") > @@ -84,9 +85,19 @@ This function is called by `org-babel-execute-src-bloc= k'." > =A0 =A0 (message command) > =A0 =A0 (shell-command command) > =A0 =A0 (with-temp-buffer > + =A0 =A0 =A0; need to figure out what the delimiter is for the header ro= w > + =A0 =A0 =A0(with-temp-buffer > + =A0 =A0 =A0 =A0(insert-file-contents out-file) > + =A0 =A0 =A0 =A0(goto-char (point-min)) > + =A0 =A0 =A0 =A0(when (re-search-forward "^\\(-+\\)[^-]" nil t) > + =A0 =A0 =A0 =A0 =A0(setq header-delim (match-string-no-properties 1)))) > =A0 =A0 =A0 (org-table-import out-file '(16)) > =A0 =A0 =A0 (org-babel-reassemble-table > - =A0 =A0 =A0 (org-table-to-lisp) > + =A0 =A0 =A0 (mapcar (lambda (x) > + =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 (if (string=3D (car x) header-delim) > + =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 'hline > + =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 x)) > + =A0 =A0 =A0 =A0 =A0 =A0 =A0 (org-table-to-lisp)) > =A0 =A0 =A0 =A0(org-babel-pick-name (cdr (assoc :colname-names params)) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0(cdr (assoc :colna= mes params))) > =A0 =A0 =A0 =A0(org-babel-pick-name (cdr (assoc :rowname-names params)) > > 2010/12/21 S=E9bastien Vauban : >> Hi Charles, >> >> Charles Sebold wrote: >>> I use org-mode and babel under Windows with osql, and the line separati= ng >>> the header from the rest of the rows in the output was bothering me. >> >> Excellent initiative! >> >>> I don't know that this is a really good fix, but maybe it's a start for= one? >>> It looks for the first output line of all dashes and replaces it later = with >>> the 'hline when the table is lisp-ified. >> >> It does not work correctly for me (with the osql engine). Look at the >> situation before/after your patch: >> >> 1. Before >> >> =A0 #+srcname: top-10-dossiers-with-many-presta >> =A0 #+begin_src sql >> =A0 SET NOCOUNT ON >> >> =A0 SELECT TOP 10 prsAbcID, COUNT(*) AS '# Presta' >> =A0 FROM presta >> =A0 GROUP BY prsAbcID >> =A0 ORDER BY COUNT(*) DESC >> =A0 #+end_src >> >> =A0 #+results: top-10-dossiers-with-many-presta >> =A0 | =A0 =A0 =A0 =A0 prsAbcID | =A0 =A0 =A0# Presta | >> =A0 | ---------------- | ------------- | >> =A0 | =A0 73020050900111 | =A0 =A0 =A0 =A0 =A0 =A022 | >> =A0 | =A0 52020030200047 | =A0 =A0 =A0 =A0 =A0 =A021 | >> =A0 | =A0 61020060400007 | =A0 =A0 =A0 =A0 =A0 =A021 | >> =A0 | =A0 62020031200052 | =A0 =A0 =A0 =A0 =A0 =A020 | >> =A0 | =A0 72020051100016 | =A0 =A0 =A0 =A0 =A0 =A020 | >> =A0 | =A0 73020050800025 | =A0 =A0 =A0 =A0 =A0 =A020 | >> =A0 | =A0 56020031100002 | =A0 =A0 =A0 =A0 =A0 =A019 | >> =A0 | =A0 63020060900056 | =A0 =A0 =A0 =A0 =A0 =A019 | >> =A0 | =A0 61020030900049 | =A0 =A0 =A0 =A0 =A0 =A018 | >> =A0 | =A0 72020030700040 | =A0 =A0 =A0 =A0 =A0 =A018 | >> =A0 | =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 | >> >> =A0 Another annoying thing is the empty line at the end. With the COUNT = ON, >> =A0 there is one empty line followed by the count. >> >> 2. After >> >> =A0 #+results: top-10-dossiers-with-many-presta >> =A0 | =A0 =A0 =A0 =A0 prsAbcID | =A0 =A0 =A0# Presta | >> =A0 | ---------------- | ------------- | >> =A0 | =A0 73020050900111 | =A0 =A0 =A0 =A0 =A0 =A022 | >> =A0 | =A0 52020030200047 | =A0 =A0 =A0 =A0 =A0 =A021 | >> =A0 | =A0 61020060400007 | =A0 =A0 =A0 =A0 =A0 =A021 | >> =A0 | =A0 62020031200052 | =A0 =A0 =A0 =A0 =A0 =A020 | >> =A0 | =A0 72020051100016 | =A0 =A0 =A0 =A0 =A0 =A020 | >> =A0 | =A0 73020050800025 | =A0 =A0 =A0 =A0 =A0 =A020 | >> =A0 | =A0 56020031100002 | =A0 =A0 =A0 =A0 =A0 =A019 | >> =A0 | =A0 63020060900056 | =A0 =A0 =A0 =A0 =A0 =A019 | >> =A0 | =A0 61020030900049 | =A0 =A0 =A0 =A0 =A0 =A018 | >> =A0 | =A0 72020030700040 | =A0 =A0 =A0 =A0 =A0 =A018 | >> =A0 |------------------+---------------| >> >> =A0 The hline is not applied on the second line, but at the end of the t= able. >> =A0 Perhaps the good condition is to work on the second line? >> >> Best regards, >> =A0Seb >> >> -- >> S=E9bastien Vauban >> >> >> _______________________________________________ >> Emacs-orgmode mailing list >> Please use `Reply All' to send replies to the list. >> Emacs-orgmode@gnu.org >> http://lists.gnu.org/mailman/listinfo/emacs-orgmode >> > > > > -- > Charles Sebold > Ego delendus sum > --=20 Charles Sebold Ego delendus sum