From mboxrd@z Thu Jan 1 00:00:00 1970 From: Jason Riedy Subject: How I'm using these: Generating SQL insert statements. Date: Sun, 02 Mar 2008 22:06:39 -0800 Message-ID: <878x108how.fsf_-_@sparse.yi.org> References: <87fxv8iqw1.fsf@sparse.yi.org> <87hcfo8j2l.fsf@sparse.yi.org> <87d4qc8idn.fsf_-_@sparse.yi.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Return-path: Received: from mailman by lists.gnu.org with tmda-scanned (Exim 4.43) id 1JW3pC-0005kE-2a for emacs-orgmode@gnu.org; Mon, 03 Mar 2008 01:06:58 -0500 Received: from exim by lists.gnu.org with spam-scanned (Exim 4.43) id 1JW3pB-0005jV-Dh for emacs-orgmode@gnu.org; Mon, 03 Mar 2008 01:06:57 -0500 Received: from [199.232.76.173] (helo=monty-python.gnu.org) by lists.gnu.org with esmtp (Exim 4.43) id 1JW3pB-0005jH-6z for emacs-orgmode@gnu.org; Mon, 03 Mar 2008 01:06:57 -0500 Received: from main.gmane.org ([80.91.229.2] helo=ciao.gmane.org) by monty-python.gnu.org with esmtps (TLS-1.0:RSA_AES_256_CBC_SHA1:32) (Exim 4.60) (envelope-from ) id 1JW3pA-0007MC-JL for emacs-orgmode@gnu.org; Mon, 03 Mar 2008 01:06:56 -0500 Received: from list by ciao.gmane.org with local (Exim 4.43) id 1JW3p2-0002kC-Et for emacs-orgmode@gnu.org; Mon, 03 Mar 2008 06:06:48 +0000 Received: from 76.191.193.207 ([76.191.193.207]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 03 Mar 2008 06:06:48 +0000 Received: from jason by 76.191.193.207 with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Mon, 03 Mar 2008 06:06:48 +0000 List-Id: "General discussions about Org-mode." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Sender: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org Errors-To: emacs-orgmode-bounces+geo-emacs-orgmode=m.gmane.org@gnu.org To: emacs-orgmode@gnu.org This code is only partially baked, but it's working for me at the moment. I'm using my multi-target changes to generate both a LaTeX description of the values as well as SQL insert statements in separate noweb chunks. The code leaves a spare blank line in place of the header and cannot handle more than one header line, but it's all I need... Jason (defun ejr/orgtbl-to-sqlinsert (table params) "Convert the orgtbl-mode TABLE to SQL insert statements. TABLE is a list, each entry either the symbol `hline' for a horizontal separator line, or a list of fields for that line. PARAMS is a property list of parameters that can influence the conversion. Supports all parameters from `orgtbl-to-generic'. Most important for SQL are: :splice When set to t, return only insert statements, don't wrap them in a transaction. Default is nil. :sqlname The name of the database table; defaults to the name of the target region. :tstart, :tend The strings used to begin and commit the transaction. :fmt A format to be used to wrap the field value, should contain %s for the original field value. The default wraps the value in the non-standard [] quote marks but does not protect against ] characters in the header name. The format may also be a function that formats its one argument. :hfmt A function that gathers the []-quoted header names into a dynamically scoped variable HDRLIST. The general parameters :skip and :skipcols have already been applied when this function is called." (let* (hdrlist (alignment (mapconcat (lambda (x) (if x "r" "l")) org-table-last-alignment "")) (params2 (list :sqlname name :tstart "BEGIN TRANSACTION;" :tend "COMMIT;" :fmt "[%s]" :hfmt (lambda (f) (push (concat "[" f "]") hdrlist) "") :hlfmt (lambda (lst) nil) :lstart (lambda () (concat "INSERT INTO " (plist-get params :sqlname) "( " (mapconcat 'identity (reverse hdrlist) ", ") " ) VALUES ( ")) :lend " );" :sep " , " :hline nil))) (orgtbl-to-generic table (org-combine-plists params2 params))))