0

I trying to implement the example from this manual Generating Unique Values in DB2 Universal Database but encountered the following problem.

I've two tables, 

CREATE TABLE PC_LIST (
        PC_ID CHAR(13) FOR BIT DATA NOT NULL, 
        PC_NAME VARCHAR(100) NOT NULL 

        PRIMARY KEY (ID)
);

And second table 

CREATE TABLE PC_DATA (
        ID CHAR(13) FOR BIT DATA NOT NULL, 
        PC_ID CHAR(13) FOR BIT DATA NOT NULL, 
        PC_NAME VARCHAR(100) NOT NULL 

        PRIMARY KEY (ID),
        FOREIGN KEY (PC_ID) REFERENCES PC_LIST(PC_ID)
);

I add records to first table PC_LIST without any issues, and use inside INSERT query GENERATE_UNIQUE() function, everything works fine. When I try to add a record to the second table and copy a value from PC_LIST.PC_ID as is I get an error Value "20150408131401000109000000 " is too long.. When I added a cast like following: CAST('20150408131401000109000000' AS CHAR(13) FOR BIT DATA) I get an error that The INSERT or UPDATE value of the FOREIGN KEY is not equal to any value of the parent key of the parent table.

What am I doing wrong? Thank you.

UPDATE: This code I use to insert data in PC_DATA

INSERT INTO PC_DATA 
(
    ID,
    PC_ID, 
    PC_NAME

) VALUES (GENERATE_UNIQUE(), CAST('20150408131401000109000000' AS CHAR(13) FOR BIT DATA) ,'anatoly-pc');

Of course that PC_ID I've taken from PC_LIST table where it exists.

Anatoly
  • 5,056
  • 9
  • 62
  • 136
  • Show the **actual** code that does the insert into `PC_DATA`. – mustaccio Apr 08 '15 at 13:56
  • Where do you get this `'20150408131401000109000000'`? Binary (`FOR BIT DATA`) string literals usually look like `x'20150408131401000109000000'` -- note the "x". – mustaccio Apr 09 '15 at 11:35
  • I browse data by using SQL explorer inside RAD and it shows this column's value without `x`. – Anatoly Apr 09 '15 at 16:16
  • @mustaccio, I've added `x` at the beginning of a string and it works like a charm. Now my question how to provide it's value when I use `PreparedStatement`? Should I write something like this `x?` and then `setString(1,'20150408131401000109000000')` or it won't work? – Anatoly Apr 12 '15 at 12:55
  • 1
    No, you treat it as a binary (`byte[]`) type in Java and bind to the parameter marker as usual. – mustaccio Apr 12 '15 at 14:35

0 Answers0