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.e...@gmail.com>wrote: > yesare <email....@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 >
_______________________________________________ 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
