SqlPlugin

This plugin adds access to SQL databases using wiki apps.

Syntax

SQL

Executes an SQL statement. This can be either a select or any other SQL statement. The result of a select can be stored under a specific id to reuse it in further SQLFORMAT statements (see below).

%SQL{"query" ...parameter ...}%

Parameter Description Default
query sql statement to be executed  
database connection to be used first database in connection pool
id identifies the query result to be reusable via SQLFORMAT  
decode values are "url" or "entity"; specifies the encoding of the query before being executed none
format format string to render each hit of a search result $id
header header srting prepended to the rendered result  
footer footer string appended to the rendered result  
separator separator to be put in between each hit rendered using the format parameter , (comma)
hidenull values are "on" or "off": flag to hide any rendered result when an sql search returned no hit; off means any output will be suppressed when nothing was found; on means that a header and footer will always be printed no matter how many hits have been found off
limit maximum number of search hits to render; a 0 (zero) will render all hits found 0
skip skip the number of hits before starting to render the search result 0

If format, header and footer are all undefined, a standard foswiki-table will be generated.

SQLFORMAT

Format the result of a previous %SQL select.

%SQLFORMAT{"id" ...parameter...}%

Parameter Description Default
id identifies the result set as given to %SQL  
continue valies are "on" or "off"; when switched on, a previously used statetment in %SQL will be reused; if switched off, the statement as specified by the corresponding %SQL will be executed again by the database engine off
format,
header,
footer,
hidenull,
skip,
limit
see above  

The format parameter may contain variables of the form $colname, where colname is the name of the column as returned by an sql select. Standard escapes like $percnt, $nop, $n and $dollar can be used in format, header, footer and separator to delay the execution on TML until after the %SQL statement has finished.

Configuring database connections

Connections to a database are configured by specifying a list of connections in configure. It is stored in an array in the variable $Foswiki::cfg{SqlPlugin}{Databases}.

Example:

$Foswiki::cfg{SqlPlugin}{Databases} = [
  {
    'id' => 'mysql',
    'dsn' => 'dbi:mysql:foswiki:localhost',
    'username' => 'foswiki_user',
    'password' => 'foswiki_password',
  },
  {
    'id' => 'sqlite',
    'dsn' => 'dbi:SQLite:dbname=/var/www/foswiki/working/work_areas/SqlPlugin/sqlite.db'
  },
  {
    'id' => 'csv',
     'dsn' => 'dbi:CSV:f_dir=/vaar/www/foswiki/working/work_areas/SqlPlugin/csv'
  },
];

This setting configures three connections - known under the ids mysql, sqlite and csv using different drivers. The first sets up a connection to a mysql database called "foswiki" on the localhost server, protected by the given user and password settings. The second connects to an sqlite database stored at the given path; the third one connects to a database of CSV files stored at the giveh directory. Note, you will need to make sure that the specified driver in the dsn parameter is installed on your system. Please look up the individual driver documentation how to specify a correct value for dsn.

Installation Instructions

You do not need to install anything in the browser to use this extension. The following instructions are for the administrator who installs the extension on the server.

Open configure, and open the "Extensions" section. Use "Find More Extensions" to get a list of available extensions. Select "Install".

If you have any problems, or if the extension isn't available in configure, then you can still install manually from the command-line. See http://foswiki.org/Support/ManuallyInstallingExtensions for more help.

Info

Author(s): Foswiki:MichaelDaum
Copyright: © 2009-2010 Michael Daum http://michaeldaumconsulting.com
License: GPL (Gnu General Public License)
Version: 8285 (2010-07-23)
Release: 1.01
Change History:  
14 Jun 2010: removed hard-coded sort of column keys
Dependencies:
NameVersionDescription
DBI>=1Required.
Home page: http://foswiki.org/Extensions/SqlPlugin
Support page: http://foswiki.org/Support/SqlPlugin
Topic attachments
I Attachment Action Size Date Who Comment
SqlPlugin.md5md5 SqlPlugin.md5 manage 0.1 K 23 Jul 2010 - 15:45 MichaelDaum  
SqlPlugin.sha1sha1 SqlPlugin.sha1 manage 0.2 K 23 Jul 2010 - 15:45 MichaelDaum  
SqlPlugin.tgztgz SqlPlugin.tgz manage 7.1 K 23 Jul 2010 - 15:45 MichaelDaum  
SqlPlugin.zipzip SqlPlugin.zip manage 10.6 K 23 Jul 2010 - 15:45 MichaelDaum  
SqlPlugin_installerEXT SqlPlugin_installer manage 4.1 K 23 Jul 2010 - 15:45 MichaelDaum  
Edit | Attach | Print version | History: r7 | r4 < r3 < r2 < r1 | Backlinks | View wiki text | Edit WikiText | More topic actions...
Topic revision: r3 - 23 Jul 2010, MichaelDaum
 
The copyright of the content on this website is held by the contributing authors, except where stated elsewhere. see CopyrightStatement. Creative Commons License