Virtual Brain/Java/snipsnap contributions 1 DB-View Macro 1.1 Description Queries a database and renders tabular result. Example: \{dbview:java:comp/env/jdbc/testDS|select * from salary\}. 1.1 Parameters 1. fully qualified JNDI datasource name to be used for database access (mandatory) 1. SQL string that will produce the tabular data to be rendered (mandatory) 1. header off - set to 'true' if rendering of the resulting table must not include a header row (optional) 1.1 Example result {table} ID | EMP_ID | SALARY | STAMP 100 | 19382 | 10000 | foo-20050530 101 | 29809 | 12345 | foo-2003-0404 {table} 1.1 Download See attachements of this snip. 1.1 Installing Copy dbview-macro.jar to your snipsnap/WEB-INF/lib directory. Then set up the data source: 1.1.1 Define the datasource For Tomcat, this is done in the tomcat/conf/server.xml file in the snipsnap context: {code:xml} <Context path="/snipsnap" docBase="/tomcat-5.0/webapps/snipsnap" debug="5" reloadable="true"> <Resource name="jdbc/testDS" auth="Container" type="javax.sql.DataSource"/> <ResourceParams name="jdbc/testDS"> <parameter> <name>factory</name> <value>org.apache.commons.dbcp.BasicDataSourceFactory</value> </parameter> <!-- MySQL dB username and password for dB connections --> <parameter> <name>username</name> <value>foo</value> </parameter> <parameter> <name>password</name> <value>bar</value> </parameter> <parameter> <name>driverClassName</name> <value>com.mysql.jdbc.Driver</value> </parameter> <parameter> <name>url</name> <value>jdbc:mysql://localhost/test?autoReconnect=true</value> </parameter> </ResourceParams> </Context> {code} 1.1.1 Reference the datasource For Tomcat, this is added in the snipsnap/WEB-INF/web.xml file: {code:xml} <resource-ref> <res-ref-name> jdbc/testDS </res-ref-name> <res-type> javax.sql.DataSource </res-type> <res-auth> Container </res-auth> </resource-ref> {code} 1.1.1 Use the datasource In the dbview-macro, a datasource as defined above would be referenced as follows: {code:none} \{dbview:java:comp/env/jdbc/testDS|select * from foobar\}. {code} 1.1 WARNING - Datasource name and SQL query can be seen by EVERYONE, even if NOT LOGGED IN. - If logged in, users may use arbitrary SQL queries and therefore gain access to any data provided by the data source available. Please consider this before installing the macro on any site. 2005-06-05 21:16:23.818 2005-06-05 21:16:00.404 Sun Jun 05 21:16:23 CEST 2005 Sun Jun 05 21:16:15 CEST 2005