1

I am trying to insert the data onto different databases. Used dbutils to incorporate QueryRunner.batch() for batch inserts. This worked for SQL Server which casts the data to the corresponding type. I tried the same with PostgreSQL but of no avail. I tried a sample insert but that has failed in PostGres which validates my claim whereas the inserts for SQL Server were successful:

private void postGrestest() throws ClassNotFoundException, SQLException
{
    Class.forName("org.postgresql.Driver");

    String dropStmt = "DROP TABLE PUBLIC.TEST";
    String createStmt = "CREATE TABLE PUBLIC.TEST(COL1 VARCHAR(10), COL2 BOOLEAN)";
    String insertStmt = "INSERT INTO PUBLIC.TEST(COL1, COL2) VALUES (?, ?)";
    try (Connection connection = DriverManager.getConnection(
            "jdbc:postgresql://<host>:5432/<dbname>", "<username>", "<password>");
            Statement stmt = connection.createStatement();
            PreparedStatement ps = connection.prepareStatement(insertStmt);)
    {
        //stmt.execute(dropStmt);
        stmt.execute(createStmt);
        Random r = new Random();
        for (int i = 0; i < 100; i++)
        {
            Object str = "Test" + i;
            ps.setObject(1, str);
            Object obj = String.valueOf(r.nextBoolean());
            ps.setObject(2, obj);
            ps.executeUpdate();
        }
    }
}

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "col2" is of type boolean but expression is of type character varying Hint: You will need to rewrite or cast the expression.
Position: 49

private void sqlserverTest() throws SQLException, ClassNotFoundException
{
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

    String dropStmt = "DROP TABLE DBO.TEST";
    String createStmt = "CREATE TABLE DBO.TEST(COL1 VARCHAR(10), COL2 BIT)";
    String insertStmt = "INSERT INTO DBO.TEST(COL1, COL2) VALUES (?, ?)";
    try (Connection connection = DriverManager.getConnection(
            "jdbc:sqlserver://<host>:<port>", "<username>", "<password>");
            Statement stmt = connection.createStatement();
            PreparedStatement ps = connection.prepareStatement(insertStmt);)
    {
        //stmt.execute(dropStmt);
        stmt.execute(createStmt);
        Random r = new Random();
        for (int i = 0; i < 100; i++)
        {
            Object str = "Test" + i;
            ps.setObject(1, str);
            Object obj = String.valueOf(r.nextBoolean());
            ps.setObject(2, obj);
            ps.executeUpdate();
        }
    }
}

The reason I have to cast because, I read the data from a file and get as an object array. I do not have idea of the type beforehand and would like to use setObject instead of specific types setBoolean, setInteger, etc.

What other ways that I can use without casting at my application level and let the driver handle the cast?

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.