Re: Help with Babel and using SQL

[yesare <email.snr@gmail.com>]

[-- Attachment #1.1: Type: text/plain, Size: 3725 bytes --]

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 <schulte.eric@gmail.com>wrote:

> yesare <email.snr@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.  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
>

[-- Attachment #1.2: Type: text/html, Size: 4682 bytes --]

[-- Attachment #2: Type: text/plain, Size: 201 bytes --]

_______________________________________________
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