CODEWITHSUNDEEP
javasqldatabasejdbcvertica
2Big2BeSmall
2Big2BeSmall

Reputation: 1378

Vertica Jdbc driver NOT throwing - SQL state: 22001 when ERROR: value too long for type

I have an issue I'm facing with Vertica JDBC Driver I try to store data into my DB - but sometimes the data isn't stored at all and I don't get any error its appear to be succeeded. it also happens when I try to use a file.

This seems to be a major bug - which against the 4 pillar of Data managment. sample code is attached.

How can I solve this?

public class VerticaTest {
    public static void main(String[] args) throws Exception
    {
        Class.forName("com.vertica.jdbc.Driver");
        String tableName = "vertica_test1";
        java.sql.Connection connection = DriverManager.getConnection("jdbc:vertica://xyz.qwe.com:5433/DB?tcpKeepAlive=true", "user", "admin");
        java.sql.Statement st = connection.createStatement();
        st.execute("Create table " + tableName + " (test_name varchar(10))");
        st.execute("grant all on " + tableName + " to public ");
        String value = "short";
        if (true) {
            value = "543543543 Sun Aug 11 065650 UTC 207657657650";
        }
        InputStream stream = new ByteArrayInputStream(value.getBytes(StandardCharsets.UTF_8));
        String copy = "COPY " + tableName + " FROM stdin  WITH parser LibCSVParser() abort on error no commit";
        VerticaCopyStream vcs = new VerticaCopyStream((VerticaConnection) connection, copy);
        vcs.start();
        vcs.addStream(stream);
        System.out.println("Reject size ROW IS " + vcs.getRejects().size());
        vcs.execute();
        ResultSet rs = st.executeQuery("SELECT count(1) FROM " + tableName);
        while (rs.next()) {
            int count = rs.getInt(1);
            System.out.println("result = " + count);
        }
        rs.close();
        // st.execute("drop table " + tableName);
    }

}

enter image description here

Upvotes: 0

Views: 440

Answers (1)

Nicolas Riousset
Nicolas Riousset

Reputation: 3609

The Vertica copy statement doesn't behave like a traditional SQL INSERT statement. COPY doesn't fail, but reports rows that couldn't be inserted.

That's wise : let's say you want to insert 10 millions rows, you may not want it to fail fail because a single line is invalid.

To deal with the insertion errors, you have several options :

  • the "ABORT ON ERROR" parameter allows you to fail the whole COPY command as soon as one error occurs
  • With Java, I tend to use the INSERT prepared statement (which behind the scenes uses COPY), as documented here. The PreparedStatement.executeBatch(); returns an array of int representing the success/failure of each processed row.
  • other strategies to deal with COPY errors are documented here.

Upvotes: 2

Related Questions