Re: [Orgmode] Help with Babel and using SQL

[Eric Schulte]

yesare <address@hidden> 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