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#
- Die Dateien SqlTable.jar, de-tds-ito-common-dynamic.jar und beliebig viele Datenbank Treiber in das WEB-INF/lib Verzeichnis legen
- durchstarten
Parameter#
Parameter | Beschreibung | Wertebereich | Default |
---|---|---|---|
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#
- Copy SqlTable.jar, de-tds-ito-common-dynamic.jar and as many database drivers as you need to the WEB-INF/lib directory
- restart
Parameters#
Parameter | Description | Allowed Values | Default |
---|---|---|---|
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
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 |