Welcome Guest, Not a member yet? Register   Sign In
Problems with sqlsrv DB driver and CI 3
#1

(This post was last modified: 05-28-2015, 05:38 AM by dimas.)

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
Reply
#2

disable escaping or don't use QB for complex queries
Related: http://forum.codeigniter.com/thread-61730.html
Reply
#3

(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?
Reply
#4

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.
Reply
#5

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.
Reply
#6

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"
); 
Reply




Theme © iAndrew 2016 - Forum software by © MyBB