0

i upgradefrom php version 2.2 to 2.4 and php 5.6 too. The problem is with the new php the function does not support mssql_connect so i installed the new driver for sqlsvr and when i made the Database connection it work fine.

I need some help. How can I change this code from mssql to sqlsvr thanks you and i waiting for your answer.

<?php

class C_DBHandler 
{

 private $db_Host     = "";         // Host, auf dem die DB läuft
 private $db_Database = "";         // zu verwendetende Database
 private $db_User     = "";         // User und Paßwort für Login
 private $db_Password = "";

 private $db_Link     = 0;          // Resultat des connect()
 private $db_Query    = 0;          // Resultat des query()
 private $db_Record   = array();    // aktuelles fetch_array()-Ergebnis
 private $db_Row;                   // Aktuelle Ergebniszeile
 private $db_numRows = "";

 private $LSDconf     = "";
 private $LSDDBconf   = "";
 private static $instance;          // Klasseninstanzname



public function __get($property){
    $Err = C_GetHDL();
    $Err->SetSubject("LSD-Error (property)");
    $Err->SetBody("Attempt to read from not existing property \"$property\". Class: \"".__CLASS__."\"; Triggered by User: \"".$_SESSION['ULogin']."\"");
    $Err->SendAdminInfo("mail_db");
}

public function __set($property, $val){
    $Err = C_FuncHandler::GetHDL();
    $Err->SetSubject("LSD-Error (property)");
    $Err->SetBody("Attempt to write \"$val\" to not existing property \"$property\". Class: \"".__CLASS__."\"; Triggered by User: \"".$_SESSION['ULogin']."\"");
    $Err->SendAdminInfo("mail_db");
}

function __autoload($className){
    $fileName = $className.'.inc';
    require($fileName);
}




private function __construct(){
    require ('config.inc');
    require ('DB-config.inc');
    $this->LSDconf   = $LSDconf;
    $this->LSDDBconf = $LSDDBconf;
}


public function __clone()
{
   trigger_error('Clone is not allowed.', E_USER_ERROR);
}



public static function GetHDL()
{
   if (!isset(self::$instance)) {
       $c = __CLASS__;
       self::$instance = new $c;
   }

   return self::$instance;
}


 private function connect() {
    $this->db_Host      = $this->LSDDBconf['DBSec']['host'];
    $this->db_Database  = $this->LSDDBconf['DBSec']['dbname'];
    $this->db_Password  = $this->LSDDBconf['DBSec']['pwd'];
    $this->db_User      = $this->LSDDBconf['DBSec']['user'];

    if ( 0 == $this->db_Link ) {
        $this->db_Link=mssql_connect($this->db_Host, $this->db_User, $this->db_Password);
        if (!$this->db_Link) {
            die("<br><br><b><font color=\"red\">Invalid SQL connect-DB</font></b>");

        }
        if (!mssql_select_db($this->db_Database,$this->db_Link)) {              $Err->SendAdminInfo("mail_db");
            die("<br><br><b><font color=\"red\">Invalid SQL select-DB</font></b>");

        }
    }
 }


 public function query($Query_String) {
    $this->connect();
    $this->db_Query = mssql_query($Query_String,$this->db_Link);
    $this->db_Row   = 0;
    if (!$this->db_Query) {
        die("<br><br><b><font color=\"red\">Invalid SQL Query</font></b>");

    }
    return $this->db_Query;
 }


 public function next_record() {
    $this->db_Record = mssql_fetch_array($this->db_Query);
    $this->db_Row   += 1;

    return $this->db_Record;
 }

 public function num_rows(){
    $this->db_numRows = mssql_num_rows($this->db_Query);
    return $this->db_numRows;
 }



 public function rows_affected(){
    $this->db_Query   = mssql_query("SELECT @@ROWCOUNT", $this->db_Link);
    $this->db_numRows = mssql_fetch_row($this->db_Query);
    return $this->db_numRows[0];
 }


public function mssql_addslashes($MyString) {
    $MyString = str_replace("'", "''", $MyString);
    return $MyString;
}

public function Setdb_Query($val){
    $this->db_Query = $val;
}

public function Getdb_Query(){
    return $this->db_Query;
}

   }
?>
Zhorov
  • 28,486
  • 6
  • 27
  • 52
klk2000
  • 13
  • 7

2 Answers2

1

Changes, that have to be made, to migrate from MSSQL to SQLSRV extensions of PHP:

Connection:

Functions mssql_connect() and mssql_select_db() must be replaced with sqlsrv_connect():

public function connect() {
    $this->db_Host      = $this->LSDDBconf['DBSec']['host'];
    $this->db_Database  = $this->LSDDBconf['DBSec']['dbname'];
    $this->db_Password  = $this->LSDDBconf['DBSec']['pwd'];
    $this->db_User      = $this->LSDDBconf['DBSec']['user'];

    if ( 0 == $this->db_Link ) {
        $this->db_Link = sqlsrv_connect($this->db_Host, array("Database"=>$this->db_Database, "UID"=>$this->db_User, "PWD"=>$this->db_Password));
        if ($this->db_Link === false) {
            $Err->SendAdminInfo("mail_db");
           die("<br><br><b><font color=\"red\">Invalid SQL connect-DB</font></b>");
            exit;
        }
   }
}

Query:

public function query($Query_String) {
    $this->connect();
    # SQLSRV_CURSOR_FORWARD - Lets you move one row at a time starting at the first row of the result set until you reach the end of the result set.
    # This is the default cursor type. sqlsrv_num_rows returns an error for result sets created with this cursor type.
    # SQLSRV_CURSOR_STATIC - Lets you access rows in any order but will not reflect changes in the database.
    # SQLSRV_CURSOR_DYNAMIC - Lets you access rows in any order and will reflect changes in the database. 
    # sqlsrv_num_rows returns an error for result sets created with this cursor type.
    # SQLSRV_CURSOR_KEYSET - Lets you access rows in any order. However, a keyset cursor does not update the row count if a row is deleted from the
    # table (a deleted row is returned with no values).
    # SQLSRV_CURSOR_CLIENT_BUFFERED - Lets you access rows in any order. Creates a client-side cursor query.
    $this->db_Query = sqlsrv_query($this->db_Link, $Query_String, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
    $this->db_Row   = 0;
    if (!$this->db_Query) {
        die("<br><br><b><font color=\"red\">Invalid SQL Query</font></b>");
    }
    return $this->db_Query;
}

Fetch record:

public function next_record() {
    # SQLSRV_FETCH_ASSOC - sqlsrv_fetch_array returns the next row of data as anassociative array.
    # SQLSRV_FETCH_BOTH - sqlsrv_fetch_array returns the next row of data as an array with both numeric and associative keys. This is the default value.
    # SQLSRV_FETCH_NUMERIC - sqlsrv_fetch_array returns the next row of data as a numerically indexed array
    $this->db_Record = sqlsrv_fetch_array($this->db_Query, SQLSRV_FETCH_BOTH);
    $this->db_Row   += 1;
    return $this->db_Record;
}

Row count:

public function num_rows(){
    # sqlsrv_num_rows requires a client-side, static, or keyset cursor, and will return false if you use a forward cursor or
    # a dynamic cursor. (A forward cursor is the default.) For more information about cursors, see sqlsrv_query and Cursor Types (SQLSRV Driver).
    $this->db_numRows = sqlsrv_num_rows($this->db_Query);
    return $this->db_numRows;
}
public function rows_affected(){
    $this->db_Query   = sqlsrv_query($this->db_Link, "SELECT @@ROWCOUNT");
    $this->db_numRows = sqlsrv_fetch_array($this->db_Query, SQLSRV_FETCH_NUMERIC);
    return $this->db_numRows[0];
}

Notes:

You can read Brian Swan's article and Microsoft documentation.

Zhorov
  • 28,486
  • 6
  • 27
  • 52
  • thank you for your answer and nice links i did like it in the links here the code where i made the changed $this->db_Link = sqlsrv_connect($this->db_Database, $this->db_Host, $this->db_User, $this->db_Password); sqlsrv_connect($this->db_Database, $this->db_Link); – klk2000 Jun 26 '18 at 15:22
  • i get this error : Catchable fatal error: Argument 2 passed to sqlsrv_connect() must be of the type array, string given, called in C:\AppServ\Apache24\htdocs\C_DBHandler.inc on line 147 and defined in C:\AppServ\Apache24\htdocs\C_DBHandler.inc on line 132 – klk2000 Jun 26 '18 at 15:23
  • i dont know maybe something is missing $Servername array function – klk2000 Jun 26 '18 at 15:24
  • @klk2000 Functions mssql_connect() and mssql_select_db() must be replaced with just one function sqlsrv_connect() with this syntax: sqlsrv_connect($this->db_Host, $conninfo). Using sqlsrv_connect($this->db_Database, $this->db_Host, $this->db_User, $this->db_Password); in this way is not correct. – Zhorov Jun 26 '18 at 19:57
  • Thanks you for your help the connection to the database work but ii have a problem with the user authentification, they cannot login maybe i missing something here : – klk2000 Jun 27 '18 at 14:00
  • public function rows_affected(){ $this->db_Query =sqlsrv_query("SELECT @@ROWCOUNT", $this->db_Link); #$this->db_numRows = sqlsrv_fetch_row($this->db_Query); $this->db_numRows = mssql_fetch_row($this->db_Query); return $this->db_numRows[0]; } – klk2000 Jun 27 '18 at 14:00
  • or maybe i need to moved the or generate new htpasswd or in php.conf something is missing thanks you for your help – klk2000 Jun 27 '18 at 14:02
  • @klk2000 Change parameters order: $this->db_Query =sqlsrv_query($this->db_Link, "SELECT @@ROWCOUNT"); – Zhorov Jun 27 '18 at 14:13
  • Thanks you for your answer but nothing changed what about this line $this->db_numRows = mssql_fetch_row($this->db_Query); – klk2000 Jun 27 '18 at 14:29
  • @klk2000 See updated answer. I've tested it, so I think that this will work for you. – Zhorov Jun 28 '18 at 06:24
  • It Work perfect with your last update. the only problem was insert new entry and view last entry. thank you very much for your help. i made some changed here the most important public function query($Query_String) { $this->connect(); $this->db_Query = sqlsrv_query($this->db_Link, $Query_String, array(), array("Scrollable" => SQLSRV_CURSOR_CLIENT_BUFFERED)); $this->db_Row = 0; if (!$this->db_Query) { die("

    Invalid SQL Query"); } return $this->db_Query;
    – klk2000 Jun 28 '18 at 12:48
  • thanks you for your last help the only thing it still not work is the search function name of file is searchNameString.php i leave here – klk2000 Jul 03 '18 at 08:24
  • if(isset($_GET['getNameByLetters']) && isset($_GET['letters'])){ $letters = $_GET['letters']; $letters = preg_replace("/[^a-z0-9\. ]/si","",$letters); $res = $DBH->query("SELECT EName FROM T_Entry WHERE EName LIKE '%$letters%' AND ESID = '4' AND EAktiv = '1' ".$ExtIntUse." ORDER BY EName"); while($inf = mssql_fetch_array($res)); { $str .= $inf['EName']."|"; } echo htmlentities($str); } i need to change this code to work with sqlsvr i tried like it – klk2000 Jul 03 '18 at 08:27
  • if(isset($_GET['getNameByLetters']) && isset($_GET['letters'])){ $letters = $_GET['letters']; $letters = preg_replace("/[^a-z0-9\. ]/si","",$letters); $res = $DBH->query("SELECT EName FROM T_Entry WHERE EName LIKE '%$letters%' AND ESID = '4' AND EAktiv = '1' ".$ExtIntUse." ORDER BY EName"); while ($inf = sqlsrv_fetch_array($res, SQLSRV_FETCH_BOTH)); { $str .= $inf['EName']."|"; } echo htmlentities($str); } – klk2000 Jul 03 '18 at 08:28
  • but it's still dont work, i wating for your answer thanks you – klk2000 Jul 03 '18 at 08:29
  • @klk2000 I think ii's typing error. Remove ';' after while ($inf = sqlsrv_fetch_array($res, SQLSRV_FETCH_BOTH)). Or use while ($inf = $DBH->next_record()) { $str .= $inf['EName']."|"; }. And you can always ask a new question. Comments are not recommended for answering a question or providing an alternate solution to an existing answer; instead, post a new question (or edit to expand an existing one). – Zhorov Jul 03 '18 at 09:47
  • thank you for your helpagain, i typed a error i removed the ";" and now its work next time i willmade a new question thanks you. – klk2000 Jul 06 '18 at 13:08
0

You might want to replace mssql_connect() with sqlsrv_connect() if you installed new library. Actually all mssql functions may be replaced. It is the first, quick idea :-) BTW: mssql_connect() is removed only from php 7, so far should have worked in php 5.6: http://php.net/manual/en/function.mssql-connect.php