using orgtbl-sqlinsert

using orgtbl-sqlinsert

[Eric Abrahamsen]

I've got a table I'm trying to insert into a sqlite database. I've been
looking at orgtbl-sqlinsert and this page[fn:1], but I suspect all that
is out of date. I haven't seen anyone talking about #+ORGTBL: keywords,
or things like "#+BEGIN RECEIVE ORGTBL exsql" in the past year or so.

Do those still work? I can't for the life of me get it to do anything.
I've got a sqlite database called "market.sqlite" in the same directory
as a file containing this:

#+TBLNAME: terms
#+ORGTBL: SEND market.sqlite orgtbl-to-sqlinsert :sqlname "terms"
| Chinese          | English                            |
|------------------+------------------------------------|
| 音像制品出版     | A/V Publishing                     |
| 定价总金额       | Aggregate Retail Price             |
(etc)

I've tried C-c C-c on that header, calling
`org-babel-execute-src-block', waving my fingers at it, and cursing
loudly, one of which usually works. But I get no error, no insertion
into the database, and no results block with INSERT statements (I tried
making a #+BEGIN RECEIVE ORGTBL marketdb.sqlite block as noted on worg).

Does this still work? Or is it still possible to jimmy orgtbl-sqlinsert
to either insert directly, or create a block of INSERT statements?

Thanks!

Footnotes:

[fn:1] http://orgmode.org/worg/org-tutorials/multitarget-tables.html

Re: using orgtbl-sqlinsert

[Eric Abrahamsen]

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

A very quiet "bump", to see if anyone knows how to handle this...

> I've got a table I'm trying to insert into a sqlite database. I've been
> looking at orgtbl-sqlinsert and this page[fn:1], but I suspect all that
> is out of date. I haven't seen anyone talking about #+ORGTBL: keywords,
> or things like "#+BEGIN RECEIVE ORGTBL exsql" in the past year or so.
>
> Do those still work? I can't for the life of me get it to do anything.
> I've got a sqlite database called "market.sqlite" in the same directory
> as a file containing this:
>
> #+TBLNAME: terms
> #+ORGTBL: SEND market.sqlite orgtbl-to-sqlinsert :sqlname "terms"
> | Chinese          | English                            |
> |------------------+------------------------------------|
> | 音像制品出版     | A/V Publishing                     |
> | 定价总金额       | Aggregate Retail Price             |
> (etc)
>
> I've tried C-c C-c on that header, calling
> `org-babel-execute-src-block', waving my fingers at it, and cursing
> loudly, one of which usually works. But I get no error, no insertion
> into the database, and no results block with INSERT statements (I tried
> making a #+BEGIN RECEIVE ORGTBL marketdb.sqlite block as noted on worg).
>
> Does this still work? Or is it still possible to jimmy orgtbl-sqlinsert
> to either insert directly, or create a block of INSERT statements?
>
> Thanks!
>
> Footnotes:
>
> [fn:1] http://orgmode.org/worg/org-tutorials/multitarget-tables.html

[PATCH] Re: using orgtbl-sqlinsert

[Eric Abrahamsen]

[-- Attachment #1: Type: text/plain, Size: 1250 bytes --]

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> Eric Abrahamsen <eric@ericabrahamsen.net> writes:
>
> A very quiet "bump", to see if anyone knows how to handle this...

Clearly whining wasn't getting me anywhere, so here's a patch. I don't
claim to understand all the ins and outs of orgtbl-to-generic, so this
might not be complete, but at least it gets it working again. There were
plain old errors in the existing code (undefined variables, etc), so
this has to be an improvement, even if it's incomplete.

So with this patch, calling `org-babel-execute-src-block' on the
#+BEGIN_SRC line in the following:

#+TBLNAME: terms
| Chinese          | English                            |
|------------------+------------------------------------|
| 音像制品出版     | A/V Publishing                     |

#+NAME: insert-statements
#+BEGIN_SRC emacs-lisp :var terms=terms :wrap "SRC sqlite :db \"dbname.sqlite\""
  (orgtbl-to-sqlinsert terms '(:sqlname "terms"))
#+END_SRC

#+RESULTS: insert-statements

Produces this:

#+RESULTS: insert-statements
#+BEGIN_SRC sqlite :db "dbname.sqlite"
BEGIN TRANSACTION;
INSERT INTO terms( Chinese, English ) VALUES ( '音像制品出版' , 'A/V Publishing' );
COMMIT;
#+END_SRC


Hope all's in order.

E


[-- Warning: decoded text below may be mangled, UTF-8 assumed --]
[-- Attachment #2: 0001-contrib-lisp-orgtbl-sqlinsert.el.patch --]
[-- Type: text/x-patch, Size: 1328 bytes --]

From dfec3ea34c4aca1c6e46064e2bd867252b87d46d Mon Sep 17 00:00:00 2001
From: Eric Abrahamsen <eric@ericabrahamsen.net>
Date: Wed, 5 Jun 2013 11:44:35 +0800
Subject: [PATCH] * contrib/lisp/orgtbl-sqlinsert.el (orgtbl-to-sqlinsert):
 Bugfixes regarding header line formatting

---
 contrib/lisp/orgtbl-sqlinsert.el | 6 +++---
 1 file changed, 3 insertions(+), 3 deletions(-)

diff --git a/contrib/lisp/orgtbl-sqlinsert.el b/contrib/lisp/orgtbl-sqlinsert.el
index f07a0ba..b00c93d 100644
--- a/contrib/lisp/orgtbl-sqlinsert.el
+++ b/contrib/lisp/orgtbl-sqlinsert.el
@@ -70,14 +70,14 @@ this function is called."
          (*orgtbl-default-fmt* 'orgtbl-sql-strip-and-quote)
 	 (params2
 	  (list
-	   :sqlname name
+	   :sqlname (plist-get params :sqlname)
 	   :tstart (lambda () (concat (if nowebname
 					  (format "<<%s>>= \n" nowebname)
 					"")
 				      "BEGIN TRANSACTION;"))
 	   :tend (lambda () (concat "COMMIT;" (if nowebname "\n@ " "")))
-	   :hfmt (lambda (f) (progn (if firstheader (push f hdrlist)) ""))
-	   :hlfmt (lambda (lst) (setq firstheader nil))
+	   :hfmt (lambda (f) (progn (if firstheader (push f hdrlist) "")))
+	   :hlfmt (lambda (&rest cells) (setq firstheader nil))
 	   :lstart (lambda () (concat "INSERT INTO "
 				      sqlname "( "
 				      (mapconcat 'identity (reverse hdrlist)
-- 
1.8.3

Re: [PATCH] Re: using orgtbl-sqlinsert

[Bastien]

Hi Eric,

Eric Abrahamsen <eric@ericabrahamsen.net> writes:

> Clearly whining wasn't getting me anywhere, so here's a patch. I don't
> claim to understand all the ins and outs of orgtbl-to-generic, so this
> might not be complete, but at least it gets it working again. There were
> plain old errors in the existing code (undefined variables, etc), so
> this has to be an improvement, even if it's incomplete.

I applied this patch, thanks.

-- 
 Bastien

Re: using orgtbl-sqlinsert

[Carsten Dominik]

[-- Attachment #1: Type: text/plain, Size: 1786 bytes --]

Hi Eric,

do you have orgtbl-to-sqlite defined?  What does it look like?
Do you have the BEGIN RECEIVE and END RECEIVE lines in the buffer?
Are you working in a buffer that is not in Org-mode?  Do you have orgtbl-mode turned on?

Regards

- Carsten

On 26.5.2013, at 08:56, Eric Abrahamsen <eric@ericabrahamsen.net> wrote:

> I've got a table I'm trying to insert into a sqlite database. I've been
> looking at orgtbl-sqlinsert and this page[fn:1], but I suspect all that
> is out of date. I haven't seen anyone talking about #+ORGTBL: keywords,
> or things like "#+BEGIN RECEIVE ORGTBL exsql" in the past year or so.
> 
> Do those still work? I can't for the life of me get it to do anything.
> I've got a sqlite database called "market.sqlite" in the same directory
> as a file containing this:
> 
> #+TBLNAME: terms
> #+ORGTBL: SEND market.sqlite orgtbl-to-sqlinsert :sqlname "terms"
> | Chinese          | English                            |
> |------------------+------------------------------------|
> | 音像制品出版     | A/V Publishing                     |
> | 定价总金额       | Aggregate Retail Price             |
> (etc)
> 
> I've tried C-c C-c on that header, calling
> `org-babel-execute-src-block', waving my fingers at it, and cursing
> loudly, one of which usually works. But I get no error, no insertion
> into the database, and no results block with INSERT statements (I tried
> making a #+BEGIN RECEIVE ORGTBL marketdb.sqlite block as noted on worg).
> 
> Does this still work? Or is it still possible to jimmy orgtbl-sqlinsert
> to either insert directly, or create a block of INSERT statements?
> 
> Thanks!
> 
> Footnotes:
> 
> [fn:1] http://orgmode.org/worg/org-tutorials/multitarget-tables.html
> 
> 


[-- Attachment #2: Message signed with OpenPGP using GPGMail --]
[-- Type: application/pgp-signature, Size: 455 bytes --]

Re: using orgtbl-sqlinsert

[Jason Riedy]

And Eric Abrahamsen writes:
> I've got a table I'm trying to insert into a sqlite
> database. I've been looking at orgtbl-sqlinsert and this
> page[fn:1], but I suspect all that is out of date.

FYI, I have changed employers and thus no longer have up-to-date
paperwork at the FSF for assignment.  That is only one reason I
haven't updated anything relevant to the orgtbl-sqlinsert
procrasti-working hack...

A more important reason is that I suspect a method using
org-babel would be much better.

I was looking for a semi-one-off method to convert a table
definition into something within my workflow at the time.  Org
mode was somewhat new and seemed useful (now proven that it is
very useful).  I was focused on *my* workflow, but the mindset
behind org-babel is more general.  I apologize, but I have not
had the time to look into adapting the mechanism or pursuing the
generalization.
-- 
Jason

Re: using orgtbl-sqlinsert

[Carsten Dominik]

[-- Attachment #1: Type: text/plain, Size: 1102 bytes --]


On 25.9.2013, at 02:31, Jason Riedy <jason@lovesgoodfood.com> wrote:

> And Eric Abrahamsen writes:
>> I've got a table I'm trying to insert into a sqlite
>> database. I've been looking at orgtbl-sqlinsert and this
>> page[fn:1], but I suspect all that is out of date.
> 
> FYI, I have changed employers and thus no longer have up-to-date
> paperwork at the FSF for assignment.  That is only one reason I
> haven't updated anything relevant to the orgtbl-sqlinsert
> procrasti-working hack...
> 
> A more important reason is that I suspect a method using
> org-babel would be much better.
> 
> I was looking for a semi-one-off method to convert a table
> definition into something within my workflow at the time.  Org
> mode was somewhat new and seemed useful (now proven that it is
> very useful).  I was focused on *my* workflow, but the mindset
> behind org-babel is more general.  I apologize, but I have not
> had the time to look into adapting the mechanism or pursuing the
> generalization.

I must have overlooked this, my apologies.

Thanks!

Bug queue one down :)

- Carsten

> -- 
> Jason


[-- Attachment #2: Message signed with OpenPGP using GPGMail --]
[-- Type: application/pgp-signature, Size: 455 bytes --]

Re: using orgtbl-sqlinsert

[Eric Abrahamsen]

Carsten Dominik <carsten.dominik@gmail.com> writes:

> Hi Eric,
>
> do you have orgtbl-to-sqlite defined?  What does it look like?
> Do you have the BEGIN RECEIVE and END RECEIVE lines in the buffer?
> Are you working in a buffer that is not in Org-mode?  Do you have orgtbl-mode turned on?

Hi Carsten,

I later replied to that thread (at least, I think it should have been
part of the same thread) with a patch to orgtbl-to-sqlite. Bastien
applied it, and to my knowledge all is well. As Jason mentioned, this
and I guess the other orgtbl-* functions seem sort of out of date at
this point (should they all be replaced with Babel?), but the problem I
brought up here, at least, is fixed.

Thanks!
Eric

> On 26.5.2013, at 08:56, Eric Abrahamsen <eric@ericabrahamsen.net> wrote:
>
>> I've got a table I'm trying to insert into a sqlite database. I've been
>> looking at orgtbl-sqlinsert and this page[fn:1], but I suspect all that
>> is out of date. I haven't seen anyone talking about #+ORGTBL: keywords,
>> or things like "#+BEGIN RECEIVE ORGTBL exsql" in the past year or so.
>> 
>> Do those still work? I can't for the life of me get it to do anything.
>> I've got a sqlite database called "market.sqlite" in the same directory
>> as a file containing this:
>> 
>> #+TBLNAME: terms
>> #+ORGTBL: SEND market.sqlite orgtbl-to-sqlinsert :sqlname "terms"
>> | Chinese          | English                            |
>> |------------------+------------------------------------|
>> | 音像制品出版     | A/V Publishing                     |
>> | 定价总金额       | Aggregate Retail Price             |
>> (etc)
>> 
>> I've tried C-c C-c on that header, calling
>> `org-babel-execute-src-block', waving my fingers at it, and cursing
>> loudly, one of which usually works. But I get no error, no insertion
>> into the database, and no results block with INSERT statements (I tried
>> making a #+BEGIN RECEIVE ORGTBL marketdb.sqlite block as noted on worg).
>> 
>> Does this still work? Or is it still possible to jimmy orgtbl-sqlinsert
>> to either insert directly, or create a block of INSERT statements?
>> 
>> Thanks!
>> 
>> Footnotes:
>> 
>> [fn:1] http://orgmode.org/worg/org-tutorials/multitarget-tables.html
>> 
>> 

using orgtbl-sqlinsert

[Eric Abrahamsen]

I've got a table I'm trying to insert into a sqlite database. I've been
looking at orgtbl-sqlinsert and this page[fn:1], but I suspect all that
is out of date. I haven't seen anyone talking about #+ORGTBL: keywords,
or things like "#+BEGIN RECEIVE ORGTBL exsql" in the past year or so.

Do those still work? I can't for the life of me get it to do anything.
I've got a sqlite database called "market.sqlite" in the same directory
as a file containing this:

#+TBLNAME: terms
#+ORGTBL: SEND market.sqlite orgtbl-to-sqlinsert :sqlname "terms"
| Chinese          | English                            |
|------------------+------------------------------------|
| 音像制品出版     | A/V Publishing                     |
| 定价总金额       | Aggregate Retail Price             |
(etc)

I've tried C-c C-c on that header, calling
`org-babel-execute-src-block', waving my fingers at it, and cursing
loudly, one of which usually works. But I get no error, no insertion
into the database, and no results block with INSERT statements (I tried
making a #+BEGIN RECEIVE ORGTBL marketdb.sqlite block as noted on worg).

Does this still work? Or is it still possible to jimmy orgtbl-sqlinsert
to either insert directly, or create a block of INSERT statements?

Thanks!

Footnotes:

[fn:1] http://orgmode.org/worg/org-tutorials/multitarget-tables.html