CODEWITHSUNDEEP
hibernatepostgresqljdbcprepared-statementcomposite-types
quonn
quonn

Reputation: 51

JDBC PreparedStatement: How to add a Composite Type

How do you add a PostgreSQL Composite Type by means of a PreparedStatement?
The composite type is:

CREATE TYPE fullcontact AS (address address,contacts contact);
ALTER TYPE fullcontact OWNER TO postgres;
COMMENT ON TYPE fullcontact IS 'Describe a type for a contact info with phone, email, and  address';

I am trying to add it via

FullContact fc = (FullContact)value;
PGobject pgObj = castToPGObject(fc);
PreparedStatement st = [this is a reference i obtain from a method];
st.setObject(index,pgObj);

The castToPGObject converts the FullContact instance to the ROW format. This is the result of the conversion of one instance:

ROW(ROW('Italy','Country',null,10,'1202'),ROW('email','aPhone','aMobile',null))

Used with an INSERT it works like a charm, but when I try to use PreparedStatement it fails. I have looked at the code of the JDBC Driver and it seems that the STRUCT type of PostgreSQL is not supported. Am i missing something?

From setObject I obtain that instead of the right conversion PreparedStatement adds ' where it shouldn't. E.g., instead of the ROW above I obtain:

'ROW(ROW(''Italy'',''Country'',null,10,''1202''),ROW(''email'',''aPhone'',''aMobile'',null))'

And this fails because it is not recognized as a Struct any more. I have tried also by adding

st.setEscapeProcessing(false);

but the ' are still added. I need to use PreparedStatement because of a problem I have inside Hibernate when defining a custom usertype.
I am thinking about joining the JDBC Driver project to try to add support for STRUCT object.

Upvotes: 3

Views: 1883

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

The row constructor ROW() is a PostgreSQL function call. You need to pass a value - or to be precise: a string literal representing the composite type.

Try this syntax:

SELECT '("(Italy,""Country with space"",,10,1202)"
        ,"(email,aPhone,aMobile,)")'::fullcontact

So your value would be:

("(Italy,""Country with space"",,10,1202)","(email,aPhone,aMobile,)")

I put in 'Country with space' to demonstrate the syntax with special characters.
Read the details about composite value input in the manual.

Upvotes: 1

Related Questions