Solved

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

Posted on 2010-11-30
10
852 Views
Last Modified: 2012-05-10
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.
0
Comment
Question by:joein610
  • 4
  • 3
  • 3
10 Comments
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 34239334
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
 

Author Comment

by:joein610
ID: 34239476
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
 
LVL 13

Expert Comment

by:darren-w-
ID: 34239774
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34240216
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
 

Author Comment

by:joein610
ID: 34241363
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:joein610
ID: 34242620
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
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34247363
Are you hooked up with this: http://blogs.msdn.com/b/sqlphp/
0
 
LVL 13

Expert Comment

by:darren-w-
ID: 34247372
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
 

Author Comment

by:joein610
ID: 34249005
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
 
LVL 13

Expert Comment

by:darren-w-
ID: 34249424
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now