KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

PDOStatement::getColumnMeta(): Driver does not support this function #275

Open Clara Gaudry opened this topic on on May 9, 2018 - 13 comments

Clara Gaudry commented on May 9, 2018

Hi,

I would like to connect to a Oracle database to make charts but have this warning:

Warning: PDOStatement::getColumnMeta(): SQLSTATE[IM001]: Driver does not support this function: driver doesn't support meta data in C:\wamp\www\Koolreport\koolreport\datasources\PdoDataSource.php on line 146

Also, i saw that only the following drivers support this method:

PDO_DBLIB
PDO_MYSQL
PDO_PGSQL
PDO_SQLITE

I'm using Instant Client Package version 12.2 of Oracle and php 5.6.35.

I'm a beginner with koolreport and would like to know where my mistake comes from.

Thank you

KoolReport commented on May 9, 2018

I will tell dev.team to check and come back to you

KoolReport commented on May 9, 2018

I confirm the issue with Oracle that GetColumnMeta() function of PDO is not supported. I will come back to you with a solution within 1 day.

David Winterburn commented on May 10, 2018

Hi Clara,

Please copy and replace the following code to the file C:\wamp\www\Koolreport\koolreport\datasources\PdoDataSource.php:

<?php
/**
 * This file contain class to handle pulling data from MySQL, Oracle, SQL Server and many others.
 *
 * @author KoolPHP Inc (support@koolphp.net)
 * @link https://www.koolphp.net
 * @copyright KoolPHP Inc
 * @license https://www.koolreport.com/license#mit-license
 */

namespace koolreport\datasources;
use \koolreport\core\DataSource;
use \koolreport\core\Utility;
use PDO;

class PdoDataSource extends DataSource
{
	static $connections;
	protected $connection;
	protected $query;
	protected $sqlParams;
	protected function onInit()
	{		
		$connectionString = Utility::get($this->params,"connectionString","");
		$username = Utility::get($this->params,"username","");
		$password = Utility::get($this->params,"password","");
		$charset = Utility::get($this->params,"charset");
		
		$key = md5($connectionString.$username.$password);
		if(PdoDataSource::$connections==null)
		{
			PdoDataSource::$connections = array();
		}
		if(isset(PdoDataSource::$connections[$key]))
		{
			$this->connection = PdoDataSource::$connections[$key];
		}
		else 
		{
			$this->connection = new PDO($connectionString,$username,$password);
			PdoDataSource::$connections[$key] = $this->connection;
		}
		if($charset)
		{
			$this->connection->exec("set names '$charset'");
		}
	}
	
	public function query($query,$sqlParams=null)
	{
		$this->query =  (string)$query;
		if($sqlParams!=null)
		{
			$this->sqlParams = $sqlParams;
		}
		return $this;
	}

	public function params($sqlParams)
	{
		$this->sqlParams = $sqlParams;
		return $this;
	}
	
	protected function bindParams($query,$sqlParams)
	{
		if($sqlParams!=null)
		{
			foreach($sqlParams as $key=>$value)
			{
				if(gettype($value)==="array")
				{
					$value = "'".implode("','",$value)."'";
					$query = str_replace($key,$value,$query);
				}
				else if(gettype($value)==="string")
				{
					$query = str_replace($key,"'$value'",$query);
				}
				else
				{
					$query = str_replace($key,$value,$query);
				}
			}
		}
		return $query;
	}

	protected function guessType($native_type)
	{
		$map = array(
			"character"=>"string",
			"char"=>"string",
			"string"=>"string",
			"str"=>"string",
			"text"=>"string",
			"blob"=>"string",
			"binary"=>"string",
			"enum"=>"string",
			"set"=>"string",
			"int"=>"number",
			"double"=>"number",
			"float"=>"number",
			"long"=>"number",
			"numeric"=>"number",
			"decimal"=>"number",
			"real"=>"number",
			"bit"=>"number",
			"boolean"=>"number",
			"datetime"=>"datetime",
			"date"=>"date",
			"time"=>"time",
			"year"=>"datetime",
		);
		
		$native_type = strtolower($native_type);
		
		foreach($map as $key=>$value)
		{
			if(strpos($native_type,$key)!==false)
			{
				return $value;
			}			
		}
		return "unknown";
	}

	protected function guessTypeFromValue($value)
	{
		$map = array(
			"float"=>"number",
			"double"=>"number",
			"int"=>"number",
			"integer"=>"number",
			"bool"=>"number",
			"numeric"=>"number",
			"string"=>"string",
		);

		$type = strtolower(gettype($value));
		foreach($map as $key=>$value)
		{
			if(strpos($type,$key)!==false)
			{
				return $value;
			}			
		}
		return "unknown";
	}
	
	public function start()
	{
		$query = $this->bindParams($this->query,$this->sqlParams);
		$stm = $this->connection->prepare($query);
		$stm->execute();

		$error = $stm->errorInfo();
		if($error[2]!=null)
		{
			throw new \Exception("Query Error >> [".$error[2]."] >> $query");
			return;
		}

		$metaData = array("columns"=>array());
		$numcols = $stm->columnCount();
		$driver = strtolower($this->connection->getAttribute(PDO::ATTR_DRIVER_NAME));
		$metaSupport = false;
		$metaSupportDrivers = array('dblib', 'mysql', 'pgsql', 'sqlite');
		foreach ($metaSupportDrivers as $supportDriver)
			if (strpos($driver, $supportDriver) > -1)
				$metaSupport = true;
		if (! $metaSupport) {
			$row = $stm->fetch(PDO::FETCH_ASSOC);
			$cNames = array_keys($row);
		}
		for($i=0;$i<$numcols;$i++)
		{
			if (! $metaSupport) {
				$cName = $cNames[$i];
				$cType = $this->guessTypeFromValue($row[$cName]);
			}
			else {
				$info = $stm->getColumnMeta($i);
				$cName = $info["name"];
				$cType = $this->guessType($info["native_type"]);

			}
			$metaData["columns"][$cName] = array(
				"type"=>$cType,
			);
			switch($cType)
			{
				case "datetime":
					$metaData["columns"][$cName]["format"] = "Y-m-d H:i:s";
					break;
				case "date":
					$metaData["columns"][$cName]["format"] = "Y-m-d";
					break;
				case "time":
					$metaData["columns"][$cName]["format"] = "H:i:s";
					break;
			}
		}
				
		$this->sendMeta($metaData,$this);
		$this->startInput(null);
		
		$numberColumnList = array();
		foreach($metaData["columns"] as $cName=>$cMeta)
		{
			if($cMeta["type"]=="number")
			{
				array_push($numberColumnList,$cName);
			}
		}
						
		
		while($row)
		{
			foreach($numberColumnList as $cName)
			{
					$row[$cName]+=0;
			}
			$this->next($row,$this);
			$row=$stm->fetch(PDO::FETCH_ASSOC);
		}			
		$this->endInput(null);
	}
}

Then try it with your Oracle connection again and let us know if it works for you. Thanks!

Clara Gaudry commented on May 14, 2018

Hi,

I don't have the previous error anymore but It doesn't work yet, i have the error : "Data column(s) for axis #0 cannot be of type string" while I only have numbers and dates in my table. I guess the axis #0 is the header of my Oracle's table. Could you help me?

KoolReport commented on May 14, 2018

Please specify the type of column:

BarChart::create(array(
    ...
    "columns"=>array(
        "dateColumn",
        "numberColumn"=>array(
            "type"=>"number", //<-- this is important
        )
    )
))
Clara Gaudry commented on May 15, 2018

I did, but it doesn't work.

LineChart::create(array(
        "dataStore"=>$this->dataStore('table'),
        "width"=>"100%",
        "height"=>"500px",
        "columns"=>array(
            "MF6_HD"=>array(
                "label"=>"Date",
		"type"=>"date"
            ),
            "MF6_DEBIT"=>array(
                "label"=>"Débit",
		"type"=>"number"
            ),
	    "MF6_TAUX"=>array(
                "label"=>"Taux",
		"type"=>"number"
            ),
        ),
        "options"=>array(
            "title"=>"PTM12",
        )
    ));
KoolReport commented on May 15, 2018

Does any error show? or it shows the same error as before?

Clara Gaudry commented on May 15, 2018

The same as before

David Winterburn commented on May 15, 2018

Hi Clara,

In your report's setup function, please use the process ColumnMeta to change the type of 'MF6_HD' to string while others to number like this:

use \koolreport\processes\ColumnMeta;
...
function setup() {
...
->pipe(new ColumnMeta(array(
    "MF6_HD"=>array(
        'type' => 'string',
    ),
    "MF6_DEBIT"=>array(
        'type' => 'number',
    ),
    "MF6_TAUX"=>array(
        'type' => 'number',
    ),
)))
->pipe($this->dataStore('table'));

}

Thanks!

Clara Gaudry commented on May 15, 2018

I still have the error and i've no idea where it could comes from.

public function setup()
    {
        $this->src('criter')
        ->query("select PTM_ID, MF6_HD, MF6_DEBIT, MF6_TAUX from HST_MES_FLUX_6 where PTM_ID = 12 and MF6_HD >= '01/04/2018'  MF6_HD < '04/04/2018' order by MF6_HD")
		
	->pipe(new ColumnMeta(array(
			"MF6_HD"=>array(
				'type' => 'string',
			),
			"MF6_DEBIT"=>array(
				'type' => 'number',
			),
			"MF6_TAUX"=>array(
				'type' => 'number',
			),
		)))
        ->pipe($this->dataStore('table'));
		
    }
David Winterburn commented on May 15, 2018

Hi Clara,

Please try the following code:

use \koolreport\processes\Map;
...
public function setup()
{
    $this->src('criter')
    ->query("select PTM_ID, MF6_HD, MF6_DEBIT, MF6_TAUX from HST_MES_FLUX_6 where PTM_ID = 12 and MF6_HD = '04/04/2018' order by MF6_HD")
        
    ->pipe(new Map(array(
        '{value}' => function($row, $metaData) {
            $row['MF6_HD'] = (string)$row['MF6_HD'];
            $row['MF6_DEBIT'] = (float)$row['MF6_DEBIT'];
            $row['MF6_TAUX'] = (float)$row['MF6_TAUX'];
            return array($row);
        }
    )))

    ->pipe(new ColumnMeta(array(
        "MF6_HD"=>array(
            'type' => 'string',
        ),
        "MF6_DEBIT"=>array(
            'type' => 'number',
        ),
        "MF6_TAUX"=>array(
            'type' => 'number',
        ),
    )))
    ->pipe($this->dataStore('table'));
}
LineChart::create(array(
    "dataStore"=>$this->dataStore('table'),
    "width"=>"100%",
    "height"=>"500px",
    "columns"=>array(
        "MF6_HD"=>array(
            "label"=>"Date",
        ),
        "MF6_DEBIT"=>array(
            "label"=>"Débit",
        ),
        "MF6_TAUX"=>array(
            "label"=>"Taux",
        ),
    ),
    "options"=>array(
        "title"=>"PTM12",
    )
));

Thanks!

Clara Gaudry commented on May 15, 2018

It works perfectly, thanks a lot for your patience and help !

David Winterburn commented on May 15, 2018

You are very welcome, Clara! And thank you for your tips.

Don't hesitate to contact us whenever you have any problem with our report framework and components. Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
solved

None