CakePHP useTable Model with SQL Views

Question

I am in the process of converting our CakePHP-built website from Pervasive to SQL Server 2005. After many problems that I started working, I use the ADODB driver with 'connect' like odbc_mssql. It is connected with our database and perfectly builds SQL queries.

However, here's rub: one of our models was related to the SQL representation in Pervasive. I have ported the view, but it appears using the settings that I have that CakePHP cannot find the view in SQL Server.

Could not find much after some google searches - does anyone else encounter such a problem? Is there a solution / workaround, or is there some kind of redesign in my future?

Answer 1

First of all, what version of CakePHP are you using? I assume this is CakePHP 1.2 +.

Problem

I am not familiar with SQL Server 2005 (as well as any other versions), but after some research, I think the problem is with the DboMssql::listSources() method, which selects the available table names from INFORMATION_SCHEMA.TABLES , and therefore it isn’t " see "any available views.

Decision

Modify DboMssql::listSources() to select available table names from sys.tables or, if I am mistaken about sys.tables , to additionally select names from INFORMATION_SCHEMA.VIEWS .

Thus, in order not to get confused with the main CakePHP files, you need to create your own data source that extends DboMssql and cancels the ::listSources() method. For this you need:

• Create <path/to/app>/models/datasources/dbo/dbo_custom_mssql.php :

   <?php App::import('Datasource', 'DboMssql'); class DboCustomMssql extends DboMssql { public function listSources() { $cache = DboSource::listSources(); if ($cache != null) { return $cache; } $result = $this->fetchAll('SELECT TABLE_NAME FROM SYS.TABLES', false); if (!$result || empty($result)) { return array(); } else { $tables = array(); foreach ($result as $table) { $tables[] = $table[0]['TABLE_NAME']; } DboSource::listSources($tables); return $tables; } } } 

• Change config/database.php config: 'driver' => 'custom_mssql'
• Test

NB: Worst of all, the interface DboMssql::listSources() is incorrect (without the additional argument $data (for example, Datasource::listSources() declares)) and does not provide any extension point, therefore, to have the source list cached, we forced to call DboSource::listSources() instead of the broken parent::listSources() .

Answer 2

I can clearly see SQL Server views. The main difference that I am making to you is that I use the mssql driver, not the odbc_mssql driver. Perhaps you should try switching to this and finding out what problems you have in the first place.