Re: Re: Patch for ob-sql.el SQL output

From: Charles Sebold <csebold@gmail.com>
To: "Sébastien Vauban" <wxhgmqzgwmuf@spammotel.com>
Cc: emacs-orgmode <emacs-orgmode@gnu.org>
Subject: Re: Re: Patch for ob-sql.el SQL output
Date: Tue, 21 Dec 2010 12:58:32 -0600	[thread overview]
Message-ID: <AANLkTimFPtxF4-pjTT3hiVzM2m20gPGJSA9FfhqGLqdr@mail.gmail.com> (raw)
In-Reply-To: <877hf381yp.fsf@gmail.com>

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

Sébastien:

What do you think would be a good solution for the blank between the output
of a table and the row count if someone didn't run SET NOCOUNT ON?

I mean, should that be another hline, or no blank line, or what?

If somebody's trying to programatically use this table, it will mess up
their data, but I would think they wouldn't have NOCOUNT off in that case.

It seems to me like the right thing to do is another hline, but I'm not
convinced.  And it is even more of a hack, because I don't know how to test
for the "rows affected" line without an ugly search for some regexp like "
rows? affected)", and in other language environments I would guess it
doesn't even say that.

In the intermediate output file the row count line is preceded by a blank
line, but if there is only one column being output how can you tell the
difference between that and a blank record?

Anyway, if somebody wants the row count line at the moment I don't know how
to work around the extra blank without a ton of logic for single-column or
multi-column responses, and this is probably just making this code more and
more osql-specific, which will not be helpful for people on free database
systems.  But if anybody has any ideas or suggestions, I may have a little
time to try to implement them.

On Tue, Dec 21, 2010 at 11:34 AM, Eric Schulte <schulte.eric@gmail.com>wrote:

> Hi Charles,
>
> This looks great, I've just applied this most recent patch.
>
> Thanks -- Eric
>
> Charles Sebold <csebold@gmail.com> 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-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= (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 <csebold@gmail.com>:
> >> I feel silly.  I 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'."
> >>          (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,19 @@ 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))))
> >>       (org-table-import out-file '(16))
> >>       (org-babel-reassemble-table
> >> -       (org-table-to-lisp)
> >> +       (mapcar (lambda (x)
> >> +                 (if (string= (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 Sébastien Vauban <wxhgmqzgwmuf@spammotel.com>:
> >>> Hi Charles,
> >>>
> >>> Charles Sebold wrote:
> >>>> I use org-mode and babel under Windows with osql, and the line
> separating
> >>>> 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
> >>>
> >>>   #+srcname: top-10-dossiers-with-many-presta
> >>>   #+begin_src sql
> >>>   SET NOCOUNT ON
> >>>
> >>>   SELECT TOP 10 prsAbcID, COUNT(*) AS '# Presta'
> >>>   FROM presta
> >>>   GROUP BY prsAbcID
> >>>   ORDER BY COUNT(*) DESC
> >>>   #+end_src
> >>>
> >>>   #+results: top-10-dossiers-with-many-presta
> >>>   |         prsAbcID |      # Presta |
> >>>   | ---------------- | ------------- |
> >>>   |   73020050900111 |            22 |
> >>>   |   52020030200047 |            21 |
> >>>   |   61020060400007 |            21 |
> >>>   |   62020031200052 |            20 |
> >>>   |   72020051100016 |            20 |
> >>>   |   73020050800025 |            20 |
> >>>   |   56020031100002 |            19 |
> >>>   |   63020060900056 |            19 |
> >>>   |   61020030900049 |            18 |
> >>>   |   72020030700040 |            18 |
> >>>   |                  |               |
> >>>
> >>>   Another annoying thing is the empty line at the end. With the COUNT
> ON,
> >>>   there is one empty line followed by the count.
> >>>
> >>> 2. After
> >>>
> >>>   #+results: top-10-dossiers-with-many-presta
> >>>   |         prsAbcID |      # Presta |
> >>>   | ---------------- | ------------- |
> >>>   |   73020050900111 |            22 |
> >>>   |   52020030200047 |            21 |
> >>>   |   61020060400007 |            21 |
> >>>   |   62020031200052 |            20 |
> >>>   |   72020051100016 |            20 |
> >>>   |   73020050800025 |            20 |
> >>>   |   56020031100002 |            19 |
> >>>   |   63020060900056 |            19 |
> >>>   |   61020030900049 |            18 |
> >>>   |   72020030700040 |            18 |
> >>>   |------------------+---------------|
> >>>
> >>>   The hline is not applied on the second line, but at the end of the
> table.
> >>>   Perhaps the good condition is to work on the second line?
> >>>
> >>> Best regards,
> >>>  Seb
> >>>
> >>> --
> >>> Sébastien 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
> >>
>

-- 
Charles Sebold
Ego delendus sum

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

[-- Attachment #2: Type: text/plain, Size: 201 bytes --]

_______________________________________________
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