Codeigniter删除记录失败

Question

I am attempting to run the following query in CodeIgniter:

DELETE FROM TblOrders 
WHERE 
TblOrders.orderPaid = 0 
AND 
TblOrders.orderStarted < '02/15/2014' 
AND 
(Select Top 1 paymentID From TblOrderPayments Where TblOrderPayments.orderID = TblOrders.orderID) Is NULL

Works perfect in MS SQL Manager.

However, the following code fails:

public function clearup(){
    $this->db->where('TblOrders.orderPaid', 0);
    $this->db->where('TblOrders.orderStarted <', date('m/d/Y', strtotime('-30 days')));
    $this->db->where('(Select Top 1 paymentID From TblOrderPayments Where TblOrderPayments.orderID = TblOrders.orderID) Is NULL', null);
    $this->db->delete('TblOrders');
    var_dump($this->db->last_query());
    exit;
    if($this->db->affected_rows() > 0){
        $this->session->set_userdata(array('okMsg'=>'Your old un-paid orders have been removed from the system.'));
        redirect('/orders/current', 'refresh');
    }else{
        $this->session->set_userdata(array('errMsg'=>'There was an issue clearing out your old orders.'));
        redirect('/orders/current', 'refresh');
    }
}

With the following error:

DELETE failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDING'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.

DELETE FROM TblOrders WHERE TblOrders.orderPaid = 0 AND TblOrders.orderStarted < '02/15/2014' AND (Select Top 1 paymentID From TblOrderPayments Where TblOrderPayments.orderID = TblOrders.orderID) Is NULL

Filename: /media/ServerStorage/Sites/tk.admin/models/orders_m.php

As you can see here, the query this error is reporting is the same query run in MS SQL Manager...

What can I do to get it to work in my CodeIgniter code?

Answer 1

Try

$this->db->query('SET ANSI_NULLS ON');
$this->db->query('SET QUOTED_IDENTIFIER ON');
$this->db->query('SET CONCAT_NULL_YIELDS_NULL ON'); 
$this->db->query('SET ANSI_WARNINGS ON');
$this->db->query('SET ANSI_PADDING ON');

before interacting with the db:

$this->db->query('SET ANSI_NULLS ON');
$this->db->query('SET QUOTED_IDENTIFIER ON');
$this->db->query('SET CONCAT_NULL_YIELDS_NULL ON'); 
$this->db->query('SET ANSI_WARNINGS ON');
$this->db->query('SET ANSI_PADDING ON');
$this->db->where('TblOrders.orderPaid', 0);
$this->db->where('TblOrders.orderStarted <', date('m/d/Y', strtotime('-30 days')));
$this->db->where('(Select Top 1 paymentID From TblOrderPayments Where TblOrderPayments.orderID = TblOrders.orderID) Is NULL', null);
$this->db->delete('TblOrders');