Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.
Home » RADICORE development » Bug Reports » Error update column to popup with Postgresql
Error update column to popup with Postgresql [message #4258] Sat, 14 June 2014 09:05 Go to next message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
Column->Update and set as popup field with task id results in an update SQL statement that produces fatal error:

Fatal Error: PostgreSQL: ERROR: 42703: column "list1" does not exist LINE 1: ...ntrol='popup', optionlist=NULL,task_id=h_supplier(list1), fo... ^ LOCATION: errorMissingColumn, src\backend\parser\parse_relation.c:2655 (# 42703)

SQL query: UPDATE dict_column SET control='popup', optionlist=NULL,task_id=h_supplier(list1), foreign_field='supplier_id', revised_date='2014-06-14 07:52:10', revised_user='EKL' WHERE database_id='hotel' AND table_id='expense' AND column_id='supplier_id'

It appears that ...task_id=h_supplier(list1)... is missing the single quotes. For full error details, please see attached pdf file.

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4259 is a reply to message #4258] Sun, 15 June 2014 05:37 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
Yes, it is missing the quotes. Fix attached.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4261 is a reply to message #4259] Mon, 16 June 2014 09:50 Go to previous messageGo to next message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
Installing this patch will show another problem.

When I do table->columns->import columns
for a table with auto increment pkey, system produces error:
[expense_id] col_default: col_default cannot be greater than 40 characters.
and refuses to import any columns.

I suspect auto-increment field in PostgreSQL is imported into Radicore with the default value as 
nextval('....................._id_seq'::regclass)
, which sometimes will exceed $fieldspec['size'] characters and therefor cannot pass the validateField() function in std.validate.class.

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4262 is a reply to message #4261] Mon, 16 June 2014 10:07 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
Whoops! Embarassed

I forgot to send you the .sql file with the following database update:
SET search_path TO "dict";

ALTER TABLE dict.dict_column ALTER COLUMN col_default TYPE character varying(255);


Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4263 is a reply to message #4262] Mon, 16 June 2014 14:05 Go to previous messageGo to next message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
That modification allowed us to move further one step but hit next problem instead upon 'import columns':
Fatal Error: PostgreSQL: ERROR: 23502: null value in column "is_password" violates not-null constraint DETAIL: Failing row contains (hotel, expense, expense_id, 1, Expense Id, null, integer, null, null, 10, 10, N, Y, PRI, nextval('expense_expense_id_seq'::regclass), 1, N, null, null, null, -2147483648, 2147483647, 0, 2147483647, null, null, null, null, null, null, null, null, null, null, null, N, null, null, null, null, null, null, null, null, null, null, null, null, 2014-06-14 04:27:40, EKL, 2014-06-16 17:36:29, EKL, integer). LOCATION: ExecConstraints, src\backend\executor\execMain.c:1611 (# 23502)

SQL query: UPDATE dict_column SET col_default='nextval(''expense_expense_id_seq''::regclass)', col_auto_increment='1', is_password=NULL,auto_insert=NULL,auto_update=NULL,infinityisnull=NULL,revised_date='2014-06-16 17:36:29', revised_user='EKL' WHERE database_id='hotel' AND table_id='expense' AND column_id='expense_id'

Error in line 1446 of file 'D:\Pet\PHP Full Env\UwAmp\www\radpg184\includes\dml.pgsql.class.inc'.
and this error when trying to do import columns for some other tables:
Fatal Error: PostgreSQL: ERROR: 23502: null value in column "col_auto_increment" violates not-null constraint DETAIL: Failing row contains (hotel, department, department_id, 1, Department Id, null, varchar, null, null, 16, 16, N, Y, PRI, null, null, null, null, null, null, null, null, null, null, null, null, null, UPPER, N, null, null, null, null, null, null, N, null, null, null, null, null, null, null, null, null, null, null, null, 2014-06-09 01:53:40, MGR, 2014-06-16 17:59:12, EKL, varchar). LOCATION: ExecConstraints, src\backend\executor\execMain.c:1611 (# 23502)

SQL query: UPDATE dict_column SET col_auto_increment=NULL,col_unsigned=NULL,auto_insert=NULL,auto_update=NULL,infinityisnull=NULL,revised_date='2014-06-16 17:59:12', revised_user='EKL' WHERE database_id='hotel' AND table_id='department' AND column_id='department_id'

Error in line 1446 of file 'D:\Pet\PHP Full Env\UwAmp\www\radpg184\includes\dml.pgsql.class.inc'.
Perhaps need to make more modifications to the table column definitions for dict_column?

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4264 is a reply to message #4263] Mon, 16 June 2014 14:19 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
I do not get this error on my system. Both of those columns should be defined with "character(1) NOT NULL DEFAULT 'N'::bpchar" which means that a null value should be replaced with "N".

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4265 is a reply to message #4264] Mon, 16 June 2014 21:13 Go to previous messageGo to next message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
That is odd. I noticed it will work only when 'import column' is used for a table for the first time. If you try re-'import column' again for any table, it will generate this error.

Please take a look at this http:// www.postgresql.org/message-id/20140204081310.GC10459@defunct .ch where it is stated that in PostgreSQL Quote:
When you
explicitly set the columns to NULL the default values don't apply. You
have to leave them out or to explicitly request the default values
andQuote:
Specifying NULL explicitly means that is what you want and if the
column is constrained to be non-NULL the system will be unable to fulfill
your request and throw an error.

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4266 is a reply to message #4265] Tue, 17 June 2014 03:45 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2371
Registered: April 2006
Location: Surrey, UK
Senior Member
It would appear that when I created the PostgreSQL schema some of the columns were set to NOT NULL by mistake. The quickest way to correct this would be to import the dict_column table into the dictionary, then export it to rebuild the dict_column.dict.inc file.

I shall fix the schema in the next release.

Tony Marston
http://www.tonymarston.net
http://www.radicore.org

Report message to a moderator

Re: Error update column to popup with Postgresql [message #4276 is a reply to message #4266] Fri, 20 June 2014 12:06 Go to previous message
kong is currently offline  kong
Messages: 90
Registered: December 2011
Member
In the mean time I will work around this problem by deleting the NOT NULL restrictions in the columns definitions of the table dict_column in phpPgAdmin.

Report message to a moderator

Previous Topic: SQL error upon log in (PostgreSQL)
Next Topic: Update Multiple Scroll Sequence Behavior
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ] [ PDF ]

Current Time: Mon Dec 23 09:48:30 EST 2024

Total time taken to generate the page: 0.01684 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.1.3.
Copyright ©2001-2023 FUDforum Bulletin Board Software