Skip to main content ↑ ↓
Topic solved
This topic has been marked as solved and requires no further attention.
Topic: Classic 2.6.4 eval - Error when insert none required field (Read 5295 times) previous topic - next topic

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #30 –
I have the same problem with PHPRad Studio Classic Edition Version 2.7.3 but in my case the column is a foreign key to another table defined as INT(11) NULL. The column is not required and must be set to NULL when no value is supplied. When I attempt to set the default value to NULL, PHPRad tells me:

Quote
Value Not a Numeric Value. Table Field Accept Only Numeric Values

Setting the value to a numeric value that does not exist in the foreign table would result in a foreign key referential integrity violation.

The bottom line is: NULL needs to be the default value for fields that are nullable - regardless of the data type.

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #32 –
Quote
Error 500
Server Error

Exception Traces
This will only be displayed in DEVELOPMENT_MODE.
Error Message   SQLSTATE[HY000]: General error: 1366 Incorrect integer value: '' for column 'Teams_teamId' at row 1
File   D:\Websites\wp247shiftpress_phpRad\app\models\PDODb.php On Line 1861
Stack Trace   
1 D:\Websites\wp247shiftpress_phpRad\app\models\PDODb.php(1861): PDOStatement->execute()
2 D:\Websites\wp247shiftpress_phpRad\app\controllers\SchedulesController.php(218): PDODb->update('schedules', Array)
3 D:\Websites\wp247shiftpress_phpRad\system\Router.php(196): SchedulesController->edit('1', Array)
4 D:\Websites\wp247shiftpress_phpRad\system\Router.php(109): Router->run('schedules/edit/1/')
5 D:\Websites\wp247shiftpress_phpRad\index.php(106): Router->init()

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #33 –
@wescleveland‍ the issue is with you database table structure, you made the column Teams_teamId an int and you are trying to insert data that is not an integer into it, please correct your database table column to accept the right data or make sure the data being entered in your add/edit page is an integer (number).

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #34 –
@willvin This is NOT a database design issue. I've been programming for over 40 years and designing databases for the past 25 years. This is a COMMON scenario.

The column Teams_teamId in the Schedules table is a foreign key into the Teams table. The teamId column in the Teams table is a typical ID column which is defined as INT(11). Schedules.Teams_teamId is not required and thus is NULLable. I am not trying to set the value of Schedules.Teams_teamId to ''. Instead, PHPRad is attempting to set the value of the INT column to '' when no team was selected on the Add/Edit page. Because the column is NULLable, PHPRad should be setting the column to NULL instead of '' when no value is supplied.

As for how the data is entered on the Add/Edit page, the field is setup as a SELECT field where the value is being selected from the teamId column in the Teams table. When I select a team on the Schedules Add/Edit page, the Schedules.Teams_teamId value is populated correctly by PHPRad. But when no team is selected, PHPRad attempts to set the value of Schedules.Teams_teamId to '' even though PHPRad knows that Schedules.Teams_teamId is an INT and that it is NULLable.

Add to this that PHPRad will not allow me to set the default value for Schedules.Teams_teamId to NULL. I must enter a numeric value in the default value field. But there is no legitimate number that can be used because it is a foreign key and any value that is not in the Teams table would violate referential integrity rules.

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #35 –
@wescleveland‍ please use the Page Event to add a PHP code in Before Edit and Before Add that checks if that field does not contain a value and then pop it from the array, if it does, you let it insert it.

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #36 –
@willvin Thanks for the suggestion. I appreciate you trying to provide solutions, but this still doesn't solve the problem. It just keeps the column from being updated if nothing is selected. The problem with this is that when Schedules.Teams_teamId is not NULL (contains a Teams.teamId value) going into the Edit page and the value is removed during the edit so that no Team is selected when the update occurs, removing the field from the array will cause the field to NOT be updated. The end result is that the Schedules.Teams_teamId will continue to contain the old Schedules.Teams_teamId value instead of being set to NULL.

IMO, the right solution is for PHPRad to properly handle setting NULLable columns to NULL when no value is selected. I realize that this will require code changes and won't immediately solve this problem. But, knowing that the problem has been acknowledged and is being addressed makes my decision to purchase PHPRad much easier.

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #37 –
So, here's a workaround that works. Instead of removing the field from the $modeldata array in the BeforeAdd and BeforeUpdate events, set it to NULL:

Code: [Select]
if ( empty( $modeldata['Teams_teamId'] ) ) $modeldata['Teams_teamId'] = null;

PDO is smart enough to know the difference between an empty string ('') and a null string and generates the correct database insert/update when the value is null.

This is still a workaround and hopefully will not be accepted as a solution. This creates too much extra/unnecessary work for the developer. IMO, since PHPRad already knows that the field is NULLable and that it is not required, this should be the default action taken by PHPRad for any NULLable column.

Here's what I would like to see PHPRad do:
  • When retrieving information from the database catalog:
    • Set the field's default value to the database default for the field
    • If there is no default value in the catalog and the field is NULLable, set the field's default value to NULL
    • If the field is NULLable, set the field's default field-required to false
  • Provide a NULL default option (checkbox) on the Manage Database dialog that, when checked, would cause the default value to be set to NULL (not the literal 'NULL'). This cannot be a literal in the DefaultValue field because it would not allow for the literal 'NULL' to be used as a default value.
  • Modify the controller to set the value to NULL when the field has the NULL default option checked and the field is empty

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #38 –
Hello,

I doubt if the issue has been resolved as I get the error still. Until I specify a date, or do as suggested by inclusion in Before Edit the Null value couldn't be updated.

Any help?

BR
 

Re: Classic 2.6.4 eval - Error when insert none required field

Reply #39 –
Please if you know you will be dealing with empty date and time values, set the field in your database table to varchar, else you can use datetime, time, date, or timestamp for the field.