MS SQL Express Error

[eluser]polaris1927[/eluser]
Hi,

I am new to CodeIgnitor, so far it's okay, but I am getting a bug with scaffoding.

Below is my environment setting:

Microsoft SQL Server Management Studio Express - 9.00.3042.00
Microsoft Data Access Components (MDAC) - 2000.085.1117.00 (xpsp_sp2_rtm.040803-2158)
Microsoft MSXML - 2.6 3.0 4.0 6.0
Microsoft Internet Explorer - 7.0.5730.11
Microsoft .NET Framework - 2.0.50727.832
Operating System - 5.1.2600

Investigating a little this problem:

SQL Express defines table with a primary key and auto increment as follows:

CREATE TABLE [dbo].[entries](
[id] [int] IDENTITY(1,1) NOT NULL,
[title] [varchar](128) NULL,
[body] [text] NULL,
CONSTRAINT [PK_primary] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

----------------------------------------------------------

The database.php file is defined as follows:

$active_group = "default";
$active_record = TRUE;

$db['default']['hostname'] = "localhost\sqlexpress";
$db['default']['username'] = "pbx";
$db['default']['password'] = "pbx";
$db['default']['database'] = "blog";
$db['default']['dbdriver'] = "mssql";
$db['default']['dbprefix'] = "";
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";
$db['default']['char_set'] = "utf8";
$db['default']['dbcollat'] = "utf8_general_ci";

-----------------------------------------------------------------


The problem:

When Scaffolding produces the create view, the 'id' field is included in the table. Since the 'id' field is an auto-increment field, you cannot insert the record.

Is there a workaround?

Thanks

[eluser]fuddmain[/eluser]
I'm having the same problem. Is there a solution?

I have the latest version of CI (1.6.3) and MSSQL 2000.

Thanks.

[eluser]fuddmain[/eluser]
I've figured out this problem. In the mssql_result.php file the field_data() function uses PHP's mssql_fetch_field() function. This function does not return information on the primary key. So, the field_data() function just sets every column to primary_key = 0. My quick fix was to assume the first column would be the primary key and add a counter variable and and if statement to the field_data() function:

Code:

function field_data()
    {
        $retval = array();
        //my addition
        $counter = 1;
        while ($field = mssql_fetch_field($this->result_id))
        {    
            $F                 = new stdClass();
            $F->name         = $field->name;
            $F->type         = $field->type;
            $F->max_length    = $field->max_length;
            //my additon
            if ($counter == 1) {
                $F->primary_key = 1;
                $counter++;
            } else {
                $F->primary_key = 0;
            }
            $F->default        = '';
            
            $retval[] = $F;
        }
        
        return $retval;
    }

This works. Now I have to run down an issue with the scaffolding view choking on tables with an underscore: tbl_Entries.

[eluser]fuddmain[/eluser]
The second problem, mentioned above, happened when using the Scaffolding to view a table that has an underscore in the name (tbl_Entries). It would work just fine without the underscore (Entries). The problem was in the _list_columns() function in mssql_driver.php. It returns a sql string:

Code:

return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$this->_escape_table($table)."'";

In this case, the table we are concerned about (tbl_Entries) is not being queried, rather, it's name is passed to a query to look up column information. So, the table name does not need to be escaped.

The new query string:

Code:

return "SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = '".$table."'";

This won't be a problem if you have not changed the _escape_table function in mssql_drive.php, which does not actually do anything. I had changed this function based on this post in the forum.

Cheers.