Codeigniter Deleting Record Fails

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?

Just a quick observation, try setting this to a variable before you run your query and just putting the variable in the second argument. `date('m/d/Y', strtotime('-30 days')` — Chitowns24, Mar 17 '14 at 16:48
Yeah, I know. That was just a different observation remove this `, null` in your third where clause and let me know what you get — Chitowns24, Mar 17 '14 at 16:53
See if this link helps you out http://stackoverflow.com/questions/8388272/error-deleting-all-tables-delete-failed-because-the-following-set-options-have — Chitowns24, Mar 17 '14 at 16:56
@Chitowns24 removing the null makes no difference, as for the second link, please see my question where I state the the query runs without issue inside MS SQL Server Manager — Kevin, Mar 17 '14 at 17:16

stormdrain · Accepted Answer · 2014-03-19T13:02:14.947

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

edited Mar 19 '14 at 13:02

answered Mar 17 '14 at 18:21

stormdrain

7,915
4
37
76

Incorrect syntax error on ANSI_NULLS ON line. SQL 2012 – Kevin Mar 19 '14 at 12:45
Forgot `SET`. Updated. – stormdrain Mar 19 '14 at 13:02
thanks... now on to other errors... :( gonna be one of those days LOL – Kevin Mar 19 '14 at 13:11

score 0 · Answer 2 · answered Mar 19 '14 at 12:32

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

The ,NULL,FALSE in the where() tells CodeIgniter not to escape the query, which may mess it up.

Codeigniter Deleting Record Fails

2 Answers2