%DATABASE_SQL{description="table_description" sql="sql"}%
This is the most general of the commands. It allows you to execute arbitrary SQL statements, and provides flexible formatting of the results.
description
- identifies the database configuration to use (required)
sql
- The SQL to execute (required)
format
- (optional) Format to display results in. if you don't give this parameter results will be ignored.
header
- (optional) The header to display on the results table.
separator
- string that separates results. Default newline.
UPDATE
statement) you must not give a format
parameter. The format
parameter is a string describing the required format for the results, where each $colname
will expand to the value of that column. See FormattedSearch for more on how format
, header
and separator
work.
%DATABASE_SQL_TABLE{description="db1" headers="hdr1,hdr2,hdr3" command="sql command"}%
description
- identifies the database configuration to use
headers
- Table headers.
command
- Any SQL command that returns rows
%DATABASE_SQL_REPEAT{description="description" command="..SQL COMMAND.." columns="col1,col2,col3"}% .... user formatting .... %DATABASE_SQL_REPEAT%
description
- identifies the database configuration to use
columns
- The columns in the table to return. Default "*"
command
- Any SQL command that returns values
%DATABASE_TABLE{description="table_description" headers="hdr1,hdr2,hdr3" columns="col1,col2,col3"}%
description
- identifies the database+table configuration to use (required)
columns
- The columns in the table to return. Default "*"
headers
- Table headers
%DATABASE_REPEAT{description="table_description" table="mytable" columns="col1,col2,col3"}% .... user formatting .... %DATABASE_REPEAT%
description
- identifies the database+table configuration to use
columns
- The columns in the table to return. Default "*"
%DATABASE_EDIT{description="table_description" display_text="HTML link text"}%
Creates a frame and invokes an external database editor.
description
- identifies the database configuration to use
display_text
- (optional) The columns in the table to return
%DATABASE_SQL{description="mysql_user_info" format="| $User | $Select_priv |" header="| *User Name* | *Select Privs* |"}%
You will get back a table with one row for each matching database entry.
Using the format
functionality, you can define how the database data is displayed wrapping it in any formatting you choose. For example, if you wanted to create a single table cell containing the information for 3 fields of the Kalendus calendar, you could use the following:
%DATABASE_SQL{description="calendar_events" sql="SELECT * from calendar" format="| $startdate $subject $body |"}%
Or let's say you wanted to display the next two upcoming scheduled events in the Kalendus calendar.
%DATABASE_SQL{description="calendar_events" command="SELECT subject,body,startdate FROM kalendus_event WHERE to_days(startdate) > to_days(now()) order by startdate limit 2" format="$startdate<br />$subject<br />$body"}%
configure
interface (Go to Plugins->Find More Extensions) .zip
or .tgz
archives
perl <module>_installer
)
configure
and enable the module, if it is a plugin.
,v
files in your existing install (take care not to lock the files when you check in)
configure
to set up the plugin.
Plugin Author: | TWiki:Main.TaitCyrus |
Copyright: | © 2002-2007 TWiki:Main.TaitCyrus © 2007-2011 TWiki:TWiki.TWikiContributor |
License: | GPL (GNU General Public License) |
Plugin Version: | 22417 (2011-11-11) |
Change History: | |
2011-11-11: | TWikibug:Item6800: Fix for using qw(...) as parentheses, which is deprecated in Perl 5.14 -- TWiki:Main.PeterThoeny |
2011-05-14: | TWikibug:Item6701: Small fix in Config.spec -- TWiki:Main.PeterThoeny |
2011-02-19: | TWikibug:Item6638: Doc improvements; changing TWIKIWEB to SYSTEMWEB -- TWiki:Main.PeterThoeny |
17 Sep 2007 | Bugs:Item4343 Minor corrections to Config.spec - TWiki:Main.CrawfordCurrie |
18 March 2007 | Rewritten for efficiency and clarity, and added DATABASE_SQL - TWiki:Main.CrawfordCurrie |
5 May 2003 (v1.3) | Add support for the primary DB to be in a local file instead of in a DB. It is acknowledged that this reduces security somewhat Also added support for Oracle (by adding in the concept of a SID) |
20 Mar 2002 (v1.2) | Added table editing ability |
18 Feb 2002 (v1.11): | Removed hard coded $debug=1; |
16 Feb 2002 (v1.1): | Added the two REPEAT functions |
20 Jan 2002 (v1.0): | Initial version |
CPAN Dependencies: | CPAN:DBI |
Other Dependencies: | phpmyadmin (optional, to support DATABASE_EDIT) |
Perl Version: | 5.0 (tested with 5.6.1 [mysql] and 5.8.0 [Oracle and Local] ) |
Plugin Home: | http://TWiki.org/cgi-bin/view/Plugins/DatabasePlugin |
Feedback: | http://TWiki.org/cgi-bin/view/Plugins/DatabasePluginDev |