Solved

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

Posted on 2010-11-30
10
842 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
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.

705 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

20 Experts available now in Live!

Get 1:1 Help Now