Solved

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

Posted on 2010-11-30
10
874 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
10 Comments
 
LVL 110

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 110

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
 

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 110

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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Suggested Solutions

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

726 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