* 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 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
* 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
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).