From mboxrd@z Thu Jan 1 00:00:00 1970 From: yesare Subject: Re: Help with Babel and using SQL Date: Sun, 13 Feb 2011 19:54:16 -0600 Message-ID: References: <87ei7cpahv.fsf@gnu.org> <87r5bbrdgh.fsf@gmail.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="===============1320943761==" Return-path: Received: from [140.186.70.92] (port=52475 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1PoneC-0001km-6L for emacs-orgmode@gnu.org; Sun, 13 Feb 2011 20:54:41 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1PoneA-00006A-Hi for emacs-orgmode@gnu.org; Sun, 13 Feb 2011 20:54:40 -0500 Received: from mail-ww0-f49.google.com ([74.125.82.49]:51215) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1PoneA-00005t-84 for emacs-orgmode@gnu.org; Sun, 13 Feb 2011 20:54:38 -0500 Received: by wwb17 with SMTP id 17so4422483wwb.30 for ; Sun, 13 Feb 2011 17:54:36 -0800 (PST) In-Reply-To: <87r5bbrdgh.fsf@gmail.com> List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: Eric Schulte Cc: emacs-orgmode@gnu.org, Bastien --===============1320943761== Content-Type: multipart/alternative; boundary=0016368319507c279a049c3454bf --0016368319507c279a049c3454bf Content-Type: text/plain; charset=ISO-8859-1 Eric and Dan: Thanks for the feedback. Here is what happened. Regarding var substitution, when I tested it earlier I was running org-mode 7.3 and it did not work. After upgrading to 7.4 the var substitution worked fine. I was also testing it from a xp machine and connecting to a mysql in a linux. First I got a "UNC paths not supported.." then I tried mapping a drive to the linux. That failed because babel was executing CMD.EXE and not finding mysql. This was an error on my side. So I switched my emacs session to linux and it worked. As far as the example provided by Eric on how to load a org table into mysql table, I am still trying to get this to work. When I execute the code babel creates a temp file as /tmp/babel-176640QI/sql-data-1766424f but mysql is complaining with a "ERROR 13 (HY000): Can't get stat of '/tmp/babel-176640QI/sql-data-1766424f' (Errcode: 13)" This must be an issue on my distribution (I think)?? I will continue to test and post results if I succeed. On Sun, Feb 13, 2011 at 12:21 PM, Eric Schulte wrote: > yesare writes: > > > Thanks. > > > > I opened up ob-sql.el and read the code to see if I can figure it out. > > > > I think I have answered my first question. I wrote the following and hit > > C-c C-c and I got the result set back. > > > > #+srcname: sampsql > > #+begin_src sql :engine mysql :cmdline -h hostname -u username -pmypass > -D dbname > > select * from tablename; > > #+end_src > > > > Hi, > > I'm happy to see that the above is now working, so you are successfully > using sql to query an external database and insert the results into an > Org-mode file as a table. > > > > > regarding my 2nd question (reading orgtables), I am thinking that one > can't > > read orgtables in the same way as you read a db table. But you can use > an > > org table to extract data snippets to pass as variables to a sql or build > a > > dynamic sql. Is my understanding correct? > > > > > > I was also experimenting on passing variables. I modified the above > example > > as shown below but I did not get any results > > > > #+srcname: sampsql > > #+begin_src sql :engine mysql :cmdline -h hostname -u username -pmypass > -D dbname :var table="tablename" > > select * from $table; > > #+end_src > > > > A useful tool in these situations where you are not sure what a code > block will do is to call `org-babel-expand-src-block' C-c C-v v. This > will show you how the code block is expanded. When called on your > example above, I see the following > > select * from tablename; > > so your two previous code blocks should behave identically. > > > If rather, you were hoping to read a value from an Org-mode table you > will need to make some adjustments to your previous block, see my > modified example below. It is different in that... > 1. I broke the header arguments out into multiple lines (for readability) > 2. I don't quote the table name so the Org reads it as a reference and > not a literal string > 3. when Org-mode passes tabular data to sql, it writes the data into an > external file, and then passes the name of that file into the sql > code block, so I adjusted the body of the sql code block to reflect > this behavior. Try expanding the org-mode code block below to see > the results. > > #+tblname: example-table-for-sql > | a | b | > |---+----| > | 1 | 10 | > | 2 | 11 | > | 3 | 12 | > | 4 | 13 | > | 5 | 14 | > | 6 | 15 | > > #+srcname: sampsql > #+headers: :var table=example-table-for-sql > #+headers: :cmdline -h hostname -u username -pmypass -D dbname > #+begin_src sql :engine mysql > load data infile "$table" into mytable; > #+end_src > > Hope this is helpful. Best -- Eric > --0016368319507c279a049c3454bf Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Eric and Dan:
Thanks for the feedback.

Here is= what happened.

Regarding var=A0substitution, when= I tested it earlier I was running org-mode 7.3 and it did not work. After = upgrading to 7.4 the var substitution worked fine.

I was also testing it from a xp machine and connecting = to a mysql in a linux. =A0First I got a "UNC paths not supported..&quo= t; then I tried mapping a drive to the linux. =A0That failed because babel = was executing CMD.EXE and not finding mysql. =A0This was an error on my sid= e. =A0So=A0=A0I switched my emacs session to linux and it worked.

As far as the example provided by Eric on how to load a= org table into mysql table, I am still trying to get this to work.
When I execute the code=A0babel creates a temp file as=A0/tmp/babel-1766= 40QI/sql-data-1766424f=A0
but mysql is complaining with a=A0
"ERROR 13 (HY000): C= an't get stat of '/tmp/babel-176640QI/sql-data-1766424f' (Errco= de: 13)"

This must be an issue on my distribu= tion (I think)?? =A0I will continue to test and post results if I succeed.<= /div>


On Sun, Feb 13, 2011 at = 12:21 PM, Eric Schulte <schulte.eric@gmail.com> wrote:
yesare <email.s= nr@gmail.com> writes:

> Thanks.
>
> I opened up ob-sql.el and read the code to see if I can figure it out.=
>
> I think I have answered my first question. =A0I wrote the following an= d hit
> C-c C-c and I got the result set back.
>
> #+srcname: sampsql
> #+begin_src sql :engine mysql :cmdline -h hostname -u username -pmypas= s -D dbname
> select * from tablename;
> #+end_src
>

Hi,

I'm happy to see that the above is now working, so you are successfully=
using sql to query an external database and insert the results into an
Org-mode file as a table.

>
> regarding my 2nd question (reading orgtables), I am thinking that one = can't
> read orgtables in the same way as you read a db table. =A0But you can = use an
> org table to extract data snippets to pass as variables to a sql or bu= ild a
> dynamic sql. Is my understanding correct?
>
>
> I was also experimenting on passing variables. =A0I modified the above= example
> as shown below but I did not get any results
>
> #+srcname: sampsql
> #+begin_src sql :engine mysql :cmdline -h hostname -u username -pmypas= s -D dbname :var table=3D"tablename"
> select * from $table;
> #+end_src
>

A useful tool in these situations where you are not sure what a code<= br> block will do is to call `org-babel-expand-src-block' C-c C-v v. =A0Thi= s
will show you how the code block is expanded. =A0When called on your
example above, I see the following

=A0select * from tablename;

so your two previous code blocks should behave identically.


If rather, you were hoping to read a value from an Org-mode table you
will need to make some adjustments to your previous block, see my
modified example below. =A0It is different in that...
1. I broke the header arguments out into multiple lines (for readability) 2. I don't quote the table name so the Org reads it as a reference and<= br> =A0 not a literal string
3. when Org-mode passes tabular data to sql, it writes the data into an
=A0 external file, and then passes the name of that file into the sql
=A0 code block, so I adjusted the body of the sql code block to reflect =A0 this behavior. =A0Try expanding the org-mode code block below to see =A0 the results.

#+tblname: example-table-for-sql
| a | =A0b |
|---+----|
| 1 | 10 |
| 2 | 11 |
| 3 | 12 |
| 4 | 13 |
| 5 | 14 |
| 6 | 15 |

#+srcname: sampsql
#+headers: :var table=3Dexample-table-for-sql
#+headers: :cmdline -h hostname -u username -pmypass -D dbname
#+begin_src sql :engine mysql
load data infile "$table" into mytable;
#+end_src

Hope this is helpful. =A0Best -- Eric

--0016368319507c279a049c3454bf-- --===============1320943761== Content-Type: text/plain; charset="us-ascii" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Disposition: inline _______________________________________________ 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 --===============1320943761==--