mssql.php

Ir a la documentación de este archivo.

<?php
class DbMsSQL extends DbBase implements DbBaseInterface  {
 
        public $id_connection;
 
        public $last_result_query;
 
        protected $last_query;
 
        public $last_error;
 
        const DB_ASSOC = MSSQL_ASSOC;
 
        const DB_BOTH = MSSQL_BOTH;
 
        const DB_NUM = MSSQL_NUM;
 
        const TYPE_INTEGER = 'INT';
 
        const TYPE_DATE = 'SMALLDATETIME';
 
        const TYPE_VARCHAR = 'VARCHAR';
 
        const TYPE_DECIMAL = 'DECIMAL';
 
        const TYPE_DATETIME = 'DATETIME';
 
        const TYPE_CHAR = 'CHAR';
 
        public function connect($config){
                if(!extension_loaded('mssql')){
                        throw new KumbiaException('Debe cargar la extensión de PHP llamada php_mssql');
                }
                if(!isset($config['port']) || !$config['port']) {
                        $config['port'] = 1433;
                }
                //if($this->id_connection = mssql_connect("{$config['host']},{$config['port']}", $config['username'], $config['password'], true)){
                if($this->id_connection = mssql_connect($config['host'], $config['username'], $config['password'], true)){
                        if($config['name']!=='') {
                                if(!mssql_select_db($config['name'], $this->id_connection)){
                                        throw new KumbiaException($this->error());
                                }
                        }
                        return true;
                } else {
                        throw new KumbiaException($this->error());
                }
        }
        public function query($sql_query){
                $this->debug($sql_query);
                if($this->logger){
                    Logger::debug($sql_query);
                }
                
                $this->last_query = $sql_query;
                if($result_query = mssql_query($sql_query, $this->id_connection)){
                        $this->last_result_query = $result_query;
                        return $result_query;
                }else{
                        throw new KumbiaException($this->error(" al ejecutar <em>\"$sql_query\"</em>"));
                }
        }
        public function close(){
                if($this->id_connection) {
                        return mssql_close();
                }
                return false;
        }
        public function fetch_array($result_query='', $opt=MSSQL_BOTH){

                if(!$result_query){
                        $result_query = $this->last_result_query;
                        if(!$result_query){
                                return false;
                        }
                }
                return mssql_fetch_array($result_query, $opt);
        }
        public function __construct($config){
                $this->connect($config);
        }
        public function num_rows($result_query=''){

                if(!$result_query){
                        $result_query = $this->last_result_query;
                        if(!$result_query){
                                return false;
                        }
                }
                if(($number_rows = mssql_num_rows($result_query))!==false){
                        return $number_rows;
                } else {
                        throw new KumbiaException($this->error());
                }
        }
 
        public function field_name($number, $result_query=''){

                if(!$result_query){
                        $result_query = $this->last_result_query;
                        if(!$result_query){
                                return false;
                        }
                }
                if(($fieldName = mssql_field_name($result_query, $number))!==false){
                        return $fieldName;
                } else {
                        throw new KumbiaException($this->error());
                }
        }
        public function data_seek($number, $result_query=''){
                if(!$result_query){
                        $result_query = $this->last_result_query;
                        if(!$result_query){
                                return false;
                        }
                }
                if(($success = mssql_data_seek($result_query, $number))!==false){
                        return $success;
                } else {
                        throw new KumbiaException($this->error());
                }
        }
 
        public function affected_rows($result_query=''){
                if(($numberRows = mssql_affected_rows())!==false){
                        return $numberRows;
                } else {
                        throw new KumbiaException($this->error());
                }
        }
 
        public function error($err=''){
                if(!$this->id_connection){
                        $this->last_error = mssql_get_last_message() ? mssql_get_last_message() : "[Error Desconocido en MsSQL: $err]";
                        if($this->logger){
                                Logger::error($this->last_error);
                        }
                        return $this->last_error;
                }
                $this->last_error = mssql_get_last_message() ? mssql_get_last_message() : "[Error Desconocido en MsSQL: $err]";
                $this->last_error.= $err;
                if($this->logger){
                    Logger::error($this->last_error);
                }
                return $this->last_error;
        }
        public function no_error(){
                return mssql_errno();
        }
        public function last_insert_id($table='', $primary_key=''){

                //$id = false;
                $result = mssql_query("select max({$primary_key}) from $table");
                if ($row = mssql_fetch_row($result)) {
                        $this->id_connection = trim($row[0]);
                }
                mssql_free_result($result);
                return $this->id_connection;
        }
        public function table_exists($table, $schema=''){
                $table = addslashes("$table");
                if($schema==''){
                        $num = $this->fetch_one("SELECT COUNT(*) FROM
                                                INFORMATION_SCHEMA.TABLES
                                                WHERE TABLE_NAME = '$table'");
                } else {
                        $schema = addslashes("$schema");
                        $num = $this->fetch_one("SELECT COUNT(*) FROM
                                                INFORMATION_SCHEMA.TABLES
                                                WHERE TABLE_NAME = '$table'
                                                AND TABLE_SCHEMA = '$schema'");
                }
                return $num[0];
        }
        public function limit($sql){
                $params = Util::getParams(func_get_args());
                
                //TODO: añadirle el offset
                if(isset($params['limit'])){
                        $sql = str_ireplace("SELECT ", "SELECT TOP $params[limit] ", $sql);
                }               
                return $sql;
        }
 
        public function drop_table($table, $if_exists=true){
                if($if_exists){
                        $sql = "IF EXISTS(SELECT TABLE_NAME FROM
                        INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = '$table')
                        DROP TABLE $table;";
                        return $this->query($sql);
                } else {
                        return $this->query("DROP TABLE $table");
                }
        }
        public function list_tables(){
                return $this->fetch_all("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES");
        }
 
        /*
        public function describe_table($table, $schema=''){
                $sql = "SELECT A.name as Field,
                        (case when A.isnullable=0 then 'NO' when A.isnullable=1 then 'YES' end) as 'Null',
                        (case when A.colorder=1 then 'PRI' when A.colorder>1 then '' end ) as 'Key',
                        convert(varchar, C.name) + '(' + convert(varchar, (A.length))  + ')' as 'Type',
                        (case when A.cdefault=0 then 'NULL' when A.cdefault<>0 then '0' end) as 'Default'
                        FROM syscolumns A
                        left join  sysobjects B on A.id = B.id
                        left join systypes C on C.xtype = A.xtype
                        WHERE  B.name = '$table'";
                return $this->fetch_all($sql);          
        }
        */
        public function describe_table($table, $schema=''){
                $describeTable = $this->fetch_all("exec sp_columns @table_name = '$table'");
                $finalDescribe = array();
                foreach($describeTable as $field){
                        $finalDescribe[] = array(
                                'Field' => $field['COLUMN_NAME'],
                                'Type' => $field['LENGTH'] ? $field['TYPE_NAME'] : $field['TYPE_NAME'].'('.$field['LENGTH'].')',
                                'Null' => $field['NULLABLE'] == 1 ? 'YES' : 'NO'
                        );
                }
                $describeKeys = $this->fetch_all("exec sp_pkeys @table_name = '$table'");
                foreach($describeKeys as $field){
                        for($i=0;$i<=count($finalDescribe)-1;++$i){
                                if($finalDescribe[$i]['Field']==$field['COLUMN_NAME']){
                                        $finalDescribe[$i]['Key'] = 'PRI';
                                } else {
                                        $finalDescribe[$i]['Key'] = "";
                                }
                        }
                }
                return $finalDescribe;
        }

        
        public function fetch_object($result_query = NULL){
                if(!$result_query){
                        $result_query = $this->last_result_query;
                }
                return mssql_fetch_object($result_query);
        }
        
        public function create_table ($table, $definition, $index = array()){
                
        }
}