PHP mssql_query函数代码示例

 function sql_query($sqltype, $query, $con)
 {
     if ($sqltype == 'mysql') {
         if (class_exists('mysqli')) {
             return $con->query($query);
         } elseif (function_exists('mysql_query')) {
             return mysql_query($query);
         }
     } elseif ($sqltype == 'mssql') {
         if (function_exists('sqlsrv_query')) {
             return sqlsrv_query($con, $query);
         } elseif (function_exists('mssql_query')) {
             return mssql_query($query);
         }
     } elseif ($sqltype == 'pgsql') {
         return pg_query($query);
     } elseif ($sqltype == 'oracle') {
         return oci_execute(oci_parse($con, $query));
     } elseif ($sqltype == 'sqlite3') {
         return $con->query($query);
     } elseif ($sqltype == 'sqlite') {
         return sqlite_query($con, $query);
     } elseif ($sqltype == 'odbc') {
         return odbc_exec($con, $query);
     } elseif ($sqltype == 'pdo') {
         return $con->query($query);
     }
 }

function checksuspended()
{
    testremote();
    $db_a = tDataConnect();
    $m_conn = mDataConnect();
    $query_local = "select * from suspendedtoday";
    $query_remote = "select * from suspendedtoday";
    $query = "select * from suspendedlist";
    if ($_SESSION["standalone"] == 1) {
        if ($_SESSION["remoteDBMS"] == "mssql") {
            $result = mssql_query($query_local, $db_a);
        } else {
            $result = mysql_query($query, $db_a);
        }
    } else {
        if ($_SESSION["remoteDBMS"] == "mssql") {
            $result = sql_query($query_remote, $db_a);
        } else {
            $result = mysql_query($query_remote, $m_conn);
        }
    }
    # That's just not right, fix it later?
    #    $num_rows = sql_fetch_array($result);
    $num_rows = mysql_num_rows($result);
    if ($num_rows == 0) {
        return 0;
    } else {
        return 1;
    }
    sql_close($db_a);
}

function checksuspended()
{
    testremote();
    $db_a = tDataConnect();
    $m_conn = mDataConnect();
    $query_local = "select * from suspendedtoday";
    $query_remote = "select * from " . trim($_SESSION["mServer"]) . "." . trim($_SESSION["mDatabase"]) . ".dbo.suspendedtoday";
    $query = "select * from suspendedlist";
    if ($_SESSION["standalone"] == 1) {
        if ($_SESSION["remoteDBMS"] == "mssql") {
            $result = mssql_query($query_local, $db_a);
        } else {
            $result = mysql_query($query, $db_a);
        }
    } else {
        if ($_SESSION["remoteDBMS"] == "mssql") {
            $result = sql_query($query_remote, $db_a);
        } else {
            $result = mysql_query($query, $m_conn);
        }
    }
    $num_rows = sql_fetch_array($result);
    if ($num_rows == 0) {
        return 0;
    } else {
        return 1;
    }
    sql_close($db_a);
}

 /**
  * Sends a database query to MSSQL server.
  *
  * @param	string		$query 		a database query
  * @param	integer		$limit
  * @param 	integer		$offset
  * @return 	integer				id of the query result
  */
 public function sendQuery($query, $limit = 0, $offset = 0)
 {
     $query = $this->handleLimitParameter($query, $limit, $offset);
     $this->queryCount++;
     $result = @mssql_query($query, $this->linkID);
     if ($result === false) {
         throw new DatabaseException("Invalid SQL: " . $query, $this);
     }
     // get insert id and affected rows
     if (preg_match('/^INSERT /i', $query)) {
         $row = $this->getFirstRow("SELECT @@IDENTITY AS id, @@ROWCOUNT as affected");
         $this->insertID = $row['id'];
         $this->affectedRows = $row['affected'];
     } else {
         if (preg_match('/^(UPDATE|DELETE) /i', $query)) {
             $row = $this->getFirstRow("SELECT @@ROWCOUNT as affected");
             $this->affectedRows = $row['affected'];
         }
     }
     $this->queryID = $result;
     if ($offset != 0) {
         $this->seekResult($this->queryID, $offset);
     }
     return $this->queryID;
 }

function findFlights($flight)
{
    //Connects to database
    require 'connect_db.php';
    $query = mssql_query('SELECT * FROM FLIGHT');
    if (!mssql_num_rows($query)) {
        echo 'No records found';
    } else {
        //Creates tables and fills it with flight numbers and their delays
        echo '<br><br><br><br><table border = 1>';
        echo '<th>Flight Number</th><th>Delayed</th><th>Depature Time</th>';
        while ($row = mssql_fetch_assoc($query)) {
            $i = 0;
            //Check if flight is what is looking for
            if (strcmp($row['Flight_number'], $flight) == 0) {
                $i = $i + 1;
                echo '<tr><td>' . $row['Flight_number'] . '</td>';
                if (strcmp($row['Delayed'], '1') != 0) {
                    echo '<td>' . 'On Time' . '</td>';
                } else {
                    echo '<td>' . 'Delayed' . '</td>';
                }
                echo '<td>' . $row['Depature_time'] . '</td></tr>';
                //^End else
            }
        }
        //^ends while
        echo '</table>';
    }
}

function DriverMssqlExec($conn, $sql)
{
    $result = mssql_query($sql, $conn);
    if (!$result) {
        throw new lmbDbException('MSSQL execute error happened: ' . mssql_get_last_message() . ". SQL: " . $sql);
    }
}

function conn($DB)
{
    $serverName = "intelisis";
    //serverName\instanceName
    $connectionInfo = array("Database" => $DB, "UID" => "intelisis", "PWD" => "");
    $conn = mssql_connect($serverName, "intelisis", "");
    mssql_select_db($DB, $conn);
    $user = $_SESSION["user"];
    if (!$conn) {
        die('Something went wrong while connecting to MSSQL');
    }
    $con1 = "set dateformat dmy";
    $con1 = mssql_query($con1);
    $con2 = "SET DATEFIRST 7";
    $con2 = mssql_query($con2);
    $con3 = "SET ANSI_NULLS OFF";
    $con3 = mssql_query($con3);
    $con4 = "SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED";
    $con4 = mssql_query($con4);
    $con5 = "SET LOCK_TIMEOUT -1";
    $con5 = mssql_query($con5);
    $con6 = "SET QUOTED_IDENTIFIER OFF";
    $con6 = mssql_query($con6);
    $con7 = "set language spanish";
    $con7 = mssql_query($con7);
}

 /**
  * @desc Generates a XLS File from an SQL Query (and outputs it to the browser)
  * @access public
  * @param  String $query Query String
  * @param  String $db Name of the Database
  * @param  String $user User to Access the Database
  * @param  String $pass Password to Access the Database
  * @param  String $host Name of the Host holding the DB
  */
 function WriteSQLDump($query, $db, $user = 'root', $pass = '', $host = 'localhost')
 {
     $xls_line = 0;
     $col = 0;
     $link = $this->_db_connect($db, $user, $pass, $host);
     if ($link) {
         $result = @mssql_query($query, $link);
         if (!$result) {
             $this->WriteValue(1, 0, "An error occured while excuting the query " . $query);
             $this->OutputFile();
             exit;
         }
         $lines = @mssql_num_rows($result);
         $colums = mssql_num_fields($result);
         for ($e = 0; $e < $colums; $e++) {
             $this->WriteValue(0, $e, trim(ucwords(str_replace("_", " ", mssql_field_name($result, $e)))));
         }
         for ($col = 0; $col < $colums; $col++) {
             $col_name = mssql_field_name($result, $col);
             for ($i = 0; $i < $lines; $i++) {
                 $CellValue = mssql_result($result, $i, $col_name);
                 $xls_line = $i + 1;
                 $this->WriteValue($xls_line, $col, $CellValue);
             }
         }
     } else {
         $this->WriteValue(1, 0, "Could not connect to the database:");
     }
     $this->OutputFile();
 }

    public function getAll()
    {
        $obj_ids = $this->getObjIds();
        $retsult = array();
        $sql = <<<SQL
\t\t\tSELECT
\t\t\t\ttemp.OBJID as id,
\t\t\t\tCONVERT(VARCHAR, (DB1.GMT+'02:00'), 21) as dt,
\t\t\t\tDB1.LAT as lat,
\t\t\t\tDB1.LON as lon,
\t\t\t\tDB1.AVTO_NO as avto_no,
\t\t\t\tDB1.SPEED as speed
\t\t\tFROM [monitoring_new].[dbo].[OD_LTE_OBJLASTPOS] DB1
\t\t\tINNER JOIN (
\t\t\t\tSELECT
\t\t\t\t\tOBJID,
\t\t\t\t\tMAX(GMT) AS DAT
\t\t\t\tFROM [monitoring_new].[dbo].[OD_LTE_OBJLASTPOS]
\t\t\t\tWHERE GMT>=cast(getutcdate() as date) and OBJID in ({$obj_ids})
\t\t\t\tGROUP BY OBJID) as temp
\t\t\tON (temp.OBJID=DB1.OBJID and temp.DAT=DB1.GMT)
\t\t\tORDER BY temp.OBJID
SQL;
        $cur = mssql_query($sql, $this->conm);
        while ($data = mssql_fetch_assoc($cur)) {
            $result[$data['id']] = array('obj_id' => $data['id'], 'dt' => $data['dt'], 'lat' => $data['lat'], 'lon' => $data['lon'], 'name' => $data['avto_no']);
        }
        return $result;
    }

 /**
  * @param $sql
  *
  * @return array
  */
 public function query($sql)
 {
     //
     $this->connection = $this->getConnection();
     // Run query
     $query = mssql_query($sql, $this->connection);
     // On error
     if ($query === false) {
         Response::error(500, $_SERVER["SERVER_PROTOCOL"] . ' DB query failed (SQL): ' . mssql_get_last_message());
     }
     // E.g. boolean is returned if no rows (e.g. no resource found or on UPDATE)
     if ($query === true) {
         $response = $query;
     } else {
         // Response
         $response = array();
         //
         // Loop rows and add to response array
         if (mssql_num_rows($query) > 0) {
             while ($row = mssql_fetch_assoc($query)) {
                 $response[] = $row;
             }
         }
         // Free the query result
         mssql_free_result($query);
     }
     // Close link
     $this->closeConnection();
     //
     return $response;
 }

 /**
  * Get the current enrolments and course identifiers from the SIS
  *
  * @author Andrew Zoltay
  * date    2012-09-12
  * @global object $DB - Moodle database object
  * @param link_identifier $sourceconn - connection to enrolment source db
  * @param integer $monthsahead - number of months into the future to get enrolments
  * @return array of db records or false if error occurs
  */
 private function fetch_student_enrolments($sourceconn, $monthsahead)
 {
     global $DB;
     if ($sourceconn) {
         $query = "EXEC Learn.usp_GetStudentEnrolments @intMonthsBeforeStart = {$monthsahead};";
         $result = mssql_query($query, $sourceconn);
         if (!$result) {
             $this->write_log("Error calling Learn.usp_GetStudentEnrolments: " . mssql_get_last_message(), true);
             return false;
         }
         // Get student role id.
         $studentroleid = $DB->get_field('role', 'id', array('archetype' => 'student'), MUST_EXIST);
         // Get the data in the correct format for enrol_rru plugin to deal with it.
         $enrolments = array();
         while ($row = mssql_fetch_assoc($result)) {
             // Format enrolments.
             $enrolment = array();
             $enrolment['chrCourseCode'] = $row['chrCourse_Code'];
             $enrolment['intUserCode'] = $row['intStudent_PK'];
             $enrolment['intRoleID'] = $studentroleid;
             $enrolments[] = $enrolment;
         }
         return $enrolments;
     } else {
         $this->write_log("Connection not established", true);
         return false;
     }
 }

 public function FindItem()
 {
     $resultQ = mssql_query("select [Name] from [Character] where (charindex (0x" . $this->ProductSerial . ", Inventory) %" . DIVISOR / 2 . "=4)");
     while ($result = mssql_fetch_object($resultQ)) {
         echo Print_error("<ul><li>Item foi encontrado no invent&aacute;rio do char: " . $result->Name . ".</li></ul>");
         $exitFunction = true;
     }
     unset($result, $resultQ);
     $findColumnsQ = mssql_query("sp_MShelpcolumns N'dbo.warehouse', null, 'id', 1");
     while ($findColumns = mssql_fetch_object($findColumnsQ)) {
         if ($findColumns->col_typename == "varbinary") {
             $resultQ = mssql_query("select [AccountId] from [warehouse] where (charindex (0x" . $this->ProductSerial . ", " . $findColumns->col_name . ") %" . DIVISOR / 2 . "=4)");
             while ($result = mssql_fetch_object($resultQ)) {
                 echo Print_error("<ul><li>Encontrado no bau do login: " . $result->AccountId . ". Tabela: warehouse, coluna: {$findColumns->name}</li></ul>");
                 $exitFunction = true;
             }
         }
     }
     unset($result, $resultQ);
     $resultQ = mssql_query("select [AccountId],[Number] from [ExtWarehouse] where (charindex (0x" . $this->ProductSerial . ", Items) %" . DIVISOR / 2 . "=4)");
     while ($result = mssql_fetch_object($resultQ)) {
         echo Print_error("<ul><li>Item foi encontrado no bau extra " . $result->Number . " do login: " . $result->AccountId . ".</li></ul>");
         $exitFunction = true;
     }
     unset($result, $resultQ);
     $resultQ = @mssql_query("select [AccountId] from [ExtWarehouseVirtual] where (charindex (0x" . $this->ProductSerial . ", Item) %" . DIVISOR / 2 . "=4)");
     while ($result = @mssql_fetch_object($resultQ)) {
         echo Print_error("<ul><li>Item foi encontrado no bau virtual do login: " . $result->AccountId . ".</li></ul>");
         $exitFunction = true;
     }
     unset($result, $resultQ);
     if ($exitFunction == false) {
         echo "<ul><li>Esse item n&atilde;o foi encontrado no servidor.</li></ul>";
     }
 }

    function Login()
    {
        $link = Conectarse();
        $consulta = "SELECT * FROM [022BDCOMUN].DBO.USUARIOS\r\nWHERE usuario='{$this->user}' AND contrasena='{$this->pass}' AND idtipousuario=2 ";
        $resultado = mssql_query($consulta, $link) or die(mssql_error());
        $fila = mssql_fetch_array($resultado);
        if (!$fila[0]) {
            echo '<script>
alert("Usuario o Password errados, por favor verifique.")
self.location = "/adm-reserva/"
</script>';
        } else {
            if (!isset($_SESSION)) {
                @session_start();
            }
            $_SESSION['id_usuario'] = $fila['id_usuario'];
            $_SESSION['nombres'] = $fila['nombres'];
            $_SESSION['apellidos'] = $fila['apellidos'];
            $_SESSION['starsoft'] = $fila['starsoft'];
            $_SESSION['idempresa'] = $fila['idempresa'];
            $_SESSION['idarea'] = $fila['idarea'];
            $_SESSION['aud_jefe'] = $fila['aud_jefe'];
            header("Location: /adm-reserva/home");
        }
    }

function getProductos()
{
    $myServer = "172.30.5.49";
    $myUser = "UsrPsg";
    $myPass = "PsGcRm1402*LaU+";
    $myDB = "LAUMAYER";
    $dbhandle = mssql_connect($myServer, $myUser, $myPass) or die("Couldn't connect to SQL Server on {$myServer}");
    $selected = mssql_select_db($myDB, $dbhandle) or die("Couldn't open database {$myDB}");
    //Realiza el query en la base de datos
    $mysqli = makeSqlConnection();
    //$sql = "SELECT * FROM psg_productos a LEFT JOIN psg_productos_cstm ac ON a.id = ac.id_c";
    $sql = "SELECT id,name FROM psg_productos where deleted ='0'";
    $res = $mysqli->query($sql);
    $rows = array();
    while ($r = mysqli_fetch_assoc($res)) {
        $obj = (object) $r;
        $querySaldo = "Select dbo.F_Saldo_Bodega_Informe(Year(GETDATE()),MONTH(GETDATE()),'" . $r['id'] . "','BODPRDCTO','T','C') as Saldo";
        $result = mssql_query($querySaldo);
        if ($row = mssql_fetch_array($result)) {
            $obj->saldo = $row['Saldo'];
        }
        $a = (array) $obj;
        $rows[] = $a;
    }
    mssql_close($dbhandle);
    if (empty($rows)) {
        return '{"results" :[]}';
    } else {
        //Convierte el arreglo en json y lo retorna
        $temp = json_encode(utf8ize($rows));
        return '{"results" :' . $temp . '}';
    }
}

 private function executeQuery()
 {
     $return = false;
     if ($this->queryType == 'other') {
         if (mssql_query($this->query, $this->link) === true) {
             $return = true;
             $this->error = mssql_get_last_message();
         }
     } else {
         if ($result = mssql_query($this->query, $this->link)) {
             // Conteo de registros
             if ($this->queryType == 'insert' || $this->queryType == 'update' || $this->queryType == 'delete') {
                 $this->numRows = mssql_rows_affected($this->link);
                 $return = true;
             } else {
                 $this->numRows = mssql_num_rows($result);
                 $fetchType = MSSQL_NUM;
                 if ($this->queryReturn == 'assoc') {
                     $fetchType = MSSQL_ASSOC;
                 } elseif ($this->queryReturn == 'both') {
                     $fetchType = MSSQL_BOTH;
                 }
                 $return = array();
                 while ($row = mssql_fetch_array($result, $fetchType)) {
                     array_push($return, $row);
                 }
             }
             $this->error = mssql_get_last_message();
             mssql_free_result($result);
         } else {
             $this->error = mssql_get_last_message();
         }
     }
     return $return;
 }