ERROR: 22P02: invalid input syntax for integer [message #4943] |
Thu, 27 August 2015 15:35 ![Go to next message Go to next message](/theme/default/images/down.png) |
rafs
Messages: 69 Registered: May 2015
|
Member |
|
|
My idps table:
CREATE TABLE idps (
uid integer NOT NULL,
idp_type character(3) NOT NULL,
idp_id integer NOT NULL,
idp_calc character varying(12) NOT NULL,
idp_status integer DEFAULT 0 NOT NULL,
created_date timestamp without time zone DEFAULT '2000-01-01 00:00:00'::timestamp without time zone NOT NULL,
created_user character varying(16) DEFAULT 'SYSTEM'::character varying NOT NULL,
revised_date timestamp without time zone,
revised_user character varying(16)
);
CREATE SEQUENCE idps_uid_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1;
ALTER TABLE ONLY idps ALTER COLUMN uid SET DEFAULT nextval('idps_uid_seq'::regclass);
ALTER TABLE ONLY idps ADD CONSTRAINT idps_pkey PRIMARY KEY (idp_type, idp_id);
ALTER TABLE ONLY idps ADD CONSTRAINT idps_uid_key UNIQUE (uid);
ALTER TABLE ONLY idps ADD CONSTRAINT idps_idp_type_fkey FOREIGN KEY (idp_type) REFERENCES idp_types(idp_type);
The Error:
Fatal Error: PostgreSQL: ERROR: 22P02: invalid input syntax for integer: "" LINE 1: SELECT count(*) FROM idps WHERE uid='' ^ LOCATION: pg_atoi, numutils.c:52
SQL query: SELECT count(*) FROM idps WHERE uid=''
Error in line 578 of file 'C:\xampp\radicore\includes\dml.pgsql.class.inc'.
I tried: (1) removing the unique index from uid in the db, (2) removing "nextval('idps_uid_seq'::regclass)" as the default value in the RADICORE>>DB>>TABLE:idps>>COL:uid>>Default value field.
Neither worked.
|
|
|
Re: ERROR: 22P02: invalid input syntax for integer [message #4944 is a reply to message #4943] |
Thu, 27 August 2015 21:41 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
rafs
Messages: 69 Registered: May 2015
|
Member |
|
|
The entire error log shows that "uid: string = null" is being sent to insertRecord.
My guess is that since uid is a unique key, the framework/DAO is trying to validate the uniqueness of 'uid' before the insertRecord by doing a SELECT query with the given value in the form, but this value is null and the framework is expecting a value here. But this is an auto_increment value (but not a PK)...
The rest of the error log entry is below...
2015-07-22 00:59:21
Fatal Error: PostgreSQL: ERROR: 22P02: invalid input syntax for integer: "" LINE 1: SELECT count(*) FROM idps WHERE uid='' ^ LOCATION: pg_atoi, numutils.c:52
SQL query: SELECT count(*) FROM idps WHERE uid=''
Error in line 578 of file 'C:\xampp\radicore\includes\dml.pgsql.class.inc'.
Host Info: idp-etl2.eng.sonicwall.com, Server Version: 9.3.6
Client Encoding: UTF8, Server Encoding: UTF8
Database: NSDB2, Schema: "NSDB2", PUBLIC
PHP_SELF: /radicore/nsdb2/idps(add1).php
CURRENT DIRECTORY: C:\xampp\htdocs\radicore\nsdb2
SERVER_ADDR: 10.204.23.21
User Id: MGR
Role Id: GLOBAL
REQUEST_URI: /radicore/nsdb2/idps(add1).php
Page Stack:
[0] main_menu
[1] nsdb2
[2] nsdb2_idps(list1)
[3] nsdb2_idps(add1)
Backtrace:
0: array =
function: string = errorHandler
args: array =
0: integer = 256
1: string = PostgreSQL: ERROR: 22P02: invalid input syntax for integer: ""
LINE 1: SELECT count(*) FROM idps WHERE uid=''
^
LOCATION: pg_atoi, numutils.c:52
2: string = C:\xampp\radicore\includes\dml.pgsql.class.inc
3: integer = 578
4: object = pgsql
1: array =
file: string = C:\xampp\radicore\includes\dml.pgsql.class.inc
line: integer = 578
function: string = trigger_error
args: array =
0: string = PostgreSQL: ERROR: 22P02: invalid input syntax for integer: ""
LINE 1: SELECT count(*) FROM idps WHERE uid=''
^
LOCATION: pg_atoi, numutils.c:52
1: integer = 256
2: array =
file: string = C:\xampp\radicore\includes\dml.pgsql.class.inc
line: integer = 1040
function: string = getCount
class: string = pgsql
type: string = ->
args: array =
0: string = nsdb2
1: string = idps
2: string = SELECT count(*) FROM idps WHERE uid=''
3: array =
file: string = C:\xampp\radicore\includes\std.table.class.inc
line: integer = 7653
function: string = insertRecord
class: string = pgsql
type: string = ->
args: array =
0: string = nsdb2
1: string = idps
2: array =
uid: string = null
idp_type: string = idp
idp_id: string = 1
idp_calc: string = idp:1
idp_status: string = 0
created_date: string = 2000-01-01 00:00:00
created_user: string = SYSTEM
revised_date: string = null
revised_user: string = null
4: array =
file: string = C:\xampp\radicore\includes\std.table.class.inc
line: integer = 4180
function: string = _dml_insertRecord
class: string = Default_Table
type: string = ->
args: array =
0: array =
uid: string = null
idp_type: string = idp
idp_id: string = 1
idp_calc: string = idp:1
idp_status: string = 0
created_date: string = 2000-01-01 00:00:00
created_user: string = SYSTEM
revised_date: string = null
revised_user: string = null
5: array =
file: string = C:\xampp\radicore\includes\std.add1.inc
line: integer = 104
function: string = insertRecord
class: string = Default_Table
type: string = ->
args: array =
0: array =
uid: string = null
idp_type: string = idp
idp_id: string = 1
idp_calc: string = idp:1
idp_status: string = 0
created_date: string = 2000-01-01 00:00:00
created_user: string = SYSTEM
revised_date: string =
revised_user: string =
6: array =
file: string = C:\xampp\htdocs\radicore\nsdb2\idps(add1).php
line: integer = 9
args: array =
0: string = C:\xampp\radicore\includes\std.add1.inc
function: string = require
[Updated on: Thu, 27 August 2015 21:43] Report message to a moderator
|
|
|
|
Re: ERROR: 22P02: invalid input syntax for integer [message #4948 is a reply to message #4946] |
Fri, 28 August 2015 14:29 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
rafs
Messages: 69 Registered: May 2015
|
Member |
|
|
I think my field 'uid' is already a serial data type. This link shows that the serial is just an integer with a default of nextval('idps_uid_seq') function set to the named SEQUENCE.
http://www.postgresql.org/docs/9.2/static/datatype-numeric.h tml, Section 8.1.4).
Also, I tested this insert from command line using psql and it does insert the (DEFAULT) next value into my 'uid' field for this record:
INSERT INTO idps (idp_type, idp_id, idp_calc, idp_status) VALUES ('idp', 1, 'idp1', 0)
In my case, the field, 'uid', is not the PK, it is a candidate key with UNIQUE constraint. (The PK is a compound key from idp_type, idp_id.)
Is the framework using $pkey variable to mean only PUBLIC KEY only, or does it include UNIQUE KEYs as well?
Quoting you from the link you mention (above), you say, $pkey, but my field is not a PK...
Quote:
With PostgreSQL the same can be achieved with:
$query = "SELECT currval('" .$tablename .'_' .$pkey ."_seq')";
$result = pg_query($link, $query) or trigger_error($this, E_USER_ERROR);
$id = pg_fetch_result($result, 0, 0);
This is because the SERIAL keyword makes use of a counter with the default name of <tablename>_<fieldname>_seq. This can be accessed using the currval() and nextval() functions.
Could this be the issue?
[Updated on: Fri, 28 August 2015 15:41] Report message to a moderator
|
|
|
Re: ERROR: 22P02: invalid input syntax for integer [message #4949 is a reply to message #4948] |
Fri, 28 August 2015 16:53 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
rafs
Messages: 69 Registered: May 2015
|
Member |
|
|
Unless I am mistaken, I think there is a logical error in dml.pgsql.class.inc.
In function: insertRecord
[Line: 973]
// find out if any field in the primary key has 'serial' (auto_increment) set
[Line:1004]
// build 'where' string using values for primary key
[Line:1034]
// validate any optional unique/candidate keys
The framework is not expecting an auto_increment candidate key, I think. Trying to modify the function...
[Updated on: Fri, 28 August 2015 19:18] Report message to a moderator
|
|
|
|
|
|
Re: ERROR: 22P02: invalid input syntax for integer [message #4960 is a reply to message #4955] |
Mon, 31 August 2015 16:30 ![Go to previous message Go to previous message](/theme/default/images/up.png) ![Go to next message Go to next message](/theme/default/images/down.png) |
rafs
Messages: 69 Registered: May 2015
|
Member |
|
|
In this version of the project I was experimenting with trying to avoid having technical PKs everywhere, as per your design suggestions. I like this approach better, but in a table I also needed a unique identifier, 'uid' that means something external to the DB relationships. So, basically most tables had natural string primary keys, and in several of them they were compound primary keys. The one with the three compound keys also required 'uid' for external use. I preferred the three compound keys because they meant something and could be searched in the one table without having to figure out how to search the current table by creating JOINing SQL queries or however that is supposed to happen.
Basically, I am still learning and experimenting. The old DB used technical keys that got hard-coded into a final product. Those design choices have created restrictions future expansion. I am trying to add a new layer of abstraction to the old design, so now these previous integer keys, are themselves entities within this new model.
If I make it to the end of this project I will probably have made every mistake possible.
[Updated on: Mon, 31 August 2015 18:12] Report message to a moderator
|
|
|
|
Re: ERROR: 22P02: invalid input syntax for integer [message #4981 is a reply to message #4960] |
Wed, 02 September 2015 09:02 ![Go to previous message Go to previous message](/theme/default/images/up.png) |
AJM
Messages: 2361 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
If you need an auto-incrementing unique key but you already have a natural unique key (which may or may not be a compound key) then my advice is to set the auto-increment field as the primary key and leave the other as a candidate key. The reason for this approach is that it then becomes possible to update the values in the candidate key (provided that the result is still unique) while the primary key remains fixed and immutable.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|