Re: [Orgmode] Re: Patch for ob-sql.el SQL output

[Eric Schulte]

Hi Charles,

This looks great, I've just applied this most recent patch.

Thanks -- Eric

Charles Sebold <address@hidden> 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 <address@hidden>:
>> 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 <address@hidden>:
>>> 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.
>>> address@hidden
>>> http://lists.gnu.org/mailman/listinfo/emacs-orgmode
>>>
>>
>>
>>
>> --
>> Charles Sebold
>> Ego delendus sum
>>