Bug 21605

Summary: Cannot create EDI account
Product: Koha Reporter: Nick Clemens <nick>
Component: AcquisitionsAssignee: Colin Campbell <colin.campbell>
Status: CLOSED FIXED QA Contact: Martin Renvoize <martin.renvoize>
Severity: major    
Priority: P5 - low CC: colin.campbell, fridolin.somers, jonathan.druart, katrin.fischer, lucas, m.de.rooy, martin.renvoize
Version: Main   
Hardware: All   
OS: All   
See Also: https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=21610
Change sponsored?: --- Patch complexity: Trivial patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on:    
Bug Blocks: 17258    
Attachments: patch to validate the input to the boolean functionality fields
Bug 21605: Ensure EDI acct fields set to boolean val
Bug 21605: (follow-up) Simple ternary boolean to number conversion
Bug 21605: Ensure EDI acct fields set to boolean val
Bug 21605: (follow-up) Simple ternary boolean to number conversion

Description Nick Clemens 2018-10-18 13:52:09 UTC
DBIx::Class::Storage::DBI::_dbh_execute(): Incorrect integer value: '' for column 'auto_orders' at row 1 at /home/vagrant/kohaclone/admin/edi_accounts.pl line 101
 at /usr/share/perl5/DBIx/Class/Exception.pm line 77
Comment 1 Jonathan Druart 2018-10-18 21:28:08 UTC
I do not plan to fix this one, Koha::Object should be used to take advantage of the generic/global solution (see bug 21610)
Comment 2 Colin Campbell 2018-10-19 11:45:22 UTC
Cant reproduce on mariadb 10-2-18 is table set up with correct defaults?
Comment 3 Jonathan Druart 2018-10-19 12:08:46 UTC
(In reply to Colin Campbell from comment #2)
> Cant reproduce on mariadb 10-2-18 is table set up with correct defaults?

You need to turn on strict SQL modes, see bug 20521 (patch "Use a specific config for strict sql modes") for more details.
Comment 4 Colin Campbell 2018-10-25 15:42:40 UTC
Yes that was with that strict config switched on
Comment 5 Jonathan Druart 2018-10-25 17:23:49 UTC
I confirm the issue.

Fresh DB,  Home › Administration › EDI accounts › Add new account 
fill some fields, submit.
Boom: 
DBD::mysql::st execute failed: Incorrect integer value: '' for column 'auto_orders' at row 1 [for Statement "INSERT INTO `vendor_edi_accounts` ( `auto_orders`, `description`, `download_directory`, `host`, `id_co
de_qualifier`, `invoices_enabled`, `orders_enabled`, `password`, `plugin`, `quotes_enabled`, `responses_enabled`, `san`, `transport`, `upload_directory`, `username`, `vendor_id`) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?,
 ?, ?, ?, ?, ?, ?, ?, ? )" with ParamValues: 0='', 1="xxx", 2="", 3="", 4="14", 5='', 6='', 7="", 8="", 9='', 10='', 11="", 12="FTP", 13="", 14="", 15="1"] at /usr/share/perl5/DBIx/Class/Storage/DBI.pm line 1832.

I guess it comes from there:
<input name="auto_orders" id="auto_orders" value="" type="checkbox">

As I said previously this will certainly be fixed by bug 21610.
Comment 6 Colin Campbell 2018-10-26 09:51:33 UTC
Created attachment 81262 [details] [review]
patch to validate the input to the boolean functionality fields

Testing: setting and unsetting of boolean fields should work. Write failure as described in bug should not occur
Comment 7 Marcel de Rooy 2018-11-26 13:40:25 UTC
+            $fields->{$capability} = defined $input->param($capability);
+            if ($fields->{$capability} != 1) {
+                $fields->{$capability} = 0;

Somehow I do not understand.
Say I pass a 0 in the field.
Than $fields->{$capability} = 1 since 0 is defined.
Say I pass 'a'. Again = 1.
Only undef is not defined and will put empty string into $fields converted to 0.
Comment 8 Colin Campbell 2018-11-26 16:32:48 UTC
If the capability is not set the checkbox returns undefined - defined should convert that to the boolean value
Comment 9 Marcel de Rooy 2018-12-13 14:13:55 UTC
Created attachment 83167 [details] [review]
Bug 21605: Ensure EDI acct fields set to boolean val

Validate the input to the fields indicating account
functionality. The only valid values are 1 and 0
non-integer (or integers other than 0/1) are invalid

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 10 Marcel de Rooy 2018-12-13 14:14:00 UTC
Created attachment 83168 [details] [review]
Bug 21605: (follow-up) Simple ternary boolean to number conversion

Imo it is much easier to do just "$a ? 1 : 0" than testing defined($a) etc.
in the additional loop.
Less code, more readable?

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Comment 11 Katrin Fischer 2019-01-07 18:06:42 UTC
Bad bug - we should make it go away. Martin offered and I am ok with it :)
Comment 12 Martin Renvoize 2019-01-07 18:11:41 UTC
Created attachment 83700 [details] [review]
Bug 21605: Ensure EDI acct fields set to boolean val

Validate the input to the fields indicating account
functionality. The only valid values are 1 and 0
non-integer (or integers other than 0/1) are invalid

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 13 Martin Renvoize 2019-01-07 18:11:44 UTC
Created attachment 83701 [details] [review]
Bug 21605: (follow-up) Simple ternary boolean to number conversion

Imo it is much easier to do just "$a ? 1 : 0" than testing defined($a) etc.
in the additional loop.
Less code, more readable?

Signed-off-by: Marcel de Rooy <m.de.rooy@rijksmuseum.nl>
Signed-off-by: Martin Renvoize <martin.renvoize@ptfs-europe.com>
Comment 14 Martin Renvoize 2019-01-07 18:13:20 UTC
Passing QA. This is a trivial patch and causes no regressions. I agree with Marcels alternative/follow being easier to read and process.
Comment 15 Nick Clemens 2019-01-08 15:55:25 UTC
Awesome work all!

Pushed to master for 19.05
Comment 16 Martin Renvoize 2019-01-10 13:42:21 UTC
Pushed to 18.11.x for 18.11.02
Comment 17 Lucas Gass 2019-01-10 21:24:02 UTC
backported to 18.05 for 18.05.08
Comment 18 Fridolin Somers 2019-01-16 08:19:37 UTC
Pushed to 17.11.x for 17.11.14