I'm trying to call a Postgres routine which takes a custom Object type as a parameter.
create type person_type as
(
first varchar,
second varchar,
is_real boolean
);
My routine (stored proc):
create function person_routine(person person_type)
returns void
language plpgsql
as $$
BEGIN
INSERT INTO person(first, second, is_real) VALUES
(person.first,person.second,person.is_real);
END;
$$;
Then I attempt creating a Java class to represent the custom type:
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
public class PersonType implements SQLData {
public String first;
public String second;
public boolean is_real;
private String sql_type;
@Override
public String getSQLTypeName() throws SQLException {
return sql_type;
}
@Override
public void readSQL(SQLInput stream, String typeName) throws SQLException {
sql_type = typeName;
second = stream.readString();
first = stream.readString();
is_real = stream.readBoolean();
}
@Override
public void writeSQL(SQLOutput stream) throws SQLException {
stream.writeString(first);
stream.writeBoolean(is_real);
stream.writeString(second);
}
}
Then i attempted to execute the code like this:
.apply(JdbcIO.<Person>write()
.withDataSourceConfiguration(JdbcIO.DataSourceConfiguration.create(
"org.postgresql.Driver", configuration.GetValue("postgres_host"))
.withUsername(configuration.GetValue("postgres_username"))
.withPassword(configuration.GetValue("postgres_password")))
.withStatement("SELECT person_routine(?)")
.withPreparedStatementSetter(new JdbcIO.PreparedStatementSetter<Person>() {
public void setParameters(Person element, PreparedStatement query)
throws SQLException {
PersonType dto = new PersonType();
dto.first = element.first;
dto.second = element.second;
dto.is_real = element.is_real;
query.setObject(1, dto);
}
})
);
Unfortunately that gives me an exception:
java.lang.RuntimeException: org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of dto.PersonType. Use setObject() with an explicit Types value to specify the type to use.
Any help would be great.
So, it's about using PGobject(). This is how i achieved it and it seems to work really well.
PGobject person = new PGobject();
person.setType("person_type");
person.setValue(String.format("(%s,%s,%s)","something","something","FALSE"));
query.setObject(1, person);