Re: [Orgmode] Help with Babel and using SQL

[yesare]

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 <address@hidden> wrote:

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