Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Application Development » ERROR: 22P02: invalid input syntax for integer ( LINE 1: SELECT count(*) FROM idps WHERE uid='' ^ LOCATION: pg_atoi, numutils.c:52)
ERROR: 22P02: invalid input syntax for integer [message #4943] Thu, 27 August 2015 15:35 Go to next message
rafs is currently offline  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 messageGo to next message
rafs is currently offline  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 #4946 is a reply to message #4944] Fri, 28 August 2015 05:35 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
Registered: April 2006
Location: Surrey, UK
Senior Member
This is because RADICORE is not recognising the column uid as being an AUTO_INCREMENT column. If you look at Creating a PostgreSQL driver for Radicore you will see that it expects to see the keyword "serial" for each column that is to be filled from a sequence.

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 messageGo to next message
rafs is currently offline  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 messageGo to next message
rafs is currently offline  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 #4950 is a reply to message #4949] Sat, 29 August 2015 05:08 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
Registered: April 2006
Location: Surrey, UK
Senior Member
The AUTO_INCREMENT/SERIAL option should only be used for primary keys. Any other usage is not supported.

Re: ERROR: 22P02: invalid input syntax for integer [message #4952 is a reply to message #4950] Sat, 29 August 2015 14:18 Go to previous messageGo to next message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
Actually it does work!
I kept the field as is, namely 'serial' type, (a.k.a. 'integer' with DEFAULT nextval('idps_uid_seq')), but I removed the NOT NULL constraint AND the UNIQUE constraint.
Bingo.
(A day and a half spent with the debugger, but live and learn.)
Thanks Tony. Super happy to have figured this out.
Re: ERROR: 22P02: invalid input syntax for integer [message #4955 is a reply to message #4952] Sun, 30 August 2015 05:28 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
Registered: April 2006
Location: Surrey, UK
Senior Member
If you are not using a SERIAL/AUTO_INCREMENT field as the primary key, then what is the point?

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 messageGo to next message
rafs is currently offline  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 #4970 is a reply to message #4960] Tue, 01 September 2015 05:45 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
Registered: April 2006
Location: Surrey, UK
Senior Member
Experienced people don't make mistakes. The only way to gain experience is by making mistakes. Been there, done that.

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
AJM is currently offline  AJM
Messages: 2275
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.

Previous Topic: How to provide choices for two compound foreign keys in a form?
Next Topic: Running batch jobs
Goto Forum:
  


Current Time: Sun Nov 28 16:02:51 EST 2021

Total time taken to generate the page: 0.01024 seconds