Search code examples
javasqlderby

Issues inserting DATE value into Derby database

I am trying to setup a Derby database and insert some sample info into it for testing.

The code to get the tables I'd like is...

CREATE TABLE users(
    userid varchar(128) primary key, 
    passwd_digest varchar(128)
    );
CREATE TABLE customers(
    cust_id int not null primary key(START WITH 1, INCREMENT BY 1), 
    cust_name varchar(128)
    );
CREATE TABLE orders(
    order_id int not null primary key, 
    cust_id int not null(START WITH 1, INCREMENT BY 1),
    foreign key(cust_id) references customers(cust_id), 
    order_date date, 
    order_desc varchar(128)
    );

Now when I try to insert the sample data into it using the following code:

insert into customers(cust_id, cust_name) values (3, 'Ringo');
insert into orders(order_id, cust_id, order_date, order_desc) values (4, 3, 12.12.1957, 'A drumset');

I get the following error from IJ:

ERROR 42X01: Syntax error: Encountered ".1957" at line 1, column 82.
Issue the 'help' command for general information on IJ command syntax.
Any unrecognized commands are treated as potential SQL commands and executed directly.
Consult your DBMS server reference documentation for details of the SQL syntax supported by your server.

I've tried putting the date values in the following ways (disregard the other data, it's only the dates that appear to not work) :

insert into orders(order_id, cust_id, order_date, order_desc) values (4, 3, 1957-12-12 00:00:00:000, 'A drumset');
insert into orders(order_id, cust_id, order_date, order_desc) values (4, 3, 1957-12-12, 'A drumset');
insert into orders(order_id, cust_id, order_date, order_desc) values (4, 3, "12-12-1957", 'A drumset');
insert into orders(order_id, cust_id, order_date, order_desc) values (4, 3, DATE("12-12-1957"), 'A drumset');
insert into orders(order_id, cust_id, order_date, order_desc) values (4, 3, 12-12-1957, 'A drumset');

but they all produce similar errors including:

ERROR 42821: Columns of type 'DATE' cannot hold values of type 'INTEGER'.

I'm not sure why I'm getting these errors since I'm putting in values matching the format of Derby's docs (at least it looks that way to me, since it's not working I'm sure I've got it wrong somewhere).

Solution

  • Use the DATE or TIMESTAMP functions, or the JDBC escape syntax, whichever you prefer, to convert your literal values into date or timestamp data types.

    Or write your insert logic in Java using PreparedStatement, and use the setDate() and setTimestamp() functions.

    Various docs for the above:

    1. DATE function: https://db.apache.org/derby/docs/10.15/ref/rrefdatefunc.html
    2. TIMESTAMP function: https://db.apache.org/derby/docs/10.15/ref/rreftimestampfunc.html
    3. JDBC Escape syntax for dates and times: https://db.apache.org/derby/docs/10.15/ref/rrefjdbc1020262.html
    4. PreparedStatement: https://stackoverflow.com/a/18615191/193453