Custom Database Method

 
 
For Databases, where InfoCaptor does not provide direct database adapter, you can build your own database adapter using the following approach.
 
With the below approach you can even override existing InfoCaptor provided adapters.
 
You need to define your custom adapters in the custom_connection_methods.php file
 
This file is located in the base directory and you can edit in notepad++ or other editors
 
 
1. File name : custom_connection_methods.php
 
This is the file where infocaptor enterprise will look for custom adapters.
 
2. $db_result["db_name_alias"]
 
The list of pre-defined database aliases are as follows
  • excel
  • access
  • mysql
  • postgresql
  • oracle
  • mssql
 
So by saying $db_result["mysql"]= function_name, we are telling InfoCaptor to use my custom defined method for the database type "mysql"
 
3. function_name : This could be any valid function name you can define. This should match exactly what you define next as the function name
 
4. $db_param : This is the database parameter array that infocaptor will pass to your custom method
 
5. $sql : This is the sql infocaptor will pass to your custom method.
 
 
The example below is for mysql database but you can model your adapter around the same structure.
 
For e.g As of this writing InfoCaptor Enterprise provides adapters for Excel, Access and MySQL
 
So if you wanted to define your own adapter for PostgreSQL you can replace the mysql functions with postgres equivalent functions.
 
 
You can start by copying the mysql functions and the array assigment and cloning it as a new adapter.
 
Next, replace the mysql strings and methods with postgres equivalents.
 
Example
1. replace with postgresql
2. Give a new name to your postgres adapter
3. 2 and 3 should be the same name
4. mysql_connect will be replaced by pg_connect
e.g format pg_connect("host=sheep port=5432 dbname=mary user=lamb password=foo");
 
5. You may not need this for postgresql
 
6. mysql_query will be replaced by pg_query
 
So replace the methods or modify the structure as you need but keep in mind that the final result this adapter should return should be of the following format
 
 
It should return an associative array containing "legends" and "data"
 
legends : Is the list of all column names
data : is the actual data organized in columns
 
Here is the sample code for MySQL
 
 
Here is the full code for the php file
 
<?php
 
 
$db_result["mysql"]="my_custom_mysql_adapter"; 
 
 
function my_custom_mysql_adapter(  $db_param  ,  $sql  )
{
try
{
 
_log(0,"Calling my_custom_mysql_adapter");
$db = mysql_connect($db_param["host"].":".$db_param["port"], $db_param["dbuser"], $db_param["dbpass"]);
if ($db) _log(0,"mysql connection success");
else _log(0,"mysql connection failed");
mysql_select_db($db_param["dbname"],$db);
$result = mysql_query($sql,$db);
if ($result === false) die("failed");
 
 
// Loop to handle all results
$row_id=0;
$column_names=array();
$col_data=array();
$row_limit=$db_param['row_limit'];
if (!isset($row_limit) ) $row_limit=10;
while( ($row = mysql_fetch_array($result)) && $row_id<$row_limit)
{
  //$row_data[$row_id]=array();
  // each key and value can be examined individually as well
  $col_id=0;
  //_log(0,print_r($row,true));
  foreach($row as $key => $value)
  {
    if (is_int($key)) continue;
    // _log(0,"key = {$key} val = {$value}");    
    $col_data[$col_id][$row_id]=utf8_encode($value);
$col_id++;
    if ($row_id==0)
{
  array_push($column_names,$key); //just capture column names only once
//array_push($row_data[$row_id],$value);
//  print "<br>Key: " . $key . " Value: " . $value;
  }
  $row_id++;
}
 
$content["legends"]=$column_names;
$content["data"]=$col_data;
_log(0, json_encode($content));
    return $content;
}
    catch (exception $e)
    {
          _log(0,"connection methods get_connection exception ".print_r($e,true));
  $content["legends"]=array("Error");
  $content["data"]=array(0);
  return $content;
    }
}
 
function _log($level, $msg)
{
 
 
global $gs;
    
 
if (!($gs["logging_enabled"]=='Y' && $gs["log_level"]<=$level)) return;
$dateStamp=date("Y_m_d", time());
//$dataStamp=md5( $dateStamp )."_".$dateStamp;
$fd = fopen("log/x_log_".$dateStamp.".log", "a");
// append date/time to message
$str = "[" . date("Y/m/d h:i:s", time()) . "] " . $msg."     session=".session_id();
// write string
fwrite($fd, $str . "\n");
// close file
fclose($fd);
@chmod("log/x_log_".$dateStamp.".log", 0600);
}
 
 
?>
Use online Web Dashboards in the cloud or download and host your own Dashboards