Help with Babel and using SQL

Help with Babel and using SQL

[yesare]

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

Hello:
I am new to orgmode.
So far I have managed to organize and use org by reading documemtation.
Now I want to experiment with babel and SQL and I am stuck as I am unable to
find any good documentation on this.
I also searched the mail archive and I see several code snippets but so far
I have not been able to put them together.
I see stuf like "engine" but I can't find documentation.

I am trying to do two things.


   1. Query a mysql or oracle table and post results in orgtable format
   2. Select data from an existing orgtable in current buffer and do further
   processing with them (example: insert them into Oracle).

I am sorry if this is vague but I am just looking for some initial guidance
on how to get around.

Is this possible?

Can someone point me to the documentation or share some experience.

Thanks.

[-- Attachment #1.2: Type: text/html, Size: 1055 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

Re: Help with Babel and using SQL

[Bastien]

Hi Yesare,

I'm not familiar enough with Babel to give you useful guidance, 
but others on the list may help you.

yesare <email.snr@gmail.com> writes:

> I am trying to do two things.
>  
>
>  1. Query a mysql or oracle table and post results in orgtable format
>  2. Select data from an existing orgtable in current buffer and do
>     further processing with them (example: insert them into Oracle).
>
> I am sorry if this is vague but I am just looking for some initial
> guidance on how to get around.

Can you post the code your tried?  What you expected?  What happened
instead?   This will help people to know where and why you're stuck.

Thanks!

-- 
 Bastien

Re: Help with Babel and using SQL

[yesare]

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

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

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







On Sun, Feb 13, 2011 at 3:08 AM, Bastien <bastien.guerry@wikimedia.fr>wrote:

> Hi Yesare,
>
> I'm not familiar enough with Babel to give you useful guidance,
> but others on the list may help you.
>
> yesare <email.snr@gmail.com> writes:
>
> > I am trying to do two things.
> >
> >
> >  1. Query a mysql or oracle table and post results in orgtable format
> >  2. Select data from an existing orgtable in current buffer and do
> >     further processing with them (example: insert them into Oracle).
> >
> > I am sorry if this is vague but I am just looking for some initial
> > guidance on how to get around.
>
> Can you post the code your tried?  What you expected?  What happened
> instead?   This will help people to know where and why you're stuck.
>
> Thanks!
>
> --
>  Bastien
>

[-- Attachment #1.2: Type: text/html, Size: 2501 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

Re: Help with Babel and using SQL

[Eric Schulte]

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

Re: Help with Babel and using SQL

[Dan Davison]

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
>
> 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'm not sure yet. Could you give a concrete example of something that
you think is not possible? Then it shouldn't be too hard for us to say
whether it is or isn't possible.

> I was also experimenting on passing variables.  I modified the above example
> as shown below but I did not get any results

I'm not the best person to help here (Eric Schulte and Seb Vauban would
know more about using babel with sql) but your example does seem to work
to me. If I put point on the block and issue C-c C-v C-v
(org-babel-expand-src-block), then I see

select * from tablename;

but if I get rid of the :var then I see

select * from $table;

demonstrating that the :var substitution has been performed

What do you get when you expand the block?

Dan

>
> #+srcname: sampsql
> #+begin_src sql :engine mysql :cmdline -h hostname -u username -pmypass -D
> dbname :var table="tablename"
> select * from $table;
> #+end_src
>
>
>
>
>
>
>
> On Sun, Feb 13, 2011 at 3:08 AM, Bastien <bastien.guerry@wikimedia.fr>wrote:
>
>> Hi Yesare,
>>
>> I'm not familiar enough with Babel to give you useful guidance,
>> but others on the list may help you.
>>
>> yesare <email.snr@gmail.com> writes:
>>
>> > I am trying to do two things.
>> >
>> >
>> >  1. Query a mysql or oracle table and post results in orgtable format
>> >  2. Select data from an existing orgtable in current buffer and do
>> >     further processing with them (example: insert them into Oracle).
>> >
>> > I am sorry if this is vague but I am just looking for some initial
>> > guidance on how to get around.
>>
>> Can you post the code your tried?  What you expected?  What happened
>> instead?   This will help people to know where and why you're stuck.
>>
>> Thanks!
>>
>> --
>>  Bastien
>>
> _______________________________________________
> 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

Re: Help with Babel and using SQL

[yesare]

[-- 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