mssql_connect和sqlsrv_connect之间的区别

Question

I have just changed the connection driver (extension) from mssql_connect to work with sqlsrv_connect. Unfortunately things do not seem to work as I wanted. I'd appreciate if someone can tell me what’s wrong and how can I fix it.

Code excerpt:

//a oracle DB moudle with a generic functions such as 
//db_connect() db_query()

/// turn on verbose error reporting (15) to see all warnings and errors 
error_reporting(15);
//generic DB operations

// Global record . for using in 
$curr_rec = NULL;

function dbok($res)
{
    if($res==false){
        echo "DB error: "."FIXME need error mesg"."
";
        exit;
    }
    return $res;
}

function db_now_expr()
{
    return "getdate()";
}

function db_is_connected($dbh)
{
    return $dbh>0;
}

function ensure_connected($dbh)
{
    if(!db_is_connected($dbh)) die("DB is not connected, operation failed");
    //echo "DEBUG: hdb=$dbh"; 
}

//connect to given database 
//return handler to DB


function dbo_logon($dbserver,$dbname)
{

    $tsql = ??????? ====>>> what should i put here???? 

    $dbserver = "computername\SQLEXPRESS";
    $dbname = MY_Database_name;

    $connectionOptions = array("Database"=>"BULL");

    $dbh=dbok(sqlsrv_connect($dbserver, $connectionOptions));
    dbok(sqlsrv_query($dbname,$dbh)); 

    $stmt = sqlsrv_query( $connectionOptions, $tsql );

    return $dbh;

}

//close DB
function dbo_logoff($dbh)
{
    if(!db_is_connected($dbh)) echo("DB disconnect error");
    sqlsrv_close($dbh);
}

Here is the original code using mssql_connect:

//a oracle DB module with a generic function such as 
//db_connect() db_query()

/// turn on verbose error reporting (15) to see all warnings and errors 
error_reporting(15);
//generic DB operations

// Global record . for using in 
$curr_rec = NULL;

function dbok($res)
{
    if($res==false){
        echo "DB error: "."FIXME need error mesg"."
";
        exit;
    }
    return $res;
}

function db_now_expr()
{
    return "getdate()";
}

function db_is_connected($dbh)
{
    return $dbh>0;
}

function ensure_connected($dbh)
{
    if(!db_is_connected($dbh)) die("DB is not connected, operation failed");
    //echo "DEBUG: hdb=$dbh"; 
}

//connect to given database 
//return handler to DB
function dbo_logon($dbserver,$dbuser,$dbpass,$dbname)
{
    // $dbh=dbok(mssql_pconnect($dbserver,$dbuser,$dbpass));
    $dbh=dbok(mssql_connect($dbserver,$dbuser,$dbpass));
    //error_log("connect to [$dbname]".date("His")." 
",3,"/tmpbull.log"); //DBEUG DELME
    dbok(mssql_select_db($dbname,$dbh));
    //dbo_exec($dbh,"alter session set NLS_DATE_FORMAT='dd-mm-yyyy //hh24:mi:ss'");
    return $dbh;

}

//close DB
function dbo_logoff($dbh)
{
    if(!db_is_connected($dbh)) echo("DB disconnect error");
    mssql_close($dbh);
}

Note that I had to change the authentication method from SQL authentication to Windows authentication because sqlsrv_connect uses Windows authentication instead of SQL authentication. Is that right?

Answer 1

I think these two links will help to understand Difference between mssql and sqlsrv

• http://blogs.msdn.com/b/brian_swan/archive/2010/03/08/mssql-vs-sqlsrv-what-s-the-difference-part-1.aspx
• http://blogs.msdn.com/b/brian_swan/archive/2010/03/10/mssql-vs-sqlsrv-what-s-the-difference-part-2.aspx

Though in short, to quote one of the articles:

The sqlsrv driver is built, maintained, and supported by Microsoft`

and

The mssql driver is a community-built driver.

I’m not sure how recently this driver was updated or maintained as an official PHP extension, but as of the release of PHP 5.3, it is no longer available with PECL. A quick internet search turns up a few places to download the mssql driver, but none of them that I’ve found indicate that the driver is being actively maintained.

Source: http://blogs.msdn.com/b/brian_swan/archive/2010/03/08/mssql-vs-sqlsrv-what-s-the-difference-part-1.aspx

As for your code sample, see below:

$dbserver = "computername\SQLEXPRESS";

$dbname = "BULL";   
$connetion = dbo_logon($dbserver,$dbname);  
function dbo_logon($dbserver,$dbname) {  
    $connectionOptions = array("Database"=>$dbname);  
    $dbh=sqlsrv_connect($dbserver, $connectionOptions);  
    if(!$dbh){  
         die("Error in Database connection");  
         return false;   
    }  
 return $dbh;   
 }

I think this will work for the connection.please note the code is not tested.