DokuWiki

It's better when it's simple

User Tools

Site Tools


plugin:sqlcomp:discussion

sqlcomp Plugin

Discussion for SQLCOMP plugin

Notes For Microsoft Environnement And MDS Ms Access DB

Bad syntax

[[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 !!

Feature request

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'

SQLite sample?

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.

SQLite and Windows

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.

How to connect a MySQL database in a domain using several different bases?

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”;

Refresh/Renew page when db changes

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

mssql

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. — lupo49 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.
msmsql-connect does not exist in php >= 7.0 FIXME –> cf Patch Proposal down on this page

Table rendering options

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

Sample for MSSQL on Windows-Server

PHP Version 5.6, Drivers from Microsoft installes (SQLSRV)
No connection possible

[[mssql:SRV2012R2:sa:xyz:testdb|select top 5 kundennumm,name from kunde]]

The Microsoft SQL-Drivers on Windows need sqlsv not mssql !!! https://docs.microsoft.com/de-de/sql/connect/php/step-4-connect-resiliently-to-sql-with-php

PHP 7 / 7.1

Patch for SQL SRV on PHP 7

Setup : (cool M$ do some howto better on Linux than on M$) For exemple on Centos :

https://www.microsoft.com/en-us/sql-server/developer-get-started/php/rhel/step/1.html

Nota on page 2 - Step 2.1 simply use that :

yum install php-sqlsrv
 
	private function _mssql($Server,$User,$Pass,$Database,$Query,$Opts){
 
		/*
		Connect to the local server using Windows Authentication and specify
		the AdventureWorks database as the database in use. To connect using
		SQL Server Authentication, set values for the "UID" and "PWD"
		 attributes in the $connectionInfo parameter. For example:
		$connectionInfo = array("UID" => $uid, "PWD" => $pwd, "Database"=>"AdventureWorks");
		*/
		$serverName = $Server;
		$connectionInfo = array( 
				"Database" => $Database,
				"Uid" => $User,
				"PWD" => $Pass,
				);
		$conn = sqlsrv_connect( $serverName, $connectionInfo);
 
		if( $conn )
		{
			 echo "Connection established.\n";
		}
		else
		{
			 throw new Exception($this->getLang("problem mssql_connect ? " + print_r( sqlsrv_errors(), true) ));
			// die( print_r( sqlsrv_errors(), true));
		}
 
		//-----------------------------------------------
		// Perform operations with connection.
		//-----------------------------------------------
 
	$stmt = sqlsrv_query( $conn, $Query);
 
 
	$dbArray = array();
 
 
	if($stmt=== true) {
		$dbArray[] = array( $this->getLang("affected") => sqlsrv_rows_affected ($stmt));
	} else {
	while ($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) )
		$dbArray[] =  $row;
 
 
 
		/* Close the connection. */
		sqlsrv_close( $conn);
 
 
		return $dbArray;
 
	}
    }
 

In todo : warning on param array

iconv ... at least a try ./
plugin/sqlcomp/discussion.txt · Last modified: 2018-08-28 19:21 by jm_zz

Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International
CC Attribution-Share Alike 4.0 International Donate Powered by PHP Valid HTML5 Valid CSS Driven by DokuWiki