MSSQL and Active Record love hate tragedy (1.5.4 => 1.6.2)

[eluser]Morty[/eluser]
Hi there!

I recently updated my CI from 1.5.4 to 1.6.2 and I've lost complete control of my MSSQL queries. None of them function anymore. I've tried to alter the protect identifiers function (solution seen while I was searching for answers) but to no avail.

I'm surprised that I might be the first one to be confronted with this kind of issues, but my search did not bring good results. Maybe I should buy another pair of eyes if you tell me that it can be found...

Thanks in advance for any help you may provide!

[eluser]Michael Wales[/eluser]
Do your queries involve portions that should not be escaped? Between these versions a third boolean parameter was added to many of the Active Record functions that allows you to force the query to remain unescaped.

Check out the docs for more info.

[eluser]Morty[/eluser]
I suppose that by unescaped queries you refer to NULL or NOW() for example, right?

No, I'm not using this kind of instructions.

Here is an example of a query built by the new AR and which does not work:

Code:

SELECT "ITMREF_0" AS "PN", "STOFCY_0" AS "Site", "LOC_0" AS "Loc", COUNT(*) AS NB FROM ARCEXPL.STOJOU WHERE IPTDAT_0 > '23/01/2008' GROUP BY "ITMREF_0", "STOFCY_0", "LOC_0"

You can see that even field names were escaped. I find it really strange.

Of course I want things to be escaped, but not field names.
I should have updated my CI long ago, maybe I could have been more precise about when this "bug" started to occur.

Edit: I only found that in the documentation:

Code:

In many databases it is advisable to protect table and field names - for example with backticks in MySQL. Active Record queries are automatically protected, however if you need to manually protect an identifier you can use:

$this->db->protect_identifiers('table_name');

What I want is not to protect field names for MSSQL.

[eluser]Morty[/eluser]
Guess I will revert to 1.5.4 until someone finds out what's wrong with MSSQL and CI 1.6.2...

[eluser]Derek Allard[/eluser]
Sorry for the hassle. I don't think many people are using MSSQL, but that doesn't mean I'm not interested in helping you work through it if you don't mind bearing with me.

Firstly, I think you can get it to work by opening database/drivers/mssql_driver.php and making the _protect_identifiers() function into

Code:

function _protect_identifiers($item, $first_word_only = FALSE)
{
    return $item;
}

Could you give me an example of what your AR query is, and what you'd want the output to be? I'd like to work with you to get this fixed up if you have the patience

[eluser]Morty[/eluser]
No problem. As soon as I start working on it on Monday, I'll tell you what are the issues and what would output the syntax checker integreated in Microsoft Enterprise Manager for my query (I suppose that would be the best syntax we could get for MSSQL).

[eluser]Morty[/eluser]
Here's more info, and a workaround (for the time being).

First you need to comment out the protect identifier function:

Code:

function _protect_identifiers($item, $first_word_only = FALSE)
    {
        /* if (is_array($item))
        {
            $escaped_array = array();

            foreach($item as $k=>$v)
            {
                $escaped_array[$this->_protect_identifiers($k)] = $this->_protect_identifiers($v, $first_word_only);
            }

            return $escaped_array;
        }    

        // This function may get "item1 item2" as a string, and so
        // we may need ""item1" "item2"" and not ""item1 item2""
        if (ctype_alnum($item) === FALSE)
        {
            if (strpos($item, '.') !== FALSE)
            {
                $aliased_tables = implode(".",$this->ar_aliased_tables).'.';
                $table_name =  substr($item, 0, strpos($item, '.')+1);
                $item = (strpos($aliased_tables, $table_name) !== FALSE) ? $item = $item : $this->dbprefix.$item;
            }

            // This function may get "field >= 1", and need it to return ""field" >= 1"
            $lbound = ($first_word_only === TRUE) ? '' : '|\s|\(';

            $item = preg_replace('/(^'.$lbound.')([\w\d\-\_]+?)(\s|\)|$)/iS', '$1"$2"$3', $item);
        }
        else
        {
            return "\"{$item}\"";
        }

        $exceptions = array('AS', '/', '-', '%', '+', '*', 'OR', 'IS');
        
        foreach ($exceptions as $exception)
        {
        
            if (stristr($item, " \"{$exception}\" ") !== FALSE)
            {
                $item = preg_replace('/ "('.preg_quote($exception).')" /i', ' $1 ', $item);
            }
        } */
        return $item;

Sample request when it's commented out:

Code:

SELECT ITMREF_0 AS PN, STOFCY_0 AS Site, LOC_0 AS Loc, FLOOR(SUM(QTYPCU_0)) AS qty FROM ARCEXPL.STOCK STK WHERE QTYPCU_0 <> 0 GROUP BY ITMREF_0, STOFCY_0, LOC_0 ORDER BY PN, Site, Loc

Code:

SELECT     ITMREF_0 AS PN, STOFCY_0 AS Site, LOC_0 AS Loc, FLOOR(SUM(QTYPCU_0)) AS qty
FROM         ARCEXPL.STOCK STK
WHERE     (QTYPCU_0 <> 0)
GROUP BY ITMREF_0, STOFCY_0, LOC_0
ORDER BY PN, Site, Loc

This is the same request as corrected by SQL Server Enterprise.

Another one (sent by AR, then corrected by SQLSE):

Code:

SELECT STOSER.ITMREF_0 AS PN, STOSER.SERNUM_0 AS SN FROM ARCEXPL.STOSER STOSER WHERE STOSER.STOFCY_0 = 'LU1' AND BPCNUM_0 = '' AND STOSER.SERNUM_0 IN ('G063616667', 'E066316793')

Code:

SELECT     ITMREF_0 AS PN, SERNUM_0 AS SN
FROM         ARCEXPL.STOSER STOSER
WHERE     (STOFCY_0 = 'LU1') AND (BPCNUM_0 = '') AND (SERNUM_0 IN ('G063616667', 'E066316793'))

And now what AR is doing wrong:

Code:

SELECT "ITMREF_0" AS "PN", "STOFCY_0" AS "Site", "LOC_0" AS "Loc", COUNT(*) AS NB FROM ARCEXPL.STOJOU WHERE IPTDAT_0 > '26/01/2008' GROUP BY "ITMREF_0", "STOFCY_0", "LOC_0"

It seems that field names are protected, and there is no need for it.
With workaround:

Code:

SELECT ITMREF_0 AS PN, STOFCY_0 AS Site, LOC_0 AS Loc, COUNT(*) AS NB FROM ARCEXPL.STOJOU WHERE IPTDAT_0 > '26/01/2008' GROUP BY ITMREF_0, STOFCY_0, LOC_0

Hope that'll help.

[eluser]Derek Allard[/eluser]
OK, so just to revise this, it looks like trying to protect the fields is breaking it for you?

Can anyone else confirm/verify this?

[eluser]Morty[/eluser]
Exactly. And fixing it via commenting out the protect identifiers function does not solve the same kind of issue with 'DISTINCT' statement.

[eluser]cristian_c[/eluser]
I have the same problem with mssql and active record. I did this and now is working. I dont know if this is the best solution.

Code:

function _protect_identifiers($item, $first_word_only = FALSE)
{
    return $item;
}