FrontAccounting forum — Active/Inactive bank

Re: Active/Inactive bank

null@example.com (dvarsani) — Wed, 06 Mar 2013 16:24:07 +0000

Hi Joe

The fix has solve the bug.

Thanks

Re: Active/Inactive bank

null@example.com (joe) — Wed, 06 Mar 2013 15:02:54 +0000

The cast fix is rather from Janusz

Joe

Re: Active/Inactive bank

null@example.com (apmuthu) — Wed, 06 Mar 2013 11:22:49 +0000

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.

Re: Active/Inactive bank

null@example.com (joe) — Tue, 05 Mar 2013 22:36:42 +0000

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

Re: Active/Inactive bank

null@example.com (dvarsani) — Tue, 05 Mar 2013 17:29:16 +0000

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'

Re: Active/Inactive bank

null@example.com (apmuthu) — Tue, 05 Mar 2013 15:09:59 +0000

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

Re: Active/Inactive bank

null@example.com (dvarsani) — Tue, 05 Mar 2013 11:42:02 +0000

I am using 2.3.15

Re: Active/Inactive bank

null@example.com (doitashimashite) — Tue, 05 Mar 2013 10:59:17 +0000

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

Active/Inactive bank

null@example.com (dvarsani) — Tue, 05 Mar 2013 10:40:41 +0000

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'