Windows 10, DB2 11.1 express-C
I have "SQL connected" to this table on DB2, and cant create any row on it from db-explorer.
Tried several combinations of , filling up all columns, or partially filled up.
When trying to create a row from dbExplorer I get the following error.
Code:
c:\program files (x86)\dataflex 19.0\bin\dbexplor.exe
Erro genérico 07001 (-99999)--[IBM][CLI Driver] CLI0100E Wrong number of parameters. SQLSTATE=07001 Table = WEBAPP_USER (8), Source = DB2_DRV.Save
Error: 12289
MSG_ERROR_REPORT (433) - oErrorDialog (91) - at address 67362
MSG_REQUEST_SAVE (375) - oWebApp_User_DD (2057) - at address 16764
MSG_REQUEST_SAVE (375) - oRecordEditContainer (2067) - at address 20518
MSG_REQUEST_SAVE (375) - oIRN_USERdbForm (2123) - at address 21923
MSG_REQUEST_SAVE (375) - oIRN_USERdbForm (2123) - at address 22146
MSG_REQUEST_SAVE (375) - oIRN_USERdbForm (2123) - at address 90124
MSG_ONEXECUTE (9366) - oSaveCurrentRecordButton (289) - at address 72701
MSG_EXECUTE (9365) - oSaveCurrentRecordButton (289) - at address 57048
MSG_ONCOMEXECUTE (8509) - oRebar (146) - at address 56038
[start] - at address 101763
follow the table definition
Code:
CREATE TABLE APPCTRL.WEBAPP_USER (
IRN_USER INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE NO CACHE ORDER ),
SUSERLOGIN VARCHAR(150 OCTETS) NOT NULL DEFAULT '',
SFULLNAME VARCHAR(100 OCTETS) NOT NULL DEFAULT '',
SNICKNAME VARCHAR(50 OCTETS) NOT NULL DEFAULT '',
SPASSWORD VARCHAR(128 OCTETS) NOT NULL DEFAULT '',
DTLAST_LOGIN TIMESTAMP NOT NULL DEFAULT '0001-01-01-00.00.00.000000',
IUNSUCCESSFULL_LOGINS SMALLINT NOT NULL DEFAULT 0,
DTLAST_UNSUCCESSFULL_LOGIN TIMESTAMP NOT NULL DEFAULT '0001-01-01-00.00.00.000000',
IUSER_STATE SMALLINT NOT NULL DEFAULT 0,
SSECURITYQUESTION1 VARCHAR(200 OCTETS) NOT NULL DEFAULT '',
SSECURITYANSWER1 VARCHAR(50 OCTETS) NOT NULL DEFAULT '',
SSECURITYQUESTION2 VARCHAR(200 OCTETS) NOT NULL DEFAULT '',
SSECURITYANSWER2 VARCHAR(50 OCTETS) NOT NULL DEFAULT '',
SID_INTERFACELANG CHAR(5 OCTETS) NOT NULL DEFAULT '',
SID_LANG CHAR(5 OCTETS) NOT NULL DEFAULT '',
SDATE_FORMAT CHAR(10 OCTETS) NOT NULL DEFAULT '',
SDATE_SEPARATOR CHAR(1 OCTETS) NOT NULL DEFAULT '',
STIMESTAMP_FORMAT VARCHAR(23 OCTETS) NOT NULL DEFAULT '',
STIME_SEPARATOR CHAR(1 OCTETS) NOT NULL DEFAULT '',
SMONEY_SYMBOL CHAR(6 OCTETS) NOT NULL DEFAULT '',
SDECIMAL_SEPARATOR CHAR(1 OCTETS) NOT NULL DEFAULT '',
STHOUSANDS_SEPARATOR CHAR(1 OCTETS) NOT NULL DEFAULT ''
)
ORGANIZE BY ROW
DATA CAPTURE NONE
IN TSUD04K0003
COMPRESS NO;
CREATE UNIQUE INDEX APPCTRL.WEBAPP_USER_PK
ON APPCTRL.WEBAPP_USER
(IRN_USER ASC)
PCTFREE 5
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;
CREATE UNIQUE INDEX APPCTRL.WEBAPP_USER_X002
ON APPCTRL.WEBAPP_USER
(SUSERLOGIN ASC)
PCTFREE 5
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;
CREATE UNIQUE INDEX APPCTRL.WEBAPP_USER_X003
ON APPCTRL.WEBAPP_USER
(SID_INTERFACELANG ASC,
IRN_USER ASC)
PCTFREE 5
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;
CREATE UNIQUE INDEX APPCTRL.WEBAPP_USER_X004
ON APPCTRL.WEBAPP_USER
(SID_LANG ASC,
IRN_USER ASC)
PCTFREE 5
MINPCTUSED 0
ALLOW REVERSE SCANS
PAGE SPLIT SYMMETRIC
COMPRESS NO;
ALTER TABLE APPCTRL.WEBAPP_USER ADD CONSTRAINT WEBAPP_USER_PK PRIMARY KEY
(IRN_USER);
COMMENT ON COLUMN APPCTRL.WEBAPP_USER.DTLAST_LOGIN IS
'timestamp for tha last sucessfull login';
COMMENT ON COLUMN APPCTRL.WEBAPP_USER.DTLAST_UNSUCCESSFULL_LOGIN IS
'timestamp for the last unsuccessfull login attempt;
';
COMMENT ON COLUMN APPCTRL.WEBAPP_USER.IUNSUCCESSFULL_LOGINS IS
'number of unsuccefull login attempts
If the number breaches the max number of login attentps, user should be blocked. (IUSER_STATE = Blocked)
Passwrd must be reset after this. After passwd reset, this counter should be reset to 0.';
COMMENT ON COLUMN APPCTRL.WEBAPP_USER.IUSER_STATE IS
'0 - Pending Confirmation
1- Active ;
2 - Disabled ; (Manually)
3 - Blocked by several invalid login attempts;
4 - Temp Passwrod, requires new permanent passwd at next login ;
';
COMMENT ON COLUMN APPCTRL.WEBAPP_USER.SID_LANG IS
'Language Row ID';
COMMENT ON COLUMN APPCTRL.WEBAPP_USER.SPASSWORD IS
'Current valid password, encrypted';
COMMENT ON COLUMN APPCTRL.WEBAPP_USER.SUSERLOGIN IS
'user e-mail';
And here, the INT generated by the "SQL connect wizard":
Code:
DRIVER_NAME DB2_DRV
SERVER_NAME DFCONNID=RVMEDREP_ConnID
DATABASE_NAME WEBAPP_USER
SCHEMA_NAME APPCTRL
RECNUM_TABLE NO
PRIMARY_INDEX 1
TABLE_CHARACTER_FORMAT ANSI
USE_DUMMY_ZERO_DATE YES
FIELD_NUMBER 1
FIELD_INDEX 1
FIELD_NUMBER 2
FIELD_INDEX 2
FIELD_NUMBER 14
FIELD_INDEX 3
FIELD_RELATED_FILE 4
FIELD_RELATED_FIELD 1
FIELD_NUMBER 15
FIELD_INDEX 4
FIELD_RELATED_FILE 12
FIELD_RELATED_FIELD 1
INDEX_NUMBER 1
INDEX_NAME WEBAPP_USER_PK
INDEX_NUMBER 2
INDEX_NAME WEBAPP_USER_X002
INDEX_NUMBER 3
INDEX_NAME WEBAPP_USER_X003
INDEX_NUMBER 4
INDEX_NAME WEBAPP_USER_X004
I am able to create/save rows from other utilities (db2clp, IBM dataStudio, etc... )
The issue seems related only to create records, If I try to update an existing row, I get no error.
Onother important note. I do not use default DB2 COLLATE. I use a non-case-sensitivity collate: CLDR181_S1
This colation must be specified during db creation.
Code:
CREATE DATABASE RVMEDREP
AUTOMATIC STORAGE YES
ON '<drive>'
USING CODESET UTF-8 TERRITORY BR
COLLATE USING CLDR181_S1