PHP sqlsrv_free_stmt函数代码示例

 public function Query($Procedimiento, $RetornaDatos, $arrayValores = "")
 {
     $parametros = array();
     $NombreServidor = constant("sqlHost");
     $InfoConexion = array("UID" => constant("sqlUsuario"), "PWD" => constant("sqlContrasena"), "Database" => $this->NombreBaseDatos);
     $conn = sqlsrv_connect($NombreServidor, $InfoConexion);
     if (is_array($arrayValores)) {
         $stringInterrogacion = "(?";
         for ($i = 1; $i < count($arrayValores); $i++) {
             $stringInterrogacion .= ",?";
         }
         $stringInterrogacion .= ")";
         $Procedimiento = "{call " . $Procedimiento . " " . $stringInterrogacion . "}";
         for ($i = 0; $i < count($arrayValores); $i++) {
             array_push($parametros, array($arrayValores[$i], SQLSRV_PARAM_IN));
         }
     } else {
         $Procedimiento = "{call " . $Procedimiento . "}";
     }
     $stmt3 = sqlsrv_query($conn, $Procedimiento, $parametros);
     if ($RetornaDatos) {
         $array = array();
         while ($obj = sqlsrv_fetch_array($stmt3, SQLSRV_FETCH_ASSOC)) {
             $array[] = $obj;
         }
         return $array;
         sqlsrv_free_stmt($stmt3);
     }
     sqlsrv_close($conn);
 }

 public function getOne($sql)
 {
     $res = $this->query($sql);
     $row = sqlsrv_fetch_array($res, $this->rsType);
     sqlsrv_free_stmt($res);
     return $row;
 }

function dbcall()
{
    session_start();
    $seubid = (string) session_id();
    $server = "bamsql2";
    $options = array("UID" => "genes", "PWD" => "Genes12", "Database" => "genes");
    $conn = sqlsrv_connect($server, $options);
    if ($conn === false) {
        die("<pre>" . print_r(sqlsrv_errors(), true));
    }
    $rno = $_POST['Rnumber'];
    $name = $_POST['name'];
    $email = $_POST['email'];
    $gender = $_POST['gender'];
    $sql = "insert INTO dbo.contactinfo values('{$rno}','{$name}','{$email}','{$gender}')";
    $query = sqlsrv_query($conn, $sql);
    if ($query === false) {
        exit("<pre>" . print_r(sqlsrv_errors(), true));
    }
    #while ($row = sqlsrv_fetch_array($query))
    # {  echo "<p>Hello, $row[ascore]!</p>";
    #}
    sqlsrv_free_stmt($query);
    sqlsrv_close($conn);
}

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     // Procura na tabela OCRD (Partner Card)
     $query = "SELECT CardCode, CardName, CardFName, frozenFor, CntctPrsn, Phone1, IndustryC FROM OCRD WHERE " . $filter;
     if (empty($filter)) {
         $query = "SELECT CardCode, CardName, CardFName, frozenFor, CntctPrsn, Phone1, IndustryC FROM OCRD WHERE CardName IS NOT NULL ORDER BY cardName";
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new BusinessPartnerDTO();
         $dto->cardCode = $record['CardCode'];
         $dto->cardName = $record['CardName'];
         $dto->cardFName = $record['CardFName'];
         $dto->inactive = $record['frozenFor'];
         $dto->contactPerson = $record['CntctPrsn'];
         $dto->telephoneNumber = $record['Phone1'];
         $dto->industry = $record['IndustryC'];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 function free_result()
 {
     if (is_resource($this->result_id)) {
         sqlsrv_free_stmt($this->result_id);
         $this->result_id = FALSE;
     }
 }

 function RetrieveOther($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT OINV.Serial, INV1.Usage, OINV.CardCode, OINV.CardName, OINV.DocTotal, ORCT.CashSum, ORCT.CheckSum, ORCT.TrsfrSum, ORCT.DocDueDate, OSLP.SlpCode, OSLP.SlpName, OINV.U_demFaturamento FROM ORCT ";
     $query .= "JOIN RCT2 ON RCT2.DocNum = ORCT.DocEntry ";
     $query .= "JOIN OINV ON OINV.DocEntry = RCT2.DocEntry ";
     $query .= "JOIN INV1 ON INV1.DocEntry = OINV.DocEntry ";
     $query .= "JOIN OSLP ON OSLP.SlpCode = OINV.SlpCode ";
     if (!empty($filter)) {
         $query .= " WHERE " . $filter;
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new InvoicePaymentDTO();
         $dto->serial = $record['Serial'];
         $dto->tipo = $record['Usage'];
         $dto->cardCode = $record['CardCode'];
         $dto->cardName = $record['CardName'];
         $dto->valorNotaFiscal = $record['DocTotal'];
         $dto->valorDinheiro = $record['CashSum'];
         $dto->valorCheque = $record['CheckSum'];
         $dto->valorDeposito = $record['TrsfrSum'];
         $dto->date = $record['DocDueDate'];
         $dto->slpCode = $record['SlpCode'];
         $dto->slpName = $record['SlpName'];
         $dto->demFaturamento = $record['U_demFaturamento'];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT Address, AddrType, Street, StreetNo, Building, ZipCode, Block, City, State, Country, U_Secretaria FROM CRD1 WHERE " . $filter;
     if (empty($filter)) {
         $query = "SELECT Address, AddrType, Street, StreetNo, Building, ZipCode, Block, City, State, Country, U_Secretaria FROM CRD1 WHERE Address IS NOT NULL";
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new PartnerAddressDTO();
         $dto->addressLabel = $record["Address"];
         $dto->addrType = $record["AddrType"];
         $dto->street = $record["Street"];
         $dto->streetNo = $record["StreetNo"];
         $dto->building = $record["Building"];
         $dto->zipCode = $record["ZipCode"];
         $dto->block = $record["Block"];
         $dto->city = $record["City"];
         $dto->state = $record["State"];
         $dto->country = $record["Country"];
         $dto->locationRef = $record["U_Secretaria"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT * FROM ( ";
     $query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, ";
     $query .= "FAB.FirmName AS fabricante, CHAM.id AS numeroChamado, CHAM.tempoAtendimento, MONTH(CHAM.dataAtendimento) AS mesReferencia, YEAR(CHAM.dataAtendimento) AS anoReferencia ";
     $query .= "FROM MYSQL...chamadoServico CHAM ";
     $query .= "JOIN OINS EQP ON CHAM.cartaoEquipamento = EQP.insID ";
     $query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
     $query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
     $query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
     $query .= "              ) LABOREXPENSES ";
     if (isset($filter) && !empty($filter)) {
         $query = $query . " WHERE " . $filter;
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query . " ORDER BY nomeCliente, serieEquipamento");
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new LaborExpenseDTO();
         $dto->codigoCliente = $record["codigoCliente"];
         $dto->nomeCliente = $record["nomeCliente"];
         $dto->codigoEquipamento = $record["codigoEquipamento"];
         $dto->serieEquipamento = $record["serieEquipamento"];
         $dto->codigoModelo = $record["codigoModelo"];
         $dto->tagModelo = $record["tagModelo"];
         $dto->fabricante = $record["fabricante"];
         $dto->numeroChamado = $record["numeroChamado"];
         $dto->tempoAtendimento = $record["tempoAtendimento"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 function RetrieveEmployeesByPosition($employeePosition1 = null, $employeePosition2 = null, $employeePosition3 = null)
 {
     $dtoArray = array();
     $subQuery = "SELECT posId FROM OHPS";
     if (!empty($employeePosition1)) {
         $subQuery = $subQuery . " WHERE name LIKE '%" . $employeePosition1 . "%'";
     }
     if (!empty($employeePosition2)) {
         $subQuery = $subQuery . " OR name LIKE '%" . $employeePosition2 . "%'";
     }
     if (!empty($employeePosition3)) {
         $subQuery = $subQuery . " OR name LIKE '%" . $employeePosition3 . "%'";
     }
     $query = "SELECT empID, firstName, middleName, lastName FROM OHEM WHERE position IN (" . $subQuery . ") ORDER BY firstName DESC";
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query);
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new EmployeeDTO();
         $dto->empID = $record["empID"];
         $dto->firstName = $record["firstName"];
         $dto->middleName = $record["middleName"];
         $dto->lastName = $record["lastName"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

function GetFacultyQuestions($groupid)
{
    include_once 'db_Connection.php';
    $conn = sqlsrv_connect($serverName, $connectionInfo);
    $questionvalues = array();
    $questions = array();
    $faculty = array();
    $facultyquestions = array();
    if ($conn) {
        //----get acadyear-------------------
        $acid = 0;
        $sqlstr = " SELECT [AcadYearID] FROM [dbo].[Groups] where [groupid]=? ";
        $params = array($groupid);
        $sqlquery = sqlsrv_query($conn, $sqlstr, $params);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_array($sqlquery, SQLSRV_FETCH_ASSOC)) {
                $acid = $row['AcadYearID'];
            }
            sqlsrv_free_stmt($sqlquery);
        }
        $sqlstr = " SELECT [questionTypeId],[value],[text] " . " FROM [dbo].[QuestionValues] " . " order by questionTypeId ";
        //  $params = array ($acid);
        $sqlquery = sqlsrv_query($conn, $sqlstr);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_object($sqlquery)) {
                $questionvalues[] = $row;
            }
            sqlsrv_free_stmt($sqlquery);
        }
        //----get question array-------------------
        $sqlstr = " SELECT [QuestionID],[QueastionText],[questionType],[maxmark],-1 as mark,'' as description " . " FROM [dbo].[Questions] " . " where [QuestionLecturer]=0 " . " and [Acadyear]=? ";
        $params = array($acid);
        $sqlquery = sqlsrv_query($conn, $sqlstr, $params);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_object($sqlquery)) {
                $row->questionValues = array();
                foreach ($questionvalues as &$questValue) {
                    if ($questValue->questionTypeId === $row->questionType) {
                        array_push($row->questionValues, $questValue);
                    }
                }
                $questions[] = $row;
            }
            sqlsrv_free_stmt($sqlquery);
        }
        //----get faculty-------------------
        $sqlstr = " SELECT [FacultyID],[FacultyName] FROM [dbo].[Groups] " . " where [groupid]=? ";
        $params = array($groupid);
        $sqlquery = sqlsrv_query($conn, $sqlstr, $params);
        if ($sqlquery) {
            while ($row = sqlsrv_fetch_object($sqlquery)) {
                $row->quests = $questions;
                $faculty[] = $row;
            }
            $facultyquestions = array("result" => 0, "data" => $faculty);
        }
        sqlsrv_close($conn);
    }
    return $facultyquestions;
}

 public function query($sql)
 {
     LogMaster::log($sql);
     if ($this->start_from) {
         $res = sqlsrv_query($this->connection, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_STATIC));
     } else {
         $res = sqlsrv_query($this->connection, $sql);
     }
     if ($res === false) {
         $errors = sqlsrv_errors();
         $message = array();
         foreach ($errors as $error) {
             $message[] = $error["SQLSTATE"] . $error["code"] . $error["message"];
         }
         throw new Exception("SQLSrv operation failed\n" . implode("\n\n", $message));
     }
     if ($this->insert_operation) {
         sqlsrv_next_result($res);
         $last = sqlsrv_fetch_array($res);
         $this->last_id = $last["dhx_id"];
         sqlsrv_free_stmt($res);
     }
     if ($this->start_from) {
         $data = sqlsrv_fetch($res, SQLSRV_SCROLL_ABSOLUTE, $this->start_from - 1);
     }
     return $res;
 }

 /**
  * This function frees the command reference.
  *
  * @access public
  * @override
  *
  * @see http://php.net/manual/en/function.sqlsrv-free-stmt.php
  */
 public function free()
 {
     if ($this->command !== NULL) {
         @sqlsrv_free_stmt($this->command);
         $this->command = NULL;
         $this->record = FALSE;
     }
 }

 public function close()
 {
     if ($this->rsrc) {
         sqlsrv_free_stmt($this->rsrc);
         $this->rsrc = null;
     }
     $this->current = null;
 }

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT * FROM ( ";
     $query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, FAB.FirmName AS fabricante, ";
     $query .= "CHAM.dataAtendimento AS dataDespesa, CASE WHEN DESP.codigoInsumo IS NULL THEN CAST(DESP.quantidade AS VARCHAR) + 'UN ' + DESP.nomeItem ELSE (TINS.tipoInsumo + '( Número do Chamado: ' + CAST(CHAM.id AS VARCHAR) + ' )') END AS descricaoDespesa, ";
     $query .= "PROD.AvgPrice AS precoMedio, PROD.U_Durability AS durabilidade, DESP.totalDespesa ";
     $query .= "FROM MYSQL...chamadoServico CHAM ";
     $query .= "JOIN MYSQL...despesaChamado DESP ON CHAM.id = DESP.codigoChamado ";
     $query .= "LEFT JOIN MYSQL...insumo INS ON INS.id = DESP.codigoInsumo ";
     $query .= "LEFT JOIN MYSQL...tipoInsumo TINS ON INS.tipoInsumo = TINS.id ";
     $query .= "JOIN OINS EQP ON CHAM.cartaoEquipamento = EQP.insID ";
     $query .= "LEFT JOIN OITM PROD ON PROD.ItemCode = DESP.codigoItem COLLATE database_default ";
     $query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
     $query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
     $query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
     $query .= "              UNION ALL                          ";
     $query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, FAB.FirmName AS fabricante, ";
     $query .= "PED.data AS dataDespesa, CAST(ITM.quantidade AS VARCHAR) + 'UN ' + ITM.nomeItem AS descricaoDespesa, PROD.AvgPrice AS precoMedio, PROD.U_Durability AS durabilidade, ITM.total AS totalDespesa ";
     $query .= "FROM MYSQL...pedidoConsumivel PED ";
     $query .= "JOIN MYSQL...solicitacaoItem ITM ON ITM.pedidoConsumivel_id = PED.id ";
     $query .= "JOIN OINS EQP ON PED.codigoCartaoEquipamento = EQP.insID ";
     $query .= "LEFT JOIN OITM PROD ON PROD.ItemCode = ITM.codigoItem COLLATE database_default ";
     $query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
     $query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
     $query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
     $query .= "              ) DESPESAS ";
     if (isset($filter) && !empty($filter)) {
         $query = $query . " WHERE " . $filter;
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query . " ORDER BY nomeCliente, dataDespesa, serieEquipamento");
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new EquipmentExpenseDTO();
         $dto->codigoCliente = $record["codigoCliente"];
         $dto->nomeCliente = $record["nomeCliente"];
         $dto->codigoEquipamento = $record["codigoEquipamento"];
         $dto->serieEquipamento = $record["serieEquipamento"];
         $dto->codigoModelo = $record["codigoModelo"];
         $dto->tagModelo = $record["tagModelo"];
         $dto->fabricante = $record["fabricante"];
         $dto->dataDespesa = $record["dataDespesa"];
         $dto->descricaoDespesa = $record["descricaoDespesa"];
         $dto->precoMedioUnitario = $record["precoMedio"];
         $dto->vidaUtil = $record["durabilidade"];
         $dto->totalDespesa = $record["totalDespesa"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }

 function RetrieveRecordArray($filter = null)
 {
     $dtoArray = array();
     $query = "SELECT * FROM ( ";
     $query .= "SELECT EQP.customer AS codigoCliente, CLI.cardName + ' (' + CLI.cardCode + ')' AS nomeCliente, EQP.insID AS codigoEquipamento, EQP.manufSN AS serieEquipamento, MDL.id AS codigoModelo, MDL.modelo AS tagModelo, FAB.FirmName AS fabricante, ";
     $query .= "LEITURA.data AS dataLeitura, CONTADOR.nome AS tipoMedidor, LEITURA.contagem AS medicao, LEITURA.ajusteContagem AS ajusteLeitura, CASE WHEN LEITURA.reset = 0 THEN 'N' ELSE 'S' END AS reset, FUNCIONARIO.firstName + ' ' + FUNCIONARIO.lastName AS assinaturaDatacopy, ";
     $query .= "LEITURA.assinaturaCliente AS assinaturaCliente, LEITURA.obs AS observacao, FORMA.nome AS formaLeitura, ORIGEM.nome AS origemLeitura, ORIGEM.id AS idOrigemLeitura ";
     $query .= "FROM MYSQL...leitura LEITURA ";
     $query .= "JOIN OINS EQP ON LEITURA.codigoCartaoEquipamento = EQP.insID ";
     $query .= "JOIN OCRD CLI ON EQP.customer = CLI.cardCode ";
     $query .= "JOIN MYSQL...modeloEquipamento MDL ON EQP.U_Model = MDL.id ";
     $query .= "JOIN OMRC FAB ON MDL.fabricante = FAB.FirmCode ";
     $query .= "JOIN MYSQL...contador CONTADOR ON LEITURA.contador_id = CONTADOR.id ";
     $query .= "JOIN OHEM FUNCIONARIO ON LEITURA.assinaturaDatacopy = FUNCIONARIO.empId ";
     $query .= "JOIN MYSQL...formaLeitura FORMA ON LEITURA.formaLeitura_id = FORMA.id ";
     $query .= "JOIN MYSQL...origemLeitura ORIGEM ON LEITURA.origemLeitura_id = ORIGEM.id ";
     $query .= "              ) LEITURAS ";
     if (isset($filter) && !empty($filter)) {
         $query = $query . " WHERE " . $filter;
     }
     $recordSet = sqlsrv_query($this->sqlserverConnection, $query . " ORDER BY dataLeitura");
     $index = 0;
     while ($record = sqlsrv_fetch_array($recordSet, SQLSRV_FETCH_ASSOC)) {
         $dto = new EquipmentReadingDTO();
         $dto->codigoCliente = $record["codigoCliente"];
         $dto->nomeCliente = $record["nomeCliente"];
         $dto->codigoEquipamento = $record["codigoEquipamento"];
         $dto->serieEquipamento = $record["serieEquipamento"];
         $dto->codigoModelo = $record["codigoModelo"];
         $dto->tagModelo = $record["tagModelo"];
         $dto->fabricante = $record["fabricante"];
         $dto->dataLeitura = $record["dataLeitura"];
         $dto->tipoMedidor = $record["tipoMedidor"];
         $dto->medicao = $record["medicao"];
         $dto->ajusteLeitura = $record["ajusteLeitura"];
         $dto->reset = $record["reset"];
         $dto->assinaturaDatacopy = $record["assinaturaDatacopy"];
         $dto->assinaturaCliente = $record["assinaturaCliente"];
         $dto->observacao = $record["observacao"];
         $dto->formaLeitura = $record["formaLeitura"];
         $dto->origemLeitura = $record["origemLeitura"];
         $dtoArray[$index] = $dto;
         $index++;
     }
     sqlsrv_free_stmt($recordSet);
     return $dtoArray;
 }