This is my first DokuWiki extension. I created it for personal usage, but changed it a little bit and decided to contribute it to the public. I know that it is far away from perfect, but for my (our) problem it's doing its job quite well. Perhaps I will update it if I have some spare time, and if anybody is interested in updates. As you might have noticed, English is not my native language, so please excuse any typing mistakes, I'm doing my best

• 2008-08-07: Initial Release.

In the company where I work we have some tables, or data within a table, which is often wrong due to wrong csv imports which the customer delivers or something like that. Mostly this data can be identified by simple SQL-Selects in combination with Where-Statements.

As we run a DokuWiki here to have all information from a project in one space I decided to code this little extension.

What can this little extension do:

• Execute SQL-Statements and show Resultset
• If no Resultset is given (e.g. Update, Insert, Delete) it will show the number of affected rows.
• Works with many database types (MySQL, MicrosoftSQL, Oracle, PostgreSQL, Access, SQLite, CSV)
• Compare the Resultsets against a cached version of the same resultset, marking changed rows.
• Optionally have server connection string (hostname/user/pass/etc.) in a separate file. (e.g. localhost will be replaced with mysql:localhost:root:password:databasename
• Admin interface for clearing cache and manage database servers. (80% finished)

[[Type:Server:Username:Password:Database|Query|Refresh]]

So we have 3 Parameters with a Pipe between them.

Valid Parameters are:

refresh = Time in minutes. This parameter is not mandatory. It will give the interval in minutes how long a change on the resultset is marked as changed.

It is also possible to have the connection info in a separate file. Firstly for security reasons if you don't want the wikiusers to see the server connect parameters like username and password. Second for maintenance, if any of the server settings is changing you have to change all pages where you use a resultset… I will write more detailed information about that if anyone is interested.

[[mysql:localhost:root::information_schema|Select TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS From Tables where TABLE_ROWS  >= 0|5000]]

The output could look like this:

Create a folder named sqlcomp in your plugins directory. Create a file called syntax.php in that directory.

Copy/Paste the script into that file. That's all.

<?php
/**
 *
 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
 * @author     Christoph Lang <calbity@gmx.de>
 */
 
// based on http://www.dokuwiki.org/plugin:tutorial
 
/**
 *
 * Usage:
 * [[mysql:server:username:password:database|query|refresh]]
 * [[mssql:server:username:password:database|query|refresh]]
 * [[oracle:server:username:password:database|query|refresh]]
 * [[sqlite:unused:unused:unused:path to sqlite3 database|query|refresh]]
 * [[sqlcsv:unused:unused:unused:path to csv file|delimiter|refresh]]
 * [[sqlaccess:unused:unused:password:path to mdb file|query|refresh]]
 * [[postgresql:server:username:password:database|query|refresh]]
 */
 
/**
 * Disabled, to use that class in standalone mode...
 * // must be run within DokuWiki
 */
 
if (!defined('DOKU_INC')) die();
if (!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN', DOKU_INC . 'lib/plugins/');
require_once(DOKU_PLUGIN . 'syntax.php');
 
 
/**
 * All DokuWiki plugins to extend the parser/rendering mechanism
 * need to inherit from this class
 */
class syntax_plugin_sqlcomp extends DokuWiki_Syntax_Plugin {
 
    private $sPath = "data/cache/sql/";
    private $sConfig = "lib/plugins/sqlcomp/config.php";
 
    /*
    Layout
    */
    private $aMessages = array(
                              "error" => "<div id=\"error\" style=\"text-align:center; font-weight: bold; border: 2px solid #0f0;background-color: #f00; padding: 5px; margin: 5px\">%text%</div>\n",
                              "message" => "<div id=\"difference\" style=\"text-align:center; font-weight: bold; border: 2px solid #fd0;background-color: #ffd; padding: 5px; margin: 5px\">%text%</div>\n",
                              "pre" => "<table class=\"inline\">\n",
                              "post" => "</table>\n",
                              "th" => "<th class=\"row%number%\" style=\"%type%\">%text%</th>",
                              "td" => "<td class=\"col%number%\" style=\"%type%\">%text%</td>",
                              "tr" => "<tr class=\"row%number%\" style=\"%type%\">%text%</tr>\n",
                              "same" => "",
                              "new" => "border:2px solid green;",
                              "deleted" => "border:2px solid red;",
                              "changed" => "border:2px solid blue;"
                              );
    /*
    Default Language - German
    */
    private $aString = array(
                            //Number of affected Rows
                            "affected" => "Anzahl geänderter Zeilen",
                            //This Database Type is not yet Supported...
                            "nohandler" => "Dieser Datenbanktyp wird (noch) nicht unterstützt...",
                            //There are some differences in the table!
                            "difference" => "Es wurden Unterschiede in den Tabellen festgestellt!",
                            //Everything is allright.
                            "same" => "Alles in Ordnung.",
                            //The resultset is empty.
                            "empty" => "Das Resultset ist leer.",
                            //An unkown error occured!
                            "problem" => "Es ist ein unbekanntes Problem aufgetreten!",
                            //Cache is displayed, but new data could not be retrieved.
                            "cache" => "Cache wird angezeigt, aber neue Daten konnten nicht abgerufen werden.",
                            //Cache was refreshed, or table was collected for the first time.
                            "first" => "Der Cache wurde soeben erneuert, oder die Tabelle wurde das erste Mal abgerufen.",
                            //New data could not be retrieved.
                            "connection" => "Die neuesten Daten konnten nicht abgerufen werden.",
                            //The data is not valid. Please review your connection settings!
                            "wrong" => "Die eingegebenen Daten sind ungültig! Bitte Überprüfen!"
                            );
 
    private $defaultRefresh = 1;
 
    function getInfo() {
        return array(
        'author'  => 'Christoph Lang',
        'email'   => 'calbity@gmx.de',
        'date'    => '2008-07-10',
        'name'    => 'SQLCOMP Plugin',
        'desc'    => 'This plugin let you display reultsets from various databases and show changes.',
        'url'     => 'http://www.google.de'
        );
    }
 
    public function query($query){
      $temp = null;
      $data = $this->handle($query,"","",$temp);
      return $this->_query($data,"csv");       
    }
 
    public function __construct(){      
    }
 
    private function _error($text){
      return str_replace("%text%",$text,$this->aMessages["error"]);      
    }
 
    private function _message($text){
      return str_replace("%text%",$text,$this->aMessages["message"]);
 
    }
 
 
 
    private function _sqlaccess($Server,$User,$Pass,$Database,$Query){
 
        if(!$connection = odbc_connect("DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=$Database", "ADODB.Connection", $Pass, "SQL_CUR_USE_ODBC") or false)
          throw new Exception($this->aString["problem"]);
 
        $rs = odbc_exec($connection,$Query);
 
        $dbArray = array();
        while ($row = odbc_fetch_array($rs))
          $dbArray[] = $row;
 
        odbc_close($connection);
        return $dbArray;
 
    }
    private function _postgresql($Server,$User,$Pass,$Database,$Query){
 
        if(!$connection = pg_connect("host=".$Server." dbname=".$Database." user=".$User." password=".$Pass) or false)
          throw new Exception($this->aString["problem"]);
 
        $rs = pg_exec($Query);
        $dbArray = pg_fetch_array($result, NULL, PGSQL_ASSOC);
 
        pg_close($connection);
        return $dbArray;
 
    }
 
    private function _mysql($Server,$User,$Pass,$Database,$Query){
 
        if(!$connection = mysql_connect($Server, $User, $Pass) or false)
          throw new Exception(mysql_error());
 
        mysql_select_db($Database, $connection);
        $rs = mysql_query($Query);
        $dbArray = array();
 
        if($rs === true)
          $dbArray[] = array( $this->aString["affected"] => mysql_affected_rows ($connection));
        else
          while ($row = mysql_fetch_assoc($rs))
            $dbArray[] = $row;
 
 
        mysql_close($connection);
        return $dbArray;
 
    }
    private function _mssql($Server,$User,$Pass,$Database,$Query){
 
        if(!$dbhandle = mssql_connect($Server, $User, $Pass))
          throw new Exception($this->aString["problem"]);
 
        mssql_select_db($Database, $dbhandle);
 
        $rs = mssql_query($Query);
 
        $dbArray = array();
 
        if($rs === true)
          $dbArray[] = array( $this->aString["affected"] => mssql_rows_affected ($connection));
        else
          while ($row = mssql_fetch_assoc($rs))
            $dbArray[] = $row;
 
        mssql_close($dbhandle);
        return $dbArray;
 
    }
 
    private function _oracle($Server,$User,$Pass,$Database,$Query){
          throw new Exception($this->aString["nohandler"]);      
    }
 
    private function _sqlcsv($Server,$User,$Pass,$Database,$Query){  
 
        if(!$handle = fopen($Database,"r"))
          throw new Exception($this->aString["nohandler"]);    
 
        $dbArray = array();
        $keys = fgetcsv ( $handle , 1000, $Query);
 
        while ($row = fgetcsv ( $handle , 1000, $Query)){
          $temprow = array();
          foreach($row as $key => $value)
            $temprow[$keys[$key]] = $value;
 
          $dbArray[] = $temprow;
 
        }
 
        fclose($handle);
        return $dbArray;
 
    }
 
    private function _sqlite($Server,$User,$Pass,$Database,$Query){
 
        $dbHandle = new PDO('sqlite:'.$Database);    
 
        $result = $dbHandle->query($Query);
        if(!$result)
          throw new PDOException;
        $dbArray = array();
 
        if($result->rowCount() > 0)
          $dbArray[] = array( $this->aString["affected"] => $result->rowCount() );
        else
          while($row = $result->fetch(PDO::FETCH_ASSOC))
            $dbArray[] = $row;
 
        return $dbArray;
 
    }
    private function _debug($data){
 
        $sResponse = "";
        foreach($data as $key => $value)
            $sResponse .= "".$key . "=> " .$value ."<br/>\n";            
        return $sResponse;
 
    }
 
    private function _verifyInput($data){
      if(!is_array($data))
        return false;
      if(count($data) != 7)
        return false;
      return true;    
    }
 
    private function _load($filename){
 
        $Cache = null;
        $Update = true;
        if(file_exists($filename)){
          $Cache = file_get_contents($filename);  
          $Cache = unserialize($Cache);
 
          $Update = $Cache["Update"];
          if(time() > $Update)
            $Update = true;
          else
            $Update = false;
          $Cache = $Cache["Table"];    
        }        
 
        return array($Update,$Cache);
    }
 
    private function _save($filename,$rs,$timestamp){
        $timestamp = (time() + ($timestamp*60));
        $Cache["Update"] = $timestamp;
        $Cache["Table"] = $rs;
 
        $Cache = serialize($Cache);
 
        $handle = fopen($filename,"w");
        fwrite($handle,$Cache);
        fclose($handle);
 
 
    }
    private function array2csv($data){
 
      $sResponse = "";
 
      $keys = array_keys($data[0]);
      $sResponse .= implode(";",$keys)."\n";
      foreach($data as $row)
        $sResponse .= implode(";",$row)."\n";
 
 
      return $sResponse;
 
    }
 
 
    private function _query($data,$type=null) {      
 
        //return $this->_debug($data);
 
        if(!$this->_verifyInput($data))
          return $this->_error($this->aString["wrong"]);
 
        if(!is_dir($this->sPath))
          mkdir($this->sPath);
 
        $filename = $this->sPath.md5($data[0].$data[1].$data[2].$data[3].$data[4].$data[5]);
 
        $Cache = $this->_load($filename);
        $Update = true;
        if(is_array($Cache)){
          $Update = $Cache[0];
          $Cache = $Cache[1];
        }
 
        try{  
          switch($data[0]){
            case "mysql": $rs = $this->_mysql($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "mssql": $rs = $this->_mssql($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "oracle": $rs = $this->_oracle($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "sqlite": $rs = $this->_sqlite($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "sqlaccess": $rs = $this->_sqlaccess($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "postgresql": $rs = $this->_postgresql($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            case "sqlcsv": $rs = $this->_sqlcsv($data[1], $data[2], $data[3],$data[4],$data[5]); break;
            default: return $this->_error($this->aString["nohandler"]);
          }
        }catch(Exception $ex){
          $sResponse = $this->_error($this->aString["problem"]);
          if(isset($Cache)){
            $sResponse = $this->_print($Cache);    
            $sResponse .= $this->_error($this->aString["cache"]);
          }
          return $sResponse;
        }    
 
        if ($rs === false){
          return $this->_error($this->aString["empty"] );
        }
 
        if(isset($type) && $type == "csv")
          return $this->array2csv($rs);
 
        $difference = $this->_difference($Cache,$rs);
        $sResponse = $difference[0];    
 
        if($Update && isset($rs)){
          $this->_save($filename,$rs,$data[6]);      
 
        }  
 
        $sResponse .= $difference[1];      
 
 
        return $sResponse;
    }
    function _print($array){
 
        $i = 0;
 
        $th = "";
        $td = "";
        $tr = "";
        if(!isset($array[0]))
          return $this->_error($this->aString["problem"]);
 
        $temp = array_keys($array[0]);
        foreach($temp as $column){
          if($column == "type")
            continue;  
          $th .= str_replace(array("%number%","%text%","%type%"),array(0,$column,""),$this->aMessages["th"]);      
        }
        $tr = str_replace(array("%number%","%text%","%type%"),array(0,$th,""),$this->aMessages["tr"]);
 
        foreach($array as $row) {
 
          $j = 0;
          $td = "";
          if(!isset($row["type"]))
            $row["type"] = $this->aMessages["same"];
 
          foreach($row as $key => $Value){          
            if($key == "type")
              continue;  
            $td .= str_replace(array("%number%","%text%","%type%"),array($j,$Value,$row["type"]),$this->aMessages["td"]);
            $j++;            
          }
          $tr .= str_replace(array("%number%","%text%","%type%"),array($i,$td,$row["type"]),$this->aMessages["tr"]);
          $i++;          
        }
 
        $sResponse = $this->aMessages["pre"];
        $sResponse .= $tr;        
        $sResponse .= $this->aMessages["post"];
 
        return $sResponse;
 
 
    }
 
 
    function _difference($Cache,$New){
 
            if($New == $Cache){
              return array($this->_print($New),"");
              return array($this->_print($New),$this->_message($this->aString["same"]));
            }
 
            if(!isset($New) && isset($Cache))
              return array($this->_print($Cache),$this->_message($this->aString["difference"]));
 
            if(isset($New) && !isset($Cache))
              return array($this->_print($New),$this->_message($this->aString["first"]));
 
            if(count($New) <= 0)
              return array($this->_print($Cache),$this->_message($this->aString["connection"]));
 
            $Max = count($Cache);
            if(count($New) > count($Cache))
              $Max = count($New);
 
            $PrintArray = array();        
 
            for($i=0; $i < $Max; $i++){
              if(isset($Cache[$i]) && !isset($New[$i]))
                $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["deleted"]));
 
              if(!isset($Cache[$i]) && isset($New[$i]))
                $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["new"]));
 
              if(isset($Cache[$i]) && isset($New[$i])){
                if($Cache[$i] != $New[$i]){
                  $PrintArray[] = array_merge($Cache[$i],array("type" => $this->aMessages["changed"]));
                  $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["changed"]));
                }else
                  $PrintArray[] = array_merge($New[$i],array("type" => $this->aMessages["same"]));
 
              }                
 
            }
 
            return array($this->_print($PrintArray),$this->_message($this->aString["difference"]));
 
    }
 
    function connectTo($mode) {
        $this->Lexer->addSpecialPattern('\[\[mysql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[mssql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[oracle\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlite\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlaccess\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[postgresql\:.*?\]\]', $mode, 'plugin_sqlcomp');
        $this->Lexer->addSpecialPattern('\[\[sqlcsv\:.*?\]\]', $mode, 'plugin_sqlcomp');
 
        if(!file_exists($this->sConfig))
          $this->_createConfig();  
 
        include($this->sConfig);
 
        foreach($sqlcomp as $key => $value)
          $this->Lexer->addSpecialPattern('\[\['.$key.'.*?\]\]', $mode, 'plugin_sqlcomp');
 
 
    }
    function _createConfig(){  
 
        $sContent = "";
        $sContent .= "<?php\n";
        $sContent .= "//Sample Configfile\n";
        $sContent .= "//Add as many servers as you want here...\n";
        $sContent .= '$sqlcomp["localhost"] = "mysql:localhost:root::information_schema";';
        $sContent .= '$sqlcomp["sampleconnection"] = "sqltype:servername:username:password:database";';
        $sContent .= "\n?>\n";
 
        $handle = fopen($this->sConfig,"w");
        fwrite($handle,$sContent);
        fclose($handle);
 
    }
 
    function getType() { return 'substition'; }
 
    function getSort() { return 267; }
 
    function handle($match, $state, $pos, &$handler) {
 
        $temp = $match;
        $match = substr($match,2,-2);
 
        $MyData = array();       
        $match = explode("|",$match);   
 
        if(file_exists($this->sConfig))
          include($this->sConfig);
 
        foreach($sqlcomp as $key => $value)
          if($key == $match[0])
            $match[0] = $value;
 
        $MyData =  explode(":",$match[0]);
        $MyData[] = $match[1];    
 
        if(isset($match[2]))
          $MyData[] = $match[2];    
        else
          $MyData[] = $this->defaultRefresh;  
 
        for($i=0;$i < 5; $i++)
          $MyData[$i] = str_replace(" ", ":",$MyData[$i]);
 
        return $MyData;
 
    }
 
    function render($mode, &$renderer, $data) {
 
        if ($mode == 'xhtml') {          
            $renderer->doc .= $this->_query($data);               
            return true;
        }
        return false;
    }
}

[[sqlaccess:ee:ee:mdp:"C:\\bd1.mdb"|SELECT * FROM T_DOSSIER_MAIN|5000]]

The Problem is I use 2 separators for the parameters. | for grouping parameters|sql|refresh time… and : for selecting the different parameters like sqltype,username,….
So the : is already used and cannot be used within an parameter.
I faced the same problem too, and did a quick and dirty solution that worked at least for me, try:

[[sqlaccess:ee:ee:mdp:C \bd1.mdb|SELECT * FROM T_DOSSIER_MAIN|5000]]

This should work, as the space between C and \ will (normally) be filled with a :.

Try that and let me know if that worked. Btw: “Die eingegebenen Daten sind ungültig! Bitte überprüfen!” means “The data you entered is wrong because of wrong syntax/parameters. Please check them again!”

This works really well !!

For example if you get some strange characters in the result like: Clotur�es �tique

Please check the following. Is the syntax.php file from sqlcomp encoded in UTF-8? If not, try to save it in UTF-8 format. If so, UTF-8 strings should be handled correctly. If it still does not work, try replacing:

$renderer->doc .= $this->_query($data);    

with:

$renderer->doc .= utf8_encode($this->_query($data));    

Hope that fixes your problem

The trick you expose fixes this problem really well! I just have to say thank you for this great plugin!

Replace code in syntax.php

$renderer->doc .= $this->_query($data);    

with

$renderer->doc .= utf8_encode($this->_query($data));    

I really think this should go into the code (OliverG).

Hi, Nice plugin. I've added a security note and a php5 dependency to your information at the top of this page. Also, you might want to check out the plugin localization functions to move your strings out of the plugin itself. I didn't see how your refresh system worked, however you might want to take a look at the way DokuWiki handles RSS feeds (see, inc/parser/metadata.php – handles “metadata” in renderer and sets date|valid|age key). — Christopher Smith 2008/11/19 02:43

Hi, nice plugin. I was wondering if the config.php file is actually used by it. It would be nice not to have to post the entire connection data, every time a query is made. But I did not find out how to the use the plugin without it.

Edit: Sorry, now I found you wrote that it is possible, can you just give an example? Thanks

If you already have the extension running in your dokuwiki, just edit the file /lib/plugins/sqlcomp/config.php

This is the default content of the config.php file:

<?php
//Sample Configfile
//Add as many servers as you want here...
$sqlcomp["sampleconnection"] = "sqltype:servername:username:password:database";
?>

Open the file with a text editor and change the entry or add as many new entrys as you like. You must have access to the filesystem to edit this file, as for now no edit from the admin menu is possible. Maybe that will be integrated in a later version.

For example:

$sqlcomp["MyLocalMySQLServer"] = "mysql:localhost:root:secretpassword:cdcol";
$sqlcomp["microsoftServer"] = "mssql:127.0.0.1:root:secretpassword:database_with_some_stuff";

If you have finished editing your config.php file, you can start SQL-Querys from within DokuWiki with the following syntax:

[[MyLocalMySQLServer|SELECT titel FROM cds|5]]
or
[[microsoftServer|SELECT * FROM table_test|10]]

My extension will automatically expand the above syntax into the following syntax, which matches the syntax of the plugin:

[[mysql:localhost:root:secretpassword:cdcol|SELECT titel FROM cds|5]]
or
[[mssql:127.0.0.1:root:secretpassword:database_with_some_stuff|SELECT * FROM table_test|10]]

So, I hope that helps you. I know it's far from perfect, and if I sometime have enough free time I will make everything much more easier… but for now atleast it works.

As always: If you find any spelling mistakes, please correct them

Thanks! Works really fine

BEWARE: In the current version of this plugin the database alias name, as given in config.php, would be fully matched again all Dokuwiki links and so even match normal links. E.G. if you configre to have an alias named “db” connecting to somewhere, the plugin would also handle links like "[[dbfields]]" which is not wanted. To prevent this you must change the following lines in the syntax.php

        foreach($sqlcomp as $key => $value)
          $this->Lexer->addSpecialPattern('\[\['.$key.'.*?\]\]', $mode, 'plugin_sqlcomp');

to this

        foreach($sqlcomp as $key => $value)
          # IMPORTANT CHANGE TO DISTINGUISH BETWEEN LINKS AND CONFIGS
          $this->Lexer->addSpecialPattern('\[\['.$key.'\|.*?\]\]', $mode, 'plugin_sqlcomp');

– o.geisen/2011-07-19

Could you please code this:

I want to read a MySQL table. Some fields of this table contain HTML links. These links should be visible as clickable links in the result, not as text. Best regards. Peter

Peter, I needed something similar. I have a DB table that contains a list of server asset tags, IP addresses, and Names. I wanted clickable links on the wiki. In this case I wrote a View in MySQL that parsed out the data the way I wanted it and only used sqlcomp to display the view.

   
SELECT Matrix.Name AS Name,
  CONCAT('<a href="https://', Matrix.DracIP, '">', Matrix.DracIP,'</a>') AS DracIP,Matrix.Asset AS Asset                                                        
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'

I'm trying to make this work with data plugin. The sqlite database resides in ”/data/cache/dataplugin.sqlite” but I get an error doing queries: “Es ist ein unbekanntes Problem aufgetreten!”, which according to google is Unknown error.

I tried the following, to no avail:

[[sqlite::::data/cache/dataplugin.sqlite|select * from pages limit 10|5]]
[[sqlite::::"data/cache/dataplugin.sqlite"|select * from pages limit 10|5]]
[[sqlite::::"/public_html/kambing/data/cache/dataplugin.sqlite"|select * from pages limit 10|5]]

How is this plugin used with sqlite?

Solution:

The first example you have provided should work, I have checked that for myself:

[[sqlite::::data/cache/dataplugin.sqlite|select * from pages limit 10|5]]

I think the problem is you are either using an old sqlite 2 database or the file is not available. My plugin only supports sqlite3 databases. The Problem is sqlite2 and sqlite3 is incompatible. Check http://www.sqlite.org/version3.html for more information.

Tokenizer fails on windows paths (C:/path/file/…) for sqlite database. I patched the plugin like this (this hack is really bad …) Better specify escaping!

    private function _query($data,$type=null) {      
		// dirty fix for windows drive letter... 
		if( count($data) == 8 ) {
		  $data[4] = $data[4] . ':' . $data[5];
		  for($i = 5; $i < 7; ++$i) {
			$data[$i] = $data[$i+1];
		  }
		  unset($data[7]);
		}

Or you could just use the same trick as on Notes For Microsoft Environment And MDS Ms Access DB. Look a few points above. It's the same problem as if you're using a access DB.

Let us admit that a domain uses two databases, DB1 and DB2. In PHP, for example, we shall connect on DB1 with the following code:

$hostname=’localhost’; $username=’xxxxx’; $password=’ ?????’; $database ='mydomain:DB1’; $mysqli = new mysqli($hostname, $username, $password, $database);

Note the $database parameter indicating the name of domain and the name of the target database separated by one :

With SqlComp, just code a connection string in config.php including the name of the domain and the name of the target database separated by a blank this time !

Example : $sqlcomp[“mydomain”] = “mysql:localhost:xxxx:?????:mydomain DB1”;

I was getting mkdir errors and then fopen errors. I do not know if this is because my data directory is outside of webroot or if this plugin only works from certain directories or if it is some PHP config problem I have. I had to get help from a PHP guru. Using the full path instead of a relative path fixed my problem.

class syntax_plugin_sqlcomp extends DokuWiki_Syntax_Plugin {
 
    private $sPath = "";
    private $sConfig = "";
 
    /*
    Layout
    */

…

    public function __construct(){      
      $this->sPath = DOKU_INC . "data/cache/sql/";
      $this->sConfig = DOKU_INC . "lib/plugins/sqlcomp/config.php";
    }

Empty Pages

No pages any more after copy this syntax.php in the lib\plugins folder :( When I move the sqlcomp folder away from DokuWiki, than I see all my pages again. But when I let the sqlcom folder in plugins, then I can't see any from my pages. Every page looks empty. Have anyone the same or have a solution for me?

I think that is a problem with your PHP Version or your Installation! It seems that there is a PHP Error. Please check again your Installation and see if there are any errors in your Logfile. A blank page would result if there is a error and Error-Reporting is disabled. — Christoph Lang

OK, I found the copy and paste error from the script

After setting this in doku.php

• ini_set( 'display_errors', true );
• error_reporting( E_ALL );

“Parse error: syntax error, unexpected '”' in /usr/local/nagios/share/dokuwiki/lib/plugins/sqlcomp/syntax.php on line 80”

And now everything is working fine? — Christoph Lang

Yes, it works now, then the umlauts doesn't works after the copy paste. With the changed under doku.php I saw the error in your script and changed it. Thanks…/Wiede

Refresh/Renew

I added a query in Wiki and looks great, but when I changed the DB entries then I don't see the changed also in the wiki page. I thought that happend automatically. So I added also the refreh time now with 1 but the same….no refresh from the query. Is it possible that the query witll automatically run when I open the page? /Wiede

Insert somewhere on your page:

~~NOCACHE~~

Dokuwiki is caching the pages. So only the plugin's cache is used and not dokuwiki's cache. — Christoph Lang

perfect, thanks /wiede

Basics? I added the Plugin in my WIKI and addes a Query like this:

[[mssql:Servername.Domain.de:SQLuser:PassWD:Database|Query|5000]]

and I get the following:

Fatal error: Call to undefined function mssql_connect() in D:\WEKUwiki\htdocs\lib\plugins\sqlcomp\syntax.php on line 166

What did I wrong?

Your PHP-Setup doesn't know the method mssql_connect. Have a look on this pages: http://php.net/manual/de/function.mssql-connect.php and http://msdn.microsoft.com/en-us/library/cc793139%28SQL.90%29.aspx - There are the minimum PHP-Version requirements and some other hints to get it running. — lupo49M. S.
lupo49

DE / NRW / Sauerland 2010/10/05 20:58

Exactly like lupo49 said: The problem is your server and not the plugin. Check his links to get it working by enabling mssql-connect in your server configuration.

What about adding some rendering options like table heading names, or column configs?

This function wasn't working for me, I've made the following changes to make it work:

    private function _postgresql($Server,$User,$Pass,$Database,$Query){
 
        if(!$connection = pg_connect("host=".$Server." dbname=".$Database." user=".$User." password=".$Pass) or false)
          throw new Exception($this->aString["problem"]);
 
        $rs = pg_query($Query);
        $dbArray = pg_fetch_all($rs);
 
        pg_close($connection);
        return $dbArray;
 
    }

Grtz, Jan

The function to read from an Oracle DB was not implemented in the code. So, I have written it. Simply change the function _oracle with this one:

private function _oracle($Server,$User,$Pass,$Database,$Query){
	if(!$connection = oci_connect($User, $Pass, $Server) or false)
		throw new Exception(oci_error());
 
	// execute query
	$rs = oci_parse($connection, $Query);
	oci_execute($rs);
	$dbArray = array();
 
	if($rs === true)
		$dbArray[] = array($this->aString["affected"] => oci_affected_rows($connection));
	else
		while($row = oci_fetch_assoc($rs))
			$dbArray[] = $row;
 
	oci_free_statement($rs);
	oci_close($connection);
	return $dbArray;
}

Attention: to use this function, first you have to install the oracle libraries (for example oracle instant client) on your webserver. I have managed this copying the content of the oracle instantclient-basic-nt-xxx into my apache\bin folder. Then you have to activate the php_oci8 extension in your php.ini file.

- Sandro Santinato/2011-08-24