FrontAccounting forum — Query page size bug

Re: Query page size bug

null@example.com (dvarsani) — Thu, 02 Jan 2014 13:01:41 +0000

Hi
I sorted this issue with updating my column query_size to SMALLINT.
My users prefer viewing 200 lines per page (less pages to view) rather than clicking "Next" for 20/25 times. These days everybody uses mouse wheel to scroll up/down. I think we should change this in FA's new version.
Thanks

Re: Query page size bug

null@example.com (apmuthu) — Mon, 30 Dec 2013 18:04:01 +0000

Updated in my personal GitHub Repo.

For those not intending to wait for FA v2.4, grab the code from here.

Re: Query page size bug

null@example.com (apmuthu) — Mon, 30 Dec 2013 17:36:00 +0000

ALTER TABLE `0_users` CHANGE `query_size` `query_size` TINYINT(1) UNSIGNED NOT NULL DEFAULT 10;

Do that for each of the companies (replacing the table prefix "0_" with the appropriate one for each company) and for all Charts of Accounts in the sql folder.

Re: Query page size bug

null@example.com (joe) — Mon, 30 Dec 2013 09:34:55 +0000

Will do that for next major, 2.4

For those who need this now, please run an ALTER command on the user table.

Joe

Re: Query page size bug

null@example.com (tm) — Mon, 30 Dec 2013 09:15:50 +0000

Well... Just make it an unsigned TINYINT, then. That really should be enough for anybody. (I would have thought 7 bits would be enough, but...)

There is no reason to allow negative values for any "size", and it shouldn't affect data already stored in the database.

Re: Query page size bug

null@example.com (joe) — Mon, 30 Dec 2013 07:34:57 +0000

Thanks apmuthu.
We nevewr thought that the Query size should be that big. with 127 items on page you don't see all lines. In my opinion, suitable values would be 10-30 or 40 rows.

/Joe

Re: Query page size bug

null@example.com (apmuthu) — Mon, 30 Dec 2013 05:39:41 +0000

Wiki-ed it with screenshot.

Re: Query page size bug

null@example.com (apmuthu) — Mon, 30 Dec 2013 05:06:13 +0000

The current version on the Official GitHub is still TINYINT for query_size and is VARCHAR(20) for page_size.

CREATE TABLE IF NOT EXISTS `0_users` (
  `id` smallint(6) NOT NULL auto_increment,
  `user_id` varchar(60) NOT NULL default '',
  `password` varchar(100) NOT NULL default '',
  `real_name` varchar(100) NOT NULL default '',
  `role_id` int(11) NOT NULL default '1',
  `phone` varchar(30) NOT NULL default '',
  `email` varchar(100) default NULL,
  `language` varchar(20) default NULL,
  `date_format` tinyint(1) NOT NULL default '0',
  `date_sep` tinyint(1) NOT NULL default '0',
  `tho_sep` tinyint(1) NOT NULL default '0',
  `dec_sep` tinyint(1) NOT NULL default '0',
  `theme` varchar(20) NOT NULL default 'default',
  `page_size` varchar(20) NOT NULL default 'A4',
  `prices_dec` smallint(6) NOT NULL default '2',
  `qty_dec` smallint(6) NOT NULL default '2',
  `rates_dec` smallint(6) NOT NULL default '4',
  `percent_dec` smallint(6) NOT NULL default '1',
  `show_gl` tinyint(1) NOT NULL default '1',
  `show_codes` tinyint(1) NOT NULL default '0',
  `show_hints` tinyint(1) NOT NULL default '0',
  `last_visit_date` datetime default NULL,
  `query_size` tinyint(1) default '10',
  `graphic_links` tinyint(1) default '1',
  `pos` smallint(6) default '1',
  `print_profile` varchar(30) NOT NULL default '1',
  `rep_popup` tinyint(1) default '1',
  `sticky_doc_date` tinyint(1) default '0',
  `startup_tab` varchar(20) NOT NULL default '',
  `inactive` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `user_id` (`user_id`)
) ENGINE=MyISAM  AUTO_INCREMENT=2 ;

The column query_size has not changed since FA v2.1 as seen from:

File: sql/alter2.1.sql
Line 74: ALTER TABLE `0_users` ADD `query_size` TINYINT(1) DEFAULT '10';

Re: Query page size bug

null@example.com (tm) — Mon, 30 Dec 2013 00:15:33 +0000

On my system (2.3.15) the query_size column is defined as a TINYINT, which is a signed 8 bit integer. So, 128 would indeed be outside its range.

Since mfaa98 didn't have this problem, I guess it's been fixed in a later version. Easiest solution would be upgrading. (I'm just waiting for the new year, before I do that.)

If that's not an option, I suggest you google "mysql alter table".

Re: Query page size bug

null@example.com (mfaa98) — Sun, 29 Dec 2013 22:30:04 +0000

dvarsani, i ran your sql query in my test database and it ran without problem. may be something wrong with your table structure?

Re: Query page size bug

null@example.com (dvarsani) — Sun, 29 Dec 2013 18:55:22 +0000

I think you have misunderstood my error. It is the field in preferences which takes number of rows value to displayed for on-screen transaction report ie custom transaction inquiry.

Re: Query page size bug

null@example.com (apmuthu) — Sun, 29 Dec 2013 18:27:27 +0000

Page Size should be either A4 or Letter

Query page size bug

null@example.com (dvarsani) — Sun, 29 Dec 2013 11:45:04 +0000

Hi
I am getting this error when I am trying update Query page size above 127.

DATABASE ERROR :
At file C:\Frontaccounting\admin\db\users_db.inc:65:
could not update user display prefs for 2
error code : 1264
error message : Out of range value for column 'query_size' at row 1
sql that failed was : UPDATE 0_users SET prices_dec='2',qty_dec='2',rates_dec='4',percent_dec='2',date_format='1',date_sep='0',tho_sep='0',dec_sep='0',print_profile='',theme='cool',page_size='A4',language='C',startup_tab='orders',show_gl='1',show_codes='1',show_hints='1',rep_popup='1',graphic_links='1',sticky_doc_date='0',query_size='200' WHERE id='2'