What function do you use to connect to MSSQL from PHP 5.3.3?

Good morning, I've been using these http://us.php.net/manual/en/book.mssql.php but when we migrated to the newest php version I was forced to find an alternative solution. I've been told to start using SQLSRV extension so I started playing around with it but it  doesn't work for one of my queries and even doesn't return any errors.

This is the code:

 $sql = "EXEC SHOW_TICKET_VIEW_NEW1 '".$STATUS."','".$SHOW."','(".
$owners.")','".$ORDER."','".$search."'";
$result  = sqlsrv_query($dbh1, $sql, array(), array( "Scrollable" =>
SQLSRV_CURSOR_KEYSET )) or die ("Could not get results1.");
if (sqlsrv_num_rows($result) === false){
        echo "ERROR";
}

if( ($errors = sqlsrv_errors() ) != null)
{
   foreach( $errors as $error)
   {
          echo "SQLSTATE: ".$error[ 'SQLSTATE']."\n";
          echo "code: ".$error[ 'code']."\n";
          echo "message: ".$error[ 'message']."\n";
   }

}

All what I  get is Could not get results1.


Please advise.
joein610Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Ray PaseurConnect With a Mentor Commented:
I'm guessing "latest version" means PHP 5.3.3?  Have a look at these links:
http://us.php.net/manual/en/mssql.requirements.php
http://us.php.net/manual/en/mssql.installation.php
http://us.php.net/manual/en/mssql.configuration.php

Nothing jumped out here:
http://us.php.net/migration53
http://us.php.net/manual/en/migration53.removed-extensions.php

In short, it looks like there is no need to change to SQLSRV.  Just install the MSSQL and keep running.
0
 
joein610Author Commented:
You have to install the extension to be able to use the SQLSRV commands. In my case I had to download the extension file and attached it to my php.ini file:

[php.ini]
extension=php_sqlsrv_53_nts_vc9.dll

Downloads are available here:
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=ccdf728b-1ea0-48a8-a84a-5052214caad9&displaylang=en


I have no problems using it besides the query I provided before. No errors, nothing. Why ?
0
 
darren-w-Commented:
I use pdo:

Here is a connection class I use that gets its details from a ini file

Here is the ini file:

; this is an INI file
db_driver=mssql
db_user=[dbusename here]
db_password=****

[dsn]
host=[put server ip/url here]
dbname=[db name here]

[db_options]
;PDO::MYSQL_ATTR_INIT_COMMAND=set names utf8

[db_attributes]
ATTR_ERRMODE=ERRMODE_WARNING

Open in new window


Connection class:

<?php

/*
 * This class creates a single db connection
 *
 */
include_once 'dbSettings.php';

class DBC {

    private static $objInstance;
    private function __construct() {

    }

    private function __clone() {

    }
    public static function getInstance() {
        $settings = new dbSettings();
        $auth = $settings->getAuth();
        if (!self::$objInstance) {
            try {
                self::$objInstance = new PDO($auth[0], $auth[1], $auth[2], $settings->getOptions());
            } catch (Exception $e) {
                self::$objInstance->rollBack();
                echo "Failed: " . $e->getMessage();
            }
            foreach ($settings->getAttrib() as $k => $v) {
                self::$objInstance->setAttribute(constant("PDO::{$k}")
                        , constant("PDO::{$v}"));
            }
        }
        return self::$objInstance;
    }

    final public static function __callStatic($chrMethod, $arrArguments) {
        $objInstance = self::getInstance();
        return call_user_func_array(array($objInstance, $chrMethod), $arrArguments);
    }

}

?>

Open in new window


The class that loads the settings:

<?php


class dbSettings {

    private $options, $auth, $attrib;

    function dbSettings() {
        $this->setSettings();
    }

    public function setSettings() {
        $ini = "connectionSettings.ini";
        $parse = parse_ini_file($ini, true);
        $driver = $parse ["db_driver"];
        $dsn = "${driver}:";
        $user = $parse ["db_user"];
        $password = $parse ["db_password"];
        $options = $parse ["db_options"];
        $attributes = $parse ["db_attributes"];
        foreach ($parse ["dsn"] as $k => $v) {
            $dsn .= "${k}=${v};";
        }
        $t = array($dsn, $user, $password);
        $this->setOptions($options);
        $this->setAuth(array($dsn, $user, $password));
        $this->setAttrib($attributes);
    }

    private function setOptions($_value) {
        $this->options = $_value;
    }

    function getOptions() {
        return $this->options;
    }

    private function setAuth($_value) {
        $this->auth = $_value;
    }

    function getAuth() {
        return $this->auth;
    }

    private function setAttrib($_value) {
        $this->attrib = $_value;
    }

    function getAttrib() {
        return $this->attrib;
    }

}
?>

Open in new window


to use it/ ie to get a connection I

include the file:

include_once 'DBC.php';

get the connection:

$con = DBC::getInstance();

I then pass call to the connection:

$count = $con->exec("DELETE FROM fruit WHERE colour = 'red'");

(http://www.php.net/manual/en/pdo.exec.php)

http://www.php.net/manual/en/book.pdo.php

Darren
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Ray PaseurCommented:
Not sure why you are not getting error output.  I assume you have tested it with a deliberately induced error, right?  I am a little suspicious of this sort of thing:

if (sqlsrv_num_rows($result) === false){
        echo "ERROR";
}

Are you sure that the sqlsrv_num_rows() function will return FALSE  -- or maybe it will return zero, which is not the same as FALSE.
0
 
joein610Author Commented:
Are you sure that the sqlsrv_num_rows() function will return FALSE  -- or maybe it will return zero, which is not the same as FALSE.

http://msdn.microsoft.com/en-us/library/ee376931%28SQL.90%29.aspx
0
 
joein610Author Commented:
Also with PDO:

"This extension is not available anymore on Windows with PHP 5.3 or later. "

http://ca2.php.net/manual/en/ref.pdo-dblib.php
0
 
Ray PaseurCommented:
Are you hooked up with this: http://blogs.msdn.com/b/sqlphp/
0
 
darren-w-Commented:
On Windows, you should use SqlSrv, an alternative driver for MS SQL is available from Microsoft: » http://www.microsoft.com/sqlserver/2005/en/us/PHP-Driver.aspx . The documentation is available at » http://msdn.microsoft.com/en-us/library/cc296221.aspx
0
 
joein610Author Commented:
As I said. I have the SQLSRV extension already installed, up & running but it seems like it doesn't like my stored procedures and it doesn't even return any errors. Do I have to handle stored procedures other way than using the regular php_mssql extension?
0
 
darren-w-Commented:
Here is a snippet of a class I have just written that uses the regular mssql connection, its being written to be used on PHP v 4.8.8 though...

<?php
include_once 'includes/dbSettings.php';
$c = new WriteOut();



class WriteOut {

    var $stmt, $link,$dbs;

    function WriteOut() {
        $this->dbs = new dbSettings();
        $this->connect();
        $this->writeData();

    }

    function connect() {

        $this->setLink(mssql_connect($this->dbs->getHost(), $this->dbs->getDbUser(), $this->dbs->getPassword()));

        if
        (!$this->getLink()) {
            die('Could not connect: ' . mssql_error());
        }
        echo
        'Successful connection';
//lets test calling a procedure
        $rtn = null;
        mssql_select_db($this->dbs->getDbname(), $this->getLink());
        $this->setStmt(mssql_init('Qualification.spMultiplePathwaysSource', $this->getLink()));
    }

    function writeData() {
        $pid = 4353;
        $cstat = 2;
        mssql_bind($this->getStmt(), '@providerid', $pid, SQLINT4, false, false);
        mssql_bind($this->getStmt(), '@courseStatus', $cstat, SQLINT4, false, false);
        mssql_bind($this->getStmt(), '@ReturnMessage', $rtn, SQLVARCHAR, true);
        $test = mssql_execute($this->getStmt());
        $op = mssql_fetch_assoc($test);
        print "<pre>";
        print_r($op);
        print "</pre>";

        $this->closeLink();
    }

    function setLink($cnstr) {
        $this->link = $cnstr;
    }

    function getLink() {
        return $this->link;
    }

    function closeLink() {
        mssql_close($this->link);
        print "got here";
    }

    function setStmt($a_stmt) {
        $this->stmt = $a_stmt;
    }

    function getStmt() {
        return $this->stmt;
    }

}

?>

Open in new window


it obviously depends on some other classes, but gives the idea of calling a procedure this way
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.