View Attach (4) Info

SqlTablePlugin V1.1#

Motivation#

Wir haben bei uns eine Menge verschiedener CMDBs im Einsatz. Das JSPWiki nutzen wir als globale Wissensdatenbank. Um die Ausschnitte aus den verschiedenen CMDBs in unserer Wiki sichtbar zu machen habe ich dieses Plugin wentwickelt. Es erlaubt aus nahezu beliebigen Datenbanken, Tabellensichten via SQL im Wiki sichtbar zu machen.

Unterstützte Datenbanken#

  • DB2
  • Oracle
  • MSSQL
  • MySQL
  • SAPDB/MAXDB

Beispiel#

[{INSERT de.tds.ito.wiki.sqltable.SqlTablePlugin WHERE 
sql='select Rechner, Betriebssystem, Rechnermodell from dbo.Tab4Einzelrechner where Betriebssystem like \'Linux%\'' 
type=mssql db=rechner server=redwood port=1433 login=geheim password='geheim'
tableparam='width="100%"' sortable=true filter=true top=100}]

Installation#

  1. Die Dateien SqlTable.jar, de-tds-ito-common-dynamic.jar und beliebig viele Datenbank Treiber in das WEB-INF/lib Verzeichnis legen
  2. durchstarten

Parameter#

ParameterBeschreibungWertebereichDefault
sql das Sql Statement String [-]
top die wieviel ersten Zeilen sollen angezeigt werden. Integer [100]
type Typ der Datenbank [mssql|db2|oracle|sapdb|mysql|mssql6.5jtds|mssql7+jtds|sybase10jtds|sybase11+jtds] [-]
db Name der Datenbank String [-]
server IP oder Rechnername auf dem die Datenbank läuft. String [-]
port Der TCP Port der Datenbank Integer [-]
login Das Datenbank Login String [-]
password Das Datenbank Passwort String [-]
sortable Erlaubt die Sortierung der Tabelle via Ajax [true|false] [false]
filter Erlaubt die Filterung der Tabelle via Ajax [true|false] [false]
tableparam Zusätzliche Attribute für <table> String [-]
rowparam Zusätzliche Attribute für <tr> String [-]
columnparam Zusätzliche Attribute für <td> String [-]
linkcols Liste der Spalten, die als Link auf eine Wiki Seite dargestellt werden sollen. Vector<Integer> :(col[ http://myurl/%cell%],col,col,...) [-]
cache viele Minuten soll das SQL Ergebnis gecacht werden (0=kein caching). Integer [0]
format soll das Ergebnis anstelle als csv mit oder ohne header ausgegeben werden String {[-]

Disclaimer: I only tried to translate the German text to the English language. I have not written or tested this plugin - so there is no guarantee that the English text is correct.
--Frank_Fischer

Motivation#

We have a lot of different CMDBs. The JSPWiki is used as a global knowledge database. To view parts of our CMDBs in our Wiki, I have created a plugin which can display a table out of many different kinds of databases on a wiki page.

Supported Databases#

  • DB2
  • Oracle
  • MSSQL
  • MySQL
  • SAPDB/MAXDB

Example#

[{INSERT de.tds.ito.wiki.sqltable.SqlTablePlugin WHERE 
sql='select Rechner, Betriebssystem, Rechnermodell from dbo.Tab4Einzelrechner where Betriebssystem like \'Linux%\'' 
type=mssql db=rechner server=redwood port=1433 login=geheim password='geheim'
tableparam='width="100%"' sortable=true filter=true top=100}]

Installation#

  1. Copy SqlTable.jar, de-tds-ito-common-dynamic.jar and as many database drivers as you need to the WEB-INF/lib directory
  2. restart

Parameters#

ParameterDescriptionAllowed ValuesDefault
sql the Sql Statement String [-]
top how much rows sould be displayed max. Integer [100]
type type of database [mssql|db2|oracle|sapdb|mysql|mssql6.5jtds|mssql7+jtds|sybase10jtds|sybase11+jtds] [-]
db name of the database String [-]
server IP or hostname of the database server String [-]
port the TCP port the database listens to Integer [-]
login the database login String [-]
password database password String [-]
sortable enabled sorting of the table via ajax [true|false] [false]
filter enables filtering of the table via ajax [true|false] [false]
tableparam additional attribute of <table> String [-]
rowparam additional attribute of <tr> String [-]
columnparam additional attribute of <td> String [-]
linkcols list of columns, which should be displayed as Wiki Link, seperated by , Vector<Integer> :(col,col,col,...) [-]
cache the number of minutes to cache the sql result (0=caching is disabled) Integer [0]
format to format the output as pure csv [-|csv|csvh] [-]

Hi all!

I am trying this plug-in with mysql 5.0.19, mysql-connector-java-5.0.6, JSPWiki 2.5.90-cvs.
I got this error in the wikipage.
Note I have the SQL result at the bottom of the page.
Any help appreciated. 

java.sql.SQLException: Can't call commit when autocommit=true at 
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:914) at 
com.mysql.jdbc.Connection.commit(Connection.java:2274) at 
de.tds.ito.common.database.SqlExecutor.execute(SqlExecutor.java:17) at 
de.tds.ito.wiki.sqltable.SqlTablePlugin.execute(SqlTablePlugin.java:323) at 
com.ecyrd.jspwiki.plugin.PluginManager.execute(PluginManager.java:380) at 
com.ecyrd.jspwiki.parser.PluginContent.getText(PluginContent.java:155) at 
org.jdom.output.XMLOutputter.printTextRange(XMLOutputter.java:1054) at 
org.jdom.output.XMLOutputter.printContentRange(XMLOutputter.java:989) at 
org.jdom.output.XMLOutputter.outputElementContent(XMLOutputter.java:445) at 
com.ecyrd.jspwiki.render.XHTMLRenderer.getString(XHTMLRenderer.java:63) at 
com.ecyrd.jspwiki.render.RenderingManager.getHTML(RenderingManager.java:258) at 
com.ecyrd.jspwiki.render.RenderingManager.getHTML(RenderingManager.java:299) at 
com.ecyrd.jspwiki.WikiEngine.textToHTML(WikiEngine.java:1445) at 
com.ecyrd.jspwiki.WikiEngine.getHTML(WikiEngine.java:1387) at 
com.ecyrd.jspwiki.tags.InsertPageTag.doWikiStartTag(InsertPageTag.java:128) at 
com.ecyrd.jspwiki.tags.WikiTagBase.doStartTag(WikiTagBase.java:88) at 
org.apache.jsp.templates.default_.PageTab_jsp._jspx_meth_wiki_005fInsertPage_005f0(PageTab_jsp.java:658) at 
org.apache.jsp.templates.default_.PageTab_jsp._jspService(PageTab_jsp.java:271) at 
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:98) at 
javax.servlet.http.HttpServlet.service(HttpServlet.java:803) at 
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:328) at 

(...)

--EnricoM, 17-Jul-2007

Workaround: In mysql.ini added: 

init-connect=SET AUTOCOMMIT=0

(or in MySQL Administrator --> Startup Variables --> Advanced --> Enable init connect with SET AUTOCOMMIT=0)

Can u modify the connection string adding relaxAutoCommit=true? Thx!

--EnricoM, 20-Jul-2007

Hallo

Is this plug in open source ? Can I get the source code ?

thanks Tracer

--Tracer, 09-Aug-2007

Hallo

Is there a way, to run the plugin with Microsoft Access ?

thank you

--Tracer, 10-Aug-2007

Hi,

I'd like to add support for a H2 database. How would this be done?

There is another plugin around--WikiSQL--which uses JNDI to work with database, thus reducing the effort to provide support for any new database type. Unfortunately WikiSQL allows only SELECT statements.

Would it be an option to enhance SQL Tables with JNDI support?

- Sil68, 30-Mar-2008

Is anyone using this plugin seeing all their data enclosed in single quotes? All of the data that is being pulled from the mysql db is showing up in the table surrounded by single quotes. so even a blank string shows up as ' '. Any thoughts or help would be appreciated.

--JBolter, 09-May-2008

Add new attachment

Only authorized users are allowed to upload new attachments.

List of attachments

Kind Attachment Name Size Version Date Modified Author Change note
jar
 SqlTable.jar 13.4 kB 5 23-Mar-2007 17:56 Lukas Weberruss csf format bug fix for new common
jpg
 SqlTablePlugin..jpg 87.6 kB 1 12-Jan-2007 16:01 Lukas Weberruss
exe
 de-tds-ito-common-dynamic.part... 400.0 kB 3 23-Mar-2007 18:06 Lukas Weberruss Support for more database types and updated SqlEntry
rar
 de-tds-ito-common-dynamic.part... 180.0 kB 3 23-Mar-2007 18:07 Lukas Weberruss Support for more database types and updated SqlEntry
« This page (revision-24) was last changed on 09-May-2008 20:25 by 63.250.183.170