CODEWITHSUNDEEP
javapostgresqljdbcapache-beamjdbc-postgres
Chimmy
Chimmy

Reputation: 167

JDBCIO Calling Postgres Routine (Stored Proc) which takes a Custom Object Type as parameter

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.

Upvotes: 1

Views: 235

Answers (1)

Chimmy
Chimmy

Reputation: 167

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);

Upvotes: 0

Related Questions