Radicore Forum: Bug Reports » Error update column to popup with Postgresql

Home » RADICORE development » Bug Reports » Error update column to popup with Postgresql

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error update column to popup with Postgresql [message #4258]

Sat, 14 June 2014 09:05

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.

Attachment: radicore error.pdf
(Size: 218.42KB, Downloaded 1201 times)

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

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

Yes, it is missing the quotes. Fix attached.

Attachment: dml.pgsql.class.zip
(Size: 73.61KB, Downloaded 811 times)

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

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

AJM
Messages: 2367
Registered: April 2006
Location: Surrey, UK

Senior Member

Whoops!

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

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

AJM
Messages: 2367
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

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

AJM
Messages: 2367
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

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

] [

]

Current Time: Fri Nov 15 14:50:24 EST 2024

Total time taken to generate the page: 0.01216 seconds