Scaffolding and MS SQL Server 2000

[eluser]Unknown[/eluser]
Didn't find this on the forums. Database connection set up with ODBC driver to local SQL Server 2000. WinXP Pro x64, SQL Server 2000 RTM, PHP 5.2.3, Apache 2.0.59, CI 1.5.4. Working through the Blog example, and trying to get scaffolding set up:


database.php:

Code:

$active_group = "default";

$db['default']['hostname'] = "MYDSN";
$db['default']['username'] = "myusername";
$db['default']['password'] = "mypassword";
$db['default']['database'] = "MYDB";
$db['default']['dbdriver'] = "odbc";
$db['default']['dbprefix'] = "";
$db['default']['active_r'] = TRUE;
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = "";

Controller:

Code:

<?php
class Blog extends Controller {

       function Blog()
       {
            parent::Controller();
            $this->load->database();
            $this->load->scaffolding('table_name');
       }

}
?>

When I go to the scaffolding URL, I get the following error:

An Error Was Encountered

Error Number: 37000

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '`'.

SELECT COUNT(*) AS numrows FROM `table_name`

Apparently SQL Server 2000 does not like backwards ticks in queries. I verified this in MS SQL Query Analyzer. Normal single-quotes produce the same error; only double-quotes do not produce an error in Query Analyzer.

[eluser]Unknown[/eluser]
Maybe setting up your connection driver to 'mssql' instead of 'ODBC' would solve the problem.

I think scaffolding use ActiveRecord in CodeIgniter, which have their own query generator based on your connection driver settings. Settings your driver to 'ODBC' didn't tell CodeIgniter exactly what database you're going to use, thus they can't know what query generator engine to pick.