From mboxrd@z Thu Jan 1 00:00:00 1970 From: "Eric Schulte" Subject: Re: Re: Patch for ob-sql.el SQL output Date: Tue, 21 Dec 2010 10:34:06 -0700 Message-ID: <877hf381yp.fsf@gmail.com> References: <80hbe7gmwh.fsf@missioncriticalit.com> Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Return-path: Received: from [140.186.70.92] (port=51083 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PV66L-0000xb-7s for emacs-orgmode@gnu.org; Tue, 21 Dec 2010 12:34:21 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PV66G-000437-Vj for emacs-orgmode@gnu.org; Tue, 21 Dec 2010 12:34:17 -0500 Received: from mail-iw0-f169.google.com ([209.85.214.169]:49392) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PV66G-000433-ND for emacs-orgmode@gnu.org; Tue, 21 Dec 2010 12:34:12 -0500 Received: by iwn40 with SMTP id 40so4962344iwn.0 for ; Tue, 21 Dec 2010 09:34:12 -0800 (PST) In-Reply-To: (Charles Sebold's message of "Tue, 21 Dec 2010 11:08:18 -0600") 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: Charles Sebold Cc: =?utf-8?Q?S=C3=A9bastien?= Vauban , emacs-orgmode Hi Charles, This looks great, I've just applied this most recent patch. Thanks -- Eric Charles Sebold writes: > 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-bloc= k'." > (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. =C2=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-bloc= k'." >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(in-file (org-babel-temp-file "sql-in-= ")) >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(out-file (or (cdr (assoc :out-file pa= rams)) >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0(org-babel-temp-file "sql-out-"))) >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0 (header-delim "") >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(command (case (intern engine) >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ('= msosql (format "osql %s -s \"\t\" -i %s -o %s" >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(or cmdline "= ") >> @@ -84,9 +85,19 @@ This function is called by `org-babel-execute-src-blo= ck'." >> =C2=A0 =C2=A0 (message command) >> =C2=A0 =C2=A0 (shell-command command) >> =C2=A0 =C2=A0 (with-temp-buffer >> + =C2=A0 =C2=A0 =C2=A0; need to figure out what the delimiter is for the= header row >> + =C2=A0 =C2=A0 =C2=A0(with-temp-buffer >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0(insert-file-contents out-file) >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0(goto-char (point-min)) >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0(when (re-search-forward "^\\(-+\\)[^-]" ni= l t) >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(setq header-delim (match-string-no-= properties 1)))) >> =C2=A0 =C2=A0 =C2=A0 (org-table-import out-file '(16)) >> =C2=A0 =C2=A0 =C2=A0 (org-babel-reassemble-table >> - =C2=A0 =C2=A0 =C2=A0 (org-table-to-lisp) >> + =C2=A0 =C2=A0 =C2=A0 (mapcar (lambda (x) >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (if (string=3D= (car x) header-delim) >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = 'hline >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 x)) >> + =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 (org-table-to-lisp)) >> =C2=A0 =C2=A0 =C2=A0 =C2=A0(org-babel-pick-name (cdr (assoc :colname-nam= es params)) >> =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0(cdr (assoc :colnames params))) >> =C2=A0 =C2=A0 =C2=A0 =C2=A0(org-babel-pick-name (cdr (assoc :rowname-nam= es params)) >> >> 2010/12/21 S=C3=A9bastien Vauban : >>> Hi Charles, >>> >>> Charles Sebold wrote: >>>> I use org-mode and babel under Windows with osql, and the line separat= ing >>>> 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 fo= r 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 >>> >>> =C2=A0 #+srcname: top-10-dossiers-with-many-presta >>> =C2=A0 #+begin_src sql >>> =C2=A0 SET NOCOUNT ON >>> >>> =C2=A0 SELECT TOP 10 prsAbcID, COUNT(*) AS '# Presta' >>> =C2=A0 FROM presta >>> =C2=A0 GROUP BY prsAbcID >>> =C2=A0 ORDER BY COUNT(*) DESC >>> =C2=A0 #+end_src >>> >>> =C2=A0 #+results: top-10-dossiers-with-many-presta >>> =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 prsAbcID | =C2=A0 =C2=A0 =C2=A0# P= resta | >>> =C2=A0 | ---------------- | ------------- | >>> =C2=A0 | =C2=A0 73020050900111 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A022 | >>> =C2=A0 | =C2=A0 52020030200047 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A021 | >>> =C2=A0 | =C2=A0 61020060400007 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A021 | >>> =C2=A0 | =C2=A0 62020031200052 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A020 | >>> =C2=A0 | =C2=A0 72020051100016 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A020 | >>> =C2=A0 | =C2=A0 73020050800025 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A020 | >>> =C2=A0 | =C2=A0 56020031100002 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A019 | >>> =C2=A0 | =C2=A0 63020060900056 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A019 | >>> =C2=A0 | =C2=A0 61020030900049 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A018 | >>> =C2=A0 | =C2=A0 72020030700040 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A018 | >>> =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0= | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | >>> >>> =C2=A0 Another annoying thing is the empty line at the end. With the CO= UNT ON, >>> =C2=A0 there is one empty line followed by the count. >>> >>> 2. After >>> >>> =C2=A0 #+results: top-10-dossiers-with-many-presta >>> =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 prsAbcID | =C2=A0 =C2=A0 =C2=A0# P= resta | >>> =C2=A0 | ---------------- | ------------- | >>> =C2=A0 | =C2=A0 73020050900111 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A022 | >>> =C2=A0 | =C2=A0 52020030200047 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A021 | >>> =C2=A0 | =C2=A0 61020060400007 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A021 | >>> =C2=A0 | =C2=A0 62020031200052 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A020 | >>> =C2=A0 | =C2=A0 72020051100016 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A020 | >>> =C2=A0 | =C2=A0 73020050800025 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A020 | >>> =C2=A0 | =C2=A0 56020031100002 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A019 | >>> =C2=A0 | =C2=A0 63020060900056 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A019 | >>> =C2=A0 | =C2=A0 61020030900049 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A018 | >>> =C2=A0 | =C2=A0 72020030700040 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A018 | >>> =C2=A0 |------------------+---------------| >>> >>> =C2=A0 The hline is not applied on the second line, but at the end of t= he table. >>> =C2=A0 Perhaps the good condition is to work on the second line? >>> >>> Best regards, >>> =C2=A0Seb >>> >>> -- >>> S=C3=A9bastien 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 >>