Mssql Incorrect Brackets

Yii 1.1 General Discussion
1

I am having some problems with Mssql 2008 and Yii Framework with incorrect bracket placement when using the query builder.




$queryCommand = Yii::app()->db->createCommand();

$selectArray = array();

$selectArray[] = 't.id';

$selectArray[] = "CASE t.name WHEN '' THEN t.name ELSE t.name + ' : ' + t.description END AS text";

$queryCommand->select($selectArray);

$queryCommand->from("table t");

I get a weird query string.


SELECT [t].[id], [CASE] AS [t].[name WHEN '' THEN t].[name ELSE t].[name + ' : ' + t].[description END AS text]

FROM [table] [t]

Is there a better way to do the case?

If I add a SUBSTRING function to the t.description no brackets are added.




$queryCommand = Yii::app()->db->createCommand();

$selectArray = array();

$selectArray[] = 't.id';

$selectArray[] = "CASE t.name WHEN '' THEN t.name ELSE t.name + ' : ' + SUBSTRING(t.description,0,30) END AS text";

$queryCommand->select($selectArray);

$queryCommand->from("table t");

I get




SELECT [t].[id], CASE t.name WHEN '' THEN t.name ELSE t.name + ' : ' +    SUBSTRING(t.description,0,30) END AS text

FROM [table] [t]

Which runs fine because the brackets aren’t required.

2

In MSSQL the brackets are used to delimit identifiers, Yii places them automatically in case you clash with a reserved keyword. They don’t create any problems to your DB and you can actually copy and past with the brackets to your MSSQL and it will run fine. I used MSSQL with Yii for a while now and I can tell you the brackets are harmless. I do suggest however you avoid using Yii with MSSQL and if you can switch to MySQL or Sqlite do so. You will need drivers on the server, there’s a fault with pagination and there will be some little problems on the way.

All fixable however.

If you have to use MSSQL (like I did as the DB was ready) then go for it but be patient.

Cheers