CodeIgniter Forums

Full Version: Problems with sqlsrv DB driver and CI 3
You're currently viewing a stripped down version of our content. View the full version with proper formatting.
Hi,

I'm migrating my web app from CI 2.x to CI 3.0. My app uses the 'sqlsrv' driver.

When I try to open my web app I get database errors, for example:

Code:
Error Number: 42000/102

[Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near ')'.

SELECT "Data" "Data" FROM (SELECT cf.idCalendari idCalendari, "f"."Data"" FROM GT_CalendarisFestius cf LEFT JOIN GT_Festius f ON cf"."idCalendari = f"."idCalendari)" "festius" WHERE "festius"."idCalendari" IN(5, 6)

I don't know why CI 3 are putting doble quotes inside the SQL, and are doing it badly (as you can see the SQL is bad constructed).

The model is this:


Code:
$this->db->select('Data Data');
$this->db->from('(SELECT cf.idCalendari idCalendari, f."Data" FROM GT_CalendarisFestius cf LEFT JOIN GT_Festius f ON cf.idCalendari = f.idCalendari) festius');
$this->db->where_in('festius.idCalendari', array(5,6));

The same happens with a SELECT like this:

Code:
$this->db->select("CONVERT(VARCHAR(10),a.Dia,105) as Day")

It get transformed to this:

Code:
SELECT CONVERT(VARCHAR(10), "a"."Dia", "105 )" as "Day"

How can I solve it? Is it possible to disable this double quote annoying bug?

Thx
disable escaping or don't use QB for complex queries
Related: http://forum.codeigniter.com/thread-61730.html
(05-28-2015, 11:08 AM)gadelat Wrote: [ -> ]disable escaping or don't use QB for complex queries
Related: http://forum.codeigniter.com/thread-61730.html

How can I disable escaping for from() ?
Why it worked with CI 2 and it doesn't with CI 3?
Something changed in the logic for either _track_aliases() or protect_identifiers() (or both) which made it nearly impossible to use queries in the from clause in CI3. In the end, you'll probably have to convert it to use $this->db->query() instead of query builder.

The $_protect_identifiers property is protected in CI3, so I'm not sure what the "supported" method is for disabling this feature.
Thx mwhitney and gadelat.

I think that it must appear an advice in the upgrade guide (http://www.codeigniter.com/userguide3/in...e_300.html). If I had known this would happen I would not have even started the migration!

I've tones of queries to change :-( If someone knows how to disable escaping in the from clause it would be appreciated. Meanwhile I will stay with CI 2.
For people getting the same errors with subselects in the form() method you can bypass the escaping with parenthesis.

Not working example:

PHP Code:
$this->db->from("(SELECT field, max(field2)
                            FROM table
                            WHERE field3=1
                            GROUP BY field) con"
); 


Working example:

PHP Code:
$this->db->from("(SELECT field, max(field2)
                            FROM table
                            WHERE field3=1
                            GROUP BY (field)) virtualTable"
);