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, Sep 23 '14 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, Sep 23 '14 at 16:06
ps.setObject(1, skus, java.sql.Types.VARCHAR, skus.length); //Fixes error, post it as the answer. — Voltron, Sep 23 '14 at 19:10
Near-identical repost at http://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, Sep 24 '14 at 02:42

score 6 · Accepted Answer · answered Sep 24 '14 at 01:01

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 '14 at 01:01

Craig Ringer

307,061
76
688
778

1

@NathanHughes Re your deleted answer - Most DBMSs support native arrays, and [it's almost never right to store comma-separated data](http://dba.stackexchange.com/q/55871/7788) if you can avoid it. – Craig Ringer Sep 24 '14 at 07:34
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. – Nathan Hughes Sep 25 '14 at 18:16

error setting java String[] to postgres prepared statement

1 Answers1

Linked