Not compatible with SQLSRV driver.

GroceryCrudModel.php appears to have an explicit query to collect field names. This breaks functionality when using CodeIgniter 4 with it's SQLSRV driver (For MS SQL).

I replaced the get_field_types_basic_table() function and also added the check_db_extra() function per a suggestion at the office Grocery Crud forum. However, now the datatables view loads but the data is coming back empty (no rows). The table in question does have data and the datatables is successfully pulling the fields corrrectly. Does another function need updating to query for data?

Here is the two functions I aforementioned that have been added to the GroceryCrudModel.php file. Please help!

function get_field_types_basic_table()
  {
        $db_field_types = array();
        //thanks to marc_s for this nice query
        $show_colums = "SELECT 
                            c.name 'field',
                            t.name 'type',
                            c.max_length 'max_length',
                            c.precision ,
                            c.scale ,
                            c.is_nullable,
                            ISNULL(i.is_primary_key, 0) 'primary_key'
                        FROM    
                            sys.columns c
                        INNER JOIN 
                            sys.types t ON c.system_type_id = t.system_type_id
                        LEFT OUTER JOIN 
                            sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
                        LEFT OUTER JOIN 
                            sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
                        WHERE
                            c.object_id = OBJECT_ID(?)
                        AND t.name <> 'sysname'";

        $rows_metadata = $this->db->query($show_colums, array($this->table_name));

        foreach ($rows_metadata->getResult() as $db_field_type) {

            $db_field_types[$db_field_type->field]['db_max_length'] = $db_field_type->max_length;
            $db_field_types[$db_field_type->field]['db_type']       = $db_field_type->type;
            $db_field_types[$db_field_type->field]['db_null']       = ($db_field_type->is_nullable == 1) ? true : false;
            $db_field_types[$db_field_type->field]['primary_key']   = $db_field_type->primary_key;
            $db_field_types[$db_field_type->field]['name']          = $db_field_type->field;
            $db_field_types[$db_field_type->field]['db_extra']      = $this->check_db_extra($db_field_type);
        }

        $results = $this->get_field_types($this->table_name);

        foreach($results as $num => $row)
        {
            $row = (array)$row;
            $results[$num] = (object)( array_merge($row, $db_field_types[$row['name']])  );
            $results[$num]->type = $results[$num]->db_type; 
        }

        return $results;
    }

    /**
     * Check id field is identity and assign extra properties to it
     * @param  object $db_field_type field meta-data
     * @return string extra property
     */
    public function check_db_extra($db_field_type)
    {   
        $extra = '';
        return ($db_field_type->primary_key === 1) 
                ? $extra = 'auto_increment'
                : $extra = '';
    }

scoumbourdis / grocery-crud-codeigniter-4

Not compatible with SQLSRV driver. #18