@Test
public void testInsertBind() throws Exception {
final List<JdbcTest.Employee> employees = new ArrayList<>();
CalciteAssert.AssertThat with = mutable(employees);
with.query("select count(*) as c from \"foo\".\"bar\"").returns("C=1\n");
with.doWithConnection(
new Function<CalciteConnection, Object>() {
public Object apply(CalciteConnection c) {
try {
final String sql = "insert into \"foo\".\"bar\"\n" + "values (?, 0, ?, 10.0, null)";
try (PreparedStatement p = c.prepareStatement(sql)) {
p.setInt(1, 1);
p.setString(2, "foo");
final int count = p.executeUpdate();
assertThat(count, is(1));
}
return null;
} catch (SQLException e) {
throw Throwables.propagate(e);
}
}
});
with.query("select count(*) as c from \"foo\".\"bar\"").returns("C=2\n");
with.query("select * from \"foo\".\"bar\"")
.returnsUnordered(
"empid=0; deptno=0; name=first; salary=0.0; commission=null",
"empid=1; deptno=0; name=foo; salary=10.0; commission=null");
}
/** Some of the rows have the wrong number of columns. */
@Test
public void testInsertMultipleRowMismatch() {
final List<JdbcTest.Employee> employees = new ArrayList<>();
CalciteAssert.AssertThat with = mutable(employees);
with.query(
"insert into \"foo\".\"bar\" values\n"
+ " (1, 3, 'third'),\n"
+ " (1, 4, 'fourth'),\n"
+ " (1, 5, 'fifth ', 3)")
.throws_("Incompatible types");
}
@Test
public void testInsert2() {
final List<JdbcTest.Employee> employees = new ArrayList<>();
CalciteAssert.AssertThat with = mutable(employees);
with.query("insert into \"foo\".\"bar\" values (1, 1, 'second', 2, 2)").updates(1);
with.query(
"insert into \"foo\".\"bar\"\n"
+ "values (1, 3, 'third', 0, 3), (1, 4, 'fourth', 0, 4), (1, 5, 'fifth ', 0, 3)")
.updates(3);
with.query("select count(*) as c from \"foo\".\"bar\"").returns("C=5\n");
with.query("insert into \"foo\".\"bar\" values (1, 6, null, 0, null)").updates(1);
with.query("select count(*) as c from \"foo\".\"bar\"").returns("C=6\n");
}
@Test
public void testInsert() {
final List<JdbcTest.Employee> employees = new ArrayList<>();
CalciteAssert.AssertThat with = mutable(employees);
with.query("select * from \"foo\".\"bar\"")
.returns("empid=0; deptno=0; name=first; salary=0.0; commission=null\n");
with.query("insert into \"foo\".\"bar\" select * from \"hr\".\"emps\"").updates(4);
with.query("select count(*) as c from \"foo\".\"bar\"").returns("C=5\n");
with.query(
"insert into \"foo\".\"bar\" " + "select * from \"hr\".\"emps\" where \"deptno\" = 10")
.updates(3);
with.query("select \"name\", count(*) as c from \"foo\".\"bar\" " + "group by \"name\"")
.returnsUnordered(
"name=Bill; C=2",
"name=Eric; C=1",
"name=Theodore; C=2",
"name=first; C=1",
"name=Sebastian; C=2");
}