[Orgmode] Re: Patch for ob-sql.el SQL output
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@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
[Orgmode] Re: Patch for ob-sql.el SQL output
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@gnu.org http://lists.gnu.org/mailman/listinfo/emacs-orgmode
Re: [Orgmode] Re: Patch for ob-sql.el SQL output
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 wrote:
> Hi Charles,
>
> This looks great, I've just applied this most recent patch.
>
> Thanks -- Eric
>
> Charles Sebold 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 :
> >> 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-
Re: [Orgmode] Re: Patch for ob-sql.el SQL output
Hi Charles,
This looks great, I've just applied this most recent patch.
Thanks -- Eric
Charles Sebold 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 :
>> 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 :
>>> 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 |
>>> | 6102006047 | 21 |
>>> | 62020031200052 | 20 |
>>> | 72020051100016 | 20 |
>>> | 73020050800025 | 20 |
>>> | 5602003112 | 19 |
>>> | 63020060900056 | 1
Re: [Orgmode] Re: Patch for ob-sql.el SQL output
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 :
> 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 :
>> 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 |
>> | 6102006047 | 21 |
>> | 62020031200052 | 20 |
>> | 72020051100016 | 20 |
>> | 73020050800025 | 20 |
>> | 5602003112 | 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
>>
>>
Re: [Orgmode] Re: Patch for ob-sql.el SQL output
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 :
> 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 |
> | 6102006047 | 21 |
> | 62020031200052 | 20 |
> | 72020051100016 | 20 |
> | 73020050800025 | 20 |
> | 5602003112 | 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 |
> | 6102006047 | 21 |
> | 62020031200052 | 20 |
> | 72020051100016 | 20 |
> | 73020050800025 | 20 |
> | 5602003112 | 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
___
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
[Orgmode] Re: Patch for ob-sql.el SQL output
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 | | 6102006047 |21 | | 62020031200052 |20 | | 72020051100016 |20 | | 73020050800025 |20 | | 5602003112 |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 | | 6102006047 |21 | | 62020031200052 |20 | | 72020051100016 |20 | | 73020050800025 |20 | | 5602003112 |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
