inserting value in postgres custom-built type field using java api

Question

I have a type as 'designation' in my postgreSQL DB instance. I have a table named 'Prof' that has a column 'designation' of type 'designation'. I want to insert values into this column using java.

code snippet:

insProf.setInt(1, id);
insProf.setInt(2, univ_id);
insProf.setString(3, desg);
int rs_insProf = insProf.executeUpdate();

output error:

Enter designation: org.postgresql.util.PSQLException: ERROR: invalid input value for enum designation: ""

Please help with with syntax, I am not able to figure out how best I can handle this.

I would try to use insProf.setObject(3, desg, Types.OTHER) — UninformedUser
– UninformedUser, Commented Mar 29, 2017 at 7:02
Are you sure that your designation type accepts the empty string ''? (It only accepts it, if you enumerated that too within the definition). You may want to use NULL instead of the empty string. — pozs
– pozs, Commented Mar 29, 2017 at 10:21

wysohn · Accepted Answer · 2017-03-29 07:01:08Z

1

you can use setObject() and getObject() method.

https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setObject(int,%20java.lang.Object)

take a look at the setObject() and getObject().

according to the documentation, "If the object is of a class implementing the interface SQLData, the JDBC driver should call the method SQLData.writeSQL to write it to the SQL data stream."

so you can create your own type by creating a custom class implementing SQLData interface.

plus, don't forget to register your custom type into the driver's type map.

there is a tutorial too: https://docs.oracle.com/javase/tutorial/jdbc/basics/sqlcustommapping.html

answered Mar 29, 2017 at 7:01

wysohn

481 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Youcef LAIDANI · Accepted Answer · 2017-03-29 07:02:23Z

0

To insert an enum, you have to use this syntax:

INSERT INTO Prof (id, unit_id, designation) VALUES(?, ?, {?, ?, ?});
//-------------------------------------------------------^-------^

Or

You can use an array in your case of type designation like this :

INSERT INTO Prof (id, unit_id, designation) VALUES(?, ?, ARRAY[?]::designation[]);
//-------------------------------------------------------------^

To set multiple you can use a loop for example to fill your enum, if you have more then one enum.

answered Mar 29, 2017 at 7:02

Youcef LAIDANI

60.3k21 gold badges111 silver badges178 bronze badges

Collectives™ on Stack Overflow

inserting value in postgres custom-built type field using java api

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related