Overview

The DataBase macro displays results of SQL select statements as a table. Currently connections to ODBC, MySQL and Oracle DB connections are supported. Of course any database source that can be connected by ODBC is supported.

I'm only able to test and support ODBC connections. Consider accessing even MySQL or Oracle DB by ODBC if available. ODBC connections can be made to MS SQL, MySQL, Oracle, Adabas, Sybase, MS Access, MS Excel, CSV text files and any other data source for which an ODBC driver exists.

This macro can be used as a framework. Please supply support for additional database types to make it as universal as possible.

Usage

<<DataBase(source, headers)>>
<<DataBase(source, headers, "query")>>

source

is one of the data source keys predefined in your wiki (specified in the wikiconfig.py, see Setup section below)

header

specifies if field names should be displayed as table header (the default is False)

"query"

SQL select query (only if the source allows/requires queries)

Setup

Requirements

For ODBC connections

Install pyodbc library (http://pyodbc.sourceforge.net)

For MySQL connections

Install MySql for Python (http://sourceforge.net/projects/mysql-python)

For Oracle connections

The prerequisites for Oracle database connections should be the same as for the MacroMarket/SQL macro. I'm not able to verify this.

There is however a change to this: The ORACLE_HOME environment path is not set by the macro code any more. It should be set in your operating system.

Download

Review the /ReleaseNotes.

Download the current version (DataBase-1.0beta2.py).

Install

Rename the file to DataBase.py and copy it to your /data/plugin/macro directory.

Configure

The following configuration options can be set in the wikiconfig.py (setting database_macro_sources is mandatory of course):

The database_macro_sources variable must be a dictionary having the following format:

{ source : [ connection_type, parameters, query ], ... }

source

a key which will be used in the wiki to reference the connection

connection_type

currently either one of these values: 'odbc', 'mysql' or 'oracle'

parameters

dictionary of connection parameters which are dependent on the connection type (see examples below)

query

(optional) if a query is specified, the macro uses this query and doesn't allow/require the user to specify a query in the macro parameters

Allowing users to supply SQL queries is a security risk. It can be used for SQL injections. For use on Internet it is recommended to always specify a query.

Example Connection Parameters

ODBC Connections

Examples providing the full connection information:

{ 'driver':'{SQL Server}', 'server':'(local)', 'database':'HRDB', 'Trusted_Connection':'Yes' }
{ 'driver':'{SQL Server}', 'server':'SQL01', 'database':'HRDB', 'uid':'moinwiki', 'pwd':'moin-odbc' }
{ 'driver':'{Microsoft ODBC for Oracle}', 'server':'ORACLE01', 'Persist Security Info':'False', 'Trusted_Connection':'Yes' }
{ 'driver':'{Microsoft Access Driver (*.mdb)}', 'dbq':'C:\Program Files\TSL\HR\HR.mdb' }
{ 'driver':'{Microsoft Excel Driver (*.xls)}', 'dbq':'C:\Program Files\TSL\HR\Vacation.xls' }
{ 'driver':'{Microsoft Text Driver (*.txt; *.csv)}', 'dbq':'C:\Program Files\TSL\HR\Locations.txt' }

Example on using an ODBC data source name:

{ 'dsn':'HRDB', 'uid':'moinwiki', 'pwd':'moin-odbc' }

Further information on ODBC connection strings can be found at:

• http://pyodbc.sourceforge.net/docs.html

• http://msdn2.microsoft.com/en-us/library/system.data.odbc.odbcconnection.connectionstring.aspx

• http://msdn2.microsoft.com/en-us/library/aa177865(SQL.80).aspx

MySQL and Oracle Connections

{ 'server':'SQL01', 'port':'3306', 'database':'hrdb', 'uid':'moinwiki', 'pwd':'moin-db' }

The port value is optional. If it is omitted, the default port value of the connection type is used.

Feedback

Please use the sub pages /Bugs to report bugs and /Questions to post questions. Other feedback can be posted here.