0

I've got a CakePHP application connecting to a remote MSSQL server through ODBC, but it's not working as planned. Every query dies becasue it is trying to put backticks around identifiers, which is not correct for MSSQL.

As an example, I have a model called Item for a table called items, and when I call

$this->Item->find('all')

it tries to use the query 

SELECT `Item`.`id`, `Item`.`name`, `Item`.`descrtiption` FROM `items` AS `Item` WHERE 1

...and I get an error about invalid syntax near ` at line 1.
Is there anyway to prevent this behaviour and remove the backticks? Or else use square brackets like SQL Server seems to like?

death_au
  • 1,282
  • 2
  • 20
  • 41

2 Answers2

2

I recently took a good look around the odbc driver with the intention of using it against MSSQL 2008 in CakePHP 1.3. Unless you are prepared to put a considerable amount of work in then it's not feasible at present.

Your immediate problem is that you need to override the default quotes with [ and ]. These are set at the top of the dbo_odbc.php file here

var $startQuote = "[";
var $endQuote = "]";

Once you do this the next issue you will run into is the default use of LIMIT, so you'll need to provide your own limiting function copied from dbo_mssq to override

/**
 * Returns a limit statement in the correct format for the particular database.
 *
 * @param integer $limit Limit of results returned
 * @param integer $offset Offset from which to start results
 * @return string SQL limit/offset statement
 */
    function limit($limit, $offset = null) {
        if ($limit) {
            $rt = '';
            if (!strpos(strtolower($limit), 'top') || strpos(strtolower($limit), 'top') === 0) {
                $rt = ' TOP';
            }
            $rt .= ' ' . $limit;
            if (is_int($offset) && $offset > 0) {
                $rt .= ' OFFSET ' . $offset;
            }
            return $rt;
        }
        return null;
    }

You'll then run into two problems, neither of which I solved.

  1. In the describe function the odbc_field_type call is not returning a field type. I'm not sure how critical this is if you describe the fields in the model, but it doesn't sound promising.
  2. More crucially, in the fields function that's used to generate a field list cake works by recursively exploding the . syntax to generate a series of AS aliases. This is fine if you're recursion level is zero, but with deeper recursion you end up with a field list that looks something like 'this.that.other AS this_dot_that.other AS this_dot_that_dot_other', which is invalid MSSQL syntax.

Neither of these are unsolvable, but at this point I decided it was simpler to reload my server and use the MSSQL driver than continue to chase prblems with the ODBC driver, but YMMV

ADDED: This question seems to be getting a bit of attention: so anyone who takes this further could they append their code to this answer - and hopefully we can assemble a solution between us.

Cruachan
  • 15,733
  • 5
  • 59
  • 112
  • We didn't have that much data to pull out of the database, so we ended up just writing the queries ourselves. Bad practice, but it worked for our needs. I'm marking this as the answer because it's the best explanation of the problem at hand, and does go most of the way to providing a workable solution. – death_au Sep 13 '11 at 04:32
  • Similar story here. I was attempting to add a second instance of SQL Server (2008r2) to a production website because I couldn't upgrade the default SQL2005 on there. I spent several hours paching my head against trying to get PHP native drivers to connect to a non-default named instance, then attempted to use ODBC when that didn't work - and subsequently drew a blank there too. In the end I just crossed my fingers, told the users the websites would be down overnight and uninstalled SQL2005 completely before replacing it with with SQL2008 and adding back everything. Much simpler!!! – Cruachan Sep 13 '11 at 21:01
0

why dont you just use the mssql dbo https://github.com/cakephp/cakephp/blob/master/cake/libs/model/datasources/dbo/dbo_mssql.php

dogmatic69
  • 7,574
  • 4
  • 31
  • 49
  • Had more problems with that than it's worth, not the least of which was that every connection we tried to make ended up saying it could not connect to the server. The ODBC one connects fine. – death_au Feb 15 '11 at 22:30