Make SQL query and form the table

Help / Discuss
1

Hello!

Have anybody used something like PocketQuery for Confluence?

I have a need to form the table at the page by making the SQL Query to the database to fetch the list of server currently in service.

I have found the SQL tools plugin, but, it doesn’t do exactly what I want.
I also found the SQL query snipplet at the wiki, but, it didn’t work for 2 reasons:

  • XWiki don’t boot if I put the driver’s jar jtds-1.3.1.jar to the tomcat lib folder
  • it stores password as a plan text that I don’t really like.

I would be thankful if you can recommend me something to get the table formed by SQL query in the proper way.

Thank you!

BR

2

@vmassol

Vincent, am I right that if I use SQL Tools, I just can get the output on the page of the plugin?
Do you know which approach will be fine to form the tables from the quered data from SQL DB?

Thank you!

3

Please, what does “form the table” mean for you? To select some fields from the XWiki database? To style the output?

4

Ricardo, hi! No, I don’t need anything from the XWiki DB. I would like to fetch the SQL query from the remote SQL server and represent the data in form of the table on the page. PocketQuery in Confluence worked in this way.

5

Hi @DarkJoney! Accept my apologies for not following this conversation!

I don’t know at what point you currently are, but I must come back to some issues close to your questions.

To be able to query remote databases, I created time ago a Script Component. I remember I had issues, but I don’t remember which one! The following code is currently working to me when instantiated as a XWiki.ScriptComponentClass object using Groovy Language:

import javax.inject.Named;
import javax.inject.Singleton;

import org.xwiki.component.annotation.Component;
import org.xwiki.script.service.ScriptService;

import java.sql.*;
import java.util.*;

@Named("dummy")

public class ComponentScriptService implements ScriptService
{
  public String get()
  {
    return "Toto is my best friend!";
  }
  public String getlong()
  {
    return "Even with a minimum number of code lines!";
  }
  public Connection getConnection()
  {
    def conn = "dummy"
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection("jdbc:mysql://hostname/database", "username", "password");
    return conn;
  }
}

Once defined, I call the component from any page. For instance, the following code works to retrieve 100 random entries from a real database:

{{groovy}}
import groovy.sql.Sql

def sql = new Sql(services.dummy.getConnection())
  println "(% class='doOddEven filterable grid sortable' id='tableid' %)"
  println "(% class='sortHeader' %)|=n|=Pid|= Surname|= Name|= Category"
    sql.execute "SET @row_number = 0"
    sql.rows("SELECT (@row_number:=@row_number + 1) AS n,PersonnelID,Surname,Name,CASE WHEN Category LIKE '%management%' THEN 'Rm&T staff' ELSE Category END AS Category FROM IGFAE_Personnel ORDER BY RAND() LIMIT 10").each{
      println "|${it.n}|${it.PersonnelID}|${it.Surname}|${it.Name}|${it.Category}"
    }
  sql.close()

{{/groovy}}

You can currently see it in action. As you see, we defined username and password in the component. No need to enter them in clear text in the page containing the SQL query.

The relevant XWiki information is in the Script Component extension page.

Hope this helps!