Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2010-11-30
10
Medium Priority
?
898 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 111

Accepted Solution

by:
Ray Paseur earned 2000 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 111

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 111

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

604 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