Codeigniter删除记录失败 - Codeigniter Deleting Record Fails

我试图在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

工作完美的MS SQL管理器

但是，下面的代码失败了:

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');
    }
}

有以下错误:

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

正如你在这里看到的，这个error报告的查询是在MS SQL Manager中运行的相同的查询…

我能做些什么来让它在我的CodeIgniter代码中工作?

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');

在与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');

try this

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, FALSE);
    $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');
    }
}

where()中的，NULL,FALSE告诉CodeIgniter不要转义查询，否则可能会把它弄乱。