• From: UK
  • Registered: 10/02/2012
  • Posts: 146

Topic: Active/Inactive bank

There is a bug on bank accounts. When I untick inactive box I am getting this error. I am also getting this type when I make taxes active from inactive status.

DATABASE ERROR : Can't update record status
error code : 1366
error message : Incorrect integer value: '' for column 'inactive' at row 1
sql that failed was : UPDATE 0_bank_accounts SET inactive = '' WHERE id='6'

  • From: Netherlands, Amsterdam
  • Registered: 01/09/2013
  • Posts: 16

Re: Active/Inactive bank

No problem here. Which version are you using? I tested with v 2.3.15.

  • From: UK
  • Registered: 10/02/2012
  • Posts: 146

Re: Active/Inactive bank

I am using 2.3.15

4 Reply by apmuthu (edited by apmuthu 03/05/2013 04:02:45 pm)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Active/Inactive bank

Roles and Permissions issue?
Should the statement be to set inactive=0 instead of inactive=''?

Offending code is at lines 56-61 in includes/db/sql_functions.inc:

function update_record_status($id, $status, $table, $key) {
    $sql = "UPDATE ".TB_PREF.$table." SET inactive = "
        . db_escape($status)." WHERE $key=".db_escape($id);
        
      db_query($sql, "Can't update record status");
}

Beware of legacy issues of if and when the column changed to be integer and whether all such tables have the same field type for field name inactive.

There are a total of 31 tables having inactive tinyint(1) and none having a field name of inactive with any other field type. Extensions are another matter though and whether they use this function here is also to be checked especially if they are not tinyint(1) or any int() for that matter.

Therefore it can be safely be changed to:

function update_record_status($id, $status, $table, $key) {
    $sql = "UPDATE ".TB_PREF.$table." SET inactive = " . $status+0
                . " WHERE $key=".db_escape($id);
        
      db_query($sql, "Can't update record status");
}

apmuthu's Website

  • From: UK
  • Registered: 10/02/2012
  • Posts: 146

Re: Active/Inactive bank

Hi

after updating the .inc file, I am now getting this error

DATABASE ERROR : Can't update record status
error code : 1064
error message : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0 WHERE id='6'' at line 1
sql that failed was : 0 WHERE id='6'

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Active/Inactive bank

Hello guys,

Yes, certain releases of MySql don't like this.

This has now been fixed in file sql_functions.inc. Please download and replace on your server.

HG repository updated.

/Joe

Post's attachments

sql_functions.inc 1.7 kb, 2 downloads since 2013-03-05 

You don't have the permssions to download the attachments of this post.

7 Reply by apmuthu (edited by apmuthu 03/06/2013 11:46:53 am)

  • From: Singapore, India, Chennai
  • Registered: 05/02/2009
  • Posts: 6,160

Re: Active/Inactive bank

Thanks Joe. HG 3196 fixes it. Your cast fix is elegant:

$sql = "UPDATE ".TB_PREF.$table." SET inactive = "
        . ((int)$status)." WHERE $key=".db_escape($id);

The list of tables and fields that have a non integer primary key or multiple keys and having field name "inactive" are:

Table Name      Primary Key      Field Type
chart_class      cid                     varchar(3)
chart_master   account_code    varchar(15)
chart_types     id                      varchar(10)
currencies        curr_abbrev      char(3)
item_units       abbr                     varchar(20)
locations          loc_code           varchar(5)
stock_master   stock_id            varchar(20)

cust_branch     branch_code, debtor_no       int(11), int(11)

It is the last one above that is worrisome as the function used assumes a single primary key only. Kindly test what happens when same debtor is in multiple branches (or multiple debtors for same branch) and it is sought to make inactive a certain branch_code/debtor_no combination only.

apmuthu's Website

  • joe
  • Administrator
  • Offline
  • Registered: 03/15/2007
  • Posts: 5,107

Re: Active/Inactive bank

The cast fix is rather from Janusz smile

Joe

  • From: UK
  • Registered: 10/02/2012
  • Posts: 146

Re: Active/Inactive bank

Hi Joe

The fix has solve the bug.

Thanks