Sql Server connection

[therecluse26]

Hi, I read one of your other issues on here suggesting to create a new class for SQL Server queries, and I was able to do so by copying and modifying the MySQL report type and changing all of the mysql functions to fit the sqlsrv driver. At first, I had some issues, but was able to get rid of all the errors I was getting. The problem is now, regardless of what connection information I use (correct or incorrect), it simply throws out a blank report with no error and no data. I'm pretty stumped as to why this is, do you have any idea? Here's my config setting:

'sqlsrv'=>array(
    'host'=>'localhost',
    'connectionInfo'=>array(
        'UID'=>'username',
        'PWD'=>'password' ),
),

...and my SqlSrvReportType.php file

<?php
class SqlSrvReportType extends ReportTypeBase {
public static function init(&$report) {
    $environments = PhpReports::$config['environments'];

    if(!isset($environments[$report->options['Environment']][$report->options['Database']])) {
        throw new Exception("No ".$report->options['Database']." info defined for environment '".$report->options['Environment']."'");
    }

    //make sure the syntax highlighting is using the proper class
    SqlFormatter::$pre_attributes = "class='prettyprint linenums lang-sql'";

    $sqlsrv = $environments[$report->options['Environment']][$report->options['Database']];

    //default host macro to sqlsrv's host if it isn't defined elsewhere
    if(!isset($report->macros['host'])) $report->macros['host'] = $sqlsrv['host'];

    //replace legacy shorthand macro format
    foreach($report->macros as $key=>$value) {
        $params = $report->options['Variables'][$key];

        //macros shortcuts for arrays
        if(isset($params['multiple']) && $params['multiple']) {
            //allow {macro} instead of {% for item in macro %}{% if not item.first %},{% endif %}{{ item.value }}{% endfor %}
            //this is shorthand for comma separated list
            $report->raw_query = preg_replace('/([^\{])\{'.$key.'\}([^\}])/','$1{% for item in '.$key.' %}{% if not loop.first %},{% endif %}\'{{ item }}\'{% endfor %}$2',$report->raw_query);

            //allow {(macro)} instead of {% for item in macro %}{% if not item.first %},{% endif %}{{ item.value }}{% endfor %}
            //this is shorthand for quoted, comma separated list
            $report->raw_query = preg_replace('/([^\{])\{\('.$key.'\)\}([^\}])/','$1{% for item in '.$key.' %}{% if not loop.first %},{% endif %}(\'{{ item }}\'){% endfor %}$2',$report->raw_query);
        }
        //macros sortcuts for non-arrays
        else {
            //allow {macro} instead of {{macro}} for legacy support
            $report->raw_query = preg_replace('/([^\{])(\{'.$key.'+\})([^\}])/','$1{$2}$3',$report->raw_query);
        }
    }

    //if there are any included reports, add the report sql to the top
    if(isset($report->options['Includes'])) {
        $included_sql = '';
        foreach($report->options['Includes'] as &$included_report) {
            $included_sql .= trim($included_report->raw_query)."\n";
        }

        $report->raw_query = $included_sql . $report->raw_query;
    }

    //set a formatted query here for debugging.  It will be overwritten below after macros are substituted.
    $report->options['Query_Formatted'] = SqlFormatter::format($report->raw_query);
}

public static function openConnection(&$report) {
    if(isset($report->conn)) return;

    $environments = PhpReports::$config['environments'];
    $config = $environments[$report->options['Environment']][$report->options['Database']];

    //the default is to use a user with read only privileges
    $username = $config['user'];
    $password = $config['pass'];
    $host = $config['host'];
    $Database = $config['Database'];
    $connectionInfo = $config['connectionInfo'];

    //if the report requires read/write privileges
    if(isset($report->options['access']) && $report->options['access']==='rw') {
        if(isset($config['user_rw'])) $username = $config['user_rw'];
        if(isset($config['pass_rw'])) $password = $config['pass_rw'];
        if(isset($config['host_rw'])) $host = $config['host_rw'];
    }

    if(!($report->conn = sqlsrv_connect($host, $connectionInfo, $Database))) {
        throw new Exception('Could not connect to sqlsrv: '.sqlsrv_error());
    }

    /* if(isset($config['database'])) {
        if(!sqlsrv_select_db($config['database'],$report->conn)) {
            throw new Exception('Could not select sqlsrv database: '.sqlsrv_error($report->conn));
        }
    } */
}

public static function closeConnection(&$report) {
    if(!isset($report->conn)) return;
    sqlsrv_close($report->conn);
    unset($report->conn);
}

public static function getVariableOptions($params, &$report) {
    $query = 'SELECT DISTINCT '.$params['column'].' FROM '.$params['table'];

    if(isset($params['where'])) {
        $query .= ' WHERE '.$params['where'];
    }

    if(isset($params['order']) && in_array($params['order'], array('ASC', 'DESC')) ) {
        $query .= ' ORDER BY '.$params['column'].' '.$params['order'];
    }

    $result = sqlsrv_query($report->conn, $query);

    if(!$result) {
        throw new Exception("Unable to get variable options: ".sqlsrv_error());
    }

    $options = array();

    if(isset($params['all'])) $options[] = 'ALL';

    while($row = SQLSRV_FETCH_ASSOC($result)) {
        $options[] = $row[$params['column']];
    }

    return $options;
}

public static function run(&$report) {      
    $macros = $report->macros;
    foreach($macros as $key=>$value) {
        if(is_array($value)) {
            $first = true;
            foreach($value as $key2=>$value2) {
                //$value[$key2] = sqlsrv_real_escape_string(trim($value2));
                $value[$key2] = $value2;
                $first = false;
            }
            $macros[$key] = $value;
        }
        else {
            //$macros[$key] = sqlsrv_real_escape_string($value);
            $macros[$key] = $value;
        }

        if($value === 'ALL') $macros[$key.'_all'] = true;
    }

    //add the config and environment settings as macros
    $macros['config'] = PhpReports::$config;
    $macros['environment'] = PhpReports::$config['environments'][$report->options['Environment']];

    //expand macros in query
    $sql = PhpReports::render($report->raw_query,$macros);

    $report->options['Query'] = $sql;

    $report->options['Query_Formatted'] = SqlFormatter::format($sql);

    //split into individual queries and run each one, saving the last result        
    $queries = SqlFormatter::splitQuery($sql);

    $datasets = array();

    $explicit_datasets = preg_match('/--\s+@dataset(\s*=\s*|\s+)true/',$sql);

    foreach($queries as $i=>$query) {
        $is_last = $i === count($queries)-1;

        //skip empty queries
        $query = trim($query);
        if(!$query) continue;

        $result = sqlsrv_query($report->conn,$query);
        if(!$result) {
            throw new Exception("Query failed: ".sqlsrv_error($report->conn));
        }

        //if this query had an assert=empty flag and returned results, throw error
        if(preg_match('/^--[\s+]assert[\s]*=[\s]*empty[\s]*\n/',$query)) {
            if(SQLSRV_FETCH_ASSOC($result))  throw new Exception("Assert failed.  Query did not return empty results.");
        }

        // If this query should be included as a dataset
        if((!$explicit_datasets && $is_last) || preg_match('/--\s+@dataset(\s*=\s*|\s+)true/',$query)) {
            $dataset = array('rows'=>array());

            while($row = SQLSRV_FETCH_ASSOC($result)) {
                $dataset['rows'][] = $row;
            }

            // Get dataset title if it has one
            if(preg_match('/--\s+@title(\s*=\s*|\s+)(.*)/',$query,$matches)) {
                $dataset['title'] = $matches[2];
            }

            $datasets[] = $dataset;
        }
    }

    return $datasets;
}
}

[bluemothergoose]

Your SQLSRV_FETCH_ASSOC can't be referenced by itself like in MySQL driver for PHP. When you call mysql_fetch_assoc, it calls mysql_fetch_array and the mysql_fetch_assoc parameter is passed in by default. SQL Server driver for PHP on the other hand does not have this functionality so you must call it in this way:

sqlsrv_fetch_array($result,SQLSRV_FETCH_ASSOC)

Also, SQL server driver for PHP does not support sqlsrv_select_db like MySQL does; you can circumvent this by using mssql_select_db until you come up with a better solution.

Cheers

[therecluse26]

Goodness... that did it! How stupid of me, I've used that function a million times in the past.. I wasn't thinking. Sorry and thank you! Side note, do you think there would be any simple way within your code to pass the database parameter through from a particular sql file (maybe somehow in the comment section where variables are set?) other than the "USE" function? A lot of the databases I have to work with are Azure SQL dbs which don't support the USE statement.

[bluemothergoose]

Direct connections must be made when using Azure SQL since you can only reference the current database with the database parameter; A new database may only be chosen on a per-connection basis, so in order to switch db's on the fly, new connections must be made.

[bluemothergoose]

You may be able to parse through the sql file and pull out a specific keyword that you name as a variable, similar to how it pulls out other query keywords. How you choose to implement it is up to you though.

[jcvcms]

Does this work for SQL Express Server?

[jdorn]

I just pushed full PDO support today, so using SqlServer should no longer require a custom ReportType class.