class FrxMSSQL in Forena Reports 8

Class FrxMSSQL

Plugin annotation


@FrxDriver(
  id="FrxMSSQL",
  name="Microsoft SQL Server Driver"
)

Hierarchy

class \Drupal\forena\FrxPlugin\Driver\DriverBase implements DriverInterface uses FrxAPI
- class \Drupal\forena\FrxPlugin\Driver\FrxMSSQL uses FrxAPI

Expanded class hierarchy of FrxMSSQL

1 string reference to 'FrxMSSQL'

DataSourceDefinitionForm::buildForm in src/Form/DataSourceDefinitionForm.php: [@inheritdoc}

File

src/FrxPlugin/Driver/FrxMSSQL.php, line 22: Oracle specific driver that takes advantage of oracles native XML support

Namespace

Drupal\forena\FrxPlugin\Driver

View source

class FrxMSSQL extends DriverBase {
  use FrxAPI;
  private $db;
  private $use_mssql_xml;

  /**
   * Object constructor
   *
   * @param string $uri
   *   Database connection string.
   * @param string $repos_path
   *   Path to location of data block definitions
   */
  public function __construct($name, $conf, DataFileSystem $fileSystem) {
    parent::__construct($name, $conf, $fileSystem);
    $this->db_type = 'mssql';
    $this->use_mssql_xml = FALSE;
    $uri = $conf['uri'];
    $this->debug = $conf['debug'];
    if ($conf['mssql_xml']) {
      $this->use_mssql_xml = TRUE;
    }
    if ($uri) {

      // Test for mssql suport
      if (!is_callable('mssql_connect')) {
        $this
          ->app()
          ->error('MSSQL support not installed.', 'MSSQL mssql support not installed.');
        return NULL;
      }
      try {
        ini_set('mssql.textlimit', 2147483647);
        ini_set('mssql.textsize', 2147483647);
        $db = mssql_connect($uri, $conf['user'], $conf['password']);
        $this->db = $db;
        if ($db) {
          mssql_select_db($conf['database'], $db);
          mssql_query("SET QUOTED_IDENTIFIER ON");
        }
      } catch (\Exception $e) {
        $this
          ->app()
          ->error('Unable to connect to database ' . $conf['title'], $e
          ->getMessage());
      }
    }
    else {
      $this
        ->app()
        ->error('No database connection string specified', 'No database connection: ' . print_r($conf, 1));
    }

    // Set up the stuff required to translate.
    $this->te = new SQLReplacer($this);
  }

  /**
   * Get data based on file data block in the repository.
   *
   * @param string $sql
   *   Query to execute
   * @param array $options
   *   Key/value pair or array containing parameter type ifnormation for the
   *   query.
   * @return \SimpleXMLElement | array
   *   Data from executed sql query.
   */
  public function sqlData($sql, $options = array()) {

    // Load the block from the file
    $db = $this->db;

    // Load the types array based on data
    $this->types = isset($options['type']) ? $options['type'] : array();
    $xml = '';

    // Load the types array based on data
    $this->types = isset($options['type']) ? $options['type'] : array();
    if ($sql && $db) {
      $sql = $this->te
        ->replace($sql);
      if ($this->use_mssql_xml) {
        $xml = $this
          ->mssql_xml($sql, 'table');
      }
      else {
        $xml = $this
          ->php_xml($sql);
      }
      if ($this->debug) {
        if ($xml) {
          $d = htmlspecialchars($xml
            ->asXML());
        }
        $this
          ->debug('SQL: ' . $sql, '<pre> SQL:' . $sql . "\n XML: " . $d . "\n</pre>");
      }
      return $xml;
    }
    else {
      return NULL;
    }
  }

  /**
   * Generate xml from sql using the provided f_forena
   *
   * @param string $sql
   *   SQL statement
   * @return \SimpleXMLElement
   *   XML Element
   */
  private function mssql_xml($sql, $block) {
    $db = $this->db;

    //$rs->debugDumpParams();
    $fsql = $sql . ' FOR XML AUTO';
    $rs = mssql_query($db, $fsql, array(
      $sql,
      '',
    ));
    if ($rs) {
      $row = mssql_fetch_row($rs);
      $xml_text = $row[0];
    }
    if ($xml_text) {
      $xml = new \SimpleXMLElement($xml_text);
      if ($xml
        ->getName() == 'error') {
        $msg = (string) $xml . ' in ' . $block . '.sql. ';
        $this
          ->app()
          ->error($msg . 'See logs for more info', $msg . ' in <pre> ' . $sql . '</pre>');
      }
    }
    if ($rs) {
      mssql_free_result($rs);
    }
    return $xml;
  }
  private function php_xml($sql) {
    $db = $this->db;
    $xml = new \SimpleXMLElement('<table/>');
    $rs = mssql_query($sql, $db);
    $rownum = 0;
    while ($row = mssql_fetch_assoc($rs)) {
      $rownum++;
      $row_node = $xml
        ->addChild('row');
      $row_node['num'] = $rownum;
      foreach ($row as $key => $value) {
        $row_node
          ->addChild(strtolower($key), htmlspecialchars($value));
      }
    }
    if ($rs) {
      mssql_free_result($rs);
    }
    return $xml;
  }

  /**
   * Perform search of tables.
   * @see FrxDataSource::searchTables()
   */
  public function searchTables($str) {
    $str .= '%';
    $db = $this->db;
    $sql = $this
      ->searchTablesSQL();
    $str = "'" . str_replace("'", "''", $str) . "'";
    $sql = str_replace(':str', $str, $sql);
    $rownum = 0;
    $rs = mssql_query($sql, $db);
    $tables = array();
    while ($row = mssql_fetch_assoc($rs)) {
      $tables[] = $row['table_name'];
    }
    if ($rs) {
      mssql_free_result($rs);
    }
    return $tables;
  }

  /**
   * Perform search of tables.
   * @see FrxDataSource::searchTables()
   */
  public function searchTableColumns($table, $str) {
    $str .= '%';
    $db = $this->db;
    $sql = $this
      ->searchTableColumnsSQL();
    $str = "'" . str_replace("'", "''", $str) . "'";
    $sql = str_replace(':str', $str, $sql);
    $table = "'" . str_replace("'", "''", $table) . "'";
    $sql = str_replace(':table', $table, $sql);
    $rownum = 0;
    $rs = mssql_query($sql, $db);
    $columns = array();
    if ($rs) {
      while ($row = mssql_fetch_assoc($rs)) {
        $columns[] = $row['COLUMN_NAME'];
      }
    }
    if ($rs) {
      mssql_free_result($rs);
    }
    return $columns;
  }

  /**
   * Implement custom SQL formatter to make sure that strings are properly escaped.
   * Ideally we'd replace this with something that handles prepared statements, but it
   * wouldn't work for
   *
   * @param string $value
   *   The value of the string replacement.
   * @param string $key
   *   The token name being replaced.
   * @param bool $raw
   *   True implies that data shold not be formatted.
   * @return string
   *   Formatted data
   */
  public function format($value, $key, $raw = FALSE) {
    if ($raw) {
      return $value;
    }
    $value = $this
      ->parmConvert($key, $value);
    if ($value === '' || $value === NULL) {
      $value = 'NULL';
    }
    elseif (is_int($value)) {
      $value = (int) $value;
      $value = (string) $value;
    }
    elseif (is_float($value)) {
      $value = (double) $value;
      $value = (string) $value;
    }
    else {
      $value = "'" . str_replace("'", "''", $value) . "'";
    }
    return $value;
  }

  /**
   * Destructor - Closes database connections.
   *
   */
  public function __destruct() {
    $db = $this->db;
    if ($db) {
      mssql_close($db);
    }
  }

}

Members

Name	Modifiers	Type	Description	Overrides
DriverBase::$block_ext	public	property
DriverBase::$block_extensions	public	property
DriverBase::$block_name	public	property
DriverBase::$block_path	public	property
DriverBase::$comment_prefix	public	property
DriverBase::$comment_suffix	public	property
DriverBase::$conf	public	property
DriverBase::$debug	public	property		1
DriverBase::$fileSvc	public	property
DriverBase::$name	public	property
DriverBase::$te	protected	property
DriverBase::$types	public	property
DriverBase::access	public	function	Implements the basic default security check of calling an access method. Overrides DriverInterface::access
DriverBase::buildFilterSQL	public	function	Build the SQL clause based on builder data.
DriverBase::data	public	function	Return data based on block definition. Overrides DriverInterface::data
DriverBase::getSQLInclude	public	function
DriverBase::listDataBlocks	public	function	Find all the blocks matching a provided search string
DriverBase::listDBBlocks	public	function	@TODO: Determine whether we still need this.
DriverBase::loadBlock		function	Load blcok data from filesystem Overrides DriverInterface::loadBlock
DriverBase::loadBlockFromFile	protected	function
DriverBase::parmConvert	public	function	Perform generic type conversion based on attributes.
DriverBase::parseSQLFile	public	function	Break the contents of a sql file down to its source.
DriverBase::parseXMLFile	public	function	Parse XML File contents into contents.
DriverBase::phpData	public	function
DriverBase::searchTableColumnsSQL	public	function
DriverBase::searchTablesSQL	public	function
DriverBase::tokens	public	function	Load tokens from block source
DriverBase::xmlData	public	function	Implement static XML functioin
FrxAPI::app	public	function	Returns containing application service
FrxAPI::currentDataContext	public	function	Get the current data context.
FrxAPI::currentDataContextArray	public	function
FrxAPI::dataManager	public	function	Returns the data manager service
FrxAPI::dataService	public	function	Return Data Service
FrxAPI::documentManager	public	function	Returns the fornea document manager
FrxAPI::error	public	function	Report an error
FrxAPI::getDataContext	public	function	Get the context of a specific id.
FrxAPI::getDocument	public	function	Get the current document
FrxAPI::getReportFileContents	public	function	Load the contents of a file in the report file system.
FrxAPI::innerXML		function	Enter description here...	1
FrxAPI::popData	public	function	Pop data off of the stack.
FrxAPI::pushData	public	function	Push data onto the Stack
FrxAPI::report	public	function	Run a report with a particular format.	1
FrxAPI::reportFileSystem	public	function	Get the current report file system.
FrxAPI::setDataContext	public	function	Set Data context by id.
FrxAPI::setDocument	public	function	Change to a specific document type.
FrxAPI::skins	public	function	Get list of skins.
FrxMSSQL::$db	private	property
FrxMSSQL::$use_mssql_xml	private	property
FrxMSSQL::format	public	function	Implement custom SQL formatter to make sure that strings are properly escaped. Ideally we'd replace this with something that handles prepared statements, but it wouldn't work for
FrxMSSQL::mssql_xml	private	function	Generate xml from sql using the provided f_forena
FrxMSSQL::php_xml	private	function
FrxMSSQL::searchTableColumns	public	function	Perform search of tables. Overrides DriverBase::searchTableColumns
FrxMSSQL::searchTables	public	function	Perform search of tables. Overrides DriverBase::searchTables
FrxMSSQL::sqlData	public	function	Get data based on file data block in the repository.
FrxMSSQL::__construct	public	function	Object constructor Overrides DriverBase::__construct
FrxMSSQL::__destruct	public	function	Destructor - Closes database connections.

You are here

class FrxMSSQL in Forena Reports 8

Plugin annotation

Hierarchy

File

Namespace

Members

API Navigation