From mboxrd@z Thu Jan 1 00:00:00 1970 From: "Eric Schulte" Subject: Re: Help with Babel and using SQL Date: Sun, 13 Feb 2011 11:21:55 -0700 Message-ID: <87r5bbrdgh.fsf@gmail.com> References: <87ei7cpahv.fsf@gnu.org> Mime-Version: 1.0 Content-Type: text/plain Return-path: Received: from [140.186.70.92] (port=54636 helo=eggs.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1Poglx-0003ym-QC for emacs-orgmode@gnu.org; Sun, 13 Feb 2011 13:34:15 -0500 Received: from Debian-exim by eggs.gnu.org with spam-scanned (Exim 4.71) (envelope-from ) id 1Poglw-0003T0-IC for emacs-orgmode@gnu.org; Sun, 13 Feb 2011 13:34:13 -0500 Received: from mail-pv0-f169.google.com ([74.125.83.169]:38814) by eggs.gnu.org with esmtp (Exim 4.71) (envelope-from ) id 1Poglw-0003Sm-Bl for emacs-orgmode@gnu.org; Sun, 13 Feb 2011 13:34:12 -0500 Received: by pvc30 with SMTP id 30so932319pvc.0 for ; Sun, 13 Feb 2011 10:34:11 -0800 (PST) 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: yesare Cc: emacs-orgmode@gnu.org, Bastien 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