error setting java String[] to postgres prepared statement

Question

I'm trying to insert an Array of Strings into Postgres. I get an invalid type error from Postgres.

public static void main(String[] args) throws SQLException {
    String[] skus = { "0514", "0414", "0314", "0214", "0114", "1213", "1113", "1013", "0913", "0813", "0713", "0613" };
    String sqlString = "Insert into dbo.Inventory_Metrics skus values(?)";
    Connection conn = DriverManager.getConnection(getPostgresConnUrl());
    PreparedStatement ps = conn.prepareStatement(sqlString);

    //THIS NEXT LINE THROWS AN ERROR

    ps.setObject(1, skus, java.sql.Types.NVARCHAR, skus.length);
    int status = ps.executeUpdate();
    ps.close();

    System.out.print(status);
}
public static String getPostgresConnUrl() {
    String database = "mycode";
    String userName = "xxxxxxxx";
    String password = "xxxxxxxx";
    return "jdbc:postgresql://192.168.0.50:5432/" + database + "?user=" + userName + "&password=" + password;

}

But you have a single question mark, and an array of values. You might try a string of comma-separated values instead. — Dave Newton
– Dave Newton, Commented Sep 23, 2014 at 15:33
The JDBC API doesn't work this way. Please describe what you want to achieve. On top of that: PostgreSQL doesn't support the NVARCHAR type afaik — Mark Rotteveel
– Mark Rotteveel, Commented Sep 23, 2014 at 16:06
ps.setObject(1, skus, java.sql.Types.VARCHAR, skus.length); //Fixes error, post it as the answer. — Voltron
– Voltron, Commented Sep 23, 2014 at 19:10
Near-identical repost at stackoverflow.com/q/26005620/398670 . (+1, thanks for including your code and the error message. In future, your PostgreSQL and PgJDBC version should also be included in all questions). — Craig Ringer
– Craig Ringer, Commented Sep 24, 2014 at 2:42

Craig Ringer · Accepted Answer · 2014-09-24 01:01:40Z

6

You must use the JDBC array API, per the documentation.

You can't just setObject an array in JDBC. It'd be nice if that's how it works, but it isn't. You're expected to handle arrays specially.

Array jdbcSkus = con.createArrayOf("VARCHAR", skus);
pstmt.setArray(2, jdbcSkus);

answered Sep 24, 2014 at 1:01

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

2 Comments

Craig Ringer Over a year ago

@NathanHughes Re your deleted answer - Most DBMSs support native arrays, and it's almost never right to store comma-separated data if you can avoid it.

Nathan Hughes Over a year ago

agreed, it was just unclear what the OP wanted. i assumed nvarchar was right and went with that, then deleted when I saw Mark's comment. couldn't agree more about avoiding comma-separated data (almost never might not be strong enough wording). not sure an array is much better though.

Collectives™ on Stack Overflow

error setting java String[] to postgres prepared statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related