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?
I think these two links will help to understand Difference between mssql and sqlsrv
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.
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.
"The sqlsrv driver is built, maintained, and supported by Microsoft`" well right now Sep 2014, there is no official release to support php 5.6 last official release is from april 2012. MS style...