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