* Patch for ob-sql.el SQL output
@ 2010-12-21 15:02 Charles Sebold
2010-12-21 15:34 ` Sébastien Vauban
0 siblings, 1 reply; 8+ messages in thread
From: Charles Sebold @ 2010-12-21 15:02 UTC (permalink / raw)
To: emacs-orgmode
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.
I don't know that this is a really good fix, but maybe it's a start
for one? I've filled out paperwork with FSF for copyright assignment.
It looks for the first output line of all dashes and replaces it
later with the 'hline when the table is lisp-ified. I didn't test
this with mysql or postgresql.
diff --git a/lisp/ob-sql.el b/lisp/ob-sql.el
index 5bb123d..13e9bd3 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))
--
Charles Sebold
Ego delendus sum
^ permalink raw reply related [flat|nested] 8+ messages in thread
* Re: Patch for ob-sql.el SQL output
2010-12-21 15:02 Patch for ob-sql.el SQL output Charles Sebold
@ 2010-12-21 15:34 ` Sébastien Vauban
2010-12-21 16:57 ` Charles Sebold
0 siblings, 1 reply; 8+ messages in thread
From: Sébastien Vauban @ 2010-12-21 15:34 UTC (permalink / raw)
To: emacs-orgmode-mXXj517/zsQ
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-mXXj517/zsQ@public.gmane.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Re: Patch for ob-sql.el SQL output
2010-12-21 15:34 ` Sébastien Vauban
@ 2010-12-21 16:57 ` Charles Sebold
2010-12-21 17:08 ` Charles Sebold
0 siblings, 1 reply; 8+ messages in thread
From: Charles Sebold @ 2010-12-21 16:57 UTC (permalink / raw)
To: Sébastien Vauban; +Cc: emacs-orgmode
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
^ permalink raw reply related [flat|nested] 8+ messages in thread
* Re: Re: Patch for ob-sql.el SQL output
2010-12-21 16:57 ` Charles Sebold
@ 2010-12-21 17:08 ` Charles Sebold
2010-12-21 17:34 ` Eric Schulte
0 siblings, 1 reply; 8+ messages in thread
From: Charles Sebold @ 2010-12-21 17:08 UTC (permalink / raw)
To: Sébastien 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= (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
^ permalink raw reply related [flat|nested] 8+ messages in thread
* Re: Re: Patch for ob-sql.el SQL output
2010-12-21 17:08 ` Charles Sebold
@ 2010-12-21 17:34 ` Eric Schulte
2010-12-21 18:58 ` Charles Sebold
2010-12-22 10:05 ` Sébastien Vauban
0 siblings, 2 replies; 8+ messages in thread
From: Eric Schulte @ 2010-12-21 17:34 UTC (permalink / raw)
To: Charles Sebold; +Cc: Sébastien Vauban, emacs-orgmode
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
>>
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Re: Patch for ob-sql.el SQL output
2010-12-21 17:34 ` Eric Schulte
@ 2010-12-21 18:58 ` Charles Sebold
2010-12-22 10:23 ` Sébastien Vauban
2010-12-22 10:05 ` Sébastien Vauban
1 sibling, 1 reply; 8+ messages in thread
From: Charles Sebold @ 2010-12-21 18:58 UTC (permalink / raw)
To: Sébastien Vauban; +Cc: emacs-orgmode
[-- 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
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Patch for ob-sql.el SQL output
2010-12-21 17:34 ` Eric Schulte
2010-12-21 18:58 ` Charles Sebold
@ 2010-12-22 10:05 ` Sébastien Vauban
1 sibling, 0 replies; 8+ messages in thread
From: Sébastien Vauban @ 2010-12-22 10:05 UTC (permalink / raw)
To: emacs-orgmode-mXXj517/zsQ
Hi Charles and Eric,
"Eric Schulte" wrote:
> This looks great, I've just applied this most recent patch.
For the SQL code blocks I currently play with (and with the `msosql' engine),
it works great:
- hline correctly outputted
- last blank line correctly removed
Thanks!
Best regards,
Seb
--
Sébastien Vauban
_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode-mXXj517/zsQ@public.gmane.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode
^ permalink raw reply [flat|nested] 8+ messages in thread
* Re: Patch for ob-sql.el SQL output
2010-12-21 18:58 ` Charles Sebold
@ 2010-12-22 10:23 ` Sébastien Vauban
0 siblings, 0 replies; 8+ messages in thread
From: Sébastien Vauban @ 2010-12-22 10:23 UTC (permalink / raw)
To: emacs-orgmode-mXXj517/zsQ
Hi Charles,
Charles Sebold wrote:
> 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.
I always disable the COUNT (let's say, enable the NOCOUNT) as this messes up
all the tables, but I don't have a good solution, would we want to see the
total -- which is, though, an instructive and important data.
Depending on the context of the SELECT, it could sometimes be equivalent to an
Org table formula (vsum), but how to automatically tell??
> 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.
I would say that you could identify that blank line by looking at line "-2", I
mean counted from the bottom of the table.
I have no idea how other engines report such data (I don't have Postgre or
MySQL at hand), but I'd be interested in hearing from them.
Other idea: don't put the COUNT information in the table, but just under it,
as normal Org text. Maybe that's the most portable and viable solution?
Best regards,
Seb
--
Sébastien Vauban
_______________________________________________
Emacs-orgmode mailing list
Please use `Reply All' to send replies to the list.
Emacs-orgmode-mXXj517/zsQ@public.gmane.org
http://lists.gnu.org/mailman/listinfo/emacs-orgmode
^ permalink raw reply [flat|nested] 8+ messages in thread
end of thread, other threads:[~2010-12-22 10:23 UTC | newest]
Thread overview: 8+ messages (download: mbox.gz / follow: Atom feed)
-- links below jump to the message on this page --
2010-12-21 15:02 Patch for ob-sql.el SQL output Charles Sebold
2010-12-21 15:34 ` Sébastien Vauban
2010-12-21 16:57 ` Charles Sebold
2010-12-21 17:08 ` Charles Sebold
2010-12-21 17:34 ` Eric Schulte
2010-12-21 18:58 ` Charles Sebold
2010-12-22 10:23 ` Sébastien Vauban
2010-12-22 10:05 ` Sébastien Vauban
Code repositories for project(s) associated with this public inbox
https://git.savannah.gnu.org/cgit/emacs/org-mode.git
This is a public inbox, see mirroring instructions
for how to clone and mirror all data and code used for this inbox;
as well as URLs for read-only IMAP folder(s) and NNTP newsgroup(s).